Download multiple dataframes in multiple sheets of the same excel file in a shiny app

Question

I wonderif there is a way to download 2 dataframes in the same excel file but in different sheet via shiny app.

library(shiny)
library(xlsx)
ui <- shinyUI(fluidPage(

  titlePanel("Testing File upload"),

  sidebarLayout(
    sidebarPanel(
      downloadButton("dl","Export in Excel")

    ),

    mainPanel(
    )
  )
))

server <- shinyServer(function(input, output) {

  output$dl <- downloadHandler(

    filename = function() {
      paste0("df_dmodel", "_Table", ".xls")
    },
    content = function(file){
      tbl<-iris
      tbl2<-mtcars
      write.xlsx(tbl,tbl2 file, 
                 sheetName = "Sheet1", row.names = FALSE)

    }


  ) 

})

shinyApp(ui = ui, server = server)

You can use `writexl::write_xlsx()` with a named list to output multiple sheets into an excel doc. Something like this can get your named list: `sheets <- ls(pattern = "tbl"); setNames(mget(sheets), paste0("sheet", seq_len(length(Sheets))))`. — Andrew, May 20 '20 at 19:19
how mtcars is included in this? could u provide a working example? — firmo23, May 20 '20 at 19:23
Does this answer your question? [Easy way to export multiple data.frame to multiple Excel worksheets](https://stackoverflow.com/questions/27713310/easy-way-to-export-multiple-data-frame-to-multiple-excel-worksheets) — Ian Campbell, May 20 '20 at 19:27
Can someone please help me solve an extension to this question - https://stackoverflow.com/questions/67768707/how-to-download-multiple-editable-datatable-outputs-in-r-shiny-in-one-xlsx-file — Nil_07, May 31 '21 at 07:27

score 5 · Accepted Answer · answered May 20 '20 at 19:43

try changing your server code to this. Also, remember to open the app in your browser and not just the rstudio viewer (assuming your are using rstudio). Hope this helps!

server <- shinyServer(function(input, output) {

    output$dl <- downloadHandler(

        filename = function() {
            paste0("df_dmodel", "_Table", ".xlsx")
        },
        content = function(file){
            tbl<-iris
            tbl2<-mtcars
            sheets <- mget(ls(pattern = "tbl")) # getting all objects in your environment with tbl in the name
            names(sheets) <- paste0("sheet", seq_len(length(sheets))) # changing the names in your list
            writexl::write_xlsx(sheets, path = file) # saving the file
        }
    ) 
})

score 3 · Answer 2 · answered May 20 '20 at 19:45

An alternative to Andrew's answer using write.xlsx from openxlsx with a list of dataframes.

library(shiny)
library(openxlsx)

ui <- shinyUI(fluidPage(

  sidebarLayout(
    sidebarPanel(
      downloadButton("dl","Export in Excel")
    ),
    mainPanel(
    )
  )
))

server <- shinyServer(function(input, output) {

  output$dl <- downloadHandler(

    filename = function() {
      "test.xlsx"
    },
    content = function(filename){

      df_list <- list(iris=iris, mtcars=mtcars)
      write.xlsx(x = df_list , file = filename, row.names = FALSE)
    }
  ) 

})

shinyApp(ui = ui, server = server)

Download multiple dataframes in multiple sheets of the same excel file in a shiny app

2 Answers2