Part 3: Save Data Frames as Excel Files

The write_xlsx() function from the writexl package provides a straightforward and efficient way to export data frames as Excel files.

The write_xlsx() function from the writexl package provides a straightforward and efficient way to export data frames as Excel files.

• Use write_xlsx() to export data frames as Excel files
• Use the col_names col_names argument to handle column names

write_xlsx(x = <data.frame>,
           path = "<filename.xlsx",
           col_names = TRUE)

The write_xlsx() Function

write_xlsx(x = <data.frame>,
           path = "<filename.xlsx",

The writexl package is one of the most efficient options when it comes to exporting data frames as Excel worksheets. The write_xlsx() provides a fast and convenient solution to write Excel files, with only a handful of input parameters. It can be used to export a single data frame as an Excel worksheet, but also to write multiple data frames to an Excel workbook.

The main reason for the popularity of write_xlsx() is its speed and simplicity. Consider the following tibble, containing the average spending per order for each customer:

avgSpending

# A tibble: 10 × 5
   OrderID CustID TotalPrice Payment     Status   
     <dbl> <chr>       <dbl> <chr>       <chr>    
 1       1 C1             35 Credit Card Delivered
 2       2 C2             30 Debit Card  Delivered
 3       3 C4             50 Paypal      Delivered
 4       4 C3             13 Credit Card Returned 
 5       5 C5             63 Paypal      Delivered
 6       6 C6             20 Debit Card  Delivered
 7       7 C7             13 Paypal      Delivered
 8       8 C3             50 Debit Card  Returned 
 9       9 C8             13 Credit Card Delivered
10      10 C4             40 Debit Card  Delivered

To write avgSpending to an Excel file with write_xlsx(), we only need to define the following two parameters:

1. The data frame object as the argument x.
2. The path with the name for the output file.

library(writexl)
write_xlsx(x = avgSpending,
           path = "avgSpending.xlsx")

Note: that if we define an existing Excel file for the path, the function will automatically overwrite it.

Optional Argument: col_names

           col_names = TRUE)

By default, the write_xlsx() function will automatically extract the column names from the data frame and use them as column names for the Excel worksheet as well. However, in certain situations we might want to skip the column names and only write the data entries to an Excel file. In these cases we can change the default setting using the col_names argument.

Consider the newsletter_subscriptions tibble with a single column containing the users, who are subscribed to the weekly updates:

newsletter_subscriptions

# A tibble: 10 × 1
   Email                   
   <chr>                   
 1 lisa.rogers@email.com   
 2 mark.harrison@email.com 
 3 robert.adams@email.com  
 4 emily.jenkins@email.com 
 5 sarah.hunter@email.com  
 6 michael.mason@email.com 
 7 ryan.lane@email.com     
 8 megan.cole@email.com    
 9 daniel.stone@email.com  
10 hannah.edwards@email.com

If we want to write this tibble to an Excel file but ignore the column names, we must set the col_names argument to FALSE:

write_xlsx(x = newsletter_subscriptions,
           path = "newsletter_subscriptions.xlsx",
           col_names = FALSE)

Write Multiple Data Frames to an Excel File

The write_xlsx() function supports the export of multiple data frames to an Excel file. In this case, the data frames need to be collected in a list, with each element inserted as an individual worksheet into the output.

• Use write_xlsx() to insert multiple data frames into an Excel file
• Specify a (named) list holding all data frames mapping to worksheet names

write_xlsx(x = list(<sheet_name1> = <data.frame1>,
                    <sheet_name2> = <data.frame2>),
           path = "<filename.xlsx")

Export a Workbook from a List of Data Frames

The write_xlsx() function enables to write multiple data frames to an Excel file. These will be organized in separate worksheets, forming an Excel workbook.

Consider the tibbles productMargins and itemsSold:

productMargins

# A tibble: 5 × 2
  Product        Margin
  <chr>           <dbl>
1 Socks (2 Pair)   0.34
2 Sweatshirt       0.4 
3 T-Shirt          0.22
4 Caps             0.27
5 Pants            0.35

itemsSold

# A tibble: 5 × 2
  Product         Sold
  <chr>          <dbl>
1 Socks (2 Pair)     3
2 Sweatshirt         3
3 T-Shirt            7
4 Caps               4
5 Pants              5

To write these tibbles to an Excel file, we must provide them as a list to write_xlsx(). In addition, we can name the list elements. These names will be used as names for the respective worksheets:

library(writexl)
write_xlsx(x = list(margins = productMargins, sales = itemsSold),
           path = "summaries.xlsx")