Keep protection(or re-protect) certain columns of imported Excel workbook through R

Question

I have an Excel workbook with two sheets. On the first sheet columns B and C have formulas (referring to the second, still empty, sheet) that are protected against editing. Now I imported this workbook through R, added my data to the second sheet and saved the workbook.

library(openxlsx)
wb <- loadWorkbook('Tables.xlsx')
writeData(wb, 'Sheet2', mydf)
saveWorkbook(wb, New_tables.xlsx')

However, after saving the workbook, columns B and C of sheet 1 are not protected anymore. Is there any specific function that I could add to protect (only) columns B and C from sheet 1 against editing?

Thanks!

Answer 1

There is a protectWorksheet function you can use.

You will need to createStyle to set locked = TRUE or FALSEthen you can control the columns and rows that you want to lock from editing. For example:

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "NEW")
writeData(wb, "NEW", mtcars)
addStyle(wb, "NEW", rows = 1:nrow(mtcars), cols = 1, gridExpand = TRUE, style = createStyle(locked = FALSE))
protectWorksheet(wb, "NEW", protect = TRUE)
saveWorkbook(wb, "New_tables.xlsx", overwrite = TRUE)

This will lock first column up to the rows where data is populated and locks everywhere else.