I'm experiencing big problems using ExcelPackage, because sometimes the formulas are not updated (even if I remove the value).
I've tried using ExcelInterop to save xlsx as xls, thinking that this could resolve the issue, but it didn't.
The way I've found to resolve it is by pressing CTRL + ALT + F9. The user will not like doing it everytime, so, I want to do it programatically. Do you know any effective way to ensure all xlsx formulas are updated?
Call Application.CalculateFull from a VBA macro (or over the COM object)
I have found this to work for me before:
for (int iWorksheet = 1; iWorksheet <= workbook.Worksheets.Count; iWorksheet++)
{
Worksheet ws = ((Worksheet)workbook.Worksheets[iWorksheet]);
ws.UsedRange.Formula = ws.UsedRange.Formula;
ws.Calculate();
}
Calculate() was not necessary there.SpreadsheetGear for .NET can open, calculate and save xls and xlsx workbooks without Excel and tends to be easier to work with from .NET applications because it is entirely .NET code (written in C#).
You can see some examples here and download the free trial here if you want to try it out.
Disclaimer: I own SpreadsheetGear LLC
You can always set Application.Calculation = xlAutomatic. That way you don't rely on your own code to perform the calculation and it's called only when needed.
Note that this setting affects the entire Excel instance. So if your client doesn't want other workbooks work like this - they should open it in a separate Excel instance.
In C#, use Application.Calculate() for evaluating all open workbooks, Workbook.Calculate() in order to evaluate a workbook or Worksheet.Calculate() for a worksheet.
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.calculate.aspx
