library(rhandsontable)## Warning: package 'rhandsontable' was built under R version 3.2.5DF = data.frame(integer = 1:10,
numeric = rnorm(10),
logical = rep(TRUE, 10),
character = LETTERS[1:10],
factor = factor(letters[1:10], levels = letters[10:1],
ordered = TRUE),
factor_allow = factor(letters[1:10], levels = letters[10:1],
ordered = TRUE),
date = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, width = 600, height = 300) %>% hot_col("factor_allow", allowInvalid = TRUE)To improve readability, NA values will be displayed as blank cells. This requires converting columns containing NA to characters, and in the case of factors and Dates, may not display the data in the desired format. It may be beneficial to concert these type of columns to character before passing to rhandsontable.
DF_na = data.frame(integer = c(NA, 2:10),
logical = c(NA, rep(TRUE, 9)),
character = c(NA, LETTERS[1:9]),
factor = c(NA, factor(letters[1:9])),
date = c(NA, seq(from = Sys.Date(), by = "days",
length.out = 9)),
stringsAsFactors = FALSE)
DF_na$factor_ch = as.character(DF_na$factor)
DF_na$date_ch = c(NA, as.character(seq(from = Sys.Date(), by = "days",
length.out = 9)))
rhandsontable(DF_na, width = 550, height = 300)To control character column values, the column type can be specified as dropdown or autocomplete.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
# try updating big to a value not in the dropdown
rhandsontable(DF, rowHeaders = NULL, width = 550, height = 300) %>%
hot_col(col = "big", type = "dropdown", source = LETTERS) %>%
hot_col(col = "small", type = "autocomplete", source = letters,
strict = FALSE)A column can also be specified as a password type.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, width = 550, height = 300) %>%
hot_col("small", "password")New in version 0.2, sparkline.js charts can be added to the table. Thanks to the sparkline package and Ramnath Vaidyanathan for inspiration.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
# First, using jsonlite to convert to JSON format
DF$chart = c(sapply(1:5,
function(x) jsonlite::toJSON(list(values=rnorm(10),
options = list(type = "bar")))),
sapply(1:5,
function(x) jsonlite::toJSON(list(values=rnorm(10),
options = list(type = "line")))))
# Second, using the htmlwidget to render
rhandsontable(DF, rowHeaders = NULL, width = 550, height = 300) %>%
hot_col("chart", renderer = htmlwidgets::JS("renderSparkline"))It’s also possible to define a custom column renderer function.
DF = data.frame(
title = c(
"<a href='http://www.amazon.com/Professional-JavaScript-Developers-Nicholas-Zakas/dp/1118026691'>Professional JavaScript for Web Developers</a>",
"<a href='http://shop.oreilly.com/product/9780596517748.do'>JavaScript: The Good Parts</a>",
"<a href='http://shop.oreilly.com/product/9780596805531.do'>JavaScript: The Definitive Guide</a>"
),
desc = c(
"This <a href='http://bit.ly/sM1bDf'>book</a> provides a developer-level introduction along with more advanced and useful features of <b>JavaScript</b>.",
"This book provides a developer-level introduction along with <b>more advanced</b> and useful features of JavaScript.",
"<em>JavaScript: The Definitive Guide</em> provides a thorough description of the core <b>JavaScript</b> language and both the legacy and standard DOMs implemented in web browsers."
),
comments = c(
"I would rate it ★★★★☆",
"This is the book about JavaScript",
"I've never actually read it, but the <a href='http://shop.oreilly.com/product/9780596805531.do'>comments</a> are highly <strong>positive</strong>."
),
cover = c(
"http://ecx.images-amazon.com/images/I/51bRhyVTVGL._SL50_.jpg",
"http://ecx.images-amazon.com/images/I/51gdVAEfPUL._SL50_.jpg",
"http://ecx.images-amazon.com/images/I/51VFNL4T7kL._SL50_.jpg"
),
stringsAsFactors = FALSE
)
rhandsontable(DF, allowedTags = "<em><b><strong><a><big>",
width = 800, height = 450, rowHeaders = FALSE) %>%
hot_cols(colWidths = c(200, 200, 200, 80)) %>%
hot_col(1:2, renderer = "html") %>%
hot_col(1:3, renderer = htmlwidgets::JS("safeHtmlRenderer")) %>%
hot_col(4, renderer = "
function(instance, td, row, col, prop, value, cellProperties) {
var escaped = Handsontable.helper.stringify(value),
img;
if (escaped.indexOf('http') === 0) {
img = document.createElement('IMG');
img.src = value;
Handsontable.Dom.addEvent(img, 'mousedown', function (e){
e.preventDefault(); // prevent selection quirk
});
Handsontable.Dom.empty(td);
td.appendChild(img);
}
else {
// render as text
Handsontable.renderers.TextRenderer.apply(this, arguments);
}
return td;
}")For shiny apps, use renderer = htmlwidgets::JS(“safeHtmlRenderer”) to display columns with html data.
Numeric columns are formatted using the numeral.js library.
DF = data.frame(int = 1:10, float = rnorm(10), cur = rnorm(10) * 1E5,
lrg = rnorm(10) * 1E8, pct = rnorm(10))
rhandsontable(DF, width = 550, height = 300) %>%
hot_col("float", format = "0.0") %>%
hot_col("cur", format = "$0,0.00") %>%
hot_col("lrg", format = "0a") %>%
hot_col("pct", format = "0%")The whole table and individual columns can to set to readOnly to prevent the user from making changes.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, readOnly = TRUE, width = 550, height = 300) %>%
hot_col("val", readOnly = FALSE)Column sorting can be enabled; sorting only impacts the widget and will not reorder the original data set.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, width = 550, height = 300) %>%
hot_cols(columnSorting = TRUE)With larger tables it my be desirable to highlight the row and column for a selected cell.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
# click on a cell to see the highlighting
rhandsontable(DF, width = 550, height = 300) %>%
hot_table(highlightCol = TRUE, highlightRow = TRUE)Column and row dimensions can be customized. For larger data sets, (multiple) top rows and left columns can be frozen.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:5]))
rhandsontable(MAT, width = 600, height = 600) %>%
hot_cols(colWidths = 100) %>%
hot_rows(rowHeights = 50)For larger data sets, (multiple) top rows and left columns can be frozen.
MAT = matrix(rnorm(26 * 26), nrow = 26, dimnames = list(LETTERS, letters))
# scroll through the table to see the fixed row and column
rhandsontable(MAT, width = 550, height = 300) %>%
hot_cols(fixedColumnsLeft = 1) %>%
hot_rows(fixedRowsTop = 1)Comments (hover) can also be added to individual cells and will appear as red flags in the upper right of the cell.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, width = 550, height = 300) %>%
hot_cell(1, 1, "Test comment")Additionally, comments can be added via data.frame or matrix.
MAT_comments = matrix(ncol = ncol(DF), nrow = nrow(DF))
MAT_comments[1, 1] = "Test comment"
MAT_comments[2, 2] = "Another test comment"
rhandsontable(DF, comments = MAT_comments, width = 550, height = 300)Custom borders can be drawn around cells to highlight specific items.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:5]))
rhandsontable(MAT, width = 550, height = 300) %>%
hot_table(customBorders = list(list(
range = list(from = list(row = 1, col = 1),
to = list(row = 2, col = 2)),
top = list(width = 2, color = "red"),
left = list(width = 2, color = "red"),
bottom = list(width = 2, color = "red"),
right = list(width = 2, color = "red"))))Pre-defined validation can be added for numeric columns in two ways:
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:5]))
rhandsontable(MAT * 10, width = 550, height = 300) %>%
hot_validate_numeric(col = 1, min = -50, max = 50, exclude = 40)For character columns, a vector of allowed options can be specified. A more user-friendly approach may be to use a dropdown column with strict = TRUE.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10],
small = letters[1:10],
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, width = 550, height = 300) %>%
hot_validate_character(col = "big", choices = LETTERS[1:10])Conditional formatting can also be specified via custom JavaScript function. Future enhancements will look to simplify this interface.
MAT = matrix(runif(100, -1, 1), nrow = 10,
dimnames = list(LETTERS[1:10], LETTERS[1:10]))
diag(MAT) = 1
MAT[upper.tri(MAT)] = MAT[lower.tri(MAT)]
rhandsontable(MAT, readOnly = TRUE, width = 750, height = 300) %>%
hot_cols(renderer = "
function (instance, td, row, col, prop, value, cellProperties) {
Handsontable.renderers.TextRenderer.apply(this, arguments);
if (row == col) {
td.style.background = 'lightgrey';
} else if (col > row) {
td.style.background = 'grey';
td.style.color = 'grey';
} else if (value < -0.75) {
td.style.background = 'pink';
} else if (value > 0.75) {
td.style.background = 'lightgreen';
}
}")The chroma.js library can be used to turn the table into a heatmap.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:5]))
rhandsontable(MAT, width = 550, height = 300) %>%
hot_heatmap()The data grid will be editable by default and can be used as input to a shiny app. A few shiny and shinydashboard example links are listed below. Note that the shinyapps.io links may not work if the has hit the monthly usage limit.
When using rhandsontable and an input, these apps generally use the hot_to_r function to convert data to an R data.frame and shiny::reactiveValues to store a cached version of the data (see the Data file editor example below). The rhandsontable function also includes the selectCallback parameter, which will fire a callback event when a table cell is selected (see the Table callback linked to chart example below).