This article describes how to go from a grid table...
...to a customized heatmap:
Requirements
A table with multiple rows and columns.
Method
1. Select your table.
2. Copy the table name from Properties > GENERAL > Name.
3. Select Calculation > Custom Code.
4. Go to Properties > R CODE in the object inspector.
5. Paste the below MyHeatmap function into your output:
MyHeatmap <- function(x)
{
# Lookups for coloring cells and fonts
require(RColorBrewer)
cell.colors = colorRampPalette(brewer.pal(9,"Blues"))(101)
font.colors = c(rep("blue", 60), rep("white", 41))
# Scaling the data to be on [1, 2, ..., 101], for the lookups
min.x <- min(x)
max.x <- max(x)
scaled.x = round((x - min(x)) / (max(x) - min(x)) * 100, 0) + 1
# Writing the cells styles
n.rows = nrow(x)
n.columns = ncol(x)
rows = rep(1:n.rows, rep(n.columns, n.rows))
columns = rep(1:n.columns, n.rows)
x.lookups = as.numeric(t(scaled.x))
cells.styles = paste0('td.cell', rows, columns, ' {background-color: ', cell.colors[x.lookups], '; color: ', font.colors[x.lookups] ,';}')
cell.styles = paste0(cells.styles, collapse = "\n")
# Creating the table
columns.headers = paste0('<th>', c("", dimnames(x)[[2]]) ,'</th>')
tble = paste0(columns.headers, collapse = "\n")
row.headers = paste0('<th>', c(dimnames(x)[[1]]) ,'</th>')
print('row.headers')
print(row.headers)
for (row in 1:n.rows)
{
row.cells = paste0('<td class = "border cell', row, 1:n.columns, '">', x[row,],'</td>')
print('row.cells')
print(row.cells)
tble = paste0(tble, '<tr>', row.headers[row], paste(row.cells, collapse = '\n'), '</tr>')
}
# Assembling the HTML
html = paste0('<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {border-collapse: collapse; }
th, td {padding: 5px; text-align: center; font-family: arial; font-size: 8pt}
td.border {border: 1px solid grey; }',' td.cell {background-color: #F7FBFF; color: blue;}',
cell.styles, '
</style>
</head>
<body>
<table style="width:100%">', tble, '
</table>
</body>
</html>')
rhtmlMetro::Box(text = html, text.as.html = TRUE)
}
6. Add a line at the bottom of the code that uses this function and references the name of the table from step 2:
MyHeatmap(table_name)
Next
How to Create a Table with Conditional Formatting using R and HTML