Putting a plot from Shiny into an Excel using the "xlsx" package and then downloading it

[Joël Heller]

Hello fellow shiny users. 

I am running across a problem I can't seem to find any solution to on the various forums and websites. 

I will share a relatively simple reproducible example that illustrates my problem. 

I want to do the following: Upload some data, do some processing and analysis, then put the whole bunch of outputs into an Excel file and finally download it. 

I have no trouble creating that Excel in the shiny server part, and putting dataframes/tables into it. But I can't put images in the Excel file.

It is because the AddPicture function from the xlsx package I use requires the following things:

____________________________________________________________________________

addPicture(file, sheet, scale=1, startRow=1, startColumn=1)

file

the absolute path to the image file.

sheet

a worksheet object as returned by createSheet or by subsetting getSheets. The picture will be added on this sheet at position startRow, startColumn.

"The absolute path to the image file" is the problem. I can't have an absolute path to the file, as when I execute a similar script with R. Or can I?

____________________________________________________________________________________________

Here is some code that shows the error:

If you execute it as it stands, you will be able to download an Excel file with a dataframe in the first sheet no problem. (Because there is no absolute path needed, just a dataframe object)

The part with #  in the server part is what bring me problems. You can uncomment it to see the error (Error : no applicable method for 'grid.draw' applied to an object of class "NULL" ) . Does anyone have an idea on how to solve this? 

SERVER

library(shiny)

library(xlsx)

library(ggplot2)

server <- function(input, output) {

  

output$download.Excel <- downloadHandler(

  filename = function() { paste("Excelfile.xlsx")},

  content = function(file){

    example_plot=plot(1:10,1:10)

    Results_Workbook <- createWorkbook(type='xlsx')

    A=as.data.frame(matrix(2,2,2))

    sheet.1 <- createSheet(Results_Workbook, sheetName = "Data frame")

    addDataFrame(A, sheet=sheet.1, startRow=4, startColumn=2,row.names=FALSE)

    setColumnWidth(sheet.1,colIndex=c(1:100),colWidth=30)

    sheet.2 <- createSheet(Results_Workbook, sheetName = "Plot")

    # ggsave("plot",example_plot, device="emf")

    # addImage(file = "plot.emf", sheet = sheet.2, scale = 55,

    #          startRow = 4, startColumn = 4)

    saveWorkbook(Results_Workbook,file)

  } 

  

)

}

UI

library(shiny)

ui <- fluidPage(

  titlePanel("Simple classification script with R/Shiny"),

  sidebarLayout(

    sidebarPanel(

      downloadButton('download.Excel', 'Download')

      

    ),

    mainPanel(p("Description of the results")

    )

  )

)

Best regards,

Joël