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

Question

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 CellProtection() function, but I don't know how to turn password protection on for the worksheet in order to actually make the columns protected.

library(xlsx)
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?

I think that is not implemented (there's no mention of "password" in that package's help pages) and the help page for XLConnect::loadworkbook (which is how to create an instance of an R object of "workbook"-class) says: "password Password to use when opening password protected files. The default NULL means no password is being used. This argument is ignored when creating new files using create = TRUE." — IRTFM
Thanks Hack-R and 42-; Yes, with XLConnect you can password protect the entire workbook on a save, but that doesn't work for me. I just want to protect a few cells from being edited by users. I know this is possible to do in a SAS, for example, so I figured it would be a piece of cake in R - I was wrong though. — rocket1906
@rocket1906 That's interesting, how do you do that? I see their documentation says it can read password protected files, but it didn't mention saving a file with a password. Would be good to know for future reference. — Hack-R
@Hack-R My apologies, I was thinking of the package RDCOMClient. Here's how: eApp <- COMCreate("Excel.Application") wk <- eApp$Workbooks()$Open(Filename="file.xlsx") wk$SaveAs(Filename="file.xlsx", Password="mypassword") — rocket1906
Haven't tested my reading of the manual but I thought it said you could only save a password protected file after it was opened with its password. It didn't appear that you could create a pwd-protected file de_novo — IRTFM

ssword ssword · Accepted Answer · 2017-02-02T22:05:17

I have been using @AEF 's answer. But today I found out this in fact can be done in the xlsx package:

s1$protectSheet("mypassword").

Of course, before you call saveworkbook

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

3 Answers