Export an R data frame into a formatted Excel sheet

A straightforward method for exporting an R data frame to a formatted Excel workbook.

I typically work with CSV files in R these days, but sometimes there is a requirement to make data available in proprietary XLSX format for Microsoft Excel users.

The process for exporting data frames into Excel is quite straightforward. To do this, we require the OpenXLSX package.

To demonstrate its utility, I am going to use the iris dataset:

data("iris")

Next, I need to create a Workbook:

wb <- createWorkbook()

I’ll add a new worksheet to the workbook, which I will call “Iris”

addWorksheet(wb,"Iris")

Next, I need to write the data table to the Excel workbook, by specifying that I want the iris dataframe written to the “Iris” worksheet, and formatted using TableStyleLight6:

writeDataTable(
  wb, 
  "Iris", 
  iris,
  tableStyle = "TableStyleLight6")

The columns in the Excel file will be narrow and may conceal data. Rather than manually widening them, you can do it automatically:

setColWidths(wb, 
             "Iris", 
             cols = 1:ncol(iris), 
             widths = "auto

Finally, it’s time to save the Excel workbook to file:

saveWorkbook(wb,
             "/path/to/iris_data.xlsx", 
             overwrite = TRUE)

You can even combine multiple data sets together. For instance iris can be placed in one worksheet and mtcars in another but saved to the same file with different formats:

# Load data
data("iris")
data("mtcars")

# Export to XLSX in a formatted table
wb <- createWorkbook()
addWorksheet(wb,"Iris")
addWorksheet(wb,"Cars")
writeDataTable(
  wb, 
  "Iris", 
  iris,
  tableStyle = "TableStyleLight6")
writeDataTable(
  wb, 
  "Cars", 
  mtcars,
  tableStyle = "TableStyleLight4")
setColWidths(wb, 
             "Iris", 
             cols = 1:ncol(iris), 
             widths = "auto")
setColWidths(wb, 
             "Cars", 
             cols = 1:ncol(mtcars), 
             widths = "auto")
saveWorkbook(wb,
             "/path/to/iris_and_cars.xlsx", 
             overwrite = TRUE)

Let’s have a look:

Have Your Say

Δ