Overwriting existing formula causes error when opening file in Excel

[nicojx]

Hello, apologies me again. :)

When I overwrite pre-existing formulas, I get an error when opening the workbook.

We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

Then:

Repairs to file.xlsx
Excel was able to open the file by repairing or removing the unreadable content.
Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

The error only appears when Excel has saved the workbook, not when it's only been saved by openxlsx2.

wb <- 
  wb_workbook()$
  add_worksheet()$
  add_formula(dims = "A1", x = "=\"test\"")$
  save("overwrite_formula.xlsx")

xl_open("overwrite_formula.xlsx")  # and press "Save" in Excel

wb_load("overwrite_formula.xlsx")$
  add_data(dims = "A1", x = "Formula overwritten")$
  save("overwrite_formula_done.xlsx")$
  open()

overwrite_formula.xlsx
overwrite_formula_done.xlsx

[JanMarvin]

Thanks! Again a hard to spot one. In this case we might have to remove the cell from the calcChain. Unfortunately for this we have to find out if a cell we write on is in the calcChain. If it is, we have to remove it from there.

You could have a look at wb$calcChain and modify the xml string (basically remove the cell from it). I'm taking a day off from openxlsx2 development and have a look over the weekend.

[JanMarvin]

With formulas there is a different know problems. The following will create a warning when opening the workbook: Create a workbook in Excel and enter a formula. Fill a few cells, by pulling down the cell a few lines. Save the file. Open it in openxlsx2 and write into the formula region, save the file and open it Excel. The workbook is complaining or broken IIRC. That's because of the way Excel evaluates formulas, but currently this is a different issue and currently a wontfix.

[JanMarvin]

Oh and your issues are really helpful ❤️
They can be reproduced with simple examples and common enough to appear in every day usage. Currently you might be still among the first real world users, therefore keep em coming!