Dynamically plot data Table Output by changing excel worksheets from a workbook using Slider in R shiny

Question

I am currently trying to import a excel workbook into R studio as a list and trying to plot DataTableOutput in R shiny and I want to change the list of worksheets by using Slider based on that the DataTableOutput should change .But it is quite unsuccessful. please help me regarding this. Data

Answer 1

First, let's set up a reproducible example. We will make a list of 3 dataframes and write them to an excel workbook with each dataframe going into its own sheet. I used this SO answer to create/write the data and modified the example from this blog on how to read in an excel workbook with multiple sheets:

#install.packages("writexl")
library(writexl)
library(readxl)
library(tidyverse)

data <- list(cars = mtcars, iris = iris, teeth = ToothGrowth)

#writes data to current working directory
new_workbook <- write_xlsx(data, "new-xlsx.xlsx")

#just reading it back in to see how it can be done
#from https://dominicroye.github.io/en/2019/import-excel-sheets-with-r/
read_workbook <- new_workbook %>%
  excel_sheets() %>%
  set_names() %>%
  map(read_excel,
      path = new_workbook)

Now that we have a common dataset we can build a shiny app that lets us upload the data and choose a sheet to view. You mention wanting to use a slider to choose a sheet so we can use sliderTextInput from the shinyWidgets package to put text in a slider. Furthermore, creating the sliderTextInput in the server with renderUI (+ uiOutput in ui) allows us to reactively create an input with the names of the excel sheets for each unique .xlsx uploaded:

#install.packages("shinyWidgets")
library(shiny)
library(shinyWidgets)
library(readxl)
library(tidyverse)

ui <- fluidPage(
  fileInput("file", "Upload Workbook", accept = ".xlsx"),
  uiOutput("slider"),
  tableOutput("table"),
  verbatimTextOutput("text")
)

server <- function(input, output, session) {
  #getting sheet names to give to sliderTextInput
  sheet_names <- reactive({
    req(input$file)
    excel_sheets(input$file$datapath)
  })

  #use renderUI + sliderTextInput to reactively create the input with sheet names
   output$slider <- renderUI({
     sliderTextInput("slider",
                     "Choose sheet",
                     choices = sheet_names(),
                     grid = T)
   })

   #read data in as we did above substituting the shiny input$file$datapath for the local datapath
   data <- reactive({
     req(input$file)
     input$file$datapath %>%
             excel_sheets() %>%
             set_names() %>%
             map(read_excel, 
             path = input$file$datapath)
   })

   output$table <- renderTable({
     req(input$slider)
     data()[[input$slider]]

   })

}

shinyApp(ui, server)