Index: org/apache/poi/ss/formula/eval/AreaEvalBase.java =================================================================== --- org/apache/poi/ss/formula/eval/AreaEvalBase.java (revision 1132610) +++ org/apache/poi/ss/formula/eval/AreaEvalBase.java (working copy) @@ -114,4 +114,13 @@ public int getWidth() { return _lastColumn-_firstColumn+1; } + + /** + * Added 2011-06 by UW - IconParc for jumping over nested subtotals + * @author Ulrich Wenzel / IconParc GmbH + * @param row + * @param col + * @return + */ + public abstract int getFunctionIndex( int row, int col ); } Index: org/apache/poi/ss/formula/WorkbookEvaluator.java =================================================================== --- org/apache/poi/ss/formula/WorkbookEvaluator.java (revision 1132610) +++ org/apache/poi/ss/formula/WorkbookEvaluator.java (working copy) @@ -202,7 +202,7 @@ int sheetIndex = getSheetIndex(cell.getSheet()); _cache.notifyDeleteCell(_workbookIx, sheetIndex, cell); } - + private int getSheetIndex(EvaluationSheet sheet) { Integer result = _sheetIndexesBySheet.get(sheet); if (result == null) { @@ -620,4 +620,20 @@ public FreeRefFunction findUserDefinedFunction(String functionName) { return _udfFinder.findFunction(functionName); } + + /** + * @author Ulrich Wenzel / IconParc GmbH + * Added 2011-06 for jumping over nested subtotals + * @param srcCell + * @return functionIndex + */ + public int getFunctionIndex( EvaluationCell srcCell ){ + Ptg[] ptgs = _workbook.getFormulaTokens(srcCell); + int index = -1; + if( ptgs.length > 0 && ptgs[ptgs.length-1] instanceof FuncVarPtg){ + FuncVarPtg fVar = (FuncVarPtg)ptgs[ptgs.length-1]; + index = fVar.getFunctionIndex(); + } + return index; + } } Index: org/apache/poi/ss/formula/LazyAreaEval.java =================================================================== --- org/apache/poi/ss/formula/LazyAreaEval.java (revision 1132610) +++ org/apache/poi/ss/formula/LazyAreaEval.java (working copy) @@ -73,7 +73,7 @@ int absColIx = getFirstColumn() + columnIndex; return new LazyAreaEval(getFirstRow(), absColIx, getLastRow(), absColIx, _evaluator); } - + public String toString() { CellReference crA = new CellReference(getFirstRow(), getFirstColumn()); CellReference crB = new CellReference(getLastRow(), getLastColumn()); @@ -87,4 +87,16 @@ sb.append("]"); return sb.toString(); } + + /** + * @author Ulrich Wenzel / IconParc GmbH + * Added 2011-06 for jumping over nested subtotals + * @param row + * @param col + * @return functionIndex + */ + public int getFunctionIndex( int row, int col ){ + int functionIndex = _evaluator.getFunctionIndex( row, col ); + return functionIndex; + } } Index: org/apache/poi/ss/formula/functions/Subtotal.java =================================================================== --- org/apache/poi/ss/formula/functions/Subtotal.java (revision 1132610) +++ org/apache/poi/ss/formula/functions/Subtotal.java (working copy) @@ -17,11 +17,15 @@ package org.apache.poi.ss.formula.functions; +import org.apache.poi.ss.formula.eval.AreaEvalBase; +import org.apache.poi.ss.formula.eval.BlankEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.NotImplementedException; +import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.OperandResolver; import org.apache.poi.ss.formula.eval.ValueEval; -import org.apache.poi.ss.formula.eval.NotImplementedException; /** * Implementation for the Excel function SUBTOTAL

@@ -56,6 +60,10 @@ * @author Paul Tomlin < pault at bulk sms dot com > */ public class Subtotal implements Function { + + public static final int FUNC_SUBTOTAL = 344; //see FunctionEval.produceFunctions(); + public static final int COUNT = 2; + public static final int COUNTA = 3; private static Function findFunction(int functionCode) throws EvaluationException { switch (functionCode) { @@ -83,18 +91,116 @@ return ErrorEval.VALUE_INVALID; } - Function innerFunc; + Function innerFunc; + int functionCode = -1; 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(); } - ValueEval[] innerArgs = new ValueEval[nInnerArgs]; - System.arraycopy(args, 1, innerArgs, 0, nInnerArgs); + /* + * @author Ulrich Wenzel / IconParc GmbH + */ + /*********************************************************/ + //Collect all values of all areas without nested subtotals + /*********************************************************/ + DoubleList doubleList = new DoubleList(); + int count = 0; + /************************************************/ + //Iterate over all areas + /************************************************/ + for( int i=1; i < args.length; i++ ){ + AreaEvalBase lazyArea = (AreaEvalBase)args[i]; + int rowFirst = lazyArea.getFirstRow(); + int rowLast = lazyArea.getLastRow(); + int colFirst = lazyArea.getFirstColumn(); + int colLast = lazyArea.getLastColumn(); + + for( int iRow = rowFirst; iRow <= rowLast; iRow++ ){ + for( int iCol = colFirst; iCol <= colLast; iCol++ ){ + + int formulaIndex = lazyArea.getFunctionIndex( iRow, iCol ); + if( formulaIndex == FUNC_SUBTOTAL ){ + if( iRow == srcRowIndex && iCol == srcColumnIndex ){ //create Self-Reference-Error + ValueEval ret = lazyArea.getValue( iRow-rowFirst, iCol-colFirst ); + return ret; + } + continue; + } + + ValueEval ret = lazyArea.getValue( iRow-rowFirst, iCol-colFirst ); + + switch( functionCode ){ + case COUNT: + if( (ret instanceof NumberEval ) || ret == MissingArgEval.instance) + count++; // only missing arguments and numbers are counted + break; + case COUNTA: + if( ret != BlankEval.instance) + count++; // Note - everything but BlankEval counts + break; + default: + if( ret instanceof NumberEval ) + doubleList.add( ((NumberEval)ret).getNumberValue() ); + else if( ret instanceof ErrorEval ) + return ret; + } + } + } + } + switch( functionCode ){ + case COUNT: + case COUNTA: + return new NumberEval( count ); + default: + } + try { + double[] values = doubleList.toArray(); + + return new NumberEval( ((AggregateFunction) innerFunc).evaluate( values )); + + } catch (EvaluationException e) { + throw new RuntimeException( e ); + } + } + /** + * Ulrich Wenzel / IconParc GmbH 2011-06: Taken from MultiOperandNumericFunction + */ + private static class DoubleList { + private double[] _array; + private int _count; + + public DoubleList() { + _array = new double[8]; + _count = 0; + } - return innerFunc.evaluate(innerArgs, srcRowIndex, srcColumnIndex); + public double[] toArray() { + if(_count < 1) { + return MultiOperandNumericFunction.EMPTY_DOUBLE_ARRAY; + } + double[] result = new double[_count]; + System.arraycopy(_array, 0, result, 0, _count); + return result; + } + + private void ensureCapacity(int reqSize) { + if(reqSize > _array.length) { + int newSize = reqSize * 3 / 2; // grow with 50% extra + double[] newArr = new double[newSize]; + System.arraycopy(_array, 0, newArr, 0, _count); + _array = newArr; + } + } + + public void add(double value) { + ensureCapacity(_count + 1); + _array[_count] = value; + _count++; + } } + } Index: org/apache/poi/ss/formula/SheetRefEvaluator.java =================================================================== --- org/apache/poi/ss/formula/SheetRefEvaluator.java (revision 1132610) +++ org/apache/poi/ss/formula/SheetRefEvaluator.java (working copy) @@ -18,6 +18,7 @@ package org.apache.poi.ss.formula; import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.Cell; /** * * @@ -53,4 +54,21 @@ } return _sheet; } + + /** + * @author Ulrich Wenzel / IconParc GmbH + * Added 2011-06 for jumping over nested Subtotals + * @param rowIndex + * @param columnIndex + * @return + */ + public int getFunctionIndex( int rowIndex, int columnIndex ){ + EvaluationCell cell = getSheet().getCell( rowIndex, columnIndex ); + + if( cell == null || cell.getCellType() != Cell.CELL_TYPE_FORMULA ) + return -1; + + int ret = _bookEvaluator.getFunctionIndex( cell ); + return ret; + } }