How to embed Excel-like Spreadsheet Table in RMarkdown

using excelR library in R

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:

install.packages('excelR')

or, the latest development version from Github:

devtools::install_github('Swechhya/excelR')

excelR - Loading and Basic Example

library(excelR)

excelTable(head(iris)) 

Excel Formulas

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

More Features

While the above two examples are used to explain the type of offerings that excelR provides, 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
  • Search
  • Pagination
  • Lazy loading
  • Native color picker
  • Data picker dropdown with autocomplete, multiple, and icons feature
  • Date picker

Summary

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.

 
comments powered by Disqus