How to use writexl library to Generate multiple xlsx-output in R In Excel

Using openxlsx 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:

Subsheet 1 added to the main sheet

Subsheet 2 added to the main sheet

Export Dataframes to Multiple Excel Sheets in R

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.