Bug 51422

Summary: Support using RecalcIdRecord to trigger a full formula recalculation on load
Product: POI Reporter: Nick Burch <apache>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: PC   
OS: Linux   

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?