You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2012/02/06 08:37:12 UTC

svn commit: r1240903 - in /poi/trunk: src/documentation/content/xdocs/ src/java/org/apache/poi/hssf/usermodel/ src/java/org/apache/poi/ss/formula/ src/java/org/apache/poi/ss/formula/eval/forked/ src/ooxml/java/org/apache/poi/xssf/usermodel/ src/testcas...

Author: yegor
Date: Mon Feb  6 07:37:11 2012
New Revision: 1240903

URL: http://svn.apache.org/viewvc?rev=1240903&view=rev
Log:
bugzilla 52575: added an option to ignore missing workbook references in formula evaluator

Added:
    poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java
    poi/trunk/test-data/spreadsheet/52575_main.xls   (with props)
    poi/trunk/test-data/spreadsheet/52575_source.xls   (with props)
Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java
    poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationCell.java
    poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
    poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
    poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Mon Feb  6 07:37:11 2012
@@ -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>

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java Mon Feb  6 07:37:11 2012
@@ -66,7 +66,10 @@ final class HSSFEvaluationCell implement
 	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();
+	}
+}

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationCell.java?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationCell.java Mon Feb  6 07:37:11 2012
@@ -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();
+}

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java Mon Feb  6 07:37:11 2012
@@ -83,13 +83,13 @@ public final class OperationEvaluationCo
 		} 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 + "'.");
 			}

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Mon Feb  6 07:37:11 2012
@@ -67,6 +67,8 @@ import org.apache.poi.ss.util.CellRefere
 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 {

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java Mon Feb  6 07:37:11 2012
@@ -128,4 +128,8 @@ final class ForkedEvaluationCell impleme
 	public int getColumnIndex() {
 		return _masterCell.getColumnIndex();
 	}
+    public int getCachedFormulaResultType() {
+        return _masterCell.getCachedFormulaResultType();
+    }
+
 }

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java?rev=1240903&r1=1240902&r2=1240903&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java Mon Feb  6 07:37:11 2012
@@ -69,7 +69,10 @@ final class XSSFEvaluationCell implement
 	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();
+	}
+}

Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java?rev=1240903&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java Mon Feb  6 07:37:11 2012
@@ -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());
+	}
+
+}

Added: poi/trunk/test-data/spreadsheet/52575_main.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/52575_main.xls?rev=1240903&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/52575_main.xls
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream

Added: poi/trunk/test-data/spreadsheet/52575_source.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/52575_source.xls?rev=1240903&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/52575_source.xls
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org