openxlsx::write.xlsx()

Function of the Week

Write data.frame or a list of data.frames to an xlsx file

1 write.xlsx()

In this document, I will introduce the write.xlsx() function and show what it’s for.

library(openxlsx)
library(here)

here() starts at /Users/niederha/Library/CloudStorage/OneDrive-OregonHealth&ScienceUniversity/teaching/BSTA 526/BSTA_526_shared/webpage/Function_of_the_week_BSTA526_W24

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.3     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.3     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.1     

── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(palmerpenguins)
data(penguins)

1.1 What is it for?

Write write.xlsx() allows you to save the data frames you have created and modified as new excel sheets.

Let’s say you want to create a separate excel sheet for a specific group. In this example we’ll take the palmerspenguins data set and make a new excel sheet with only the Adelie penguins.

#filter for Adelie penguins 
penguins_adelie <- penguins %>%
  filter(species == "Adelie")%>%
  print()

# A tibble: 152 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 142 more rows
# ℹ 2 more variables: sex <fct>, year <int>

#save as new excel sheet
write.xlsx(penguins_adelie, 
           file = "penguins_adeile.xlsx")

The excel file is only useful if you know where it is! You can also use write.xlsx with the here() function to help specify a specific location you would like to save the new excel file. For example I can use here() to specify that I want to save the excel file to my BSTA 526 folder so it makes it easier to find later.

#to save in functions of the week folder 
write.xlsx(penguins_adelie, 
           file = here("penguins_adiele.xlsx"))

You can also use write.xlsx to write overwrite an existing file with the overwrite argument. For example, I want to create a data frame from the palmerpenguins data set that only has Adelie penguins that live on Torgersen and overwrite our existing file we created above.

#filtering for island Torgersen
penguins_adelie_torgersen <- penguins_adelie %>%
  filter(island == "Torgersen")%>%
  print

# A tibble: 52 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 42 more rows
# ℹ 2 more variables: sex <fct>, year <int>

#to overwrite previous file 
write.xlsx(penguins_adelie_torgersen, 
           file = here("penguins_adiele.xlsx"),
           overwrite = TRUE)

1.2 Is it helpful?

write.xlsx() is straightforward and helpful tool since it allows you to save excel sheets of data you have cleaned and/or modified. I could see this being extremely helpful if you need to share cleaned excel sheets of the data with other people or publishers. While it could be useful to use the overwrite argument to overwrite messy excel files this may not be in best practice.