|Summary:||Support using RecalcIdRecord to trigger a full formula recalculation on load|
|Product:||POI||Reporter:||Nick Burch <apache>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
Description Nick Burch 2011-06-22 21:20:29 UTC
Just got this back from the Microsoft docs team: In regards to your question about how to flag an .XLS file so that all formula fields will be recalculated the next time it's opened by Excel, there is not a record specifically for this purpose. However, there is one that can be used to trigger certain behavior in Excel that will accomplish the same results. Please take a look at MS-XLS section 2.4.215<http://msdn.microsoft.com/en-us/library/dd909116(office.12).aspx>. The dwBuildId value of the RecalcId record, when set to 0, will tell Excel that it needs to recalculate all formulas in the workbook the next time the file is opened. When the recalculation is complete, Excel will update dwBuildId to the correct value and future recalculations will be performed according to the Workbook Calculation settings. It is not recommended to change the dwBuildId to a value other than what Excel sets it to, or zero. We currently have something on the sheet to request formula recalculation, setForceFormulaRecalculation(boolean), which apparently doesn't always work. We should therefore probably add the recalcid trick in too
Comment 1 Yegor Kozlov 2011-06-27 15:55:44 UTC
Committed in r1140210 I added Workbook.setForceFormulaRecalculation. Users are recommended to use it instead of Sheet.setForceFormulaRecalculation. The latter works only on sheet level and may not work if formulas refer to other sheets or external sources. I tested in Excel 2003 and 2010 and the recalcid trick always worked. HSSF implements Workbook.setForceFormulaRecalculation via RecalcIdRecord. XSSF does it via CTCalcPr.calcId property which is direct counterpart of RecalcId. I also added some notes about formula recalculation on http://poi.apache.org/spreadsheet/eval.html Yegor
Comment 2 Nick Burch 2011-06-27 16:01:34 UTC
Thanks for tackling this! I think it'd probably make sense to add a getter too. That way, if you open a file from POI (or from the python xlrt, which is where this originally started!) you can easily check to see if the formula values are going to be there+valid. What do you think?
Comment 3 Yegor Kozlov 2011-06-30 15:54:30 UTC
Workbook.getForceFormulaRecalculation added in r1141585 Yegor (In reply to comment #2) > Thanks for tackling this! > > I think it'd probably make sense to add a getter too. That way, if you open a > file from POI (or from the python xlrt, which is where this originally > started!) you can easily check to see if the formula values are going to be > there+valid. What do you think?