Change column sum output of shiny datatable

Question

This is a follow up question to this question: Adding Total/Subtotal to the bottom of a DataTable in Shiny with the following code snippet:

library(shiny)
library(DT)

ui <- shinyUI(fluidPage(
  h1('Testing TableTools'),
  mainPanel(
    dataTableOutput('display')
  )      
))

Names <- c("",names(mtcars))
FooterNames <- c(rep("",4),Names[5:6],rep("",6))

server <- function(input, output, session) {

  sketch <- htmltools::withTags(table(
    tableHeader(Names),tableFooter(FooterNames)
  ))

  opts <- list(
    dom = 'Bfrtip', buttons = list('colvis','print',list(extend='collection',text='Download',buttons = list('copy','csv','excel','pdf'))),
               footerCallback = JS(
                 "function( tfoot, data, start, end, display ) {",
                 "var api = this.api(), data;",
                 "$( api.column(5).footer()).html('SubTotal:  '+",
                 "api.column(5).data().reduce( function ( a, b ) {",
                 "return a + b;",
                 "} )",
                 ");",
                 "$( api.column(4).footer()).html('SubTotal: '+",
                 "api.column(4).data().reduce( function ( a, b ) {",
                 "return a + b;",
                 "} )",
                 ");","}")
  )

  output$display <- DT::renderDataTable(container = sketch,extensions = 'Buttons',options = opts,{
    mtcars
  })
}

shinyApp(ui = ui, server = server)

In my dataframe I have columns that contain doubles with two digits after the comma. The subTotal is shown as something like this:

1234,51999999999 instead of 1234,52.

What do I have to change in the Javascript code to get only two digits after the comma, thounsander seperators as a dot and the decimal point as a comma and also, a Euro (€) symbol next to the sum?

user_16853114 · Answer

Why you want to do this in JavaScript if you are using shiny? This is the R way:

paste0(
  formatC(1234.51999999999, format="f", big.mark=".",
  decimal.mark = ",", digits=2), "€"
)
# [1] "1.234,52€"

Or use JS to do the job:

library(shiny)
library(DT)

ui <- shinyUI(fluidPage(
  h1("Testing TableTools"),
  mainPanel(
    dataTableOutput("display")
  )
))

Names <- c("", names(mtcars))
FooterNames <- c(rep("", 4), Names[5:6], rep("", 6))

server <- function(input, output, session) {
  sketch <- htmltools::withTags(table(
    tableHeader(Names), tableFooter(FooterNames)
  ))

  opts <- list(
    dom = "Bfrtip", buttons = list("colvis", "print", list(extend = "collection", text = "Download", buttons = list("copy", "csv", "excel", "pdf"))),
    footerCallback = JS(
      "
      function(tfoot, data, start, end, display) {
    var api = this.api(),
        data;
    var sum1 =  api.column(5).data().reduce(function(a, b) {
        return a + b;
    });
    sum1 = Intl.NumberFormat('de-DE', { style: 'currency', currency: 'EUR' }).format(sum1)

    $(api.column(5).footer()).html('SubTotal:  ' + sum1)
}
"
    )
  )

  output$display <- DT::renderDataTable(container = sketch, extensions = "Buttons", options = opts, {
    mtcars
  })
}

shinyApp(ui = ui, server = server)

Change column sum output of shiny datatable

1 Answers