Author: gwoolsey
Date: Mon Mar 18 19:09:11 2019
New Revision: 1855789
URL: http://svn.apache.org/viewvc?rev=1855789&view=rev
Log:
#60724 - Partial implementation for SUBTOTAL() 'ignore hidden rows' variations
The function still doesn't deal with auto-filtering, but it now handles
variations that should skip hidden rows.
Taught the evaluation framework to know about hidden rows similar to what was
already there for skipping subtotals within subtotal ranges.
Added unit test cases.
Modified:
poi/site/src/documentation/content/xdocs/changes.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationSheet.java
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationSheet.java
poi/trunk/src/java/org/apache/poi/ss/formula/LazyAreaEval.java
poi/trunk/src/java/org/apache/poi/ss/formula/LazyRefEval.java
poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/TwoDEval.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationSheet.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Count.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Counta.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Subtotal.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSubtotal.java
poi/trunk/test-data/spreadsheet/SubtotalsNested.xls
Modified: poi/site/src/documentation/content/xdocs/changes.xml
URL:
http://svn.apache.org/viewvc/poi/site/src/documentation/content/xdocs/changes.xml?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/site/src/documentation/content/xdocs/changes.xml (original)
+++ poi/site/src/documentation/content/xdocs/changes.xml Mon Mar 18 19:09:11
2019
@@ -28,6 +28,7 @@
<person id="DN" name="David North" email="[email protected]" />
<person id="DS" name="Dominik Stadler" email="[email protected]" />
<person id="GJS" name="Glen Stampoultzis" email="[email protected]"
/>
+ <person id="GW" name="Greg Woolsey" email="[email protected]" />
<person id="JM" name="Josh Micich" email="[email protected]" />
<person id="JO" name="Javen O'Neal" email="[email protected]" />
<person id="MJ" name="Marc Johnson" email="[email protected]" />
@@ -87,6 +88,9 @@
<release version="4.1.0" date="2019-02-??">
<actions>
+ <action dev="GW" type="add" fixes-bug="60724"
context="SS_Common">Implement 'ignore hidden rows' variations for existing
implemented variants</action>
+ <action dev="GW" type="fix" fixes-bug="63264"
context="SS_Common">Conditional Format rule evaluation calculates relative
references incorrectly</action>
+ <action dev="GW" type="fix" fixes-bug="61652" context="SS_Common">Fix
NPE in EDATE function when date evaluates to an invalid value</action>
<action dev="PD" type="fix" fixes-bug="62151" context="POIFS">Work
around illegal reflective access in Java 9+ when freeing buffers</action>
<action dev="PD" type="add" fixes-bug="63029" context="OPC">OPCPackage
Potentially clobbers files on close()</action>
<action dev="PD" type="add" fixes-bug="62980" context="SS_Common XSSF
HSSF">Make D* functions ignore case in headings </action>
Modified:
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationSheet.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationSheet.java
(original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationSheet.java
Mon Mar 18 19:09:11 2019
@@ -45,7 +45,17 @@ final class HSSFEvaluationSheet implemen
public int getLastRowNum() {
return _hs.getLastRowNum();
}
-
+
+ /* (non-Javadoc)
+ * @see org.apache.poi.ss.formula.EvaluationSheet#isRowHidden(int)
+ * @since POI 4.0.2
+ */
+ public boolean isRowHidden(int rowIndex) {
+ HSSFRow row = _hs.getRow(rowIndex);
+ if (row == null) return false;
+ return row.getZeroHeight();
+ }
+
@Override
public EvaluationCell getCell(int rowIndex, int columnIndex) {
HSSFRow row = _hs.getRow(rowIndex);
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationSheet.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationSheet.java Mon Mar
18 19:09:11 2019
@@ -48,4 +48,12 @@ public interface EvaluationSheet {
* @since POI 4.0.0
*/
public int getLastRowNum();
+
+ /**
+ * Used by SUBTOTAL and similar functions that have options to ignore
hidden rows
+ * @param rowIndex
+ * @return true if the row is hidden, false if not
+ * @since POI 4.0.2
+ */
+ public boolean isRowHidden(int rowIndex);
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/LazyAreaEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/LazyAreaEval.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/LazyAreaEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/LazyAreaEval.java Mon Mar 18
19:09:11 2019
@@ -94,4 +94,14 @@ final class LazyAreaEval extends AreaEva
SheetRefEvaluator _sre =
_evaluator.getSheetEvaluator(_evaluator.getFirstSheetIndex());
return _sre.isSubTotal(getFirstRow() + rowIndex, getFirstColumn() +
columnIndex);
}
+
+ /**
+ * @return whether the row at rowIndex is hidden
+ * @see org.apache.poi.ss.formula.eval.AreaEvalBase#isRowHidden(int)
+ */
+ public boolean isRowHidden(int rowIndex) {
+ // delegate the query to the sheet evaluator which has access to
internal ptgs
+ SheetRefEvaluator _sre =
_evaluator.getSheetEvaluator(_evaluator.getFirstSheetIndex());
+ return _sre.isRowHidden(getFirstRow() + rowIndex);
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/LazyRefEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/LazyRefEval.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/LazyRefEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/LazyRefEval.java Mon Mar 18
19:09:11 2019
@@ -47,10 +47,22 @@ public final class LazyRefEval extends R
return new LazyAreaEval(area, _evaluator);
}
+ /**
+ * @return true if the cell is a subtotal
+ */
public boolean isSubTotal() {
SheetRefEvaluator sheetEvaluator =
_evaluator.getSheetEvaluator(getFirstSheetIndex());
return sheetEvaluator.isSubTotal(getRow(), getColumn());
}
+
+ /**
+ * @return whether the row at rowIndex is hidden
+ */
+ public boolean isRowHidden() {
+ // delegate the query to the sheet evaluator which has access to
internal ptgs
+ SheetRefEvaluator _sre =
_evaluator.getSheetEvaluator(_evaluator.getFirstSheetIndex());
+ return _sre.isRowHidden(getRow());
+ }
public String toString() {
CellReference cr = new CellReference(getRow(), getColumn());
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java Mon Mar
18 19:09:11 2019
@@ -56,6 +56,8 @@ final class SheetRefEvaluator {
}
/**
+ * @param rowIndex
+ * @param columnIndex
* @return whether cell at rowIndex and columnIndex is a subtotal
* @see org.apache.poi.ss.formula.functions.Subtotal
*/
@@ -77,4 +79,14 @@ final class SheetRefEvaluator {
return subtotal;
}
+ /**
+ * 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
+ * @return true if the row is hidden
+ */
+ public boolean isRowHidden(int rowIndex) {
+ return getSheet().isRowHidden(rowIndex);
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/TwoDEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/TwoDEval.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/TwoDEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/TwoDEval.java Mon Mar 18
19:09:11 2019
@@ -19,6 +19,7 @@ package org.apache.poi.ss.formula;
import org.apache.poi.ss.formula.eval.AreaEval;
import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.Subtotal;
/**
* Common interface of {@link AreaEval} and {@link
org.apache.poi.ss.formula.eval.AreaEvalBase},
@@ -64,5 +65,13 @@ public interface TwoDEval extends ValueE
* @return true if the cell at row and col is a subtotal
*/
boolean isSubTotal(int rowIndex, int columnIndex);
+
+ /**
+ *
+ * @param rowIndex
+ * @return true if the row is hidden
+ * @see Subtotal
+ */
+ boolean isRowHidden(int rowIndex);
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java Mon Mar
18 19:09:11 2019
@@ -146,4 +146,11 @@ public abstract class AreaEvalBase imple
return false;
}
+ /**
+ * @return false by default, meaning all rows are calculated
+ * @see org.apache.poi.ss.formula.TwoDEval#isRowHidden(int)
+ */
+ public boolean isRowHidden(int rowIndex) {
+ return false;
+ }
}
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationSheet.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationSheet.java
(original)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationSheet.java
Mon Mar 18 19:09:11 2019
@@ -62,6 +62,14 @@ final class ForkedEvaluationSheet implem
return _masterSheet.getLastRowNum();
}
+ /* (non-Javadoc)
+ * @see org.apache.poi.ss.formula.EvaluationSheet#isRowHidden(int)
+ * @since POI 4.0.2
+ */
+ public boolean isRowHidden(int rowIndex) {
+ return _masterSheet.isRowHidden(rowIndex);
+ }
+
@Override
public EvaluationCell getCell(int rowIndex, int columnIndex) {
RowColKey key = new RowColKey(rowIndex, columnIndex);
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
(original)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
Mon Mar 18 19:09:11 2019
@@ -162,7 +162,7 @@ public abstract class AggregateFunction
* @param func the function to wrap
* @return wrapped instance. The actual math is delegated to the argument
function.
*/
- /*package*/ static Function subtotalInstance(Function func) {
+ /*package*/ static Function subtotalInstance(Function func, boolean
countHiddenRows) {
final AggregateFunction arg = (AggregateFunction)func;
return new AggregateFunction() {
@Override
@@ -178,6 +178,9 @@ public abstract class AggregateFunction
return false;
}
+ public boolean isHiddenRowCounted() {
+ return countHiddenRows;
+ }
};
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Count.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Count.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Count.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Count.java Mon Mar
18 19:09:11 2019
@@ -86,6 +86,9 @@ public final class Count implements Func
}
};
+ /**
+ * matches hidden rows but not subtotals
+ */
private static final I_MatchPredicate subtotalPredicate = new
I_MatchAreaPredicate() {
public boolean matches(ValueEval valueEval) {
return defaultPredicate.matches(valueEval);
@@ -100,15 +103,33 @@ public final class Count implements Func
};
/**
+ * matches nither hidden rows or subtotals
+ */
+ private static final I_MatchPredicate subtotalVisibleOnlyPredicate = new
I_MatchAreaPredicate() {
+ public boolean matches(ValueEval valueEval) {
+ return defaultPredicate.matches(valueEval);
+ }
+
+ /**
+ * don't count cells that are subtotals
+ */
+ public boolean matches(TwoDEval areEval, int rowIndex, int
columnIndex) {
+ return !areEval.isSubTotal(rowIndex, columnIndex) &&
!areEval.isRowHidden(rowIndex);
+ }
+ };
+
+ /**
* Create an instance of Count to use in {@link Subtotal}
* <p>
* If there are other subtotals within argument refs (or nested
subtotals),
* these nested subtotals are ignored to avoid double counting.
* </p>
+ * @param includeHiddenRows true to include hidden rows in the aggregate,
false to skip them
+ * @return function
*
* @see Subtotal
*/
- public static Count subtotalInstance() {
- return new Count(subtotalPredicate );
+ public static Count subtotalInstance(boolean includeHiddenRows) {
+ return new Count(includeHiddenRows ? subtotalPredicate :
subtotalVisibleOnlyPredicate);
}
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Counta.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Counta.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Counta.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Counta.java Mon Mar
18 19:09:11 2019
@@ -80,7 +80,8 @@ public final class Counta implements Fun
return true;
}
};
- private static final I_MatchPredicate subtotalPredicate = new
I_MatchAreaPredicate() {
+
+ private static final I_MatchPredicate subtotalPredicate = new
I_MatchAreaPredicate() {
public boolean matches(ValueEval valueEval) {
return defaultPredicate.matches(valueEval);
}
@@ -93,8 +94,21 @@ public final class Counta implements Fun
}
};
- public static Counta subtotalInstance() {
- return new Counta(subtotalPredicate);
+ private static final I_MatchPredicate subtotalVisibleOnlyPredicate = new
I_MatchAreaPredicate() {
+ public boolean matches(ValueEval valueEval) {
+ return defaultPredicate.matches(valueEval);
+ }
+
+ /**
+ * don't count cells in rows that are hidden or subtotal cells
+ */
+ public boolean matches(TwoDEval areEval, int rowIndex, int
columnIndex) {
+ return !areEval.isSubTotal(rowIndex, columnIndex) && !
areEval.isRowHidden(rowIndex);
+ }
+ };
+
+ public static Counta subtotalInstance(boolean includeHiddenRows) {
+ return new Counta(includeHiddenRows ? subtotalPredicate :
subtotalVisibleOnlyPredicate);
}
}
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java
(original)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java
Mon Mar 18 19:09:11 2019
@@ -153,6 +153,14 @@ public abstract class MultiOperandNumeri
}
/**
+ * @return true if values in hidden rows are counted
+ * @see Subtotal
+ */
+ public boolean isHiddenRowCounted() {
+ return true;
+ }
+
+ /**
* Collects values from a single argument
*/
private void collectValues(ValueEval operand, DoubleList temp) throws
EvaluationException {
@@ -165,6 +173,7 @@ public abstract class MultiOperandNumeri
for (int rcIx = 0; rcIx < width; rcIx++) {
ValueEval ve = ae.getValue(sIx, rrIx, rcIx);
if (!isSubtotalCounted() && ae.isSubTotal(rrIx, rcIx))
continue;
+ if (!isHiddenRowCounted() && ae.isRowHidden(rrIx))
continue;
collectValue(ve, true, temp);
}
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Subtotal.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Subtotal.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Subtotal.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Subtotal.java Mon
Mar 18 19:09:11 2019
@@ -56,7 +56,17 @@ import java.util.List;
* <tr align='center'><td>9</td><td>SUM</td></tr>
* <tr align='center'><td>10</td><td>VAR *</td></tr>
* <tr align='center'><td>11</td><td>VARP *</td></tr>
- * <tr align='center'><td>101-111</td><td>*</td></tr>
+ * <tr align='center'><td>101</td><td>AVERAGE</td></tr>
+ * <tr align='center'><td>102</td><td>COUNT</td></tr>
+ * <tr align='center'><td>103</td><td>COUNTA</td></tr>
+ * <tr align='center'><td>104</td><td>MAX</td></tr>
+ * <tr align='center'><td>105</td><td>MIN</td></tr>
+ * <tr align='center'><td>106</td><td>PRODUCT</td></tr>
+ * <tr align='center'><td>107</td><td>STDEV</td></tr>
+ * <tr align='center'><td>108</td><td>STDEVP *</td></tr>
+ * <tr align='center'><td>109</td><td>SUM</td></tr>
+ * <tr align='center'><td>110</td><td>VAR *</td></tr>
+ * <tr align='center'><td>111</td><td>VARP *</td></tr>
* </table><br>
* * Not implemented in POI yet. Functions 101-111 are the same as functions
1-11 but with
* the option 'ignore hidden values'.
@@ -68,20 +78,28 @@ public class Subtotal implements Functio
private static Function findFunction(int functionCode) throws
EvaluationException {
switch (functionCode) {
- case 1: return
subtotalInstance(AggregateFunction.AVERAGE);
- case 2: return Count.subtotalInstance();
- case 3: return Counta.subtotalInstance();
- case 4: return subtotalInstance(AggregateFunction.MAX);
- case 5: return subtotalInstance(AggregateFunction.MIN);
- case 6: return
subtotalInstance(AggregateFunction.PRODUCT);
- case 7: return
subtotalInstance(AggregateFunction.STDEV);
+ case 1: return
subtotalInstance(AggregateFunction.AVERAGE, true);
+ case 2: return Count.subtotalInstance(true);
+ case 3: return Counta.subtotalInstance(true);
+ case 4: return subtotalInstance(AggregateFunction.MAX,
true);
+ case 5: return subtotalInstance(AggregateFunction.MIN,
true);
+ case 6: return
subtotalInstance(AggregateFunction.PRODUCT, true);
+ case 7: return
subtotalInstance(AggregateFunction.STDEV, true);
case 8: throw new
NotImplementedFunctionException("STDEVP");
- case 9: return subtotalInstance(AggregateFunction.SUM);
+ case 9: return subtotalInstance(AggregateFunction.SUM,
true);
case 10: throw new
NotImplementedFunctionException("VAR");
case 11: throw new
NotImplementedFunctionException("VARP");
- }
- if (functionCode > 100 && functionCode < 112) {
- throw new NotImplementedException("SUBTOTAL - with
'exclude hidden values' option");
+ case 101: return
subtotalInstance(AggregateFunction.AVERAGE, false);
+ case 102: return Count.subtotalInstance(false);
+ case 103: return Counta.subtotalInstance(false);
+ case 104: return
subtotalInstance(AggregateFunction.MAX, false);
+ case 105: return
subtotalInstance(AggregateFunction.MIN, false);
+ case 106: return
subtotalInstance(AggregateFunction.PRODUCT, false);
+ case 107: return
subtotalInstance(AggregateFunction.STDEV, false);
+ case 108: throw new
NotImplementedFunctionException("STDEVP SUBTOTAL with 'exclude hidden values'
option");
+ case 109: return
subtotalInstance(AggregateFunction.SUM, false);
+ case 110: throw new
NotImplementedFunctionException("VAR SUBTOTAL with 'exclude hidden values'
option");
+ case 111: throw new
NotImplementedFunctionException("VARP SUBTOTAL with 'exclude hidden values'
option");
}
throw EvaluationException.invalidValue();
}
@@ -93,9 +111,10 @@ public class Subtotal implements Functio
}
final Function innerFunc;
+ int functionCode = 0;
try {
ValueEval ve = OperandResolver.getSingleValue(args[0],
srcRowIndex, srcColumnIndex);
- int functionCode = OperandResolver.coerceValueToInt(ve);
+ functionCode = OperandResolver.coerceValueToInt(ve);
innerFunc = findFunction(functionCode);
} catch (EvaluationException e) {
return e.getErrorEval();
@@ -116,6 +135,9 @@ public class Subtotal implements Functio
if(lazyRefEval.isSubTotal()) {
it.remove();
}
+ if (functionCode > 100 &&
lazyRefEval.isRowHidden()) {
+ it.remove();
+ }
}
}
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
---
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java
(original)
+++
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java
Mon Mar 18 19:09:11 2019
@@ -45,6 +45,16 @@ final class SXSSFEvaluationSheet impleme
return _xs.getLastRowNum();
}
+ /* (non-Javadoc)
+ * @see org.apache.poi.ss.formula.EvaluationSheet#isRowHidden(int)
+ * @since POI 4.0.2
+ */
+ public boolean isRowHidden(int rowIndex) {
+ SXSSFRow row = _xs.getRow(rowIndex);
+ if (row == null) return false;
+ return row.getZeroHeight();
+ }
+
@Override
public EvaluationCell getCell(int rowIndex, int columnIndex) {
SXSSFRow row = _xs.getRow(rowIndex);
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
---
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
(original)
+++
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
Mon Mar 18 19:09:11 2019
@@ -52,6 +52,16 @@ final class XSSFEvaluationSheet implemen
return _xs.getLastRowNum();
}
+ /* (non-Javadoc)
+ * @see org.apache.poi.ss.formula.EvaluationSheet#isRowHidden(int)
+ * @since POI 4.0.2
+ */
+ public boolean isRowHidden(int rowIndex) {
+ final XSSFRow row = _xs.getRow(rowIndex);
+ if (row == null) return false;
+ return row.getZeroHeight();
+ }
+
/* (non-JavaDoc), inherit JavaDoc from EvaluationWorkbook
* @since POI 3.15 beta 3
*/
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSubtotal.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSubtotal.java?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSubtotal.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSubtotal.java
Mon Mar 18 19:09:11 2019
@@ -376,6 +376,15 @@ public final class TestSubtotal {
confirmExpectedResult(evaluator, "SUBTOTAL(COUNT;B2:B8,C2:C8)",
cellC2, 3.0);
confirmExpectedResult(evaluator, "SUBTOTAL(COUNTA;B2:B8,C2:C8)",
cellC3, 5.0);
+ // test same functions ignoring hidden rows over a copy of the same
data
+ cellC1 = sheet.getRow(11).getCell(3);
+ cellC2 = sheet.getRow(12).getCell(3);
+ cellC3 = sheet.getRow(13).getCell(3);
+ confirmExpectedResult(evaluator, "SUBTOTAL(SUM NO
HIDDEN;B22:B28;C22:C28)", cellC1, 17.0);
+ confirmExpectedResult(evaluator, "SUBTOTAL(COUNT NO
HIDDEN;B22:B28,C22:C28)", cellC2, 2.0);
+ confirmExpectedResult(evaluator, "SUBTOTAL(COUNTA NO
HIDDEN;B22:B28,C22:C28)", cellC3, 4.0);
+
+
workbook.close();
}
@@ -393,7 +402,6 @@ public final class TestSubtotal {
{ "SUBTOTAL(8,B2:B3)", NotImplementedException.class.getName() },
{ "SUBTOTAL(10,B2:B3)", NotImplementedException.class.getName() },
{ "SUBTOTAL(11,B2:B3)", NotImplementedException.class.getName() },
- { "SUBTOTAL(107,B2:B3)", NotImplementedException.class.getName() },
{ "SUBTOTAL(0,B2:B3)", null },
{ "SUBTOTAL(9)", FormulaParseException.class.getName() },
{ "SUBTOTAL()", FormulaParseException.class.getName() },
@@ -404,7 +412,7 @@ public final class TestSubtotal {
try {
a3.setCellFormula(f[0]);
fe.evaluateAll();
- assertEquals(FormulaError.VALUE.getCode(),
a3.getErrorCellValue());
+ assertEquals(f[0], FormulaError.VALUE.getCode(),
a3.getErrorCellValue());
} catch (Exception e) {
actualEx = e;
}
Modified: poi/trunk/test-data/spreadsheet/SubtotalsNested.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/SubtotalsNested.xls?rev=1855789&r1=1855788&r2=1855789&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
