R Shiny - Excel file does not download in correct format

Question

I am developing a Shiny app that takes an Excel document, changes its structure and format, and returns a new Excel doc with 10 unique sheets (based on regions of US). The code I wrote is fine in regards to changing the data but it does not return an Excel file. Instead it simply returns a blank file. If someone knows how to answer this, that would be awesome. Here is my code, abridged to only include some of the data manipulation (so that is easier to comprehend).

Let me know if this makes sense!

Excel doc link on GDrive: https://drive.google.com/file/d/1OHaYh0OcMDTbU5BhrL81EO7d5pgwfJju/view?usp=sharing

UI:

library(shiny)
library(xlsx)
library(dplyr)library(shinythemes)
shinyUI(fluidPage(theme = shinytheme("spacelab"),
titlePanel("Dataset Manipulation Example"),
tags$caption("Edited by S. Gouyet 2.21.2018"),

sidebarLayout(
      sidebarPanel(
            fileInput('file1', 'Insert File',
                            accept = c(".xlsx")
                  ),

            downloadButton("downloadData", "Download updated Excel document")

                )
                ,
                mainPanel(
                )

              )

)

)

Server:

shinyServer(function(input, output) {

reacdata<-reactive({

inFile1 <- input$file1

if(is.null(inFile1))

  return(NULL)


df <- read_excel(inFile1$datapath)
df <- df %>% select(`SALES REGION`, `Price`)



Northeast1 <- df %>% filter(`SALES REGION` == "Northeast 1")

Northeast2 <- df %>% filter(`SALES REGION` == "Northeast 2")

CapMetro <-   df %>% filter(`SALES REGION` == "Cap-Metro")

Southern <-   df %>% filter(`SALES REGION` == "Southern")

Eastern <-    df %>% filter(`SALES REGION` == "Eastern")

GreatLakes <- df %>% filter(`SALES REGION` == "Great Lakes")

Western <-    df %>% filter(`SALES REGION` == "Western")

Pacific <-    df %>% filter(`SALES REGION` == "Pacific")

wb <- createWorkbook()

sheet  <- createSheet(wb, sheetName="addDataFrame1")
addDataFrame(df, sheet)

sheet  <- createSheet(wb, sheetName="Northeast1")
addDataFrame(Northeast1, sheet)

sheet <- createSheet(wb, sheetName = "Northeast 2")
addDataFrame(Northeast2, sheet)

sheet <- createSheet(wb, sheetName = "Cap-Metro")
addDataFrame(Northeast2, sheet)

sheet <- createSheet(wb, sheetName = "Southern")
addDataFrame(Southern, sheet)

sheet <- createSheet(wb, sheetName = "Eastern")
addDataFrame(Southern, sheet)

sheet <- createSheet(wb, sheetName = "Great Lakes")
addDataFrame(GreatLakes, sheet)

sheet <- createSheet(wb, sheetName = "Western")
addDataFrame(Western, sheet)

sheet <- createSheet(wb, sheetName = "Pacific")
addDataFrame(Pacific, sheet)

})

output$downloadData <- downloadHandler( 
filename ="test.xlsx",
content = function(file) {
  saveWorkbook(wb, file)
}

  )
})

Will you add an example dataset? See [this answer](https://stackoverflow.com/a/5963610/8099834) for some options — Hallie Swan, Mar 07 '18 at 00:25

score 1 · Answer 1 · answered Mar 07 '18 at 02:30

Troubleshooting

Issue 1

When I run your app and try to download an Excel sheet, I get this error:

ERROR: object 'wb' not found

That means R could not find the wb, so saveWorkbook doesn't have an object to write into an Excel sheet.

You create wb within a reactive expression that stores the data in reacdata. That means we need call reacdata() in saveWorkbook. Note the parentheses.

output$downloadData <- downloadHandler( 
    filename ="test.xlsx",
    content = function(file) {
      saveWorkbook(reacdata(), file)
    }
  )

Issue 2

We re-run the app and try to download an Excel sheet. However, now we get this error:

ERROR: attempt to apply non-function

We can then look at the stack trace in the console:

Warning: Error in saveWorkbook: attempt to apply non-function
Stack trace (innermost first):
    54: saveWorkbook
    53: download$func [#67]
     4: <Anonymous>
     3: do.call
     2: print.shiny.appobj
     1: <Promise>
Error : attempt to apply non-function

We see that the error occured in saveWorkbook.

The last object we return in the reactive expression will be stored in reacdata(). The last line in reacdata is addDataFrame(Pacific, sheet). However, we want reacdata() to contain the workbook object. So, we just add one more line to reacdata, like this:

reacdata<-reactive({
  # data manipulation etc...
  wb
})

If you run your app with these changes, it will work. But we can also make a couple improvements.

Improvements

Make a list of dataframes per region with split. To keep regions not included in the uploaded dataframe, we make SALES REGION into a factor.

df$`SALES REGION` <- factor(df$`SALES REGION`, levels = c("Northeast 1",
                                                          "Northeast 2",
                                                          "Cap-Metro",
                                                          "Southern",
                                                          "Eastern",
                                                          "Great Lakes",
                                                          "Western", 
                                                          "Pacific"))
df_list <- split(df, df$`SALES REGION`, drop = FALSE)

Write a function to add named sheets.

## wb: workbook
## data_frame: data.frame to add to the new sheet
## sheet_name: name of the new sheet
add_sheet <- function(wb, data_frame, sheet_name) {
  sheet <- createSheet(wb, sheetName = sheet_name)
  addDataFrame(data_frame, sheet)
}

Create the workbook object and then add the sheets

## create workbook    
wb <- createWorkbook()

## add sheets
add_sheet(wb, df, "addDataFrame1")
for(i in names(df_list)) {
    add_sheet(wb, df_list[[i]], i)
}

Full App

## write out data
example <- data.frame(structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
                                 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26), `SALES REGION` = c("Northeast 1", 
                                                                                                             "Northeast 2", "Western", "Southern", "Pacific", "Northeast 1", 
                                                                                                             "Northeast 2", "Western", "Southern", "Pacific", "Northeast 1", 
                                                                                                             "Northeast 2", "Western", "Southern", "Pacific", "Northeast 1", 
                                                                                                             "Northeast 2", "Western", "Southern", "Pacific", "Northeast 1", 
                                                                                                             "Northeast 2", "Western", "Southern", "Pacific", "Great Lakes"
                                 ), Price = c(100, 106, 3201, 4236.66666666667, 5787.16666666667, 
                                              7337.66666666667, 8888.16666666667, 10438.6666666667, 11989.1666666667, 
                                              13539.6666666667, 15090.1666666667, 16640.6666666667, 18191.1666666667, 
                                              19741.6666666667, 21292.1666666667, 22842.6666666667, 24393.1666666667, 
                                              25943.6666666667, 27494.1666666667, 29044.6666666667, 30595.1666666667, 
                                              32145.6666666667, 33696.1666666667, 35246.6666666667, 36797.1666666667, 
                                              1031), Jobs = c(10, 900, 30, 321, 331, 337.3, 343.6, 349.9, 356.2, 
                                                              362.5, 368.8, 375.1, 381.4, 387.7, 394, 400.3, 406.6, 412.9, 
                                                              419.2, 425.499999999999, 431.799999999999, 438.099999999999, 
                                                              444.399999999999, 450.699999999999, 456.999999999999, 9312)), .Names = c("ID", 
                                                                                                                                       "SALES REGION", "Price", "Jobs"), row.names = c(NA, -26L), class = c("tbl_df", 
                                                                                                                                                                                                            "tbl", "data.frame")))
write.xlsx(example, "Example.xlsx")

## ui
library(shiny)
library(xlsx)
library(dplyr)
library(shinythemes)
ui <- fluidPage(theme = shinytheme("spacelab"),
                  titlePanel("Dataset Manipulation Example"),
                  tags$caption("Edited by S. Gouyet 2.21.2018"),

                  sidebarLayout(
                    sidebarPanel(
                      fileInput('file1', 'Insert File',
                                accept = c(".xlsx")
                      ),

                      downloadButton("downloadData", "Download updated Excel document")

                    )
                    ,
                    mainPanel(
                    )

                  )

)

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

  reacdata<-reactive({

    inFile1 <- input$file1

    if(is.null(inFile1))
      return(NULL)

    df <- read_excel(inFile1$datapath)
    df <- df %>% select(`SALES REGION`, `Price`)

    # create list with one dataframe per region
    df$`SALES REGION` <- factor(df$`SALES REGION`, levels = c("Northeast 1",
                                                              "Northeast 2",
                                                              "Cap-Metro",
                                                              "Southern",
                                                              "Eastern",
                                                              "Great Lakes",
                                                              "Western", 
                                                              "Pacific"))
    df_list <- split(df, df$`SALES REGION`, drop = FALSE)

    # write function to add named sheets
    ## wb: workbook
    ## data_frame: data.frame to add to the new sheet
    ## sheet_name: name of the new sheet
    add_sheet <- function(wb, data_frame, sheet_name) {
      sheet <- createSheet(wb, sheetName = sheet_name)
      addDataFrame(data_frame, sheet)
    }

    # make the workbook and add the first dataframe
    wb <- createWorkbook()
    add_sheet(wb, df, "addDataFrame1")

    # use a loop to add sheets
    for(i in names(df_list)) {
      add_sheet(wb, df_list[[i]], i)
    }

    # return the workbook object
    wb

  })

  output$downloadData <- downloadHandler( 
    filename ="test.xlsx",
    content = function(file) {
      saveWorkbook(reacdata(), file)
    }
  )
}

shinyApp(ui = ui, server = server)