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);
I am submitting this issue to our development team and you will receive more information through the support case that has been opened for you.
Oh, sorry. I misunderstood your original post. I thought you were saying that when you open the file, the values looked the same but it prompted you to save anyway. This could be a bug with the calculation logic. I have forwarded this post to the Developer Support Manager and a DS engineer will be contacting you about this issue.
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.
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.