rexcelshinyreadxl

Choose which Excel sheet to upload from within Shinyapp

I am developing a shinyapp that allows users to upload their data. What often happens is the data sheet is not stored in the first position, so the intended sheet is not uploaded.

One workaround is to specify that the intended data must be stored in first position, but I was wondering if there is a way to select from the sheets. I imagine this would be a three-step process:

  1. Upload an excel file in the usual way, using {readxl} e.g.
dat_raw <- reactive({ 

  req(input$file)

  readxl::read_excel(input$file$datapath)
   
 }
  
})
  1. Store the sheet names, using readxl::excel_sheets(), and show these in a drop-down menu, perhaps using observe and updateSelectInput, e.g.
observe({
  updateSelectInput(
   session = session,
   inputId = "sheetnames",
   choices = SHEETNAMES)
  )
 })
  1. Somehow use the selected sheetname to tell the app which data to then read (might require another upload) or process (if all sheets have been stored in memory).

I've searched extensively and cannot find this problem raised anywhere else. I'm grateful for any help you can provide

Solution

  • I like to proceed as follows. Make a reactiveVal named Dat to store the uploaded sheet. Then do (the sweetalert stuff is from the shinyWidgets package):

          observeEvent(input[["file"]], {
        xlfile <- input[["file"]][["datapath"]]
        ext <- tolower(file_ext(xlfile))
        if(ext == "xlsx") {
          sheets <- excel_sheets(xlfile)
          if(length(sheets) == 1L) {
            Dat(read_xlsx(input[["file"]][["datapath"]], sheet = 1L))
          } else {
            inputSweetAlert(
              session,
              inputId = "sheet",
              title = "Select the sheet",
              type = "question",
              input = "select",
              inputOptions = sheets
            )
          }
        } else {
          sendSweetAlert(
            session,
            title = "Wrong file",
            text = "Please upload a `xlsx` file.",
            type = "error"
          )
        }
      })

      observeEvent(input[["sheet"]], {
        Dat(read_xlsx(input[["file"]][["datapath"]], sheet = input[["sheet"]]))
      })

    enter image description here

    library(shiny)
library(shinyWidgets)
library(readxl)

ui <- fluidPage(
    sidebarLayout(
        sidebarPanel(
            fileInput("file", "Choose XLSX file")
        ),
        mainPanel(
            tableOutput("table")
        )
    )
)

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

    Dat <- reactiveVal()

      observeEvent(input[["file"]], {
        xlfile <- input[["file"]][["datapath"]]
        ext <- tolower(tools::file_ext(xlfile))
        if(ext == "xlsx") {
          sheets <- excel_sheets(xlfile)
          if(length(sheets) == 1L) {
            Dat(read_xlsx(input[["file"]][["datapath"]], sheet = 1L))
          } else {
            inputSweetAlert(
              session,
              inputId = "sheet",
              title = "Select the sheet",
              type = "question",
              input = "select",
              inputOptions = sheets
            )
          }
        } else {
          sendSweetAlert(
            session,
            title = "Wrong file",
            text = "Please upload a `xlsx` file.",
            type = "error"
          )
        }
      })

      observeEvent(input[["sheet"]], {
        Dat(read_xlsx(input[["file"]][["datapath"]], sheet = input[["sheet"]]))
      })

    output[["table"]] <- renderTable({
        req(Dat())
        Dat()
    })

}

shinyApp(ui, server)