Workbook formulas are not recalculated on opening in Excel

[nicojx]

After amending an existing workbook in openxlsx2, formulas are not recalculating on opening the amended workbook in Excel. As a consequence, formula results remain wrong (and are quite difficult to get Excel to recalculate, too). I would argue that all workbooks saved by openxlsx2::wb_save() should be set to recalculate on opening in Excel.

To reproduce:

• Create a workbook in Excel, with a formula that relies on another cell in the workbook.
• Amend relied-upon value using openxlsx2::wb_load() and openxlsx2::write_data(), save via openxlsx2::wb_save()
• Open amended workbook in Excel: The formula shows the original result (pressing the Formulas > Calculation > Calculate Now button does not lead to a recalculation).

library(openxlsx2)

wb <- wb_load("test_calculation.xlsx")
write_data(
  wb = wb,
  sheet = "Sheet1",
  x = "Beta",
  startCol = 1,
  startRow = 1,
  colNames = FALSE
)
wb_save(wb, "test_calculation_result.xlsx")

test_calculation.xlsx
test_calculation_result.xlsx

[JanMarvin]

I see, you can click F2 on the cell to get the formula reevaluated. Not sure at the moment, if there is a way to tell excel to reevaluate all formulas. I'll look into it.
PS: You should use wb_add_data().

[JanMarvin]

Maybe we need to keep calcChain.xml (a file in the xlsx file) intact for this to work. I never understood what it was supposed to do.

[JanMarvin]

I have opened #428 this should fix the issue. It is caused by calculation properties in combination with a calculation chain. Excel uses this when writing xlsx files to let Excel know in which order cells need to be evaluated.
When we load the workbook we simply took what Excel provided. Therefore all formulas that were created until this stage, were never updated unless modified manually or when copying.

[nicojx]

Many thanks, that makes sense!

[JanMarvin]

Thanks for reporting! Things like this are difficult to spot - more than a year went by without anyone noticing, because they occur only in a very specific set of conditions - and they cause even harder headaches later on.

[nicojx]

No problem -- happy to help.

On your earlier comment to use wb_add_data() -- I have not been able to make this work to amend the spreadsheet. From the help it looks like I should just be able to replace write_data with wb_add_data, but this has not at all worked for me, even in this example. The original "Alpha" was not overwritten. Am I doing something wrong?

[JanMarvin]

You can use write_data()or wb_add_data(), but the latter is what I use and which is more tested and might provide more features, the former is merely a relic from openxlsx.

fl <- "test_calculation.xlsx"

wb <- wb_load(fl)$
  add_data(dims = "A1", x = "Beta")$
  open()

wb <- wb_load(fl) %>% 
  wb_add_data(dims = "A1", x = "Beta") %>% 
  wb_open()