From R to excel: Part 1

To export multiple data frames into a single Excel file with multiple sheets

R
Author

Zahier Nasrudin

Published

January 3, 2024

Introduction

Welcome welcome.

In this blog post, we will be exploring:

  1. How to export multiple data frames into a single Excel file with multiple sheets using the writexl and openxlsx packages

By the end of this post, you & I should have a solid understanding of how to export multiple data frames into 1 excel file (.xlsx)

Purpose

Why do you need to export your R output to Excel?

  1. Data Sharing: Excel is a widely used tool for data analysis and reporting.

  2. Further Analysis: Some users might want to perform additional analyses or calculations on the exported data using Excel’s functions, pivot tables, or other features.

  3. User Preference: For some tasks or audiences/stakeholders, Excel might be the preferred tool due to its user-friendly interface or specific features.

How-to

Load library

We will first load the dplyr library:

library(dplyr)

Data sets

For the data frames, we will use datasets available from the dplyr package.

Here are the datasets available in dplyr:

However, for this, we will focus only just four of them:

band_instruments (3 rows and 2 columns)


band_members (3 rows and 2 columns)


star_wars (87 rows and 14 columns)


storms (19,066 rows and 13 columns)

Export (writexl)

I will demonstrate two methods for exporting to an Excel file: using the writexl and openxlsx packages

  • Using writexl package
    • First, define the data frames within a list.
    • Export using write_xlsx
library(writexl)

excel_list <- list("Instruments" = band_instruments,
                   "Members" = band_members,
                   "Star Wars" = starwars,
                   "Storms" = storms)


write_xlsx(excel_list, "file_with_sheets_version1.xlsx")


If you prefer not to format the header (no bold header), you may include format_headers = FALSE:

write_xlsx(excel_list, "file_with_sheets_version1.xlsx", format_headers = FALSE)


Export (openxlsx)

  • Using openxlsx package

    • Follow the same process: define the list.
    • Export using write.xlsx
library(openxlsx)

write.xlsx(excel_list, "file_with_sheets_version2.xlsx")


And there you have it! Now you can send the file to stakeholders who prefer Excel.