时间戳

首页 > 解决方案 > 可以从 rvg 组合 openxlsx-Workbook 和 xl_add_vg - 导出可编辑图形

r - 可以从 rvg 组合 openxlsx-Workbook 和 xl_add_vg - 导出可编辑图形

问题描述

我想从 excel 文件中的闪亮应用程序中导出一些表格和 ggplot 图形。到目前为止，我一直在使用 openxlsx，添加了许多工作表和内容。现在我想将 ggplot 作为可编辑矢量图形导出到 Excel。这对于 rvg/officer-packages 非常可行。有没有可能把这个结合起来？我想在由 openxlsx 创建的具有更多内容的 excel 文件中添加带有此可编辑图形的工作表。到目前为止，我只能在两个单独的文件中分别独立地实现它。

    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)

标签： rshinyofficeropenxlsxrvg

解决方案

您可以使用以下内容创建一个临时 xlsx 文件，library(openxlsx)然后使用编辑它（添加另一张表）将其读回，library(officer)最后将其提供给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)

推荐阅读