如何在Shiny代码中调用Excel两个工作表分别生成表格与图表

原代码默认仅读取Excel的第一个工作表，只需拆分数据读取逻辑，分别读取两个工作表对应不同页面使用即可，修改点如下：

• 拆分原有data响应式变量为两个：data_first读取FIRST工作表，为PAGE1的表格提供数据；data_second读取SECOND工作表，为PAGE2的图表提供数据
• PAGE1关联的日期范围生成、表格渲染逻辑全部调整为调用data_first
• PAGE2关联的日期选择器、分类下拉框、图表渲染逻辑全部调整为调用data_second

library(shiny)
library(shinythemes)
library(readxl)
library(dplyr)

ui <- fluidPage(
  
  shiny::navbarPage(theme = shinytheme("flatly"), collapsible = TRUE,
                    br(),
                    tabPanel("PAGE1",
                             sidebarLayout(
                               sidebarPanel(
                                 uiOutput('fileInput'),
                                 uiOutput('daterange')
                                 
                               ),
                               mainPanel(
                                 dataTableOutput('table')
                               )
                             )),
                    
                    tabPanel("PAGE2",
                             sidebarLayout(
                               sidebarPanel(
                                 uiOutput('date'),
                                 uiOutput("mycode")
                                 
                               ),
                               mainPanel(
                                 tabsetPanel(
                                   tabPanel("", plotOutput("graph",width = "100%", height = "600")
                                   )
                                 ))
                             ))))

server <- function(input, output,session) {
  
  output$fileInput <- renderUI({
    fileInput("file",h4("Import file"), multiple = T, accept = ".xlsx")
  })
  
  # 读取FIRST工作表，用于PAGE1表格展示
  data_first <- reactive({
    if (is.null(input$file)) {
      return(NULL)
    }
    
    else {
      df3 <- read_excel(input$file$datapath, sheet = "FIRST")
      validate(need(all(c('date1', 'date2') %in% colnames(df3)), "FIRST工作表字段错误"))  
      df4 <- df3 %>% mutate_if(~inherits(., what = "POSIXct"), as.Date) 
      return(df4)
    }
  })
  
  # 读取SECOND工作表，用于PAGE2图表生成
  data_second <- reactive({
    if (is.null(input$file)) {
      return(NULL)
    }
    
    else {
      df3 <- read_excel(input$file$datapath, sheet = "SECOND")
      validate(need(all(c('date1', 'date2', 'Category', 'DR02', 'DR1') %in% colnames(df3)), "SECOND工作表字段错误"))  
      df4 <- df3 %>% mutate_if(~inherits(., what = "POSIXct"), as.Date) 
      return(df4)
    }
  })
  
  data_subset <- reactive({
    req(input$daterange1)
    req(input$daterange1[1] <= input$daterange1[2])
    days <- seq(input$daterange1[1], input$daterange1[2], by = 'day')
    subset(data_first(), date2 %in% days)
  })
  
  
  output$table <- renderDataTable({
    data_subset()
  })
  
  output$daterange <- renderUI({
    req(data_first())
    dateRangeInput("daterange1", "Period you want to see:",
                   min = min(data_first()$date2),
                   max = max(data_first()$date2))
  })
  
  
  output$date <- renderUI({
    req(data_second())
    all_dates <- seq(as.Date('2021-01-01'), as.Date('2021-01-15'), by = "day")
    disabled <- as.Date(setdiff(all_dates, as.Date(data_second()$date2)), origin = "1970-01-01")
    entrydate<- dateInput(input = "Date2", 
                          label = h4("Choose"),
                          min = min(data_second()$date2),
                          max = max(data_second()$date2),
                          format = "dd-mm-yyyy",
                          datesdisabled = disabled)
    entrydate$children[[2]]$attribs$placeholder <- "No selected date"
    entrydate
  })
  
  
  output$mycode <- renderUI({
    req(input$Date2)
    df1 <- data_second()
    df5 <- df1[as.Date(df1$date2) %in% input$Date2,]
    selectInput("code", label = h4("Category"),choices=c("No code selected" = "", sort(unique(df5$Category))))
  })
  
  output$graph <- renderPlot({
    df1 <- data_second()
    req(input$Date2,input$code)
    plot(x=df1$DR02,y=df1$DR1)})
  
  
}

shinyApp(ui = ui, server = server) 

内容的提问来源于stack exchange，提问作者Antonio