Some formulas get overwritten with values when editing existing workbook

[bolleywall]

So formulas are lost and in their place static values remain.

How to recreate:

1. In Excel, create a new workbook, enter e.g. =SECOND(NOW()) in each cell in A1:T20, save to Formulas.xlsx, close Excel
2. In Julia REPL, enter: using XLSX; XLSX.openxlsx("Formulas.xlsx", mode="rw") do xf; end
3. Re-open Formulas.xlsx in Excel. Press Ctrl+Alt+F9 to update formula values. Only a few cells will update their values, as most cells have lost their formulas and now only contain a static value.

[aleave]

I have the same issue and this is really a Major issue that should be very clearly be reported on the main page of the package. Anyone like me trying to use the package in a real work environment is going to be severely impacted by this and I am honestly shocked that this is not the case. Thankfully I saved a copy of my excel before experimenting with XLSX but it is then only by luck than I discovered this issue. Sorry for the harsh tone, but this is not "a bug"... this is a major issue and you need to make it very clear to potential users.

[felipenoris]

@aleave thanks for the input! I wrote a warning in the docs about it.

[rmateus]

Any prevision on when this issue will be solved? Thanks for this work

[felipenoris]

It is most likely that this feature will be removed in a future release.

[nathanrboyer]

Even if the formulas are still shown in the Excel file, sometimes I cannot get them to evaluate with the new numbers. I tried hitting Enter, Calculate Now, Calculate Sheet, and Refresh All, but the formula cells still show old values.

[PhyX-Meow]

It is most likely that this feature will be removed in a future release.

Does this mean in future release, one will not able to write to an xlsx file using XLSX.jl?

[felipenoris]

It is most likely that this feature will be removed in a future release.

Does this mean in future release, one will not able to write to an xlsx file using XLSX.jl?

What I meant is that the edit feature may be removed, which relates to opening an existing file and write to it using the flag mode="rw". Writing from a blank spreadsheet with mode="w" is a feature that will not be removed.