How to Export a Data Frame From R to Microsoft Excel

1. Options to Export a Data Frame From R to Excel
2. Use write.csv() and write.csv2() Functions to Export a Data Frame as a CSV File
3. Use Packages to Export a Data Frame From R to Excel
4. Conclusion

R is a powerful tool for data analysis and visualization. But everybody does not use R.

We may need to send the results of our data analysis to someone as an Excel file, or we may want to display the data in a PowerPoint presentation. Whatever the reason, exporting our data to Excel is a useful skill to have.

Options to Export a Data Frame From R to Excel

R often offers several ways to perform the same task.

This article will learn how to export a data frame to Excel using the write.csv() and write.csv2() functions of the utils package. The utils package is included in the installation of R and is loaded by default.

It is the most general method because it does not require additional packages. But it involves two steps. One, export the data frame as a CSV file. Two, open the CSV file in Excel and save it as an Excel file.

We will also learn about three packages that directly export a data frame as an Excel file. These packages need to be installed by the user. They may also require the presence of other software on the system.

Use write.csv() and write.csv2() Functions to Export a Data Frame as a CSV File

R’s inbuilt functionality allows us to export a data frame to Excel in two steps described below.

The write.csv() and write.csv2() functions create CSV files that spreadsheet software can read. These functions are convenience wrappers to the write.table() function which creates plain text files.

CSV stands for Comma Separated Values. A CSV file is a plain text file. Each row of the data frame becomes a line in the file. Columns are separated by a comma (or a semicolon).

When a data frame is exported using the write.csv() function, R creates a text file in which the comma is used as the field (column) separator, and the full stop is used as the decimal point.

If you want to create a file where the comma is used as the decimal point, you should use the write.csv2() function. This function uses a semicolon as the field separator because a comma is used as the decimal point.

The function documentation states that this is as per the Excel convention for CSV files in some Western European locales.

In the example below, we’ll create a small data frame and export it as a CSV file using the write.csv() function. The first argument is the name of the data frame.

The second argument is the file name in double quotes. The third argument prevents the creation of a column of row numbers.

By default, it will save the file in the current working directory of R. We can find the current working directory using the getwd() function.

# Create two vectors.
myText = c("Unit 1", "Unit 2", "Unit 3")
myNumbers = c(55, 1.53, 0.049)

# Create the data frame using the vectors.
df = data.frame(myText, myNumbers)

# Export the data frame as a CSV file.
# It will save the file in the current working directory of R.
write.csv(df, file="myCSVFile.csv", row.names = FALSE)

Use the write.csv2() function in the last line of code above to create a CSV file that uses the comma as the decimal separator.

Open the CSV File and Save It as an Excel File

To create an Excel file from our CSV file, follow these steps.

If the CSV file does not look right in Excel, open it with a plain text editor to verify that the data has been exported correctly. If Excel can’t display the correct data properly, try the following.

• If Excel creates more or fewer columns than expected, modify the separator option while opening the CSV file. Use the same separator that was found in the CSV file.
• If Excel is not displaying the decimal character correctly, try to change the number format of the concerned columns after opening the file. The system locale setting may also offer a clue.

Use Packages to Export a Data Frame From R to Excel

Several contributed packages allow us to directly export a data frame to Microsoft Excel. We will learn about three such packages.

These packages have several functions and options that allow us to customize various aspects of the Excel file. However, we’ll focus on the basic use of the function that exports a data frame as an Excel file.

If these packages are not already installed, we need to install them.

Please note that R will raise an error if we try to load multiple packages with the same name functions. If this happens, we have to detach the package that we no longer need using the syntax detach(package:name_of_package) before loading the required package.

Use the openxlsx Package to Export a Data Frame From R to Excel

The openxlsx package has the write.xlsx() function to create an Excel file.

On Windows, the openxlsx package requires the RTools software to be installed on the computer. Otherwise, some of its functions may not work.

The first argument to the function is the data frame, and the second argument is the file’s name to create. By default, it will create the file in the current working directory of R.

# Install the openxlsx package if you do not already have it.
# This is a one-time task.
install.packages("openxlsx")

# Load the openxlsx package.
library(openxlsx)

# Use the write.xlsx() function to create the Excel file.
# It will save the file to R's current working directory.
write.xlsx(df, file = "My_openxlsx_File.xlsx")

Use the xlsx Package to Export a Data Frame From R to Excel

The xlsx package requires Java (including JDK) to be installed.

This package has the write.xlsx() function to create an Excel file from a data frame. The first argument is the data frame.

The second argument is the file to be created. The third argument prevents the creation of a column of row numbers.

# Install the xlsx package if you do not already have it.
# This is a one-time task.
install.packages("xlsx")

# If the openxlsx package is currently loaded, detach it.
detach(package:openxlsx)

# Load the xlsx package.
library(xlsx)

# Use the write.xlsx() function to create the Excel file.
# It will save the file to R's current working directory.
write.xlsx(df, file = "My_xlsx_File.xlsx", row.names = FALSE)

Use the writexl Package to Export a Data Frame From R to Excel

The writexl package has the write_xlsx() function to create an Excel file. The first argument is the data frame, and the second argument is the file’s path to create.

If only the file name is given, it gets created in R’s current working directory.

# Install the writexl package if you do not already have it.
# This is a one-time task.
install.packages("writexl")

# Load the writexl package.
library(writexl)

# Use the write_xlsx() function to create the Excel file.
# It will save the file to R's current working directory.
write_xlsx(df, path = "My_writexl_File.xlsx")

Get Help With R Functions in R Studio

For help with R functions in R Studio, click Help > Search R Help and type the function name in the search box without parentheses.

Alternately, type a question mark followed by the function name at the command prompt in the R Console. For example, ?write.table.

For more detailed documentation of any package, search for the package on CRAN.

Conclusion

R’s default facility to write CSV files is sufficient to export a data frame to Microsoft Excel, but it needs two steps. Other packages allow us to export an Excel file in a single step, but first need to be installed.

Some of them also depend on other software. Given these various options, we can choose the best method for us.