在R Shiny中通过下载按钮导出响应式ggplot图表和rhandsontable至Excel工作簿的问题

我明白你遇到的两个问题了——导出的图表总是初始的默认图，而且尝试把表格数据加入Excel时会报错。这两个问题的核心原因是你在服务器启动时就创建了Excel工作簿和保存了图表，这时候用的都是初始的默认数据，而不是用户修改后的响应式数据。下面是修复后的完整代码，我会在后面解释关键的修改点：

修复后的UI代码 #

library(shiny)
library(rhandsontable)
library(tidyverse)
library(ggplot2)
library(xlsx)

ui <- fluidPage(sidebarLayout(
  sidebarPanel (
    titlePanel("Data Calculation"),
    selectInput(
      "select",
      label = h3("Tool"),
      choices = list(
        "Select Tool" = "Select Tool",
        "Tool-0200" = "Tool-0200",
        "Tool-0300" = "Tool-0300",
        "Tool-0500" = "Tool-0500",
        "Tool-0600" = "Tool-0600",
        "Tool-0700" = "Tool-0700",
        "Tool-0800" = "Tool-0800",
        "Tool-0900" = "Tool-0900",
        "Tool-1000" = "Tool-1000"
      ),
      selected = 1,
      width = "150"
    ),
    rHandsontableOutput('table'),
    downloadButton("downloadData", "Download"),
    width = 3
  ),
  mainPanel(br(),
            h2("Data Visualization"),
            plotOutput("testplot"))
))

修复后的Server代码 #

server <- function(input, output, session) ({
  df <- reactiveValues(data = data.frame(
    Wafer = c(
      "Sample-01",
      "Sample-02",
      "Sample-03",
      "Sample-04",
      "Sample-05"
    ),
    Angle = seq(-1, 1, 0.5),
    RS = c(8, 2, 0, 2, 8)
  ))
  
  output$table <- renderRHandsontable({
    rhandsontable(df$data)
  })
  
  output$testplot <- renderPlot({
    ggplot(df$data, aes(x = Angle, y = RS)) +
      geom_line() +
      geom_point(shape = 21, color = "black", fill = "black", size = 3) +
      geom_smooth(formula = y ~ poly(x, 2, raw = TRUE), method = lm, se = TRUE) +
      xlab("Tilt Angle /°") + ylab(expression(paste("RS /" , Omega, "/squ"))) +
      theme_bw() +
      theme(
        axis.title.x = element_text(size = 16),
        axis.text.x = element_text(size = 14),
        axis.title.y = element_text(size = 16),
        axis.text.y = element_text(size = 14)
      )
  }, height = 600, width = 800)
  
  # Downloadbutton
  output$downloadData <- downloadHandler(
    filename = function() {
      paste(input$select, "_Angle-Test_", Sys.Date(), ".xlsx", sep = "")
    },
    content = function(file) {
      # 生成当前最新的ggplot图表对象
      current_plot <- ggplot(df$data, aes(x = Angle, y = RS)) +
        geom_line() +
        geom_point(shape = 21, color = "black", fill = "black", size = 3) +
        geom_smooth(formula = y ~ poly(x, 2, raw = TRUE), method = lm, se = TRUE) +
        xlab("Tilt Angle /°") + ylab(expression(paste("RS /" , Omega, "/squ"))) +
        theme_bw() +
        theme(
          axis.title.x = element_text(size = 16),
          axis.text.x = element_text(size = 14),
          axis.title.y = element_text(size = 16),
          axis.text.y = element_text(size = 14)
        )
      
      # 创建临时图片文件，避免文件冲突
      temp_plot <- tempfile(fileext = ".png")
      ggsave(temp_plot, plot = current_plot, height = 6, width = 8)
      on.exit(file.remove(temp_plot)) # 下载完成后自动删除临时文件
      
      # 基于当前数据创建Excel工作簿
      wb <- createWorkbook(type = "xlsx")
      sheet <- createSheet(wb, sheetName = "Test")
      
      # 添加用户修改后的表格数据到Excel
      addDataFrame(df$data, sheet, startRow = 3, startColumn = 1)
      
      # 添加最新生成的图表到Excel
      addPicture(
        temp_plot,
        sheet,
        scale = 1,
        startRow = 10,
        startColumn = 1
      )
      
      # 保存工作簿到下载文件
      saveWorkbook(wb, file = file)
    }
  )
  
  observeEvent(input$table$changes$changes, {
    df$data <- hot_to_r(input$table)
  })
  
  # Reactive Fit
  data_fit <- reactive({
    coeff <- lm(RS ~ poly(Angle, 2, raw = TRUE),  data = df$data)
    a <- coeff[["coefficients"]][["poly(Angle, 2, raw = TRUE)2"]]
    b <- coeff[["coefficients"]][["poly(Angle, 2, raw = TRUE)1"]]
    c <- -(b / 2 / a)
    d <- coeff[["coefficients"]][["(Intercept)"]]
    return(list(a = a, b = b, c = c, d = d))
  })
})

shinyApp(ui = ui, server = server)

关键修改说明 #

• 将Excel操作移至downloadHandler内部：所有创建工作簿、生成当前图表、添加图片和数据的操作都放到downloadHandler的content函数里，这样每次点击下载按钮时，都会基于用户最新修改的表格数据来生成内容，而不是启动时的初始数据。
• 临时保存响应式图表：不再提前保存固定的图片文件，而是在下载时根据当前数据生成最新的ggplot对象，并用临时文件保存（避免文件覆盖或冲突），下载完成后自动删除临时文件。
• 正确调用addDataFrame：直接使用df$data（因为df是reactiveValues对象，不是reactive表达式），确保把用户修改后的表格数据正确写入Excel。

备注：内容来源于stack exchange，提问作者fichtenelch.phd