Table of Contents
Oliver Folkerd's Tabulator is nothing less than an awesome plugin, which I'm extensively using in all my JQuery-based Web Applications. In a nutshell, it's a lightweight JQuery plugin that allows you to create interactive tables from any HTML Table, Javascript Array, AJAX data source or JSON formatted data. The plugin also comes with a complete set of CSS classes to easily and effectively styling your tables.
If you don't know it yet, take a look at the Tabulator official page and/or to the Tabulator GitHub repository.
Among the many useful features offered by Tabulator, there's the download data function, which allows the users to download the table data as a file directly from their browser, without server support. The download will contain the values of all data currently visible in the table, thus matching the current column layout, column titles, sorting and filtering, in one of the following formats:
- csv - Comma seperated value file
- json - JSON formatted text file
- xlsx - Excel File (Requires the SheetJS Library)
- pdf - PDF File (Requires the jsPDF Library and jsPDF-AutoTable Plugin)
Trigger a download is just as easy as calling the download function, passing the file type (from the above list) as the first argument, and an optional second argument of the file name for the download (if this is left out it will be "Tabulator.ext"). Here's a quick example:
|
1 2 |
//download table data as a CSV formatted file with a file name of data.csv $("#my-table").tabulator("download", "csv", "data.csv"); |
The Problem
Although the download data feature is great, it lacks something really important: since it's limited to the currently visible data, it doesn't give the chance to download *all* the data of a pagination-enabled table. To better understand the issue, let's picture a table with 5000 total rows which is set to show 50 rows per page. The download data function will only get you the 50 currently visible rows, thus ignoring the remaining 4950: in other words, the resulting file (be it CSV, JSON, XLSX or PDF) will only contains the currently available/visible/fetched rows.
The only way to overcome this "by design" would be to entirely drop the pagination and getting a 5000-rows table - which could easily kill your HTML page.
The Fix
As a matter of fact, downloading only the currently-visible rows is a legitimate behaviour, expecially when remote paging is enabled, since the previous/next/remaining rows haven't even been fetched from the server.
However, since I really needed a download all data function, I did my best to find a way to download all rows regardless if they're visible or not: I managed to do that by "hacking" the downloadDataMutator / downloadDataFormatter function in the following way.
The hack has been originally published in this GitHub issue, but I choose to make a post about it to better explain it.
The "Download All Data" Button
The first thing I did was to implement a "download all" button with an async ajax call to fetch all the values from the server: as a matter of fact, I used a slightly modified version of the same Web Service that I implemented to populate the table in the first place - without the paging logic, so that it would always return all the available rows.
|
1 2 3 4 5 6 7 8 9 10 11 |
$("a#download_XLSX_all").click(function (e) { e.stopPropagation(); e.preventDefault(); $.ajax({ url: '/ws_to_fetch_all_data/', }).done(function (data) { // store the whole data into a global variable allData = data.data; $("div.tabulator").tabulator("download", "xlsx", "data.xlsx"); }); }); |
Notice how the WS result - a JSON array of all the rows - is stored in the allData global variable.
The DownloadDataMutator hack
The next (and final) step involves hacking the Tabulator's downloadDataMutator option method (which has been renamed to downloadDataFormatter in Tabulator 3.5 and above) to replace the "current" table (visible) data with the allData global variable which we populated early on:
|
1 2 3 4 5 6 7 8 9 10 |
downloadDataMutator: function (data) { // if the allData global variable is present, // we use it instead of "current" paginated data. if (allData) { data = allData; allData = null; } // [TODO: do other column-based trasformations, if required return data; } |
Notice how the allData global variable is then set to null, so that the standard download data function would still work for any subsequent download method call: that way multiple download visible rows and download all rows buttons can cohesist without significative conflicts.
Conclusion
Let's briefly summarize what this Tabulator's data-swapping hack actually does:
- when the download all data button is clicked, it downloads all the unpaginated data from the Web Server in a single AJAX call and stores the results in the allData global variable;
- as soon as the AJAX call finishes and the allData global variable is set, it fires the Tabulator's native download method, who also triggers the downloadDataMutator hook (downloadDataFormatter in Tabulator 3.5 and above);
- within the downloadDataMutator / downloadDataFormatter hook, it swaps the "actual" table data (the visible rows) with the previously-fetched data stored in the allData global variable (all the available rows), providing it's not null; right after that, the allData variable is set to null because it's not needed anymore.
That's about it: I sincerely hope that such hack will help those who're using Tabulator and are missing such handy feature!
Many thanks to @olifolkerd for his awesome work!
