r语言 - 可以从 rvg 组合 openxlsx-工作簿和xl_add_vg - 导出可编辑的图形

Question

我想从excel文件中的闪亮应用程序中导出一些表格和ggplot图形。到目前为止，我使用openxlsx，添加了许多工作表和内容。现在我想将 ggplot 作为可编辑的矢量图形导出到 Excel。这对于 rvg/officer-pack 来说是非常可行的。 有没有可能结合这个？我想将带有此可编辑图形的工作表添加到由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)

Answer 1

您可以使用library(openxlsx)的内容创建一个临时 xlsx 文件，并使用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)