How can I solve my problem in Apache Poi and Excel

I am using Apache Poi and Java for 开发者_如何学JAVAExcel manipulation. I have modified some of the cell in Excel file by programmatically using Java.

After that, when I open that Excel file manually for seeing that update. After seeing, when I try to close the Excel file it again ask me like "do you want to save the changes you made to test.xls file.

If I press yes button, then only I can able to read the formula cell values programmatically further. Otherwise, if I access the formula cell value it returns 0 value.

How can I resolve this problem?

By default, POI reads the cached response to the formula when you read that cell. When you open the file with Excel, it will do the calculations. Then when you save the file the responses are saved along. Then when you read it with poi, you get the right answers.

If you want the 'correct' answer you need to calculate the formula response. Last time I needed to do this I had to write my own parser, but now there's this : http://poi.apache.org/spreadsheet/eval.html.

Or in short:

Workbook wb = new HSSFWorkbook(inputstream);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
... snip ...
CellValue value = evaluator.evaluate(cellWithFormula);

Can't yet post comments so i'll leave a new answer. Joeri answer is correct for just one Cell

For multiple cell it is more efficient to use the folowing :

FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
fe.evaluateAll();

Which will in fact update the cached formula value.

Then to get them you'll do as you would for any other cell, for example :

cell.getCellStringValue();

This method is worth using if you have to reevaluate an entire workbook.

Take note that at the day i'm writing only the POI 3.10-beta1 version of POI can evaluate formulas using dates