0

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)
CoCoL0r3s
  • 56
  • 4
  • 2
    Your issue is in the `calc()` function. `col_names = letters[1:cell_range]` is improperly defined as `cell_range` is not a number, but `B15:C16` when 2 col are selected. The error you are noticing is coming from this statement. – YBS May 07 '21 at 15:36
  • 1
    Perhaps you should use `col_names = letters[1:num_of_cols]` – YBS May 07 '21 at 16:12
  • Well silly me, sometimes one should just head for weekend... Thank you guys, your answers did the trick, data is getting imported now. – CoCoL0r3s May 10 '21 at 06:55

0 Answers0