Hi there,
I've got a reasonably complicated Excel spreadsheet that I want to automatically calculate a whole load of values for me.
I've got code similar to the following which does this:
var workbook = Workbook.Load(@"c:\temp\workbook.xlsx");
workbook.SuspendCalculations();
FillDataRow(workbook.Worksheets[0], dataReader);
workbook.CalculationMode = CalculationMode.Automatic;
workbook.ResumeCalculations();
workbook.Recalculate();
// code to extract calculated values
workbook.Save(@"c:\temp\workbook - results.xlsx);
Are you sure there are values not calculating correctly? It is possible MS Excel is updating some other field in the file, like the version of the program which saved the file, or the internal calculation order cache.
I believe so!! I've spent a whole day trying to get things to work without success... and I've now had to move code over to Excel COM Automation for the time being to keep the business happy :O(
So if I set the calculation mode to manual just prior to saving the workbook...
workbook.CalculationMode = CalculationMode.Manual;
And then open up in Excel, the values in the workbook are incorrect - I've got #N/A and in loads of places.
If I then force Excel to recalculate the workbook, everything updates to what I expect.