Replacing the reference of a shared formula breaks the workbook

[JanMarvin]

A bug that I actually caught early on in development (using the loadExample.xlsx file iirc). But until recently, we were unable to fill and recreate shared formulas and apparently nobody else noticed it (or cared enough to report it).

What causes the bug: The reference of the shared formula is overwritten. Therefore the remaining shared formula cells have no reference. Excel will complain and fixes the workbook by removing the shared formula from the remaining cells.

The fix: A proper fix would be

• Detect if a cell is the reference of shared formula
• Get replace the formulas for all the shared formula cell with their un-shared variant

library(openxlsx2)

wb <- wb_workbook()$add_worksheet()$
  add_data(x = 1)$
  add_formula(dims = "B1:D1", x = "A1 + 1", shared = TRUE)

wb$add_data(x = 2, dims = "B1") # overwrite the reference of the shared formula

if (interactive()) wb$open()

Another rather interesting behavior I realized just now, if one replaces a shared formula cell with something else, the other shared formula cells could still be impacted. In the example below, a cell of the shared formula range is replaced with a value. this changes the output of the formulas. The Values will now be: 1, 2, 2*, 3. (* indicates the replaced value) instead of 1, 2, 3, 4 (as in the original example without any replacement).

Therefore it might be helpful to throw a warning, if the user replaces any shared formula cell.

library(openxlsx2)

wb <- wb_workbook()$add_worksheet()$
  add_data(x = 1)$
  add_formula(dims = "B1:D1", x = "A1 + 1", shared = TRUE)

wb$add_data(x = 2, dims = "C1") # a shared formula cell

if (interactive()) wb$open()

[JanMarvin]

#1091 (comment)

[JanMarvin]

Closed in #1218