CODEWITHSUNDEEP
rexcelopenxlsx
Alexandre Sanches
Alexandre Sanches

Reputation: 231

How to export an Excel file in R?

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.

Upvotes: 2

Views: 1793

Answers (2)

Brutalroot
Brutalroot

Reputation: 311

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)

Upvotes: 1

rodolfoksveiga
rodolfoksveiga

Reputation: 1261

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'.

Upvotes: 2

Related Questions