Shiny + downloadHandler + Openxlsx does not generate a xlsx file

Question

I'm trying to generate a .xlsx file through the Openxlsx package with a reactive name and header inside file (the input variables are "ASL.1" and "Year.1"). The object to be saved in the file is the reactive table "tab_1 ()", that is generated by the app without any problems, but when I try to download it the name that is generated by the browser (Chrome) is not (i.e.) "Tab_1_TOSCANA_2015".xlsx" but "download_tab_1", the outputId of the button "download" associated, and nothing is generated. I do not understand where the problem is, since I checked other similar examples with Openxlsx and I do not see errors in my script; if I try to write a .csv file using the "write.csv" command everything works.

The script is here: https://drive.google.com/drive/folders/1dSI9qWgQyShjXjkJ2B6COuWzuWZie5IP?usp=sharing

The App (this is just a small part) is

https://cerimp-open-data.shinyapps.io/Malprof/

require(shiny)
require(dplyr)
require(reshape2)
require(stringr)
require(shinythemes)
require(ggplot2)
require(openxlsx)
require(leaflet)
require(RColorBrewer)
require(rgdal)
require(rgeos)
require(maptools)

load("dati.RData")

#### UI ####
ui <- fluidPage(
  theme = shinytheme("spacelab"),
  titlePanel("Indice"),
  navlistPanel(
    #### Tab I ####
    tabPanel(title = "Tab. I Tassi per ASL di competenza e Sesso",
             h1(textOutput(outputId = "tab_1_text"), style = "font-size:100%"), 
             fluidRow(column(3, selectInput(inputId = "ASL.1",
                                            label = "Territorio",
                                            choices = list("TOSCANA", "ASL CENTRO","ASL NORD-OVEST","ASL SUD-EST"),
                                            selected = "Toscana",
                                            multiple = FALSE)),
                      column(3, selectInput(inputId = "Anno.1",
                                            label = "Anno di manifestazione",
                                            choices = as.list(unique(malprof$Anno)),
                                            selected = max(malprof$Anno),
                                            multiple = FALSE))),
             fluidRow(column(2, downloadButton(outputId = "download_tab_1",
                                               label = "Scarica i dati"))),
             div(tableOutput(outputId = "tab_1"), style = "font-size:80%")
    ),  
    #### Fig 1 ####
    tabPanel(title = "Fig. 1 Andamento delle denunce INAIL e delle segnalazioni Malprof",
             h1(textOutput(outputId = "fig_1_text"),  style = "font-size:100%"),
             fluidRow(column(3, selectInput(inputId = "ASL.fig.1",
                                            label = "Territorio",
                                            choices = list("TOSCANA", "ASL CENTRO","ASL NORD-OVEST","ASL SUD-EST"),
                                            selected = "Toscana",
                                            multiple = FALSE))),
             div(plotOutput(outputId = "fig.1"), style = "font-size:80%")
    )
)


#### SERVER ####
server <- function(input, output) {

  fargs <- list(big.mark=".", decimal.mark=",") #parametri per la formattazione dei numeri nelle tabelle

  annoUltimo <- max(malprof$Anno)

  rg <- filter(malprof, ASL == "TOSCANA")
  no <- filter(malprof, ASL == "ASL NORD-OVEST")
  se <- filter(malprof, ASL == "ASL SUD-EST")
  ce <- filter(malprof, ASL == "ASL CENTRO")

  #### Tabella I - Distribuzione di frequenza delle segnalazioni di MP e dei relativi tassi per 100.000 abitanti suddivisi per ASL di competenza e Sesso  #### 
  selezioneASL.1 <- reactive({switch(input$ASL.1, 
                                     "TOSCANA" = rg,
                                     "ASL CENTRO" = ce,
                                     "ASL NORD-OVEST" = no,
                                     "ASL SUD-EST" = se)})

  tab.1 <- reactive({
    pop <- popTosc %>% filter(Anno == input$Anno.1) %>%
      dcast(EXASL ~ SEX, drop = T, fill = 0, fun.aggregate = sum, value.var = "N") %>%
      filter(!is.na(EXASL))   
    mp <- selezioneASL.1() %>% filter(Anno == input$Anno.1) %>%
      dcast(EXASL ~ sesso_lav, drop = T, fill = 0, fun.aggregate = length, value.var = "Anno")
    tab <- pop %>% inner_join(mp, by = c("EXASL" = "EXASL")) %>%
      mutate(T_F = round((F.y/F.x)*100000, 1), 
             T_M = round((M.y/M.x)*100000, 1)) %>%
       select(EXASL, F.x, M.x, F.y, M.y, T_F, T_M)
    tab.tot <- c("TOTALE", sum(tab$F.x), sum(tab$M.x), sum(tab$F.y), sum(tab$M.y), round((sum(tab$F.y)/sum(tab$F.x))*100000, 1), round((sum(tab$M.y)/sum(tab$M.x))*100000, 1))  
    tab <- rbind(tab, tab.tot)
    tab$F.x <- as.numeric(tab$F.x)
    tab$M.x <- as.numeric(tab$M.x)
    tab$F.y <- as.numeric(tab$F.y)
    tab$M.y <- as.numeric(tab$M.y)
    tab$T_F <- as.character(tab$T_F)
    tab$T_M <- as.character(tab$T_M)
    tab <- rename(tab, "EXASL" = EXASL, "Pop. F" = F.x, "Pop. M" = M.x, "Segn. F" = F.y, "Segn. M" = M.y, "Tasso - F" = T_F, "Tasso - M" = T_M)
    tab
  })

  output$tab_1_text <- renderText(paste0("Distribuzione di frequenza delle segnalazioni di MP e dei relativi tassi per 100.000 abitanti suddivisi per ASL di competenza e Sesso - ", input$ASL.1, ", ", input$Anno.1, "."))

  output$tab_1 <- renderTable({tab.1()}, 
                              display=c("s","s","d","d","d","d","s","s"), 
                              spacing="s",
                              align = 'lcccccc',
                              na="--", format.args=fargs)

    output$download_tab_1 <- downloadHandler(
    filename = function() {
      paste("Tab_1_", input$ASL.1, "_", input$Anno.1, ".xlsx", sep = "")
    },
    content = function(file) {
      wb <- createWorkbook()
      addWorksheet(wb, sheetName = "Dati", gridLines = TRUE)
      intestazione <- paste0("Distribuzione di frequenza delle segnalazioni di MP e dei relativi tassi per 100.000 abitanti suddivisi per ASL di competenza e Sesso - ", input$ASL.1, ", ", input$Anno.1, ".")
      writeData(wb, 1, x = intestazione)
      writeDataTable(wb, sheet = 1, startRow = 3, x = tab.1(), colNames = TRUE)
      saveWorkbook(wb, file)
    }
  )  
}

# Run the application 
shinyApp(ui = ui, server = server)

Sigurd

Answer 1

I have been working through what sounds like the same problem. It was caused by a problem the openxlsx package being accessed by the downloadHandler (Shiny). No amount of fixing permissions or ensuring the package was in the correct folder worked. As far as we could figure out its a problem with the Shiny download handler interacting with openxlsx.

In the end I fixed this by saving a local version temp of the XLSX and then referencing this in the downloadHandler.

Move this section (inside the downloadHandler) to outside of the download handler:

 addWorksheet(wb, sheetName = "Dati", gridLines = TRUE)
  intestazione <- paste0("Distribuzione di frequenza delle segnalazioni di MP e dei relativi tassi per 100.000 abitanti suddivisi per ASL di competenza e Sesso - ", input$ASL.1, ", ", input$Anno.1, ".")
  writeData(wb, 1, x = intestazione)
  writeDataTable(wb, sheet = 1, startRow = 3, x = tab.1(), colNames = TRUE)
  saveWorkbook(wb, file)

Then inside the handler use a version of this:

    output$downloadData <- downloadHandler(
    filename = function(){paste0(intestazione,".xlsx")},
    content = function(file) {
file.copy(filename,file)

  #file.rename(fname,file)
}

)

Particularly

Where do you put the generation of the xlsx file then ? In another shiny function ? How did you trigger it from inside the download handler ? – Dual

Answer 2

I think I get it working by simply referencing file in the saveWorkbook function inside the download handler...

This is working with openxlsx package and Shiny downloadHandler :

 output$quick_export <- downloadHandler(
    filename = "my export.xlsx",
    content = function(file) {
      wb <- createWorkbook()
      addWorksheet(wb, "Cars")
      x <- mtcars[1:6, ]
      writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
      saveWorkbook(wb, file, overwrite = TRUE)
    }
  )

Niggling