20

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?

M--
  • 25,431
  • 8
  • 61
  • 93
user5516342
  • 435
  • 1
  • 6
  • 10
  • 1
    I use `openxlsx` and the functions `loadWorkbook` and `addWorksheet`. Check the documentation to see if may help https://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf – Pierre L Jan 11 '16 at 21:31
  • 3
    Possible duplicate of [R: easy way to export multiple data.frame to multiple excel worksheets?](http://stackoverflow.com/questions/27713310/r-easy-way-to-export-multiple-data-frame-to-multiple-excel-worksheets) – boshek Jan 11 '16 at 21:33
  • 1
    I don't think it's quite a duplicate, because the OP here is asking how to insert the sheet at the first position. The method in the linked question inserts new sheets at the last position. I'm not sure how to change the sheet order with the `xlsx` package. However, the `openxlsx` package has a `worksheetOrder` function that might be useful here. – eipi10 Jan 11 '16 at 22:04

3 Answers3

26

@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")
EcologyTom
  • 2,344
  • 2
  • 27
  • 38
  • 3
    This is a nice summary of how to deal with writing multiple worksheets. You have to create the workbook object, add all sheets and then write it out. Because I needed to add to an existing file I had to read xlsx file, add worksheet, save. When I did some more processing I have to again read - add - save. Morale of the story: be aware it is not possible to add worksheets without reading in the whole file first (i.e. creating a wrokbook object - in your case empty, or in other cases - read from disk). – r0berts Feb 18 '19 at 07:13
13

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)
Navid Ghajarnia
  • 151
  • 2
  • 8
4

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.

Corina Roca
  • 385
  • 4
  • 15