Hello Sapan, > Can you please let me know if this has been fixed in poi or if I can expect > a fix sometime soon.
Before we start talking about fixes, and schedules, we need to understand
what is going wrong. Your description thus far has not enabled me to create
a test case that can fail in the way you are describing. You haven't
attached an example file or java code, nor did you comment whether the
example code I wrote represented your use case. I have attempted to match
your description better with the following code (which also works):
// --------------------------------
InputStream is = new FileInputStream("nameTest.xls");
Workbook wb = WorkbookFactory.create(is); // works with both HSSF and XSSF
Sheet sheet = wb.getSheet("Sheet1");
// Verify some important details about the formula set-up:
Cell cellA1 = sheet.getRow(0).getCell(0);
Cell cellB1 = sheet.getRow(0).getCell(1);
Cell cellC2 = sheet.getRow(1).getCell(2);
Name name = wb.getNameAt(0);
assertEquals("5*C2*ESRAMDEB", cellA1.getCellFormula());
assertEquals("ESRAMDEB", name.getNameName());
assertEquals("Sheet1!$B$1", name.getRefersToFormula());
assertEquals(5.0, cellB1.getNumericCellValue(), 0.0);
assertEquals(2.0, cellC2.getNumericCellValue(), 0.0);
// Do the evaluation:
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
assertEquals(50.0, fe.evaluate(cellA1).getNumberValue(), 0.0);
// --------------------------------
The error text you describe matches (FormulaParser.java:443), and if this
has occurred, it suggests two things. Firstly, "evalName.isRange()" on line
440 has returned false even though your use case expects true. Secondly,
since the FormulaParser is being invoked, it seems likely that you are using
XSSF (with .xlsx files). Formulas are not re-parsed during execution in
HSSF; the only way to get this error on HSSF is by calling
cell.setCellFormula("5*C2 * ESRAMDEB"). Just to be sure, I saved
"nameTest.xls" as "nameTest.xlsx" and ran the above code again.
Relevant code
lines 440-444:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?annotate=729028
lines 119-126:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java?annotate=729028
lines 132-133:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java?annotate=729028
At the moment I am guessing that there is something unusual about your
spreadsheet that causes "evalName.isRange()" to return false when it should
return true.
Please upload a sample spreadsheet which exhibits this problem. Is the
example code I have written relevant? If not please post code that
reproduces the bug for you.
regards,
Josh
nameTest.xls
Description: MS-Excel spreadsheet
nameTest.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
