I found previous reports of this and tried setting both
workbook.Settings.ReCalculateOnOpen = false;
workbook.Settings.RecalculateBeforeSave = false;
but neither stopped the annoying “Save changes” prompt when closing an unchanged xlsx file
Finally I tried setting
workbook.Settings.CalcMode = CalcModeType.Manual;
and the prompting when closing files from Excel 2010 has stopped.
Yes, there are lots of formulas in my xlsx spreadsheet. No, there are not any date based formulas. All are simply sums of cells in the workbook. There are no outside references…
If anyone has any other thoughts regarding this issue please let me know.
Thanks for your posting and using Aspose.Cells for .NET.
Well, whenever there are formulas, Ms-Excel will automatically calculate them everytime if the calculation mode is set automatic.
This will then modify your file and Ms-Excel will prompt you to save modifications on closing the file. That’s why, you have to set the calculation mode to manual.
If you want to get rid of this prompt, you can remove all of your formulas and convert them into values instead.
You can do it using the Worksheet.Cells.RemoveFormulas() method, it will remove your formulas and write their final values instead.
I understand the underlying issue now. When I create the sheet using Aspose.Cells and a cell has a formula like “=SUM(A1:A2)”, when saving the workbook in Aspose that formula is not actually calculated unless the workbook.Settings.CalcMode = CalcModeType.Automatic. Correct?
When opening the workbook the first time in Excel none of the formulas show resulting values until I do “Calculate now” in Excel if I set “CalcModeType.Manual”.
I want results to show in the workbook when it is opened for the first time in Excel. And I want to retain the formulas rather than convert them to values.
Looks like the solution is:
workbook.CalculateFormula(true);//this renders the formula results within the cells when the workbook is opened so they do not appear empty
workbook.Settings.ReCalculateOnOpen = false;//this prevents the prompting to save changes when closing an unchanged document
Applying these settings lets me keep the formulas in the cells, display results for the formula when opened in Excel and prevents the annoying Excel prompt to save changes when closing the workbook.
I understand the underlying issue now. When I create the sheet using Aspose.Cells and a cell has a formula like "=SUM(A1:A2)", when saving the workbook in Aspose that formula is not actually calculated unless the workbook.Settings.CalcMode = CalcModeType.Automatic. Correct?
When opening the workbook the first time in Excel none of the formulas show resulting values until I do "Calculate now" in Excel if I set "CalcModeType.Manual".
I want results to show in the workbook when it is opened for the first time in Excel. And I want to retain the formulas rather than convert them to values.
Looks like the solution is:
workbook.CalculateFormula(true);//this renders the formula results within the cells when the workbook is opened so they do not appear empty
workbook.Settings.ReCalculateOnOpen = false;//this prevents the prompting to save changes when closing an unchanged document
Applying these settings lets me keep the formulas in the cells, display results for the formula when opened in Excel and prevents the annoying Excel prompt to save changes when closing the workbook.
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
I think, you are right. If your formula values are not changing then you can just set ReCalculateOnOpen false.
It will then retain the formulas and also avoid the Excel prompt.
So, you can use this solution for your needs. Let us know if you are still facing issues with this approach. We will investigate your issue further and help/advise you asap.
Jeff B: