Hide HTML code inside datatable edit when formatting the table with formattable

Question

I'm using formattable and DT together in order to create a custom table, while still being able to edit the cell values (using editable=T, from DT).

The problem is that if I use formattable() to make a custom table, whenever I double click on a cell to edit its content, it will show the HTML code instead of the simple value.

Here an example:

library(formattable)
library(DT)

products <- data.frame(id = 1:5, 
                       price = c(10, 15, 12, 8, 9),
                       rating = c(5, 4, 4, 3, 4),
                       market_share = percent(c(0.1, 0.12, 0.05, 0.03, 0.14)),
                       revenue = accounting(c(55000, 36400, 12000, -25000, 98100)),
                       profit = accounting(c(25300, 11500, -8200, -46000, 65000)))

f_table <- formattable(products, list(
  price = color_tile("transparent", "lightpink"))) 

as.datatable(f_table, editable=T)
# as.datatable is from formattable, it lets you keep the table styling

Here you can see the problem:

Is there a simple way to fix this?

Answer 1

Instead of using formattable, you can use DT with a render option to set your CSS.

library(DT)

products <- data.frame(id = 1:5, 
                       price = c(10, 15, 12, 8, 9),
                       rating = c(5, 4, 4, 3, 4))

render <- c(
  "function(data, type, row){",
  "  if(type === 'display'){",
  "    var s = '<span style=\"padding: 0 4px; border-radius: 4px; background-color: pink;\">' + data + '</span>';",
  "    return s;",
  "  } else {",
  "    return data;",
  "  }",
  "}"
)

datatable(products, editable = "cell", 
          options = list(
            columnDefs = list(
              list(targets = 2, render = JS(render))
            )
          )
)

Something strange happens: if you double-click exactly on the cell content (the value, e.g. 10), then the edit does not work. You have to double-click on the cell but not on the value.

---

EDIT

Here is another solution, taken from this question.

library(DT) 

products <- data.frame(id = 1:5, 
                       price = c(10, 15, 12, 8, 9),
                       rating = c(5, 4, 4, 3, 4))

break_points <- 
  function(x) stats::quantile(x, probs = seq(.05, .95, .05), na.rm = TRUE)
red_shade <- 
  function(x) round(seq(255, 40, length.out = length(x) + 1), 0) %>% 
  {paste0("rgb(255,", ., ",", ., ")")}

brks <- apply(products, 2, break_points)
clrs <- apply(brks, 2, red_shade)

column <- "price"

datatable(products, editable = "cell") %>%
  formatStyle(column, backgroundColor = styleInterval(brks[,column], clrs[,column]))