I am trying to write a Shiny App which takes multiple xlsx files as input, processes them and offers to download a summary file afterwards. In a first step, I construct a dummy DF to append the the files to. Please note that this is my first experience with Shiny apps.
Unfortunately the app crashes directly after uploading the files. It seems read_xlsx
is not able to work with the path of the tmp files uploaded. Following warning/error is received when running the app:
Warnung in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet,
NAs durch Umwandlung erzeugt
Warnung: Error in :: NA/NaN Argument
In the example code at the end (example is design to only upload 1 file), I added some print statements to debug the warnings. See the output below. What catches my eye is the character(0)
result of the third print statement, which correlates to the warning of the app. I am pretty sure I am missing something very basic here, but what is it? Is this related to environments as discussed here?
[1] "C:\\Users\\userxyz\\AppData\\Local\\Temp\\RtmpE5aM7A/0448481d282a5ba2c08583e0/0.xlsx"
[1] 25
character(0)
character(0)
[1] "B15:Z16"
minimal example:
library(shiny)
library(readxl)
library(dplyr)
calc <- function(tmp_files, num_of_cols) {
print(tmp_files)
print(num_of_cols)
files <- list.files(enc2native(normalizePath(tmp_files)), full.names = T)
files2 <- list.files(tmp_files, full.names = T)
print(files)
print(files2
cell_range <- paste0("B15:", toupper(letters[num_of_cols+1]), "16")
print(cell_range)
cols <- read_xlsx(tmp_files, range = cell_range, col_names = letters[1:cell_range]) %>%
mutate_all(str_replace_na, replacement = "") %>%
transmute_all(str_c, collapse = "") %>%
slice(-1) %>%
as.character()
}
ui <- fluidPage(
sidebarLayout(
sidebarPanel(
fileInput("upload", "Upload Files",
multiple = TRUE),
numericInput(inputId = "num_of_cols",
label = "Specify number of Columns",
value = 0),
actionButton(inputId = "gobutton", "GO!"),
downloadButton("downloadbutton", "Download Results")
),
mainPanel(
tableOutput("upload_overview")
)
)
)
server <- function(input, output) {
output$upload_overview <- renderTable(input$upload$datapath)
observeEvent(
input$gobutton, {
data <- calc(input$upload$datapath, input$num_of_cols)
})
output$downloadbutton <- downloadHandler(
filename = "table.xlsx",
content = function(file) {write_xlsx(data, path = file)}
)
}
shinyApp(ui = ui, server = server)