Bug 45593 - Formula "Sum(a1:g1)" not calculating string values
Summary: Formula "Sum(a1:g1)" not calculating string values
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-08-07 14:49 UTC by Vinod
Modified: 2008-08-21 13:24 UTC (History)
1 user (show)



Attachments
Input spreadsheet (20.00 KB, application/vnd.ms-excel)
2008-08-08 00:32 UTC, Josh Micich
Details
This is the input file (13.50 KB, application/vnd.ms-excel)
2008-08-08 04:11 UTC, Vinod
Details
This is the output file (13.00 KB, application/vnd.ms-excel)
2008-08-08 04:12 UTC, Vinod
Details
This is the code (3.84 KB, text/x-java)
2008-08-08 04:14 UTC, Vinod
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Vinod 2008-08-07 14:49:07 UTC
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);
					}
				}
			}
		}
	}
---------------------------------
Comment 1 Josh Micich 2008-08-08 00:30:53 UTC
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.
Comment 2 Josh Micich 2008-08-08 00:32:35 UTC
Created attachment 22409 [details]
Input spreadsheet
Comment 3 Vinod 2008-08-08 04:11:58 UTC
Created attachment 22413 [details]
This is the input file
Comment 4 Vinod 2008-08-08 04:12:29 UTC
Created attachment 22414 [details]
This is the output file
Comment 5 Vinod 2008-08-08 04:14:21 UTC
Created attachment 22415 [details]
This is the code

This is the code which is not calculating the formulas correctly
Comment 6 Vinod 2008-08-08 04:18:06 UTC
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
Comment 7 Josh Micich 2008-08-08 09:16:55 UTC
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?
Comment 8 Vinod 2008-08-08 09:39:38 UTC
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.
Comment 9 Josh Micich 2008-08-08 17:11:43 UTC
(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.
Comment 10 Vinod 2008-08-09 03:36:12 UTC
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.
Comment 11 Vinod 2008-08-12 07:46:55 UTC
Kindly update.
Comment 12 Josh Micich 2008-08-19 01:14:42 UTC
(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



Comment 13 Vinod 2008-08-21 13:24:28 UTC
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.