You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2021/11/02 13:17:39 UTC
svn commit: r1894675 - in
/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula: LazyAreaEval.java
SheetRangeEvaluator.java SheetRefEvaluator.java
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: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org