You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by gw...@apache.org on 2019/03/18 19:09:12 UTC
svn commit: r1855789 - in /poi: site/src/documentation/content/xdocs/
trunk/src/java/org/apache/poi/hssf/usermodel/
trunk/src/java/org/apache/poi/ss/formula/
trunk/src/java/org/apache/poi/ss/formula/eval/
trunk/src/java/org/apache/poi/ss/formula/eval/f...
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="dnorth@apache.org" />
<person id="DS" name="Dominik Stadler" email="centic@apache.org" />
<person id="GJS" name="Glen Stampoultzis" email="user@poi.apache.org" />
+ <person id="GW" name="Greg Woolsey" email="gwoolsey@apache.org" />
<person id="JM" name="Josh Micich" email="josh@apache.org" />
<person id="JO" name="Javen O'Neal" email="onealj@apache.org" />
<person id="MJ" name="Marc Johnson" email="mjohnson@apache.org" />
@@ -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: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org