Protecting dims on worksheet

[JanMarvin]

This SO requested how to protect only a fraction of the cells on a workbook. The main issue with this approach is, that by default Excel locks all cells and one has to actively unlock a cell. Building on #411 I created an answer with openxlsx2 that uses column styles to create the required output.

library(openxlsx2)
# create a workbook
wb <- wb_workbook()$add_worksheet()

# create an unlocked cell style
s1 <- create_cell_style(locked = FALSE)
wb$add_style(s1, "s1")

# make this the default cell style for the entire worksheet
wb$worksheets[[1]]$cols_attr <- sprintf(
  "<col min=\"1\" max=\"16384\" width=\"8.83203125\" style=\"%s\"/>",
  wb$styles_mgr$get_xf_id("s1")
)

# locked cells from OP?
dims_list <- list(
  list(1:3, 3:10),
  list(5:10, 8:12),
  list(100:105, 300:408)
)
dims_list <- lapply(dims_list, function(x)rowcol_to_dims(x[[1]], x[[2]]))

# lock a few ranges and highlight these in red
for (dims in dims_list) {
  message("locking: ", dims)
  wb$add_fill(dims = dims, color = wb_color("red"))
  wb$add_cell_style(dims = dims, locked = TRUE)
}
#> locking: C1:J3
#> locking: H5:L10
#> locking: KN100:OR105

# protect the worksheet and the workbook
wb$protect_worksheet()
wb$protect()

if (interactive()) wb$open()

[Layalchristine24]

@JanMarvin For some reason, applying this example to my use case does not solve the problem, as it still locks all cells, even those which have the option "locked = FALSE".

wb  <- my_wb

# create an unlocked cell style
s1 <- create_cell_style(locked = FALSE)
wb %>%
  wb_add_style(style = s1, style_name = "s1")

# make this the default cell style for the entire worksheet
wb$worksheets[[1]]$cols_attr <- sprintf(
  "<col min=\"1\" max=\"24\" bestFit=\"1\" customWidth=\"1\" hidden=\"false\" width=\"40.711\" style=\"%s\"/>",
  wb$styles_mgr$get_xf_id("s1")
)

dims_list <- list("A2:A76", "B2:B76", "C2:C76", "X2:X76", "A77:X876")

for (dims in dims_list) {
  message("locking: ", dims)
  wb$add_fill(dims = dims, color = wb_color("red"))
  wb$add_cell_style(dims = dims, locked = TRUE)
}

wb$protect_worksheet(
  properties = c(
    autoFilter = FALSE,
    deleteRows = FALSE,
    insertRows = FALSE
  )
)

wb$protect()

Here are the properties of my Worksheet:

wb$worksheets[[1]]
    c("<wbWorksheet>", "Public:", "add_page_break: function (row = NULL, col = NULL)", "add_sparklines: function (sparklines)", "append: function (field, value = NULL)", "autoFilter: <autoFilter ref=\"A1:G1\"/>", "cellWatches:", "clean_sheet: function (dims = NULL, numbers = TRUE, characters = TRUE, styles = TRUE", "clone: function (deep = FALSE)", "colBreaks:", "cols_attr: <col min=\"1\" max=\"24\" bestFit=\"1\" customWidth=\"1\" hidden ...", "conditionalFormatting:", "controls:", "customProperties:", "customSheetViews:", "dataConsolidate:", "dataValidations: <dataValidation type=\"list\" allowBlank=\"1\" showInputMess ...", "dimension: <dimension ref=\"A1:X876\"/>", "drawing:", "drawingHF:", "extLst:", "fold_cols: function (col_df)", "freezePane: <pane ySplit=\"1\" xSplit=\"1\" topLeftCell=\"B2\" activePane= ...", "get_post_sheet_data: function ()", "get_prior_sheet_data: function ()", "headerFooter: NULL", "hyperlinks: list", "ignore_error: function (dims = \"A1\", calculatedColumn = FALSE, emptyCellReference = FALSE", "ignoredErrors:", "initialize: function (tab_color = NULL, odd_header = NULL, odd_footer = NULL", "legacyDrawing:", "legacyDrawingHF:", "merge_cells: function (rows = NULL, cols = NULL, solve = FALSE)", "mergeCells:", "oleObjects:", "pageMargins: <pageMargins left=\"0.7\" right=\"0.7\" top=\"0.75\" bottom=\"0 ...", "pageSetup: <pageSetup paperSize=\"9\" orientation=\"portrait\" horizont ...", "phoneticPr:", "picture:", "printOptions: <printOptions gridLines=\"1\" gridLinesSet=\"1\"/>", "protectedRanges:", "relships: list", "rowBreaks:", "scenarios:", "set_print_options: function (gridLines = NULL, gridLinesSet = NULL, headings = NULL", "set_sheetview: function (color_id = NULL, default_grid_color = NULL, right_to_left = NULL", "sheet_data: wbSheetData, R6", "sheetCalcPr:", "sheetFormatPr: <sheetFormatPr baseColWidth=\"8.43\" defaultRowHeight=\"16\" ...", "sheetPr:", "sheetProtection:", "sheetViews: <sheetViews><sheetView showGridLines=\"1\" showRowColHeade ...", "smartTags:", "sortState:", "tableParts:", "unfold_cols: function ()", "unmerge_cells: function (rows = NULL, cols = NULL)", "webPublishItems:", "Private:", "cols: NULL", "data_validation: function (type, operator, value, allowBlank, showInputMsg, showErrorMsg", "do_append_x14: function (x, s_name, l_name)", "sheetData: NULL")

Can you see where the problem is?

[JanMarvin]

Do you assign this to the correct worksheet:
wb$worksheets[[1]]$cols_attr?
The 1 is the index of the worksheet in the workbook.

[Layalchristine24]

Yes, I do. 1 is the correct index. Never mind. Thank you!

[JanMarvin]

Oh, the problem is here that you have to modify either all columns or rows if you want to be able to delete columns or rows.

In the example above you assign a style with locked = FALSE to the columns and you want to be able to delete rows. This way you would be able to delete columns if you switch to deleteColumns. But again you would only be able to remove columns where all cells in the column have style locked = FALSE. Otherwise it is impossible to delete the rows. You can't have your cake and eat it!

If you want to be able to insert/delete/hide rows/columns you have to make sure that there are no locked cells in the range. The entire locking and style application thing is strange and quite counterintuitive, I wholeheartedly agree, but luckily nothing we have invented. openxlsx2 just provides you with the tools to modify these things.

[Layalchristine24]

Thank you very much. It's clear now. With the issues #872 and #871 and the PR #873, I know now that the property deleteRows = FALSE and the style locked = TRUE are not independent from each other. I just hoped that this could be the case (i.e. some cells are not editable but can be deleted without being edited) as an added value compared to Excel. Thank you for your support @JanMarvin!