Export to Excel in R (XLSX or XLS)

How to export or write data to Excel Spreadsheet in R? You can use write.xlsx() function of openxlsx and xlsx packages to export or write data from DataFrame to an Excel Spreadsheet or worksheet.

In this article, I will explain how to write or export DataFrame to an excel spreadsheet in R by using xlsx and openxlsx packages. In order to use these packages, you need to install them first.

1. Quick Examples

The following are quick examples of how to write or export a DataFrame to an Excel Spreadsheet with extension .xlsx

# Quick Examples

# Using openxlsx package
library(openxlsx)
write.xlsx(df, '/Users/admin/new_file.xlsx')

# write.xlsx() Example using colNames
write.xlsx(df,'/Users/admin/new_file.xlsx',colNames = TRUE)

#using xlsx package
install.packages("xlsx")
write.xlsx(df, '/Users/admin/new_file.xlsx')

# Write.xlsx() Example with sheetName
write.xlsx(df,'/Users/admin/new_file.xlsx', sheetName="Sheet1")

Let’s create a DataFrame in R, run these examples and capture the output.

# Create DataFrame
df <- data.frame(
  id = c(10,11,12,13),
  name = c('sai','ram','deepika','sahithi'),
  gender = c('M','M',NA,'F'),
  dob = as.Date(c('1990-10-02','1981-3-24','1987-6-14','1985-8-16')),
  state = c('CA','NY',NA,NA)
)
df

Yields below output.

# Output
  id    name gender        dob state
1 10     sai      M 1990-10-02    CA
2 11     ram      M 1981-03-24    NY
3 12 deepika   <NA> 1987-06-14  <NA>
4 13 sahithi      F 1985-08-16  <NA>

2. Use openxlsx Package to Write DataFrame to Excel

openxlsx R Package provides several features and high-level interfaces to write/export, format, and edit Excel spreadsheet or worksheet. For example, use write.xlsx() to export DataFrame to excel spreadsheet in R. In order to use openxlsx library, you need to first install it by using install.packages('openxlsx'). Once installation completes, load the openxlsx library to use this write.xlsx() method. To load a library in R use library("openxlsx").

2.1 Install openxlsx Package

In order to install openxlsx package, run the below statement in R or RStudio. This installs all dependencies as well.

# Install openxlsx package
install.packages("openxlsx", dependencies=TRUE)

2.2 Syntax of write.xlsx()

Following is the syntax of the write.xlsx() function.

# Syntax of write.xlsx()
write.xlsx(x, file, asTable = FALSE, overwrite = TRUE, ...)

Arguments–

• x – A data.frame or a (named) list of objects that can be handled by writeData() or writeDataTable() to write to file.
• file – A file path to save the xlsx file.
• asTable – If TRUE will use writeDataTable() rather than writeData() to write x to the file (default: FALSE).
• overwrite – Overwrite existing files (Defaults to TRUE as with write.table).
• and several other arguments.

2.3 Write Excel (xlsx) in R Example

write.xlsx() function is used to write or export list of DataFrames (data.frame) to Excel spreadsheet with extension .xlsx

# Load openxlsx
library(openxlsx)

# write.xlsx() Example
write.xlsx(df,'/Users/admin/new_file.xlsx',colNames = TRUE)

3. Use xlsx Package to Export Excel File

xlsx R Package provides write.xlsx() function to write/export Excel spreadsheet or worksheet. In order to use xlsx library, you need to first install it by using install.packages('xlsx'). Once installation completes, load the xlsx library to use this write.xlsx() method. To load a library in R use library("xlsx").

3.1 Install xlsx Package

In order to install openxlsx package, run the below statement in R or RStudio. This installs all dependencies as well.

# Install xlsx package
install.packages("xlsx", dependencies=TRUE)

3.2 write.xlsx() Syntax

The following is a syntax of the write.xlsx() function of xlsx package.

# Syntax
write.xlsx(x,file,sheetName = "Sheet1",col.names = TRUE,
     row.names = TRUE,append = FALSE,showNA = TRUE,
     password = NULL
   )

3.3 Write DataFrame to xlsx in R Example

The following example using write.xlsx() function is used to write or export a list of DataFrames (data.frame) to an Excel spreadsheet with the extension .xlsx

# Load openxlsx
library(xlsx)

# Write.xlsx() Example
write.xlsx(df,'/Users/admin/new_file.xlsx', sheetName="Sheet1")

Conclusion

In this article, you have learned two package openxlsx, xlsx that provides the method write.xlsx() to export or write DataFrame to an excel spreadsheet or worksheet.

Related Articles

• How to Import Text File as a String in R
• How to Read Text File to DataFrame in R
• How to Read CSV From URL in R
• How to Read Multiple CSV Files in R
• How to Read CSV Files in R
• How to Export CSV in R Using write.csv()

References

• https://www.rdocumentation.org/packages/openxlsx/versions/4.2.5