How to build a Shiny app that opens excel and worksheet?

Question

I am trying to build an app that lets the user to upload an excel file, choose a worksheet and some data (i.e column) and make graphs.

I have prepared a dummy app to show my probelms. I have two of them...

1. The update of the input for the worksheet name is not working. Althogh the input field is updated by updateSelectInput it always rewrite to the default value (I think there is a nesting issues but I try to solve it for more then two days, without any luck...)
2. I don't know how to set the input of the worksheet to be able to receive any name later, now it always jumps back to the first WS (If I set choices = c('') or choices = 1 it gives an error that there is no such WS)

library(shiny)
library(xlsx)
library(readxl)

#++++++++++++++++++++++++
# create dummy excel

first  <-  data.frame(ID_1 = 1:5, a1 = letters[1:5], a2 = sample(1:10, 5))
second  <- data.frame(ID_1 = 1:10, b1 = letters[6:15], b2 = sample(1:30, 10),b3 = sample(1:30, 10))
third  <-  data.frame(ID_1 = 1:8, b1 = letters[6:13], b2 = sample(5:30, 8), b3 = sample(5:30, 8))
                      
write.xlsx(first, file = "dummy.xlsx", sheetName = "first", row.names = FALSE, append = FALSE)
write.xlsx(second, file = "dummy.xlsx", sheetName = "second", row.names = FALSE, append = TRUE)
write.xlsx(third, file = "dummy.xlsx", sheetName = "third", row.names = FALSE, append = TRUE)

#++++++++++++++++++++++++
not_sel="not selected"

ui <- fluidPage(

   sidebarLayout(
    sidebarPanel(
      fileInput("xls_input","choose file",accept=c(".xlsx")),  
      selectInput("ws_var", "choose WS", choices = c("second")), 
      selectInput("data_var","choose cloumn", c(not_sel)),
    actionButton("run_button","cacluate",icon=icon("play")),
    ),
    mainPanel(
      textOutput("calc")
    )
  )
)  

server <- function(input, output){
  
  xdata <- reactive({
    req(input$xls_input)
    infile<-input$xls_input
    observeEvent(input$ws_var,{
      choices_ws <- excel_sheets(path = infile$datapath)
      updateSelectInput(inputId = "ws_var", choices = choices_ws)
    })
    read_excel(infile$datapath,input$ws_var)
  })
   
  observeEvent(xdata(),{
      choices <- names(xdata())
      updateSelectInput(inputId = "data_var", choices = choices)
  })
     
  output$calc <- eventReactive(input$run_button,{
      xdata_<-xdata()
      xdata_var_<-input$data_var
      calc_data <- sum(xdata_[[xdata_var_]])
  })
  
}
shinyApp(ui = ui, server = server)

Answer 1

I added more than you asked for to try and solve this, so I hope the extra steps are helpful or insightful. I also don't have xlsx since I don't have java, so I used openxlsx instead.

I can spot a few issues with your current code. For example, your reactive has an observeEvent in it, which observes the ws_var input, and is meant to update the worksheet names based on when a worksheet is selected. It might work better if you observed the xls_input instead.

What I provided may be a bit more robust. I added show/hide features using shinyjs, seeing as each step is dependent on the previous step. Meaning the following step will only show when the previous is selected. Additionally, not all columns in the tables are numeric. For output$calc, it will try and sum those characters which for me causes a problem. So I added an if statement which will look to see if the column is numeric or not (which uses dplyr)

library(shiny)
# library(xlsx) #I don't have java, using xlsx instead
library(openxlsx)
library(readxl)
library(shinyjs) #Using to hide/show elements
library(dplyr) #Using to select numeric columns

#++++++++++++++++++++++++
# create dummy excel

first  <-  data.frame(ID_1 = 1:5, a1 = letters[1:5], a2 = sample(1:10, 5))
second  <- data.frame(ID_1 = 1:10, b1 = letters[6:15], b2 = sample(1:30, 10),b3 = sample(1:30, 10))
third  <-  data.frame(ID_1 = 1:8, b1 = letters[6:13], b2 = sample(5:30, 8), b3 = sample(5:30, 8))

wb<-createWorkbook()
addWorksheet(wb, "first")
addWorksheet(wb, "second")
addWorksheet(wb, "third")
writeData(wb, "first", first)
writeData(wb, "second", second)
writeData(wb, "third", third)
saveWorkbook(wb, "dummy.xlsx", overwrite = T)

#++++++++++++++++++++++++
not_sel="not selected"

ui <- fluidPage(
  useShinyjs(),
  sidebarLayout(
    sidebarPanel(
      fileInput("xls_input","choose file",accept=c(".xlsx")),  
      hidden( #Hide the later lines as they depend on the file used
        selectInput("ws_var", "choose WS", choices = not_sel), 
        selectInput("data_var","choose cloumn", c(not_sel)),
        actionButton("run_button","cacluate",icon=icon("play"))
      )
    ),
    mainPanel(
      hidden(
        textOutput("calc")
      )
    )
  )
)  

server <- function(input, output){
  
  observeEvent(input$xls_input,{ 
    infile<-input$xls_input #Gets uploaded file information
    choices_ws <- excel_sheets(path = infile$datapath) #Shows worksheets in said file
    updateSelectInput(inputId = "ws_var", choices = c(not_sel, choices_ws)) #Updates the worksheet select input with the choices
    shinyjs::show("ws_var") #Shows the worksheet select input
    shinyjs::hide("data_var") #File will adjust worksheet and column choices and further steps, hide when file chosen
    shinyjs::hide("run_button")
    shinyjs::hide("calc")
  })
  
  observeEvent(input$ws_var,{
    shinyjs::hide("run_button") #When selecting a worksheet, it will alter the column choices, and the calculation, so hide the next steps
    shinyjs::hide("calc")
    updateSelectInput(inputId = "data_var", choices = c(not_sel, names(xdata()))) #Based on the reactive data, shows worksheet choices
    if(input$ws_var == not_sel) { #If worksheet choice is the default "not selected", don't show next step, else show next step
      shinyjs::hide("data_var") #Hide the column choice if worksheet not selected
    } else {
      shinyjs::show("data_var") #Shows the column choice if worksheet selected
    }
  })
  
  xdata<-reactive({
    req(input$xls_input, input$ws_var != not_sel) #If file uploaded, and a worksheet is selected, proceed with function
    infile<-input$xls_input #Gets uploaded file information
    read_excel(infile$datapath, input$ws_var) #Reads the selected worksheet of the uploaded file
  })

  observeEvent(input$data_var,{
    shinyjs::hide("calc") #Calculation will change based on column chosen but won't occur until run_button pressed, so hide until pressed
    if(input$data_var == not_sel) { #If column choice is default "not selected", don't show calculate button, else show it
      shinyjs::hide("run_button")
    } else {
      shinyjs::show("run_button")
    }
  })

  observeEvent(input$run_button,{ #When calculate button is pressed, shows text output for calculation
    shinyjs::show("calc")
  })
  
  output$calc<-renderText({ #Calculation text output
    numeric_columns<-names(xdata()%>%select_if(is.numeric)) #Which columns in the selected worksheet are numeric
    if(input$data_var %in% numeric_columns) { #If the column is numeric, then sum the data, else say column is not numeric
      sum(xdata()[[input$data_var]])
    } else ("Column selected is not numeric")
  })
  
}
shinyApp(ui = ui, server = server)

I hope these extra steps are helpful, best of luck!