Easy way to export multiple data frame to multiple Excel worksheets

Question

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  lt -             sn  lt -             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

User · Answer

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")

User · Answer

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  lt - Sys time          Creating large data frame     x  lt - as data frame matrix 1 4000000 200000 20       y  lt - as data frame matrix 1 4000000 200000 20       z  lt - as data frame matrix 1 4000000 200000 20          Creating a workbook     wb  lt - 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  lt - Sys time       time taken  lt - 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

User · Answer

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  lt - list  sheet1Name    sheet1   sheet2Name    sheet2   assume sheet1 and sheet2 are data frames write xlsx sheets   path to location

User · Answer

I had this exact problem and I solved it this way   library openxlsx    loads library and doesn t require Java installed  your df list  lt - 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

User · Answer

I do it in this way for openxlsx using following function  mywritexlsx lt -function fname  temp xlsx  sheetname  Sheet1  data                    startCol   1  startRow   1  colNames   TRUE  rowNames   FALSE      if   file exists fname       wb   createWorkbook     else    wb  lt - loadWorkbook file  fname    sheet   addWorksheet wb  sheetname     writeData wb sheet data startCol   startCol  startRow   startRow             colNames   colNames  rowNames   rowNames    saveWorkbook wb  fname overwrite   TRUE

User · Answer

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  lt - 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

User · Answer

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  lt - vector   for  obj iter in all objects      obj name  lt - obj iter   obj iter  lt - get obj iter    if  class obj iter      data frame           all dataframes  lt - c all dataframes  obj name      obviously sapply routine would be better here

User · Answer

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

User · Answer

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  lt -  quot C  R code    file xlsx quot   export list sn1   tempTable1  sn2   tempTable2  sn3   tempTable3   filename

User · Answer

for a lapply-friendly version    library data table  library xlsx   path2txtlist  lt - your list of txt files wb  lt - createWorkbook   lapply seq along path2txtlist   function  j    sheet  lt - createSheet wb  paste  sheetname   j   addDataFrame fread path2txtlist j    sheet sheet  startColumn 1  row names FALSE      saveWorkbook wb   My File xlsx

User · Answer

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  lt - 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

User · Answer

I m not familiar with the package WriteXLS  I generally use XLConnect    library XLConnect     newWB  lt - loadWorkbook    filename  F  TempDir tempwb xlsx     create TRUE     for i in 1 10     wsName  lt - 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

[r] Easy way to export multiple data.frame to multiple Excel worksheets

Examples related to r

Examples related to excel

Examples related to xlsx