I am using excel templates to collect data from various people. To minmize the chances of people changing the structure of the templates I wanted to protect the sheets, but still allow them to fill in the data values that I want. At the same time I also use the dataValidationcommand that one can only use once in the Cranversion (Stack Question)
I was happy that i found a forked version of the openxlsx package that has included a function protectWorksheet to enable sheet protection, Link. But the awalker89 fork has not fixed the issue with the dataValidationfunction, that would be fixed in the tkunstekfork.
I found a question about using different version of a package, but it seems to be rather complicated and I am afraid this would then not work, because I would be mixing two different version in one excel file.
Can I install openxlsx version under different names like openxlsx_awalker and openxlsx_tkunstek` and then load them parallely to to what I want to do? How would I do this?
I could also open the excel file first with the tkunstekpackage, enable the dataValidations and then open it again with the awalkerpackage to protect the sheet, but still not sure if the files would work thereafter.
I would image a MWE as follows
library(openxlsx_awalker)
library(openxlsx_tkunstek)
wb <- createWorkbook()
addWorksheet(wb, "S1")
writeDataTable(wb, 1, x = data.frame(iris[1:30,],yesno="Yes",highlow="Low"))
addWorksheet(wb, "Drop-down values", visible=FALSE)
yesno_values_df = data.frame("YESNO" = c("Yes", "No"))
yesno.labels<-"'Drop-down values'!$A$2:$A$3"
source_values_df = data.frame("Sources" = c("High", "Low"))
highlow.labels<-"'Drop-down values'!$B$2:$B$3"
writeData(wb, sheet = "Drop-down values", x =yesno_values_df, startCol =1)
writeData(wb, sheet = "Drop-down values", x =source_values_df, startCol =2)
openxlsx_tkunstek::dataValidation(wb, "S1", col = 6, rows = 1:10,
type = "list", value =yesno.labels)
openxlsx_tkunstek::dataValidation(wb, "S1", col = 7, rows = 1:10,
type = "list", value =highlow.labels)
# Formatting cells / columns is allowed , but inserting / deleting columns is protected:
openxlsx_awalker::protectWorksheet(wb, "S1", protect = TRUE, lockFormattingCells = FALSE, lockFormattingColumns = FALSE, lockInsertingColumns = TRUE, lockDeletingColumns = TRUE)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)