How to Export a Data Frame to an Excel File in R

Introduction

Often, after manipulating or analyzing a data frame in R, you may want to save your results in an Excel file format. Excel is a popular spreadsheet software, and exporting data frames to Excel can allow for further data manipulation or data presentation in a familiar format for non-R users.

This comprehensive guide will explore how to export data frames to Excel files in R using two primary packages: writexl and openxlsx.

1. Exporting Data Frames using the writexl package

The writexl package offers a straightforward method to write data frames to an Excel file without any external dependencies such as Java.

Step 1: Installing the writexl package

If you haven’t already installed the writexl package, you can do so using the install.packages() function:

install.packages("writexl")

Step 2: Loading the writexl package

After the package is installed, load it into your R environment:

library(writexl)

Step 3: Prepare a data frame

Create a data frame. For instance:

# Create a data frame
df <- data.frame(
  Name = c("John", "Sara", "Laura", "Bob"),
  Age = c(23, 27, 22, 24),
  Height = c(5.8, 5.4, 5.6, 5.9),
  Weight = c(68, 59, 65, 73)
)

Step 4: Write the data frame to an Excel file

You can use the write_xlsx() function from the writexl package to write the data frame to an Excel file:

write_xlsx(df, "data.xlsx")

Here, “data.xlsx” is the name of the Excel file to be created.

2. Exporting Data Frames using the openxlsx package

The openxlsx package provides capabilities to write, read, and manipulate Excel files. It gives more control over the Excel workbook, such as modifying cell styles, creating multiple worksheets, and adding formulas.

Step 1: Installing the openxlsx package

You can install the openxlsx package using the install.packages() function:

install.packages("openxlsx")

Step 2: Loading the openxlsx package

After the package is installed, load it into your R environment:

library(openxlsx)

Step 3: Write the data frame to an Excel file

The write.xlsx() function from the openxlsx package is used to write the data frame to an Excel file:

write.xlsx(df, "data.xlsx")

Here, “data.xlsx” is the name of the Excel file to be created. By default, the write.xlsx() function creates a new workbook and a sheet named “Sheet 1” where it writes the data. The function also offers several other parameters for more control, like setting the sheet name, whether to show row names, or adding a password to the file.

Advanced Usage of openxlsx package

In addition to the straightforward writing of a data frame to an Excel file, the openxlsx package allows for more complex Excel file manipulation.

Creating multiple worksheets

You can create multiple worksheets within the same workbook using the createWorkbook(), addWorksheet(), and writeData() functions.

# Create a new workbook
wb <- createWorkbook()

# Add worksheets
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

# Write data to worksheets
writeData(wb, "Sheet 1", df)
writeData(wb, "Sheet 2", df)

# Save workbook
saveWorkbook(wb, "data.xlsx", overwrite = TRUE)

Modifying cell styles

The openxlsx package also allows you to modify cell styles using the createStyle() and addStyle() functions.

# Create a style
style <- createStyle(fontColour = "blue", bgFill = "yellow")

# Add style to cells
addStyle(wb, "Sheet 1", style, rows = 1, cols = 1:4, gridExpand = TRUE)

# Save workbook
saveWorkbook(wb, "data.xlsx", overwrite = TRUE)

Conclusion

This guide provided an in-depth exploration of exporting data frames to Excel files in R using the writexl and openxlsx packages. While the writexl package provides a simple and efficient way to write data frames to Excel, the openxlsx package offers more control over the Excel workbook, including creating multiple worksheets and modifying cell styles. The method you choose will depend on your specific needs and circumstances.