please help to solve my problem in apache poi and excel

Question

Hi, i am using apache poi and java for excel manipulation.i have modified some of the cell in excel file by programatically using java.

After that, when i opening 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 programatically further.otherwise,if i access the formula cell value it returns 0 value....

How to resolve this problem....

Please guide me to get this solution.

Answer 1

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