Generate an MS Excel Workbook from inside RMarkdown

Interested in publishing a one-time post on R-bloggers.com? Press here to learn how.

I make a presentation every week or so with {RMarkdown}. Invariably, one or more associates, those not fluent in R, will ask, “Can I get a copy of your ‘Excel’ ?” I don’t do much with Microsoft Excel directly, however, I’ve made creating a workbook part of my workflow using {openxlsx}. Now, I can immediately fire off a matching MS Excel Workbook after a discussion and look responsive. Here is an example:

```{r setup}
  library(tidyverse)
  library(openxlsx)

  make_one_sheet <- function(){ 
    sheet_name <- knitr::opts_current$get()$label
    addWorksheet(wb, sheet = sheet_name)
    writeData(wb, sheet = sheet_name, x = df_to_excel) 
    insertPlot(wb, sheet = sheet_name, 
      startCol = ncol(df_to_excel) + 2 ) 
    saveWorkbook(wb, file ="my_excel.xlsx", 
      overwrite = TRUE) 
  }

  wb <- createWorkbook()
```

I define a function in the setup chunk, to add to each chunk, which writes out the data and an image of the ggplot to tie everything together. The chunk label becomes the worksheet name. Also the workbook is set up in the setup chunk. Saving the workbook with every chunk simplifies adding new chunks as the deck is developed.

```{r dot_plot}
  plot_data <- iris %>% filter(Sepal.Length > 5) 

  plot_data %>% ggplot(aes(x = Sepal.Length, y = Sepal.Width, color = Species)) +  
    geom_point() 

  df_to_excel <- plot_data %>% 
    select(Species, Sepal.Length, Sepal.Width)  

  make_one_sheet()
```

Each chunk has the same four steps: do all of the processing of data to get ready for the specific ggplot, create the ggplot like one normally would, reorganize the data frame for the worksheet, and finally make the worksheet/workbook. Then you can do another ggplot:

```{r histogram}
  plot_data <- iris %>%  group_by(Species) %>%
    mutate(Group = cut(Petal.Length, breaks = 0:7)) %>% 
    group_by(Group, Species) %>% tally()

  plot_data %>% ggplot(aes(x = Group, y = n, fill = Species)) +
    geom_bar(stat = "identity", position = "stack")

  df_to_excel <- plot_data %>%
    pivot_wider(id_cols = "Group", names_from = "Species", values_from = "n")
  
  make_one_sheet()
```

Sometimes you want to pivot data for the worksheet, change some of the columns names, etc., for the workbook. Now you have your slides and a companion Excel Workbook!

Leave a Reply Cancel reply