1. added support for shared formulas in XSSF, see bug #464332. improved validation of workbook-global and sheet-global names

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

2 files changed, 138 insertions, 66 deletions

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 668e83a0e2..b9035db6b2 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
@@ -19,83 +19,106 @@ package org.apache.poi.xssf.usermodel;
 
 import junit.framework.TestCase;
 
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.FormulaEvaluator;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.xssf.XSSFTestDataSamples;
 
 public final class TestXSSFFormulaEvaluation extends TestCase {
-	public TestXSSFFormulaEvaluation(String name) {
-		super(name);
-		
-		// 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() );
-		
-		Cell c2 = r.createCell(1);
-		c2.setCellFormula("10/2");
+    public TestXSSFFormulaEvaluation(String name) {
+        super(name);
+
+        // 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() );
+
+        Cell c2 = r.createCell(1);
+        c2.setCellFormula("10/2");
         assertEquals(0.0, c2.getNumericCellValue() );
 
-		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)");
+        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() );
 
-		Cell c2 = r.createCell(1);
-		c2.setCellFormula("SUM(A1:E1)");
+        Cell c2 = r.createCell(1);
+        c2.setCellFormula("SUM(A1:E1)");
         assertEquals(0.0, c2.getNumericCellValue() );
 
-		Cell c3 = r.createCell(2);
-		c3.setCellFormula("COUNT(A1:A1)");
+        Cell c3 = r.createCell(2);
+        c3.setCellFormula("COUNT(A1:A1)");
         assertEquals(0.0, c3.getNumericCellValue() );
 
-		Cell c4 = r.createCell(3);
-		c4.setCellFormula("COUNTA(A1:E1)");
+        Cell c4 = r.createCell(3);
+        c4.setCellFormula("COUNTA(A1:E1)");
         assertEquals(0.0, c4.getNumericCellValue() );
 
 
-		// 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);
-	}
+        // 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 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());
+    }
 }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java
index 5cf0990d4c..4d273ea26c 100755
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java
@@ -109,4 +109,53 @@ public class TestXSSFName extends TestCase {
         wb.removeName(0);

         assertEquals(1, wb.getNumberOfNames());

     }

+

+    public void testScope() {

+        XSSFWorkbook wb = new XSSFWorkbook();

+        wb.createSheet();

+        wb.createSheet();

+        

+        XSSFName name;

+

+        name = wb.createName();

+        name.setNameName("aaa");

+        name = wb.createName();

+        try {

+            name.setNameName("aaa");

+            fail("Expected exception");

+        } catch(Exception e){

+            assertEquals("The workbook already contains this name: aaa", e.getMessage());

+        }

+

+        name = wb.createName();

+        name.setSheetIndex(0);

+        name.setNameName("aaa");

+        name = wb.createName();

+        name.setSheetIndex(0);

+        try {

+            name.setNameName("aaa");

+            fail("Expected exception");

+        } catch(Exception e){

+            assertEquals("The sheet already contains this name: aaa", e.getMessage());

+        }

+

+        name = wb.createName();

+        name.setSheetIndex(1);

+        name.setNameName("aaa");

+        name = wb.createName();

+        name.setSheetIndex(1);

+        try {

+            name.setNameName("aaa");

+            fail("Expected exception");

+        } catch(Exception e){

+            assertEquals("The sheet already contains this name: aaa", e.getMessage());

+        }

+

+        int cnt = 0;

+        for (int i = 0; i < wb.getNumberOfNames(); i++) {

+            if("aaa".equals(wb.getNameAt(i).getNameName())) cnt++;

+        }

+        assertEquals(3, cnt);

+    }

+

 }