From e99294ebc57615f13f6ff10cc6cf82a65a4de2a2 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sat, 4 Sep 2010 15:03:48 +0000 Subject: [PATCH] fixed XSSFFormulaEvaluator to support blank cells, added BaseTestFormulaEvaluator - a common superclass for testing implementations of FormulaEvaluator git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@992620 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../hssf/usermodel/HSSFFormulaEvaluator.java | 3 + .../poi/ss/usermodel/FormulaEvaluator.java | 9 + .../xssf/usermodel/XSSFFormulaEvaluator.java | 5 + .../usermodel/TestXSSFFormulaEvaluation.java | 134 +--------- .../usermodel/TestHSSFFormulaEvaluator.java | 66 +---- .../usermodel/BaseTestFormulaEvaluator.java | 246 ++++++++++++++++++ test-data/spreadsheet/shared_formulas.xls | Bin 0 -> 22528 bytes 8 files changed, 282 insertions(+), 182 deletions(-) create mode 100644 src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java create mode 100644 test-data/spreadsheet/shared_formulas.xls diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 7afde36539..c0ce8db185 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 49873 - fixed XSSFFormulaEvaluator to support blank cells 49850 - added a getter for _iStartAt in ListFormatOverrideLevel 49761 - change cell type to error when setting Double.NaN or Infinities 49833 - ensure that CTNumPr is included in poi-ooxml-schemas.jar diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index 16b5a5a0dc..4242602c66 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -132,6 +132,9 @@ public class HSSFFormulaEvaluator implements FormulaEvaluator { public void notifyUpdateCell(HSSFCell cell) { _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell(cell)); } + public void notifyUpdateCell(Cell cell) { + _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell((HSSFCell)cell)); + } /** * Should be called to tell the cell value cache that the specified cell has just been * deleted. diff --git a/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java b/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java index e60104bd31..3d1076fdf5 100644 --- a/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java +++ b/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java @@ -47,6 +47,15 @@ public interface FormulaEvaluator { * formula cell, or the formula text has changed */ void notifyDeleteCell(Cell cell); + + /** + * Should be called to tell the cell value cache that the specified (value or formula) cell + * has changed. + * Failure to call this method after changing cell values will cause incorrect behaviour + * of the evaluate~ methods of this class + */ + void notifyUpdateCell(Cell cell); + /** * If cell contains a formula, the formula is evaluated and returned, * else the CellValue simply copies the appropriate cell value from 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 be0fa5a6fb..f6f89967d0 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java @@ -90,6 +90,9 @@ public class XSSFFormulaEvaluator implements FormulaEvaluator { public void notifyDeleteCell(Cell cell) { _bookEvaluator.notifyDeleteCell(new XSSFEvaluationCell((XSSFCell)cell)); } + public void notifyUpdateCell(Cell cell) { + _bookEvaluator.notifyUpdateCell(new XSSFEvaluationCell((XSSFCell)cell)); + } /** * If cell contains a formula, the formula is evaluated and returned, @@ -115,6 +118,8 @@ public class XSSFFormulaEvaluator implements FormulaEvaluator { return new CellValue(cell.getNumericCellValue()); case XSSFCell.CELL_TYPE_STRING: return new CellValue(cell.getRichStringCellValue().getString()); + case XSSFCell.CELL_TYPE_BLANK: + return null; } throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")"); } 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 916e29ca0b..708a6d904b 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java @@ -21,139 +21,15 @@ import junit.framework.TestCase; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.XSSFTestDataSamples; +import org.apache.poi.xssf.XSSFITestDataProvider; -public final class TestXSSFFormulaEvaluation extends TestCase { - @Override - protected void setUp() { - // Use system out logger - System.setProperty( - "org.apache.poi.util.POILogger", - "org.apache.poi.util.SystemOutLogger" - ); - } - - public void testSimpleArithmatic() { - XSSFWorkbook wb = new XSSFWorkbook(); - Sheet s = wb.createSheet(); - Row r = s.createRow(0); - - Cell c1 = r.createCell(0); - c1.setCellFormula("1+5"); - assertEquals(0.0, c1.getNumericCellValue(), 0.0); - - Cell c2 = r.createCell(1); - c2.setCellFormula("10/2"); - assertEquals(0.0, c2.getNumericCellValue(), 0.0); - - FormulaEvaluator fe = new XSSFFormulaEvaluator(wb); - - fe.evaluateFormulaCell(c1); - fe.evaluateFormulaCell(c2); - - assertEquals(6.0, c1.getNumericCellValue(), 0.0001); - assertEquals(5.0, c2.getNumericCellValue(), 0.0001); - } - - public void testSumCount() { - XSSFWorkbook wb = new XSSFWorkbook(); - Sheet s = wb.createSheet(); - Row r = s.createRow(0); - r.createCell(0).setCellValue(2.5); - r.createCell(1).setCellValue(1.1); - r.createCell(2).setCellValue(3.2); - r.createCell(4).setCellValue(10.7); - - r = s.createRow(1); - - Cell c1 = r.createCell(0); - c1.setCellFormula("SUM(A1:B1)"); - assertEquals(0.0, c1.getNumericCellValue(), 0.0); - - Cell c2 = r.createCell(1); - c2.setCellFormula("SUM(A1:E1)"); - assertEquals(0.0, c2.getNumericCellValue(), 0.0); - - Cell c3 = r.createCell(2); - c3.setCellFormula("COUNT(A1:A1)"); - assertEquals(0.0, c3.getNumericCellValue(), 0.0); - - Cell c4 = r.createCell(3); - c4.setCellFormula("COUNTA(A1:E1)"); - assertEquals(0.0, c4.getNumericCellValue(), 0.0); - +public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { - // Evaluate and test - FormulaEvaluator fe = new XSSFFormulaEvaluator(wb); - - fe.evaluateFormulaCell(c1); - fe.evaluateFormulaCell(c2); - fe.evaluateFormulaCell(c3); - fe.evaluateFormulaCell(c4); - - assertEquals(3.6, c1.getNumericCellValue(), 0.0001); - assertEquals(17.5, c2.getNumericCellValue(), 0.0001); - assertEquals(1, c3.getNumericCellValue(), 0.0001); - assertEquals(4, c4.getNumericCellValue(), 0.0001); + public TestXSSFFormulaEvaluation() { + super(XSSFITestDataProvider.instance); } public void testSharedFormulas(){ - XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("shared_formulas.xlsx"); - - XSSFSheet sheet = wb.getSheetAt(0); - - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - XSSFCell cell; - - cell = sheet.getRow(1).getCell(0); - assertEquals("B2", cell.getCellFormula()); - assertEquals("ProductionOrderConfirmation", evaluator.evaluate(cell).getStringValue()); - - cell = sheet.getRow(2).getCell(0); - assertEquals("B3", cell.getCellFormula()); - assertEquals("RequiredAcceptanceDate", evaluator.evaluate(cell).getStringValue()); - - cell = sheet.getRow(3).getCell(0); - assertEquals("B4", cell.getCellFormula()); - assertEquals("Header", evaluator.evaluate(cell).getStringValue()); - - cell = sheet.getRow(4).getCell(0); - assertEquals("B5", cell.getCellFormula()); - assertEquals("UniqueDocumentNumberID", evaluator.evaluate(cell).getStringValue()); - } - - /** - * Test creation / evaluation of formulas with sheet-level names - */ - public void testSheetLevelFormulas(){ - XSSFWorkbook wb = new XSSFWorkbook(); - - XSSFRow row; - XSSFSheet sh1 = wb.createSheet("Sheet1"); - XSSFName nm1 = wb.createName(); - nm1.setNameName("sales_1"); - nm1.setSheetIndex(0); - nm1.setRefersToFormula("Sheet1!$A$1"); - row = sh1.createRow(0); - row.createCell(0).setCellValue(3); - row.createCell(1).setCellFormula("sales_1"); - row.createCell(2).setCellFormula("sales_1*2"); - - XSSFSheet sh2 = wb.createSheet("Sheet2"); - XSSFName nm2 = wb.createName(); - nm2.setNameName("sales_1"); - nm2.setSheetIndex(1); - nm2.setRefersToFormula("Sheet2!$A$1"); - - row = sh2.createRow(0); - row.createCell(0).setCellValue(5); - row.createCell(1).setCellFormula("sales_1"); - row.createCell(2).setCellFormula("sales_1*3"); - - XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(wb); - assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0); - assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0); - - assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0); - assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0); + baseTestSharedFormulas("shared_formulas.xlsx"); } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java index d1b1a73b09..2d97c1be05 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java @@ -21,6 +21,7 @@ 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.record.NameRecord; import org.apache.poi.hssf.record.formula.eval.NumberEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; @@ -30,11 +31,17 @@ import org.apache.poi.ss.formula.WorkbookEvaluator; import org.apache.poi.ss.formula.WorkbookEvaluatorTestHelper; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator; + /** * * @author Josh Micich */ -public final class TestHSSFFormulaEvaluator extends TestCase { +public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator { + + public TestHSSFFormulaEvaluator() { + super(HSSFITestDataProvider.instance); + } /** * Test that the HSSFFormulaEvaluator can evaluate simple named ranges @@ -50,58 +57,6 @@ public final class TestHSSFFormulaEvaluator extends TestCase { assertEquals(3.72, cv.getNumberValue(), 0.0); } - public void testFullColumnRefs() { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet sheet = wb.createSheet("Sheet1"); - HSSFRow row = sheet.createRow(0); - HSSFCell cell0 = row.createCell(0); - cell0.setCellFormula("sum(D:D)"); - HSSFCell cell1 = row.createCell(1); - cell1.setCellFormula("sum(D:E)"); - - // some values in column D - setValue(sheet, 1, 3, 5.0); - setValue(sheet, 2, 3, 6.0); - setValue(sheet, 5, 3, 7.0); - setValue(sheet, 50, 3, 8.0); - - // some values in column E - setValue(sheet, 1, 4, 9.0); - setValue(sheet, 2, 4, 10.0); - setValue(sheet, 30000, 4, 11.0); - - // some other values - setValue(sheet, 1, 2, 100.0); - setValue(sheet, 2, 5, 100.0); - setValue(sheet, 3, 6, 100.0); - - - HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); - assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0); - assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0); - } - - private static void setValue(HSSFSheet sheet, int rowIndex, int colIndex, double value) { - HSSFRow row = sheet.getRow(rowIndex); - if (row == null) { - row = sheet.createRow(rowIndex); - } - row.createCell(colIndex).setCellValue(value); - } - - /** - * {@link HSSFFormulaEvaluator#evaluate(org.apache.poi.ss.usermodel.Cell)} should behave the same whether the cell - * is null or blank. - */ - public void testEvaluateBlank() { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); - assertNull(fe.evaluate(null)); - HSSFSheet sheet = wb.createSheet("Sheet1"); - HSSFCell cell = sheet.createRow(0).createCell(0); - assertNull(fe.evaluate(cell)); - } - /** * Test for bug due to attempt to convert a cached formula error result to a boolean */ @@ -276,4 +231,9 @@ public final class TestHSSFFormulaEvaluator extends TestCase { assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCachedFormulaResultType()); assertEquals(36.90, cell.getNumericCellValue(), 0.0001); } + + public void testSharedFormulas(){ + baseTestSharedFormulas("shared_formulas.xls"); + } + } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java new file mode 100644 index 0000000000..1cc964f037 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java @@ -0,0 +1,246 @@ +/* ==================================================================== + 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.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.ss.ITestDataProvider; + +/** + * Common superclass for testing implementatiosn of{@link FormulaEvaluator} + * + * @author Yegor Kozlov + */ +public abstract class BaseTestFormulaEvaluator extends TestCase { + + protected final ITestDataProvider _testDataProvider; + + /** + * @param testDataProvider an object that provides test data in / specific way + */ + protected BaseTestFormulaEvaluator(ITestDataProvider testDataProvider) { + _testDataProvider = testDataProvider; + } + + public void testSimpleArithmetic() { + Workbook wb = _testDataProvider.createWorkbook(); + Sheet s = wb.createSheet(); + Row r = s.createRow(0); + + Cell c1 = r.createCell(0); + c1.setCellFormula("1+5"); + assertEquals(0.0, c1.getNumericCellValue(), 0.0); + + Cell c2 = r.createCell(1); + c2.setCellFormula("10/2"); + assertEquals(0.0, c2.getNumericCellValue(), 0.0); + + FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); + + fe.evaluateFormulaCell(c1); + fe.evaluateFormulaCell(c2); + + assertEquals(6.0, c1.getNumericCellValue(), 0.0001); + assertEquals(5.0, c2.getNumericCellValue(), 0.0001); + } + + public void testSumCount() { + Workbook wb = _testDataProvider.createWorkbook(); + Sheet s = wb.createSheet(); + Row r = s.createRow(0); + r.createCell(0).setCellValue(2.5); + r.createCell(1).setCellValue(1.1); + r.createCell(2).setCellValue(3.2); + r.createCell(4).setCellValue(10.7); + + r = s.createRow(1); + + Cell c1 = r.createCell(0); + c1.setCellFormula("SUM(A1:B1)"); + assertEquals(0.0, c1.getNumericCellValue(), 0.0); + + Cell c2 = r.createCell(1); + c2.setCellFormula("SUM(A1:E1)"); + assertEquals(0.0, c2.getNumericCellValue(), 0.0); + + Cell c3 = r.createCell(2); + c3.setCellFormula("COUNT(A1:A1)"); + assertEquals(0.0, c3.getNumericCellValue(), 0.0); + + Cell c4 = r.createCell(3); + c4.setCellFormula("COUNTA(A1:E1)"); + assertEquals(0.0, c4.getNumericCellValue(), 0.0); + + + // Evaluate and test + FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); + + fe.evaluateFormulaCell(c1); + fe.evaluateFormulaCell(c2); + fe.evaluateFormulaCell(c3); + fe.evaluateFormulaCell(c4); + + assertEquals(3.6, c1.getNumericCellValue(), 0.0001); + assertEquals(17.5, c2.getNumericCellValue(), 0.0001); + assertEquals(1, c3.getNumericCellValue(), 0.0001); + assertEquals(4, c4.getNumericCellValue(), 0.0001); + } + + public void baseTestSharedFormulas(String sampleFile){ + Workbook wb = _testDataProvider.openSampleWorkbook(sampleFile); + + Sheet sheet = wb.getSheetAt(0); + + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + Cell cell; + + cell = sheet.getRow(1).getCell(0); + assertEquals("B2", cell.getCellFormula()); + assertEquals("ProductionOrderConfirmation", evaluator.evaluate(cell).getStringValue()); + + cell = sheet.getRow(2).getCell(0); + assertEquals("B3", cell.getCellFormula()); + assertEquals("RequiredAcceptanceDate", evaluator.evaluate(cell).getStringValue()); + + cell = sheet.getRow(3).getCell(0); + assertEquals("B4", cell.getCellFormula()); + assertEquals("Header", evaluator.evaluate(cell).getStringValue()); + + cell = sheet.getRow(4).getCell(0); + assertEquals("B5", cell.getCellFormula()); + assertEquals("UniqueDocumentNumberID", evaluator.evaluate(cell).getStringValue()); + } + + /** + * Test creation / evaluation of formulas with sheet-level names + */ + public void testSheetLevelFormulas(){ + Workbook wb = _testDataProvider.createWorkbook(); + + Row row; + Sheet sh1 = wb.createSheet("Sheet1"); + Name nm1 = wb.createName(); + nm1.setNameName("sales_1"); + nm1.setSheetIndex(0); + nm1.setRefersToFormula("Sheet1!$A$1"); + row = sh1.createRow(0); + row.createCell(0).setCellValue(3); + row.createCell(1).setCellFormula("sales_1"); + row.createCell(2).setCellFormula("sales_1*2"); + + Sheet sh2 = wb.createSheet("Sheet2"); + Name nm2 = wb.createName(); + nm2.setNameName("sales_1"); + nm2.setSheetIndex(1); + nm2.setRefersToFormula("Sheet2!$A$1"); + + row = sh2.createRow(0); + row.createCell(0).setCellValue(5); + row.createCell(1).setCellFormula("sales_1"); + row.createCell(2).setCellFormula("sales_1*3"); + + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0); + assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0); + + assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0); + assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0); + } + + public void testFullColumnRefs() { + Workbook wb = _testDataProvider.createWorkbook(); + Sheet sheet = wb.createSheet("Sheet1"); + Row row = sheet.createRow(0); + Cell cell0 = row.createCell(0); + cell0.setCellFormula("sum(D:D)"); + Cell cell1 = row.createCell(1); + cell1.setCellFormula("sum(D:E)"); + + // some values in column D + setValue(sheet, 1, 3, 5.0); + setValue(sheet, 2, 3, 6.0); + setValue(sheet, 5, 3, 7.0); + setValue(sheet, 50, 3, 8.0); + + // some values in column E + setValue(sheet, 1, 4, 9.0); + setValue(sheet, 2, 4, 10.0); + setValue(sheet, 30000, 4, 11.0); + + // some other values + setValue(sheet, 1, 2, 100.0); + setValue(sheet, 2, 5, 100.0); + setValue(sheet, 3, 6, 100.0); + + + FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); + assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0); + assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0); + } + + private static void setValue(Sheet sheet, int rowIndex, int colIndex, double value) { + Row row = sheet.getRow(rowIndex); + if (row == null) { + row = sheet.createRow(rowIndex); + } + row.createCell(colIndex).setCellValue(value); + } + + /** + * {@link FormulaEvaluator#evaluate(org.apache.poi.ss.usermodel.Cell)} should behave the same whether the cell + * is null or blank. + */ + public void testEvaluateBlank() { + Workbook wb = _testDataProvider.createWorkbook(); + FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); + assertNull(fe.evaluate(null)); + Sheet sheet = wb.createSheet("Sheet1"); + Cell cell = sheet.createRow(0).createCell(0); + assertNull(fe.evaluate(cell)); + } + + /** + * Test for bug due to attempt to convert a cached formula error result to a boolean + */ + public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() { + + Workbook wb = _testDataProvider.createWorkbook(); + Sheet sheet = wb.createSheet("Sheet1"); + Row row = sheet.createRow(0); + Cell cellA1 = row.createCell(0); + Cell cellB1 = row.createCell(1); + cellB1.setCellFormula("A1+1"); + FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); + + cellA1.setCellErrorValue((byte)ErrorConstants.ERROR_NAME); + fe.evaluateFormulaCell(cellB1); + + cellA1.setCellValue(2.5); + fe.notifyUpdateCell(cellA1); + try { + fe.evaluateInCell(cellB1); + } catch (IllegalStateException e) { + if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) { + throw new AssertionFailedError("Identified bug 46479a"); + } + } + assertEquals(3.5, cellB1.getNumericCellValue(), 0.0); + } + +} \ No newline at end of file diff --git a/test-data/spreadsheet/shared_formulas.xls b/test-data/spreadsheet/shared_formulas.xls new file mode 100644 index 0000000000000000000000000000000000000000..b71006ceff13ba4c9362a172281934bee0f2aa23 GIT binary patch literal 22528 zcmeHP3v650c|N2liLx9^vgNm9)ACD}ACV|qw)3JNa#APqqmHtqs0xLaE^W?|s8Ez^ z4>h8!>rgaVm)e=L#opSk35KOj+B~*&ZMN9J+Gd#21w(?YnbEnM7o=#hHeQACWeqdDHFEV)L&=xzz2tKpo7%X0Sngfq9ZN~ow`>lpMBg3 z;+kslM90gu=O#j;2A%&$A}qcUIfr*4{vEkbjI!^4%sQCK#%|C2&QRt^?EOyL zr4GpubvO%K-Gr-Z@vTq;HR(N+9=j`(J$xXOIeZ2Mn-FGL5YB-2S@QkslJy1oeiOd= zIhwNX`deEzqXcVzcX+ET_XqO5MZV9#dq_MldESte4g{~4P(FX(Lu|0H`2Fe-9VHFt z7!n-_W#>VTio&;TwV@sIn=~AnrG7mns6z-kyuj*(MVnWLIpR52!>Z4=RfXD*&q%%c zXo>C`-(qnHuDe+{sJ8CI1<=Lft6r zFQuUsif$5ZA}&NfRcVVkniX#QS|y=x^$i6=%eofm)!(|cA5zwf)p&tbg_ zM?$O>*L#K8HSo%lswpB930G*BSc9JA3U&C4avf$V+U2|;r&B`wPdd~lxjp+DzC|2rT0=X~gY<3pdSK7S=~xLESq$MzF0 znk4<gAE_6SQ?fi4Q8qo5dU45%OGM;(Nv}l=k@WvX0hw(7uSGaB)7SdY+l+KB z+9b|91^7b$;(|}oi~eu>GZ%%D{;JrAORVK=eYj9m=vXt_bS@qX^cXI&cGT#aTrMtU z`J#Pn|K~!pfO8Knv97dnxFD7EqMlzZ*sVZ6)6_Jleh%#XsHAh@>fqaUw(Vbwaaqze zJ98l{>6)Hg=t}ylnDmL8i*{&s=vsMByk(prpiF0DpNck%PZjS;Oo;DvHeNg)T_=uv z=r`cIFc#p=aN*MIm-cW%=gN(CehKrQxXsTyA*A zjTe~aaX=8BBycGRhcXwWL^~VAN~zkFf^Zxw3o_Xg41%Sg0)iV;b&4_Gr#j0j0ipBA zAk?*6-);Kpo|V2~A+fG|`BRfp4UR}){G1cUGaM_G``o?sBn92F4Um~9da zva%8o`>Bq96LacXwmMdd6H|W>KJY0EGT9Rh67zuI#%z;dkX4m{*iX6qtHb3{+3Hv+ zu8RCY_yDXd$Yf72$WjjoZp=1OK~9Ee;z1o&qVe_>v30R+0|VAbuH!^>w>Ux56;dd& zIDZZH)!Oj@E+*C?Z^J{00bxIquon^`Y`xb;tkn58-_BR_nmq=C{R)KRFvT?KV&bJ@ zS^~t_OARTe+DctI|F32-Gj%bYrD9eFh_M$VQcR7N`pMfrG>e&~i`h^rrY%4Wmn&6R z?!i#CR_fxv{nRX`Sr@ahRLuGSF&zr@})5RD(ZB>96 z)zc<>{^jBeW-;@1F$Pat6Cg(Qw8@^yvu~KiEYQUmJngywF{-Cc_B{W?x6EP|>S7F@ z))^p1^|Z;J_g;I>EM^hKbOn1_3}U(}@U(d6A3giJErtONsTk9))x{V*ZFztg)zjjg zZ=8DAEM~DT#^7nK0b*27i+BG0>!;0Pmgr&(p0+kXjOuCe&ZochJ+qjYF2>+#?Ezv` zPm6b+c=^0p%u-#9!P7Pbh*3Q)-ucTH|Ij36Ivz}6-c=kow`t?%wfS1#u~LY=_y4UK zbeRhZkJ3SxYM`nYVbgQpZZqg|7Zm=ZgRam(Ri6>!^tV4|23_HT!drCE)f%YkAwon0-LS*h_v@=@@N84T5!wDc9d!&g?}a)?;Sbztb6TH`U2v~D z)D7D+w`MYM)Em6Gp#}MfxD`grXs*hQZwCv)9IP35UX2yRN37&vdgNdXYXwcwszzA$ zBT$IGpN66bp`g7+kS>4@vt-wC8;-763(f36crrW#iwp%Eu}=qYGJxCh&j&o8_=^`g zhbMTa3XT}4gU>X8+i}nb+@2bQCHMvv9I;Rf2vxHT;EacMi`A-TIT@ZNHgX$l*U0EN zTw&bXWZ`Dihey#y;fri^x1;5;z3(&{p8@=e-M=xjjml~pcn zVl~1uAbOgZ1XmOL&e6oab2PE<7`d*WsD`PQdsV2ehTZp}&e`HR-!|4XaRWWGH=P@T zwK}|9RSjs3UN}Pz#-+VVojXM5zEMJCa%^yItD$PXgrLFg7K%t8@@yy2?oQaSYwO(6 zqngA`Xd`h~GCRTuWk=J5SJ7}i0`pyvkVQ9gf|MzWa?F+%{(SUq@p)xWTt09(ktlGnlZR=fe+FxF>nQ z8hSK3PduuK4DmZ8etrcfGryvINBV>C;t*a>;mj*OBXeTYA`b}K8#JF{@k%qtN)0V9mDLTSe(yG#C)arzV5v{ z`a0VCJNi00+WR`a42NGBzx*YDOvJhn-sSGBP$?jSXEUwKipJ2&SvGq0XvGEnmN>`{4%7 zh5<7`Ig%O%(h6DBnv`QP%>624QKj&>npkEWLS}AMP|8Y5N9-^RDpO)qD_5>13{Ds@Ju%FbR?vD4ChK|3buJVY=dd99c4t_;Cq_e@ ze$PR!7oyu58_1?dx$}xsYP-4{u;)3R&0^g%bS!q~7}k+^LccGY85|#AnZ4OTE4w2z zl1gWX<#sJ#_FMOkr?b`|p9PHOk|P6FcQR*TkA$x4H`psy*Yw2dwO_ zZX8d>=3hE{jKzD?!xq-AyT-=GA#-0cn>&V$q3)gAnuNF|J(3*SZw;hJ)6fgF=Auw8 z(Do$<4kr(S3%iNfu(5XKtYJq6b~kop2Ccazb#Mw|CHc|u}U^_Wult)1aMJ zXk7h12-<@RjojGM7WQu&8$G{mtPk@Llbz@thj6O;?VZqR7k}H#pZ`tA`ykG!^STM| z{GD@Dy!V%H|MTtx`x5<<-X`g52R6@V8HPdMnXdy#9G&wQ{{%jG&^sv86D8{fvwlLr!69A%;Dq6keoTxYK`28p9?c z_rVTI0oXK%oEHy?Ds)%=1dVABS0S+$+Y@}JHG6=R!;2e3zq$0u^x!b1Q3EGk!G&}G zl0*CreSR6cDRl+wgwSSdLu(7FTu9g|Tu6k(89YEZ<20W^Gx~v@?gU0u9GCq9y?-J7 z3&&G&Px~*Q-@wg6I6Z&LAowdaB$kMV(Ocy;_NkC2LN`kql?n5#Efanf%W^EQ(-2!q zXw*tChYi0H8a2|(F$C*08bGJfyL1}+q)tPCDWNfT=;aV;N@%o|UJhZsghmVMsPFrN4@g%BVjwdm7+7gX+ zSw>R)xCJG7#&n_Vk+d>X9*@gFzk*G3I^+wH27KMhZ(6a#3CBEJ8(z%+eh3&?DS>^U zfe%`x1WHmRfQ}twDWNtzp98viBqkE$SOfR^B@z);B+y+X`R69~&`nN0a%kYLwd5p% z$_cv5DRnT9^+e*{tlgh!FCwZ)=v6LK6ZW~a$pI1g5Tbz(O{6aDhbjW-E`t0)ygHj8 ze~sj4zoZ7lQTai4`Dll?$ET z>grI1Csvw9GE{GzUp5uyUEK#`-M_jJCI8N{Qjb}1N>Gcwa{FKi4 zqv9a;xHz-;EZ3P-IkPWfFp|s0kQj_tDh8U_#h9T@NXdSUWCxdIXJ}K|aqY4z2AJV7 z06A1HXzFs+vR81fM`<)#1CP&0X$+7m9~#N!Gb!8XQI@e(Wk(!!*%$CP0IOY*Wjc^TSOUW9d*SIXCC z&ctYz7_=fWI25QDh*vI#w5BrECi`s3PAig~qlU^3^J(nLQikkvBs;hyI|mq*9fOO@ z&Mxju?a4Y+?k;`Iw8mlJU~1gcyZ{#-73^x3mUp;(S$4YHvqmgA)gfU(jA@N}C+lF#Wj z98*<3jHfQ2Pld1bRG1^Mih$AAMNlfd*i&JS(JEI=*&TWP`mKJJRxq!^^NjAfoH-AlyXp2lXqR6f+n<@0ImrJly7 z6;uRRz(r6RyT#Mkw3EsO8@XKO#$HxzY*v^?RRLg77r;~D<(>-D@G2jS?(+Fmc!j6J zY$g=}P2?gd6<+D7FdI_kLL<6d<_fR!RG6)<0@TFB<>K_wQ{mQPguWLBAY(;j`1K4tS03(T;!uXpLOko-PxoVv)qsz|_)@sBr0Q{|xoNnH6 z$g*->2cvVvL4_NWRzAV6>bCbST+sLbDg+TwPtLs~_~d`CalToLObvO@*%&`RO#lBZ za0Su3MjlQBXW0d@1EGsqXX7Z?3PS+Skhyl zZy(1Ntu;0l+ci8oWN`v7cV%(*CpnC-i~Q=Jl&SRH>49u!ER)LF;IcDMG@g0)bHDWu zp{*>q>9+rPa_)ER>+Z*%90{g9f_o;S(y@IUTkAaHz z