How to password protect all xlsx files in a folder?

[dummy123]

I have exported my dataframe according to their respective filter, in this case, its the Country

df = data.frame(Country = c("Japan", "Japan", "Thailand", "Germany", "Thailand", "Japan"), 
                            Count = c(15, 5, 25, 5, 60, 50))

#Separate the dataframe according to their respective country
splitdf = split(df, df$Country)

#apply style and export
save_data <- function(df, name) {
  wb <- createWorkbook()
  addWorksheet(wb, name)
  writeDataTable(wb, name, df, tableStyle = "TableStyleMedium2")
  saveWorkbook(wb, paste0(name, ".xlsx"), overwrite = TRUE)
}

mapply(
  save_data,
  splitdf,
  names(splitdf)
)

Now, the 3 xlsx are stored in a folder in my working directory with their workbook name being the Country they have been filtered by. I would like to Password protect them in such a way that it will be a fixed string, "iLove" + first 2 character of their file name.
Example, the password for Workbook "Japan" will be "iLoveJa" and Germany will be "iLoveGe"

[AlexisW]

Does protectWorkbook do what you want? You can just add the call before the saveWorkbook() call, in your save_data() function. Use paste0() to create the password as you did with the filename.

[dummy123]

Hi thanks for the reply, I tried adding the line in bold but it doesn't work.

#apply style and export
save_data <- function(df, name) {
  wb <- createWorkbook()
  addWorksheet(wb, name)
  writeDataTable(wb, name, df, tableStyle = "TableStyleMedium2")
  **protectWorkbook(wb, protect=TRUE, password = "TestPassword")**
  saveWorkbook(wb, paste0(name, ".xlsx"), overwrite = TRUE)
}

[DavoWW]

Hi @dummy123,
The two packages, xlsx and openxlsx, have similar function names but different arguments, that's why the code is not working. Check the help pages to see the differences. If the sheet formatting is not crucial, then I think using xlsx::write.xlsx() may do the job you want.

HTH