TXlsFile.RecalcVersion Property
Defines which version of Excel recalculation engine will be saved in the file. This affects how Excel will recalculate the file on open.
If you set this property for example to Excel2007, then any Excel version newer than Excel 2007 will recalculate all formulas when you load the file, so it will modify the workbook and will ask for saving changes when closing. Even if you just open and close the file. Note that this will only happen if there are formulas on the sheet.
If there are any formulas that FlexCel can't recalculate, you should set this property to AlwaysRecalc. For example, if you have the formula:
"=GETPIVOTDATA("a";"b")"
FlexCel will return #NAME?, as it doesn't implement this function. If you open this file on Excel and RecalcVersion was not AlwaysRecalc when saving, Excel will not calculate it and will show also #NAME?. If RecalcVersion was AlwaysRecalc, Excel will show the right answer, but will ask for saving the file when closing.
Syntax
Unit: FlexCel.XlsAdapter
property TXlsFile.RecalcVersion: TRecalcVersion
Examples
The following code:
xls := TXlsFile.Create(true); //We can't change the recalcmode once we have opened or created the file.
try
xls.RecalcMode := TRecalcMode.Manual; //So the sheet is not recalculated before saving.
xls.NewFile(1, TExcelFileFormat.v2021); //Create the file *after* recalcmode was changed.
xls.RecalcVersion := TRecalcVersion.Excel2021; //So Excel equal or older than 2021 won't recalculate either.
xls.SetCellValue(1, 1, TFormula.Create('= 1 + 1', 3)); //Basic math...
xls.Save(OutFileName);
finally
xls.Free;
end;
will create a file with a formula "1 + 1" and result = 3 on cell A1. If RecalcVersion was TRecalcVersion.AlwaysRecalc, Excel would show the correct value when opening.