R Shiny Export Records into Excel Workbook named by User

Question

I am trying to write a Shiny application just for practice The app takes an excel sheet, grabs an ID number from the excel sheet and runs those IDs against a database returning some additional details from the database

Just for this example i have imported the data and counted the number of rows in the dataset. I then pass this dataset to the function get_DWwhich will return a dataframe and i want to count the number of records in the returned dataframe. This step occurs when the user presses the button Go

When i run my shiny app, the data imports and the number of records are counted. I also have managed to get it to return records from the database and count those.

Im having trouble exporting them using the output$downloadData, nothing happens. I press the button get the dialog box to save but when i enter the file name and press save nothing gets saved to the folder

Can anyone see where in the code i might be going wrong. I have seen the question Downloading Excel File from XLConnect with R Shiny but it doesn't use the library i want and im not quite clear on the explanation given

I have updated the code below to use the iris Dataset. Its a bit messy but it replicates the lack of saving

Server Code

# Server Code
server <- shinyServer(function(input, output) {

  # Create a reactive expression which will grab the data
  # We pass that to the outputs reactive element to the outputs
  data <- reactive({
   iris
  })

 # Return the number of records
 output$Inputnum <- renderText({
   paste(nrow(data()), "records to be checked")
 })

 # Data returned by Database
 en_data <- eventReactive(input$go, {
   get_DW(data())   
 }) 

 # Return the number of records
 output$Outputnum <- renderText({
   paste(nrow(en_data()), "records matched")
})

 output$downloadData<- downloadHandler(
  filename = function() { "name.xlsx" },

content = function(file) {
  tempFile <- tempfile(fileext = ".xlsx")
  write.xlsx(en_data(), tempFile)
  file.rename(tempFile, file)
})
})

UI Code

shinyUI(
  fluidPage(
titlePanel("POC"),
sidebarLayout(
  sidebarPanel(

    fileInput(inputId = 'file1',label =  'Choose An Excel File',
              accept=c('.xlxs')),

    radioButtons(inputId = "radio", label = "Search By:",
                 choices = list("option 1" = 1, 
                                "option 2" = 2, 
                                "option 3" = 3), 
                 selected = 1),

    hr(),

    fluidRow(column(1,
                    actionButton("go", "Get Records")),
             column(2,offset = 2,
                    downloadButton('downloadData', 'Download')),
             br()
    )),

  mainPanel(
    verbatimTextOutput("Inputnum"),
    br(),
    verbatimTextOutput("Outputnum")
  )
)
  ))

GLOBAL R FILE

#in global.R
options (scipen=FALSE,stringsAsFactors=FALSE)
library(xlsx)
library(RODBC)
library(dplyr)

get_DW <- function (mydf) {
  mydf 
}

*"Im having trouble exporting them using the output$downloadData"* - what does this mean? Error messages? Incorrect data exported? — nrussell, Jul 12 '16 at 15:30
Sorry @nrussell, you are right, i have updated the question to be less vague — John Smith, Jul 12 '16 at 15:32
`Error in handlerFunc() : could not find function "get_DW"`. Please make sure provide *all* code necessary to reproduce your problem (that also include `library(xxx)` calls). — nrussell, Jul 12 '16 at 15:45
Hi @nrussell, that function unfortunately i cannot supply as it contains SQL that we are running against the company servers. I can tell you the return type is a data.frame and it works without any problems as both the `verbatimTextOutput("Inputnum"),` and the `verbatimTextOutput("Outputnum")` return results as expected. The problems stems from the `downloadHandler`function — John Smith, Jul 12 '16 at 15:48
Then create a similar function that doesn't use SQL but returns an object of the same structure. — nrussell, Jul 12 '16 at 15:57
Possible duplicate of [Downloading Excel File from XLConnect with R Shiny](http://stackoverflow.com/questions/21383748/downloading-excel-file-from-xlconnect-with-r-shiny) — nrussell, Jul 12 '16 at 16:01

score 1 · Accepted Answer · answered Jul 12 '16 at 17:06

1

I encountered this same problem a couple of weeks ago. Try running your app externally instead of locally. This worked for me using your sample code.

answered Jul 12 '16 at 17:06

josh453

308
2
12

Hi @josh453, Can you tell me where the file is supposed to open when you run it external. Is it through an internet browser? – John Smith Jul 13 '16 at 10:56
It runs in an internet browser, for some reason unknown to me R seems not to save files to disk when running the Shiny applications within R. Perhaps someone else can enlighten me, but for debugging and testing purposes this workaround solution has worked for me. – josh453 Jul 13 '16 at 13:03

R Shiny Export Records into Excel Workbook named by User

1 Answers1