You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/10/09 08:07:37 UTC
svn commit: r703063 [2/2] - in /poi/trunk/src:
java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/formula/
testcases/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/ss/formula/
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java?rev=703063&r1=703062&r2=703063&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java Wed Oct 8 23:07:36 2008
@@ -32,6 +32,7 @@
import org.apache.poi.hssf.record.formula.FuncVarPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.formula.EvaluationListener;
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.WorkbookEvaluatorTestHelper;
@@ -168,6 +169,12 @@
assertEquals("-1000000.0-3000000.0", cell.getCellFormula());
assertEquals(-4000000, eva.evaluate(cell).getNumberValue(), 0);
}
+// public static void main(String[] args) {
+// new TestFormulaEvaluatorBugs().test44410();
+// new TestFormulaEvaluatorBugs().testSlowEvaluate45376();
+// new HSSFWorkbook();
+// System.out.println("done");
+// }
/**
* Bug 44410: SUM(C:C) is valid in excel, and means a sum
@@ -309,7 +316,7 @@
public void onCacheHit(int sheetIndex, int srcRowNum, int srcColNum, ValueEval result) {
_countCacheHits++;
}
- public void onStartEvaluate(int sheetIndex, int rowIndex, int columnIndex, Ptg[] ptgs) {
+ public void onStartEvaluate(EvaluationCell cell, ICacheEntry entry, Ptg[] ptgs) {
_countCacheMisses++;
}
}
@@ -341,7 +348,7 @@
HSSFCell cell = row.getCell(8);
EvalListener evalListener = new EvalListener();
WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.createEvaluator(wb, evalListener);
- evaluator.evaluate(cell);
+ evaluator.evaluate(HSSFEvaluationTestHelper.wrapCell(cell));
int evalCount = evalListener.getCountCacheMisses();
if (evalCount > 10) {
// Without caching, evaluating cell 'A9' takes 21845 evaluations which consumes
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/EvaluationListener.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/EvaluationListener.java?rev=703063&r1=703062&r2=703063&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/EvaluationListener.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/EvaluationListener.java Wed Oct 8 23:07:36 2008
@@ -19,6 +19,7 @@
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.usermodel.HSSFCell;
/**
* Tests should extend this class if they need to track the internal working of the {@link WorkbookEvaluator}.<br/>
@@ -28,26 +29,28 @@
* @author Josh Micich
*/
public abstract class EvaluationListener implements IEvaluationListener {
- public void onCacheHit(int sheetIndex, int rowIndex, int srcColNum, ValueEval result) {
+ public void onCacheHit(int sheetIndex, int rowIndex, int columnIndex, ValueEval result) {
// do nothing
}
- public void onReadPlainValue(int sheetIndex, int srcRowNum, int srcColNum, ValueEval value) {
+ public void onReadPlainValue(int sheetIndex, int rowIndex, int columnIndex, ICacheEntry entry) {
// do nothing
}
- public void onStartEvaluate(int sheetIndex, int rowIndex, int columnIndex, Ptg[] ptgs) {
+ public void onStartEvaluate(EvaluationCell cell, ICacheEntry entry, Ptg[] ptgs) {
// do nothing
}
- public void onEndEvaluate(int sheetIndex, int rowIndex, int columnIndex, ValueEval result) {
+ public void onEndEvaluate(ICacheEntry entry, ValueEval result) {
// do nothing
}
public void onClearWholeCache() {
// do nothing
}
- public void onClearCachedValue(int sheetIndex, int rowIndex, int columnIndex, ValueEval value) {
+ public void onClearCachedValue(ICacheEntry entry) {
// do nothing
}
- public void onClearDependentCachedValue(int sheetIndex, int rowIndex, int columnIndex,
- ValueEval value,int depth) {
+ public void sortDependentCachedValues(ICacheEntry[] entries) {
+ // do nothing
+ }
+ public void onClearDependentCachedValue(ICacheEntry entry, int depth) {
// do nothing
}
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestCellCacheEntry.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestCellCacheEntry.java?rev=703063&r1=703062&r2=703063&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestCellCacheEntry.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestCellCacheEntry.java Wed Oct 8 23:07:36 2008
@@ -17,13 +17,11 @@
package org.apache.poi.ss.formula;
-import org.apache.poi.hssf.record.formula.eval.BlankEval;
+import junit.framework.TestCase;
+
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
-import junit.framework.AssertionFailedError;
-import junit.framework.TestCase;
-
/**
* Tests {@link CellCacheEntry}.
*
@@ -32,22 +30,14 @@
public class TestCellCacheEntry extends TestCase {
public void testBasic() {
- CellCacheEntry cce = new CellCacheEntry();
- cce.updatePlainValue(new NumberEval(42.0));
- ValueEval ve = cce.getValue();
+ CellCacheEntry pcce = new PlainValueCellCacheEntry(new NumberEval(42.0));
+ ValueEval ve = pcce.getValue();
assertEquals(42, ((NumberEval)ve).getNumberValue(), 0.0);
- cce.setFormulaResult(new NumberEval(10.0), new CellLocation[] { });
- }
-
- public void testBlank() {
- CellCacheEntry cce = new CellCacheEntry();
- cce.updatePlainValue(BlankEval.INSTANCE);
- try {
- cce.updatePlainValue(BlankEval.INSTANCE);
- } catch (IllegalStateException e) {
- // bug was visible around svn r700356
- throw new AssertionFailedError("cache entry does not handle blank values properly");
- }
+ FormulaCellCacheEntry fcce = new FormulaCellCacheEntry();
+ fcce.updateFormulaResult(new NumberEval(10.0), CellCacheEntry.EMPTY_ARRAY, null);
+
+ ve = fcce.getValue();
+ assertEquals(10, ((NumberEval)ve).getNumberValue(), 0.0);
}
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestEvaluationCache.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestEvaluationCache.java?rev=703063&r1=703062&r2=703063&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestEvaluationCache.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestEvaluationCache.java Wed Oct 8 23:07:36 2008
@@ -19,7 +19,11 @@
import java.io.PrintStream;
import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Comparator;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import junit.framework.AssertionFailedError;
import junit.framework.TestCase;
@@ -33,10 +37,12 @@
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFEvaluationTestHelper;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
+import org.apache.poi.ss.formula.PlainCellCache.Loc;
/**
* Tests {@link EvaluationCache}. Makes sure that where possible (previously calculated) cached
@@ -46,33 +52,89 @@
* @author Josh Micich
*/
public class TestEvaluationCache extends TestCase {
+
+ private static final class FormulaCellCacheEntryComparer implements Comparator {
+
+ private final Map _formulaCellsByCacheEntry;
+
+ public FormulaCellCacheEntryComparer(Map formulaCellsByCacheEntry) {
+ _formulaCellsByCacheEntry = formulaCellsByCacheEntry;
+ }
+ private EvaluationCell getCell(Object a) {
+ return (EvaluationCell)_formulaCellsByCacheEntry.get(a);
+ }
+
+ public int compare(Object oa, Object ob) {
+ EvaluationCell a = getCell(oa);
+ EvaluationCell b = getCell(ob);
+ int cmp;
+ cmp = a.getRowIndex() - b.getRowIndex();
+ if (cmp != 0) {
+ return cmp;
+ }
+ cmp = a.getColumnIndex() - b.getColumnIndex();
+ if (cmp != 0) {
+ return cmp;
+ }
+ if (a.getSheet() == b.getSheet()) {
+ return 0;
+ }
+ throw new RuntimeException("Incomplete code - don't know how to order sheets");
+ }
+ }
+
private static final class EvalListener extends EvaluationListener {
private final List _logList;
private final HSSFWorkbook _book;
+ private Map _formulaCellsByCacheEntry;
+ private Map _plainCellLocsByCacheEntry;
public EvalListener(HSSFWorkbook wb) {
_book = wb;
_logList = new ArrayList();
+ _formulaCellsByCacheEntry = new HashMap();
+ _plainCellLocsByCacheEntry = new HashMap();
}
public void onCacheHit(int sheetIndex, int rowIndex, int columnIndex, ValueEval result) {
log("hit", rowIndex, columnIndex, result);
}
- public void onReadPlainValue(int sheetIndex, int rowIndex, int columnIndex, ValueEval value) {
- log("value", rowIndex, columnIndex, value);
- }
- public void onStartEvaluate(int sheetIndex, int rowIndex, int columnIndex, Ptg[] ptgs) {
- log("start", rowIndex, columnIndex, ptgs);
- }
- public void onEndEvaluate(int sheetIndex, int rowIndex, int columnIndex, ValueEval result) {
- log("end", rowIndex, columnIndex, result);
+ public void onReadPlainValue(int sheetIndex, int rowIndex, int columnIndex, ICacheEntry entry) {
+ Loc loc = new Loc(0, sheetIndex, rowIndex, columnIndex);
+ _plainCellLocsByCacheEntry.put(entry, loc);
+ log("value", rowIndex, columnIndex, entry.getValue());
+ }
+ public void onStartEvaluate(EvaluationCell cell, ICacheEntry entry, Ptg[] ptgs) {
+ _formulaCellsByCacheEntry.put(entry, cell);
+ log("start", cell.getRowIndex(), cell.getColumnIndex(), ptgs);
+ }
+ public void onEndEvaluate(ICacheEntry entry, ValueEval result) {
+ EvaluationCell cell = (EvaluationCell) _formulaCellsByCacheEntry.get(entry);
+ log("end", cell.getRowIndex(), cell.getColumnIndex(), result);
+ }
+ public void onClearCachedValue(ICacheEntry entry) {
+ int rowIndex;
+ int columnIndex;
+ EvaluationCell cell = (EvaluationCell) _formulaCellsByCacheEntry.get(entry);
+ if (cell == null) {
+ Loc loc = (Loc)_plainCellLocsByCacheEntry.get(entry);
+ if (loc == null) {
+ throw new IllegalStateException("can't find cell or location");
+ }
+ rowIndex = loc.getRowIndex();
+ columnIndex = loc.getColumnIndex();
+ } else {
+ rowIndex = cell.getRowIndex();
+ columnIndex = cell.getColumnIndex();
+ }
+ log("clear", rowIndex, columnIndex, entry.getValue());
}
- public void onClearCachedValue(int sheetIndex, int rowIndex, int columnIndex, ValueEval value) {
- log("clear", rowIndex, columnIndex, value);
+ public void sortDependentCachedValues(ICacheEntry[] entries) {
+ Arrays.sort(entries, new FormulaCellCacheEntryComparer(_formulaCellsByCacheEntry));
}
- public void onClearDependentCachedValue(int sheetIndex, int rowIndex, int columnIndex,
- ValueEval value,int depth) {
- log("clear" + depth, rowIndex, columnIndex, value);
+ public void onClearDependentCachedValue(ICacheEntry entry, int depth) {
+ EvaluationCell cell = (EvaluationCell) _formulaCellsByCacheEntry.get(entry);
+ log("clear" + depth, cell.getRowIndex(), cell.getColumnIndex(), entry.getValue());
}
private void log(String tag, int rowIndex, int columnIndex, Object value) {
StringBuffer sb = new StringBuffer(64);
@@ -139,19 +201,23 @@
_sheet = _wb.createSheet("Sheet1");
}
+ private static EvaluationCell wrapCell(HSSFCell cell) {
+ return HSSFEvaluationTestHelper.wrapCell(cell);
+ }
+
public void setCellValue(String cellRefText, double value) {
HSSFCell cell = getOrCreateCell(cellRefText);
// be sure to blank cell, in case it is currently a formula
cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
// otherwise this line will only set the formula cached result;
cell.setCellValue(value);
- _evaluator.setCachedPlainValue(_sheet, cell.getRowIndex(), cell.getCellNum(), new NumberEval(value));
+ _evaluator.notifyUpdateCell(wrapCell(cell));
}
public void setCellFormula(String cellRefText, String formulaText) {
HSSFCell cell = getOrCreateCell(cellRefText);
cell.setCellFormula(formulaText);
- _evaluator.notifySetFormula(_sheet, cell.getRowIndex(), cell.getCellNum());
+ _evaluator.notifyUpdateCell(wrapCell(cell));
}
private HSSFCell getOrCreateCell(String cellRefText) {
@@ -170,7 +236,7 @@
}
public ValueEval evaluateCell(String cellRefText) {
- return _evaluator.evaluate(getOrCreateCell(cellRefText));
+ return _evaluator.evaluate(wrapCell(getOrCreateCell(cellRefText)));
}
public String[] getAndClearLog() {
@@ -243,6 +309,7 @@
// change a low level cell
ms.setCellValue("D1", 10);
confirmLog(ms, new String[] {
+ "clear D1 10",
"clear1 C1 54",
"clear2 B1 8",
"clear3 A1 46",
@@ -272,6 +339,7 @@
ms.setCellValue("B3", 3); // B3 is in the middle of the dependency tree
confirmLog(ms, new String[] {
+ "clear B3 3",
"clear1 B2 46",
"clear2 A1 46",
});
@@ -296,6 +364,7 @@
ms.getAndClearLog();
ms.setCellFormula("B2", "B3*C2-C3"); // used to be "B3*C1-C2"
confirmLog(ms, new String[] {
+ "clear B2 46",
"clear1 A1 46",
});
@@ -307,7 +376,8 @@
"hit B3 2",
"hit C2 62",
"start C3 SUM(D3:E4)",
- "hit D3 16", "hit E3 17", "value D4 #BLANK#", "value E4 #BLANK#",
+ "hit D3 16", "hit E3 17",
+// "value D4 #BLANK#", "value E4 #BLANK#",
"end C3 33",
"end B2 91",
"end A1 91",
@@ -320,6 +390,7 @@
// Now change a value that should no longer affect B2
ms.setCellValue("D1", 11);
confirmLog(ms, new String[] {
+ "clear D1 11",
"clear1 C1 54",
// note there is no "clear2 B2 91" here because B2 doesn't depend on C1 anymore
"clear2 B1 8",
@@ -342,7 +413,7 @@
/**
* verifies that when updating a plain cell, depending (formula) cell cached values are cleared
- * only when the palin cell's value actually changes
+ * only when the plain cell's value actually changes
*/
public void testRedundantUpdate() {
MySheet ms = new MySheet();
@@ -369,6 +440,7 @@
ms.setCellValue("B1", 11); // value changing
confirmLog(ms, new String[] {
+ "clear B1 11",
"clear1 A1 25", // expect consuming formula cached result to get cleared
});
confirmEvaluate(ms, "A1", 24);
@@ -434,9 +506,55 @@
"hit D1 25",
"end A1 25",
});
-
}
+ public void testBlankCells() {
+
+
+ MySheet ms = new MySheet();
+
+ ms.setCellFormula("A1", "sum(B1:D4,B5:E6)");
+ ms.setCellValue("B1", 12);
+ ms.clearAllCachedResultValues();
+ ms.getAndClearLog();
+
+ confirmEvaluate(ms, "A1", 12);
+ confirmLog(ms, new String[] {
+ "start A1 SUM(B1:D4,B5:E6)",
+ "value B1 12",
+ "end A1 12",
+ });
+ ms.setCellValue("B6", 2);
+ ms.getAndClearLog();
+
+ confirmEvaluate(ms, "A1", 14);
+ confirmLog(ms, new String[] {
+ "start A1 SUM(B1:D4,B5:E6)",
+ "hit B1 12",
+ "hit B6 2",
+ "end A1 14",
+ });
+ ms.setCellValue("E4", 2);
+ ms.getAndClearLog();
+
+ confirmEvaluate(ms, "A1", 14);
+ confirmLog(ms, new String[] {
+ "hit A1 14",
+ });
+
+ ms.setCellValue("D1", 1);
+ ms.getAndClearLog();
+
+ confirmEvaluate(ms, "A1", 15);
+ confirmLog(ms, new String[] {
+ "start A1 SUM(B1:D4,B5:E6)",
+ "hit B1 12",
+ "hit D1 1",
+ "hit B6 2",
+ "end A1 15",
+ });
+ }
+
private static void confirmEvaluate(MySheet ms, String cellRefText, double expectedValue) {
ValueEval v = ms.evaluateCell(cellRefText);
assertEquals(NumberEval.class, v.getClass());
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java?rev=703063&r1=703062&r2=703063&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java Wed Oct 8 23:07:36 2008
@@ -127,18 +127,21 @@
confirmEvaluation(264, evaluatorA, cell);
// change [wbB]BSheet1!B3 (from 50 to 60)
- bSheet1.getRow(2).getCell(1).setCellValue(60);
- evaluatorB.setCachedPlainValue(bSheet1, 2, 1, new NumberEval(60));
+ HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
+ cellB3.setCellValue(60);
+ evaluatorB.notifyUpdateCell(cellB3);
confirmEvaluation(274, evaluatorA, cell);
// change [wbA]ASheet1!A3 (from 100 to 80)
- aSheet1.getRow(2).getCell(0).setCellValue(80);
- evaluatorA.setCachedPlainValue(aSheet1, 2, 0, new NumberEval(80));
+ HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
+ cellA3.setCellValue(80);
+ evaluatorA.notifyUpdateCell(cellA3);
confirmEvaluation(234, evaluatorA, cell);
// change [wbA]AnotherSheet!A1 (from 2 to 3)
- wbA.getSheetAt(1).getRow(0).getCell(0).setCellValue(3);
- evaluatorA.setCachedPlainValue(wbA.getSheetAt(1), 0, 0, new NumberEval(3));
+ HSSFCell cellA1 = wbA.getSheetAt(1).getRow(0).getCell(0);
+ cellA1.setCellValue(3);
+ evaluatorA.notifyUpdateCell(cellA1);
confirmEvaluation(235, evaluatorA, cell);
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org