闪亮+ downloadHandler + Openxlsx不会生成xlsx文件 [英] Shiny + downloadHandler + Openxlsx does not generate a xlsx file

问题描述

我正在尝试通过Openxlsx包生成一个.xlsx文件，该文件的内部带有响应名称和标头(输入变量为"ASL.1"和"Year.1").要保存在文件中的对象是反应性表"tab_1()"，由应用程序生成，没有任何问题，但是当我尝试下载它时，浏览器(Chrome)生成的名称却不是(即)"Tab_1_TOSCANA_2015" .xlsx，但与" download_tab_1，按钮" download的outputId相关联，并且没有生成任何内容.我不明白问题出在哪里，因为我用Openxlsx检查了其他类似的示例，但没有看到错误在我的脚本中；如果我尝试使用"write.csv"命令写入.csv文件，则一切正常.

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.

脚本位于此处: https://drive.google.com/驱动器/文件夹/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)

推荐答案

我一直在研究听起来像是同样问题的方法.这是由downloadHandler(Shiny)访问openxlsx软件包的问题引起的.没有大量的修复权限或确保程序包位于正确的文件夹中.据我们发现，Shiny下载处理程序与openxlsx交互存在问题.

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.

最后，我通过保存XLSX的本地版本临时文件，然后在downloadHandler中引用它来解决此问题.

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

将此部分(在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)
}

)

这篇关于闪亮+ downloadHandler + Openxlsx不会生成xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！