CODEWITHSUNDEEP
rshiny
Programmer Man
Programmer Man

Reputation: 1305

Can't read Excel files in R shiny

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.

Upvotes: 1

Views: 2561

Answers (1)

Patrik_P
Patrik_P

Reputation: 3200

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.

Upvotes: 4

Related Questions