Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"read_excel" in a Shiny app

Tags:

r

xlsx

shiny

I have a Shiny app that uses the read.xlsx function from package xlsx. All works fine, but I want to change to read_excel from readxl, hoping it would be faster and able to cope with large files.

ui part:

fileInput("inputFile","Upload file...")

server part:

  data <- reactive({
    inFile <- input$inputFile
    if (is.null(inFile)) { return(NULL) }    
    dataFile <- read_excel(inFile$datapath,sheet=1)
    return(dataFile)
  })

I get the "Unknown format" error.

inFile$datapath is "/tmp/.../60974676c7287e913d1c0dc5/0"
inFile$type is "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

Question 1: is there a way to tell read_excel that it's a xlsx type file?
Question 2: is it possible to control the location where the uploaded file will be stored?

like image 949
polTo Avatar asked Jun 03 '15 15:06

polTo


People also ask

What library is Read_excel in?

readxl: Read Excel Files Supports '. xls' via the embedded 'libxls' C library <https://github.com/libxls/libxls> and '. xlsx' via the embedded 'RapidXML' C++ library <http://rapidxml.sourceforge.net>. Works on Windows, Mac and Linux without external dependencies.

What is Read_excel?

read_excel() calls excel_format() to determine if path is xls or xlsx, based on the file extension and the file itself, in that order. Use read_xls() and read_xlsx() directly if you know better and want to prevent such guessing.

Can RStudio read Xlsx?

To ease this task, the RStudio IDE includes new features to import data from: csv, xls, xlsx, sav, dta, por, sas and stata files.


1 Answers

This was an open issue with the readxl package. The current workaround provided there is to copy the file data path and append .xlsx. Here is a working example on my machine limited to .xlsx files edited to use file.rename instead of file.copy.

library(shiny)
library(readxl)

runApp(
    list(
        ui = fluidPage(
            titlePanel("Use readxl"),
            sidebarLayout(
                sidebarPanel(
                    fileInput('file1', 'Choose xlsx file',
                              accept = c(".xlsx")
                              )
                    ),
                mainPanel(
                    tableOutput('contents'))
                )
            ),
        server = function(input, output){
            output$contents <- renderTable({
                inFile <- input$file1

                if(is.null(inFile))
                    return(NULL)
                file.rename(inFile$datapath,
                          paste(inFile$datapath, ".xlsx", sep=""))
                read_excel(paste(inFile$datapath, ".xlsx", sep=""), 1)
            })
        }
        )
    )

EDIT Note that with the 1.1.0 version of readxl it no longer needs to have the file renamed. The following works without a problem for me now.

library(shiny)
library(readxl)

runApp(
  list(
    ui = fluidPage(
      titlePanel("Use readxl"),
      sidebarLayout(
        sidebarPanel(
          fileInput('file1', 'Choose xlsx file',
                    accept = c(".xlsx")
          )
        ),
        mainPanel(
          tableOutput('contents'))
      )
    ),
    server = function(input, output){
      output$contents <- renderTable({

        req(input$file1)

        inFile <- input$file1

        read_excel(inFile$datapath, 1)
      })
    }
  )
)
like image 131
cdeterman Avatar answered Oct 26 '22 18:10

cdeterman