DataTable highlight empty cells

Question

I am trying to highlight empty cells in a DataTable (package DT), so that I can demonstrate to the users of the table which information still needs to be completed.

I tried to achieve this with the DT::formatStyle() function and then using one of the helper functions styleInterval() or styleEqual(), but neither of them seem to have a facility to check for empty values.

Any ideas how it could be achieved?

Reproducible example below is only highlighting the dates which are greater than 1 in green, but it does not highlight the empty ones in red...

start_date <- c("2020-07-01","2020-01-02","2019-10-01",NA,"2019-10-01",NA,"2019-10-01","2019-10-01")
end_date <- c(NA, "2020-12-31","2020-12-31","2020-12-31","2020-12-31","2020-12-31","2020-12-31","2020-12-31")

df <- cbind(start_date, end_date)

DT::datatable(df,
              selection = "single",
              options = list(
                scrollX = TRUE,
                scrollY = TRUE,
                lengthChange = FALSE,
                searching = FALSE,
                initComplete = JS(
                  "function(settings, json) {",
                  "$(this.api().table().container()).css({'font-size': '80%'});",
                  "$(this.api().table().header()).css({'font-size': '100%'});",
                  "}")
              ) 
) %>%
  DT::formatStyle(columns = c("start_date", "end_date"),
                  background = styleInterval(c(NULL, 1), c("red","green")))

That was my attempt to check if something is filled or not. Anyway, I tried your solution in my program and it works as I wanted it, so thanks a lot for helping out! — tom1003, Nov 15 '20 at 13:00

score 0 · Accepted Answer · answered Nov 15 '20 at 04:15

library(DT)

start_date <- c("2020-07-01","2020-01-02","2019-10-01",NA,"2019-10-01",NA,"2019-10-01","2019-10-01")
end_date <- c(NA, "2020-12-31","2020-12-31","2020-12-31","2020-12-31","2020-12-31","2020-12-31","2020-12-31")

df <- cbind(start_date, end_date)

datatable(df,
          selection = "single",
          options = list(
            scrollX = TRUE,
            scrollY = TRUE,
            lengthChange = FALSE,
            searching = FALSE,
            initComplete = JS(
              "function(settings, json) {",
              "$(this.api().table().container()).css({'font-size': '80%'});",
              "$(this.api().table().header()).css({'font-size': '100%'});",
              "}")
          ) 
) %>% formatStyle(
  columns = c("start_date", "end_date"),
  background = JS('value === null ? "red" : new Date(value) > new Date("2020-01-01") ? "green" : ""'))

DataTable highlight empty cells

1 Answers1