Generate an MS Excel Workbook from inside RMarkdown

[This article was first published on, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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}

  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)) +  

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

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")
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!
Generate an MS Excel Workbook from inside RMarkdown was first posted on May 22, 2022 at 2:45 am.
To leave a comment for the author, please follow the link and comment on their blog: offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)