fixed formula parser to correctly resolve sheet-level names

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

5 files changed, 84 insertions, 25 deletions

diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
index c8a46a8dde..b951ae3b62 100644
--- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
+++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
@@ -899,11 +899,11 @@ public final class TestFormulaParser extends TestCase {
 
 		wb.setSheetName(0, "Sheet1");
 		cell.setCellFormula("Sheet1!B$4:Sheet1!$C1"); // explicit range ':' operator
-		assertEquals("Sheet1!B$4:Sheet1!$C1", cell.getCellFormula()); 
+		assertEquals("Sheet1!B$4:Sheet1!$C1", cell.getCellFormula());
 
 		cell.setCellFormula("Sheet1!B$4:$C1"); // plain area ref
 		assertEquals("Sheet1!B1:$C$4", cell.getCellFormula()); // note - area ref is normalised
-		
+
 		cell.setCellFormula("Sheet1!$C1...B$4"); // different syntax for plain area ref
 		assertEquals("Sheet1!B1:$C$4", cell.getCellFormula());
 
@@ -922,7 +922,7 @@ public final class TestFormulaParser extends TestCase {
 
 		assertEquals("'true'!B2", cell.getCellFormula());
 	}
-	
+
 	public void testParseExternalWorkbookReference() {
 		HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
 		HSSFCell cell = wbA.getSheetAt(0).getRow(0).getCell(0);
@@ -931,15 +931,15 @@ public final class TestFormulaParser extends TestCase {
 		assertEquals("[multibookFormulaB.xls]BSheet1!B1", cell.getCellFormula());
 		Ptg[] expectedPtgs = FormulaExtractor.getPtgs(cell);
 		confirmSingle3DRef(expectedPtgs, 1);
-		
+
 		// now try (re-)parsing the formula
 		Ptg[] actualPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]BSheet1!B1", wbA);
 		confirmSingle3DRef(actualPtgs, 1); // externalSheetIndex 1 -> BSheet1
-		
+
 		// try parsing a formula pointing to a different external sheet
 		Ptg[] otherPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]AnotherSheet!B1", wbA);
 		confirmSingle3DRef(otherPtgs, 0); // externalSheetIndex 0 -> AnotherSheet
-		
+
 		// try setting the same formula in a cell
 		cell.setCellFormula("[multibookFormulaB.xls]AnotherSheet!B1");
 		assertEquals("[multibookFormulaB.xls]AnotherSheet!B1", cell.getCellFormula());
@@ -950,13 +950,13 @@ public final class TestFormulaParser extends TestCase {
 		assertEquals(Ref3DPtg.class, ptg0.getClass());
 		assertEquals(expectedExternSheetIndex, ((Ref3DPtg)ptg0).getExternSheetIndex());
 	}
-	
+
 	public void testUnion() {
 		String formula = "Sheet1!$B$2:$C$3,OFFSET(Sheet1!$E$2:$E$4,1,Sheet1!$A$1),Sheet1!$D$6";
 		HSSFWorkbook wb = new HSSFWorkbook();
 		wb.createSheet("Sheet1");
 		Ptg[] ptgs = FormulaParser.parse(formula, HSSFEvaluationWorkbook.create(wb));
-		
+
 		Class[] expectedClasses = {
 				// TODO - AttrPtg.class, // Excel prepends this
 				MemFuncPtg.class,
diff --git a/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java b/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java
index 1e54031f91..744c692f02 100644
--- a/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java
+++ b/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java
@@ -28,6 +28,7 @@ import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.hssf.record.formula.RefNPtg;
 import org.apache.poi.hssf.record.formula.RefPtg;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.util.HSSFColor;
 import org.apache.poi.util.LittleEndian;
 import org.apache.poi.ss.formula.Formula;
@@ -43,20 +44,21 @@ public final class TestCFRuleRecord extends TestCase
     public void testConstructors ()
     {
         HSSFWorkbook workbook = new HSSFWorkbook();
+        HSSFSheet sheet = workbook.createSheet();
 
-        CFRuleRecord rule1 = CFRuleRecord.create(workbook, "7");
+        CFRuleRecord rule1 = CFRuleRecord.create(sheet, "7");
         assertEquals(CFRuleRecord.CONDITION_TYPE_FORMULA, rule1.getConditionType());
         assertEquals(ComparisonOperator.NO_COMPARISON, rule1.getComparisonOperation());
         assertNotNull(rule1.getParsedExpression1());
         assertSame(Ptg.EMPTY_PTG_ARRAY, rule1.getParsedExpression2());
 
-        CFRuleRecord rule2 = CFRuleRecord.create(workbook, ComparisonOperator.BETWEEN, "2", "5");
+        CFRuleRecord rule2 = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "2", "5");
         assertEquals(CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS, rule2.getConditionType());
         assertEquals(ComparisonOperator.BETWEEN, rule2.getComparisonOperation());
         assertNotNull(rule2.getParsedExpression1());
         assertNotNull(rule2.getParsedExpression2());
 
-        CFRuleRecord rule3 = CFRuleRecord.create(workbook, ComparisonOperator.EQUAL, null, null);
+        CFRuleRecord rule3 = CFRuleRecord.create(sheet, ComparisonOperator.EQUAL, null, null);
         assertEquals(CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS, rule3.getConditionType());
         assertEquals(ComparisonOperator.EQUAL, rule3.getComparisonOperation());
         assertSame(Ptg.EMPTY_PTG_ARRAY, rule3.getParsedExpression2());
@@ -66,7 +68,8 @@ public final class TestCFRuleRecord extends TestCase
     public void testCreateCFRuleRecord ()
     {
         HSSFWorkbook workbook = new HSSFWorkbook();
-        CFRuleRecord record = CFRuleRecord.create(workbook, "7");
+        HSSFSheet sheet = workbook.createSheet();
+        CFRuleRecord record = CFRuleRecord.create(sheet, "7");
         testCFRuleRecord(record);
 
         // Serialize
@@ -306,7 +309,8 @@ public final class TestCFRuleRecord extends TestCase
 
     public void testWrite() {
         HSSFWorkbook workbook = new HSSFWorkbook();
-        CFRuleRecord rr = CFRuleRecord.create(workbook, ComparisonOperator.BETWEEN, "5", "10");
+        HSSFSheet sheet = workbook.createSheet();
+        CFRuleRecord rr = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "5", "10");
 
         PatternFormatting patternFormatting = new PatternFormatting();
         patternFormatting.setFillPattern(PatternFormatting.BRICKS);
diff --git a/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java b/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java
index b3c864cdb8..1e153bc45f 100644
--- a/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java
+++ b/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java
@@ -31,6 +31,7 @@ import org.apache.poi.hssf.record.CFRuleRecord;
 import org.apache.poi.hssf.record.RecordFactory;
 import org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.util.LittleEndian;
 
@@ -46,11 +47,13 @@ public final class TestCFRecordsAggregate extends TestCase
 	public void testCFRecordsAggregate() 
 	{
 		HSSFWorkbook workbook = new HSSFWorkbook();
-		List recs = new ArrayList();
+        HSSFSheet sheet = workbook.createSheet();
+
+        List recs = new ArrayList();
 		CFHeaderRecord header = new CFHeaderRecord();
-		CFRuleRecord rule1 = CFRuleRecord.create(workbook, "7");
-		CFRuleRecord rule2 = CFRuleRecord.create(workbook, ComparisonOperator.BETWEEN, "2", "5");
-		CFRuleRecord rule3 = CFRuleRecord.create(workbook, ComparisonOperator.GE, "100", null);
+		CFRuleRecord rule1 = CFRuleRecord.create(sheet, "7");
+		CFRuleRecord rule2 = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "2", "5");
+		CFRuleRecord rule3 = CFRuleRecord.create(sheet, ComparisonOperator.GE, "100", null);
 		header.setNumberOfConditionalFormats(3);
 		CellRangeAddress[] cellRanges = {
 				new CellRangeAddress(0,1,0,0),
@@ -107,13 +110,14 @@ public final class TestCFRecordsAggregate extends TestCase
 	 */
 	public void testNRules() {
 		HSSFWorkbook workbook = new HSSFWorkbook();
+        HSSFSheet sheet = workbook.createSheet();
 		CellRangeAddress[] cellRanges = {
 				new CellRangeAddress(0,1,0,0),
 				new CellRangeAddress(0,1,2,2),
 		};
 		CFRuleRecord[] rules = {
-			CFRuleRecord.create(workbook, "7"),
-			CFRuleRecord.create(workbook, ComparisonOperator.BETWEEN, "2", "5"),
+			CFRuleRecord.create(sheet, "7"),
+			CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "2", "5"),
 		};
 		CFRecordsAggregate agg = new CFRecordsAggregate(cellRanges, rules);
 		byte[] serializedRecord = new byte[agg.getRecordSize()];
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
index 03f47d6811..c38a555edd 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
@@ -25,8 +25,12 @@ import java.util.Date;
 import junit.framework.TestCase;
 
 import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.model.HSSFFormulaParser;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.NamePtg;
 import org.apache.poi.hssf.util.CellReference;
 import org.apache.poi.util.TempFile;
+import org.apache.poi.ss.formula.FormulaType;
 
 /**
  * @author Andrew C. Oliver (acoliver at apache dot org)
@@ -182,11 +186,11 @@ public final class TestFormulas extends TestCase {
         // don't know how to check correct result .. for the moment, we just verify that the file can be read.
 
         for (int x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
-        	HSSFRow r = s.getRow(x);
+            HSSFRow r = s.getRow(x);
 
             for (int y = 1; y < 256 && y > 0; y=(short)(y+2)) {
 
-            	HSSFCell c = r.getCell(y);
+                HSSFCell c = r.getCell(y);
                 assertTrue("got a formula",c.getCellFormula()!=null);
 
                 assertTrue("loop Formula is as expected "+x+"."+y+operator+y+"."+x+"!="+c.getCellFormula(),(
@@ -535,9 +539,9 @@ public final class TestFormulas extends TestCase {
 
     public void testAbsRefs() {
         HSSFWorkbook wb = new HSSFWorkbook();
-		HSSFSheet s = wb.createSheet();
-		HSSFRow r;
-		HSSFCell c;
+        HSSFSheet s = wb.createSheet();
+        HSSFRow r;
+        HSSFCell c;
 
         r = s.createRow(0);
         c = r.createCell(0);
@@ -884,4 +888,51 @@ public final class TestFormulas extends TestCase {
         assertEquals("DZ2*2", wb.getSheetAt(0).getRow(1).getCell(128).toString());
         assertEquals("B32770*2", wb.getSheetAt(0).getRow(32768).getCell(1).toString());
     }
+
+    /**
+     * Test creation / evaluation of formulas with sheet-level names
+     */
+    public void testSheetLevelFormulas(){
+        HSSFWorkbook wb = new HSSFWorkbook();
+
+        HSSFRow row;
+        HSSFSheet sh1 = wb.createSheet("Sheet1");
+        HSSFName 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");
+
+
+        HSSFSheet sh2 = wb.createSheet("Sheet2");
+        HSSFName 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");
+
+        //check that NamePtg refers to the correct NameRecord
+        Ptg[] ptgs1 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 0);
+        NamePtg nPtg1 = (NamePtg)ptgs1[0];
+        assertSame(nm1, wb.getNameAt(nPtg1.getIndex()));
+
+        Ptg[] ptgs2 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 1);
+        NamePtg nPtg2 = (NamePtg)ptgs2[0];
+        assertSame(nm2, wb.getNameAt(nPtg2.getIndex()));
+
+        //check that the formula evaluator returns the correct result
+        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
+        assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue());
+        assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue());
+
+        assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue());
+        assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue());
+    }
 }
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java b/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
index 4ad6b57932..a0ef4fabc9 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
@@ -543,7 +543,7 @@ public final class TestNamedRange extends TestCase {
         HSSFWorkbook wb = new HSSFWorkbook();
         wb.createSheet("CSCO");
 
-        Ptg[] ptgs = HSSFFormulaParser.parse("CSCO!$E$71", wb, FormulaType.NAMEDRANGE);
+        Ptg[] ptgs = HSSFFormulaParser.parse("CSCO!$E$71", wb, FormulaType.NAMEDRANGE, 0);
         for (int i = 0; i < ptgs.length; i++) {
             assertEquals('R', ptgs[i].getRVAType());
         }