Create Multiple Sheets in Excel workbook from R Tables? [using xlsx package]

Question

I followed these instructions I then followed these instructions:

# Write the first data set in a new workbook
write.xlsx(USArrests, file="myworkbook.xlsx",
      sheetName="USA-ARRESTS", append=FALSE)
# Add a second data set in a new worksheet
write.xlsx(mtcars, file="myworkbook.xlsx", sheetName="MTCARS", 
           append=TRUE)
# Add a third data set
write.xlsx(Titanic, file="myworkbook.xlsx", sheetName="TITANIC", 
           append=TRUE)

Here is what I did on my end:

openxlsx::write.xlsx(Cost_Changes_0021_Table,'C:/Users/santi/Documents/Cost Changes xlsx/0021_Cost_Changes.xlsx', sheetName ='Dept 0021 Prod Level', row.names= FALSE, append = FALSE)
openxlsx::write.xlsx(Cost_Changes_0021_Vendor_Lvl_Table,'C:/Users/santi/Documents/Cost Changes xlsx/0021_Cost_Changes.xlsx', sheetName ='Dept 0021 Vendor Level', append= TRUE, row.names= FALSE)

I am not understanding why I am not getting multiple sheets after I run that script. What happens in the second line in my code overwrites the first sheet and then I only see one tab when really I want two tabs in the work book C:/Users/santi/Documents/Cost Changes xlsx/0021_Cost_Changes.xlsx

Peter Peter · Accepted Answer · 2020-05-04T21:32:16

This vignette is your friend when it comes to openxlsx: https://cran.r-project.org/web/packages/openxlsx/vignettes/Introduction.html

Try something like this (based on the vignette)

library(openxlsx)

#create a named list of your dataframes. The list names will be the worksheet names.

xl_lst <- list('df1_name' = iris, 'df2_name' = mtcars)

write.xlsx(xl_lst, file = "xl_with_2_worksheets.xlsx")

Create Multiple Sheets in Excel workbook from R Tables? [using xlsx package]

1 Answers