How to get a list of sheet names from an .xlsx file uploaded to a Shiny app using fileInput?

Question

I have an .xlsx file with multiple sheets which I am uploading to my Shiny app using fileInput having id "file". My objective is to load a sheet using a string detect, that is if I have 3 sheets in random order named "apple", "orange" and "banana", I would like to load the "apple" sheet using string match from the list of sheets. So far I am not being able to get the list of sheets as I keep running into the error when I try to extract the sheet names using excel_sheets using readxl package-

Warning: Error in : `path` does not exist: ‘C:\Users\AppData\Local\Temp\Rtmp6dWPYS/0b16b05aa5a58cc1d1261369/0.xlsx’

The relevant server code is as follows -

    sheet_names <- reactive({
        if (!is.null(input$file)) {
            return(excel_sheets(path = input$file))
        } else {
            return(NULL)
        }
    })


apple_data <- reactive({
        req(input$file)
        inFile <- input$file
        if(is.null(inFile))
            return(NULL)
        file.rename(inFile$datapath,
                    paste(inFile$datapath, ".xlsx", sep=""))
        p<-read_excel(paste(inFile$datapath, ".xlsx", sep=""), 
    sheet = sheet_names() [str_detect(sheet_names(), regex("(apple)"))]
        

    })

After tweaking around with various functions, I eventually found a way to do it using openxlsx. Sharing the solution below -

wb<- reactive({
        req(input$file)
        inFile<- input$file
        wb<-loadWorkbook(paste(inFile$datapath, ".xlsx", sep=""))
    })
    sheetnames <- reactive({
        req(input$file)
        if (is.null(input$file)) 
            return(NULL)
        
        sheet_names<-wb()$sheet_names
        })

apple_data <- reactive({
        req(input$file)
        inFile <- input$file
        if(is.null(inFile))
            return(NULL)
        file.rename(inFile$datapath,
                    paste(inFile$datapath, ".xlsx", sep=""))
        p<-read_excel(paste(inFile$datapath, ".xlsx", sep=""), 
    sheet = sheet_names() [str_detect(sheet_names(), regex("(apple)"))]
  })

Answer 1

You can try this code -

library(shiny)
library(readxl)

ui <- fluidPage(
  fileInput('file', 'Input'),
  tableOutput('table')
)

server <- function(input, output) {
  apple_data <- reactive({
    req(input$file)
    inFile <- input$file
    if(is.null(inFile))     return(NULL)
    sheetnames <- excel_sheets(path = inFile$datapath)
    read_excel(inFile$datapath, sheet = grep('apple', sheetnames, value = TRUE))
  })
  output$table <- renderTable(apple_data())

}

shinyApp(ui, server)

Answer 2

The issue in the OP's code is that the function sheet_names() is using the logical vector str_detect(sheet_names(), regex("(apple)")) as input. Instead it would be

...
sheet = sheet_names()[str_detect(sheet_names(), regex("(apple)"))])
...