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); } } } } } ---------------------------------

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.

Created attachment 22409 [details] Input spreadsheet

Created attachment 22413 [details] This is the input file

Created attachment 22414 [details] This is the output file

Created attachment 22415 [details] This is the code This is the code which is not calculating the formulas correctly

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

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?

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.

(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.

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.

Kindly update.

(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

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.