Nothing
rhandsontable Introduction"
In rhandsontable: Interface to the 'Handsontable.js' Library
library(rhandsontable)
library(knitr)
opts_knit$set(warning = FALSE, error = FALSE, message = FALSE, cache = FALSE,
fig.width=7, fig.height=3)
Introduction
rhandsontable is a htmlwidget based on the handsontable.js library.
Handsontable is a data grid component with an Excel-like appearance. Built in JavaScript, it integrates with any data source with peak efficiency. It comes with powerful features like data validation, sorting, grouping, data binding, formula support or column ordering.
(via)
Column Types
The table includes support for numeric, logical, character and Date types. Logical values will appear as check boxes, and the pikaday.js library is used to specify Date values.
rhandsontable attempts to map R classes to an appropriate handsontable type. Factors will be mapped to dropdown, with the choices specified by level and allowInvalid set to FALSE. To allow new levels, set allowInvalid to TRUE (using hot_col; it may also be desirable to set strict to FALSE). When running in shiny, using hot_to_r will preserve custom factor ordering, and if new levels are allowed, they will be added to the end.
DF = 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 convert 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)
Dropdown / Autocomplete
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)
Password
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")
Sparkline
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)
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")))))
rhandsontable(DF, rowHeaders = NULL, width = 550, height = 300) %>%
hot_col("chart", renderer = htmlwidgets::JS("renderSparkline"))
Custom Renderer
It's also possible to define a custom column renderer function. For example, it may be desirable to include html in a cell. The example below mimics Custom renderers.
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. The allowed html tags default to <em><b><strong><a><big>, but the (hidden) allowedTags parameter can in rhandsontable can be used to customize this list.
Custom Renderer using an R Parameter {#custom_renderer_using_r}
Additional parameters passed to rhandsontable will be available to the JavaScript widget via the params property.
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)
col_highlight = 2
row_highlight = c(5, 7)
rhandsontable(DF, col_highlight = col_highlight,
row_highlight = row_highlight,
width = 550, height = 300) %>%
hot_cols(renderer = "
function(instance, td, row, col, prop, value, cellProperties) {
Handsontable.renderers.TextRenderer.apply(this, arguments);
tbl = this.HTMLWidgets.widgets[0]
hcols = tbl.params.col_highlight
hcols = hcols instanceof Array ? hcols : [hcols]
hrows = tbl.params.row_highlight
hrows = hrows instanceof Array ? hrows : [hrows]
if (hcols.includes(col) && hrows.includes(row)) {
td.style.background = 'red';
}
else if (hcols.includes(col)) {
td.style.background = 'lightgreen';
}
else if (hrows.includes(row)) {
td.style.background = 'pink';
}
return td;
}")
When using this approach in a shiny app or in a document with more than one widget, the widget search logic will need to be more robust.
HTMLWidgets.widgets.filter(function(widget) {
// this should match the table id specified in the shiny app
return widget.name === "hot"
})[0];
Right-Click Menu
Right-clicking in a cell will enable a context menu that includes customizable table actions via the hot_context_menu function. For shiny apps, formatting and comment updates made via the context menu are not currently retained.
To disable the context menu, set contextMenu = FALSE in hot_table (or rhandsontable).
Add / Remove Rows & Columns
By default a user can add or remove table rows and columns, but this functionality can be disabled. Note that Handsontable does not allow column be added or deleted to the table if column types are defined (i.e. useTypes == TRUE in rhandsontable).
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_context_menu(allowRowEdit = FALSE, allowColEdit = FALSE)
Customizing
The customOpts parameter of hot_context_menu can be used to add custom functionality to the context menu. Below are a couple examples.
Export to CSV
This example illustrates how to add an option to export the table to a csv file.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:5]))
rhandsontable(MAT, width = 550, height = 300) %>%
hot_context_menu(
customOpts = list(
csv = list(name = "Download to CSV",
callback = htmlwidgets::JS(
"function (key, options) {
var csv = csvString(this);
var link = document.createElement('a');
link.setAttribute('href', 'data:text/plain;charset=utf-8,' +
encodeURIComponent(csv));
link.setAttribute('download', 'data.csv');
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}"))))
Search
This example illustrates how to enable the search functionality in Handsontable.
DF = data.frame(val = 1:10,
bool = TRUE,
big = LETTERS[1:10],
small = factor(letters[1:10]),
dt = seq(from = Sys.Date(), by = "days", length.out = 10),
stringsAsFactors = FALSE)
rhandsontable(DF, search = TRUE, width = 550, height = 300) %>%
hot_context_menu(
customOpts = list(
search = list(name = "Search",
callback = htmlwidgets::JS(
"function (key, options) {
var srch = prompt('Search criteria');
this.search.query(srch);
this.render();
}"))))
Future enhancements will look to expand export options.
Numeric Formatting
Numeric columns are formatted using the numbro.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%")
Specify Locale
The language parameter for hot_col can be used to change the locale. See the numeral.js library for language options.
DF = data.frame(dollar = rnorm(10), euro = rnorm(10), yen = rnorm(10))
rhandsontable(DF * 1000, width = 550, height = 300) %>%
hot_col("dollar", format = "$0,000.00", language = "en-US") %>%
hot_col("euro", format = "0,000.00 $", language = "de-DE") %>%
hot_col("yen", format = "$0,000.00", language = "ja-JP")
Read Only
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)
Sorting
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)
Highlight Rows & Columns
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)
See Custom Renderer using an R Parameter for a static highlighting example.
Sizing
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)
Row Header Width
The width of the row header column can be customized using rowHeaderWidth.
rhandsontable(mtcars, rowHeaderWidth = 200)
Streching
The table can be streched to the full width by using stretchH.
MAT = matrix(rnorm(30), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:3]))
rhandsontable(MAT, width = 600, height = 300, stretchH = "all")
Fixed Rows / Columns
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)
Cell Comments
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)
Finally, comments can also be added via the right-click context menu, but these updates will not currently be retained by shiny.
Borders
Custom borders can be drawn around cells to highlight specific items. Borders can also be added via the right-click context menu, but these updates will not currently be retained by shiny.
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"))))
Validation
Numeric Columns
Pre-defined validation can be added for numeric columns in two ways:
- specify a min and max and any values within the range to exclude
- similar to a
dropdown column, specify allowed values
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)
rhandsontable(MAT * 10, width = 550, height = 300) %>%
hot_validate_numeric(col = 1, choices = c(10, 20, 40))
Character Columns
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])
Custom
It is also possible to create a custom validation function in JavaScript.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10],
letters[1:5]))
# try to update any cell to 0
rhandsontable(MAT * 10, width = 550, height = 300) %>%
hot_cols(validator = "
function (value, callback) {
setTimeout(function(){
callback(value != 0);
}, 1000)
}",
allowInvalid = FALSE)
Conditional Formatting
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';
}
}")
See Custom Renderer using an R Parameter for another example.
Heatmap
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()
Big Data
MAT = matrix(rnorm(10000 * 100), nrow = 100, dimnames= list(1:100, 1:10000))
rhandsontable(MAT, width = 550, height = 550)
Shiny
Important note on shiny use: The htmlwidgets package creates widgets as shiny output bindings. The rhandsontable package also attempts to expose the table as a pseudo shiny input binding using handsontable change events (see here for the supported events). This means the table (e.g. hot) can be accessed in shiny using either input$hot or output$hot, but these values may not be in-sync. The timing of updates will depend on the particular reactive path followed by your shiny application.
Since the widget is not currently able to use the standard shiny input binding functionality, you will need to explicitly call the hot_to_r function to convert the handsontable data to an R object.
Two additional inputs are also enabled, input$hot_select and input$hot_comment, which will fire when a cell selection or a comment changes, respectively (if you would like to see more options, please post an issue or create a PR).
This functionality is still evolving, so please don't hesitate to share suggestions and PRs.
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.
- Output only
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_output")
- Date file editor
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_datafile")
- Calculation input
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_portfolio")
- Table callback linked to chart
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_corr")
- Multiple input tables
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_frontier")
- A shinydashboard app
shiny::runGitHub("rhandsontable", "jrowen",
subdir="inst/examples/rhandsontable_dash")
Bookmarks
Version 0.14 of shiny includes new bookmarking functionality. This willl work for rhandsontable with some special handling. See this issue for more details.
Suggestions & Contributions
Please file a issue if you experience any problems with the widget or have feature requests. Pull requests are also welcome.
Try the rhandsontable package in your browser
Any scripts or data that you put into this service are public.
rhandsontable documentation built on May 27, 2021, 5:07 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
library(rhandsontable) library(knitr) opts_knit$set(warning = FALSE, error = FALSE, message = FALSE, cache = FALSE, fig.width=7, fig.height=3)
Introduction
rhandsontable is a htmlwidget based on the handsontable.js library.
Handsontable is a data grid component with an Excel-like appearance. Built in JavaScript, it integrates with any data source with peak efficiency. It comes with powerful features like data validation, sorting, grouping, data binding, formula support or column ordering. (via)
Column Types
The table includes support for numeric, logical, character and Date types. Logical values will appear as check boxes, and the pikaday.js library is used to specify Date values.
rhandsontable attempts to map R classes to an appropriate handsontable type. Factors will be mapped to dropdown, with the choices specified by level and allowInvalid set to FALSE. To allow new levels, set allowInvalid to TRUE (using hot_col; it may also be desirable to set strict to FALSE). When running in shiny, using hot_to_r will preserve custom factor ordering, and if new levels are allowed, they will be added to the end.
DF = 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 convert 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)
Dropdown / Autocomplete
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)
Password
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")
Sparkline
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) 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"))))) rhandsontable(DF, rowHeaders = NULL, width = 550, height = 300) %>% hot_col("chart", renderer = htmlwidgets::JS("renderSparkline"))
Custom Renderer
It's also possible to define a custom column renderer function. For example, it may be desirable to include html in a cell. The example below mimics Custom renderers.
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. The allowed html tags default to <em><b><strong><a><big>, but the (hidden) allowedTags parameter can in rhandsontable can be used to customize this list.
Custom Renderer using an R Parameter {#custom_renderer_using_r}
Additional parameters passed to rhandsontable will be available to the JavaScript widget via the params property.
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) col_highlight = 2 row_highlight = c(5, 7) rhandsontable(DF, col_highlight = col_highlight, row_highlight = row_highlight, width = 550, height = 300) %>% hot_cols(renderer = " function(instance, td, row, col, prop, value, cellProperties) { Handsontable.renderers.TextRenderer.apply(this, arguments); tbl = this.HTMLWidgets.widgets[0] hcols = tbl.params.col_highlight hcols = hcols instanceof Array ? hcols : [hcols] hrows = tbl.params.row_highlight hrows = hrows instanceof Array ? hrows : [hrows] if (hcols.includes(col) && hrows.includes(row)) { td.style.background = 'red'; } else if (hcols.includes(col)) { td.style.background = 'lightgreen'; } else if (hrows.includes(row)) { td.style.background = 'pink'; } return td; }")
When using this approach in a shiny app or in a document with more than one widget, the widget search logic will need to be more robust.
HTMLWidgets.widgets.filter(function(widget) {
// this should match the table id specified in the shiny app
return widget.name === "hot"
})[0];
Right-Click Menu
Right-clicking in a cell will enable a context menu that includes customizable table actions via the hot_context_menu function. For shiny apps, formatting and comment updates made via the context menu are not currently retained.
To disable the context menu, set contextMenu = FALSE in hot_table (or rhandsontable).
Add / Remove Rows & Columns
By default a user can add or remove table rows and columns, but this functionality can be disabled. Note that Handsontable does not allow column be added or deleted to the table if column types are defined (i.e. useTypes == TRUE in rhandsontable).
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_context_menu(allowRowEdit = FALSE, allowColEdit = FALSE)
Customizing
The customOpts parameter of hot_context_menu can be used to add custom functionality to the context menu. Below are a couple examples.
Export to CSV
This example illustrates how to add an option to export the table to a csv file.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10], letters[1:5])) rhandsontable(MAT, width = 550, height = 300) %>% hot_context_menu( customOpts = list( csv = list(name = "Download to CSV", callback = htmlwidgets::JS( "function (key, options) { var csv = csvString(this); var link = document.createElement('a'); link.setAttribute('href', 'data:text/plain;charset=utf-8,' + encodeURIComponent(csv)); link.setAttribute('download', 'data.csv'); document.body.appendChild(link); link.click(); document.body.removeChild(link); }"))))
Search
This example illustrates how to enable the search functionality in Handsontable.
DF = data.frame(val = 1:10, bool = TRUE, big = LETTERS[1:10], small = factor(letters[1:10]), dt = seq(from = Sys.Date(), by = "days", length.out = 10), stringsAsFactors = FALSE) rhandsontable(DF, search = TRUE, width = 550, height = 300) %>% hot_context_menu( customOpts = list( search = list(name = "Search", callback = htmlwidgets::JS( "function (key, options) { var srch = prompt('Search criteria'); this.search.query(srch); this.render(); }"))))
Future enhancements will look to expand export options.
Numeric Formatting
Numeric columns are formatted using the numbro.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%")
Specify Locale
The language parameter for hot_col can be used to change the locale. See the numeral.js library for language options.
DF = data.frame(dollar = rnorm(10), euro = rnorm(10), yen = rnorm(10)) rhandsontable(DF * 1000, width = 550, height = 300) %>% hot_col("dollar", format = "$0,000.00", language = "en-US") %>% hot_col("euro", format = "0,000.00 $", language = "de-DE") %>% hot_col("yen", format = "$0,000.00", language = "ja-JP")
Read Only
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)
Sorting
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)
Highlight Rows & Columns
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)
See Custom Renderer using an R Parameter for a static highlighting example.
Sizing
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)
Row Header Width
The width of the row header column can be customized using rowHeaderWidth.
rhandsontable(mtcars, rowHeaderWidth = 200)
Streching
The table can be streched to the full width by using stretchH.
MAT = matrix(rnorm(30), nrow = 10, dimnames = list(LETTERS[1:10], letters[1:3])) rhandsontable(MAT, width = 600, height = 300, stretchH = "all")
Fixed Rows / Columns
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)
Cell Comments
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)
Finally, comments can also be added via the right-click context menu, but these updates will not currently be retained by shiny.
Borders
Custom borders can be drawn around cells to highlight specific items. Borders can also be added via the right-click context menu, but these updates will not currently be retained by shiny.
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"))))
Validation
Numeric Columns
Pre-defined validation can be added for numeric columns in two ways:
- specify a min and max and any values within the range to exclude
- similar to a
dropdowncolumn, specify allowed values
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) rhandsontable(MAT * 10, width = 550, height = 300) %>% hot_validate_numeric(col = 1, choices = c(10, 20, 40))
Character Columns
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])
Custom
It is also possible to create a custom validation function in JavaScript.
MAT = matrix(rnorm(50), nrow = 10, dimnames = list(LETTERS[1:10], letters[1:5])) # try to update any cell to 0 rhandsontable(MAT * 10, width = 550, height = 300) %>% hot_cols(validator = " function (value, callback) { setTimeout(function(){ callback(value != 0); }, 1000) }", allowInvalid = FALSE)
Conditional Formatting
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'; } }")
See Custom Renderer using an R Parameter for another example.
Heatmap
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()
Big Data
MAT = matrix(rnorm(10000 * 100), nrow = 100, dimnames= list(1:100, 1:10000)) rhandsontable(MAT, width = 550, height = 550)
Shiny
Important note on shiny use: The htmlwidgets package creates widgets as shiny output bindings. The rhandsontable package also attempts to expose the table as a pseudo shiny input binding using handsontable change events (see here for the supported events). This means the table (e.g. hot) can be accessed in shiny using either input$hot or output$hot, but these values may not be in-sync. The timing of updates will depend on the particular reactive path followed by your shiny application.
Since the widget is not currently able to use the standard shiny input binding functionality, you will need to explicitly call the hot_to_r function to convert the handsontable data to an R object.
Two additional inputs are also enabled, input$hot_select and input$hot_comment, which will fire when a cell selection or a comment changes, respectively (if you would like to see more options, please post an issue or create a PR).
This functionality is still evolving, so please don't hesitate to share suggestions and PRs.
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.
- Output only
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_output")
- Date file editor
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_datafile")
- Calculation input
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_portfolio")
- Table callback linked to chart
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_corr")
- Multiple input tables
shiny::runGitHub("rhandsontable", "jrowen",
subdir = "inst/examples/rhandsontable_frontier")
- A shinydashboard app
shiny::runGitHub("rhandsontable", "jrowen",
subdir="inst/examples/rhandsontable_dash")
Bookmarks
Version 0.14 of shiny includes new bookmarking functionality. This willl work for rhandsontable with some special handling. See this issue for more details.
Suggestions & Contributions
Please file a issue if you experience any problems with the widget or have feature requests. Pull requests are also welcome.
Try the rhandsontable package in your browser
Any scripts or data that you put into this service are public.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.