rbind mutiple xlsx files with read_excel function in a shiny app

[Kahina]

Hello,

I want to upload several xlsx files with multiple sheets and bind them to one dataframe in a shiny app

i used read_excel function from reaxl package, but i don't know how to set the parameters of that function in a shiny app

below, the code r that worked to explain what i want to do, and the rshiny code with which I have a problem

library(readxl)

inputFolder <- "~/s/extract2/"

fileList <- list.files(path = inputFolder, pattern='*.xlsx')

for (f in 1:length(fileList)){

  sheetList <- excel_sheets(paste0(inputFolder, fileList[f]))

  for (s in 1:length(sheetList)) {
    if (s==1){
      tempSheet <- read_excel(paste0(inputFolder, fileList[f]), sheet = sheetList[s], .name_repair = ~ gsub(" ", ".",.x, fixed = TRUE))
      # if (f==1){
      #   df <- tempSheet
      # }
    }else {
      if(s != 1) {
        tempSheet <- read_excel(paste0(inputFolder, fileList[f]), sheet = sheetList[s],col_names=F, .name_repair = ~ gsub(" ", ".",.x, fixed = TRUE)) 
        #names(tempSheet) <- names(df)
        }
    }
    if (f == 1 & s == 1) {
            df <- tempSheet
          }
          else {
            if(s != 1) {
              names(tempSheet) <- names(df)
            }
            df <- rbind(df,tempSheet)
          }
    }
  }

library(shiny)
library(readxl)


ui <- fluidPage(
    titlePanel("Cible"),
    sidebarLayout(
        sidebarPanel(
            fileInput("xlsxs",
                      label="Fichiers bruts",
                      multiple = TRUE,
                      accept=".xlsx"),
   ),


       mainPanel(

             tableOutput("table1")

    )
    ))

server <- function(input, output) {
    options(shiny.maxRequestSize=900*1024^2)

    output$nrow1 <- renderTable({
         if(is.null(input$xlsx)){return ()}
xl <- input$xlsxs

    for (f in 1:length(xl)){

      sheetList <- excel_sheets(paste0(xl, ".xlsx", xl$datapath[f]))

      for (s in 1:length(sheetList)) {
        if (s==1){
          tempSheet <- read_excel(paste0(xl,".xlsx",xl$datapath[f]), sheet = sheetList[s], .name_repair = ~ gsub(" ", ".",.x, fixed = TRUE))

        }else {
          if(s != 1) {
            tempSheet <- read_excel(paste0(xl,".xlsx", xl$datapath[f]), sheet = sheetList[s],col_names=F, .name_repair = ~ gsub(" ", ".",.x, fixed = TRUE)) 

          }
        }
        if (f == 1 & s == 1) {
          df <- tempSheet
        }
        else {
          if(s != 1) {
            names(tempSheet) <- names(df)
          }
          df <- rbind(df,tempSheet)
        }

      }
    }

    return(df)

})
}

shinyApp(ui = ui, server = server)

Error in : path must be a string

[pieterjanvc]

Hi,

There were just a few small mistakes

library(shiny)
library(readxl)

ui <- fluidPage(
  titlePanel("Cible"),
  sidebarLayout(
    sidebarPanel(
      fileInput("xlsxs",
                label="Fichiers bruts",
                multiple = TRUE,
                accept=".xlsx")
    ),
    
    
    mainPanel(
      
      tableOutput("table1")
      
    )
  ))

server <- function(input, output) {
  
  observeEvent(input$xlsxs, {
    print(input$xlsxs)
  })
  options(shiny.maxRequestSize=900*1024^2)

  output$table1 <- renderTable({
    if(is.null(input$xlsxs)){return ()}
    xl <- input$xlsxs

    for (f in 1:nrow(xl)){

      sheetList <- excel_sheets(xl$datapath[f])

      for (s in 1:length(sheetList)) {
        if (s==1){
          tempSheet <- read_excel(xl$datapath[f], sheet = sheetList[s], .name_repair = ~ gsub(" ", ".",.x, fixed = TRUE))

        }else {
          if(s != 1) {
            tempSheet <- read_excel(xl$datapath[f], sheet = sheetList[s],col_names=F, .name_repair = ~ gsub(" ", ".",.x, fixed = TRUE))

          }
        }
        if (f == 1 & s == 1) {
          df <- tempSheet
        }
        else {
          if(s != 1) {
            names(tempSheet) <- names(df)
          }
          df <- rbind(df,tempSheet)
        }

      }
    }

    return(df)

  })
}

shinyApp(ui = ui, server = server)

• You sometimes accidentally used xlsx instead of xlsxs when calling your input variable.
• The id of your output table is table1 so you should use output$table1
• In the first for-loop, you need to use nrow instead of length
• The path in xl$datapath is already the full path, no need to paste anything to it.

Hope this helps,
PJ

[Kahina]

Thanks you so much, it worked perfectly