Add unit test for bug #45970 - shows that we can read Formulas with URL based references in them, but we can't change them properly. Part of the unit test is disabled for now

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

2 files changed, 83 insertions, 0 deletions

diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index c6b47848f7..01510b087d 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -1588,4 +1588,87 @@ public final class TestBugs extends BaseTestBugzillaIssues {
        wb = writeOutAndReadBack(wb);
        assertEquals(2, wb.getNumberOfSheets());
     }
+    
+    /**
+     * Problems with formula references to 
+     *  sheets via URLs
+     */
+    public void test45970() throws Exception {
+       HSSFWorkbook wb = openSample("FormulaRefs.xls");
+       assertEquals(3, wb.getNumberOfSheets());
+       
+       HSSFSheet s = wb.getSheetAt(0);
+       HSSFRow row;
+       
+       row = s.getRow(0);
+       assertEquals(Cell.CELL_TYPE_NUMERIC, row.getCell(1).getCellType());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(1);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("B1", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(2);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("Sheet1!B1", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(3);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(4);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("'[\u0005$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       // Change 4
+       row.getCell(1).setCellFormula("'[\u0005$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2");
+       row.getCell(1).setCellValue(123.0);
+       
+       // Add 5
+       row = s.createRow(5);
+       row.createCell(1, Cell.CELL_TYPE_FORMULA);
+       row.getCell(1).setCellFormula("'[\u0005$http://example.com/FormulaRefs.xls]Sheet1'!B1");
+       row.getCell(1).setCellValue(234.0);
+       
+       
+       // Re-test
+       wb = writeOutAndReadBack(wb);
+       s = wb.getSheetAt(0);
+       
+       row = s.getRow(0);
+       assertEquals(Cell.CELL_TYPE_NUMERIC, row.getCell(1).getCellType());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(1);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("B1", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(2);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("Sheet1!B1", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(3);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula());
+       assertEquals(112.0, row.getCell(1).getNumericCellValue());
+       
+// TODO - Fix these so they work...
+if(1==2) {
+       row = s.getRow(4);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("'[\u0005$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula());
+       assertEquals(123.0, row.getCell(1).getNumericCellValue());
+       
+       row = s.getRow(5);
+       assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
+       assertEquals("'[\u0005$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
+       assertEquals(234.0, row.getCell(1).getNumericCellValue());
+}
+    }
 }
diff --git a/test-data/spreadsheet/FormulaRefs.xls b/test-data/spreadsheet/FormulaRefs.xls
new file mode 100644
index 0000000000..72da5473eb
--- /dev/null
+++ b/test-data/spreadsheet/FormulaRefs.xls