Easy way to export multiple data.frame to multiple Excel worksheets
Asked Answered
C

13

131

I am surprised to find that there is no easy way to export multiple data.frame to multiple worksheets of an Excel file? I tried xlsx package, seems it can only write to one sheet (override old sheet); I also tried WriteXLS package, but it gives me error all the time...

My code structure is like this: by design, for each iteration, the output dataframe (tempTable) and the sheetName (sn) got updated and exported into one tab.

for (i in 2 : ncol(code)){ 
        ...
        tempTable <- ...
        sn <- ...
        WriteXLS("tempTable", ExcelFileName = "C:/R_code/../file.xlsx",
              SheetNames = sn);
}

I can export to several cvs files, but there has to be an easy way to do that in Excel, right?

Corral answered 30/12, 2014 at 22:10 Comment(2)
You are wrong about xlsx. There is a createSheet function, which allows you to create new sheets, and then write to them, in a loop. Additionally, the equivalent functions in XLConnect are vectorized, allowing for writing a list of data frames to multiple sheets.Helms
@joran, createSheet is used with addDataFrame not write.xlsx? I saw that earlier in the doc but couldn't figure out the whole process.Corral
R
195

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx: http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

Russo answered 30/12, 2014 at 22:25 Comment(11)
xlsx doesn't take care of the numbers in the first row R putting there. openxlsx remove them.Euphonize
Add row.names=FALSE to remove row names.Russo
@EcologyTom I switched from xlsx to openxlsx a while back, as I find it much more intuitive and it also avoids the java dependency.Russo
Yes, the java dependency forced me to make the same switch. Although the code is a little longer it is pretty straightforward. For a method with openxlsx version 4.0 see my supplementary answer below.Wild
Is it just me, or does sheet 2 simply write over sheet 1 when one uses this code?Priority
@Priority I get two separate sheets when I use the code in the answer.Russo
@Russo what version of the package are you using? I am getting the same result as @Priority with sheet 2 overwriting sheet 1. Using the second example I'm even more confused because when I run sheet = createSheet(wb,"Sheet 1") I get the error Error in as.vector(x, "character") : cannot coerce type 'environment' to vector of type 'character'.Greyso
I'm using xlsx 0.6.5; rJava 0.9-13; R 4.0.3. Computer is running macOS 10.15.6 (Catalina). I should point out that it's been a few years since I used xlsx in my own work. I switched to openxlsx to avoid java dependency pain. I also find openxlsx's syntax more intuitive for formatting output.Russo
it seems like this solution does not work, sheet 2 writes over sheet 1Southerland
@Southerland I just ran the code on my Mac and it writes two separate worksheets. My system is the same as listed in my previous comment.Russo
This solution seems to work for some but not others. As for me, it also overwrites sheet1 instead of generating separate sheet(s). The solution given by EcologyTom and Syed work for me.Frown
U
167

You can also use the openxlsx library to export multiple datasets to multiple sheets in a single workbook.The advantage of openxlsx over xlsx is that openxlsx removes the dependencies on java libraries.

Write a list of data.frames to individual worksheets using list names as worksheet names.

require(openxlsx)
list_of_datasets <- list("Name of DataSheet1" = dataframe1, "Name of Datasheet2" = dataframe2)
write.xlsx(list_of_datasets, file = "writeXLSX2.xlsx")
Unspent answered 5/4, 2016 at 19:26 Comment(8)
I have used these packages and I think the openxlsx is the quickest as its c++. XlConnect will eat your RAM. You might want to do some benchmarking between xlsx and openxlsxWhatever
Another advantage of that packge is that it take care of the R-numbering in the first row.Euphonize
Thanks, openxlsx::write.xlsx is the way to go... I was saving 11 sheets, each a 20,000x10 dataframe, done is a couple seconds while xlsx::write.xlsx errored out after appending the second sheet with java.lang.OutOfMemoryError: Java heap spaceDownpour
I needed to add the parameter append=TRUE to write.xlsx to make it write several sheets at once to one Excel fileMaas
Lovely! I created my list as part of a loop, and just had to initialize it (list_of_dfs <- list()) and then fill it, using temp_key and temp_df constructed during the loop (list_of_dfs[[temp_key]] = temp_df) . It was also very fast in writing, despite the 16 sheets I needed to create! Has anyone witnessed problems with memory during creation?Gamaliel
I have a similar problem resulting in a lot of sheets and I would like an option that automatically creates dataframes (based on dplyr's group_by function), and subsequently creates multiple sheets in an excel workbook. Is this possible?Adrian
This solution looked nice, but the package openxlsx seems uninstallable - I had problems with both CRAN and conda manager, in both cases it failed to install/load the package due to missing library libicui18n.so.64 (Ubuntu 18.04 with R installed from conda). On the other hand xlsx worked flawlessly.Rakish
Using openxlsx ended up being the way to go for me. xlsx failed to append worksheets even when append=TRUE. Using the instructions above, I was able to seamlessly stitch together 17 sheets with 40k rows each in a single Excel workbook.Bidwell
B
60

There's a new library in town, from rOpenSci: writexl

Portable, light-weight data frame to xlsx exporter based on libxlsxwriter. No Java or Excel required

I found it better and faster than the above suggestions (working with the dev version):

library(writexl)
sheets <- list("sheet1Name" = sheet1, "sheet2Name" = sheet2) #assume sheet1 and sheet2 are data frames
write_xlsx(sheets, "path/to/location")
Bikales answered 1/11, 2017 at 11:32 Comment(5)
Thanks! This worked where openxlsx didn't (I can't install rtools at work).Angelenaangeleno
Which version do you use for this? The default cran download doesn't support multiple sheets (yet): ' Error in writexl::write_xlsx(list(... : Argument x must be a data frame or list of data frames 'Omit
As I wrote, the dev version.Bikales
@JarkoDubbeldam: I installed mine from cran and multiple sheets do work for me (R 3.3.0). Check if the objects inside your list are data.frames.Angelenaangeleno
this is one really works. couldn't install xlsx in r.Heikeheil
W
34

Many good answers here, but some of them are a little dated. If you want to add further worksheets to a single file then this is the approach I find works for me. 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)

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

EDIT

I've now trialled a few other answers, and I actually really like @Syed's. It doesn't exploit all the functionality of openxlsx but if you want a quick-and-easy export method then that's probably the most straightforward.

Wild answered 13/6, 2018 at 9:20 Comment(0)
S
8

I'm not familiar with the package WriteXLS; I generally use XLConnect:

library(XLConnect)
##
newWB <- loadWorkbook(
  filename="F:/TempDir/tempwb.xlsx",
  create=TRUE)
##
for(i in 1:10){
  wsName <- paste0("newsheet",i)
  createSheet(
    newWB,
    name=wsName)
  ##
  writeWorksheet(
    newWB,
    data=data.frame(
      X=1:10,
      Dataframe=paste0("DF ",i)),
    sheet=wsName,
    header=TRUE,
    rownames=NULL)
}
saveWorkbook(newWB)

This can certainly be vectorized, as @joran noted above, but just for the sake of generating dynamic sheet names quickly, I used a for loop to demonstrate.

I used the create=TRUE argument in loadWorkbook since I was creating a new .xlsx file, but if your file already exists then you don't have to specify this, as the default value is FALSE.

Here are a few screenshots of the created workbook:

enter image description here

enter image description here

enter image description here

Seersucker answered 30/12, 2014 at 22:21 Comment(2)
I haven't used XLConnect, very detailed example, thanks!Corral
You're welcome - I've found it to be a very useful package. There's a pretty good vignette on CRAN that details some of the main features, with a nice example in section 4 demonstrating how to write R plots into a worksheet.Seersucker
B
8

I regularly use the packaged rio for exporting of all kinds. Using rio, you can input a list, naming each tab and specifying the dataset. rio compiles other in/out packages, and for export to Excel, uses openxlsx.

library(rio)

filename <- "C:/R_code/../file.xlsx"

export(list(sn1 = tempTable1, sn2 = tempTable2, sn3 = tempTable3), filename)
Bushore answered 27/10, 2020 at 21:9 Comment(1)
Wish there was a way on SO to supplant highly-upvoted but now-outdated answers. They were right when they posted, but this is almost certainly the best way to do this now.Starfish
D
5

Incase data size is small, R has many packages and functions which can be utilized as per your requirement.

write.xlsx, write.xlsx2, XLconnect also do the work but these are sometimes slow as compare to openxlsx.

So, if you are dealing with the large data sets and came across java errors. I would suggest to have a look of "openxlsx" which is really awesome and reduce the time to 1/12th.

I've tested all and finally i was really impressed with the performance of openxlsx capabilities.

Here are the steps for writing multiple datasets into multiple sheets.

 install.packages("openxlsx")
 library("openxlsx")

    start.time <- Sys.time()

    # Creating large data frame
    x <- as.data.frame(matrix(1:4000000,200000,20))
    y <- as.data.frame(matrix(1:4000000,200000,20))
    z <- as.data.frame(matrix(1:4000000,200000,20))

    # Creating a workbook
    wb <- createWorkbook("Example.xlsx")
    Sys.setenv("R_ZIPCMD" = "C:/Rtools/bin/zip.exe") ## path to zip.exe

Sys.setenv("R_ZIPCMD" = "C:/Rtools/bin/zip.exe") has to be static as it takes reference of some utility from Rtools.

Note: Incase Rtools is not installed on your system, please install it first for smooth experience. here is the link for your reference: (choose appropriate version)

https://cran.r-project.org/bin/windows/Rtools/ check the options as per link below (need to select all the check box while installation)

https://cloud.githubusercontent.com/assets/7400673/12230758/99fb2202-b8a6-11e5-82e6-836159440831.png

    # Adding a worksheets : parameters for addWorksheet are 1. Workbook Name 2. Sheet Name

    addWorksheet(wb, "Sheet 1")
    addWorksheet(wb, "Sheet 2")
    addWorksheet(wb, "Sheet 3")

    # Writing data in to respetive sheets: parameters for writeData are 1. Workbook Name 2. Sheet index/ sheet name 3. dataframe name

    writeData(wb, 1, x)

    # incase you would like to write sheet with filter available for ease of access you can pass the parameter withFilter = TRUE in writeData function.
    writeData(wb, 2, x = y, withFilter = TRUE)

    ## Similarly writeDataTable is another way for representing your data with table formatting:

    writeDataTable(wb, 3, z)

    saveWorkbook(wb, file = "Example.xlsx", overwrite = TRUE)

    end.time <- Sys.time()
    time.taken <- end.time - start.time
    time.taken

openxlsx package is really good for reading and writing huge data from/ in excel files and has lots of options for custom formatting within excel.

The interesting fact is that we dont have to bother about java heap memory here.

Dipstick answered 30/3, 2017 at 12:38 Comment(0)
R
5

tidy way of taking one dataframe and writing sheets by groups:

library(tidyverse)
library(xlsx)
mtcars %>% 
  mutate(cyl1 = cyl) %>% 
  group_by(cyl1) %>% 
  nest() %>% 
  ungroup() %>% 
  mutate(rn = row_number(),
         app = rn != 1,
         q = pmap(list(rn,data,app),~write.xlsx(..2,"test1.xlsx",as.character(..1),append = ..3)))
Recrystallize answered 9/6, 2021 at 9:52 Comment(0)
C
4

I had this exact problem and I solved it this way:

library(openxlsx) # loads library and doesn't require Java installed

your_df_list <- c("df1", "df2", ..., "dfn")

for(name in your_df_list){
  write.xlsx(x = get(name), 
             file = "your_spreadsheet_name.xlsx", 
             sheetName = name)
}

That way you won't have to create a very long list manually if you have tons of dataframes to write to Excel.

Changeover answered 18/7, 2019 at 10:6 Comment(2)
I don't know why this is overwriting the first worksheetStorybook
This does overwrite the sheet, I and others have faced the issue as well. Look here -#57278918Gifted
M
0

For me, WriteXLS provides the functionality you are looking for. Since you did not specify which errors it returns, I show you an example:

Example

library(WriteXLS)
x <- list(sheet_a = data.frame(a=letters), sheet_b = data.frame(b = LETTERS))
WriteXLS(x, "test.xlsx", names(x))

Explanation

If x is:

  • a list of data frames, each one is written to a single sheet
  • a character vector (of R objects), each object is written to a single sheet
  • something else, then see also what the help states:

More on usage

?WriteXLS

shows:

`x`: A character vector or factor containing the names of one or
     more R data frames; A character vector or factor containing
     the name of a single list which contains one or more R data
     frames; a single list object of one or more data frames; a
     single data frame object.

Solution

For your example, you would need to collect all data.frames in a list during the loop, and use WriteXLS after the loop has finished.

Session info

  • R 3.2.4
  • WriteXLS 4.0.0
Moule answered 11/5, 2016 at 7:44 Comment(1)
This package will work but IMHO I would try avoid the dependency of perl (as I would try to avoid the dependency of Java with xlsx) since it makes it more difficult to set-upTwickenham
P
0

I do it in this way for openxlsx using following function

mywritexlsx<-function(fname="temp.xlsx",sheetname="Sheet1",data,
                  startCol = 1, startRow = 1, colNames = TRUE, rowNames = FALSE)
{
  if(! file.exists(fname))
    wb = createWorkbook()
  else
   wb <- loadWorkbook(file =fname)
  sheet = addWorksheet(wb, sheetname)

  writeData(wb,sheet,data,startCol = startCol, startRow = startRow, 
          colNames = colNames, rowNames = rowNames)
  saveWorkbook(wb, fname,overwrite = TRUE)
}
Potheen answered 17/2, 2018 at 18:41 Comment(2)
loadWorkbook is key here for opening existing filesPotheen
Also if one wants to write formulas to excel then there is different function named writeFormula, in addition once you write formula the file needs to be refreshed or reopened then saved and then closed in excel. demo is given here [link(#46914803)Potheen
A
0

I do this all the time, all I do is

WriteXLS::WriteXLS(
    all.dataframes,
    ExcelFileName = xl.filename,
    AdjWidth = T,
    AutoFilter = T,
    FreezeRow = 1,
    FreezeCol = 2,
    BoldHeaderRow = T,
    verbose = F,
    na = '0'
  )

and all those data frames come from here

all.dataframes <- vector()
for (obj.iter in all.objects) {
  obj.name <- obj.iter
  obj.iter <- get(obj.iter)
  if (class(obj.iter) == 'data.frame') {
      all.dataframes <- c(all.dataframes, obj.name)
}

obviously sapply routine would be better here

Ampere answered 30/4, 2019 at 12:12 Comment(0)
G
0

for a lapply-friendly version..

library(data.table)
library(xlsx)

path2txtlist <- your.list.of.txt.files
wb <- createWorkbook()
lapply(seq_along(path2txtlist), function (j) {
sheet <- createSheet(wb, paste("sheetname", j))
addDataFrame(fread(path2txtlist[j]), sheet=sheet, startColumn=1, row.names=FALSE)
})

saveWorkbook(wb, "My_File.xlsx")
Gasometry answered 13/5, 2019 at 23:24 Comment(1)
Could you add some description to this answer to provide context for how this answers the question?Baillargeon

© 2022 - 2024 — McMap. All rights reserved.