Export data from R to Excel

Here is how to export data from R to Excel. If you want to write an R data frame in an xlsx file, here are multiple ways how to do that. You can do that quickly or by customizing Excel file content formatting and structure.

Consider copying data from R to Excel by using a clipboard

If you have to quickly get your data into an Excel file and this task does not recur frequently, then consider copying and pasting that. Usually, it is faster than writing data in an Excel file with R.
There are multiple methods on how to do that, and here are some of them that I found the best.

Export data from R to Excel with the writexl package

Here is a fast and simple way how to write a data frame in an xlsx file. There is nothing much to do with formatting.

require(writexl)

write_xlsx(iris, "C:\\MyLocation\\iris.xlsx")

If you want to read an Excel file with R, then here is how to do that.

Export data to Excel in R with the openxlsx package

Package openxlsx is my favorite. It allows me to do almost all necessary modifications to Excel file content. It also operates without dependency on Java.

In the beginning, you are creating a workbook object with the createWorkbook function. After that, you can add all the necessary content and formatting.

require(openxlsx)

wb <- createWorkbook()

#add sheet and enable gridlines
addWorksheet(wb, sheet = "iris", gridLines = TRUE)

By using openxlsx, you can add typically used Excel tools like filters or freeze panes. Here is how to add filters to the exported data and, at the same time, write data into the workbook object with the function writeData.

#add filters to all the columns and write data in workbook object
writeData(wb, sheet = "iris", x = iris, rowNames = FALSE, withFilter = TRUE)

If you want to freeze the first Excel spreadsheet row by using R, you can do that with the freezePane function.

#freeze first row 
freezePane(wb, sheet = "iris", firstRow = TRUE)

When all the formatting is complete, export data from R to Excel with the function saveWorkbook.

#save and overwrite Excel file
saveWorkbook(wb, "C:\\MyLocation\\iris.xlsx", overwrite = TRUE)

Here are a couple of other examples of Excel export formatting with openxlsx.

Export data to Excel with R and format as Excel table object

Here is how to use the openxlsx package and format the exported data as an Excel table object. You can do that at the same time when writing data into the workbook object.

writeDataTable(wb, sheet = "iris", iris, tableStyle = "none",  tableName = "MyName")

Write data in Excel with R and preserve existing content

With package openxlsx, you can overwrite existing Excel files and preserve existing content. For example, there is already PivotTable in a separate sheet and you want to update information in other sheets.

First of all, you have to load the existing Excel file into the workbook object in R.

#load existing Excel workbook
wb <- loadWorkbook("C:\\MyLocation\\iris.xlsx")

Then do necessary modifications as usual and overwrite that file.

writeData(wb, sheet = "iris", iris, rowNames = FALSE, withFilter = TRUE)

freezePane(wb, sheet = "iris", firstRow = TRUE)

saveWorkbook(wb, "C:\\MyLocation\\iris.xlsx", overwrite = TRUE)

Overwrite named Excel region or table object by using openxlsx

Once you created a named region in Excel with the openxlsx function createNamedRegion, you cannot overwrite that in the same way.
Instead, you will get the message “Named region with name … already exists!”. In the case of an existing Excel table object you will get message “Table with name … already exists!”

At this moment, no function in openxlsx can change the named region, but here is how to overcome that. Remove worksheet that contains named region with function removeWorksheet and add that again.

wb <- loadWorkbook("C:\\MyLocation\\iris.xlsx")

removeWorksheet(wb, "iris")

addWorksheet(wb, "iris", gridLines = TRUE)

writeData(wb, sheet = "iris", iris, rowNames = FALSE, withFilter = TRUE)

createNamedRegion(
  wb = wb,
  sheet = "iris",
  name = "MyIris",
  rows = 1:(nrow(iris) + 1),
  cols = 1:ncol(iris)
)

#in case of table object
#writeDataTable(wb, sheet = "iris", iris, tableStyle = "none",  tableName = "MyName")

saveWorkbook(wb, "C:\\MyLocation\\iris.xlsx", overwrite = TRUE)

Important! When you delete the Excel sheet with openxlsx, there should be at least one sheet besides that. If you deleted the only worksheet in the workbook object, it doesn’t matter what you add. You will get a workbook corrupted message when opening the Excel file, and the file will not open.

openxlsx error: ‘itr’ does not name a type

While installing the openxlsx package, you might get an error message that contains “‘itr’ does not name a type”. Whatever it means, it has to do something with your current R version, and the solution for me was an installation of an older version of package openxlsx.
Older versions you can find here.

A great tutorial on how to install an older version of the package in R is here.

You might be interested in how to read Excel file content with R.