Remove sheet's password protection in Excel
# remove sheet protection in Excel | |
# Sample file: https://www.dropbox.com/s/4ul0kowrscyr8cz/excel_protected.xlsx?dl=0 | |
library(stringr) | |
library(zip) | |
# file with protected sheets | |
file <- "data/excel_protected.xlsx" | |
# file name and path after removing protection | |
file_unlocked <- str_replace(basename(file), ".xlsx$", "_unlocked.xlsx") | |
file_unlocked_path <- file.path(getwd(), "data", file_unlocked) | |
# create temporary directory in project folder | |
# so we see what is going on | |
temp_dir <- "_tmp" | |
# remove and recreate _tmp folder in case it already exists | |
unlink(temp_dir, recursive = T) | |
dir.create(temp_dir) | |
# unzip Excel file into temp folder | |
unzip(file, exdir = temp_dir) | |
# get full path to XML files for all worksheets | |
worksheet_paths <- list.files( | |
paste0(temp_dir, "/xl/worksheets"), | |
full.name = TRUE, | |
pattern = ".xml") | |
# remove the XML node which contains the sheet protection | |
# We might of course use e.g. xml2 to parse the XML file, but this simple approach will suffice here | |
for (ws in worksheet_paths) { | |
x <- readLines(ws, encoding = "windows1") | |
# the "sheetProtection" node contains the hashed password "<sheetProtection SOME INFO />" | |
# we simply remove the whole node | |
out <- str_replace(x, "<sheetProtection.*?/>", "") | |
writeLines(out, ws) | |
} | |
# create a new zip, i.e. Excel file, containing the modified XML files | |
old_wd <- setwd(temp_dir) | |
f <- list.files(recursive = T, full.names = F, all.files = T, no..=T) | |
# as the Excel file is a zip file, we can directly replace the .zip extension by .xlsx | |
zip::zip(file_unlocked_path, files = f) # utils::zip does not work for some reason | |
setwd(old_wd) | |
# clean up and remove temporary directory | |
unlink(temp_dir, recursive = T) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
I think this could be improved to also remove workbook protection, by adding the lines below between 23 and 25: