John Smith John Smith - 2 months ago 26
R Question

SHINY R reading different versions of excel files based on user selections

I have a shiny app, that reads in files that are uploaded by a user
Different people have different versions of excel. So if a user is using excel 2007 or excel 2010 we use one section of code. If they upload it in excel 2003 we use a different library to read the file. The user specifies in the form which version of excel they have

The function to do this is below

get_data <- function(strFilePath, storageType) {

if (is.null(strFilePath))
return(NULL)

if (storagetType == 'xls2010' || storagetType == 'xls2007'){
df <- openxlsx:read.xlsx(strFilePath,sheet = 1)
}
else if (storagetType == 'xls2003'){
df <- XLConnect:readWorksheetFromFile(strFilePath)
}

return(df)
}


To implement this in shiny, i have two widgets. A
fileInput
and a
selectInput
. The user selects which version of excel they are running and then selects the file which then is read in by the
function get_data
. I suspect its because I'm not utilizing the reactivity correctly. When i run the app and upload the file i get the error message


Error: object 'storagetType' not found


# Global.R
storage_types <- c(
"Excel 2010" = "xls2010",
"Excel 2007" = "xls2007",
"Excel 2003" = "xls2003"
)

# UI.R
ui <- shinyUI(fluidPage(
navbarPage("Navbar!",
# Tab contains all the information to upload a file
tabPanel("Upload Data",
# Side Panel with Options
fluidRow(
column(4, wellPanel(
id = "leftPanel",
div(
id = "Header",
h3("Options", align = "center"),
tags$hr()
),

div(
selectInput("xlsversion", "2. Select your Excel version", storage_types),
fileInput(inputId = 'file1',label = '3. Choose An Excel File'),
)
)))))))

# Server.R

server <- shinyServer(
function(input, output) {
# When the Browser to the file location gets updated
upload_data <- reactive({
inFile <- input$file1
if (is.null(inFile))
return(NULL)
get_data(inFile$datapath, input$xlsversion)
})

})

Answer

You not need selectInput simply parse name of file.

Also some typo fixed

 library(shiny)

get_data <- function(strFilePath, storageType) {

  if (is.null(strFilePath))
    return(NULL)

  file_ext=substring(storageType,nchar(storageType)-3)
  if (file_ext == 'xlsx' ){
    df <- openxlsx::read.xlsx(strFilePath,sheet = 1)
  }
  else if (file_ext == '.xls'){
    df <- XLConnect::readWorksheetFromFile(strFilePath,sheet=1)
  } else{
    return(data.frame("Bad file format"))
  }

  return(df)
}
# UI.R
ui <- shinyUI(fluidPage(
  navbarPage("Navbar!",
             # Tab contains all the information to upload a file
             tabPanel("Upload Data",
                      # Side Panel with Options
                      fluidRow(
                        column(4, wellPanel(
                          id = "leftPanel",
                          div(
                            id = "Header",
                            h3("Options", align = "center"),
                            tags$hr()
                          ),

                          div(
                           fileInput(inputId = 'file1',label =  '3.  Choose An Excel File') 
                          )
                        ))),
                      dataTableOutput("result")))))

# Server.R

server <- function(input, output) {
    # When the Browser to the file location gets updated  
    upload_data <- reactive({
      if (is.null(input$file1))
        return(NULL)
      get_data(input$file1$datapath, input$file1$name)
    })
    output$result=renderDataTable({
      upload_data()
    })
}

shinyApp(ui,server)