Can't read Excel files in R shiny

Question

I have an excel file with different sheets, and I want to read it in the server side, while in the ui side the user uploads the file.

Since I have a large code I can't paste it here that's why I will use a little example:

library(shiny)
library(readxl)

shinyApp(
  ui = fluidPage(
    fileInput("file","Upload the file")
  ),
  server = function(input, output) {

    sheets <- readxl::excel_sheets(input$file$datapath)
    x <- lapply(sheets, function(X) readxl::read_excel(input$file$datapath, sheet = X))
    names(x) <- sheets

  }
)

But unfortunately I get an error, the function can't find the file given a datapath. But what is interesting is when I use the fread() function it recognizes the file in input$file$datapath but it can't read .xlsx files.

I have already tried the solutions in this question but it didn't work, somehow the paste paste() function returns 0.xlsx and my file is like fileName.xlsx, any solution would be very helpful.

Answer 1

In the server side you need a reactive environment definition:

server = function(input, output) {
    data <- reactive({
    sheets <- excel_sheets(input$file$datapath)
    x <- lapply(sheets, function(X) readxl::read_excel(input$file$datapath, sheet = X))
    names(x) <- sheets
    return(x)
})
}

EDIT: Using your source of reference "read_excel" in a Shiny app and function you used to read multiple sheets in a list of data frame Read all worksheets in an Excel workbook into an R list with data.frames you can do the below, it reads in the lists and renders the str(lists)

library(shiny)
library(readxl)

shinyApp(
 ui = fluidPage(
  fileInput("file","Upload the file"),
  verbatimTextOutput("list_sheets")
),
 server = function(input, output) { 
 read_excel_allsheets <- function(filename) {
  sheets <- readxl::excel_sheets(filename)
  x <-    lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  names(x) <- sheets
  x
}
 output$list_sheets <- renderPrint({      
  inFile <- input$file
  if(is.null(inFile)){
    return(NULL)
  }
  file.rename(inFile$datapath,paste(inFile$datapath, ".xlsx", sep=""))
  list_dfs <- read_excel_allsheets(paste(inFile$datapath, ".xlsx", sep=""))
  str(list_dfs)
})
}
)

The problem you encountered ("Missing file extension") is due to the incorrect translation of the file path into shiny.