Read different excel dataframes depending on selectInput Shiny

Question

I have a shiny app that I would wish to pass data frames from different Excel files depending on the choice from a selectInput. I tried to use observeEvent and if...else statements as shown in the example without success. Could anyone guide me on a better way to handle this? I have to read in 12 data frames from different excel files for each choice and pass them to the similarly named data frames for subsequent data manipulations.

library(shiny)
library("xlsx")


ui <- fluidPage(
  
  # Application title
  titlePanel("Reactive Dataframes"),
  
  
  sidebarLayout(
    sidebarPanel(
      selectInput(
        inputId = "selectData",
        label = " Select Data",
        selected = NULL,
        choices = c("mtcars", 
                    "iris")),
      
    ),
    
    mainPanel(
      tableOutput('table')
    )
  )
)

# 
server <- function(input, output) {
  
  observeEvent(input$selectData, {
    if (input$selectData == "mtcars") {
      data1 <-  read.xlsx('data/mtcars.xlsx',
                          sheet = 1, startRow = 8)
      }else{
        data1 <-  read.xlsx('data/iris.xlsx',
                            sheet = 1, startRow = 8)
      }
  })
  
  output$table <- renderTable({ data1 })
}

# Run the application 
shinyApp(ui = ui, server = server)

Answer 1

observeEvent returns an observable. You need a reactive. So this will work:

library(shiny)
library(xlsx)

ui <- fluidPage(
  titlePanel("Reactive Dataframes"),
  sidebarLayout(
    sidebarPanel(
      selectInput(
        inputId = "selectData",
        label = " Select Data",
        selected = NULL,
        choices = c("mtcars", "iris"))
    ),
    mainPanel(
      tableOutput('table')
    )
  )
)

server <- function(input, output) {
  myData <- reactive ({
    req(input$selectData)
    
    # read.xlsx(
    #   file.path("data", paste0(input$selectData, ".xlsx")), 
    #   sheetIndex = 1
    # )
    # Remove this code and uncomment above in your implementation
    if (input$selectData == "mtcars") mtcars
    else iris
  })

  output$table <- renderTable({ 
    req(myData())
    
    myData() 
  })

You say "and pass them to the similarly named data frames for subsequent data manipulations". I'm not clear what you mean here and why you would want to do that. I think you'll need to provide further information - and post a new question - to get an answer to that.

Answer 2

data1 is only created in the observer and it is is not in the scope you need. Use a reactive conductor instead:

  excelData <- eventReactive(input$selectData, {
    if (input$selectData == "mtcars") {
      data1 <- read.xlsx('data/mtcars.xlsx',
                          sheet = 1, startRow = 8)
    } else {
      data1 <- read.xlsx('data/iris.xlsx',
                            sheet = 1, startRow = 8)
    }
    data1
  })
  
  output$table <- renderTable({ excelData() }) # note the parentheses!