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/08/07 23:49:08 UTC
DO NOT REPLY [Bug 45593] New: Formula "Sum(a1:g1)" not calculating
string values
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Summary: Formula "Sum(a1:g1)" not calculating string values
Product: POI
Version: 3.0
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: major
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: vinod.sangwan@ebusinessware.com
I have an excel file with following:
Row1, column a1 to e1 has value AAA.
Row2 Col1 has a formula "=sum(a1:e1)"
Row2 Col2 has a formula "=A1+B1+C1+D1+E1"
Now I read this excel sheet using POI, and modify the AAA values to 5.
And used the following to calculate formulas, but Row2Col1 calculate to 0,
where as Row2Col2 calculates to 30:
--------------------------
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets();
sheetNum++) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet, wb);
for(Iterator rit = sheet.rowIterator(); rit.hasNext();)
{
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for(Iterator cit = r.cellIterator();
cit.hasNext();) {
HSSFCell c = (HSSFCell)cit.next();
if(c.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
}
}
---------------------------------
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
--- Comment #11 from Vinod <vi...@ebusinessware.com> 2008-08-12 07:46:55 PST ---
Kindly update.
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
--- Comment #4 from Vinod <vi...@ebusinessware.com> 2008-08-08 04:12:29 PST ---
Created an attachment (id=22414)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=22414)
This is the output file
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Vinod <vi...@ebusinessware.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #8 from Vinod <vi...@ebusinessware.com> 2008-08-08 09:39:38 PST ---
The value of G1 is coming out to be 0 where as it should be 30. This is using
formula "=SUM(A1:F1)".
But instead of using this SUM formula, if I use "=A1+B1+C1+D1+E1", then it
works fine, see cell G2.
I took the source code of POI to find out that the if clause is not executed in
the following:
Class - org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator
Method - xlateRefStringEval
To test this I modified this method xlateRefStringEval
if ( (flags & REF_STRING_IS_PARSED) > 0) {
to
if ( true || (flags & REF_STRING_IS_PARSED) > 0) { // forcing to go in if block
and then the SUM formula worked.
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Vinod <vi...@ebusinessware.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |vinod.sangwan@ebusinessware.
| |com
--- Comment #6 from Vinod <vi...@ebusinessware.com> 2008-08-08 04:18:06 PST ---
The code attached is a simple java file('This is the code') with main method,
where we are replacing values of cells with 5, and the formula is not working.
Also find attached the input file ('This is the input file') and output file
('This is the output file').
Kindly suggest, what is wrong in here as my requirement is that I have to
dynamically change the Strings with actual values, and the formula should be
calculated.
Thanks,
Vinod
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Vinod <vi...@ebusinessware.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|INVALID |
--- Comment #10 from Vinod <vi...@ebusinessware.com> 2008-08-09 03:36:12 PST ---
The excel is evaluating G1 correctly, if you replace cell A1 to F1 with value 5
in the input file , the excel will correctly sum this to 30, but when we
replace the values using POI, the calculation comes out to be 0.
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |NEEDINFO
--- Comment #7 from Josh Micich <jo...@gildedtree.com> 2008-08-08 09:16:55 PST ---
I'm sorry but you'll have to be clearer about what you mean by 'the formula is
not working'. Is it the value actually calculated by POI evaluator or value
shown by the spreadsheet when it opens in Excel? (they are not guaranteed to be
the same) Which cell?
I just ran your attached code with the attached sheet. The following formula
values were calculated:
G1 -> 0.0
H1 -> 0.0
I1 -> 25.0
J1 -> 1.0
K1 -> 0.0
G2 -> 25.0
The same values are visible when I open the file in Excel (2007). Your attached
output file looks the same to me.
Can you be more specific please?
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |INVALID
--- Comment #12 from Josh Micich <jo...@gildedtree.com> 2008-08-19 01:14:42 PST ---
(In reply to comment #10)
Note that there is a difference between a text cell with value "5" and a
numeric cell with value 5.0. In the excel UI, the user can choose either text
or numeric format when entering cell values. Text values can be forced by
prepending a single quote (') to the entered text. For example '5 will create
a text value "5".
The sample code I added in comment #1 uses numeric cells:
cell.setCellValue(5.0);
... which Excel/SUM() handles 'normally'.
Your sample code (from attachment id=22415) uses text cells:
cell.setCellValue(new HSSFRichTextString("5"));
... which Excel/SUM() ignores.
I am still sure that POI is consistent with Excel's behaviour
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
--- Comment #13 from Vinod <vi...@ebusinessware.com> 2008-08-21 13:24:28 PST ---
Ok, got it. Now I am setting it as follows and it works fine.
----------------------
String outputStr = replaceVariable(inputStr);
try {
double kkk = Double.parseDouble(outputStr);
cell.setCellValue(kkk);
} catch (NumberFormatException e) {
// this is a string, so set as string
cell.setCellValue(new HSSFRichTextString(outputStr));
}----------------------
Thanks a lot for your help.
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
--- Comment #5 from Vinod <vi...@ebusinessware.com> 2008-08-08 04:14:21 PST ---
Created an attachment (id=22415)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=22415)
This is the code
This is the code which is not calculating the formulas correctly
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |WORKSFORME
--- Comment #1 from Josh Micich <jo...@gildedtree.com> 2008-08-08 00:30:53 PST ---
I'm having trouble reproducing this bug. I tried on version 3.1 and also svn
trunk, and both seem to be OK. Here is the code I used:
InputStream is = new FileInputStream("c:/temp/ex44593.xls");
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
for(int i=0; i<5; i++) {
row.getCell(i).setCellValue(5.0);
}
row = sheet.getRow(1);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
evaluator.setCurrentRow(row);
System.out.println(evaluator.evaluate(row.getCell(0)).getNumberValue());
System.out.println(evaluator.evaluate(row.getCell(1)).getNumberValue());
sheet.setForceFormulaRecalculation(true);
FileOutputStream fos = new FileOutputStream("c:/temp/ex44593-out.xls");
wb.write(fos);
fos.close();
----
The output is:
25.0
25.0
----
I tried to create the input spreadsheet according to your instructions. I'll
attach it for reference.
I'm closing the bug for the moment, but please re-open if you can clarify how
to reproduce the problem.
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
--- Comment #2 from Josh Micich <jo...@gildedtree.com> 2008-08-08 00:32:35 PST ---
Created an attachment (id=22409)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=22409)
Input spreadsheet
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |INVALID
--- Comment #9 from Josh Micich <jo...@gildedtree.com> 2008-08-08 17:11:43 PST ---
(In reply to comment #8)
> The value of G1 is coming out to be 0 where as it should be 30. This is using
> formula "=SUM(A1:F1)".
>
> But instead of using this SUM formula, if I use "=A1+B1+C1+D1+E1", then it
> works fine, see cell G2.
POI is just replicating Excel's behaviour here. Excel evaluates G1 to 0.00
too.
There are quite a few tricks/inconsistencies in the implicit type conversions
that Excel does while evaluating formulas. For example, sum("5", "5", "5",
"5", "5") = 25.
Go figure.
POI does not attempt to 'correct' these inconsistencies.
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Vinod <vi...@ebusinessware.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |NEW
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
--- Comment #3 from Vinod <vi...@ebusinessware.com> 2008-08-08 04:11:58 PST ---
Created an attachment (id=22413)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=22413)
This is the input file
--
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 45593] Formula "Sum(a1:g1)" not calculating string
values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45593
Vinod <vi...@ebusinessware.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|WORKSFORME |
--
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