How to format numbers in a specific row using js in a table rendered with rhandsontable?-CodePudding

In the simplified code at the bottom of this post, I believe it is js that is used for formatting outputs of the table rendered using rhandsontable. I play around with row/column formatting with some success in the js section of the code. However, as illustrated below, how would I format row 2 of the table so that it is shown as an integer (rounded to digits = 0, so there are no decimals) with commas separating the thousands, for all columns as they are added?

I've played around with the usual formatC(), etc., with no luck. Looks like the answer might lie in js.

Code:

library(rhandsontable)
library(shiny)

mydata <- data.frame('Series 1' = c(1,2000.39,3,4),check.names = FALSE)

rownames(mydata) <- c('A','B','C','D') 

ui <- fluidPage(
  rHandsontableOutput("mytable"),
  textInput('NewCol', 'Enter new column name'),
  actionButton("goButton", "Update Table")
)

server <- function(input, output) {
  output$mytable = renderRHandsontable(df())
  
  df <- eventReactive(input$goButton, {
    if(input$NewCol!="" && !is.null(input$NewCol) && input$goButton>0){
      newcol <- data.frame(NROW(mydata))
      newcol[2,] <- c(1234.22)
      names(newcol) <- input$NewCol
      mydata <<- cbind(mydata, newcol)
    }
    rhandsontable(mydata,rowHeaderWidth = 100)%>%
      hot_cols(
        renderer = "function(instance, td, row, col, prop, value, cellProperties) {
        Handsontable.renderers.NumericRenderer.apply(this, arguments);
          // format as integers first 2 rows:
            if(row == 0 || row == 1){td.innerHTML = `${value}`;} 
          // shade 2nd row:
            if(row == 1){td.style.background='#eff0f1'} 
          // format as % the 2nd set of 2 rows:
            if(row == 2 || row == 3){td.innerHTML = `${Number.parseFloat(value*100)}%`} 
        }") %>% 
      hot_row(c(2), readOnly = TRUE) # makes row 2 read-only
    
  }, ignoreNULL = FALSE)
  observe(if (!is.null(input$mytable)) mydata <<- hot_to_r(input$mytable))
}

shinyApp(ui,server)

CodePudding user response：

One option would be to use the formatter functions provided by the

CodePudding user response：

@stefan has a nice answer. My present answer does not provide the desired output but I think it is instructive.

The rhandsontable package includes the (old version of the) Numbro library. Theoretically one should get the desired output by doing:

// shade and format second row
if(row == 1){
  td.style.background = '#eff0f1';
  td.innerHTML = numbro(value).format('0,0');
}

But there's a bug in this version of Numbro I think, and this does not display the desired format, there are the decimal digits.

For example if you want one decimal digit: numbro(value).format('0,0.0').

The formatting to percentage works: numbro(value).format('0%').

Alternative: d3.format

If you want to use a flexible library for number formatting, d3-format is very nice.

This gives the desired output:

// shade and format second row
if(row == 1){
  td.style.background = '#eff0f1';
  td.innerHTML = d3.format(',.0f')(value);
}

after including the d3-format library in your UI:

ui <- fluidPage(
  tags$head(
    tags$script(src = "https://cdn.jsdelivr.net/npm/d3-format@3")
  ),
  ......