Forcing Excel to recalculate worksheet formulas when you have changed values from code with the Open XML SDK from Microsoft

If you are using the Microsoft Office Open XML SDK to manipulate and write data into Excel spreadsheets, you might have noticed that there's one problem related to formulas: if you programmatically from C# code update a cell's value that is part of a formula in another cell, the value does not get updated when you open the sheet in Excel. Instead, you need to force Excel to recalculate the worksheet (or workbook) manually using the F9 key. To force a full recalculation, press Ctrl+Alt+Shift+F9, which does the trick.

Now, there are several other blog posts out there that talk about this issue and how to solve it from .NET code. Usually, the solution is to loop through all the cells on a sheet, and this works, but is inefficient, especially on larger workbooks.

However, there's an easy solution: Excel supports a special property in the workbook that forces a full recalculation whenever the Excel file is opened. The good news is that the property can also be set through the Open XML SDK, that is, from code.

The property is named ForceFullCalculation, and when this property is set, Excel will recalculate everything in the workbook. In addition, there's another property called FullCalculationOnLoad which instructs Excel to do full recalculation immediately after the spreadsheet is opened.

Here's an example of their usage in C#:

document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

Happy hacking!