I have an Excel spreadsheet with 8 worksheets. Each worksheet has a different data and I have a R code to update them.
My question is: Is there a way to export a .xlsx file in R to update only one specific worksheet in my Excel file without replacing the entire spreadsheet for a new file?
In my previous attempts, with the openxlsx package, I just created a whole new file, which doesn't solve my problem.
You can achieve it using the following workflow:
# load environment
library(xlsx)
# define file path
file_path = '/home/user/Downloads/my_sheet.xlsx'
# load file as a workbook
file = loadWorkbook(file_path)
# check the sheets in the file
tbls = getSheets(file)
# remove the sheet you want to change
removeSheet(file, sheetName = "Sheet1")
# start a new from scratch
new_sheet = createSheet(file, sheetName = "Sheet1")
# create a dataframe
df = data.frame()
# link the dataframe to the new sheet
addDataFrame(df, new_sheet, row.names = FALSE)
# save the workbook/file
saveWorkbook(file, file_path)
Instead of creating a new dataframe from scratch, you can import an old sheet as a dataframe, with read.xlsx(file, sheetIndex, sheetName).
Attention: an error is thrown if you use short file paths, such as '~/Downloads/my_sheet.xlsx'.
You can solve this problem using the code below.
library(migrittr)
library(openxlsx)
file_path = '/home/user/Downloads/my_sheet.xlsx'
df <- data.frame()
file_path %>%
loadWorkbook() %T>%
removeWorksheet(sheet = "Sheet1") %T>%
addWorksheet(sheetName = "Sheet1") %T>%
writeData(sheet = "Sheet1", x = df) %T>%
saveWorkbook(., file = '/home/user/Downloads/my_sheet.xlsx', overwrite = T)
