Open In App

Export Dataframes to Multiple Excel Sheets in R

Improve
Improve
Like Article
Like
Save
Share
Report

An excel workbook is used to contain tabular information stored within a cell-like structure. Every excel workbook in R contains multiple sheets to contain the data belonging to the same domain information. The excel sheet can contain columns belonging to different data types. The Excel files can be created from data frames using R. 

Using xlsx library to Generate multiple xlsx-output in R

The xlsx package in R can be used to perform read, write, and manipulation operations with Excel files. It is one of the most prominent packages available in all the varied environments to perform Excel document(xls and xlsx) formatting. It has to be downloaded and installed and downloaded into the working space using the command:

install.packages("xlsx")

A data frame can be declared using the data.frame() method, where the columns and rows are declared. Each column is associated with a fixed data type and column name. The data frame stores the data in a tabular-like structure, and this can be written to the Excel sheet, with a customized name. The write.xlsx() method in this package is used to write the data frame onto the specified sheet of the workbook. It has the following syntax : 

write.xlsx(dataframe , file=filepath , sheetName )

Arguments : 

  • dataframe – The data frame to be written to the Excel sheet
  • filepath – The file path with the file name to be created
  • sheetName – The name of the sheet to write the data frame to.

In case, the data frames have to be consecutively written to the next sheets, the append option in the write.xlsx() has to be set to TRUE so that the data entered initially in the earlier sheets is not overwritten.

R




# creating data frames
df1 = data.frame(col1 = c(1:5),
                col2 = c("Michael","Lincoln","Daniel","Fernandes","Eerie"),
                col3 = c(TRUE,FALSE,FALSE,TRUE,TRUE))
df2 = data.frame(col1 = letters[1:3],
                col2 = c("Monday","Tuesday","Wednesday"))
df3 = data.frame(col1 = c(1.2,2.3,5.3,7.3),
                col2 = c("A","B","C","D"))


Now, let’s create the sheet in a progressive manner.

R




# writing the first data frame on sheet1
library(xlsx)
write.xlsx(df1,
           file="/Users/mallikagupta/Desktop/geeks_multiple.xlsx",
           sheetName="sht1")
print("Data Frame1")
print(df1)


Output:

[1] "Data Frame1"
col1      col2  col3
1    1   Michael  TRUE
2    2   Lincoln FALSE
3    3    Daniel FALSE
4    4 Fernandes  TRUE
5    5     Eerie  TRUE
Generating multiple xlsx-output in R

Subsheet 1 added to the main sheet

This first data frame has been added as a subsheet-1 in the main sheet geek_multiple.xlsx.

R




# writing the second data frame on sheet2
write.xlsx(df2,
           file="/Users/mallikagupta/Desktop/geeks_multiple.xlsx",
           sheetName="sht2", append=TRUE)
print("Data Frame2")
print(df2)


Output:

[1] "Data Frame2"
col1      col2
1    a    Monday
2    b   Tuesday
3    c Wednesday
Generating multiple xlsx-output in R

Subsheet 2 added to the main sheet

This second data frame has been added as a subsheet-2 in the main sheet geek_multiple.xlsx.

R




# writing the third data frame on sheet3
write.xlsx(df3,
           file="/Users/mallikagupta/Desktop/geeks_multiple.xlsx",
           sheetName="sht3", append=TRUE)
print("Data Frame3")
print(df3)


Output:

[1] "Data Frame3"
col1 col2
1  1.2    A
2  2.3    B
3  5.3    C
4  7.3    D
Generating multiple xlsx-output in R

Subsheet 3 added to the main sheet

With this last piece of code, we have successfully added the third dataframe as the sub sheet number three in our main excel file geek_multiple.xlsx. So, here we can see that we have populated this geek_multiple.xlsx file with multiple sheets using different dataframe. 

Using openxlsx library to Generate multiple xlsx-output in R

The openxlsx package in R can be downloaded and installed into the R environment and is used to perform read/write and format transactions conveniently. It can be used to write data frames or Tibbles onto Excel sheets. It can also be used to style the worksheets. 

install.packages("openxlsx")

The data frames that are created can be simultaneously mapped to the sheets of the Excel workbook, using the list method. An associative mapping of the sheet-name pointing to the data frame value is created. This creates a list of data frames.

list(key = value, .. )

Arguments : 

keyvalue – The key-value pairs indicative of the sheet name and the corresponding data frame. 

 Then, the write.xlsx() method is then invoked in order to write the entire list of data frames to the specified file path. 

R




# installing the required libraries
library(openxlsx)
 
# mapping the data frames onto the list
data_frames <- list("Sheet 1" = df1, "Sheet 2" = df2, "Sheet 3" = df3)
 
# writing the list of data frames onto the xlsx file
write.xlsx(data_frames,
           file = "/Users/mallikagupta/Desktop/multiple_outputs.xlsx")


Output:

Generating multiple xlsx-output in R

Subsheet 1 added to the main sheet

Generating multiple xlsx-output in R

Subsheet 2 added to the main sheet

Generating multiple xlsx-output in R

Subsheet 3 added to the main sheet

Using writexl library to Generate multiple xlsx-output in R

The writexl package is used to provide the write_xlsx() method which writes the data frame onto the Excel sheet, specified with the extension, xls, and xlsx. It can be downloaded and installed into the working environment using the following command : 

install.packages(writexl)

The wirte_xlsx() method is used to write a list of data frames onto the specified file path location. The data frames are created on the sheet specified in the file path location. 

write_xlsx(list-of-df, file-path)

Arguments : 

  • listofdf – The data frames to be written to the specified file path
  • filepath – The file path to write the xlsx document to

R




# creating data frames
df1 = data.frame(col1 = c(1:5),
                 col3 = c(TRUE,FALSE,FALSE,TRUE,TRUE))
print("Data Frame1")
print(df1)


Output:

[1] "Data Frame1"
 col1  col3
1    1  TRUE
2    2 FALSE
3    3 FALSE
4    4  TRUE
5    5  TRUE

Now let’s create another dataframe so, that we can make the main sheet with at least two sub sheets in it.

R




df2 = data.frame(col1 = letters[1:3],
                 col4 = c(10,20,30)
                 )
print("Data Frame2")
print(df2)


Output:

[1] "Data Frame2"
 col1 col4
1    a   10
2    b   20
3    c   30

Now, let’s create the main sheet with subsheet1 containing data from the first data frame and sub sheet 2 containing the data from the second data frame.

R




# installing the required libraries
library(writexl)
 
# mapping the data frames onto the list
data_frames <- list("sht1" = df1, "sht2" = df2)
 
# writing the list of data frames onto the xlsx file
write_xlsx(data_frames,
           "/Users/mallikagupta/Desktop/approach3.xlsx")


Output:

Generating multiple xlsx-output in R

Subsheet 1 added to the main sheet

Generating multiple xlsx-output in R

Subsheet 2 added to the main sheet



Last Updated : 10 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads