Saving to Excel (from R)

Saving to Excel (from R)

Oftentimes, as R coders, the end deliverable of our amazing analysis is an Excel workbook. Handing over one of these to a client gives them a reliable record of your work and also is something that their own staff can review, at a later date.

Here are a few tips to empower your less technical clients with some bonus Excel-based data literacy:

  1. Format the data as a proper table, with the “filter downs” displayed. End-users love being able to drill down to the specific rows they’re interested in, to check that certain parts of your data are as they expect.
  2. Add formula columns (where applicable). This takes a fair but of effort. But showing how certain columns are derived from the others, might be one way of making your work more easily interpretable (depends on the circumstance).
  3. Include an already set up PivotTable. Showing a client how to use a PivotTable for the first time is often a game changer (for them). Having one available in the end-result allows you and your client to have a immediate exploration of the data, where you can ("on the fly") demonstrate how to aggregate, filter and get answers out of the raw data.

Let's take a look at how we'd accomplish these, via the opensxlsx R package.


1. Saving as a table

Let's get things set up...

library(tidyverse)
library(openxlsx)

# complete your "fancy" analysis

fancy_analysis <- diamonds %>% 
  mutate(cost_per_carat = price / carat)

For a quick 1-page workbook, with a table:

fancy_analysis %>% 
  write.xlsx("1_quick_single_sheet_export.xlsx", asTable = T)

For a more customized output, the function gets a bit more involved.

wb <- createWorkbook()
addWorksheet(wb, "analysis results")

fancy_analysis %>% 
  writeDataTable(wb, "analysis results", ., tableStyle = "TableStyleMedium2", withFilter = T)

saveWorkbook(wb, "2_slightly_nicer_table.xlsx", overwrite = TRUE)

But this method allows for:

  • multiple worksheets
  • specific table formatting
  • column data-type formatting
  • column sizing
  • conditional formatting (eg, databars)
wb <- createWorkbook()
addWorksheet(wb, "analysis results")

fancy_analysis %>% 
  writeDataTable(wb, "analysis results", ., tableStyle = "TableStyleMedium2", withFilter = T)
  

conditionalFormatting(wb, "analysis results", cols = 5, 
                      rows = 1:(nrow(fancy_analysis)+1),
                      type = "databar")

setColWidths(wb, "analysis results", cols = 11, widths = 15)


addStyle(wb, "analysis results", 
         style = createStyle(numFmt="$0"), 
         rows = 1:(nrow(fancy_analysis)+1), 
         cols = 11)

saveWorkbook(wb, "2_slightly_nicer_table_with_some_column_formatting.xlsx", overwrite = TRUE)

Note that cost_per_carat (column K) now has currency formatting and is a bit wider. Cool, right?

2. Adding formulas to columns

As mentioned, these take a bit more work. This is the end result:

Unfortunately, these formulas are not in the correct Table reference format. Which should be:

In my research, it looked like adding this proper formula format was still in "feature request" stage.

If you think it will be meaningful to add the formulas, you basically have to turn your mutate step into a text string of the corresponding Excel formula. I usually work it out in Excel first, then copy paste it into the R code.

export_df <- fancy_analysis %>% 
  mutate(col_letter_1 = int2col(which(colnames(.) == "price")),
         col_number_1 = row_number() + 1, 
         cell_ref_1 = paste0(col_letter_1, col_number_1),
         col_letter_2 = int2col(which(colnames(.) == "carat")),
         col_number_2 = row_number() + 1, 
         cell_ref_2 = paste0(col_letter_2, col_number_2),
         # formular_1 = glue::glue('=[@price]/[@carat]')  # I can't seem to get this table column formular method to work
         formular_1 = glue::glue('={cell_ref_1}/{cell_ref_2}') ) %>% 
  select(-contains("col_letter_"), -contains("col_number_"), -contains("cell_ref_"))

wb <- createWorkbook()
addWorksheet(wb, "analysis results")

writeDataTable(wb, 
               "analysis results", 
               export_df %>% select(-formular_1), 
               tableStyle = "TableStyleMedium2", 
               withFilter = T)

writeFormula(wb, 
             sheet = "analysis results", 
             x = export_df %>% pull(formular_1), 
             startCol = 11, 
             startRow = 2)

saveWorkbook(wb, "3_with_a_formular_column.xlsx", overwrite = TRUE)

3. Add a Pivot Table

To add a PivotTable, you need the end-result Excel file on-hand. Open this up, create a pivot table from the data table, set it up as you would like it, then delete the sheet with the original table in it.

In the PivotTable options tick the "Refresh data when opening file" option.

Save the file (in this case I also rename it, to say that it has a pivot table it). Then this Excel file can be imported into R and the source of the PivotTable (ie the data table) is regenerated by your process.

wb <- openxlsx::loadWorkbook("3_with_a_formular_column (with pivottable added).xlsx")

addWorksheet(wb, "analysis results")

writeDataTable(wb, 
               "analysis results", 
               export_df %>% select(-formular_1), 
               tableStyle = "TableStyleMedium2", 
               withFilter = T)

writeFormula(wb, 
             sheet = "analysis results", 
             x = export_df %>% pull(formular_1), 
             startCol = 11, 
             startRow = 2)

saveWorkbook(wb, "4_with_pivot_table.xlsx", overwrite = TRUE)

Nice and easy :-)


Hope you've found a few cool tips and tricks in this post :-)