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