You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2008/05/22 13:17:03 UTC
DO NOT REPLY [Bug 45060] New: Formula written incorrectly produces
#VALUE error
https://issues.apache.org/bugzilla/show_bug.cgi?id=45060
Summary: Formula written incorrectly produces #VALUE error
Product: POI
Version: 3.0-dev
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P3
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: cpuidle@gmx.de
Blocks: 45041
This is a follow-on issue to bug 45041.
The following example creates a sheet with
two rows as input data for the final formula in row 3. The formula produces a
#VALUE error
in excel (tested on poi 3.1beta).
Visually the formula in Excel appears ok. Just pressing
"Enter" in the formula field again fixes the problem and field displays a
value. It seems
Excel is correcting the formula in the background- visually it does not change
by this
process.
import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
public
class Test
{
static int row = 0;
static HSSFSheet sheet;
static HSSFCellStyle
xlsDateStyle, xlsNumericStyle, xlsPercentStyle;
/**
* @param args
* @throws
Exception
*/
public static void main(String[] args) throws Exception
{
FileOutputStream
out = new FileOutputStream("d:\\test.xls");
HSSFWorkbook wb = new HSSFWorkbook();
sheet
= wb.createSheet("test");
// Excel-formatted date object
xlsDateStyle = wb.createCellStyle();
xlsDateStyle.setDataFormat(wb.createDataFormat().getFormat("dd.mm.yyyy"));
//
Excel-formatted number
xlsNumericStyle = wb.createCellStyle();
xlsNumericStyle.setDataFormat(wb.createDataFormat().getFormat("0.00"));
//
Excel-formatted percent object
xlsPercentStyle = wb.createCellStyle();
xlsPercentStyle.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
addRow(new
Date(0, 0, 1), 100.0);
addRow(new Date(1, 0, 1), -110.0);
HSSFRow r = sheet.createRow(row++);
//
create the IRR formula
short col = 2;
HSSFCell c = r.createCell(col++);
c.setCellStyle(xlsPercentStyle);
c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
c.setCellFormula("(1+IRR(SUMIF(A:A,ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A))),B:B),0))^365-1");
wb.write(out);
out.close();
}
private
static void addRow(Date date, double d)
{
HSSFRow r = sheet.createRow(row++);
short
col = 0;
HSSFCell c = r.createCell(col++);
c.setCellValue(date);
c.setCellStyle(xlsDateStyle);
c
= r.createCell(col++);
c.setCellValue(d);
c.setCellStyle(xlsNumericStyle);
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 45060] Token Class Transformation incorrect when
function expects 'reference' but arg is 'value'
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45060
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |kayamkulamkochunni@gmail.com
--- Comment #4 from Josh Micich <jo...@gildedtree.com> 2008-06-15 15:28:36 PST ---
*** Bug 45206 has been marked as a duplicate of this bug. ***
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 45060] Token Class Transformation incorrect when
function expects 'reference' but arg is 'value'
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45060
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|Formula written incorrectly |Token Class Transformation
|produces #VALUE error |incorrect when function
| |expects 'reference' but arg
| |is 'value'
--- Comment #2 from Josh Micich <jo...@gildedtree.com> 2008-05-22 10:25:02 PST ---
Initial investigation shows that Excel encodes (when it re-parses) the formula
different to POI. At first I thought the problem might have been the missing
tAttrVolatile token, but that seems to make no difference. The critical
problem is the difference in the operand class of two of the function tokens.
Excel augments to them to 'array'. I am speculating that the rule being
followed here is that when the function parameter is 'reference' and the actual
argument is 'value' that it should get changed to 'array'. However, I can't
see that explicity mentioned in the ooo document.
// The formula parse tokens
AttrPtg [volatile ] (POI does not encode this token)
IntPtg [1]
AreaPtg [A:A]
AreaPtg [A:A]
FuncVarPtg [MIN nArgs=1]
StringPtg [:]
ConcatPtg
AreaPtg [A:A]
FuncVarPtg [MAX nArgs=1]
ConcatPtg
FuncVarPtg [INDIRECT nArgs=1]
FuncVarPtg [ROW nArgs=1] >>> ptgClass = V should be A
AreaPtg [B:B]
FuncVarPtg [SUMIF nArgs=3] >>> ptgClass = V should be A
IntPtg [0]
FuncVarPtg [IRR nArgs=2]
class AddPtg
// Function metadata
#Columns: (index, name, minParams, maxParams, returnClass, paramClasses)
148 INDIRECT 1 2 R V V
8 ROW 0 1 V R
345 SUMIF 2 3 V R V R
62 IRR 1 2 V R
In this current example SUMIF()'s return class is 'value' but IRR() expects
'reference' for the first parameter. I guess this somehow necessitates the
transformation of the SUMIF() token to 'array'. From there, perhaps 'forced
array' state causes SUMIF()'s second arg to be transformed to 'array' too.
A simpler example "COLUMNS(PI())" also gets encoded by Excel with
transformation to 'array' operand class. Excel tolerates POI's incorrect
encoding in this case, so the mistake is not as clearly visible.
The fix for this is going to be in FormulaParser.setParameterRVA().
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 45060] Token Class Transformation incorrect when
function expects 'reference' but arg is 'value'
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45060
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED
--- Comment #3 from Josh Micich <jo...@gildedtree.com> 2008-05-27 23:30:09 PST ---
Fix applied in svn r660828.
It seems that POI's operand class transformation differed from a lot Excel's.
A new test case (TestRVA.java) shows many of those differences.
It seems that in the function metadata for IRR, the first parameter should be
operand class 'array' (not 'reference'). However, this was not nearly enough
to get all the test cases working.
Some changes were made to the Ptg class hierarchy (to make the distinction
between classified and base tokens clearer). Amongst other changes
FormulaParser was fixed to produce a parse tree on the fly while parsing (as
opposed to storing tokens in a flat list.
As of this fix, POI still has some differences with Excel with regard to
operand class transformation, but no significant examples have been found (i.e.
in the cases where POI gets it wrong, Excel still seems to read/evaluate the
formula OK).
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 45060] Formula written incorrectly produces #VALUE
error
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45060
--- Comment #1 from Andreas Goetz <cp...@gmx.de> 2008-05-22 04:17:33 PST ---
Created an attachment (id=21987)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21987)
test case
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org