r - R Shiny openxlsx: uploaded xlsx file - select a sheet from drop-down menu

Question

In my Shiny app, I can upload an xlsx file and select sheet by typing the sheet's name:

library(shiny)
library(openxlsx)
runApp(
  list(
    ui = fluidPage(
      titlePanel("openxlsx - choose sheet"),
      sidebarLayout(
        sidebarPanel(
          fileInput('file1', 'Choose xlsx file',
                    accept = c(".xlsx"))),

        mainPanel(tableOutput('contents'),
          textInput("tab1", "Type in sheet name:", "Sheet1")))),
    server = function(input, output,session){
      output$contents <- renderTable({
        inFile <- input$file1
        if(is.null(inFile))
          return(NULL)
        file.rename(inFile$datapath,paste(inFile$datapath, ".xlsx", sep=""))
        read.xlsx(paste(inFile$datapath, ".xlsx", sep=""), sheet=input$tab1)
      })}))

I would prefer to be able to use a drop-down menu to select a sheet. I know I can use openxl package to get the sheets names, but I am not sure how to implement that in Shiny. Any help will be appreciated.

标签： rshinydrop-down-menuopenxlsx

Answer 1

在用户界面中：

uiOutput("dropdownUI")

在服务器中：

Workbook <- eventReactive(input$file1, {
  loadWorkbook(input$file1$datapath)
})

Sheets <- eventReactive(Workbook(), {
  names(Workbook())
})

output$dropdownUI <- renderUI({
  req(Sheets())
  selectInput("sheet", "Choose a sheet", Sheets())
})

Dat <- eventReactive(input$sheet, {
  read.xlsx(Workbook(), sheet = input$sheet)
})

output$contents <- renderTable({
  req(Dat())
  Dat()
})