Using Apache POI HSSF, how can I refresh all formula cells at once?

37
votes

I am filling cells of an Excel file using Apache POI, and there are a lot of formula cells in the document. However, their values are not refreshed when I open the document in Excel.

It's my understanding that I need to use a FormulaEvaluator to refresh formula cells. Is there a way, though, to update all formula cells at once? There are a lot of them, and while making an exhaustive list is not out of question, it's certainly not something I'm very willing to do.

2
javaapache-poi

2 Answers

88
votes

Sure. Refreshing all the formulas in a workbook is possibly the more typical use case anyway.

If you're using HSSF, call evaluatorAllFormulaCells:

 HSSFFormulaEvaluator.evaluateAllFormulaCells(hssfWorkbook)

If you're using XSSF, call evaluatorAllFormulaCells:

 XSSFFormulaEvaluator.evaluateAllFormulaCells(xssfWorkbook)

More details are available on the poi website