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

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?

Answer 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)