How to Format R Shiny DataTable Like Microsoft Excel Table

Question

I have some tables in Microsoft Excel that I need to recreate in an R Shiny App. The formatting in R has to remain at least mostly the same as the original context.

Here are images of the original tables:

Table 1

Table 2

Notice the formatting: There are lines under table headers and above totals, headers and totals are bolded, numbers in the Monthly Bill column have thousands seperated by commas and have dollar symbols, and the final number in Table 2 is boxed in.

If the lines were not recreatable it would be fine, but I need to at least be able to bold the selected topics, headers, and totals, and be able to get the correct number format for the Monthly Bill column.

I have tried using the DT package but I can't figure out how to format rows instead of columns. I noticed DT uses wrappers for JavaScript functions but I don't personally know JavaScript myself. Is there a way to format this the way I that I need through R packages or Javascript?

Edit:

Although it would be simple, I cannot merely include an image of the tables because some of the numbers are going to be linked to user input and must have the ability to update.

Answer 1

pixiedust makes it easy to do cell-specific customizations.

T1 <- data.frame(Charge = c("Environmental", "Base Power Cost",
                            "Base Adjustment Cost", "Distribution Adder",
                            "Retail Rate Without Fuel", "Fuel Charge Adjustment",
                            "Retail Rate With Fuel"),
                 Summer = c(0.00303, 0.06018, 0.00492, 0.00501, 0.07314,
                            0.02252, 0.09566),
                 Winter = c(0.00303, 0.05707, 0.00468, 0.01264, 0.07742, 
                            0.02252, 0.09994),
                 Transition = c(0.00303, 0.05585, 0.00459, 0.01264,
                                0.07611, 0.02252, 0.09863),
                 stringsAsFactors = FALSE)

T2 <- data.frame(Period = c("Summer", "Winter", "Transition", "Yearly Bill"),
                 Rate = c(0.09566, 0.09994, 0.09863, NA),
                 Monthly = c(118.16, 122.44, 121.13, 1446.92),
                 stringsAsFactors = FALSE)

library(shiny)
library(pixiedust)
library(dplyr)
options(pixiedust_print_method = "html")

shinyApp(
  ui = 
    fluidPage(
      uiOutput("table1"),
      uiOutput("table2")
    ),

  server = 
    shinyServer(function(input, output, session){

      output$table1 <-
        renderUI({
          dust(T1) %>% 
            sprinkle(rows = 1, 
                     border = "bottom", 
                     part = "head") %>% 
            sprinkle(rows = c(5, 7),
                     cols = 2:4,
                     border = "top") %>% 
            sprinkle(rows = c(5, 7),
                     bold = TRUE) %>% 
            sprinkle(pad = 4) %>% 
            sprinkle_colnames(Charge = "") %>% 
            print(asis = FALSE) %>% 
            HTML()
        })

      output$table2 <- 
        renderUI({
          T2 %>% 
            mutate(Monthly = paste0("$", trimws(format(Monthly, big.mark = ",")))) %>% 
            dust() %>% 
            sprinkle(rows = 1,
                     border = "bottom",
                     part = "head") %>% 
            sprinkle(rows = 4,
                     cols = 1,
                     bold = TRUE) %>% 
            sprinkle(rows = 4,
                     cols = 3,
                     border = "all") %>% 
            sprinkle(na_string = "", 
                     pad = 4) %>% 
            sprinkle_colnames(Period = "",
                              Monthly = "Monthly Bill") %>% 
            print(asis = FALSE) %>% 
            HTML()

        })

    })
)

Answer 2

This would be easier if you provided an example of your data, but sticking with DT, you should be able to utilize formatStyle to change formatting of both rows and columns. For an example to bold the first row, see the following (assuming your data frame is called df):

df %>%
  datatable() %>%
  formatStyle(
    0,
    target = "row",
    fontWeight = styleEqual(1, "bold")
  )

The rstudio DT page offers more examples: http://rstudio.github.io/DT/010-style.html

Alternatively, I think you might be better off using the stargazer package.

The base plot would look very similar to your desired result.

stargazer::stargazer(df, type = "html", title = "Table 1")

That will get you started, but see here for a LOT more flexibility: https://www.jakeruss.com/cheatsheets/stargazer/