如何捕捉交互表的手动输入并导出它？

Question

我创建了一个交互式表，它接受来自selectizeInput和手动输入的更改。更新后，我需要将表写入数据库。我的问题是，我能够捕捉到selectizeInput所做的更改。我不知道如何捕捉和保存手动输入所做的更改。例如,

library(shiny)
library(shinydashboard)
library(DT)
library(DBI)

ui <- dashboardPage(
  dashboardHeader(title = "test"),
  dashboardSidebar(),
dashboardBody(shiny::selectizeInput(inputId = "apple_lbs_filter",
                                    label = "Apple lbs",
                                    choices = c(1:10)),
              shiny::selectizeInput(inputId = "cherry_lbs_filter",
                                    label = "Cherry lbs",
                                    choices = c(1:10)),
              shiny::selectizeInput(inputId = "pineapple_lbs_filter",
                                    label = "Pineapple lbs",
                                    choices = c(1:10)),
              shiny::selectizeInput(inputId = "pear_lbs_filter",
                                    label = "Pear lbs",
                                    choices = c(1:10)),
              shiny::actionButton(inputId = "update_lbs",label = "Update lbs"),
              DT::DTOutput("fruit"))
)

server <- function(input, output, session) {

  fruit_df <- shiny::reactiveValues()
  fruit_df$df <- data.frame(fruit_name = c("apple","cherry","pineapple","pear"),
                            fruit_lbs = c(2,5,6,3))  
  
  output$fruit <- DT::renderDT({
      DT::datatable(fruit_df$df,editable = TRUE,extensions = 'Buttons',options = list(
        dom = 'frtBip',
        buttons = c('csv')
      ))
    })
  
  lbs_newentry <- shiny::observe({
    if(input$update_lbs > 0) {
      lbs_newline <- shiny::isolate(c(
        input$apple_lbs_filter,
        input$cherry_lbs_filter,
        input$pineapple_lbs_filter,
        input$pear_lbs_filter
      ))
      shiny::isolate(fruit_df$df <- cbind(fruit_name = c("apple",
                                               "cherry",
                                               "pineapple",
                                               "pear"),
                                       fruit_lbs = lbs_newline))
      
      # conn <- DBI::dbConnect(drv, user, password)
      # DBI::dbWriteTable(conn = conn,
      #                   SQL(schema.tbl),
      #                   fruit_df$df)
      # DBI::dbDisconnect(conn)
    }
  })
}

shinyApp(ui, server)

使用上面的代码，我可以用selectizeInput进行的更新(而不是手动更新)将表写入数据库。我对写入数据库部件进行了评论，以便在测试时不会遇到错误。我的猜测是，我写给db的数据是fruit_df$df，它不会捕获或保存手动输入。我应该如何捕获和保存selectizeInput和手动输入，并能够将所有更改导出到db？谢谢!

Answer 1

您必须避免在datatable中使用反应性数据，因为当它发生更改时，将重新生成完整的表，并且可以使用代理来避免这种情况。

library(shiny)
library(shinydashboard)
library(DT)

ui <- dashboardPage(
  dashboardHeader(title = "test"),
  dashboardSidebar(),
  dashboardBody(
    fluidRow(
      column(
        width = 6,
        selectizeInput(inputId = "apple_lbs_filter",
                       label = "Apple lbs",
                       choices = c(1:10)),
        selectizeInput(inputId = "cherry_lbs_filter",
                       label = "Cherry lbs",
                       choices = c(1:10)),
        selectizeInput(inputId = "pineapple_lbs_filter",
                       label = "Pineapple lbs",
                       choices = c(1:10)),
        selectizeInput(inputId = "pear_lbs_filter",
                       label = "Pear lbs",
                       choices = c(1:10))
      ),
      column(
        width = 6,
        verbatimTextOutput("reactiveDF")
      )
    ),
    actionButton("update_lbs", label = "Update lbs", class = "btn-primary"),
    br(), br(),
    DTOutput("fruit")
  )
)

server <- function(input, output, session) {
  
  Fruits <- reactiveVal(
    data.frame(
      fruit_name = c("apple", "cherry", "pineapple", "pear"),
      fruit_lbs = c(2, 5, 6, 3)
    ) 
  )
  
  output[["fruit"]] <- renderDT({
    datatable(
      isolate(Fruits()), # isolate to avoid regenerating the table (see proxy below)
      editable = list(target = "cell", disable = list(columns = c(0, 1))),
      extensions = 'Buttons',
      options = list(
        dom = 'frtBip',
        buttons = c('csv')
      )
    )
  })
  
  # use a proxy to update the data without regenerating the full table
  proxy <- dataTableProxy("fruit")
  
  observeEvent(input[["update_lbs"]], {
    lbs_newline <- c(
      input[["apple_lbs_filter"]],
      input[["cherry_lbs_filter"]],
      input[["pineapple_lbs_filter"]],
      input[["pear_lbs_filter"]]
    )
    dat <- Fruits()
    dat[["fruit_lbs"]] <- lbs_newline
    Fruits(dat) # update the reactive dataframe
    replaceData(proxy, dat, resetPaging = FALSE)
  })
  
  observeEvent(input[["fruit_cell_edit"]], { 
    info <- input[["fruit_cell_edit"]] # this input contains the info of the edit
    Fruits(editData(Fruits(), info, proxy)) 
    # editData() updates the data of the table
    # and returns the new dataframe that we store in the reactive dataframe
  })
  
  output[["reactiveDF"]] <- renderPrint({ # just to check
    Fruits()
  })
}

shinyApp(ui, server)

在这里，数据Fruits()的反应性是不必要的。您可以按以下方式进行：

server <- function(input, output, session) {
  
  Fruits <- data.frame(
    fruit_name = c("apple", "cherry", "pineapple", "pear"),
    fruit_lbs = c(2, 5, 6, 3)
  ) 
  
  output[["fruit"]] <- renderDT({
    datatable(
      Fruits, 
      editable = list(target = "cell", disable = list(columns = c(0, 1))),
      extensions = 'Buttons',
      options = list(
        dom = 'frtBip',
        buttons = c('csv')
      )
    )
  })
  
  # use a proxy to update the data without regenerating the full table
  proxy <- dataTableProxy("fruit")
  
  observeEvent(input[["update_lbs"]], {
    lbs_newline <- c(
      input[["apple_lbs_filter"]],
      input[["cherry_lbs_filter"]],
      input[["pineapple_lbs_filter"]],
      input[["pear_lbs_filter"]]
    )
    dat <- Fruits
    dat[["fruit_lbs"]] <- lbs_newline
    Fruits <<- dat
    replaceData(proxy, Fruits, resetPaging = FALSE)
  })
  
  observeEvent(input[["fruit_cell_edit"]], { 
    info <- input[["fruit_cell_edit"]] # this input contains the info of the edit
    Fruits <<- editData(Fruits, info, proxy) 
  })
  
}

但是这样你就不能像以前那样做verbatimTextOutput了。