44677
2008-03-26 06:01:08 +0000
COUNTA function gives incorrect result
2008-05-05 15:11:26 +0000
1
1
1
Unclassified
POI
HSSF
unspecified
PC
Windows XP
RESOLVED
FIXED
P2
normal
---
1
lpoublan
dev
2
oldest_to_newest
114983
0
lpoublan
2008-03-26 06:01:08 +0000
When I insert a COUNTA formula in a cell, the result is incorrect.
It seems it only returns the number of parameters.
When I open the excel file with excel (2003), if I edit the formula and just press enter (force recompute, do not modify the formula), the result becomes correct.
On the other hand, COUNT works fine.
Laurent
Example:
HSSFWorkbook wb=new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
sheet.createRow(0).createCell((short)0).setCellValue(10);
sheet.createRow(1).createCell((short)0).setCellValue(20);
sheet.createRow(2).createCell((short)0).setCellValue(true);
// the result is 2, which is ok (only numbers are taken into account)
sheet.createRow(3).createCell((short)0).setCellFormula("count(A1:A3)");
// the result is 1, it should be 3
sheet.createRow(4).createCell((short)0).setCellFormula("counta(A1:A3)");
114992
1
josh
2008-03-26 11:43:43 +0000
POI currently saves these formulas with tokens of tAreaV(0x45) instead of tAreaR (0x25). In my excel (2007) I observe *both* formulas to be wrong at first (A4=0, A5=1, until re-entered).
It looks like the problem is in FormulaParser.setParameterRVA(Node, int)
Commenting out the call to that method seems to fix this bug, but that is not a proper solution. Without that call, the token classes of all parsed formulas will stay at 'reference', which is probably wrong for other formulas.
This functionality is related to the 'Token Class Transformation' section (3.2.4) of excelfileformat.pdf. This logic seems to be partially implemented in POI but since there are no junits, it's probably not very stable yet.
116271
2
josh
2008-05-05 15:11:26 +0000
Re-tested in 3.1-beta1.
This was probably fixed in bug 44675 (the parameter operand
class should be 'reference' instead of 'value')