Possibility to combine openxlsx-Workbook and xl_add_vg from rvg - Export editable graphics

Question

I want to export some tables and ggplot-graphics from a shiny app in an excel-file. Until now I worked with openxlsx, adding many sheets and content. Now I want to export a ggplot as editable vector graphic to Excel. This is well feasible with rvg/officer-packages. Is there a possibility to combine this? I want to add the sheet with this editable graphic in an excel-file created by openxlsx with more content. Till now I can realize it only independently respectively in two single files.

    library(shiny)
    library(ggplot2)
    library(openxlsx)
    library(officer)
    library(rvg)


    ui <- fluidPage(
      plotOutput("plot", height = "350px"),
      downloadButton('Export', label = 'Export as png in xlsx'),
      downloadButton('Export2', label = 'Export as editable xlsx')
    )
    server <- function(input, output) {
      # some plot
      plot <- ggplot(mpg, aes(x = class, fill = drv)) + 
        geom_bar(position = "stack")
      output$plot <- renderPlot({
          print(plot)
      })
      # Export plot as png in xlsx
      output$Export <- downloadHandler(
        filename = function() {
            paste0("someNicePlot", ".xlsx")
        },
        content = function(file) {
          wb <- createWorkbook()
          addWorksheet(wb, "someNicePlot", gridLines = F)
          ggsave("plot.png", plot = plot, width = 5, height = 5)
          insertImage(wb, "someNicePlot", "plot.png", width = 5, height = 5)
          # add some more worksheets and content
          saveWorkbook(wb, file)
        }
      ) 
      # Export plot as editable graphic in xlsx
      output$Export2 <- downloadHandler(
        filename = function() {
          paste0("someNicePlot_editable", ".xlsx")
        },
        content = function(file) {
          wb <- read_xlsx()
          # unfortunately the first sheet is named in french (from template)
          wb <- xl_add_vg(wb, sheet = "Feuil1",
                          code = print(plot), width = 5, height = 5, left = 1, top = 2 )
          print(wb, target = file)
        }
      ) 
    }

    shinyApp(ui, server)

Answer 1

You could create a temporary xlsx-file with contents from library(openxlsx) and read it back in using library(officer) edit it (add another sheet) and finally provide it to the downloadHandler:

library(shiny)
library(ggplot2)
library(openxlsx)
library(officer)
library(rvg)

ui <- fluidPage(
  plotOutput("plot", height = "350px"),
  downloadButton('Export', label = 'Export as png in xlsx')
)

server <- function(input, output) {
  # some plot
  plot <- ggplot(mpg, aes(x = class, fill = drv)) + 
    geom_bar(position = "stack")
  output$plot <- renderPlot({
    print(plot)
  })
  # Export plot as png in xlsx
  output$Export <- downloadHandler(
    filename = function() {
      paste0("someNiceCombination", ".xlsx")
    },
    content = function(file) {

      openxlsxwb <- createWorkbook()
      addWorksheet(openxlsxwb, "someNicePlot", gridLines = F)
      ggsave("plot.png", plot = plot, width = 5, height = 5)
      insertImage(openxlsxwb, "someNicePlot", "plot.png", width = 5, height = 5)
      # add some more worksheets and content
      tmpwb <- tempfile(fileext = ".xlsx")
      saveWorkbook(openxlsxwb, tmpwb)

      # Export plot as editable graphic in xlsx
      officerwb <- read_xlsx(tmpwb)
      file.remove(tmpwb)
      officerwb <- add_sheet(officerwb, label = "someNicePlot_editable")
      officerwb <- xl_add_vg(officerwb, sheet = "someNicePlot_editable",
                             code = print(plot), width = 5, height = 5, left = 1, top = 2 )
      print(officerwb, target = file)
    }
  )
}

shinyApp(ui, server)