How to Create Multiple Sheets in Excel workbook Using R

In this article, we will discuss how to create multiple sheets in an excel file using the xlsx package. As we all know in general an excel file might contain one or more than one sheet present in it. 

Manually we can create and insert data into multiple sheets in Excel GUI Application but when it comes to R Programming by default the data is inserted into Sheet1 but we can also modify it. 

This can be achieved by specifying the sheet names in the write.xlsx() function where we want to insert data.

Syntax:  write.xlsx(df, file, sheetName, col.names, row.names, append, showNA, password)

where,

• df - data frame to be converted
• file - the path to the output excel file is specified here
• sheetName - to string is passed as sheet name
• col.names - logical value indicating if the column names of the data frame are to be written in the file
• row.names - logical value indicating if the column names of the data frame are to be written in the file
• append - when one wants to append the data to an existing file then this value needs to be marked as TRUE
• showNA- If TRUE NA(Null) Will be shown otherwise will leave as empty cells in the excel sheet

Importing Libraries

In this article, we will be requiring xlsx package to create the dataset. 

# install the xlsx package
install.packages("xlsx")
# load the package into working environment
library(xlsx)

Creating dataset and writing in Excel File

In this step we will add as many dataset we want and write them in the excel file. We just need to change the sheetname and it will be done.

# Creation of a sample data frame
df1<-data.frame(
  stu_id=c(1,2,NA,4,5,6,NA,8,9,10),
  stud_name=c('Abhi','Bhuvan','Cherry','Don',
              'Eshwar','Firoz',NA,'Kiran',NA,'Zaheer'),
  age=c(12,13,NA,15,16,NA,NA,12,14,12)
)
# Writing the data frame into an excel file including NULL(NA) values
xlsx::write.xlsx(df1,"C:\\Users\\Downloads\\Multiple_excel.xlsx",
                 sheetName="Sheet1",
                 col.names = TRUE,showNA=TRUE,append=TRUE)

Now we can add one more data frame in the excel file.

# Creation of another sample data frame
df2 <- data.frame(Roll_Number = c(1,2,3,4,5,6,7,8,9,10),
                         Age= c(18,19,20,19,18,17,18,19,17,16),
                         Marks  = c(98,96,89,90,75,35,88,92,78,94))
  
# Inserting another data frame in another sheet of excel file
xlsx::write.xlsx(df2,"C:\\Users\\Downloads\\Multiple_excel.xlsx",
                 sheetName="Sheet2",
                 col.names=TRUE,append=TRUE)

Similarly, we can more sheets to the file. Let's see the output of the above code.

Output:

The output of the second sheet.