diff options
author | Dominik Stadler <centic@apache.org> | 2017-09-19 19:02:22 +0000 |
---|---|---|
committer | Dominik Stadler <centic@apache.org> | 2017-09-19 19:02:22 +0000 |
commit | 46fd75b1e70c0b8e36a9193cfd68c7f21ee5e76e (patch) | |
tree | d88abe7d1300855edf0d0c8247790c893a7413c9 /src/ooxml | |
parent | f3b10d5703bbb7c22b64673dcf3179279272cd57 (diff) | |
download | poi-46fd75b1e70c0b8e36a9193cfd68c7f21ee5e76e.tar.gz poi-46fd75b1e70c0b8e36a9193cfd68c7f21ee5e76e.zip |
Bug 61148: Fix calculating formula value, unfortunately we currently need to set the value twice
Also rework related tests to run more of the tests for HSSF, XSSF and SXSSF
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1808929 13f79535-47bb-0310-9956-ffa450edef68
Diffstat (limited to 'src/ooxml')
6 files changed, 53 insertions, 300 deletions
diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java index 85980817e2..088694054b 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java @@ -58,7 +58,17 @@ public final class SXSSFFormulaEvaluator extends BaseXSSFFormulaEvaluator { public static SXSSFFormulaEvaluator create(SXSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { return new SXSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder); } - + public void notifySetFormula(Cell cell) { + _bookEvaluator.notifyUpdateCell(new SXSSFEvaluationCell((SXSSFCell)cell)); + } + public void notifyDeleteCell(Cell cell) { + _bookEvaluator.notifyDeleteCell(new SXSSFEvaluationCell((SXSSFCell)cell)); + } + public void notifyUpdateCell(Cell cell) { + _bookEvaluator.notifyUpdateCell(new SXSSFEvaluationCell((SXSSFCell)cell)); + } + + /** * Turns a SXSSFCell into a SXSSFEvaluationCell */ diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java index 81b61c625e..adb951d77a 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java @@ -41,16 +41,6 @@ public abstract class BaseXSSFFormulaEvaluator extends BaseFormulaEvaluator { return new XSSFRichTextString(str); } - public void notifySetFormula(Cell cell) { - _bookEvaluator.notifyUpdateCell(new XSSFEvaluationCell((XSSFCell)cell)); - } - public void notifyDeleteCell(Cell cell) { - _bookEvaluator.notifyDeleteCell(new XSSFEvaluationCell((XSSFCell)cell)); - } - public void notifyUpdateCell(Cell cell) { - _bookEvaluator.notifyUpdateCell(new XSSFEvaluationCell((XSSFCell)cell)); - } - /** * Turns a XSSFCell / SXSSFCell into a XSSFEvaluationCell */ diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java index e135b61e65..017056debb 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java @@ -23,8 +23,6 @@ import org.apache.poi.ss.formula.IStabilityClassifier; import org.apache.poi.ss.formula.WorkbookEvaluator; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Cell; -import org.apache.poi.ss.usermodel.CellType; -import org.apache.poi.ss.usermodel.CellValue; /** * Evaluates formula cells.<p> @@ -57,6 +55,16 @@ public final class XSSFFormulaEvaluator extends BaseXSSFFormulaEvaluator { return new XSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder); } + public void notifySetFormula(Cell cell) { + _bookEvaluator.notifyUpdateCell(new XSSFEvaluationCell((XSSFCell)cell)); + } + public void notifyDeleteCell(Cell cell) { + _bookEvaluator.notifyDeleteCell(new XSSFEvaluationCell((XSSFCell)cell)); + } + public void notifyUpdateCell(Cell cell) { + _bookEvaluator.notifyUpdateCell(new XSSFEvaluationCell((XSSFCell)cell)); + } + /** * Loops over all cells in all sheets of the supplied * workbook. 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 29ef231173..08ea882f8c 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFCell.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFCell.java @@ -34,6 +34,7 @@ import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.xmlbeans.XmlCursor; import org.junit.AfterClass; +import org.junit.Assume; import org.junit.Test; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst; @@ -82,4 +83,12 @@ public class TestSXSSFCell extends BaseTestXCell { swb.close(); } } + + @Test + public void testSetErrorValeFormula() throws IOException { + Assume.assumeTrue("This test is disabled because it fails for SXSSF because " + + "handling of errors in formulas is slightly different than in XSSF, " + + "but this proved to be non-trivial to solve...", + false); + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java b/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java index ff3ee87b38..c1ba0081aa 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java @@ -24,10 +24,12 @@ import static org.junit.Assert.fail; import java.io.IOException; +import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.xssf.SXSSFITestDataProvider; import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.junit.Assume; import org.junit.Test; /** @@ -37,8 +39,11 @@ import org.junit.Test; * cell is in the current window, and all references * from the cell are in the current window */ -public final class TestSXSSFFormulaEvaluation { - public static final SXSSFITestDataProvider _testDataProvider = SXSSFITestDataProvider.instance; +public final class TestSXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { + + public TestSXSSFFormulaEvaluation() { + super(SXSSFITestDataProvider.instance); + } /** * EvaluateAll will normally fail, as any reference or @@ -83,7 +88,9 @@ public final class TestSXSSFFormulaEvaluation { try { eval.evaluateAll(); fail("Evaluate All shouldn't work, as sheets flushed"); - } catch (SXSSFFormulaEvaluator.SheetsFlushedException e) {} + } catch (SXSSFFormulaEvaluator.SheetsFlushedException e) { + // expected here + } wb.close(); } @@ -118,7 +125,6 @@ public final class TestSXSSFFormulaEvaluation { /** * If all formula cells + their references are inside the window, * then evaluation works - * @throws IOException */ @Test public void testEvaluateAllInWindow() throws IOException { @@ -178,4 +184,11 @@ public final class TestSXSSFFormulaEvaluation { wb.close(); } + @Test + public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() throws IOException { + Assume.assumeTrue("This test is disabled because it fails for SXSSF because " + + "handling of errors in formulas is slightly different than in XSSF, " + + "but this proved to be non-trivial to solve...", + false); + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java index c31b236380..e4f47be461 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java @@ -26,9 +26,7 @@ import java.util.Map; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator; import org.apache.poi.ss.usermodel.Cell; -import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.CellValue; -import org.apache.poi.ss.usermodel.FormulaError; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; @@ -86,7 +84,7 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("50096.xlsx"); XSSFFormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - /** + /* * The first row simply contains the numbers 1 - 300. * The second row simply refers to the cell value above in the first row by a simple formula. */ @@ -214,32 +212,29 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { } // Link and re-try - Workbook alt = new XSSFWorkbook(); - try { + try (Workbook alt = new XSSFWorkbook()) { alt.createSheet().createRow(0).createCell(0).setCellValue("In another workbook"); // TODO Implement the rest of this, see bug #57184 /* wb.linkExternalWorkbook("alt.xlsx", alt); - + cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1"); // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ? assertEquals("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula()); - + // Evaluate it, without a link to that workbook try { evaluator.evaluate(cXSLX_nw_cell); fail("No cached value and no link to workbook, shouldn't evaluate"); } catch(Exception e) {} - + // Add a link, check it does evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator()); evaluator.setupReferencedWorkbooks(evaluators); - + evaluator.evaluate(cXSLX_nw_cell); assertEquals("In another workbook", cXSLX_nw_cell.getStringCellValue()); */ - } finally { - alt.close(); } wb.close(); @@ -375,253 +370,6 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { wb2.close(); wb1.close(); } - - @Test - public void testMultisheetFormulaEval() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet1 = wb.createSheet("Sheet1"); - XSSFSheet sheet2 = wb.createSheet("Sheet2"); - XSSFSheet sheet3 = wb.createSheet("Sheet3"); - - // sheet1 A1 - XSSFCell cell = sheet1.createRow(0).createCell(0); - cell.setCellType(CellType.NUMERIC); - cell.setCellValue(1.0); - - // sheet2 A1 - cell = sheet2.createRow(0).createCell(0); - cell.setCellType(CellType.NUMERIC); - cell.setCellValue(1.0); - - // sheet2 B1 - cell = sheet2.getRow(0).createCell(1); - cell.setCellType(CellType.NUMERIC); - cell.setCellValue(1.0); - - // sheet3 A1 - cell = sheet3.createRow(0).createCell(0); - cell.setCellType(CellType.NUMERIC); - cell.setCellValue(1.0); - - // 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(); - - cell = sheet1.getRow(1).getCell(0); - assertEquals(3.0, cell.getNumericCellValue(), 0); - - cell = sheet1.getRow(2).getCell(0); - assertEquals(4.0, cell.getNumericCellValue(), 0); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - cellA2.setCellFormula("IF(B1=0,\"\",((ROW()-ROW(A$1))*12))"); - CellValue evaluate = formulaEvaluator.evaluate(cellA2); - assertEquals("12.0", evaluate.formatAsString()); - - cellA2.setCellFormula("IF(NOT(B1=0),((ROW()-ROW(A$1))*12),\"\")"); - CellValue evaluateN = formulaEvaluator.evaluate(cellA2); - - assertEquals(evaluate.toString(), evaluateN.toString()); - assertEquals("12.0", evaluateN.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843a() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - cellA2.setCellFormula("IF(B1=0,\"\",((ROW(A$1))))"); - CellValue evaluate = formulaEvaluator.evaluate(cellA2); - assertEquals("1.0", evaluate.formatAsString()); - - cellA2.setCellFormula("IF(NOT(B1=0),((ROW(A$1))),\"\")"); - CellValue evaluateN = formulaEvaluator.evaluate(cellA2); - - assertEquals(evaluate.toString(), evaluateN.toString()); - assertEquals("1.0", evaluateN.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843b() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - - cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))"); - CellValue evaluate = formulaEvaluator.evaluate(cellA2); - assertEquals("2.0", evaluate.formatAsString()); - - cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")"); - CellValue evaluateN = formulaEvaluator.evaluate(cellA2); - - assertEquals(evaluate.toString(), evaluateN.toString()); - assertEquals("2.0", evaluateN.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843c() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - - cellA2.setCellFormula("IF(NOT(B1=0),((ROW())))"); - CellValue evaluateN = formulaEvaluator.evaluate(cellA2); - assertEquals("2.0", evaluateN.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843d() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - - cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")"); - CellValue evaluateN = formulaEvaluator.evaluate(cellA2); - assertEquals("2.0", evaluateN.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843e() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - - cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))"); - CellValue evaluate = formulaEvaluator.evaluate(cellA2); - assertEquals("2.0", evaluate.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug55843f() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - try { - XSSFSheet sheet = wb.createSheet("test"); - XSSFRow row = sheet.createRow(0); - XSSFRow row2 = sheet.createRow(1); - XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA); - XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC); - cellB1.setCellValue(10); - XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - - cellA2.setCellFormula("IF(B1=0,\"\",IF(B1=10,3,4))"); - CellValue evaluate = formulaEvaluator.evaluate(cellA2); - assertEquals("3.0", evaluate.formatAsString()); - } finally { - wb.close(); - } - } - - @Test - public void testBug56655() throws IOException { - Workbook wb = new XSSFWorkbook(); - Sheet sheet = wb.createSheet(); - - setCellFormula(sheet, 0, 0, "#VALUE!"); - setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)"); - - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - - assertEquals(CellType.ERROR, getCell(sheet, 0,0).getCachedFormulaResultType()); - assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,0).getErrorCellValue()); - assertEquals(CellType.ERROR, getCell(sheet, 0,1).getCachedFormulaResultType()); - assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,1).getErrorCellValue()); - - wb.close(); - } - - @Test - public void testBug56655a() throws IOException { - Workbook wb = new XSSFWorkbook(); - Sheet sheet = wb.createSheet(); - - setCellFormula(sheet, 0, 0, "B1*C1"); - sheet.getRow(0).createCell(1).setCellValue("A"); - setCellFormula(sheet, 1, 0, "B1*C1"); - sheet.getRow(1).createCell(1).setCellValue("A"); - setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)"); - - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - - assertEquals(CellType.ERROR, getCell(sheet, 0, 0).getCachedFormulaResultType()); - assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue()); - assertEquals(CellType.ERROR, getCell(sheet, 1, 0).getCachedFormulaResultType()); - assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 1, 0).getErrorCellValue()); - assertEquals(CellType.ERROR, getCell(sheet, 0, 3).getCachedFormulaResultType()); - assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 3).getErrorCellValue()); - - wb.close(); - } // bug 57721 @Test @@ -643,31 +391,6 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { wb.close(); } - /** - * @param row 0-based - * @param column 0-based - */ - private void setCellFormula(Sheet sheet, int row, int column, String formula) { - Row r = sheet.getRow(row); - if (r == null) { - r = sheet.createRow(row); - } - Cell cell = r.getCell(column); - if (cell == null) { - cell = r.createCell(column); - } - cell.setCellType(CellType.FORMULA); - cell.setCellFormula(formula); - } - - /** - * @param rowNo 0-based - * @param column 0-based - */ - private Cell getCell(Sheet sheet, int rowNo, int column) { - return sheet.getRow(rowNo).getCell(column); - } - @Test public void test59736() { Workbook wb = XSSFTestDataSamples.openSampleWorkbook("59736.xlsx"); |