Created attachment 26253 [details] sample spreadsheet Sample spreadsheet: A1: 1 A2: =SUBTOTAL(9,A1) A3: =SUBTOTAL(9,A1:A2) Sample code; Workbook wb = WorkbookFactory.create(new FileInputStream("subtotal.xlsx")); FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); eval.evaluateFormulaCell(wb.getSheetAt(0).getRow(2).getCell(0)); FileOutputStream fout = new FileOutputStream("subtotal_output.xlsx"); wb.write(fout); fout.close(); If you execute this sample code, you will get subtotal_output.xlsx. When opening this file in Excel 2007, you will get next spreadsheet. A1: 1 A2: 1 A3: 2 Correctly, cell A3 should be 1. It seems that the cell containing SUBTOTAL (A2) is summed up while evaluating SUBTOTAL in A3, though it should be ignored.
For anyone coming back to this bug later, please see today's discussions on this bug on the user mailing list: http://mail-archives.apache.org/mod_mbox/poi-user/201011.mbox/%3Calpine.DEB.1.10.1011051553340.11599@urchin.earth.li%3E
Bugfix: Evaluation of Subtotals does not consider nested subtotals anymore - test-files and test-data contained in new-files.tar - java-subtotals.patch does not include test-files Attachment: java-subtotals.patch and new-files.tar Therefore: 1) Double-Values of referenced cells (not subtotals) are collected and used for evaluation in class Subtotal 2) The Method getFunctionIndex is added to classes LazyAreaEval/AreaEvalBase, SheetRefEvaluator and WorkbookEvaluator which seems to be the shortes path to the WorkbookEvaluator 3) Functions Count and CountA are implemented directly in Subtotal.
Created attachment 27117 [details] code, test and excel-sheet
Created attachment 27118 [details] patch
(In reply to comment #4) > Created attachment 27118 [details] > patch Fixed in r1150673 The fix is based on the proposed patch, but I chose a bit different strategy. Instead of pulling DoubleList and iteration logic from MultiOperandNumericFunction and thus duplicating code, it is better to inject this logic right into MultiOperandNumericFunction.collectValues. I think I found an elegant way how to do that. P.S. You patch assumes that the last ptg in nested cells is FuncVarPtg: + 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; + } It is not always so. Consider two use cases: SUBTOTAL(9, A1:A2) ptgs: [9, A1:A2, SUBTOTAL] SUBTOTAL(9, A1:A2) + 1 ptgs: [9, A1:A2, SUBTOTAL, 1, '+'] Your implementation ignores the second case which is wrong. The correct logic is to iterate over all ptgs and check if there is a FuncVarPtg for the SUBTOTAL function. Regards, Yegor