How to save and download two reactive dataframes in separate tabs of a common excel file?

Question

I have the shiny app below in which I have two reactive dataframes. I want when I click download to download one excel file with both dataframes in two different tabs with their names if possible.

library(shiny)
library(openxlsx)

ui <- fluidPage(
  titlePanel("Download Data Tables as Excel"),
  sidebarLayout(
    sidebarPanel(
      actionButton("downloadBtn", "Download Excel")
    ),
    mainPanel(
      dataTableOutput("table1"),
      dataTableOutput("table2")
    )
  )
)

server <- function(input, output) {
  # Sample data tables
  table1 <- reactive({
    data.frame(
      Column1 = sample(1:100, 10),
      Column2 = sample(1:100, 10)
    )
  })
  
  table2 <- reactive({
    data.frame(
      ColumnA = sample(letters, 10),
      ColumnB = sample(LETTERS, 10)
    )
  })
  
  output$table1 <- renderDataTable({
    table1()
  })
  
  output$table2 <- renderDataTable({
    table2()
  })
  
  observeEvent(input$downloadBtn, {
    # Create a workbook and add worksheets
    wb <- createWorkbook()
    addWorksheet(wb, "Table1")
    addWorksheet(wb, "Table2")
    
    # Write data to the worksheets
    writeData(wb, "Table1", table1())
    writeData(wb, "Table2", table2())
    
    # Save the workbook to a temporary file
    file <- tempfile(fileext = ".xlsx")
    saveWorkbook(wb, file, overwrite = TRUE)
    
    # Send the file to the user
    shiny::showModal(modalDialog(
      title = "Download",
      downloadButton("downloadExcel", "Download Excel"),
      easyClose = TRUE,
      footer = NULL
    ))
    
    output$downloadExcel <- downloadHandler(
      filename = function() {
        paste("data_tables", Sys.Date(), ".xlsx", sep = "")
      },
      content = function(file) {
        file.copy(file, file)
      }
    )
  })
}

shinyApp(ui = ui, server = server)

Answer 1

I made some smaller adjustments in the downloadHandler and also replaced the (as of shiny 1.8.1) deprecated shiny::renderDataTable() and shiny::dataTableOutput() with their DT equivalents.

library(shiny)
library(openxlsx)

ui <- fluidPage(
  titlePanel("Download Data Tables as Excel"),
  sidebarLayout(
    sidebarPanel(
      actionButton("downloadBtn", "Download Excel")
    ),
    mainPanel(
      DT::DTOutput("table1"),
      DT::DTOutput("table2")
    )
  )
)

server <- function(input, output) {
  # Sample data tables
  table1 <- reactive({
    data.frame(
      Column1 = sample(1:100, 10),
      Column2 = sample(1:100, 10)
    )
  })
  
  table2 <- reactive({
    data.frame(
      ColumnA = sample(letters, 10),
      ColumnB = sample(LETTERS, 10)
    )
  })
  
  output$table1 <- DT::renderDT({
    table1()
  })
  
  output$table2 <- DT::renderDT({
    table2()
  })
  
  observeEvent(input$downloadBtn, {
    # Create a workbook and add worksheets
    wb <- createWorkbook()
    addWorksheet(wb, "Table1")
    addWorksheet(wb, "Table2")
    
    # Write data to the worksheets
    writeData(wb, "Table1", table1())
    writeData(wb, "Table2", table2())
    
    # Send the file to the user
    shiny::showModal(modalDialog(
      title = "Download",
      downloadButton("downloadExcel", "Download Excel"),
      easyClose = TRUE,
      footer = NULL
    ))
    
    output$downloadExcel <- downloadHandler(
      filename = function() {
        paste("data_tables", Sys.Date(), ".xlsx", sep = "")
      },
      content = function(file) {
        saveWorkbook(wb, file = file, overwrite = TRUE)
      }
    )
  })
}

shinyApp(ui = ui, server = server)