Asked  1 Month ago    Answers:  5   Viewed   24 times

Has anyone tried using the download handler in R Shiny to download a freshly created Excel file with XLConnect?

In the ui.R there is the unremarkable line:

downloadButton('downloadData', 'Download')

In the server.R there is the handler:

output$downloadData <- downloadHandler(

filename = function() { "output.xlsx" },

    content = function(file){
      wb <- loadWorkbook(file, create = TRUE)
      createSheet(wb, name = "Sheet1")
      writeWorksheet(wb, c(1:3), sheet = "Sheet1") # writes numbers 1:3 in file
      saveWorkbook(wb)
    }
)

I have no problem downloading a .csv and no problem creating the excel file with XLConnect. But when I run the code as above I get the following error in my Chrome browser:

IllegalArgumentException (Java): File extension "file1b683b9323bc" not supported! Only *.xls and *.xlsx are allowed!

As far as I can see, XLConnect cannot write to a temporary file.

Has anyone got a solution or workaround?

One option would be to save the file in a specific location and then creating a download link pointing to it. However, this is not very Shiny-esque as multiple users would cause havok.

Many Thanks

Marcus

 Answers

59

Try using this for the content(...) function; it works for me...

content = function(file){
      fname <- paste(file,"xlsx",sep=".")
      wb <- loadWorkbook(fname, create = TRUE)
      createSheet(wb, name = "Sheet1")
      writeWorksheet(wb, c(1:3), sheet = "Sheet1") # writes numbers 1:3 in file
      saveWorkbook(wb)
      file.rename(fname,file)
    }

The problem is that file is a randomly generated temp file, without an extension, whereas saveWorkbook(...) requires the .xlsx extension. So this just appends .xlsx to file and uses that for all the XLConnect manipulations, then renames the final file to the original name (e.g., strips off the extension).

Monday, September 6, 2021
 
Finbarr
answered 1 Month ago
50

I use

library(readxl)
read_excel('//companySharepointSite/project/.../ExcelFilename.xlsx', 'Sheet1', skip=1)

Note, no https:, and sometimes I have to open the file first (i.e., cut and paste //companySharepointSite/project/.../ExcelFilename.xlsx into my browser's address bar)

Thursday, June 24, 2021
 
jwegner
answered 4 Months ago
35

This will download them in batches and take advantage of the speedier simultaneous downloading capabilities of download.file() if the libcurl option is available on your installation of R:

library(purrr)

states <- state.abb[1:27]
agencies <- c("AID", "AMBC", "AMTRAK", "APHIS", "ATF", "BBG", "DOJ", "DOT",
              "BIA", "BLM", "BOP", "CBFO", "CBP", "CCR", "CEQ", "CFTC", "CIA",
              "CIS", "CMS", "CNS", "CO", "CPSC", "CRIM", "CRT", "CSB", "CSOSA",
              "DA", "DEA", "DHS", "DIA", "DNFSB", "DOC", "DOD", "DOE", "DOI")

walk(states, function(x) {
   map(x, ~sprintf("http://website.gov/%s_%s.zip", ., agencies)) %>% 
    flatten_chr() -> urls
    download.file(urls, basename(urls), method="libcurl")
}) 
Thursday, August 26, 2021
 
Apollo
answered 2 Months ago
15

The content function has to have an argument, it can be anything (for instance "file") and it then goes to png() function.

#Changes:
content <- function(file){ ## file = NULL --> file
  png(file) # filename --> file

Full code:

    library(gtable)
library(ggplot2)
ui <- shinyUI(fluidPage(
  titlePanel("Test app"),
  fluidRow(
    column(4,
           wellPanel(
             downloadButton('download',label="Download plot as png")
           )
    ),
    column(8,
           plotOutput("plot")
    )  
  )
))
server <- function(input,output) {

  plotting<- reactive({

    data1=data.frame(x=rnorm(50),y=rnorm(50))
    data2=data.frame(x=rexp(50),y=rexp(50))

    plot1=ggplot(data1,aes(x,y))+geom_point()
    plot2=ggplot(data2,aes(x,y))+geom_point()

    gb1=ggplot_build(plot1)
    gb2=ggplot_build(plot2)

    gA <- ggplot_gtable(gb1)
    gB <- ggplot_gtable(gb2)

    both <- gtable:::rbind_gtable(gA, gB, "last")
    return(both)
  })

  output$plot <- renderPlot({
    grid.newpage()
    grid.draw(plotting())
  })

  output$download <- downloadHandler(
    filename <- "shinytestplot.png",

  # Changes:
  content <- function(file){ ## file = NULL --> file
    png(file) # filename --> file
    grid.newpage()
    grid.draw(plotting())
    dev.off()
  }
  ) 
}
shinyApp(server=server,ui=ui)
Tuesday, August 31, 2021
 
Sergey Snegirev
answered 2 Months ago
31

Here is a working example. Again, this assumes that you run the app on your own computer, and users are allowed to access folders on this computer. You can set the root folder where user is allowed to save files (see UserFolder, user will be able to choose any subfolder of this root)

library(shiny)
library(shinyFiles)
library(xlsx)

ui <- shinyUI(fluidPage(

  titlePanel("Example"),
  shinySaveButton("save", "Save file", "Save file as ...", filetype=list(xlsx="xlsx"))

))

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

  observe({
    volumes <- c("UserFolder"="D:/Data")
    shinyFileSave(input, "save", roots=volumes, session=session)
    fileinfo <- parseSavePath(volumes, input$save)
    data <- data.frame(a=c(1,2))
    if (nrow(fileinfo) > 0) {
      write.xlsx(data, as.character(fileinfo$datapath))
    }
  })
})

shinyApp(ui = ui, server = server)
Friday, September 10, 2021
 
Alexey Ferapontov
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :