Shiny -How to save to excel every change in renderTable?-CodePudding

I use Timevis package. first of all I read an excel file with missions. In my code the user can see all the missions on a time line, and he can edit/add/remove any missions. after the user make a change I can see the update table below. I want to save to my excel file every update that the user make. this is my code:

  library(shiny)
  
  my_df <-  read_excel("x.xlsx")
  data <- data.frame(
    id = my_df$id,
    start = my_df$start
    end = my_df$end
    content = my_df$content
  )
  
  ui <- fluidPage(
    timevisOutput("appts"),
    tableOutput("table")
  )
  
  server <- function(input, output) {
    output$appts <- renderTimevis(
      timevis(
        data,
        options = list(editable = TRUE, multiselect = TRUE, align = "center")
      )
    )
    

    
    output$table <- renderTable(
      input$appts_data
    )
  }
  shinyApp(ui, server)

CodePudding user response：

You can use actionButton/ observe to call saveworkbook (package openxlsx) to save your changes. Technically you are not saving these changes, but replacing the file with an identical file containing the changes.

library(shiny)
library(openxlsx)
library(timevis)
library(readxl)

my_df <-  read_excel("x.xlsx")

data <- data.frame(
  id = my_df$id,
  start = my_df$start,
  end = my_df$end,
  content = my_df$content
)

mypath = paste0(getwd(), "/x.xlsx") # Path to x.xlsx

ui <- fluidPage(
  timevisOutput("appts"),
  tableOutput("table"),
  actionButton("save", "Save")
)

server <- function(input, output) {
  output$appts <- renderTimevis(
    timevis(
      data,
      options = list(editable = TRUE, multiselect = TRUE, align = "center")
    ))

    observeEvent(input$save, 
    {
      my_df<- createWorkbook()
      
      addWorksheet(
        my_df,
        sheetName = "data"
      )

      writeData(
      wb = my_df,
      sheet = "data",
      x = input$appts_data,
      startRow = 1,
      startCol = 1
    )

    saveWorkbook(my_df, file = mypath,
                 overwrite = TRUE)
    
    
    })  
  
  
  
  output$table <- renderTable(
    input$appts_data
  )
}
shinyApp(ui, server)