How to use xlsx library to Generate multiple xlsx-output in R In Excel
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 framesdf1 = 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 sheet1library(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
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
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 sheet3write.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
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.
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.
Contact Us