Using fileInput for Workbook and Sheet upload along with renderDataTable in shiny

Question

I want to use fileInput for Workbook and Sheet upload along with renderDataTable to upload a file and perform analysis and download the output in different formats. Couldn't figured out how to accomplish this. My minimum working example is below:

library(tidyverse)
library(shiny)
library(shinydashboard)
library(DT)

ui <-
  dashboardPage(
    skin = "green",
    dashboardHeader(
      title      = "Test",
      titleWidth = 280
      ),
    dashboardSidebar(
      width = 280,
      sidebarMenu(
        menuItem(text = "File(s) Upload", tabName = "Files",     icon = icon("file-upload")),
        menuItem(text = "Output", tabName = "Out1",     icon = icon("file-upload"))
      )
    ),
    dashboardBody(
      tabItems(
        tabItem(
          tabName = "Files",
          fluidRow(
            column(
              width = 4,
              inputPanel(
                fileInput(inputId = "File1", label = "File", multiple = TRUE, accept = c(".xlsx")),
                selectInput(inputId = "Sheet1", label = "Select sheet", choices = NULL, selected = NULL)
                )
              )
              )
          ),
        tabItem(
                 tabName = "Out1",
                 fluidRow(column(width = 10, strong("Data")), align = "center"),
                 br(),
                 fluidRow(dataTableOutput("Data1"))
                 ) 
      )
    )
  )


server <- 
  function(input, output){
    
    thedata <- 
      reactive(
        iris %>% 
        filter(Species == "setosa")
        )
    
    output$Data1 <- 
      renderDataTable(
          thedata()
        , extensions = "Buttons"
        , options = list(
                     dom = "Bfrtip"
                   , buttons = c("copy", "csv", "excel", "pdf", "print")
                   )
      )
    }

runApp(
    list(ui = ui, server = server)
  , launch.browser = TRUE
  ) 

Edited

Want to select both Excel Workbook and Sheet.

Answer 1

Here is a solution where you can choose any Excel file and dynamically change which sheet to read.

Add the following to your server:

# Populate the drop down menu with the names of the different Excel Sheets, but
# only after a new file is supplied
observe({

  sheet_names <- readxl::excel_sheets(input$File1$datapath)

  shiny::updateSelectInput(
    inputId = "Sheet1",
    choices = sheet_names,
    selected = sheet_names[[1]] # Choose first sheet as default
  )
  
}) %>%
  bindEvent(input$File1)


# When the drop down meny is populated, read the selected sheet from the Excel
# file
thedata <- reactive({
  
  req(input$Sheet1)
  
  readxl::read_xlsx(input$File1$datapath, sheet = input$Sheet1)
  
})

The rest of your code can stay the same. Under is a full reprex.

Full example, based on your code

library(tidyverse)
library(shiny)
library(shinydashboard)
library(DT)

ui <-
  dashboardPage(
    skin = "green",
    dashboardHeader(
      title      = "Test",
      titleWidth = 280
    ),
    dashboardSidebar(
      width = 280,
      sidebarMenu(
        menuItem(text = "File(s) Upload", tabName = "Files",     icon = icon("file-upload")),
        menuItem(text = "Output", tabName = "Out1",     icon = icon("file-upload"))
      )
    ),
    dashboardBody(
      tabItems(
        tabItem(
          tabName = "Files",
          fluidRow(
            column(
              width = 4,
              inputPanel(
                fileInput(inputId = "File1", label = "File", multiple = TRUE, accept = c(".xlsx")),
                selectInput(inputId = "Sheet1", label = "Select sheet", choices = NULL, selected = NULL)
              )
            )
          )
        ),
        tabItem(
          tabName = "Out1",
          fluidRow(column(width = 10, strong("Data")), align = "center"),
          br(),
          fluidRow(dataTableOutput("Data1"))
        )
      )
    )
  )


server <- function(input, output){


  # Populate the drop down menu with the names of the different Excel Sheets, but
  # only after a new file is supplied
  observe({

    sheet_names <- readxl::excel_sheets(input$File1$datapath)

    shiny::updateSelectInput(
      inputId = "Sheet1",
      choices = sheet_names,
      selected = sheet_names[[1]]
    )

  }) %>%
    bindEvent(input$File1)


  # When the drop down meny is populated, read the selected sheet from the Excel
  # file
  thedata <- reactive({

    req(input$Sheet1)

    readxl::read_xlsx(input$File1$datapath, sheet = input$Sheet1)

  })


  output$Data1 <-
    renderDataTable(
      thedata()
      , extensions = "Buttons"
      , options = list(
        dom = "Bfrtip"
        , buttons = c("copy", "csv", "excel", "pdf", "print")
      )
    )
}

runApp(
  list(ui = ui, server = server)
  , launch.browser = TRUE
)

Note: I see that you have multiple = TRUE in fileInput(). If you want to supply multiple Excel files at the same time, you need to add some logic to handle which file to read the sheet names from, and which sheet names to use for which file. I would probably set multiple to FALSE.

Answer 2

Provided you have your excel file locally with this structure (sheet name is 'Sheet1'):

structure(list(x = c(1, 2, 5), y = c(2, 9, 6)), class = "data.frame", row.names = c(NA, 
-3L))

Let's say you upload it via file upload input. Then your code should be as follows:

library(tidyverse)
library(shiny)
library(shinydashboard)
library(DT)

ui <-
  dashboardPage(
    skin = "green",
    dashboardHeader(
      title      = "Test",
      titleWidth = 280
    ),
    dashboardSidebar(
      width = 280,
      sidebarMenu(
        menuItem(text = "File(s) Upload", tabName = "Files",     icon = icon("file-upload")),
        menuItem(text = "Output", tabName = "Out1",     icon = icon("file-upload"))
      )
    ),
    dashboardBody(
      tabItems(
        tabItem(
          tabName = "Files",
          fluidRow(
            column(
              width = 4,
              inputPanel(
                fileInput(inputId = "File1", label = "File", multiple = F, accept = c(".xlsx")),
                selectInput(inputId = "sheet_name", label = "Select sheet", choices = 'Sheet1', selected = 'Sheet1')
              )
            )
          )
        ),
        tabItem(
          tabName = "Out1",
          fluidRow(column(width = 10, strong("Data")), align = "center"),
          br(),
          fluidRow(DT::DTOutput("Data1"))
        ) 
      )
    )
  )


server <-  function(session, input, output){
    
    values <- reactiveValues(
      infile = NULL
    )
    
    thedata <- reactive({
      
      if(is.null(input$File1))
        return(NULL)
      
      values$infile <- input$File1
      df <- xlsx::read.xlsx(values$infile$datapath, encoding="UTF-8", sheetName = input$sheet_name)

      # do some calculations here, add additional column 'z'
      df <- df %>% mutate(z=x+y)
      df
    })
    
    output$Data1 <- DT::renderDT(server=FALSE,{
      # Load data
      data <- thedata()
      # Show data
      datatable(data, extensions = 'Buttons', 
                options = list(
                  dom = "Bfrtip", 
                  buttons = c("copy", "csv", "excel", "pdf", "print")
               ))
    })
    
    
}


shinyApp(ui, server)