fixed evaluation of XSSF workbooks containing formulas with reference errors (#REF!), see Bugzilla 49783

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@989100 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 34 insertions, 12 deletions

diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index 67f61e1615..7668ebc6f6 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.7-beta3" date="2010-??-??">
+           <action dev="POI-DEVELOPERS" type="fix">49783 - fixed evaluation of XSSF workbooks containing formulas with reference errors (#REF!)</action>
            <action dev="POI-DEVELOPERS" type="fix">49751 - fixed fetching names of user defined styles in HSSFCellStyle.getUserStyleName()</action>
            <action dev="POI-DEVELOPERS" type="add">48900 - support for protecting a XSSF workbook</action>
            <action dev="POI-DEVELOPERS" type="fix">49725 - fixed FormulaParser to correctly process defined names with underscore</action>
diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java
index 0f186e7449..db46b53e86 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java
@@ -417,8 +417,12 @@ public final class FormulaParser {
 		SimpleRangePart part1 = parseSimpleRangePart();
 		if (part1 == null) {
 			if (sheetIden != null) {
-				throw new FormulaParseException("Cell reference expected after sheet name at index "
-						+ _pointer + ".");
+                if(look == '#'){  // error ref like MySheet!#REF!
+                    return new ParseNode(ErrPtg.valueOf(parseErrorLiteral()));    
+                } else {
+                    throw new FormulaParseException("Cell reference expected after sheet name at index "
+                            + _pointer + ".");
+                }
 			}
 			return parseNonRange(savePointer);
 		}
diff --git a/src/java/org/apache/poi/ss/usermodel/FormulaError.java b/src/java/org/apache/poi/ss/usermodel/FormulaError.java
index c3c43301e5..162f24aa4b 100644
--- a/src/java/org/apache/poi/ss/usermodel/FormulaError.java
+++ b/src/java/org/apache/poi/ss/usermodel/FormulaError.java
@@ -57,7 +57,7 @@ public enum FormulaError {
      * OFFSET(A1,0,20000) will result in a #REF! error.
      * </p>
      */
-    REF(0x1D, "#REF!"),
+    REF(0x17, "#REF!"),
 
     /**
      * Intended to indicate when what looks like a name is used, but no such name has been defined.
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 5a6e250d9e..e9daa37bfd 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
@@ -24,15 +24,7 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.openxml4j.opc.OPCPackage;
 import org.apache.poi.openxml4j.opc.PackagePart;
 import org.apache.poi.openxml4j.opc.PackagingURIHelper;
-import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.DataFormatter;
-import org.apache.poi.ss.usermodel.Font;
-import org.apache.poi.ss.usermodel.FormulaEvaluator;
-import org.apache.poi.ss.usermodel.Name;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.xssf.XSSFITestDataProvider;
 import org.apache.poi.xssf.XSSFTestDataSamples;
 import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill;
@@ -412,4 +404,29 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues {
         assertEquals("Cycle", wb.getSheetAt(0).getRow(0).getCell(1).getStringCellValue());
 
     }
+
+    public void test49783() throws Exception {
+        Workbook wb =  XSSFTestDataSamples.openSampleWorkbook("49783.xlsx");
+        Sheet sheet = wb.getSheetAt(0);
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+        Cell cell;
+
+        cell = sheet.getRow(0).getCell(0);
+        assertEquals("#REF!*#REF!", cell.getCellFormula());
+        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
+        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());
+
+        Name nm1 = wb.getName("sale_1");
+        assertNotNull("name sale_1 should be present", nm1);
+        assertEquals("Sheet1!#REF!", nm1.getRefersToFormula());
+        Name nm2 = wb.getName("sale_2");
+        assertNotNull("name sale_2 should be present", nm2);
+        assertEquals("Sheet1!#REF!", nm2.getRefersToFormula());
+
+        cell = sheet.getRow(1).getCell(0);
+        assertEquals("sale_1*sale_2", cell.getCellFormula());
+        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
+        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());
+    }
+
 }