Add sheet to Excel file

Question

I have an Excel file with multiple sheets. I want to save a data frame and insert it into the file as the first sheet. How can I do this with the xlsx package?

Answer 1

@Navid's answer is correct for the package xlsx, but unfortunately it has a java dependency which caused me to run into a lot of problems when reading/writing large amounts of data (10,000+ rows, so not even that large!).

I would recommend using the openxlsx package, which avoids the java dependency. If you want to add further worksheets to a single file then this is the approach I find works for me. @eipi10's comment about worksheetOrder() is a good tip, but you would need to alter that argument each time with something like rev(1:3) (assuming you had three worksheets in the file).

For clarity, here is the workflow for openxlsx version 4.0

# Create a blank workbook
OUT <- createWorkbook()

# Add some sheets to the workbook
addWorksheet(OUT, "Sheet 1 Name")
addWorksheet(OUT, "Sheet 2 Name")

# Write the data to the sheets
writeData(OUT, sheet = "Sheet 1 Name", x = dataframe1)
writeData(OUT, sheet = "Sheet 2 Name", x = dataframe2)

# Reorder worksheets
worksheetOrder(OUT) <- c(2,1)

# Export the file
saveWorkbook(OUT, "My output file.xlsx")

Answer 2

It is an old post and late answer, but I am writing to help others when this search result pops up.

It is possible to write in separate excel sheets, but you need to write the write.xlsx() multiple times and each time use the option, append=TRUE like this:

write.xlsx(df$sheet1, file = "myfile.xlsx", sheetName="sh1", append=TRUE)
write.xlsx(df$sheet2, file = "myfile.xlsx", sheetName="sh2", append=TRUE)
write.xlsx(df$sheet3, file = "myfile.xlsx", sheetName="sh3", append=TRUE)

Answer 3

If you want to add a data.frame in a new 'sheet' in an existing Excel file, the answer of Navid is valid. Here another example. R: Append a worksheet to an excel workbook without reading the entire workbook

In brief:

library(xlsx)
write.xlsx(your_dataframe, "test.xlsx", sheetName="New_Sheet", append=TRUE)

Where "your_dataframe" is your dataframe, "test.xlsx" is the path to your existing Excel file, and "New_sheet" is the name for the new datasheet you want to add to your existing Excel file.