R shiny app issue while loading an xlsx file

san 0
Viewed 21

enter image description hereI am stuck up with one issue in R Shiny app where I am uploading an xlsx file which has data present in 8 different sheets. I did the bind_rows for all the 8 sheets and I am able to append everything into one dataframe. The issue is I am not able to see the output when I execute the Shiny app. The error I am seeing is "Error : path must be a string". Could you please help me in resolving this issue. Thank you beforehand for the clarification.

library(shiny)
library(readxl)
library(tidyverse)
library(dplyr)

# Define UI for application
ui <- fluidPage(

# Application title
titlePanel("SDV Complaince Report"),

# Sidebar with a slider input for number of bins 
sidebarLayout(
    sidebarPanel(
        fileInput("file1", "Choose Excel File",
                  multiple = FALSE,
                  accept = c(".xls",
                             ".xlsx"))
    ),
    
    # Show a table of the final output
    mainPanel(
        tableOutput("contents")
    )
)

)

# Define server logic required
server <- function(input, output) {

output$contents <- renderTable({
    fname <- input$file1
    sheet <- excel_sheets(fname$datapath)
    data_frame <- lapply(setNames(sheet, sheet), 
                        function(x) read_excel(fname, sheet=x, col_names = F))
    for(i in 1:length(data_frame)) {
        data_frame[[i]]$...5 <- as.numeric(data_frame[[i]]$...5)
        print(class(data_frame[[i]]$...5))
    }
    
    # attaching all dataframes together
    data_frame <- bind_rows(data_frame, .id="Sheet")
    
  
})

}

# Run the application 
shinyApp(ui = ui, server = server)
shiny r
1 Answers

I'm not adding anything different than the suggestion I had in the comments, but to be able to show the whole file I needed the answer space for more info. There are three differences between my code and the one you original posted. One, I blocked off library(tidyverse) since it's not used in this example. Two, I added req(input$file1) to the table, to prevent it from trying to load the table before a file has been selected. And three, the most important change, is changing fname to fname$datapath in the read_excel portion of lapply.

When you try the code I have below on your dataset, does this work? It works for me on my own test data set. If it doesn't work for you, then my only guess is your dataset is causing the issue. Best of luck!

library(shiny)
library(readxl)
# library(tidyverse) #Not needed for the test
library(dplyr)

# Define UI for application
ui <- fluidPage(
  
  # Application title
  titlePanel("SDV Complaince Report"),
  
  # Sidebar with a slider input for number of bins 
  sidebarLayout(
    sidebarPanel(
      fileInput("file1", "Choose Excel File",
                multiple = FALSE,
                accept = c(".xls",
                           ".xlsx"))
    ),
    
    # Show a table of the final output
    mainPanel(
      tableOutput("contents")
    )
  )
)

# Define server logic required
server <- function(input, output) {
  
  output$contents <- renderTable({
    req(input$file1) #Requiring prevents the error in this table as it tries to load before file selected
    fname <- input$file1
    sheet <- excel_sheets(fname$datapath)
    data_frame <- lapply(setNames(sheet, sheet), 
                         function(x) read_excel(fname$datapath, sheet=x, col_names = F)) #Most important change - fname to fname$datapath
    for(i in 1:length(data_frame)) {
      data_frame[[i]]$...5 <- as.numeric(data_frame[[i]]$...5)
      print(class(data_frame[[i]]$...5))
    }
    
    # attaching all dataframes together
    data_frame <- bind_rows(data_frame, .id="Sheet")
    
    
  })
}

# Run the application 
shinyApp(ui = ui, server = server)
Silentdevildoll0
Related