excel asks if i want to save changes on close xlsx with formulas

[Alexander Arkhipov]

Hello and thanks for excel writer library.
The minimal code to reproduce the issue is this:

using namespace SimpleXlsx;

    CWorkbook wb;
    CWorksheet& ws = wb.AddSheet( L"отчет" );
    ws.BeginRow( 23.25 );
    ws.AddCell( L"=SUM(C3:C4)" );
    ws.EndRow();
    wb.Save( "1.xlsx" );

It saves file, file can be opened normally, but after user closes file (no changes were made), excel asks if he should save changes. I attached example.

[E.Naumovich]

Very likely it is an issue of the not-100% implementation of the Excel internal structures in generated file. When Excel opens generated file, it prepared these structures automatically and then wants to save them. In you case it might be result of the formula calculation.
My suggestion is to save generated file from Excel if possible.

[Alexander Arkhipov]

It is possible, but i have to do it every time? Can it be fixed?

[E.Naumovich]

In general better to re-save generated file from Excel. There are no problems with reading for the Excel , but there are no guarantee concerning third party software. I have strange issues with some.
100% implementation of the xlsx for me looks problematic, however it is question to Alexandr Belyak.

[Alexandr Belyak]

"...but there are no guarantee concerning third party software. I have strange issues with some."
Is this software available for free? Can you provide examples of such software and corresponding xlsx files? I'm trying to improve the compatibility of the files generated by SimpleXlsxWriter. It may be possible to improve this situation further.

[E.Naumovich]

This is commercial software, and producer is not very cooperative in such matters.
There are chances that issue related to the resolved problem of the actual size of the sheet, but I cannot prove it at that moment.
It looked like this: I create new project in this software, it is using number of the xlsx files to fetch data. At creation everything looks fine. But if I save project and than reopen, it cannot fetch data again. So I started re-save xlsx automatically and insisted other users to do it.

[Alexandr Belyak]

Hello everyone and thanks to Alexander Arkhipov for the detailed description of the problem.
E.Naumovich is right with the cause and, I think, right with the proposed solution.
The fact is that Excel saves a cache with the results of formula calculations in the xlsx file. This is necessary to speed up the work.
SimpleXlsxWriter does not contain a formula calculator and does not fill in the corresponding cache in the xlsx file. There are no plans to add a formula calculator to the library yet.
Therefore, I believe that E.Naumovich suggested a good solution with resaving the file using Excel.

[Alexander Arkhipov]

Today i got some free time to investigate problem further.
I created my test 1.xlsx with SimpleXlsxWriter, copyed it, and saved using excel (i am using excel 2010).
Then i compared changes between these two files:
It appears that they differs in two files:
.xlsx\xl\workbook.xml

original <calcPr calcId="124519"/>
saved    <calcPr calcId="145621"/>

According to this calcid is a formulas engine id, if its lower than current, document formulas are recalculated. I think its OK to use 999999 to avoid recalculation due to newer Excel client program version.
.xlsx\xl\worksheets\sheet1.xml

original <c r="A1"><f>SUM(C3:C4)</f></c>
saved    <c r="A1"><f>SUM(C3:C4)</f><v>0</v></c> 

where v tag means computed formula value.

Sooooo. To make excel stop complaining about unsaved formulas its enough to save formula and a value inside a cell.

[Alexandr Belyak]

Thank you for the research and its detailed description. In general, you have discovered what E.Naumovich and I wrote about earlier.

I think its OK to use 999999 to avoid recalculation due to newer Excel client program version.

I think it's a dirty hack. Today it can work, and tomorrow an Excel update will be released with a detailed check of this value and when opening the file, a message will appear that the xlsx file is corrupted. It is not necessary to assume that Excel does not check parameter values well.
In addition, if you incorrectly calculate the value of the formula, you can seriously harm the end user.

Sooooo. To make excel stop complaining about unsaved formulas its enough to save formula and a value inside a cell.

This trick can be applied to simple formulas, but with large formulas there may be nuances, since you need to know the features of the Excel computing engine. Otherwise, as I wrote above, you can seriously harm the end user.

I think it's not worth making the changes you suggested to the library. But you have the right to modify the library yourself to suit your needs. By and large, you have already figured out the main things.

[Alexander Arkhipov]

Ok i understand your point. But my small project uses just formulas of type "=SUM("A1:C1")" so i will make some patches to calculate resulting value and store it.

[Alexandr Belyak]

Ok, thanks for understanding.
If you have any difficulties with modifying the library, write here, I will try to help.