View | Details | Raw Unified | Return to bug 50209
Collapse All | Expand All

(-)org/apache/poi/ss/formula/eval/AreaEvalBase.java (+9 lines)
Lines 114-117 Link Here
114
	public int getWidth() {
114
	public int getWidth() {
115
		return _lastColumn-_firstColumn+1;
115
		return _lastColumn-_firstColumn+1;
116
	}
116
	}
117
	
118
	/**
119
	 * Added 2011-06 by UW - IconParc for jumping over nested subtotals
120
	 * @author Ulrich Wenzel / IconParc GmbH
121
	 * @param row
122
	 * @param col
123
	 * @return
124
	 */
125
	public abstract int getFunctionIndex( int row, int col );
117
}
126
}
(-)org/apache/poi/ss/formula/WorkbookEvaluator.java (-1 / +17 lines)
Lines 202-208 Link Here
202
		int sheetIndex = getSheetIndex(cell.getSheet());
202
		int sheetIndex = getSheetIndex(cell.getSheet());
203
		_cache.notifyDeleteCell(_workbookIx, sheetIndex, cell);
203
		_cache.notifyDeleteCell(_workbookIx, sheetIndex, cell);
204
	}
204
	}
205
	
205
206
	private int getSheetIndex(EvaluationSheet sheet) {
206
	private int getSheetIndex(EvaluationSheet sheet) {
207
		Integer result = _sheetIndexesBySheet.get(sheet);
207
		Integer result = _sheetIndexesBySheet.get(sheet);
208
		if (result == null) {
208
		if (result == null) {
Lines 620-623 Link Here
620
	public FreeRefFunction findUserDefinedFunction(String functionName) {
620
	public FreeRefFunction findUserDefinedFunction(String functionName) {
621
		return _udfFinder.findFunction(functionName);
621
		return _udfFinder.findFunction(functionName);
622
	}
622
	}
623
	
624
	/**
625
	 * @author Ulrich Wenzel / IconParc GmbH
626
	 * Added 2011-06 for jumping over nested subtotals
627
	 * @param srcCell
628
	 * @return functionIndex
629
	 */
630
	public int getFunctionIndex( EvaluationCell srcCell ){
631
		Ptg[] ptgs = _workbook.getFormulaTokens(srcCell);
632
		int index = -1;
633
		if( ptgs.length > 0  &&  ptgs[ptgs.length-1] instanceof FuncVarPtg){
634
			FuncVarPtg fVar = (FuncVarPtg)ptgs[ptgs.length-1];
635
			index = fVar.getFunctionIndex();
636
		}
637
		return index;
638
	}
623
}
639
}
(-)org/apache/poi/ss/formula/LazyAreaEval.java (-1 / +13 lines)
Lines 73-79 Link Here
73
		int absColIx = getFirstColumn() + columnIndex;
73
		int absColIx = getFirstColumn() + columnIndex;
74
		return new LazyAreaEval(getFirstRow(), absColIx, getLastRow(), absColIx, _evaluator);
74
		return new LazyAreaEval(getFirstRow(), absColIx, getLastRow(), absColIx, _evaluator);
75
	}
75
	}
76
76
	
77
	public String toString() {
77
	public String toString() {
78
		CellReference crA = new CellReference(getFirstRow(), getFirstColumn());
78
		CellReference crA = new CellReference(getFirstRow(), getFirstColumn());
79
		CellReference crB = new CellReference(getLastRow(), getLastColumn());
79
		CellReference crB = new CellReference(getLastRow(), getLastColumn());
Lines 87-90 Link Here
87
		sb.append("]");
87
		sb.append("]");
88
		return sb.toString();
88
		return sb.toString();
89
	}
89
	}
90
	
91
	/**
92
	 * @author Ulrich Wenzel / IconParc GmbH
93
	 * Added 2011-06 for jumping over nested subtotals
94
	 * @param row
95
	 * @param col
96
	 * @return functionIndex
97
	 */
98
	public int getFunctionIndex( int row, int col ){
99
		int functionIndex = _evaluator.getFunctionIndex( row, col );
100
		return functionIndex;
101
	}
90
}
102
}
(-)org/apache/poi/ss/formula/functions/Subtotal.java (-6 / +112 lines)
Lines 17-27 Link Here
17
17
18
package org.apache.poi.ss.formula.functions;
18
package org.apache.poi.ss.formula.functions;
19
19
20
import org.apache.poi.ss.formula.eval.AreaEvalBase;
21
import org.apache.poi.ss.formula.eval.BlankEval;
20
import org.apache.poi.ss.formula.eval.ErrorEval;
22
import org.apache.poi.ss.formula.eval.ErrorEval;
21
import org.apache.poi.ss.formula.eval.EvaluationException;
23
import org.apache.poi.ss.formula.eval.EvaluationException;
24
import org.apache.poi.ss.formula.eval.MissingArgEval;
25
import org.apache.poi.ss.formula.eval.NotImplementedException;
26
import org.apache.poi.ss.formula.eval.NumberEval;
22
import org.apache.poi.ss.formula.eval.OperandResolver;
27
import org.apache.poi.ss.formula.eval.OperandResolver;
23
import org.apache.poi.ss.formula.eval.ValueEval;
28
import org.apache.poi.ss.formula.eval.ValueEval;
24
import org.apache.poi.ss.formula.eval.NotImplementedException;
25
29
26
/**
30
/**
27
 * Implementation for the Excel function SUBTOTAL<p>
31
 * Implementation for the Excel function SUBTOTAL<p>
Lines 56-61 Link Here
56
 * @author Paul Tomlin &lt; pault at bulk sms dot com &gt;
60
 * @author Paul Tomlin &lt; pault at bulk sms dot com &gt;
57
 */
61
 */
58
public class Subtotal implements Function {
62
public class Subtotal implements Function {
63
	
64
	public static final int FUNC_SUBTOTAL = 344; //see FunctionEval.produceFunctions();
65
	public static final int COUNT = 2; 
66
	public static final int COUNTA = 3; 
59
67
60
	private static Function findFunction(int functionCode) throws EvaluationException {
68
	private static Function findFunction(int functionCode) throws EvaluationException {
61
		switch (functionCode) {
69
		switch (functionCode) {
Lines 83-100 Link Here
83
			return ErrorEval.VALUE_INVALID;
91
			return ErrorEval.VALUE_INVALID;
84
		}
92
		}
85
93
86
		Function innerFunc;
94
		Function innerFunc; 
95
		int functionCode = -1;
87
		try {
96
		try {
88
			ValueEval ve = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
97
			ValueEval ve = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
89
			int functionCode = OperandResolver.coerceValueToInt(ve);
98
			functionCode = OperandResolver.coerceValueToInt(ve);
90
			innerFunc = findFunction(functionCode);
99
			innerFunc = findFunction(functionCode);
91
		} catch (EvaluationException e) {
100
		} catch (EvaluationException e) {
92
			return e.getErrorEval();
101
			return e.getErrorEval();
93
		}
102
		}
94
103
95
		ValueEval[] innerArgs = new ValueEval[nInnerArgs];
104
		/*
96
		System.arraycopy(args, 1, innerArgs, 0, nInnerArgs);
105
		 * @author Ulrich Wenzel / IconParc GmbH
106
		 */
107
		/*********************************************************/
108
		//Collect all values of all areas without nested subtotals
109
		/*********************************************************/
110
		DoubleList doubleList = new DoubleList();
111
		int count = 0;
112
		/************************************************/
113
		//Iterate over all areas
114
		/************************************************/
115
		for( int i=1; i < args.length; i++ ){
116
			AreaEvalBase lazyArea = (AreaEvalBase)args[i];
117
			int rowFirst = lazyArea.getFirstRow();
118
			int rowLast = lazyArea.getLastRow();
119
			int colFirst = lazyArea.getFirstColumn();
120
			int colLast = lazyArea.getLastColumn();
121
122
			for( int iRow = rowFirst; iRow <= rowLast; iRow++ ){
123
				for( int iCol = colFirst; iCol <= colLast; iCol++ ){
124
125
					int formulaIndex = lazyArea.getFunctionIndex( iRow, iCol );
126
					if( formulaIndex == FUNC_SUBTOTAL ){
127
						if( iRow == srcRowIndex  &&  iCol == srcColumnIndex ){ //create Self-Reference-Error
128
							ValueEval ret = lazyArea.getValue( iRow-rowFirst, iCol-colFirst );
129
							return ret;
130
						}
131
						continue;
132
					}
133
134
					ValueEval ret = lazyArea.getValue( iRow-rowFirst, iCol-colFirst );
135
136
					switch( functionCode ){
137
					case COUNT:
138
						if( (ret instanceof NumberEval ) ||  ret == MissingArgEval.instance)
139
							count++; // only missing arguments and numbers are counted
140
						break;
141
					case COUNTA:
142
						if( ret != BlankEval.instance)
143
							count++;  // Note - everything but BlankEval counts
144
						break;
145
					default:
146
						if( ret instanceof NumberEval )
147
							doubleList.add( ((NumberEval)ret).getNumberValue() );
148
						else if( ret instanceof ErrorEval )
149
							return ret;
150
					}
151
				}
152
			}
153
		}
154
		switch( functionCode ){
155
		case COUNT:
156
		case COUNTA:
157
			return new NumberEval( count ); 
158
		default:
159
		}
160
		try {
161
			double[] values = doubleList.toArray();
162
163
			return new NumberEval( ((AggregateFunction) innerFunc).evaluate( values ));
164
165
		} catch (EvaluationException e) {
166
			throw new RuntimeException( e );
167
		}
168
	}
169
	/**
170
	 * Ulrich Wenzel / IconParc GmbH 2011-06: Taken from MultiOperandNumericFunction
171
	 */
172
	private static class DoubleList {
173
		private double[] _array;
174
		private int _count;
175
176
		public DoubleList() {
177
			_array = new double[8];
178
			_count = 0;
179
		}
97
180
98
		return innerFunc.evaluate(innerArgs, srcRowIndex, srcColumnIndex);
181
		public double[] toArray() {
182
			if(_count < 1) {
183
				return MultiOperandNumericFunction.EMPTY_DOUBLE_ARRAY;
184
			}
185
			double[] result = new double[_count];
186
			System.arraycopy(_array, 0, result, 0, _count);
187
			return result;
188
		}
189
190
		private void ensureCapacity(int reqSize) {
191
			if(reqSize > _array.length) {
192
				int newSize = reqSize * 3 / 2; // grow with 50% extra
193
				double[] newArr = new double[newSize];
194
				System.arraycopy(_array, 0, newArr, 0, _count);
195
				_array = newArr;
196
			}
197
		}
198
199
		public void add(double value) {
200
			ensureCapacity(_count + 1);
201
			_array[_count] = value;
202
			_count++;
203
		}
99
	}
204
	}
205
100
}
206
}
(-)org/apache/poi/ss/formula/SheetRefEvaluator.java (+18 lines)
Lines 18-23 Link Here
18
package org.apache.poi.ss.formula;
18
package org.apache.poi.ss.formula;
19
19
20
import org.apache.poi.ss.formula.eval.ValueEval;
20
import org.apache.poi.ss.formula.eval.ValueEval;
21
import org.apache.poi.ss.usermodel.Cell;
21
/**
22
/**
22
 *
23
 *
23
 *
24
 *
Lines 53-56 Link Here
53
		}
54
		}
54
		return _sheet;
55
		return _sheet;
55
	}
56
	}
57
	
58
	/**
59
	 * @author Ulrich Wenzel / IconParc GmbH
60
	 * Added 2011-06 for jumping over nested Subtotals
61
	 * @param rowIndex
62
	 * @param columnIndex
63
	 * @return
64
	 */
65
	public int getFunctionIndex( int rowIndex, int columnIndex ){
66
		EvaluationCell cell = getSheet().getCell( rowIndex, columnIndex );
67
68
		if( cell == null  ||  cell.getCellType() != Cell.CELL_TYPE_FORMULA )
69
			return -1;
70
		
71
		int ret = _bookEvaluator.getFunctionIndex( cell );
72
		return ret;
73
	}
56
}
74
}

Return to bug 50209