R/R Shiny : Downloading Data Tables using Download Handler, getting error - Can't convert to

Question

I have two data table which I want to download in two different sheets of an xlsx file after I have edited their cells. Using the below mentioned approach I am getting the error -

Warning: Error in : Assigned data cell$value must be compatible with existing data. i Error occurred for column Trial ID:. x Can't convert to . [No stack trace available]

The relevant server code is as follows -

x<- reactive({
        inFile <- input$file
        
        if(is.null(inFile))
            return(NULL)
        file.rename(inFile$datapath,
                    paste(inFile$datapath, ".xlsx", sep=""))
        read_excel(paste(inFile$datapath, ".xlsx", sep=""),sheet = 1)
        })

y <- reactive({
        inFile <- input$file
        
        if(is.null(inFile))
            return(NULL)
        file.rename(inFile$datapath,
                    paste(inFile$datapath, ".xlsx", sep=""))
        read_excel(paste(inFile$datapath, ".xlsx", sep=""),sheet = 2)
        
    })

output$table1 <- renderDataTable({
        x()
    }, filter="top", class = 'hover cell-border stripe', editable= TRUE,extensions= 'Buttons',
    options = list(dom = 'Bfrtip',pageLength =10,
                   buttons = c('copy','csv','excel','pdf','print'), scrollX=TRUE),server=FALSE)


output$table2 <- renderDataTable({
        y()
    }, filter="top", class = 'hover cell-border stripe', editable= TRUE,extensions= 'Buttons',
    options = list(dom = 'Bfrtip',pageLength =10,
                   buttons = c('copy','csv','excel','pdf','print'), scrollX=TRUE),server=FALSE)



observeEvent(input[["table1_cell_edit"]], {
            cell <- input[["table1_cell_edit"]]
            newdf <- x()
            newdf[cell$row, cell$col] <- cell$value
            x(newdf)
        })
        
observeEvent(input[["table2_cell_edit"]], {
            cell <- input[["table2_cell_edit"]]
            newdf <- y()
            newdf[cell$row, cell$col] <- cell$value
            y(newdf)
        })
            

    
output$dl <- downloadHandler(
            filename = "test.xlsx",
            content = function(file) {
            write.xlsx2(x(), file, sheetName = "Sheet1")
            write.xlsx2(y(), file, sheetName = "Sheet2", append = TRUE)
            }
        )

Can someone please tell me where I am going wrong?

YBS · Accepted Answer · 2021-06-03T15:53:23.157

1

Perhaps you should use reactiveValues object, instead of reactive object for x and y. Try the example below

library(shiny)
library(DT)
library(readxl)

ui <- fluidPage(
  fileInput("file", "Import File", accept = ".xlsx"),
  DTOutput("t1")
)

server <- function(input, output, session) {
  x <- reactiveValues()
  
  observe({
    
    xdf <- reactive({
      req(input$file)
      inFile <- input$file
      
      if(is.null(inFile)) return(NULL)
      file.rename(inFile$datapath, paste0(inFile$datapath, ".xlsx"))
      read_excel(paste0(inFile$datapath, ".xlsx"),sheet = 1)
    })
    
    x$df <- xdf()
  })  
  
  output$t1 <- renderDT({x$df},filter="top", class = 'hover cell-border stripe', selection = 'none',
                        editable= list(target = 'cell'), extensions= 'Buttons',
                        options = list(dom = 'Bfrtip',pageLength =10,
                                       buttons = c('copy','csv','excel','pdf','print'), scrollX=TRUE),server=FALSE)
  
  observeEvent(input[["t1_cell_edit"]], {
    cell <- input[["t1_cell_edit"]]
    str(cell)
    x$df <<- editData(x$df, cell)
  })

}

shinyApp(ui, server)

edited Jun 03 '21 at 15:53

answered Jun 03 '21 at 12:38

YBS

19,324
2
9
27

Can you please help me with the code to read the excel file like I have done in the reproducible example? `observe({x$df<- if(is.null(input$file$datapath)) return(NULL) file.rename(input$file$datapath, paste(input$file$datapath, ".xlsx", sep="")) read_excel(paste(input$file$datapath, ".xlsx", sep=""),sheet = 1) })` This is not showing the datatable – Nil_07 Jun 03 '21 at 13:05
This works perfectly as a standalone script. – Nil_07 Jun 03 '21 at 13:14
I tried and I got this error - **Warning in DT::coerceValue(v, x$df[i, j]) : The data type is not supported: tbl_df, tbl, data.frame Warning: Error in : Assigned data `DT::coerceValue(v, x$df[i, j])` must be compatible with existing data. i Error occurred for column `Trial ID:`. x Can't convert to . [No stack trace available]** – Nil_07 Jun 03 '21 at 13:25
I am able to edit cells having characters in them and download the edited data table but in that case, every time I am editing them, I am getting this warning in the console - **Warning in DT::coerceValue(v, x$df[i, j]) : The data type is not supported: tbl_df, tbl, data.frame** Can you please help me solve this issue for character cells and the one above for numeric cells? – Nil_07 Jun 03 '21 at 14:49
Also, the data table is sometimes loading and sometimes it isn't – Nil_07 Jun 03 '21 at 15:08
1

Sorry, I had to step out for a couple of hours. Please try the updated code. – YBS Jun 03 '21 at 15:54
Thank you for the solution. It works! Can you shed some light as to why the data table is not rendering/showing up sometimes? – Nil_07 Jun 03 '21 at 17:24
Not sure. Perhaps it might depend on your excel file. – YBS Jun 03 '21 at 17:42
It's working fine for small files but for bigger files this is happening (of size 12mbs) – Nil_07 Jun 03 '21 at 17:42
1

See answer [here](https://stackoverflow.com/questions/18037737/how-to-change-maximum-upload-size-exceeded-restriction-in-shiny-and-save-user) – YBS Jun 03 '21 at 17:58
I have already used **options(shiny.maxRequestSize=30*1024^2)**. Still I am facing this problem sometimes – Nil_07 Jun 03 '21 at 18:11
Trying to download large files is giving this error - **Warning: Error in .jcall: java.lang.OutOfMemoryError: GC overhead limit exceeded [No stack trace available]** – Nil_07 Jun 03 '21 at 18:19
1

Your initial problem was reading in file. That seems to have been addressed. Now the problem is downloading. Perhaps you should post it as a separate question. – YBS Jun 03 '21 at 18:29
[Here](https://stackoverflow.com/questions/67827355/r-r-shiny-downloading-large-files-using-download-handler-gives-error-error-in) is the link to the new question – Nil_07 Jun 03 '21 at 19:07
Can someone please solve this question - https://stackoverflow.com/questions/68864368/r-r-shiny-copying-cells-from-excel-and-pasting-them-in-a-shiny-application-usi – Nil_07 Aug 20 '21 at 15:57

R/R Shiny : Downloading Data Tables using Download Handler, getting error - Can't convert to

1 Answers1

Linked