If there’s a component that’s least interactive in an RMarkdown rendered document or a Shiny app - that’d be the Table that’s displayed. Yes,
datatable does a good job of improving the Table Apperance and custom formatting like Conditional Formatting, it doesn’t give a Google Spreadsheet or Microsoft Excel flavor. This post is to let you know how you can embed that excel-like spreadsheet Table in your Rmarkdown.
excelR - Intro
The package that’s going to help in this endeavor is
excelR by Swechhya Bista. This is an R interface to ‘jExcel’ library to create web-based interactive tables and spreadsheets compatible with ‘Excel’ or any other spreadsheet software.
excelR - Installation
The stable version of
excelR can be installed from CRAN:
or, the latest development version from Github:
excelR - Loading and Basic Example
excelR helps you create spreadsheet tables that support Basic Excel Forumulas.
In the table below, 1st Column 6th Row (A6) if you enter
=SUM(A1:A5) you’d get the total sum of all the 5 cells above.
library(excelR) df = head(iris) excelTable(df, minDimensions = c(ncol(df),nrow(df)+1))
## Warning in excelTable(df, minDimensions = c(ncol(df), nrow(df) + 1)): Since ## both column title and colHeaders are not specified 'data' column name will ## be used as column headers
In this example, We’d see how to create the table with a column that offers Dropdowns. This is primarily done with the paramater
source while building the
library(excelR) data = data.frame( Country = c('India', 'India', 'US','US'), City = c('Bangalore', 'Mumbai', 'NY', 'SA')) columns = data.frame(title=c('Country', 'City'), width= c(300, 300), type=c('text', 'dropdown'), source=I(list(0,c('Bangalore', 'Mumbai', 'NY', 'SA','Delhi','Washington')))) excelTable(data=data, columns = columns)
While the above two examples are used to explain the type of offerings that
excelR is much more than those two:
- Insert and delete rows and columns.
- Drag and drop columns
- Resizable rows and columns
- Merge rows and columns
- Lazy loading
- Native color picker
- Data picker dropdown with autocomplete, multiple, and icons feature
- Date picker
excelR is one of those R packages which may not seem to be holding Data Science use-cases right off the box, but can become quite handy as we start exploring new ideas and implement them due the nature of the package’s flexibility and customizations it offers. Nevertheless,
excelR will be very valuable while designing UI interfaces with
shiny or in fact,for a more interactive
RMarkdown document / report. Check out excelR’s own exhaustive documentation here.