Export-R-dataframes-to-Excel-workbook.Rmd

---
title: "Export multiple dataframes in R to MS-Excel workbook"
subtitle: "with openxlsx::write.xlsx() function"
author: |
  | [Ogundepo Ezekiel Adebayo](https://bit.ly/gbganalyst)
  | [I'm on Twitter](https://twitter.com/gbganalyst)
date: "`r format(Sys.time(), '%B %d, %Y')`"
output: 
  html_document:
    df_print: paged
    theme: simplex
    highlight: espresso
    toc: true
    toc_float: true
    code_download: true
    code_folding: show
editor_options: 
  chunk_output_type: console
---

```{r setup, include=FALSE}

# Document settings for R Markdown

knitr::opts_chunk$set(echo = TRUE,
                      tidy = "styler",
                      out.width = "100%",
                      message = FALSE,
                      warning = FALSE)
```

# Instructions

This program is written in R programming language version 3.6.1. In addition to base R, the following R packages were used in this tutorial:

- package "openxlsx" version 4.1.0.1
- package "readxl" version 1.3.1 
- package "magrittr" version 1.5
- package "purrr" version 0.3.2
- package "ggplot2" version 3.2.1

This program will download from the internet and install the latest version of the above packages If they are not installed in your R environment. It is necessary to have internet connection to download these packages. 

# Introduction

`openxlsx` package makes it easier to export multiple dataframes in `R` to `Excel` workbook with each dataframe in R corresponds to a worksheet in Excel.

# Package

```{r package, warning=FALSE, message=FALSE}

packages <- c("openxlsx", "readxl", "magrittr", "purrr", "ggplot2")

if (!require(install.load)) {
  install.packages("install.load")
}

install.load::install_load(packages)

```

# Data to export

For example, we want to export `airquality`, `mtcars`, `iris`, and `diamonds` dataset to a different Excel worksheet.

```{r dataset}
dataframe1 <- datasets::airquality
dataframe1 %>% head()

dataframe2 <- datasets::mtcars
dataframe2 %>% head()


dataframe3 <- datasets::iris
dataframe3 %>% head()

dataframe4 <- ggplot2::diamonds
dataframe4 %>% head()
```

Now that we have our dataframes in R, we shall name the Excel sheets according to the name of the dataframes in R.

* sheet1- airquality
* sheet2- mtcars
* sheet3- iris
* sheet4- diamonds

```{r Export excel, message = FALSE, warning = FALSE}
list_of_datasets <- 
  list("airquality" = dataframe1, "mtcars" = dataframe2, "iris" = dataframe3, "diamonds" = dataframe4)

write.xlsx(list_of_datasets, "Excel workbook.xlsx")
```

We can now look for `Excel workbook.xlsx` file in the working directory.

# Follow up

We can use Microsoft Excel to open **Excel workbook.xlsx** file in the working directory directly but we chose to use R to see the worksheets with the help of `readxl::excel_sheets()` function.

```{r excel worksheets}
excel_sheets("Excel workbook.xlsx")
```

In order to see what each excel worksheet contains, we use `purrr::map()` function 

```{r Recheck}

path <- "Excel workbook.xlsx"

path %>%
        excel_sheets() %>%
        set_names() %>%
       map(read_excel, path=path)
        
```

---
I hope you enjoy this article. Its Github repository can be found [here](https://github.com/gbganalyst/Export-R-dataframes-to-Excel-workbook) and if you like this write up, can also follow me on [Twitter](https://www.twitter.com/gbganalyst){target="_blank"} and [Linkedin](https://www.linkedin.com/in/ezekiel-ogundepo/){target="_blank"} for more updates in `R`, `Python`, and `Excel` for data science.