Downloading Excel File from XLConnect with R Shiny

Question

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

Fianna

Answer 1

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).

Lives

When I use saveWorkbook(wb) it gives me an error saying that the file parameter is required – Clite