Function with openxlsx works on R console but fails in Shiny

Question

I'm trying to write a function to wrap up some data frames for export to Excel using the openxlsx package. It fails when running from a downloadHandler function in a Shiny app, but runs fine on the console in R.

Regular R script that runs fine:

library(openxlsx)

datDf1 <- data.frame(grr = c(1:10),
                     hrm = c(11:20),
                     boo = c(21:30))
datDf2 <- data.frame(will = c(31:40),
                     this = c(41:50),
                     work = c(51:60))

addSheetFun <- function(df, datName){
  addWorksheet(wbExp, sheetName=datName)
  writeData(wbExp, sheet=datName, df)
  freezePane(wbExp, sheet=datName, firstRow=TRUE)
  setColWidths(wbExp, sheet=datName, widths="auto", cols=1:ncol(df))
}

wbExp <- createWorkbook()
addSheetFun(datDf1, "SheetOne")
addSheetFun(datDf2, "SheetTwo")

Shiny application fails:

ui.r

shinyUI(
  fluidPage(
    downloadButton("xlExl", "Click to Export")
  )
)

server.r

library(openxlsx)
library(shiny)

shinyServer(
  function(
    input, output, session
  ){
    addSheetFun <- function(df, datName){
      addWorksheet(wbExp, sheetName=datName)
      writeData(wbExp, sheet=datName, df)
      freezePane(wbExp, sheet=datName, firstRow=TRUE)
      setColWidths(wbExp, sheet=datName, widths="auto", cols=1:ncol(df))
    }

    output$xlExl <- downloadHandler(
      filename="Test.xlsx",
      content=function(file){
        datDf1 <- data.frame(grr = c(1:10),
                             hrm = c(11:20),
                             boo = c(21:30))
        datDf2 <- data.frame(will = c(31:40),
                             this = c(41:50),
                             work = c(51:60))
        wbExp <- createWorkbook()
        addSheetFun(datDf1, "SheetOne")
        addSheetFun(datDf2, "SheetTwo")
        saveWorkbook(wbExp, file, overwrite=TRUE)
      }
    )
  }
)

The error I get when running from Shiny is: "Warning: Error in %in%: object 'wbExp' not found [No stack trace available]"

I played around with tacking this this to the top of addSheetFun:

if (exists("wbExp")) {
  wbExp <- wbExp
}
else {
  wbExp <- createWorkbook()
}

and then calling it like so:

wbExp <- addSheetFun(datDf1, "SheetOne")
wbExp <- addSheetFun(datDf2, "SheetTwo")

but that only manages to overwrite the first sheet with the second.

Thoughts?

score 1 · Accepted Answer · answered Oct 24 '19 at 06:12

The Error explains why it fails, it can't find your wbExp.

Probably the easiest way to overcome the error is by using <<- when you createWorkbook.

So wbExp <<- createWorkbook(). Then your shiny app should work.

This is like 'superassignment' and will assign the object in the parent environment (suggest reading http://adv-r.had.co.nz/Environments.html)

Alternatively, you can include addSheetFun inside the downloadHandler just before wbExp <- createWorkbook().

So server.R

library(openxlsx)
library(shiny)

shinyServer(
  function(
    input, output, session
  ){


    output$xlExl <- downloadHandler(
      filename="Test.xlsx",
      content=function(file){
        datDf1 <- data.frame(grr = c(1:10),
                             hrm = c(11:20),
                             boo = c(21:30))
        datDf2 <- data.frame(will = c(31:40),
                             this = c(41:50),
                             work = c(51:60))

        addSheetFun <- function(df, datName){
          addWorksheet(wbExp, sheetName=datName)
          writeData(wbExp, sheet=datName, df)
          freezePane(wbExp, sheet=datName, firstRow=TRUE)
          setColWidths(wbExp, sheet=datName, widths="auto", cols=1:ncol(df))
        }

        wbExp <- createWorkbook()
        addSheetFun(datDf1, "SheetOne")
        addSheetFun(datDf2, "SheetTwo")
        saveWorkbook(wbExp, file, overwrite=TRUE)
      }
    )
  }
)

I need to call the function over several different downloadHandlers, so I'll go with the `wbExp <<- createWorkbook()` method. I generally don't like to stick things in the parent environment on Shiny apps, but this does seem to be the way to make it work. Thanks! — bcarothers, Oct 24 '19 at 13:54
It also looks like `rm(wbExp, envir=.GlobalEnv)` after `saveWorkbook` works so that the console won't get gunked up when testing (and won't hang around when deployed on the server?) — bcarothers, Oct 24 '19 at 14:23

Function with openxlsx works on R console but fails in Shiny

1 Answers1