How to write excel multiple sheets in R?

Use the following code to export multiple DataFrame objects to an Excel workbook containing multiple sheets.

Step 1: Create a list of DataFrames

We will first define a simple list comprising two DataFrames that we would like to write into our Excel file.

hr_list <- list(candidates = 
                data.frame (area = c('R', 'Python', 'Power BI'), num_candidates = c(122, 133, 155)),
                data.frame (area = c('R', 'Python', 'Power BI'), num_hires = c(23, 27, 31)))

Step 2: Import the openxlsx package

Next we will ensure that the openxlsx package is installed in our RStudio environment. If not, we will install it:

if (!require(openxlsx)) {

Step 3: Write the DataFrames to an Excel file

Next, we will create a workbook object, then loop through the list of DataFrame and write each of them into a new Worksheet.


hr_workbook <- createWorkbook()

for (i in 1:length(hr_list)) {
  hr_sheet_name <- paste("WS",i, sep = "-")
  addWorksheet(hr_workbook, hr_sheet_name)
  writeData(hr_workbook, hr_sheet_name, hr_list[[i]])

Step 4: Save your workbook

Last step is to define a path in the file system for your Excel file and to save the Workbook you write in the previous step.

xlsx_path = 'hr.xlsx'
saveWorkbook(hr_workbook, xlsx_path, overwrite = TRUE)

The overwrite = TRUE allows (if needed) to suppress the file it is exists in the file system.

Here’s a screenshot from the file we created:

Step 5: Append data to an existing workbook

Here’s the code you can use in order to add additional data to an already existing workbook

attrition = data.frame (area = c('R', 'Python', 'Power BI'), num_attrition = c(2, 3, 6)),
hr_workbook<- loadWorkbook(xlsx_path)
writeData(hr_workbook, ""Sheet3""WS - 1", attrition)
saveWorkbook(hr_workbook, xlsx_path, overwrite = TRUE)

Suggested Learning

How to create and append text files in RStudio?