Add disabled unit tests for bug #46670 - failing on both HSSF and XSSF, but for different reasons

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

5 files changed, 133 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 706ff5e50d..575e3b4850 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -2624,4 +2624,54 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         assertEquals("International Communication Services SA", s.getRow(2).getCell(0).getStringCellValue());
         assertEquals("Saudi Arabia-Riyadh", s.getRow(210).getCell(0).getStringCellValue());
     }
+    
+    /**
+     * Read, write, read for formulas point to cells in other files.
+     * See {@link #bug46670()} for the main test, this just
+     *  covers reading an existing file and checking it.
+     * TODO Fix this so that it works - formulas are ending up as
+     *  #REF when being changed
+     */
+//    @Test
+    public void bug46670_existing() {
+        HSSFWorkbook wb;
+        Sheet s;
+        Cell c;
+        
+        // Expected values
+        String refLocal = "'[refs/airport.xls]Sheet1'!$A$2";
+        String refHttp  = "'[9http://www.principlesofeconometrics.com/excel/airline.xls]Sheet1'!$A$2";
+        
+        // Check we can read them correctly
+        wb = openSample("46670_local.xls");
+        s = wb.getSheetAt(0);
+        assertEquals(refLocal, s.getRow(0).getCell(0).getCellFormula());
+        
+        wb = openSample("46670_http.xls");
+        s = wb.getSheetAt(0);
+        assertEquals(refHttp, s.getRow(0).getCell(0).getCellFormula());
+        
+        // Now try to set them to the same values, and ensure that
+        //  they end up as they did before, even with a save and re-load
+        wb = openSample("46670_local.xls");
+        s = wb.getSheetAt(0);
+        c = s.getRow(0).getCell(0);
+        c.setCellFormula(refLocal);
+        assertEquals(refLocal, c.getCellFormula());
+        
+        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
+        s = wb.getSheetAt(0);
+        assertEquals(refLocal, s.getRow(0).getCell(0).getCellFormula());
+
+        
+        wb = openSample("46670_http.xls");
+        s = wb.getSheetAt(0);
+        c = s.getRow(0).getCell(0);
+        c.setCellFormula(refHttp);
+        assertEquals(refHttp, c.getCellFormula());
+        
+        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
+        s = wb.getSheetAt(0);
+        assertEquals(refHttp, s.getRow(0).getCell(0).getCellFormula());
+    }
 }
diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
index 378b24526f..fb9dd8ea90 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
@@ -21,6 +21,10 @@ import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertTrue;
 import static org.junit.Assert.fail;
 
+import java.util.HashMap;
+import java.util.Map;
+
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.hssf.util.PaneInformation;
 import org.apache.poi.ss.ITestDataProvider;
 import org.apache.poi.ss.SpreadsheetVersion;
@@ -673,4 +677,83 @@ public abstract class BaseTestBugzillaIssues {
         wb.getCreationHelper().createFormulaEvaluator().evaluateFormulaCell(c);
         return s.getRow(c.getRowIndex()).getCell(c.getColumnIndex());
     }
+    
+    /**
+     * Should be able to write then read formulas with references
+     *  to cells in other files, eg '[refs/airport.xls]Sheet1'!$A$2
+     *  or 'http://192.168.1.2/[blank.xls]Sheet1'!$A$1 .
+     * Additionally, if a reference to that file is provided, it should
+     *  be possible to evaluate them too
+     * TODO Fix this to evaluate for XSSF
+     * TODO Fix this to work at all for HSSF
+     */
+//    @Test
+    public void bug46670() throws Exception {
+        Workbook wb = _testDataProvider.createWorkbook();
+        Sheet s = wb.createSheet();
+        Row r1 = s.createRow(0);
+        
+        
+        // References to try
+        String ext = "xls";
+        if (! (wb instanceof HSSFWorkbook)) ext += "x";
+        String refLocal = "'[test."+ext+"]Sheet1'!$A$2";
+        String refHttp  = "'[http://example.com/test."+ext+"]Sheet1'!$A$2";
+        String otherCellText = "In Another Workbook";
+
+        
+        // Create the references
+        Cell c1 = r1.createCell(0, Cell.CELL_TYPE_FORMULA);
+        c1.setCellFormula(refLocal);
+        
+        Cell c2 = r1.createCell(1, Cell.CELL_TYPE_FORMULA);
+        c2.setCellFormula(refHttp);
+        
+        
+        // Check they were set correctly
+        assertEquals(refLocal, c1.getCellFormula());
+        assertEquals(refHttp,  c2.getCellFormula());
+        
+        
+        // Reload, and ensure they were serialised and read correctly
+        wb = _testDataProvider.writeOutAndReadBack(wb);
+        s = wb.getSheetAt(0);
+        r1 = s.getRow(0);
+        
+        c1 = r1.getCell(0);
+        c2 = r1.getCell(1);
+        assertEquals(refLocal, c1.getCellFormula());
+        assertEquals(refHttp,  c2.getCellFormula());
+
+        
+        // Try to evalutate, without giving a way to get at the other file
+        try {
+            evaluateCell(wb, c1);
+            fail("Shouldn't be able to evaluate without the other file");
+        } catch (Exception e) {}
+        try {
+            evaluateCell(wb, c2);
+            fail("Shouldn't be able to evaluate without the other file");
+        } catch (Exception e) {}
+        
+        
+        // Set up references to the other file
+        Workbook wb2 = _testDataProvider.createWorkbook();
+        wb2.createSheet().createRow(1).createCell(0).setCellValue(otherCellText);
+        
+        Map<String,FormulaEvaluator> evaluators = new HashMap<String, FormulaEvaluator>();
+        evaluators.put(refLocal, wb2.getCreationHelper().createFormulaEvaluator());
+        evaluators.put(refHttp,  wb2.getCreationHelper().createFormulaEvaluator());
+        
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+        evaluator.setupReferencedWorkbooks(evaluators);
+        
+        
+        // Try to evaluate, with the other file
+        evaluator.evaluateFormulaCell(c1);
+        evaluator.evaluateFormulaCell(c2);
+        
+        assertEquals(otherCellText, c1.getStringCellValue());
+        assertEquals(otherCellText, c2.getStringCellValue());
+    }
 }
diff --git a/test-data/spreadsheet/46670_http.xls b/test-data/spreadsheet/46670_http.xls
new file mode 100644
index 0000000000..789284d617
--- /dev/null
+++ b/test-data/spreadsheet/46670_http.xls
diff --git a/test-data/spreadsheet/46670_local.xls b/test-data/spreadsheet/46670_local.xls
new file mode 100644
index 0000000000..0d73e63c65
--- /dev/null
+++ b/test-data/spreadsheet/46670_local.xls
diff --git a/test-data/spreadsheet/46670_ref_airline.xls b/test-data/spreadsheet/46670_ref_airline.xls
new file mode 100644
index 0000000000..b0db5c935c
--- /dev/null
+++ b/test-data/spreadsheet/46670_ref_airline.xls