Property deleteRows = FALSE does not work in wb_protect_worksheet()

[Layalchristine24]

Hi! I would like to be able to delete rows even if the worksheet is protected, but it does not seem to work. Thank you for your help!

# remotes::install_github("JanMarvin/openxlsx2")
library(palmerpenguins)
library(openxlsx2)

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_protect_worksheet(
    sheet = "my_first_sheet",
    protect = TRUE,
    properties = c(
      autoFilter = FALSE,
      deleteRows = FALSE, # does not work
      insertRows = FALSE
    )
  )

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

[JanMarvin]

Hi @Layalchristine24 , thanks for the report. The issue is a bit tricky as you have to apply row styles where the cell style information: locked is removed. This has to be applied on the entire row (and for columns most likely for the columns too), but atm we do not provide a simple way to do this. Therefore it requires a manual intervention (see below).

The entire protect/unprotect thing is a bit ... same goes with #872. It's not us, but the openxml specification or better, the excel developers who invented this confusing things.

# remotes::install_github("JanMarvin/openxlsx2")
library(palmerpenguins)
library(openxlsx2)

wb_test <- wb_workbook(
  datetime_created = Sys.time(),
) %>%
  wb_add_worksheet(
    sheet = "my_first_sheet"
  ) %>%
  wb_add_data(
    x = penguins
  ) %>%
  wb_protect_worksheet(
    sheet = "my_first_sheet",
    protect = TRUE,
    properties = c(
      autoFilter = FALSE,
      deleteRows = FALSE, # does not work
      insertRows = FALSE
    )
  ) %>% 
  wb_add_cell_style(
    dims = wb_dims(x = penguins),
    locked = FALSE
  )

styid <- wb_test %>% wb_get_cell_style(dims = "A1")

# apply style 2 (extract, modify and insert) 
rows <- wb_test$worksheets[[1]]$sheet_data$row_attr
rows$customFormat <- "1"
rows$s <- styid
wb_test$worksheets[[1]]$sheet_data$row_attr <- rows

if (interactive()) wb_open(wb_test)

[Layalchristine24]

@JanMarvin Thank you very much for this solution and the explanations.

[Layalchristine24]

@JanMarvin Sorry, but I have one more question about this solution: why do you set dims = "A1" to get the styid variable? Why should customFormat be equal to "1"? I tried this solution on the workbook I am working on, but it did not solve the problem as it did with the reprex. I get the value "2" for styid. The rows data frame does not look the same as the one from the reprex... Should it look the same for the special columns s and customFormat?

[JanMarvin]

customFormat should be "1" which is just xml for TRUE and tells the spreadsheet software to apply the style defined in s.

I simply picked one cell that has the unlocked style applied. This style is added to the rows so that every unstyled cell of this row gets the unlocked attribute.

I can create a more elaborate example later.

PS: Just for the reference, I had to google this as well and already typed a "its a Excel bug" reply.

[Layalchristine24]

@JanMarvin Thank you very much!

[JanMarvin]

If you don’t want to apply the protection to every cell you can add it only to a fraction. Like all columns in rows 1:10 or columns A:D. Subset rows as needed. Until I create the row/column style helper it’s a little more complex.

[JanMarvin]

I have implemented a wb_add_style_across() function in #873 now the example reduces to this:

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)

wb_test <- wb_workbook(
  datetime_created = Sys.time(),
) %>%
  wb_add_worksheet(
    sheet = "my_first_sheet"
  ) %>%
  wb_add_data(
    x = penguins
  ) %>%
  wb_protect_worksheet(
    sheet = "my_first_sheet",
    protect = TRUE,
    properties = c(
      autoFilter = FALSE,
      deleteRows = FALSE,
      insertRows = FALSE
    )
  ) %>% 
  wb_add_cell_style(
    dims = wb_dims(x = penguins),
    locked = FALSE
  ) %>% 
  wb_add_style_across(rows = wb_dims(x = penguins))

if (interactive()) wb_open(wb_test)

[Layalchristine24]

@JanMarvin Thank you! However, the function wb_add_style_across() cannot be found.

# pak::pkg_install("JanMarvin/openxlsx2#873")

library(palmerpenguins)
library(openxlsx2)

wb_test <- wb_workbook(
  datetime_created = Sys.time(),
) %>%
  wb_add_worksheet(
    sheet = "my_first_sheet"
  ) %>%
  wb_add_data(
    x = penguins
  ) %>%
  wb_protect_worksheet(
    sheet = "my_first_sheet",
    protect = TRUE,
    properties = c(
      autoFilter = FALSE,
      deleteRows = FALSE,
      insertRows = FALSE
    )
  ) %>% 
  wb_add_cell_style(
    dims = wb_dims(x = penguins),
    locked = FALSE
  ) %>% 
  wb_add_style_across(rows = wb_dims(x = penguins))
#> Error in wb_add_style_across(., rows = wb_dims(x = penguins)): could not find function "wb_add_style_across"

if (interactive()) wb_open(wb_test)

[JanMarvin]

Ah I’ve renamed it to wb_set_cell_style_across() and it’s now required to specify a cell or a cell style via id. I’m not sure yet about the function, that’s why I haven’t merged it yet.

[Layalchristine24]

@JanMarvin Thank you! My problem is that I need to apply the unlocked even to rows where cells need to remain locked, to be able to delete rows. However, I do not want to unlock them. But thank you for your help!

[JanMarvin]

I’m not sure that it is possible to do that with the ooxml format. Either you lock cells or you unlock them and allow their removal

[Layalchristine24]

All Right. Thank you! Is this specific to the openxlsx2 package or is it the same with openxlsx?

[JanMarvin]

I believe this is specific to the file format. I don’t even think you’re able to create this with Excel

[Layalchristine24]

Ok, thank you very much for your help.

[JanMarvin]

I've merged pull request #873 and the next release will ship wb_set_cell_style_across(). I've added a NEWS entry pointing to this issue and added a sentence to the wb_worksheet_protection() documentation as well. Thanks again for the report!