bugzilla 52575: added an option to ignore missing workbook references in formula evaluator

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

8 files changed, 197 insertions, 24 deletions

diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index c2a3693f6d..b3f0f1182f 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.8-beta6" date="2012-??-??">
+           <action dev="poi-developers" type="add">52575 - added an option to ignore missing workbook references in formula evaluator</action>
            <action dev="poi-developers" type="add">Validate address of hyperlinks in XSSF</action>
            <action dev="poi-developers" type="fix">52540 - Relax the M4.1 constraint on reading OOXML files, as some Office produced ones do have 2 Core Properties, despite the specification explicitly forbidding this</action>
            <action dev="poi-developers" type="add">52462 - Added implementation for SUMIFS()</action>
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java
index 66069f8cbc..462d92a560 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java
@@ -66,7 +66,10 @@ final class HSSFEvaluationCell implements EvaluationCell {
 	public EvaluationSheet getSheet() {
 		return _evalSheet;
 	}
-	public String getStringCellValue() {
-		return _cell.getRichStringCellValue().getString();
-	}
-}
+	public String getStringCellValue() {

+		return _cell.getRichStringCellValue().getString();

+	}

+	public int getCachedFormulaResultType() {

+		return _cell.getCachedFormulaResultType();

+	}

+}

diff --git a/src/java/org/apache/poi/ss/formula/EvaluationCell.java b/src/java/org/apache/poi/ss/formula/EvaluationCell.java
index eef4da18da..a3de9e8945 100644
--- a/src/java/org/apache/poi/ss/formula/EvaluationCell.java
+++ b/src/java/org/apache/poi/ss/formula/EvaluationCell.java
@@ -38,7 +38,9 @@ public interface EvaluationCell {
 	int getCellType();
 
 	double getNumericCellValue();
-	String getStringCellValue();
-	boolean getBooleanCellValue();
-	int getErrorCellValue();
-}
+	String getStringCellValue();

+	boolean getBooleanCellValue();

+	int getErrorCellValue();

+

+	int getCachedFormulaResultType();

+}

diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
index a154089253..87018e6abe 100644
--- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
+++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
@@ -83,13 +83,13 @@ public final class OperationEvaluationContext {
 		} else {
 			// look up sheet by name from external workbook
 			String workbookName = externalSheet.getWorkbookName();
-			try {
-				targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName);
-			} catch (WorkbookNotFoundException e) {
-				throw new RuntimeException(e.getMessage());
-			}
-			otherSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName());
-			if (otherSheetIndex < 0) {
+			try {

+				targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName);

+			} catch (WorkbookNotFoundException e) {

+				throw new RuntimeException(e.getMessage(), e);

+			}

+			otherSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName());

+			if (otherSheetIndex < 0) {

 				throw new RuntimeException("Invalid sheet name '" + externalSheet.getSheetName()
 						+ "' in bool '" + workbookName + "'.");
 			}
diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
index 1328c850b4..3807c9c291 100644
--- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
+++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
@@ -67,6 +67,8 @@ import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
 import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.util.POILogFactory;
+import org.apache.poi.util.POILogger;
 
 /**
  * Evaluates formula cells.<p/>
@@ -80,8 +82,16 @@ import org.apache.poi.ss.usermodel.Cell;
  * @author Josh Micich
  */
 public final class WorkbookEvaluator {
+	
+	private static final POILogger LOG = POILogFactory.getLogger(WorkbookEvaluator.class);
+
+    /**
+     * Whether to use cached formula results if external workbook references in a formula is not available.
+     * See Bugzilla 52575 for details.
+     */
+    private static final String IGNORE_MISSING_WORKBOOKS = WorkbookEvaluator.class.getName() + ".IGNORE_MISSING_WORKBOOKS";
 
-	private final EvaluationWorkbook _workbook;
+    private final EvaluationWorkbook _workbook;
 	private EvaluationCache _cache;
 	/** part of cache entry key (useful when evaluating multiple workbooks) */
 	private int _workbookIx;
@@ -144,11 +154,19 @@ public final class WorkbookEvaluator {
 	}
 
 	private static boolean isDebugLogEnabled() {
-		return false;
+		return LOG.check(POILogger.DEBUG);
+	}
+	private static boolean isInfoLogEnabled() {
+		return LOG.check(POILogger.INFO);
 	}
 	private static void logDebug(String s) {
 		if (isDebugLogEnabled()) {
-			System.out.println(s);
+			LOG.log(POILogger.DEBUG, s);
+		}
+	}
+	private static void logInfo(String s) {
+		if (isInfoLogEnabled()) {
+			LOG.log(POILogger.INFO, s);
 		}
 	}
 	/* package */ void attachToEnvironment(CollaboratingWorkbooksEnvironment collaboratingWorkbooksEnvironment, EvaluationCache cache, int workbookIx) {
@@ -288,9 +306,38 @@ public final class WorkbookEvaluator {
 				}
 
 				tracker.updateCacheResult(result);
-			} catch (NotImplementedException e) {
+			}
+			 catch (NotImplementedException e) {
 				throw addExceptionInfo(e, sheetIndex, rowIndex, columnIndex);
-			} finally {
+			 } catch (RuntimeException re) {
+				 if (re.getCause() instanceof WorkbookNotFoundException 
+						 //To be replaced by configuration infrastructure
+						 && Boolean.valueOf(System.getProperty(IGNORE_MISSING_WORKBOOKS))) {
+ 					logInfo(re.getCause().getMessage() + " - Continuing with cached value!");
+ 					switch(srcCell.getCachedFormulaResultType()) {
+	 					case Cell.CELL_TYPE_NUMERIC:
+	 						result = new NumberEval(srcCell.getNumericCellValue());
+	 						break;
+	 					case Cell.CELL_TYPE_STRING:
+	 						result =  new StringEval(srcCell.getStringCellValue());
+	 						break;
+	 					case Cell.CELL_TYPE_BLANK:
+	 						result = BlankEval.instance;
+	 						break;
+	 					case Cell.CELL_TYPE_BOOLEAN:
+	 						result =  BoolEval.valueOf(srcCell.getBooleanCellValue());
+	 						break;
+	 					case Cell.CELL_TYPE_ERROR:
+							result =  ErrorEval.valueOf(srcCell.getErrorCellValue());
+							break;
+	 					case Cell.CELL_TYPE_FORMULA:
+						default:
+							throw new RuntimeException("Unexpected cell type '" + srcCell.getCellType()+"' found!");
+ 					}
+				 } else {
+					 throw re;
+				 }
+			 } finally {
 				tracker.endEvaluate(cce);
 			}
 		} else {
diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java
index 8e86902570..2c169e648e 100644
--- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java
+++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java
@@ -128,4 +128,8 @@ final class ForkedEvaluationCell implements EvaluationCell {
 	public int getColumnIndex() {
 		return _masterCell.getColumnIndex();
 	}
+    public int getCachedFormulaResultType() {
+        return _masterCell.getCachedFormulaResultType();
+    }
+
 }
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java
index 127e4da86a..46f10dc26d 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java
@@ -69,7 +69,10 @@ final class XSSFEvaluationCell implements EvaluationCell {
 	public EvaluationSheet getSheet() {
 		return _evalSheet;
 	}
-	public String getStringCellValue() {
-		return _cell.getRichStringCellValue().getString();
-	}
-}
+	public String getStringCellValue() {

+		return _cell.getRichStringCellValue().getString();

+	}

+	public int getCachedFormulaResultType() {

+		return _cell.getCachedFormulaResultType();

+	}

+}

diff --git a/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java b/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java
new file mode 100644
index 0000000000..7b639f996e
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java
@@ -0,0 +1,113 @@
+/*

+ *  ====================================================================

+ *    Licensed to the Apache Software Foundation (ASF) under one or more

+ *    contributor license agreements.  See the NOTICE file distributed with

+ *    this work for additional information regarding copyright ownership.

+ *    The ASF licenses this file to You under the Apache License, Version 2.0

+ *    (the "License"); you may not use this file except in compliance with

+ *    the License.  You may obtain a copy of the License at

+ *

+ *        http://www.apache.org/licenses/LICENSE-2.0

+ *

+ *    Unless required by applicable law or agreed to in writing, software

+ *    distributed under the License is distributed on an "AS IS" BASIS,

+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ *    See the License for the specific language governing permissions and

+ *    limitations under the License.

+ * ====================================================================

+ */

+

+package org.apache.poi.ss.formula;

+

+import junit.framework.TestCase;

+import org.apache.poi.hssf.HSSFTestDataSamples;

+import org.apache.poi.hssf.usermodel.*;

+import org.apache.poi.ss.usermodel.Cell;

+import org.apache.poi.ss.usermodel.FormulaEvaluator;

+

+import java.io.IOException;

+

+public class TestMissingWorkbook extends TestCase {

+	private static final String MAIN_WORKBOOK_FILENAME = "52575_main.xls";

+	private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls";

+	private static final String SOURCE_WORKBOOK_FILENAME = "52575_source.xls";

+	

+	private static final String propertyKey = WorkbookEvaluator.class.getName() + ".IGNORE_MISSING_WORKBOOKS";

+	

+	private HSSFWorkbook mainWorkbook;

+	private HSSFWorkbook sourceWorkbook;

+	

+	@Override

+	protected void setUp() throws Exception {

+		mainWorkbook = HSSFTestDataSamples.openSampleWorkbook(MAIN_WORKBOOK_FILENAME);

+		sourceWorkbook = HSSFTestDataSamples.openSampleWorkbook(SOURCE_WORKBOOK_FILENAME);

+		

+		assertNotNull(mainWorkbook);

+		assertNotNull(sourceWorkbook);

+	}

+

+	public void testMissingWorkbookMissing() throws IOException {

+		FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator();

+		

+		HSSFSheet lSheet = mainWorkbook.getSheetAt(0);

+		HSSFRow lARow = lSheet.getRow(0);

+		HSSFCell lA1Cell = lARow.getCell(0);

+		

+		assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType());

+		try {

+			evaluator.evaluateFormulaCell(lA1Cell);

+			fail("Missing external workbook reference exception expected!");

+		}catch(RuntimeException re) {

+			assertTrue("Unexpected exception: " + re, re.getMessage().indexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1);

+		}

+	}

+	

+	public void testMissingWorkbookMissingOverride() throws IOException {

+		HSSFSheet lSheet = mainWorkbook.getSheetAt(0);

+		HSSFCell lA1Cell = lSheet.getRow(0).getCell(0);

+		HSSFCell lB1Cell = lSheet.getRow(1).getCell(0);

+		HSSFCell lC1Cell = lSheet.getRow(2).getCell(0);

+		

+		assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType());

+		assertEquals(Cell.CELL_TYPE_FORMULA, lB1Cell.getCellType());

+		assertEquals(Cell.CELL_TYPE_FORMULA, lC1Cell.getCellType());

+		

+		FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator();

+		

+		System.setProperty(propertyKey, Boolean.toString(true));

+		assertEquals(Cell.CELL_TYPE_NUMERIC, evaluator.evaluateFormulaCell(lA1Cell));

+		assertEquals(Cell.CELL_TYPE_STRING, evaluator.evaluateFormulaCell(lB1Cell));

+		assertEquals(Cell.CELL_TYPE_BOOLEAN, evaluator.evaluateFormulaCell(lC1Cell));

+

+		assertEquals(10.0d, lA1Cell.getNumericCellValue(), 0.00001d);

+		assertEquals("POI rocks!", lB1Cell.getStringCellValue());

+		assertEquals(true, lC1Cell.getBooleanCellValue());

+	}

+	

+

+	public void testExistingWorkbook() throws IOException {

+		HSSFSheet lSheet = mainWorkbook.getSheetAt(0);

+		HSSFCell lA1Cell = lSheet.getRow(0).getCell(0);

+		HSSFCell lB1Cell = lSheet.getRow(1).getCell(0);

+		HSSFCell lC1Cell = lSheet.getRow(2).getCell(0);

+		

+		assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType());

+		assertEquals(Cell.CELL_TYPE_FORMULA, lB1Cell.getCellType());

+		assertEquals(Cell.CELL_TYPE_FORMULA, lC1Cell.getCellType());

+

+		HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);

+		HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);

+		HSSFFormulaEvaluator.setupEnvironment(

+				new String[]{MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME}, 

+				new HSSFFormulaEvaluator[] {lMainWorkbookEvaluator, lSourceEvaluator});

+		

+		assertEquals(Cell.CELL_TYPE_NUMERIC, lMainWorkbookEvaluator.evaluateFormulaCell(lA1Cell));

+		assertEquals(Cell.CELL_TYPE_STRING, lMainWorkbookEvaluator.evaluateFormulaCell(lB1Cell));

+		assertEquals(Cell.CELL_TYPE_BOOLEAN, lMainWorkbookEvaluator.evaluateFormulaCell(lC1Cell));

+

+		assertEquals(20.0d, lA1Cell.getNumericCellValue(), 0.00001d);

+		assertEquals("Apache rocks!", lB1Cell.getStringCellValue());

+		assertEquals(false, lC1Cell.getBooleanCellValue());

+	}

+

+}