rocket1906 rocket1906 - 2 months ago 35
R Question

How do you save Excel file and enable cell protection in R?

I have a basic Excel workbook created with the XLSX package. I want to save it as an .xlsx file but lock all columns except for one to protect them from being edited. I'm able to set cell protection to the selected column with the

function, but I don't know how to turn password protection on for the worksheet in order to actually make the columns protected.

wb = createWorkbook()
s1 = createSheet(wb, "Sheet 1")
addDataFrame(mtcars, s1) #using mtcars as example dataset
cs = CellStyle(wb, cellProtection = CellProtection(locked=F)) #setting style to unlock cells
rows <- getRows(s1, rowIndex=2:101)
cells <- getCells(rows, colIndex = c(2)) #getting the cells to unlock
lapply(names(cells), function(ii)setCellStyle(cells[[ii]],cs)) #applying unlocking to all columns except the second one (the one i want to leave locked)

saveWorkbook(wb, "file.xlsx")

When I check the Excel file, the properties of the cells in column 2 say they're unlocked, but then I have to click on "Protect Sheet" and manually enter a password in order to actually lock all the cells.

Is there a way to do this in R and enable worksheet protection?


You can do this directly with apache POI (which is used by xlsx). Just call

.jcall(s1, "V", "protectSheet", "mypassword")

before you call saveWorkbook.