The R Stuff - Write to Excel (Multiple Sheets)

This post is about exporting Excel files using R, we will also look at how we can export multiple datasets to multiple sheets within the same workbook.

We will use the writexl package, the billboard and palmerpenguins dataset.

Read data

## Billboard dataset
billboard <- billboard::wiki_hot_100s
glimpse(billboard)

Rows: 5,701
Columns: 4
$ no     <chr> "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", …
$ title  <chr> "Theme from A Summer Place", "He'll Have to Go", "Cathy's Clown…
$ artist <chr> "Percy Faith", "Jim Reeves", "The Everly Brothers", "Johnny Pre…
$ year   <chr> "1960", "1960", "1960", "1960", "1960", "1960", "1960", "1960",…

##Penguins dataset
penguins <- palmerpenguins::penguins
glimpse(penguins)

Rows: 344
Columns: 8
$ species           <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adel…
$ island            <fct> Torgersen, Torgersen, Torgersen, Torgersen, Torgerse…
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, …
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, …
$ flipper_length_mm <int> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186…
$ body_mass_g       <int> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, …
$ sex               <fct> male, female, female, NA, female, male, female, male…
$ year              <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…

Export single sheet

Lets say we want to export the billboard dataset to an excel file, we use the write_xlsx function from the excel package.

writexl::write_xlsx(billboard, "Output/billboard.xlsx")

Export multiple sheets

If we want to export multiple datasets to multiple sheets in a single workbook, all we need to do is create a named list of data frames in R and export it using the same write_xlsx function.

listed_df <- list(billboard, penguins)
names(listed_df) <- c("Billboard Sheet", "Penguin")

glimpse(listed_df)

List of 2
 $ Billboard Sheet:'data.frame':    5701 obs. of  4 variables:
  ..$ no    : chr [1:5701] "1" "2" "3" "4" ...
  ..$ title : chr [1:5701] "Theme from A Summer Place" "He'll Have to Go" "Cathy's Clown" "Running Bear" ...
  ..$ artist: chr [1:5701] "Percy Faith" "Jim Reeves" "The Everly Brothers" "Johnny Preston" ...
  ..$ year  : chr [1:5701] "1960" "1960" "1960" "1960" ...
 $ Penguin        : tibble [344 × 8] (S3: tbl_df/tbl/data.frame)
  ..$ species          : Factor w/ 3 levels "Adelie","Chinstrap",..: 1 1 1 1 1 1 1 1 1 1 ...
  ..$ island           : Factor w/ 3 levels "Biscoe","Dream",..: 3 3 3 3 3 3 3 3 3 3 ...
  ..$ bill_length_mm   : num [1:344] 39.1 39.5 40.3 NA 36.7 39.3 38.9 39.2 34.1 42 ...
  ..$ bill_depth_mm    : num [1:344] 18.7 17.4 18 NA 19.3 20.6 17.8 19.6 18.1 20.2 ...
  ..$ flipper_length_mm: int [1:344] 181 186 195 NA 193 190 181 195 193 190 ...
  ..$ body_mass_g      : int [1:344] 3750 3800 3250 NA 3450 3650 3625 4675 3475 4250 ...
  ..$ sex              : Factor w/ 2 levels "female","male": 2 1 1 NA 1 2 1 2 NA NA ...
  ..$ year             : int [1:344] 2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 ...

write_xlsx(listed_df, "Output/excel_file_multiple_sheets.xlsx")