Author: centic
Date: Tue Nov 2 13:17:39 2021
New Revision: 1894675
URL: http://svn.apache.org/viewvc?rev=1894675&view=rev
Log:
Optimize formula evaluation of row-references
We currently walk 1 million rows for every formula which
uses a column-reference like "$A"
Execution time of test-case TestVlookup.testFullColumnAreaRef61841
went from more than 16 seconds to around 2 seconds
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/LazyAreaEval.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/LazyAreaEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/LazyAreaEval.java?rev=1894675&r1=1894674&r2=1894675&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/LazyAreaEval.java
(original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/LazyAreaEval.java Tue
Nov 2 13:17:39 2021
@@ -37,7 +37,7 @@ final class LazyAreaEval extends AreaEva
public LazyAreaEval(int firstRowIndex, int firstColumnIndex, int
lastRowIndex,
int lastColumnIndex, SheetRangeEvaluator evaluator) {
- super(evaluator, firstRowIndex, firstColumnIndex, lastRowIndex,
lastColumnIndex);
+ super(evaluator, firstRowIndex, firstColumnIndex,
evaluator.adjustRowNumber(lastRowIndex), lastColumnIndex);
_evaluator = evaluator;
}
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java?rev=1894675&r1=1894674&r2=1894675&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
Tue Nov 2 13:17:39 2021
@@ -25,7 +25,7 @@ import org.apache.poi.ss.formula.eval.Va
final class SheetRangeEvaluator implements SheetRange {
private final int _firstSheetIndex;
private final int _lastSheetIndex;
- private SheetRefEvaluator[] _sheetEvaluators;
+ private final SheetRefEvaluator[] _sheetEvaluators;
public SheetRangeEvaluator(int firstSheetIndex, int lastSheetIndex,
SheetRefEvaluator[] sheetEvaluators) {
if (firstSheetIndex < 0) {
@@ -41,7 +41,7 @@ final class SheetRangeEvaluator implemen
public SheetRangeEvaluator(int onlySheetIndex, SheetRefEvaluator
sheetEvaluator) {
this(onlySheetIndex, onlySheetIndex, new SheetRefEvaluator[]
{sheetEvaluator});
}
-
+
public SheetRefEvaluator getSheetEvaluator(int sheetIndex) {
if (sheetIndex < _firstSheetIndex || sheetIndex > _lastSheetIndex) {
throw new IllegalArgumentException("Invalid SheetIndex: " +
sheetIndex +
@@ -49,7 +49,7 @@ final class SheetRangeEvaluator implemen
}
return _sheetEvaluators[sheetIndex-_firstSheetIndex];
}
-
+
public int getFirstSheetIndex() {
return _firstSheetIndex;
}
@@ -73,4 +73,26 @@ final class SheetRangeEvaluator implemen
public ValueEval getEvalForCell(int sheetIndex, int rowIndex, int
columnIndex) {
return getSheetEvaluator(sheetIndex).getEvalForCell(rowIndex,
columnIndex);
}
+
+ /**
+ * This method returns a lower row-number if it would lie outside the
row-boundaries of
+ * any sheet.
+ *
+ * This is used to optimize cases where very high number of rows would
be checked otherwise
+ * without any benefit as no such row exists anyway.
+ *
+ * @param rowIndex The 0-based row-index to check
+ * @return If the given index lies withing the max row number across
all sheets, it is returned.
+ * Otherwise, the highest used row number across all
sheets is returned.
+ */
+ public int adjustRowNumber(int rowIndex) {
+ int maxRowNum = rowIndex;
+
+ for (int i = _firstSheetIndex; i < _lastSheetIndex; i++) {
+ maxRowNum = Math.max(maxRowNum,
_sheetEvaluators[i].getLastRowNum());
+ }
+
+ // do not try to evaluate further than there are rows in any
sheet
+ return Math.min(rowIndex, maxRowNum);
+ }
}
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRefEvaluator.java?rev=1894675&r1=1894674&r2=1894675&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
Tue Nov 2 13:17:39 2021
@@ -56,8 +56,8 @@ final class SheetRefEvaluator {
}
/**
- * @param rowIndex
- * @param columnIndex
+ * @param rowIndex The 0-based row-index to check
+ * @param columnIndex The 0-based column-index to check
* @return whether cell at rowIndex and columnIndex is a subtotal
* @see org.apache.poi.ss.formula.functions.Subtotal
*/
@@ -83,10 +83,17 @@ final class SheetRefEvaluator {
* Used by functions that calculate differently depending on row
visibility, like some
* variations of SUBTOTAL()
* @see org.apache.poi.ss.formula.functions.Subtotal
- * @param rowIndex
+ * @param rowIndex The 0-based row-index to check
* @return true if the row is hidden
*/
public boolean isRowHidden(int rowIndex) {
return getSheet().isRowHidden(rowIndex);
}
+
+ /**
+ * @return The last used row in this sheet
+ */
+ public int getLastRowNum() {
+ return getSheet().getLastRowNum();
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
