Search code examples
rshinyrhandsontable

How to choose a date from a dropdown calendar?

There is the following data frame created with rhandsontable as a shiny app. I am trying to locate a date from a dropdown calendar in the "Transaction date" column. However, my choice from the calendar is not replacing the preexisting date.

When I run a script on the R console there is popping up the following message as well: Warning in as.character.POSIXt(as.POSIXlt(x)): as.character(td, ..) no longer obeys a 'format' argument; use format(td, ..)?

Would appreciate your help!

library(shiny)
library(data.table)
library(rhandsontable)
library(shinydashboard)
library(dplyr)

`Cash in hand` <- data.table(
  "Transaction date" = as.Date("2022-01-01"),
  "Profit" = as.numeric(0),
  "Loss" = as.numeric(0),
  "Balance" = as.numeric(0),
  stringsAsFactors = FALSE
)

ui <- fluidPage(
  dashboardPage(
    dashboardHeader(title = "Accounting"),
    dashboardSidebar(
      sidebarMenu(
        menuItem("Home", tabName = "home"),
        menuItem("Current assets", tabName = "current assets", 
                 menuItem("Cash", tabName = "cash",
                          menuSubItem("Cash in hand", tabName = "cash_in_hand")
                 )
        )
      )
    ),
    dashboardBody(
      tabItems(
        tabItem(tabName = "home",
                h2("Welcome to the Home Page")
        ),
        tabItem(tabName = "cash_in_hand",
                rHandsontableOutput("Table1010")
        )
      )
    )
  )
)

server <- function(input, output) {

  data <- reactiveValues()

  observe({
   input$recalc
   data$df1010 <- as.data.frame(`Cash in hand`)
  })

  observe({
    if (!is.null(input$Table1010))
      data$df1010 <- hot_to_r(input$Table1010)
  })

  output$Table1010 <- renderRHandsontable({

    data$df1010$Balance <- data$df1010$Profit + data$df1010$Loss 

    data <- data$df1010 %>%
      mutate(`Transaction date` = as.Date("2022-01-01")) %>%
      head(1)

    rhandsontable(data, strecH = "all") %>%
      hot_col(1, type = "dropdown") %>%
      hot_col(1, type = "date")    
  })
}

shinyApp(ui, server)
Solution

  • If you want to use the the date dropdown in an rhandsontable, you can define the column in your data frame using as.character() and then pass the dateFormat explicitly inside hot_col, e.g. hot_col(1, dateFormat = 'YYYY-MM-DD', type = "date").

    enter image description here

    library(shiny)
library(data.table)
library(rhandsontable)
library(shinydashboard)
library(dplyr)

`Cash in hand` <- data.table(
    "Transaction date" = as.character(Sys.Date()),
    "Profit" = as.numeric(0),
    "Loss" = as.numeric(0),
    "Balance" = as.numeric(0),
    stringsAsFactors = FALSE
)

ui <- fluidPage(dashboardPage(
    dashboardHeader(title = "Accounting"),
    dashboardSidebar(sidebarMenu(
        menuItem("Home", tabName = "home"),
        menuItem(
            "Current assets",
            tabName = "current assets",
            menuItem(
                "Cash",
                tabName = "cash",
                menuSubItem("Cash in hand", tabName = "cash_in_hand")
            )
        )
    )),
    dashboardBody(tabItems(
        tabItem(tabName = "home",
                h2("Welcome to the Home Page")),
        tabItem(tabName = "cash_in_hand",
                rHandsontableOutput("Table1010"))
    ))
))

server <- function(input, output) {
    data <- reactiveValues()
    
    observe({
        input$recalc
        data$df1010 <- as.data.frame(`Cash in hand`)
    })
    
    observe({
        if (!is.null(input$Table1010))
            data$df1010 <- hot_to_r(input$Table1010)
    })
    
    output$Table1010 <- renderRHandsontable({
        data$df1010$Balance <- data$df1010$Profit + data$df1010$Loss
        
        rhandsontable(head(data$df1010, n = 1), stretchH = "all") |>
            hot_col(1, dateFormat = 'YYYY-MM-DD', type = "date")
    })
}

shinyApp(ui, server)