Option locked = TRUE does not lock the cell in wb_add_cell_style()

[Layalchristine24]

Hi again! I noticed that the option locked = TRUE does not seem to work. I found another way to solve this issue by first protecting the worksheet and then applying locked = FALSE to the other cells. But it would be great if cells could be directly locked by using this option. Thank you 🤝

library(palmerpenguins)
library(openxlsx2)

dims_locked <- wb_dims(
  x = penguins,
  rows = 1L,
  cols = 3L,
  select = "data"
)
wb_test <- wb_workbook(
  datetime_created = Sys.time(),
) %>%
  wb_add_worksheet(
    sheet = "my_first_sheet"
  ) %>%
  wb_add_data(
    x = penguins,
    start_col = 1L,
    start_row = 1L
  ) %>%
  wb_add_cell_style(
    dims = dims_locked,
    locked = TRUE
  ) %>%
  wb_add_fill(
    dims = dims_locked,
    color = wb_color("#e5f4cc")
  )

wb_open(wb_test)
#> Warning: will not open file when not interactive

[JanMarvin]

Hi, sorry I don't follow. Maybe you can let me know what you want to achieve?

This only sets the cell format to locked which is the default if I understand Excel correct

wb_add_cell_style(
    locked = TRUE
)

[Layalchristine24]

I thought that this option could be used to lock the cell, i.e. it should not be possible to edit/modify the value of the cell C2 in this example. Maybe I misunderstood what the locked option should do.

[JanMarvin]

Ah yes, now I get it. It's a bit counterintuitive. The best solution I found was unlocking all cells, locking a few and protecting the worksheet. I've created an example here: #663.

Both the example and the answer from #871 lack support of a style helper to apply styles to the entire column or row. But ... most of the things happen because somebody asks for it and not due to some development masterplan 😃 Might be time to extend style helpers to accept dims = "A:B or dims = 1:10.

[JanMarvin]

The full example with the code from #873. Unlock everything in columns A:H, lock C2, and protect the worksheet.

remotes::install_github("JanMarvin/openxlsx2#873")
#> Skipping install of 'openxlsx2' from a github remote, the SHA1 (379b11ed) has not changed since last install.
#>   Use `force = TRUE` to force installation
library(palmerpenguins)
library(openxlsx2)

dims_locked <- wb_dims(
  x = penguins,
  rows = 1L,
  cols = 3L,
  select = "data"
)
wb_test <- wb_workbook(
  datetime_created = Sys.time(),
) %>%
  wb_add_worksheet(
    sheet = "my_first_sheet"
  ) %>%
  wb_add_data(
    x = penguins,
    start_col = 1L,
    start_row = 1L
  ) %>%
  # beg: unlock all the penguins columns
  wb_add_cell_style(
    dims = wb_dims(x = penguins),
    locked = FALSE
  ) %>% 
  wb_add_style_across(
    cols = wb_dims(x = penguins)
  ) %>% 
  # end: unlock all the penguins columns
  # lock all but one cell
  wb_add_cell_style(
    dims = dims_locked,
    locked = TRUE
  ) %>% 
  wb_add_fill(
    dims = dims_locked,
    color = wb_color("#e5f4cc")
  ) %>%
  # protect the worksheet
  wb_protect_worksheet()

if (interactive()) wb_open(wb_test)

[JanMarvin]

I'm closing this issue, because it is more or less a documentation issue of how cell locking works. I might add a chapter to the book, but I see no way for us to fix the strange openxml cell locking/unlocking.