search

felipenoris / XLSX.jl

Excel file reader and writer for the Julia language.
https://felipenoris.github.io/XLSX.jl/stable
Other
272 stars 57 forks source link

Some formulas get overwritten with values when editing existing workbook #159

Open bolleywall opened 3 years ago

bolleywall commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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

rmateus commented 2 years ago

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

felipenoris commented 2 years ago

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

nathanrboyer commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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.