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?
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.
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.
To ease this task, the RStudio IDE includes new features to import data from: csv, xls, xlsx, sav, dta, por, sas and stata files.
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)
})
}
)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With