HTML tables

data <- data.table(datasets::iris)[1:40]
setcolorder(data, "Species")
data[]
#>     Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#>  1:  setosa          5.1         3.5          1.4         0.2
#>  2:  setosa          4.9         3.0          1.4         0.2
#>  3:  setosa          4.7         3.2          1.3         0.2
#>  4:  setosa          4.6         3.1          1.5         0.2
#>  5:  setosa          5.0         3.6          1.4         0.2
#>  6:  setosa          5.4         3.9          1.7         0.4
#>  7:  setosa          4.6         3.4          1.4         0.3
#>  8:  setosa          5.0         3.4          1.5         0.2
#>  9:  setosa          4.4         2.9          1.4         0.2
#> 10:  setosa          4.9         3.1          1.5         0.1
#> 11:  setosa          5.4         3.7          1.5         0.2
#> 12:  setosa          4.8         3.4          1.6         0.2
#> 13:  setosa          4.8         3.0          1.4         0.1
#> 14:  setosa          4.3         3.0          1.1         0.1
#> 15:  setosa          5.8         4.0          1.2         0.2
#> 16:  setosa          5.7         4.4          1.5         0.4
#> 17:  setosa          5.4         3.9          1.3         0.4
#> 18:  setosa          5.1         3.5          1.4         0.3
#> 19:  setosa          5.7         3.8          1.7         0.3
#> 20:  setosa          5.1         3.8          1.5         0.3
#> 21:  setosa          5.4         3.4          1.7         0.2
#> 22:  setosa          5.1         3.7          1.5         0.4
#> 23:  setosa          4.6         3.6          1.0         0.2
#> 24:  setosa          5.1         3.3          1.7         0.5
#> 25:  setosa          4.8         3.4          1.9         0.2
#> 26:  setosa          5.0         3.0          1.6         0.2
#> 27:  setosa          5.0         3.4          1.6         0.4
#> 28:  setosa          5.2         3.5          1.5         0.2
#> 29:  setosa          5.2         3.4          1.4         0.2
#> 30:  setosa          4.7         3.2          1.6         0.2
#> 31:  setosa          4.8         3.1          1.6         0.2
#> 32:  setosa          5.4         3.4          1.5         0.4
#> 33:  setosa          5.2         4.1          1.5         0.1
#> 34:  setosa          5.5         4.2          1.4         0.2
#> 35:  setosa          4.9         3.1          1.5         0.2
#> 36:  setosa          5.0         3.2          1.2         0.2
#> 37:  setosa          5.5         3.5          1.3         0.2
#> 38:  setosa          4.9         3.6          1.4         0.1
#> 39:  setosa          4.4         3.0          1.3         0.2
#> 40:  setosa          5.1         3.4          1.5         0.2
#>     Species Sepal.Length Sepal.Width Petal.Length Petal.Width

# create formatted data and value data
d_value <- copy(data)
d_formatted <- copy(data)

# we work with d_value to identify which cells should be colored
# we save the colors in a matrix called "css_style_matrix"
css_style_matrix <- fhiplot::htmltable_css_style_matrix(d_value)

# set some of the third column to have a red background
rows <- which(d_value[[3]] > 4)
if(length(rows) > 0) css_style_matrix[rows,3] <- fhiplot::htmltable_css_background_red

# set some of the fourth column to have a blue background according to risk level
# risk level 1
rows <- 1:nrow(d_value)
if(length(rows) > 0) css_style_matrix[rows,4] <- fhiplot::htmltable_css_risk_1_5(1, palette = "blue")

# risk level 3
rows <- which(d_value[[4]] > 1.3)
if(length(rows) > 0) css_style_matrix[rows,4] <- fhiplot::htmltable_css_risk_1_5(3, palette = "blue")

# risk level 5
rows <- which(d_value[[4]] > 1.5)
if(length(rows) > 0) css_style_matrix[rows,4] <- fhiplot::htmltable_css_risk_1_5(5, palette = "blue")

# set some of the fifth column to have a red background according to risk level
# risk level 1
rows <- 1:nrow(d_value)
if(length(rows) > 0) css_style_matrix[rows,5] <- fhiplot::htmltable_css_risk_1_5(1, palette = "red")

# risk level 2
rows <- which(d_value[[5]] > 0.1)
if(length(rows) > 0) css_style_matrix[rows,5] <- fhiplot::htmltable_css_risk_1_5(2, palette = "red")

# risk level 3
rows <- which(d_value[[5]] > 0.2)
if(length(rows) > 0) css_style_matrix[rows,5] <- fhiplot::htmltable_css_risk_1_5(3, palette = "red")


# risk level 4
rows <- which(d_value[[5]] > 0.3)
if(length(rows) > 0) css_style_matrix[rows,5] <- fhiplot::htmltable_css_risk_1_5(4, palette = "red")

# risk level 5
rows <- which(d_value[[5]] > 0.4)
if(length(rows) > 0) css_style_matrix[rows,5] <- fhiplot::htmltable_css_risk_1_5(5, palette = "red")


# format the data nicely inside d_formatted
# set columns 2-3 as numeric with 0 decimal places
d_formatted[, Sepal.Length := fhiplot::format_nor_num_0(Sepal.Length)]
d_formatted[, Sepal.Width := fhiplot::format_nor_num_0(Sepal.Width)]

# set columns 4 as numeric with 1 decimal places
d_formatted[, Petal.Length := fhiplot::format_nor_num_1(Petal.Length)]

# set columns 5 as percentage with 1 decimal places
d_formatted[, Petal.Width := fhiplot::format_nor_perc_1(100*Petal.Width)]

setnames(
  d_formatted,
  c("","Sepal length", "Sepal width", "Petal length", "Petal width (%)")
)

# now combine d_formatted (contains the text in each cell)
# and the css_style_matrix (contains the colors of each cell)
tab <- fhiplot::htmltable_quick_style(d_formatted, css_style_matrix = css_style_matrix) %>%
  htmlTable::htmlTable(
    rnames = FALSE,
    align = "|l|c|c|c|c|",
    align.header = "|l|c|c|c|c|",
    align.cgroup = "|l|c|c|c|c|",
    spacer.celltype = "skip",
    caption = "This is a caption"
  )

# you can use this function to interactively test out your table
# fhiplot::view_html(tab)
cat(tab)
Sepal length Sepal width Petal length Petal width (%)
setosa 5 4 1,4 20,0 %
setosa 5 3 1,4 20,0 %
setosa 5 3 1,3 20,0 %
setosa 5 3 1,5 20,0 %
setosa 5 4 1,4 20,0 %
setosa 5 4 1,7 40,0 %
setosa 5 3 1,4 30,0 %
setosa 5 3 1,5 20,0 %
setosa 4 3 1,4 20,0 %
setosa 5 3 1,5 10,0 %
setosa 5 4 1,5 20,0 %
setosa 5 3 1,6 20,0 %
setosa 5 3 1,4 10,0 %
setosa 4 3 1,1 10,0 %
setosa 6 4 1,2 20,0 %
setosa 6 4 1,5 40,0 %
setosa 5 4 1,3 40,0 %
setosa 5 4 1,4 30,0 %
setosa 6 4 1,7 30,0 %
setosa 5 4 1,5 30,0 %
setosa 5 3 1,7 20,0 %
setosa 5 4 1,5 40,0 %
setosa 5 4 1,0 20,0 %
setosa 5 3 1,7 50,0 %
setosa 5 3 1,9 20,0 %
setosa 5 3 1,6 20,0 %
setosa 5 3 1,6 40,0 %
setosa 5 4 1,5 20,0 %
setosa 5 3 1,4 20,0 %
setosa 5 3 1,6 20,0 %
setosa 5 3 1,6 20,0 %
setosa 5 3 1,5 40,0 %
setosa 5 4 1,5 10,0 %
setosa 6 4 1,4 20,0 %
setosa 5 3 1,5 20,0 %
setosa 5 3 1,2 20,0 %
setosa 6 4 1,3 20,0 %
setosa 5 4 1,4 10,0 %
setosa 4 3 1,3 20,0 %
setosa 5 3 1,5 20,0 %
This is a caption

Excel tables

data <- data.table(datasets::iris)[1:40]
setcolorder(data, "Species")
data[]
#>     Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#>  1:  setosa          5.1         3.5          1.4         0.2
#>  2:  setosa          4.9         3.0          1.4         0.2
#>  3:  setosa          4.7         3.2          1.3         0.2
#>  4:  setosa          4.6         3.1          1.5         0.2
#>  5:  setosa          5.0         3.6          1.4         0.2
#>  6:  setosa          5.4         3.9          1.7         0.4
#>  7:  setosa          4.6         3.4          1.4         0.3
#>  8:  setosa          5.0         3.4          1.5         0.2
#>  9:  setosa          4.4         2.9          1.4         0.2
#> 10:  setosa          4.9         3.1          1.5         0.1
#> 11:  setosa          5.4         3.7          1.5         0.2
#> 12:  setosa          4.8         3.4          1.6         0.2
#> 13:  setosa          4.8         3.0          1.4         0.1
#> 14:  setosa          4.3         3.0          1.1         0.1
#> 15:  setosa          5.8         4.0          1.2         0.2
#> 16:  setosa          5.7         4.4          1.5         0.4
#> 17:  setosa          5.4         3.9          1.3         0.4
#> 18:  setosa          5.1         3.5          1.4         0.3
#> 19:  setosa          5.7         3.8          1.7         0.3
#> 20:  setosa          5.1         3.8          1.5         0.3
#> 21:  setosa          5.4         3.4          1.7         0.2
#> 22:  setosa          5.1         3.7          1.5         0.4
#> 23:  setosa          4.6         3.6          1.0         0.2
#> 24:  setosa          5.1         3.3          1.7         0.5
#> 25:  setosa          4.8         3.4          1.9         0.2
#> 26:  setosa          5.0         3.0          1.6         0.2
#> 27:  setosa          5.0         3.4          1.6         0.4
#> 28:  setosa          5.2         3.5          1.5         0.2
#> 29:  setosa          5.2         3.4          1.4         0.2
#> 30:  setosa          4.7         3.2          1.6         0.2
#> 31:  setosa          4.8         3.1          1.6         0.2
#> 32:  setosa          5.4         3.4          1.5         0.4
#> 33:  setosa          5.2         4.1          1.5         0.1
#> 34:  setosa          5.5         4.2          1.4         0.2
#> 35:  setosa          4.9         3.1          1.5         0.2
#> 36:  setosa          5.0         3.2          1.2         0.2
#> 37:  setosa          5.5         3.5          1.3         0.2
#> 38:  setosa          4.9         3.6          1.4         0.1
#> 39:  setosa          4.4         3.0          1.3         0.2
#> 40:  setosa          5.1         3.4          1.5         0.2
#>     Species Sepal.Length Sepal.Width Petal.Length Petal.Width

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, "data")

# adding custom titles
openxlsx::writeData(
  wb,
  "data",
  x = matrix(ncol=4, nrow=0, dimnames=list(NULL,c("Sepal length", "Sepal width", "Petal length", "Petal width (%)"))),
  startCol = 2,
  startRow = 1
)

# write data
openxlsx::writeData(
  wb,
  sheet = "data",
  x = data,
  startCol = 1,
  startRow = 2,
  colNames = FALSE
)

# add styles
# title style
openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_title,
  rows = 1,
  cols = 1:10,
  gridExpand = T,
  stack = T
)

# set columns 2-3 as numeric with 0 decimal places
openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_num_0,
  rows = 2:100,
  cols = 2:3,
  gridExpand = T,
  stack = T
)

# set columns 4 as numeric with 1 decimal places
openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_num_1,
  rows = 2:100,
  cols = 4,
  gridExpand = T,
  stack = T
)

# set columns 5 as percentage with 1 decimal places
openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_prop_to_perc_1,
  rows = 2:100,
  cols = 5,
  gridExpand = T,
  stack = T
)

# set some of the third column to have a red background
# you may also use the function fhiplot::excel_style_background_yellow
# these are just shortcuts for
# fhiplot::excel_style_risk_1_5(3, palette = "red")
# fhiplot::excel_style_risk_1_5(5, palette = "red")
rows <- 1 + which(data[[3]] > 4)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_background_red,
  rows = rows,
  cols = 3,
  gridExpand = T,
  stack = T
)

# set some of the fourth column to have a blue background according to risk level
# risk level 1
rows <- 1 + 1:ncol(data)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(1, palette = "blue"),
  rows = rows,
  cols = 4,
  gridExpand = T,
  stack = T
)

# risk level 3
rows <- 1 + which(data[[4]] > 1.3)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(3, palette = "blue"),
  rows = rows,
  cols = 4,
  gridExpand = T,
  stack = T
)

# risk level 5
rows <- 1 + which(data[[4]] > 1.5)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(5, palette = "blue"),
  rows = rows,
  cols = 4,
  gridExpand = T,
  stack = T
)

# set some of the fifth column to have a red background according to risk level
# risk level 1
rows <- 1 + 1:nrow(data)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(1, palette = "red"),
  rows = rows,
  cols = 5,
  gridExpand = T,
  stack = T
)

# risk level 2
rows <- 1 + which(data[[5]] > 0.1)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(2, palette = "red"),
  rows = rows,
  cols = 5,
  gridExpand = T,
  stack = T
)

# risk level 3
rows <- 1 + which(data[[5]] > 0.2)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(3, palette = "red"),
  rows = rows,
  cols = 5,
  gridExpand = T,
  stack = T
)

# risk level 4
rows <- 1 + which(data[[5]] > 0.3)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(4, palette = "red"),
  rows = rows,
  cols = 5,
  gridExpand = T,
  stack = T
)

# risk level 5
rows <- 1 + which(data[[5]] > 0.4)
if(length(rows) > 0) openxlsx::addStyle(
  wb,
  sheet = "data",
  style = fhiplot::excel_style_risk_1_5(5, palette = "red"),
  rows = rows,
  cols = 5,
  gridExpand = T,
  stack = T
)

# freeze first column and row
openxlsx::freezePane(wb, "data", firstActiveRow=2, firstActiveCol=2)

# set widths, and hide the second column
openxlsx::setColWidths(wb, "data", cols=c(1:5), width="auto", hidden=c(F,T,F,F,F))

# save
# openxlsx::saveWorkbook(wb, "test.xlsx", overwrite = TRUE)