Export to Excel in R (XLSX or XLS)

You are currently viewing Export to Excel in R (XLSX or XLS)

How to export or write data to an Excel Spreadsheet in R? You can use the writexl package for exporting data to Excel in XLSX format and the openxlsx package for both XLS and XLSX formats.

In this article, I will explain how to write or export DataFrame to an Excel spreadsheet in R using writexl, openxlsx, and xlsx packages. 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

# Write the data frame to Excel
library(writexl)
write_xlsx(df, '/Users/admin/new_file.xlsx')

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

# write.xlsx() Example using colNames
write.xlsx(df,'/Users/admin/new_file.xls',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.

r export excel

2. Export to XLSX using writexl Package

The writexl package in R provides functions for writing data frames to Excel files in the XLSX format. The main function for exporting data into an Excel file is write_xlsx() function.

2.1 Install writexl Package


# Install  writexl Package
install.packages("writexl")

3.2 Syntax of write.xlsx()

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


# Syntax of write.xlsx()
write_xlsx(data, path, col_names = TRUE, col_width = NULL, format_headers = TRUE)

Arguments-

  • data: The data frame to be written to the Excel file.
  • path: The file path where the Excel file will be saved.
  • col_names: (Optional) A logical value indicating whether to include column names in the Excel file. Default is TRUE.
  • col_width: (Optional) A numeric vector specifying the width of each column in the Excel file. Default is NULL.
  • format_headers: (Optional) A logical value indicating whether to format the column headers. Default is TRUE.

3.3 Export Excel in R

The write_xlsx() function of the writexl package in R is designed for writing data frames to Excel files in the XLSX format. Before going to use the function of the writexl package we need to install it as install.packages(“writexl”). Once the installation is completed we move to the next step i.e. load this package as library(writexl).


# Write the data frame to Excel
# Load writexl package
library(writexl)
write_xlsx(df, '/Users/admin/new_file.xlsx')

In this example, the write_xlsx() function is used to write the data frame df to an Excel file named “new_file.xlsx” in the working directory. The default values are used for optional parameters, such as including column names (col_names = TRUE).

Yields below output.

r export excel

4. 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 spreadsheets or worksheets. You can use write.xlsx() to export DataFrame to an Excel sheet in the form of both xlsx and xls format. To use the 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").

4.1 Install openxlsx Package

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)

4.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.

4.3 Write Excel (xlsx) in R Example

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


# Load openxlsx
library(openxlsx)

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

5. Use xlsx Package to Export Excel File

xlsx R Package provides the write.xlsx() function to write/export Excel spreadsheets or worksheets. To use the 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").

5.1 Install xlsx Package

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


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

5.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
   )

5.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 three packages openxlsx, writexl, and xlsx that provide the method write.xlsx() to export or write DataFrame to an Excel spreadsheet or worksheet.

References

Tags: