Using R Shiny to read user specified excel sheet not working

Question

I have an excel file that contains multiple sheets. I am using the readxl package with Shiny so that the user can specify which sheet they want to open and analyze the corresponding data in that sheet. Here is the sample code. The issue I have is when the user selects any other sheet other than the first sheet, it does not work, the UI reverts back to the first sheet . How do I fix this?

     options(shiny.maxRequestSize=30*1024^2) # upload file upto 30 MB 
        #Load required packages
        
        library("readxl")
        library("shiny")
        
        
             DataImportUI <- tagList(
                  
                  fileInput("file", "Choose csv file",
                            accept = c(".xlsx", ".xls", ".csv", ".txt")
                  ),
                  uiOutput("ui_elements")
                )
            
        
        
    # ui.R
       
    
         ui <- 
              fluidPage(
                titlePanel(title = "Plant data visualization"),
                
                sidebarLayout(
                  
                  sidebarPanel(
                    
                    DataImportUI
                    
                  ),
                  
                  mainPanel(
                    tabsetPanel(type = "tabs",
                                tabPanel("Data Wrangle", value = 2
                                         
                                ) 
                    ) 
                  ) 
                )
              ) 
        
        
   #server.R
        
       
    
         server <- function(input, output,session) {
         
              data <- reactive({
                
                req(input$file)
                
                
                df <- read_excel(input$file$datapath, sheet = input$sheet)
                
                return(df)
              })
              
         
              
              has_file <- reactive({
                !is.null(input$file) && !is.na(input$file$name)
              })
            
              
              output$ui_elements <- renderUI({
            
                
                if (has_file()) {
                  # Display additional UI elements here
                  
                  
                  tagList(
                    h4("Select X and Y datasets"),
                    
                    fluidRow(
                      
                      column(12,
                             selectInput(inputId = "sheet", label = "Sheet", choices = excel_sheets(input$file$datapath))
                             
                      )
                    )
                    ,
                    
                    
                    fluidRow(
                      
                      column(12,
                             selectizeInput(inputId = "x", label = "X data", choices = names(data()))
                             
                      )
                    ),
                    
                    fluidRow(
                      
                      column(12,
                             selectizeInput(inputId = "y", label = "Y data", choices = names(data()), multiple = T, selected = names(data())[2])
                      )
                    ),
                    
                    
                    
                    tags$hr(style="border-color: grey;")
                    
                  )
                }
              })
              
              
            }
        
        shinyApp( ui = ui, server = server)

Answer 1

I think you have a circular dependency - output$ui_elements is dependent on data() but data() is also dependent on output$ui_elements because of input$sheets.

I have tried to separate out the dependency by creating a new renderUI for sheets. Using mtcars and iris dataset as example.

library(readxl)
library(shiny)

# Example data
# writexl::write_xlsx(dplyr::lst(mtcars, iris), "temp.xlsx")

DataImportUI <- tagList(
  fileInput("file", "Choose file",
            accept = c(".xlsx", ".xls", ".csv", ".txt")
  ),
  uiOutput("sheet_elements"),
  uiOutput("ui_elements")
)

# ui.R

ui <- 
  fluidPage(
    titlePanel(title = "Plant data visualization"),
    sidebarLayout(
      sidebarPanel(DataImportUI),
      mainPanel(
        tabsetPanel(type = "tabs",
                    tabPanel("Data Wrangle", value = 2
                    ) 
        ) 
      ) 
    )
  ) 

#server.R


server <- function(input, output,session) {
  data <- reactive({
    req(input$sheet)
    df <- read_excel(input$file$datapath, sheet = input$sheet)
    return(df)
  })
  
  output$sheet_elements <- renderUI({
    req(input$file)
    tagList(
      h4("Select X and Y datasets"),
      fluidRow(
        column(12,
               selectInput(inputId = "sheet", label = "Sheet", choices = excel_sheets(input$file$datapath))
        )
      )
    )
  })
  
  output$ui_elements <- renderUI({
    req(data())
      # Display additional UI elements here
      tagList(
        fluidRow(
          column(12,
                 selectizeInput(inputId = "x", label = "X data", choices = names(data()))
          )
        ),
        fluidRow(
          column(12,
                 selectizeInput(inputId = "y", label = "Y data", choices = names(data()), multiple = T, selected = names(data())[2])
          )
        ),
        tags$hr(style="border-color: grey;")
      )
  })
}

shinyApp( ui = ui, server = server)