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.

Data to export

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

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

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.

## [1] "airquality" "mtcars"     "iris"       "diamonds"

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

## $airquality
## # A tibble: 153 x 6
##    Ozone Solar.R  Wind  Temp Month   Day
##    <dbl>   <dbl> <dbl> <dbl> <dbl> <dbl>
##  1    41     190   7.4    67     5     1
##  2    36     118   8      72     5     2
##  3    12     149  12.6    74     5     3
##  4    18     313  11.5    62     5     4
##  5    NA      NA  14.3    56     5     5
##  6    28      NA  14.9    66     5     6
##  7    23     299   8.6    65     5     7
##  8    19      99  13.8    59     5     8
##  9     8      19  20.1    61     5     9
## 10    NA     194   8.6    69     5    10
## # … with 143 more rows
## 
## $mtcars
## # A tibble: 32 x 11
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
##  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4
##  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4
##  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
##  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
##  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
##  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
##  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
##  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2
##  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
## 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
## # … with 22 more rows
## 
## $iris
## # A tibble: 150 x 5
##    Sepal.Length Sepal.Width Petal.Length Petal.Width Species
##           <dbl>       <dbl>        <dbl>       <dbl> <chr>  
##  1          5.1         3.5          1.4         0.2 setosa 
##  2          4.9         3            1.4         0.2 setosa 
##  3          4.7         3.2          1.3         0.2 setosa 
##  4          4.6         3.1          1.5         0.2 setosa 
##  5          5           3.6          1.4         0.2 setosa 
##  6          5.4         3.9          1.7         0.4 setosa 
##  7          4.6         3.4          1.4         0.3 setosa 
##  8          5           3.4          1.5         0.2 setosa 
##  9          4.4         2.9          1.4         0.2 setosa 
## 10          4.9         3.1          1.5         0.1 setosa 
## # … with 140 more rows
## 
## $diamonds
## # A tibble: 53,940 x 10
##    carat cut       color clarity depth table price     x     y     z
##    <dbl> <chr>     <chr> <chr>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
##  1 0.23  Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
##  2 0.21  Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
##  3 0.23  Good      E     VS1      56.9    65   327  4.05  4.07  2.31
##  4 0.290 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
##  5 0.31  Good      J     SI2      63.3    58   335  4.34  4.35  2.75
##  6 0.24  Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
##  7 0.24  Very Good I     VVS1     62.3    57   336  3.95  3.98  2.47
##  8 0.26  Very Good H     SI1      61.9    55   337  4.07  4.11  2.53
##  9 0.22  Fair      E     VS2      65.1    61   337  3.87  3.78  2.49
## 10 0.23  Very Good H     VS1      59.4    61   338  4     4.05  2.39
## # … with 53,930 more rows

I hope you enjoy this article. Its Github repository can be found here and if you like this write up, can also follow me on Twitter and Linkedin for more updates in R, Python, and Excel for data science.

LS0tDQp0aXRsZTogIkV4cG9ydCBtdWx0aXBsZSBkYXRhZnJhbWVzIGluIFIgdG8gTVMtRXhjZWwgd29ya2Jvb2siDQpzdWJ0aXRsZTogIndpdGggb3Blbnhsc3g6OndyaXRlLnhsc3goKSBmdW5jdGlvbiINCmF1dGhvcjogfA0KICB8IFtPZ3VuZGVwbyBFemVraWVsIEFkZWJheW9dKGh0dHBzOi8vYml0Lmx5L2diZ2FuYWx5c3QpDQogIHwgW0knbSBvbiBUd2l0dGVyXShodHRwczovL3R3aXR0ZXIuY29tL2diZ2FuYWx5c3QpDQpkYXRlOiAiYHIgZm9ybWF0KFN5cy50aW1lKCksICclQiAlZCwgJVknKWAiDQpvdXRwdXQ6IA0KICBodG1sX2RvY3VtZW50Og0KICAgIGRmX3ByaW50OiBwYWdlZA0KICAgIHRoZW1lOiBzaW1wbGV4DQogICAgaGlnaGxpZ2h0OiBlc3ByZXNzbw0KICAgIHRvYzogdHJ1ZQ0KICAgIHRvY19mbG9hdDogdHJ1ZQ0KICAgIGNvZGVfZG93bmxvYWQ6IHRydWUNCiAgICBjb2RlX2ZvbGRpbmc6IHNob3cNCmVkaXRvcl9vcHRpb25zOiANCiAgY2h1bmtfb3V0cHV0X3R5cGU6IGNvbnNvbGUNCi0tLQ0KDQpgYGB7ciBzZXR1cCwgaW5jbHVkZT1GQUxTRX0NCg0KIyBEb2N1bWVudCBzZXR0aW5ncyBmb3IgUiBNYXJrZG93bg0KDQprbml0cjo6b3B0c19jaHVuayRzZXQoZWNobyA9IFRSVUUsDQogICAgICAgICAgICAgICAgICAgICAgdGlkeSA9ICJzdHlsZXIiLA0KICAgICAgICAgICAgICAgICAgICAgIG91dC53aWR0aCA9ICIxMDAlIiwNCiAgICAgICAgICAgICAgICAgICAgICBtZXNzYWdlID0gRkFMU0UsDQogICAgICAgICAgICAgICAgICAgICAgd2FybmluZyA9IEZBTFNFKQ0KYGBgDQoNCiMgSW5zdHJ1Y3Rpb25zDQoNClRoaXMgcHJvZ3JhbSBpcyB3cml0dGVuIGluIFIgcHJvZ3JhbW1pbmcgbGFuZ3VhZ2UgdmVyc2lvbiAzLjYuMS4gSW4gYWRkaXRpb24gdG8gYmFzZSBSLCB0aGUgZm9sbG93aW5nIFIgcGFja2FnZXMgd2VyZSB1c2VkIGluIHRoaXMgdHV0b3JpYWw6DQoNCi0gcGFja2FnZSAib3Blbnhsc3giIHZlcnNpb24gNC4xLjAuMQ0KLSBwYWNrYWdlICJyZWFkeGwiIHZlcnNpb24gMS4zLjEgDQotIHBhY2thZ2UgIm1hZ3JpdHRyIiB2ZXJzaW9uIDEuNQ0KLSBwYWNrYWdlICJwdXJyciIgdmVyc2lvbiAwLjMuMg0KLSBwYWNrYWdlICJnZ3Bsb3QyIiB2ZXJzaW9uIDMuMi4xDQoNClRoaXMgcHJvZ3JhbSB3aWxsIGRvd25sb2FkIGZyb20gdGhlIGludGVybmV0IGFuZCBpbnN0YWxsIHRoZSBsYXRlc3QgdmVyc2lvbiBvZiB0aGUgYWJvdmUgcGFja2FnZXMgSWYgdGhleSBhcmUgbm90IGluc3RhbGxlZCBpbiB5b3VyIFIgZW52aXJvbm1lbnQuIEl0IGlzIG5lY2Vzc2FyeSB0byBoYXZlIGludGVybmV0IGNvbm5lY3Rpb24gdG8gZG93bmxvYWQgdGhlc2UgcGFja2FnZXMuIA0KDQojIEludHJvZHVjdGlvbg0KDQpgb3Blbnhsc3hgIHBhY2thZ2UgbWFrZXMgaXQgZWFzaWVyIHRvIGV4cG9ydCBtdWx0aXBsZSBkYXRhZnJhbWVzIGluIGBSYCB0byBgRXhjZWxgIHdvcmtib29rIHdpdGggZWFjaCBkYXRhZnJhbWUgaW4gUiBjb3JyZXNwb25kcyB0byBhIHdvcmtzaGVldCBpbiBFeGNlbC4NCg0KIyBQYWNrYWdlDQoNCmBgYHtyIHBhY2thZ2UsIHdhcm5pbmc9RkFMU0UsIG1lc3NhZ2U9RkFMU0V9DQoNCnBhY2thZ2VzIDwtIGMoIm9wZW54bHN4IiwgInJlYWR4bCIsICJtYWdyaXR0ciIsICJwdXJyciIsICJnZ3Bsb3QyIikNCg0KaWYgKCFyZXF1aXJlKGluc3RhbGwubG9hZCkpIHsNCiAgaW5zdGFsbC5wYWNrYWdlcygiaW5zdGFsbC5sb2FkIikNCn0NCg0KaW5zdGFsbC5sb2FkOjppbnN0YWxsX2xvYWQocGFja2FnZXMpDQoNCmBgYA0KDQojIERhdGEgdG8gZXhwb3J0DQoNCkZvciBleGFtcGxlLCB3ZSB3YW50IHRvIGV4cG9ydCBgYWlycXVhbGl0eWAsIGBtdGNhcnNgLCBgaXJpc2AsIGFuZCBgZGlhbW9uZHNgIGRhdGFzZXQgdG8gYSBkaWZmZXJlbnQgRXhjZWwgd29ya3NoZWV0Lg0KDQpgYGB7ciBkYXRhc2V0fQ0KZGF0YWZyYW1lMSA8LSBkYXRhc2V0czo6YWlycXVhbGl0eQ0KZGF0YWZyYW1lMSAlPiUgaGVhZCgpDQoNCmRhdGFmcmFtZTIgPC0gZGF0YXNldHM6Om10Y2Fycw0KZGF0YWZyYW1lMiAlPiUgaGVhZCgpDQoNCg0KZGF0YWZyYW1lMyA8LSBkYXRhc2V0czo6aXJpcw0KZGF0YWZyYW1lMyAlPiUgaGVhZCgpDQoNCmRhdGFmcmFtZTQgPC0gZ2dwbG90Mjo6ZGlhbW9uZHMNCmRhdGFmcmFtZTQgJT4lIGhlYWQoKQ0KYGBgDQoNCk5vdyB0aGF0IHdlIGhhdmUgb3VyIGRhdGFmcmFtZXMgaW4gUiwgd2Ugc2hhbGwgbmFtZSB0aGUgRXhjZWwgc2hlZXRzIGFjY29yZGluZyB0byB0aGUgbmFtZSBvZiB0aGUgZGF0YWZyYW1lcyBpbiBSLg0KDQoqIHNoZWV0MS0gYWlycXVhbGl0eQ0KKiBzaGVldDItIG10Y2Fycw0KKiBzaGVldDMtIGlyaXMNCiogc2hlZXQ0LSBkaWFtb25kcw0KDQpgYGB7ciBFeHBvcnQgZXhjZWwsIG1lc3NhZ2UgPSBGQUxTRSwgd2FybmluZyA9IEZBTFNFfQ0KbGlzdF9vZl9kYXRhc2V0cyA8LSANCiAgbGlzdCgiYWlycXVhbGl0eSIgPSBkYXRhZnJhbWUxLCAibXRjYXJzIiA9IGRhdGFmcmFtZTIsICJpcmlzIiA9IGRhdGFmcmFtZTMsICJkaWFtb25kcyIgPSBkYXRhZnJhbWU0KQ0KDQp3cml0ZS54bHN4KGxpc3Rfb2ZfZGF0YXNldHMsICJFeGNlbCB3b3JrYm9vay54bHN4IikNCmBgYA0KDQpXZSBjYW4gbm93IGxvb2sgZm9yIGBFeGNlbCB3b3JrYm9vay54bHN4YCBmaWxlIGluIHRoZSB3b3JraW5nIGRpcmVjdG9yeS4NCg0KIyBGb2xsb3cgdXANCg0KV2UgY2FuIHVzZSBNaWNyb3NvZnQgRXhjZWwgdG8gb3BlbiAqKkV4Y2VsIHdvcmtib29rLnhsc3gqKiBmaWxlIGluIHRoZSB3b3JraW5nIGRpcmVjdG9yeSBkaXJlY3RseSBidXQgd2UgY2hvc2UgdG8gdXNlIFIgdG8gc2VlIHRoZSB3b3Jrc2hlZXRzIHdpdGggdGhlIGhlbHAgb2YgYHJlYWR4bDo6ZXhjZWxfc2hlZXRzKClgIGZ1bmN0aW9uLg0KDQpgYGB7ciBleGNlbCB3b3Jrc2hlZXRzfQ0KZXhjZWxfc2hlZXRzKCJFeGNlbCB3b3JrYm9vay54bHN4IikNCmBgYA0KDQpJbiBvcmRlciB0byBzZWUgd2hhdCBlYWNoIGV4Y2VsIHdvcmtzaGVldCBjb250YWlucywgd2UgdXNlIGBwdXJycjo6bWFwKClgIGZ1bmN0aW9uIA0KDQpgYGB7ciBSZWNoZWNrfQ0KDQpwYXRoIDwtICJFeGNlbCB3b3JrYm9vay54bHN4Ig0KDQpwYXRoICU+JQ0KICAgICAgICBleGNlbF9zaGVldHMoKSAlPiUNCiAgICAgICAgc2V0X25hbWVzKCkgJT4lDQogICAgICAgbWFwKHJlYWRfZXhjZWwsIHBhdGg9cGF0aCkNCiAgICAgICAgDQpgYGANCg0KLS0tDQpJIGhvcGUgeW91IGVuam95IHRoaXMgYXJ0aWNsZS4gSXRzIEdpdGh1YiByZXBvc2l0b3J5IGNhbiBiZSBmb3VuZCBbaGVyZV0oaHR0cHM6Ly9naXRodWIuY29tL2diZ2FuYWx5c3QvRXhwb3J0LVItZGF0YWZyYW1lcy10by1FeGNlbC13b3JrYm9vaykgYW5kIGlmIHlvdSBsaWtlIHRoaXMgd3JpdGUgdXAsIGNhbiBhbHNvIGZvbGxvdyBtZSBvbiBbVHdpdHRlcl0oaHR0cHM6Ly93d3cudHdpdHRlci5jb20vZ2JnYW5hbHlzdCl7dGFyZ2V0PSJfYmxhbmsifSBhbmQgW0xpbmtlZGluXShodHRwczovL3d3dy5saW5rZWRpbi5jb20vaW4vZXpla2llbC1vZ3VuZGVwby8pe3RhcmdldD0iX2JsYW5rIn0gZm9yIG1vcmUgdXBkYXRlcyBpbiBgUmAsIGBQeXRob25gLCBhbmQgYEV4Y2VsYCBmb3IgZGF0YSBzY2llbmNlLg0KDQo=