From 2af8300e508f2bc9a3ab960a0bc19ff0b011aeeb Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sun, 22 Mar 2009 15:12:11 +0000 Subject: [PATCH] update cell type when setting cached formula result in XSSFCell, also improved common xssf-hssf tests git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@757198 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/xssf/usermodel/XSSFCell.java | 88 +++++- .../poi/xssf/usermodel/TestXSSFCell.java | 67 +++++ .../poi/hssf/usermodel/TestHSSFCell.java | 235 +++------------ .../apache/poi/ss/usermodel/BaseTestCell.java | 277 ++++++++++++++++++ 6 files changed, 454 insertions(+), 215 deletions(-) create mode 100755 src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java create mode 100755 src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index ed635f9abd..fd101fb8f2 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + update cell type when setting cached formula result in XSSFCell added modifiers for anchor type to XSSFClientAnchor 46772 - support built-in data formats in XSSFDataFormat 46719 - fixed XSSFSheet.shiftRows to correctly preserve row heights diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index eb6a56f0b6..3b3ba8e7ab 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + update cell type when setting cached formula result in XSSFCell added modifiers for anchor type to XSSFClientAnchor 46772 - support built-in data formats in XSSFDataFormat 46719 - fixed XSSFSheet.shiftRows to correctly preserve row heights diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index 433b2c650c..bd8c79a0c6 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -212,16 +212,12 @@ public final class XSSFCell implements Cell { * will change the cell to a numeric cell and set its value. */ public void setCellValue(double value) { - int cellType = getCellType(); - switch (cellType) { - case CELL_TYPE_ERROR: - case CELL_TYPE_FORMULA: - cell.setV(String.valueOf(value)); - break; - default: - cell.setT(STCellType.N); - cell.setV(String.valueOf(value)); - break; + if(Double.isInfinite(value) || Double.isNaN(value)) { + cell.setT(STCellType.E); + cell.setV(FormulaError.NUM.getString()); + } else { + cell.setT(STCellType.N); + cell.setV(String.valueOf(value)); } } @@ -303,6 +299,7 @@ public final class XSSFCell implements Cell { switch(cellType){ case Cell.CELL_TYPE_FORMULA: cell.setV(str.getString()); + cell.setT(STCellType.STR); break; default: if(cell.getT() == STCellType.INLINE_STR) { @@ -466,7 +463,7 @@ public final class XSSFCell implements Cell { return CELL_TYPE_FORMULA; } - return getBaseCellType(); + return getBaseCellType(true); } /** @@ -480,18 +477,18 @@ public final class XSSFCell implements Cell { throw new IllegalStateException("Only formula cells have cached results"); } - return getBaseCellType(); + return getBaseCellType(false); } /** * Detect cell type based on the "t" attribute of the CTCell bean */ - private int getBaseCellType() { + private int getBaseCellType(boolean blankCells) { switch (cell.getT().intValue()) { case STCellType.INT_B: return CELL_TYPE_BOOLEAN; case STCellType.INT_N: - if (!cell.isSetV()) { + if (!cell.isSetV() && blankCells) { // ooxml does have a separate cell type of 'blank'. A blank cell gets encoded as // (either not present or) a numeric cell with no value set. // The formula evaluator (and perhaps other clients of this interface) needs to @@ -680,12 +677,15 @@ public final class XSSFCell implements Cell { * @see #CELL_TYPE_ERROR */ public void setCellType(int cellType) { + int prevType = getCellType(); switch (cellType) { case CELL_TYPE_BLANK: setBlank(); break; case CELL_TYPE_BOOLEAN: + String newVal = convertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING; cell.setT(STCellType.B); + cell.setV(newVal); break; case CELL_TYPE_NUMERIC: cell.setT(STCellType.N); @@ -694,6 +694,13 @@ public final class XSSFCell implements Cell { cell.setT(STCellType.E); break; case CELL_TYPE_STRING: + if(prevType != CELL_TYPE_STRING){ + String str = convertCellValueToString(); + XSSFRichTextString rt = new XSSFRichTextString(str); + rt.setStylesTableReference(stylesSource); + int sRef = sharedStringSource.addEntry(rt.getCTRst()); + cell.setV(Integer.toString(sRef)); + } cell.setT(STCellType.S); break; case CELL_TYPE_FORMULA: @@ -847,4 +854,57 @@ public final class XSSFCell implements Cell { return cell; } + /** + * Chooses a new boolean value for the cell when its type is changing.

+ * + * Usually the caller is calling setCellType() with the intention of calling + * setCellValue(boolean) straight afterwards. This method only exists to give + * the cell a somewhat reasonable value until the setCellValue() call (if at all). + * TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this + */ + private boolean convertCellValueToBoolean() { + int cellType = getCellType(); + + switch (cellType) { + case CELL_TYPE_BOOLEAN: + return TRUE_AS_STRING.equals(cell.getV()); + case CELL_TYPE_STRING: + int sstIndex = Integer.parseInt(cell.getV()); + XSSFRichTextString rt = new XSSFRichTextString(sharedStringSource.getEntryAt(sstIndex)); + String text = rt.getString(); + return Boolean.valueOf(text); + case CELL_TYPE_NUMERIC: + return Double.parseDouble(cell.getV()) != 0; + + case CELL_TYPE_FORMULA: + case CELL_TYPE_ERROR: + case CELL_TYPE_BLANK: + return false; + } + throw new RuntimeException("Unexpected cell type (" + cellType + ")"); + } + + private String convertCellValueToString() { + int cellType = getCellType(); + + switch (cellType) { + case CELL_TYPE_BLANK: + return ""; + case CELL_TYPE_BOOLEAN: + return TRUE_AS_STRING.equals(cell.getV()) ? "TRUE" : "FALSE"; + case CELL_TYPE_STRING: + int sstIndex = Integer.parseInt(cell.getV()); + XSSFRichTextString rt = new XSSFRichTextString(sharedStringSource.getEntryAt(sstIndex)); + return rt.getString(); + case CELL_TYPE_NUMERIC: + return String.valueOf(Double.parseDouble(cell.getV())); + case CELL_TYPE_ERROR: + return cell.getV(); + case CELL_TYPE_FORMULA: + // should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator + return ""; + } + throw new RuntimeException("Unexpected cell type (" + cellType + ")"); + } + } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java new file mode 100755 index 0000000000..f422bedb42 --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java @@ -0,0 +1,67 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.xssf.usermodel; + +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.ITestDataProvider; +import org.apache.poi.xssf.XSSFITestDataProvider; + +/** + * @author Yegor Kozlov + */ +public final class TestXSSFCell extends BaseTestCell { + + @Override + protected ITestDataProvider getTestDataProvider(){ + return XSSFITestDataProvider.getInstance(); + } + + public void testSetValues() { + baseTestSetValues(); + } + + public void testBoolErr() { + baseTestBoolErr(); + } + + public void testFormulaStyle() { + baseTestFormulaStyle(); + } + + public void testToString() { + baseTestToString(); + } + + public void testSetFormulaValue() { + baseTestSetFormulaValue(); + } + + public void testChangeCellType() { + Workbook wb = getTestDataProvider().createWorkbook(); + Row row = wb.createSheet().createRow(0); + baseTestChangeTypeStringToBool(row.createCell(0)); + baseTestChangeTypeBoolToString(row.createCell(1)); + baseTestChangeTypeErrorToNumber(row.createCell(2)); + baseTestChangeTypeErrorToBoolean(row.createCell(3)); + + //TODO: works in HSSF but fails in XSSF + //baseTestChangeTypeFormulaToBoolean(row.createCell(4)); + } + + +} \ No newline at end of file diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java index f79f0325dc..e00c042366 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java @@ -21,16 +21,14 @@ import java.util.Date; import java.util.GregorianCalendar; import junit.framework.AssertionFailedError; -import junit.framework.TestCase; - -import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.hssf.HSSFITestDataProvider; import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.record.DBCellRecord; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.StringRecord; -import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.ErrorConstants; +import org.apache.poi.ss.usermodel.BaseTestCell; /** * Tests various functionality having to do with {@link HSSFCell}. For instance support for @@ -39,80 +37,28 @@ import org.apache.poi.ss.usermodel.ErrorConstants; * @author Dan Sherman (dsherman at isisph.com) * @author Alex Jacoby (ajacoby at gmail.com) */ -public final class TestHSSFCell extends TestCase { +public final class TestHSSFCell extends BaseTestCell { - private static HSSFWorkbook openSample(String sampleFileName) { - return HSSFTestDataSamples.openSampleWorkbook(sampleFileName); - } - private static HSSFWorkbook writeOutAndReadBack(HSSFWorkbook original) { - return HSSFTestDataSamples.writeOutAndReadBack(original); - } - - public void testSetValues() { - HSSFWorkbook book = new HSSFWorkbook(); - HSSFSheet sheet = book.createSheet("test"); - HSSFRow row = sheet.createRow(0); + @Override + protected HSSFITestDataProvider getTestDataProvider(){ + return HSSFITestDataProvider.getInstance(); + } - HSSFCell cell = row.createCell(0); - - cell.setCellValue(1.2); - assertEquals(1.2, cell.getNumericCellValue(), 0.0001); - assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCellType()); - - cell.setCellValue(false); - assertEquals(false, cell.getBooleanCellValue()); - assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cell.getCellType()); - - cell.setCellValue(new HSSFRichTextString("Foo")); - assertEquals("Foo", cell.getRichStringCellValue().getString()); - assertEquals(HSSFCell.CELL_TYPE_STRING, cell.getCellType()); - - cell.setCellValue(new HSSFRichTextString("345")); - assertEquals("345", cell.getRichStringCellValue().getString()); - assertEquals(HSSFCell.CELL_TYPE_STRING, cell.getCellType()); + public void testSetValues() { + baseTestSetValues(); } /** * test that Boolean and Error types (BoolErrRecord) are supported properly. */ public void testBoolErr() { - - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet("testSheet1"); - HSSFRow r = null; - HSSFCell c = null; - r = s.createRow(0); - c=r.createCell(1); - //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); - c.setCellValue(true); - - c=r.createCell(2); - //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); - c.setCellValue(false); - - r = s.createRow(1); - c=r.createCell(1); - //c.setCellType(HSSFCell.CELL_TYPE_ERROR); - c.setCellErrorValue((byte)0); - - c=r.createCell(2); - //c.setCellType(HSSFCell.CELL_TYPE_ERROR); - c.setCellErrorValue((byte)7); - - wb = writeOutAndReadBack(wb); - s = wb.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(1); - assertTrue("boolean value 0,1 = true",c.getBooleanCellValue()); - c = r.getCell(2); - assertTrue("boolean value 0,2 = false",c.getBooleanCellValue()==false); - r = s.getRow(1); - c = r.getCell(1); - assertTrue("boolean value 0,1 = 0",c.getErrorCellValue() == 0); - c = r.getCell(2); - assertTrue("boolean value 0,2 = 7",c.getErrorCellValue() == 7); + baseTestBoolErr(); } + public void testSetFormulaValue() { + baseTestSetFormulaValue(); + } + /** * Checks that the recognition of files using 1904 date windowing * is working properly. Conversion of the date is also an issue, @@ -123,7 +69,7 @@ public final class TestHSSFCell extends TestCase { Date date = cal.getTime(); // first check a file with 1900 Date Windowing - HSSFWorkbook workbook = openSample("1900DateWindowing.xls"); + HSSFWorkbook workbook = getTestDataProvider().openSampleWorkbook("1900DateWindowing.xls"); HSSFSheet sheet = workbook.getSheetAt(0); assertEquals("Date from file using 1900 Date Windowing", @@ -131,7 +77,7 @@ public final class TestHSSFCell extends TestCase { sheet.getRow(0).getCell(0).getDateCellValue().getTime()); // now check a file with 1904 Date Windowing - workbook = openSample("1904DateWindowing.xls"); + workbook = getTestDataProvider().openSampleWorkbook("1904DateWindowing.xls"); sheet = workbook.getSheetAt(0); assertEquals("Date from file using 1904 Date Windowing", @@ -151,19 +97,19 @@ public final class TestHSSFCell extends TestCase { // first check a file with 1900 Date Windowing HSSFWorkbook wb; - wb = openSample("1900DateWindowing.xls"); + wb = getTestDataProvider().openSampleWorkbook("1900DateWindowing.xls"); setCell(wb, 0, 1, date); - wb = writeOutAndReadBack(wb); + wb = getTestDataProvider().writeOutAndReadBack(wb); assertEquals("Date from file using 1900 Date Windowing", date.getTime(), readCell(wb, 0, 1).getTime()); // now check a file with 1904 Date Windowing - wb = openSample("1904DateWindowing.xls"); + wb = getTestDataProvider().openSampleWorkbook("1904DateWindowing.xls"); setCell(wb, 0, 1, date); - wb = writeOutAndReadBack(wb); + wb = getTestDataProvider().writeOutAndReadBack(wb); assertEquals("Date from file using 1900 Date Windowing", date.getTime(), readCell(wb, 0, 1).getTime()); @@ -192,7 +138,7 @@ public final class TestHSSFCell extends TestCase { */ public void testActiveCell() { //read in sample - HSSFWorkbook book = openSample("Simple.xls"); + HSSFWorkbook book = getTestDataProvider().openSampleWorkbook("Simple.xls"); //check initial position HSSFSheet umSheet = book.getSheetAt(0); @@ -211,7 +157,7 @@ public final class TestHSSFCell extends TestCase { 3, s.getActiveCellRow()); //write book to temp file; read and verify that position is serialized - book = writeOutAndReadBack(book); + book = getTestDataProvider().writeOutAndReadBack(book); umSheet = book.getSheetAt(0); s = umSheet.getSheet(); @@ -226,43 +172,7 @@ public final class TestHSSFCell extends TestCase { * test that Cell Styles being applied to formulas remain intact */ public void testFormulaStyle() { - - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet("testSheet1"); - HSSFRow r = null; - HSSFCell c = null; - HSSFCellStyle cs = wb.createCellStyle(); - HSSFFont f = wb.createFont(); - f.setFontHeightInPoints((short) 20); - f.setColor(HSSFColor.RED.index); - f.setBoldweight(f.BOLDWEIGHT_BOLD); - f.setFontName("Arial Unicode MS"); - cs.setFillBackgroundColor((short)3); - cs.setFont(f); - cs.setBorderTop((short)1); - cs.setBorderRight((short)1); - cs.setBorderLeft((short)1); - cs.setBorderBottom((short)1); - - r = s.createRow(0); - c=r.createCell(0); - c.setCellStyle(cs); - c.setCellFormula("2*3"); - - wb = writeOutAndReadBack(wb); - s = wb.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - - assertTrue("Formula Cell at 0,0", (c.getCellType()==c.CELL_TYPE_FORMULA)); - cs = c.getCellStyle(); - - assertNotNull("Formula Cell Style", cs); - assertTrue("Font Index Matches", (cs.getFontIndex() == f.getIndex())); - assertTrue("Top Border", (cs.getBorderTop() == (short)1)); - assertTrue("Left Border", (cs.getBorderLeft() == (short)1)); - assertTrue("Right Border", (cs.getBorderRight() == (short)1)); - assertTrue("Bottom Border", (cs.getBorderBottom() == (short)1)); + baseTestFormulaStyle(); } /** @@ -270,7 +180,7 @@ public final class TestHSSFCell extends TestCase { */ public void testWithHyperlink() { - HSSFWorkbook wb = openSample("WithHyperlink.xls"); + HSSFWorkbook wb = getTestDataProvider().openSampleWorkbook("WithHyperlink.xls"); HSSFSheet sheet = wb.getSheetAt(0); HSSFCell cell = sheet.getRow(4).getCell(0); @@ -288,7 +198,7 @@ public final class TestHSSFCell extends TestCase { */ public void testWithTwoHyperlinks() { - HSSFWorkbook wb = openSample("WithTwoHyperLinks.xls"); + HSSFWorkbook wb = getTestDataProvider().openSampleWorkbook("WithTwoHyperLinks.xls"); HSSFSheet sheet = wb.getSheetAt(0); @@ -311,30 +221,7 @@ public final class TestHSSFCell extends TestCase { /**tests the toString() method of HSSFCell*/ public void testToString() { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFRow r = wb.createSheet("Sheet1").createRow(0); - - r.createCell(0).setCellValue(true); - r.createCell(1).setCellValue(1.5); - r.createCell(2).setCellValue(new HSSFRichTextString("Astring")); - r.createCell(3).setCellErrorValue((byte)HSSFErrorConstants.ERROR_DIV_0); - r.createCell(4).setCellFormula("A1+B1"); - - assertEquals("Boolean", "TRUE", r.getCell(0).toString()); - assertEquals("Numeric", "1.5", r.getCell(1).toString()); - assertEquals("String", "Astring", r.getCell(2).toString()); - assertEquals("Error", "#DIV/0!", r.getCell(3).toString()); - assertEquals("Formula", "A1+B1", r.getCell(4).toString()); - - //Write out the file, read it in, and then check cell values - wb = writeOutAndReadBack(wb); - - r = wb.getSheetAt(0).getRow(0); - assertEquals("Boolean", "TRUE", r.getCell(0).toString()); - assertEquals("Numeric", "1.5", r.getCell(1).toString()); - assertEquals("String", "Astring", r.getCell(2).toString()); - assertEquals("Error", "#DIV/0!", r.getCell(3).toString()); - assertEquals("Formula", "A1+B1", r.getCell(4).toString()); + baseTestToString(); } public void testSetStringInFormulaCell_bug44606() { @@ -393,88 +280,34 @@ public final class TestHSSFCell extends TestCase { } } - public void testChangeTypeStringToBool() { + public void testChangeTypeStringToBool() { HSSFCell cell = new HSSFWorkbook().createSheet("Sheet1").createRow(0).createCell(0); - - cell.setCellValue(new HSSFRichTextString("TRUE")); - assertEquals(HSSFCell.CELL_TYPE_STRING, cell.getCellType()); - try { - cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); - } catch (ClassCastException e) { - throw new AssertionFailedError( - "Identified bug in conversion of cell from text to boolean"); - } - - assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cell.getCellType()); - assertEquals(true, cell.getBooleanCellValue()); - cell.setCellType(HSSFCell.CELL_TYPE_STRING); - assertEquals("TRUE", cell.getRichStringCellValue().getString()); - - // 'false' text to bool and back - cell.setCellValue(new HSSFRichTextString("FALSE")); - cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); - assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cell.getCellType()); - assertEquals(false, cell.getBooleanCellValue()); - cell.setCellType(HSSFCell.CELL_TYPE_STRING); - assertEquals("FALSE", cell.getRichStringCellValue().getString()); + baseTestChangeTypeStringToBool(cell); } public void testChangeTypeBoolToString() { HSSFCell cell = new HSSFWorkbook().createSheet("Sheet1").createRow(0).createCell(0); - cell.setCellValue(true); - try { - cell.setCellType(HSSFCell.CELL_TYPE_STRING); - } catch (IllegalStateException e) { - if (e.getMessage().equals("Cannot get a text value from a boolean cell")) { - throw new AssertionFailedError( - "Identified bug in conversion of cell from boolean to text"); - } - throw e; - } - assertEquals("TRUE", cell.getRichStringCellValue().getString()); + baseTestChangeTypeBoolToString(cell); } public void testChangeTypeErrorToNumber_bug46479() { HSSFCell cell = new HSSFWorkbook().createSheet("Sheet1").createRow(0).createCell(0); - cell.setCellErrorValue((byte)HSSFErrorConstants.ERROR_NAME); - try { - cell.setCellValue(2.5); - } catch (ClassCastException e) { - throw new AssertionFailedError("Identified bug 46479b"); - } - assertEquals(2.5, cell.getNumericCellValue(), 0.0); + baseTestChangeTypeErrorToNumber(cell); } public void testChangeTypeErrorToBoolean_bug46479() { HSSFCell cell = new HSSFWorkbook().createSheet("Sheet1").createRow(0).createCell(0); - cell.setCellErrorValue((byte)HSSFErrorConstants.ERROR_NAME); - cell.setCellValue(true); - try { - cell.getBooleanCellValue(); - } catch (IllegalStateException e) { - if (e.getMessage().equals("Cannot get a boolean value from a error cell")) { - - throw new AssertionFailedError("Identified bug 46479c"); - } - throw e; - } - assertEquals(true, cell.getBooleanCellValue()); + baseTestChangeTypeErrorToBoolean(cell); } /** - * Test for bug in convertCellValueToBoolean to make sure that formula results get converted + * Test for bug in convertCellValueToBoolean to make sure that formula results get converted */ public void testChangeTypeFormulaToBoolean_bug46479() { HSSFCell cell = new HSSFWorkbook().createSheet("Sheet1").createRow(0).createCell(0); - cell.setCellFormula("1=1"); - cell.setCellValue(true); - cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); - if (cell.getBooleanCellValue() == false) { - throw new AssertionFailedError("Identified bug 46479d"); - } - assertEquals(true, cell.getBooleanCellValue()); + baseTestChangeTypeFormulaToBoolean(cell); } - + /** * Test for small bug observable around r736460 (prior to version 3.5). POI fails to remove * the {@link StringRecord} following the {@link FormulaRecord} after the result type had been diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java new file mode 100755 index 0000000000..9d7c78fb0f --- /dev/null +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java @@ -0,0 +1,277 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.usermodel; + +import junit.framework.TestCase; +import junit.framework.AssertionFailedError; +import org.apache.poi.ss.ITestDataProvider; + +/** + * Common superclass for testing {@link org.apache.poi.xssf.usermodel.XSSFCell} and + * {@link org.apache.poi.hssf.usermodel.HSSFCell} + */ +public abstract class BaseTestCell extends TestCase { + + /** + * @return an object that provides test data in HSSF / XSSF specific way + */ + protected abstract ITestDataProvider getTestDataProvider(); + + public void baseTestSetValues() { + Workbook book = getTestDataProvider().createWorkbook(); + Sheet sheet = book.createSheet("test"); + Row row = sheet.createRow(0); + + CreationHelper factory = book.getCreationHelper(); + Cell cell = row.createCell(0); + + cell.setCellValue(1.2); + assertEquals(1.2, cell.getNumericCellValue(), 0.0001); + assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); + + cell.setCellValue(false); + assertEquals(false, cell.getBooleanCellValue()); + assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + + cell.setCellValue(factory.createRichTextString("Foo")); + assertEquals("Foo", cell.getRichStringCellValue().getString()); + assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); + + cell.setCellValue(factory.createRichTextString("345")); + assertEquals("345", cell.getRichStringCellValue().getString()); + assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); + } + + /** + * test that Boolean and Error types (BoolErrRecord) are supported properly. + */ + public void baseTestBoolErr() { + + Workbook wb = getTestDataProvider().createWorkbook(); + Sheet s = wb.createSheet("testSheet1"); + Row r = null; + Cell c = null; + r = s.createRow(0); + c=r.createCell(1); + //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); + c.setCellValue(true); + + c=r.createCell(2); + //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); + c.setCellValue(false); + + r = s.createRow(1); + c=r.createCell(1); + //c.setCellType(HSSFCell.CELL_TYPE_ERROR); + c.setCellErrorValue((byte)0); + + c=r.createCell(2); + //c.setCellType(HSSFCell.CELL_TYPE_ERROR); + c.setCellErrorValue((byte)7); + + wb = getTestDataProvider().writeOutAndReadBack(wb); + s = wb.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(1); + assertTrue("boolean value 0,1 = true",c.getBooleanCellValue()); + c = r.getCell(2); + assertTrue("boolean value 0,2 = false",c.getBooleanCellValue()==false); + r = s.getRow(1); + c = r.getCell(1); + assertTrue("boolean value 0,1 = 0",c.getErrorCellValue() == 0); + c = r.getCell(2); + assertTrue("boolean value 0,2 = 7",c.getErrorCellValue() == 7); + } + + /** + * test that Cell Styles being applied to formulas remain intact + */ + public void baseTestFormulaStyle() { + + Workbook wb = getTestDataProvider().createWorkbook(); + Sheet s = wb.createSheet("testSheet1"); + Row r = null; + Cell c = null; + CellStyle cs = wb.createCellStyle(); + Font f = wb.createFont(); + f.setFontHeightInPoints((short) 20); + f.setColor(IndexedColors.RED.getIndex()); + f.setBoldweight(f.BOLDWEIGHT_BOLD); + f.setFontName("Arial Unicode MS"); + cs.setFillBackgroundColor((short)3); + cs.setFont(f); + cs.setBorderTop((short)1); + cs.setBorderRight((short)1); + cs.setBorderLeft((short)1); + cs.setBorderBottom((short)1); + + r = s.createRow(0); + c=r.createCell(0); + c.setCellStyle(cs); + c.setCellFormula("2*3"); + + wb = getTestDataProvider().writeOutAndReadBack(wb); + s = wb.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(0); + + assertTrue("Formula Cell at 0,0", (c.getCellType()==c.CELL_TYPE_FORMULA)); + cs = c.getCellStyle(); + + assertNotNull("Formula Cell Style", cs); + assertTrue("Font Index Matches", (cs.getFontIndex() == f.getIndex())); + assertTrue("Top Border", (cs.getBorderTop() == (short)1)); + assertTrue("Left Border", (cs.getBorderLeft() == (short)1)); + assertTrue("Right Border", (cs.getBorderRight() == (short)1)); + assertTrue("Bottom Border", (cs.getBorderBottom() == (short)1)); + } + + /**tests the toString() method of HSSFCell*/ + public void baseTestToString() { + Workbook wb = getTestDataProvider().createWorkbook(); + Row r = wb.createSheet("Sheet1").createRow(0); + CreationHelper factory = wb.getCreationHelper(); + + r.createCell(0).setCellValue(true); + r.createCell(1).setCellValue(1.5); + r.createCell(2).setCellValue(factory.createRichTextString("Astring")); + r.createCell(3).setCellErrorValue((byte)ErrorConstants.ERROR_DIV_0); + r.createCell(4).setCellFormula("A1+B1"); + + assertEquals("Boolean", "TRUE", r.getCell(0).toString()); + assertEquals("Numeric", "1.5", r.getCell(1).toString()); + assertEquals("String", "Astring", r.getCell(2).toString()); + assertEquals("Error", "#DIV/0!", r.getCell(3).toString()); + assertEquals("Formula", "A1+B1", r.getCell(4).toString()); + + //Write out the file, read it in, and then check cell values + wb = getTestDataProvider().writeOutAndReadBack(wb); + + r = wb.getSheetAt(0).getRow(0); + assertEquals("Boolean", "TRUE", r.getCell(0).toString()); + assertEquals("Numeric", "1.5", r.getCell(1).toString()); + assertEquals("String", "Astring", r.getCell(2).toString()); + assertEquals("Error", "#DIV/0!", r.getCell(3).toString()); + assertEquals("Formula", "A1+B1", r.getCell(4).toString()); + } + + /** + * Test that setting cached formula result keeps the cell type + */ + public void baseTestSetFormulaValue() { + Workbook wb = getTestDataProvider().createWorkbook(); + Sheet s = wb.createSheet(); + Row r = s.createRow(0); + + Cell c1 = r.createCell(0); + c1.setCellFormula("NA()"); + assertEquals(0.0, c1.getNumericCellValue()); + assertEquals(Cell.CELL_TYPE_NUMERIC, c1.getCachedFormulaResultType()); + c1.setCellValue(10); + assertEquals(10.0, c1.getNumericCellValue()); + assertEquals(Cell.CELL_TYPE_FORMULA, c1.getCellType()); + assertEquals(Cell.CELL_TYPE_NUMERIC, c1.getCachedFormulaResultType()); + + Cell c2 = r.createCell(1); + c2.setCellFormula("NA()"); + assertEquals(0.0, c2.getNumericCellValue()); + assertEquals(Cell.CELL_TYPE_NUMERIC, c2.getCachedFormulaResultType()); + c2.setCellValue("I changed!"); + assertEquals("I changed!", c2.getStringCellValue()); + assertEquals(Cell.CELL_TYPE_FORMULA, c2.getCellType()); + assertEquals(Cell.CELL_TYPE_STRING, c2.getCachedFormulaResultType()); + } + + public void baseTestChangeTypeStringToBool(Cell cell) { + + cell.setCellValue("TRUE"); + assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); + try { + cell.setCellType(Cell.CELL_TYPE_BOOLEAN); + } catch (ClassCastException e) { + throw new AssertionFailedError( + "Identified bug in conversion of cell from text to boolean"); + } + + assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals(true, cell.getBooleanCellValue()); + cell.setCellType(Cell.CELL_TYPE_STRING); + assertEquals("TRUE", cell.getRichStringCellValue().getString()); + + // 'false' text to bool and back + cell.setCellValue("FALSE"); + cell.setCellType(Cell.CELL_TYPE_BOOLEAN); + assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); + assertEquals(false, cell.getBooleanCellValue()); + cell.setCellType(Cell.CELL_TYPE_STRING); + assertEquals("FALSE", cell.getRichStringCellValue().getString()); + } + + public void baseTestChangeTypeBoolToString(Cell cell) { + cell.setCellValue(true); + try { + cell.setCellType(Cell.CELL_TYPE_STRING); + } catch (IllegalStateException e) { + if (e.getMessage().equals("Cannot get a text value from a boolean cell")) { + throw new AssertionFailedError( + "Identified bug in conversion of cell from boolean to text"); + } + throw e; + } + assertEquals("TRUE", cell.getRichStringCellValue().getString()); + } + + public void baseTestChangeTypeErrorToNumber(Cell cell) { + cell.setCellErrorValue((byte)ErrorConstants.ERROR_NAME); + try { + cell.setCellValue(2.5); + } catch (ClassCastException e) { + throw new AssertionFailedError("Identified bug 46479b"); + } + assertEquals(2.5, cell.getNumericCellValue(), 0.0); + } + + public void baseTestChangeTypeErrorToBoolean(Cell cell) { + cell.setCellErrorValue((byte)ErrorConstants.ERROR_NAME); + cell.setCellValue(true); + try { + cell.getBooleanCellValue(); + } catch (IllegalStateException e) { + if (e.getMessage().equals("Cannot get a boolean value from a error cell")) { + + throw new AssertionFailedError("Identified bug 46479c"); + } + throw e; + } + assertEquals(true, cell.getBooleanCellValue()); + } + + /** + * Test for bug in convertCellValueToBoolean to make sure that formula results get converted + */ + public void baseTestChangeTypeFormulaToBoolean(Cell cell) { + cell.setCellFormula("1=1"); + cell.setCellValue(true); + cell.setCellType(Cell.CELL_TYPE_BOOLEAN); + if (cell.getBooleanCellValue() == false) { + throw new AssertionFailedError("Identified bug 46479d"); + } + assertEquals(true, cell.getBooleanCellValue()); + } + +} \ No newline at end of file -- 2.39.5