From 39d963aa3335c858ef77b4ad0eef27bdd092c88c Mon Sep 17 00:00:00 2001 From: Vladislav Galas Date: Sat, 26 Jan 2019 19:41:25 +0000 Subject: [PATCH] made call Cell.setCellType(CellType.FORMULA) illegall. Deprecated Cell.setCellType(). Purged all redundant calls from project. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1852246 13f79535-47bb-0310-9956-ffa450edef68 --- .../hssf/usermodel/examples/CellTypes.java | 4 +-- .../usermodel/examples/HyperlinkFormula.java | 2 -- .../apache/poi/hssf/usermodel/HSSFCell.java | 2 -- .../org/apache/poi/ss/usermodel/Cell.java | 13 ++++++-- .../org/apache/poi/ss/usermodel/CellBase.java | 9 ++++++ .../apache/poi/ss/usermodel/RangeCopier.java | 6 ++-- .../poi/xssf/usermodel/XSSFPivotTable.java | 5 --- .../apache/poi/xssf/usermodel/XSSFRow.java | 28 ++++++++++++++-- .../poi/ss/formula/functions/TestProper.java | 1 - .../xssf/extractor/TestXSSFExportToXML.java | 12 +++---- .../poi/xssf/streaming/TestSXSSFCell.java | 5 +++ .../poi/xssf/usermodel/TestXSSFBugs.java | 2 +- .../hssf/usermodel/TestHSSFDataFormatter.java | 1 - .../functions/TestCalendarFieldFunction.java | 1 - .../poi/ss/formula/functions/TestDate.java | 1 - .../poi/ss/formula/functions/TestFixed.java | 1 - .../ss/usermodel/BaseTestBugzillaIssues.java | 3 +- .../apache/poi/ss/usermodel/BaseTestCell.java | 32 +++++++++++++++++++ .../usermodel/BaseTestFormulaEvaluator.java | 3 -- 19 files changed, 92 insertions(+), 39 deletions(-) diff --git a/src/examples/src/org/apache/poi/hssf/usermodel/examples/CellTypes.java b/src/examples/src/org/apache/poi/hssf/usermodel/examples/CellTypes.java index 51dc89cc5d..f589bc6ad0 100644 --- a/src/examples/src/org/apache/poi/hssf/usermodel/examples/CellTypes.java +++ b/src/examples/src/org/apache/poi/hssf/usermodel/examples/CellTypes.java @@ -24,7 +24,7 @@ import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.usermodel.FormulaError; public class CellTypes { public static void main(String[] args) throws IOException { @@ -35,7 +35,7 @@ public class CellTypes { row.createCell(1).setCellValue(new Date()); row.createCell(2).setCellValue("a string"); row.createCell(3).setCellValue(true); - row.createCell(4).setCellType(CellType.ERROR); + row.createCell(4).setCellErrorValue(FormulaError.NUM); // Write the output to a file try (FileOutputStream fileOut = new FileOutputStream("workbook.xls")) { diff --git a/src/examples/src/org/apache/poi/hssf/usermodel/examples/HyperlinkFormula.java b/src/examples/src/org/apache/poi/hssf/usermodel/examples/HyperlinkFormula.java index 5d4b18a3c2..52294360c6 100644 --- a/src/examples/src/org/apache/poi/hssf/usermodel/examples/HyperlinkFormula.java +++ b/src/examples/src/org/apache/poi/hssf/usermodel/examples/HyperlinkFormula.java @@ -24,7 +24,6 @@ import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.CellType; /** * Test if hyperlink formula, with url that got more than 127 characters, works @@ -36,7 +35,6 @@ public class HyperlinkFormula { HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); - cell.setCellType(CellType.FORMULA); cell.setCellFormula("HYPERLINK(\"http://127.0.0.1:8080/toto/truc/index.html?test=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\", \"test\")"); try (FileOutputStream fileOut = new FileOutputStream("workbook.xls")) { diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index eb92d5bddb..7926cbdb1a 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -292,8 +292,6 @@ public class HSSFCell extends CellBase { } if (getCellType() == CellType.BLANK) { frec.getFormulaRecord().setValue(0); - } else if (setValue) { - frec.getFormulaRecord().setValue(getNumericCellValue()); } frec.setXFIndex(styleIndex); _record = frec; diff --git a/src/java/org/apache/poi/ss/usermodel/Cell.java b/src/java/org/apache/poi/ss/usermodel/Cell.java index 21e701490e..4f344ba8e9 100644 --- a/src/java/org/apache/poi/ss/usermodel/Cell.java +++ b/src/java/org/apache/poi/ss/usermodel/Cell.java @@ -78,15 +78,22 @@ public interface Cell { *

to NUMERIC: numeric value is left as is. True converts to 1.0, false converts to 0. otherwise, the * value is set to 0. Formula is removed.

*

If what you want to do is get a String value for your - * numeric cell, stop!. This is not the way to do it. + * numeric cell, stop! This is not the way to do it. * Instead, for fetching the string value of a numeric or boolean * or date cell, use {@link DataFormatter} instead.

*

If cell is a member of an array formula group containing more than 1 cell, an {@link IllegalStateException} - * is thrown. If the array formula group contains only this cell, it is removed

- * @throws IllegalArgumentException if the specified cell type is invalid (null or _NONE) + * is thrown. If the array formula group contains only this cell, it is removed.

+ *

Passing {@link CellType#FORMULA} is illegal and will result in an {@link IllegalArgumentException}.

+ * + * @deprecated This method is deprecated and will be removed in POI 5.0. + * Use explicit {@link #setCellFormula(String)}, setCellValue(...) or {@link #setBlank()} + * to get the desired result. + * @throws IllegalArgumentException if the specified cell type is invalid (null, _NONE or FORMULA) * @throws IllegalStateException if the current value cannot be converted to the new type or * if the cell is a part of an array formula group containing other cells */ + @Deprecated + @Removal(version = "5.0") void setCellType(CellType cellType); /** diff --git a/src/java/org/apache/poi/ss/usermodel/CellBase.java b/src/java/org/apache/poi/ss/usermodel/CellBase.java index 9777739fcf..7a430257ef 100644 --- a/src/java/org/apache/poi/ss/usermodel/CellBase.java +++ b/src/java/org/apache/poi/ss/usermodel/CellBase.java @@ -35,6 +35,15 @@ public abstract class CellBase implements Cell { throw new IllegalArgumentException("cellType shall not be null nor _NONE"); } + if (cellType == CellType.FORMULA) { + if (getCellType() != CellType.FORMULA){ + throw new IllegalArgumentException("Calling Cell.setCellType(CellType.FORMULA) is illegal. " + + "Use setCellFormula(String) directly."); + } else { + return; + } + } + tryToDeleteArrayFormulaIfSet(); setCellTypeImpl(cellType); diff --git a/src/java/org/apache/poi/ss/usermodel/RangeCopier.java b/src/java/org/apache/poi/ss/usermodel/RangeCopier.java index 162100240f..6c73530b5c 100644 --- a/src/java/org/apache/poi/ss/usermodel/RangeCopier.java +++ b/src/java/org/apache/poi/ss/usermodel/RangeCopier.java @@ -94,9 +94,9 @@ public abstract class RangeCopier { destRow = destSheet.createRow(rowNo + deltaY); Cell newCell = destRow.getCell(columnIndex + deltaX); - if(newCell != null) - newCell.setCellType(sourceCell.getCellType()); - else newCell = destRow.createCell(columnIndex + deltaX, sourceCell.getCellType()); + if(newCell == null) { + newCell = destRow.createCell(columnIndex + deltaX); + } cloneCellContent(sourceCell, newCell, null); if(newCell.getCellType() == CellType.FORMULA) diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java index 6b77b91eff..8ace06e95a 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java @@ -425,8 +425,6 @@ public class XSSFPivotTable extends POIXMLDocumentPart { private void addDataField(DataConsolidateFunction function, int columnIndex, String valueFieldName, String valueFormat) { checkColumnIndex(columnIndex); - AreaReference pivotArea = getPivotArea(); - CTDataFields dataFields; if(pivotTableDefinition.getDataFields() != null) { dataFields = pivotTableDefinition.getDataFields(); @@ -435,9 +433,6 @@ public class XSSFPivotTable extends POIXMLDocumentPart { } CTDataField dataField = dataFields.addNewDataField(); dataField.setSubtotal(STDataConsolidateFunction.Enum.forInt(function.getValue())); - Cell cell = getDataSheet().getRow(pivotArea.getFirstCell().getRow()) - .getCell(pivotArea.getFirstCell().getCol() + columnIndex); - cell.setCellType(CellType.STRING); dataField.setName(valueFieldName); dataField.setFld(columnIndex); if (valueFormat != null && !valueFormat.trim().isEmpty()) { diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java index e7671c5968..57df559f95 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java @@ -28,6 +28,7 @@ import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.usermodel.FormulaError; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.helpers.RowShifter; import org.apache.poi.ss.util.CellRangeAddress; @@ -220,12 +221,33 @@ public class XSSFRow implements Row, Comparable { } XSSFCell xcell = new XSSFCell(this, ctCell); xcell.setCellNum(columnIndex); - if (type != CellType.BLANK) { - xcell.setCellType(type); + if (type != CellType.BLANK && type != CellType.FORMULA) { + setDefaultValue(xcell, type); } + _cells.put(colI, xcell); return xcell; } + + private static void setDefaultValue(XSSFCell cell, CellType type) { + switch (type) { + case NUMERIC: + cell.setCellValue(0); + break; + case STRING: + cell.setCellValue(""); + break; + case BOOLEAN: + cell.setCellValue(false); + break; + case ERROR: + cell.setCellErrorValue(FormulaError._NO_ERROR); + break; + default: + throw new AssertionError(); + } + } + /** * Returns the cell at the given (0 based) index, * with the {@link org.apache.poi.ss.usermodel.Row.MissingCellPolicy} from the parent Workbook. @@ -588,7 +610,7 @@ public class XSSFRow implements Row, Comparable { else { for (final Cell c : srcRow){ final XSSFCell srcCell = (XSSFCell)c; - final XSSFCell destCell = createCell(srcCell.getColumnIndex(), srcCell.getCellType()); + final XSSFCell destCell = createCell(srcCell.getColumnIndex()); destCell.copyCellFrom(srcCell, policy); } diff --git a/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestProper.java b/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestProper.java index a56a8682ff..2296b6a4a9 100644 --- a/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestProper.java +++ b/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestProper.java @@ -59,7 +59,6 @@ public final class TestProper { private void confirm(Workbook wb) { Sheet sheet = wb.createSheet("new sheet"); cell11 = sheet.createRow(0).createCell(0); - cell11.setCellType(CellType.FORMULA); confirm("PROPER(\"hi there\")", "Hi There"); //simple case confirm("PROPER(\"what's up\")", "What'S Up"); //apostrophes are treated as word breaks diff --git a/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java b/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java index eda6dc0ac3..46f01eb850 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java @@ -537,20 +537,16 @@ public final class TestXSSFExportToXML { Cell cFormulaString = row.createCell(3); cFormulaString.setCellFormula("A1"); - cFormulaString.setCellType(CellType.FORMULA); - + Cell cFormulaNumeric = row.createCell(4); cFormulaNumeric.setCellFormula("F1"); - cFormulaNumeric.setCellType(CellType.FORMULA); - + Cell cNumeric = row.createCell(5); cNumeric.setCellValue(1.2); - cNumeric.setCellType(CellType.NUMERIC); - + Cell cDate = row.createCell(6); cDate.setCellValue(new Date()); - cDate.setCellType(CellType.NUMERIC); - + boolean found = false; for (POIXMLDocumentPart p : wb.getRelations()) { diff --git a/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFCell.java index e696378667..c834cd528b 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFCell.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFCell.java @@ -187,4 +187,9 @@ public class TestSXSSFCell extends BaseTestXCell { @Ignore public void setCellFormula_isExceptionSafe_onBlankCell() { } + + @Test + @Ignore + public void setCellType_FORMULA_onAnArrayFormulaCell_doesNothing() { + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index 7ed41f9c6d..99d95e1b5b 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -626,7 +626,7 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { // Otherwise should go sheet.getRow(1).getCell(0).setCellFormula("A1"); // stay sheet.getRow(2).getCell(0).setCellFormula(null); // go - sheet.getRow(3).getCell(0).setCellType(CellType.FORMULA); // stay + sheet.getRow(3).getCell(0).setCellFormula("14"); // stay XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); sheet.getRow(4).getCell(0).setCellType(CellType.STRING); // go diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java index 1ed7dd3a5d..ed72f82a8f 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java @@ -217,7 +217,6 @@ public final class TestHSSFDataFormatter { { // formula cell row = sheet.createRow(7); HSSFCell cell = row.createCell(0); - cell.setCellType(CellType.FORMULA); cell.setCellFormula("SUM(12.25,12.25)/100"); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(format.getFormat("##.00%;")); diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java b/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java index e9af8c12ee..9a7f194a93 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java @@ -40,7 +40,6 @@ public final class TestCalendarFieldFunction extends TestCase { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); cell11 = sheet.createRow(0).createCell(0); - cell11.setCellType(CellType.FORMULA); evaluator = new HSSFFormulaEvaluator(wb); } diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestDate.java b/src/testcases/org/apache/poi/ss/formula/functions/TestDate.java index b9f5224c8d..131df38d6d 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestDate.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestDate.java @@ -40,7 +40,6 @@ public final class TestDate extends TestCase { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); cell11 = sheet.createRow(0).createCell(0); - cell11.setCellType(CellType.FORMULA); evaluator = new HSSFFormulaEvaluator(wb); } diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java b/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java index e14ea3c45b..6cc50e742b 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java @@ -48,7 +48,6 @@ public final class TestFixed { try { HSSFSheet sheet = wb.createSheet("new sheet"); cell11 = sheet.createRow(0).createCell(0); - cell11.setCellType(CellType.FORMULA); evaluator = new HSSFFormulaEvaluator(wb); } finally { wb.close(); diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java index c924a85674..ae389c05b2 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java @@ -1218,8 +1218,7 @@ public abstract class BaseTestBugzillaIssues { value == null || value.length() == 0); cell = row.createCell(1); - // also verify that setting formulas to null works - cell.setCellType(CellType.FORMULA); + cell.setCellFormula("0"); cell.setCellValue((String)null); wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java index 3190c62f03..1e2b4d4f96 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java @@ -1358,6 +1358,38 @@ public abstract class BaseTestCell { assertTrue(cell.getBooleanCellValue()); } + @Test(expected = IllegalArgumentException.class) + public void setCellType_FORMULA_onANonFormulaCell_throwsIllegalArgumentException() { + Cell cell = getInstance(); + cell.setCellType(CellType.FORMULA); + } + + @Test + public void setCellType_FORMULA_onAFormulaCell_doesNothing() { + Cell cell = getInstance(); + cell.setCellFormula("3"); + cell.setCellValue("foo"); + + cell.setCellType(CellType.FORMULA); + + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals(CellType.STRING, cell.getCachedFormulaResultType()); + assertEquals("foo", cell.getStringCellValue()); + } + + @Test + public void setCellType_FORMULA_onAnArrayFormulaCell_doesNothing() { + Cell cell = getInstance(); + cell.getSheet().setArrayFormula("3", CellRangeAddress.valueOf("A1:A2")); + cell.setCellValue("foo"); + + cell.setCellType(CellType.FORMULA); + + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals(CellType.STRING, cell.getCachedFormulaResultType()); + assertEquals("foo", cell.getStringCellValue()); + } + @Test public final void setBlank_delegatesTo_setCellType_BLANK() { Cell cell = mock(CellBase.class); diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java index 309da9472c..4712a85536 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java @@ -384,12 +384,10 @@ public abstract class BaseTestFormulaEvaluator { // sheet1 A2 formulae cell = sheet1.createRow(1).createCell(0); - cell.setCellType(CellType.FORMULA); cell.setCellFormula("SUM(Sheet1:Sheet3!A1)"); // sheet1 A3 formulae cell = sheet1.createRow(2).createCell(0); - cell.setCellType(CellType.FORMULA); cell.setCellFormula("SUM(Sheet1:Sheet3!A1:B1)"); wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); @@ -589,7 +587,6 @@ public abstract class BaseTestFormulaEvaluator { if (cell == null) { cell = r.createCell(column); } - cell.setCellType(CellType.FORMULA); cell.setCellFormula(formula); } -- 2.39.5