How to use two sheet of a spreadsheet in shiny code

[JojoSouza]

The shiny code below has two tabPanel, which are PAGE 1 and PAGE 2. The first basically generates a table after a spreadsheet is inserted by fileInput and chosen the start and end dates in the daterange. On PAGE 2, graphs are generated after a calendar date is chosen. Codes work fine.

The spreadsheet I'm using is this: www.encurtador.com.br/wMRX3

Note that the spreadsheet I sent you has two sheets, FIRST and SECOND. The code is basically just considering the sheet FIRST to generate table and graphs. What I want to do is: for table generation the code consider the FIRST sheet and for figure generation consider the SECOND sheet.

Executable code below:

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


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")
  })

    data <- reactive({
    if (is.null(input$file)) {
      return(NULL)
    }
      
    else {
     df3 <- read_excel(input$file$datapath)
      validate(need(all(c('date1', 'date2') %in% colnames(df3)), "Incorrect file"))  
      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(), date2 %in% days)
    })
    

  output$table <- renderDataTable({
    data_subset()
  })
  
  output$daterange <- renderUI({
    req(data())
    dateRangeInput("daterange1", "Period you want to see:",
                   min = min(data()$date2),
                   max = max(data()$date2))
  })
  
  
  output$date <- renderUI({
    req(data())
    all_dates <- seq(as.Date('2021-01-01'), as.Date('2021-01-15'), by = "day")
    disabled <- as.Date(setdiff(all_dates, as.Date(data()$date2)), origin = "1970-01-01")
    entrydate<- dateInput(input = "Date2", 
                          label = h4("Choose"),
                          min = min(data()$date2),
                          max = max(data()$date2),
                          format = "dd-mm-yyyy",
                          datesdisabled = disabled)
    entrydate$children[[2]]$attribs$placeholder <- "No selected date"
    entrydate
  })
  
  
  output$mycode <- renderUI({
    req(input$Date2)
    df1 <- data()
    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()
    req(input$Date2,input$code)
    plot(x=df1$DR02,y=df1$DR1)})
  
  
}

shinyApp(ui = ui, server = server)

[Hayward]

Nice looking interface.

Try:

      df3_first  <- read_excel(input$file$datapath, sheet = 1)
      df3_second <- read_excel(input$file$datapath, sheet = 2)

Then you can manipulate them independently. Or, get them both in df3 with a map:

df3 <- map(1:2, ~read_excel('/home/tristan/Documents/Downloads/example.xlsx', sheet = .))

Also, the function excel_sheets() reads all the names of the worksheets if you specifically need them by name or need the count.

[JojoSouza]

Thanks @Hayward for reply! I will try to adjust my code.