[prev in list] [next in list] [prev in thread] [next in thread]
List: poi-user
Subject: Re: Name cell does not work in formula
From: Josh Micich <josh.micich () gmail ! com>
Date: 2009-01-30 19:34:36
Message-ID: a644352c0901301134q7675d24ao309e28764cb91306 () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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
[Attachment #5 (text/html)]
Hello Sapan,<br><br>> Can you please let me know if this has been fixed in poi or \
if I can expect<br> > a fix sometime soon.<br>
<br>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):<br> <br>// \
--------------------------------<br>InputStream is = new \
FileInputStream("nameTest.xls");<br>Workbook wb = \
WorkbookFactory.create(is); // works with both HSSF and XSSF<br>Sheet sheet = \
wb.getSheet("Sheet1");<br> // Verify some important details about the \
formula set-up:<br>Cell cellA1 = sheet.getRow(0).getCell(0);<br>Cell cellB1 = \
sheet.getRow(0).getCell(1);<br>Cell cellC2 = sheet.getRow(1).getCell(2);<br>Name name \
= wb.getNameAt(0);<br> assertEquals("5*C2*ESRAMDEB", \
cellA1.getCellFormula());<br>assertEquals("ESRAMDEB", \
name.getNameName());<br>assertEquals("Sheet1!$B$1", \
name.getRefersToFormula());<br>assertEquals(5.0, cellB1.getNumericCellValue(), \
0.0);<br> assertEquals(2.0, cellC2.getNumericCellValue(), 0.0);<br><br>// Do the \
evaluation:<br>FormulaEvaluator fe = \
wb.getCreationHelper().createFormulaEvaluator();<br>assertEquals(50.0, \
fe.evaluate(cellA1).getNumberValue(), 0.0);<br> // \
--------------------------------<br> <br>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.<br> <br>Relevant code<br>lines 440-444: <a \
href="http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/Formul \
aParser.java?annotate=729028">http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?annotate=729028</a><br>
lines 119-126: <a href="http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/po \
i/hssf/usermodel/HSSFEvaluationWorkbook.java?annotate=729028">http://svn.apache.org/vi \
ewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java?annotate=729028</a><br>
lines 132-133: <a href="http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apa \
che/poi/xssf/usermodel/XSSFEvaluationWorkbook.java?annotate=729028">http://svn.apache. \
org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java?annotate=729028</a><br>
<br>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.<br><br>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.<br> <br>regards,<br>Josh<br>
--001485f645609836ed0461b84b25--
["nameTest.xls" (application/vnd.ms-excel)]
["nameTest.xlsx" (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)]
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic