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)),
hires=
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)) {
install.packages("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.
library(openxlsx)
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)
FAQ
I have huge dataset