How many times did you hear users asking you: “something simple, a grid like excel”?
When I was a VB programmer, oh yes, I have this dark spot on my career, and it is not the only one… this request threw me in panic. Usually what your “killer” is asking you is not what you, programmer, are thinking of (namely the power of cell functions, programmability, graph etc., that probably your “killer” does not even imagine): what they are thinking about is the editor flexibility, the ability to add columns, rows, move cells blocks, copy and paste from different sources.
After the ritual pointing-out that your application is NOT Excel, the VB solution was to adopt the standard flexGrid or the mythical TrueDbGrid that made happy both the killer-user and the victim-programmer.
I spent some days looking around for a solution. I warn you that this post does not pretend to be a complete list of available grid/table editors and that my investigation is mainly “emotional”: I want to feel on a web-app the same prickle that I experience when using Excel.
Of course it is not only romantic surfing, I needed to edit data to fill the graph snipplet for Patapage, so there are some “nice to have” requirements: to be free of charge, light, based on jQuery, appealing. Server side aspects as pagination, sorting, CRUD etc. are for the moment not the crucial points as the amount of information I need to manage is limited and a totally client side solution would be acceptable.
Actually the implementations I found can be classified in three main groups:
- data driven: these are generally components with all the features needed for listing a large number for rows, with server-side pagination, scrolling, search and sort functionality; generally are lacking editing functionality, or when the feature is present is single row based, reflecting the fact that there is an underlying database. Data binding is made through XML or Json, depending the language supported server-side
- light edit: here the focus is given to the “editing agility”, every row is always in editing status, search and sort are rarely available and in any case working on a limited set of rows isn’t a real limitation. Data is filled starting from a JS object, calling methods, or from an html table. Some of these implementation supply callback methods that can be used to interact server side.
- spreadsheets: oh yes! there are also some really sophisticated JS clones of the glorious one! These are generally complex solution (in terms of dependencies), supporting formulas, graphs and lot of functionalities. Here the focus is the “simulation effect”, data management and server side integration are really in background.
When I started my search I didn’t imagine that there were so many solutions, so I decided that it would be better here to limit the result to the ones based on jQuery. Lets go in depth :
One great example of this category is jqGrid by Tony Tomov. This is a really well engineered solution released under MIT and GPL licenses.
Supports pagination, search, sort and even edit. There are two specific versions for PHP and .NET that drastically reduce the server-side implementation effort. Loading data is supported in multiple ways through XML, JSON or JS arrays and examples are supplied for PHP and MySql. There is a third party Ruby example here.
It uses jQuery.UI components for display, so the look and feel is pretty. Switching to edit mode is something perceivable, the user will see that the edit is active, but this is not necessarily bad in this class of components.
It supports sub-grids, multiple input types, master-detail, multi-selections and other useful features.
Column configuration is done in a declarative way using JS objects:
colNames:['Inv No','Date', 'Client', 'Amount'],
that gives you the flexibility you need for well tailored applications.
Seems a rock solid components, the behavior in resizing, scrolling, paging is really pleasant.
Documentation is not the strongest point but there are lots of examples that will help setting up your grid.
by Allan Jardine released under GPL v2 and BSD is a powerful data grid component.
Supports pagination, resizing, filtering, multi-column sorting, jQuery UI theme roller, and lots of interesting features.
Data feeding is provided a-la-carte, from DOM, Ajax XML or JSON sources, JS arrays… it’s your choice!
There is also the icing on the cake: cells and rows grouping, cell custom renderer, multi-row selection and much more.
Provides cell editing via jEditable cell-by-cell, not a row at once, and this in some cases could be a limitation; so you may need more that one update to change a row. Probably you can work around this trivia-point.
Configurations starts from zero-config in case of DOM feeding and can be refined to a real full-control using JS objects.
It is well-documented with a large set of examples and this is another plus!
A black spot (at least for me, Java programmer): server side examples in PHP only.
Another valid example is Flexgrid by
What I really appreciated in this component is the lightness and its simplest feeding example based on an html table. In this case the whole setup consists of:
Of course this is a quite minimalist approach to this component that supports also XML and JSON data feeding.
Paging, sorting, resizing and other nice features like button injection are supported.
What is missing is the edit capability that is planned but still not released.
Similarly to the previous component the configuration (if needed 😉 ) is done by JS objects.
Documentation: we all are programmers, we hate to write it but we love to read it… so here too is really succinct with few examples for PHP server-side both XML and JSON.
tgrid by Mateusz Mikołajczyk is a lightweight datagrid, that supports all main features like sorting, paging, filtering.
I didn’t discover the license type so be careful on usage.
Data is fed by Ajax Json calls that make this component scalable enough.
Documentation supplied is “short” but with some examples, so setup is not a big deal.
This components supports a “row rough” edit, in the sense that it is not in-place, but out of the grid: it did not give me the right feeling. Taken out the edit, tgrid runs well.
Configuration does not offer many options; something nice is the cell custom renderer that allows you to format data.
Ingrid (great name!) is a less powerful grid component, but it is really unobtrusive and cute enough to be considered. Released under GPL license, it supports column resizing, sorting, paging etc.
Data is fed only by providing an html table, and this could find some criticism.
Google Chrome support has some little creeps on display.
Multiple row selection is a nice feature that earns some points to this component.
Editing is not supported.
In the same spirit of loveliness jTps by Jim Palmer is released under MIT license.
Paging, resizing, sorting are supported; a nice feature is smooth scrolling. Even in this case editing is not supported, but the real limit of this component is in term of scalability: data feeding does not support Ajax sub-sequential loading, so all data is loaded at once. Paging is only client side limiting the meaning of this feature.
Same category for dhtmlxGrid by DHTMLX.
dhtmlxGrid is distributed under GNU GPL v2, but also available under a commercial license.
It is built over a “dhtmlxcommon” js library instead of most famous jQuery or Prototye libraries, but this is not a big deal if you need such powerful component.
For the sake of completeness I have to cite TableEditor by Brice Burgess that seems a little bit outdated (even if really well indexed), and Snowcore’ jQuery Data Grid that is in Russian and this fact did not help me to understand how to use it; moreover the site icon let’s me suppose that IE testing is not a priority for Snowcore 🙂
This category, as before stated, is mainly focused on edit agility instead of server-side data management. This fact steers the implementation towards a all-client-side approach. Implementations in this category are generally a little bit rough with respect to the previous ones.
A real interesting exception is the excellent SlickGrid by Michael Leibman released under MIT style license.
Here the author is particularly careful in rendering performance; he uses a virtual rendering technique to limit the number of DOM elements in order to make SlickGrid really scalable. The example provided with 50000 rows really run in a flash.
The author states in the “grid vs data” paragraph that here the focus is on the grid, not on the data. Despite his intentions, sorting, resizing, filtering, resizing and edit callbacks, give this grid a place even in in the previous category.
Data feed can be done via JS arrays, or via Ajax using the Slick.Data.RemoteModel object provided.
Custom cell renderer allows you to express your creativity, as shown explicitly in the examples when injecting a Sparkline graph in the last column (see my previous post for a excursus on charting components).
Rows multi-selection, ordering, and custom row rendering using John Resig’s micro-templates makes this component really flexible.
Configuration is done as usual using JS objects.
Having all this features and also having taken care of cell movements with the keyboard and easy editing is a great achievement! Thanks Michael.
Once again Allan Jardine provides us a useful tool called keytable that injects Excel-like key movement on a generic table.
This can have multiple usages as exemplified on demos available on the site. It easy to understand how a key-movement can change the user experience when it is used on a powerful grid component like DataTables (see above). Here the author’ example.
Another really basic example of editable table is supplied by Greg Weber with uiTableEdit: there are no examples online, so I built a simple test page, and the components just add the capability to change the cell value. No key movement or other additional features are handled; it can be used as starting point for further refinement.
Same kind of “roughness” for tEditable by Josh Hundley, where edit is click based, and key-movement is not supported. This do not mean that this tools is useless, just that it is really far from a complete solution.
It converts DOM cells to text-areas on the fly when editing, and this makes this component almost setup-free.
A part of this category (the rough part 🙂 ) is my contribution, used to fill data on PataPage’ charting playground.
Here the functionality are really basic: key-movement, edit, load from url, add and remove only are supported. The grid content is “saved” on a text area with \t and \n separators: same format expected when loading from url. I’m working for supporting the ^v (paste) capability to facilitate user data insertion as block of cells.
Actually this category comprises solution wider than previous ones, in the sense that a complete solution will probably include all the features listed before; this is on one side a plus, but on the other side if you are interested in extracting some parts (e.g. table edit but not on formulas, server-side integration but not on key-movement etc.), this could be a little difficult due to the complexity of the solutions available.
Light years beyond other solutions at least as first impression, jQuery.sheet by Robert Plummer is a really wonderful library. I didn’t find the license type.
Really looks like an Excel sheet and the key-movement seems realistic.
Data feeding is done using a DOM table, eventually Ajax-loaded, or by adding row and cols programmatically.
The grid supports resizing, but the real wondering feature is the cell-function support. Not limited to SUM, AVG and other standard functions, but also chart are allowed.
Lots of callback will help you to integrate the sheet on you application.
There are some little bugs on cell selection and some minor layout issue with Chrome, but the overall behavior is impressive.
The last, but definitively not least, example of websheet (a web spreadsheet) is the Googledocs one. It is a really good live-teaching example, and even if it is not a ready-to-use library I suggest you to open it and dissect it with, for instance, Firebug.
It is interesting to see how many tricks they used: the whole grid is one iframe, the rows are grouped in small tables of 20 rows (probably in order to scale on IE), the cell value is stored in the td dom; when you edit a cell a text area is printed over the cell (not inside), and so on.
I tried to implement my grid using the same structure, but after some hours I surrendered; the effort to “master” the layout was over my actual estimation, and I fell back to the solution discussed above. Would be gorgeous to have a jQuery open-source implementation like this!
Google docs spreadsheet is the only application I found that gave me the right prickle!
Well I stopped my searching here, and I didn’t explore Mootools, Sriptacolous, Ext and maverick ones in detail.
There is a booklet for this blog post here on licorize.com. (Licorize is one of my latest creations, please take a look)
I’ll just list here some other libraries I found:
Spreadsheet Dojo Widget
Ext JS DataGrid
YUI 2 DataTable
BlueShoes SpSpeadsheet editor
Sigma Grid 2.2