upload excel to app and then read the excel into a .Rmd

[DML]

I am trying to create a shiny app in which I can upload an excel file with a fixed structure (all excel files to be uploaded have the same structure). Then the idea is to open this excel file in a .Rmd where it will be manipulated. The code below is a simplified version of the .R and the .Rmd. It works on my computer but the deployment fails. I have been surprisingly unsuccessful after reproducing the solutions for similar questions but dealing with the upload of a .csv instead of an excel. I tried several options, most of them work on my computer, none of them were deployed. In some cases, the problem is "datain should be reactive" (or something similar) and "path does not exist". I would really appreciate it if you can try to deploy the app before posting a solution. Beforehand thank you very much for your feedback.

app

library(shiny)



ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      fileInput("datain", "Upload WHIP Export", buttonLabel = "Choose file..."),
      downloadButton("report", "Generate report")
    ),
    mainPanel("Have fun")
    )
  )




server <- function(input, output) {
  output$report <- downloadHandler(
    filename = "report.html",
    content = function(file) {
      
      report<-"test.Rmd"
      src <- normalizePath(report)
      owd <- setwd(tempdir())
      on.exit(setwd(owd))
      file.copy(src, report, overwrite = TRUE)
      
      params <- list(datain = input$datain$datapath)
      rmarkdown::render(report,
                        # output_file = output,
                        params = params,
                        envir = new.env(parent = globalenv())
      )
    }
  )
}



shinyApp(ui, server)

test.Rmd

---
title: "Test"
output: html_document
params:
  datain: NA
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)

library(readxl)

number_of_columns=619 # counted the number of columns in the original excel

spec<-read_excel(path = params$datain, sheet = 1, col_types = rep("text", number_of_columns))

spec$Country

[CALUM_POLWART]

Comment out the read excel line for now and add

get.wd() to the chunk. I think you will find wd is a Random tmp file (you could have 10 users upload xls files at once and they probably shouldn't be able to read each others).

Then where is the xls file saved? You need to find a way to make the two find eachother...

[DML]

Solution:

Load the dataset within the shiny app as reactive. Then pass the dataset as a parameter of the RMD. Run the app selecting "Run app externally" in studio

server <- function(input, output) {
  
  number_of_columns=627 # counted the number of columns in the original excel
  
  dataset<-reactive({
    # inFile <- input$datain
    dat<-read_excel(path =input$datain$datapath, sheet = 1, col_types = rep("text", number_of_columns))
    return(dat)
  })
  
  dataset2<-reactive({
    # inFile <- input$datain
    dat<-read_excel(path =input$datain$datapath, sheet = 2, col_types = rep("text", number_of_columns))
    return(dat)
  })
  
  output$report <- downloadHandler(
    filename = "report.html",
    content = function(file) {
      
      report<-"test.Rmd"
      src <- normalizePath(report)
      owd <- setwd(tempdir())
      on.exit(setwd(owd))
      file.copy(src, report, overwrite = TRUE)
      
      spec <- dataset()
      obs <- dataset2()
      
      params <- list(spec = spec,
                     obs = obs)
      
      out<-rmarkdown::render(report,
                             output_file = file,
                             params = params,
                             envir = new.env(parent = globalenv()))
      
      file.rename(out, file)
      
    }
  )
}

in the .RMD

title: "It Works"
output: html_document
params:
  spec: spec
  obs: obs