In business applications being able to export data to Microsoft Excel is a very common requirement. In most business environments Excel is the go-to tool for business analytics and quick-and-easy data processing. Hence, being able to communicate with Excel is an essential feature for most kinds of business software.
The usual approach for meeting that requirement is generating a CSV file on the server. However, while simple this approach can come with a few downsides:
While CSV is a useful, accessible plain text format that can easily be imported into Excel and in fact almost any data processing software, sometimes native Excel files (.xls or the Office Open XML .xlsx format) can be required for both added convenience (no import dialog) and better non-ASCII character set / encoding support. While there are robust libraries like Apache POI for creating Microsoft Office documents doing so can be a difficult, time-consuming and error-prone task. After all, Microsoft Office document formats aren’t exactly known for their straightforward implementation or transparent documentation.
Moreover, in modern client-heavy web applications data filtering and display often happens on-the-fly and in the browser client only without touching the server again for filter or search operations. Therefore the server might not even know which data entries are visible to the user at a given moment. Exchanging that information would require another roundtrip between client and server, which would add complexity to your software as well as incur a performance penalty.
There is a better, in many use cases much more expedient solution: Much of the data displayed in web applications already is available as tabular data because it’s displayed in HTML tables. Now, a lesser known feature of Excel (since Excel 2007) is that it’s capable of natively opening data that comes in a specific XML format. That XML format supports incorporating HTML tables perfectly well.
This means you can just take the current state of a data table in your web application and directly load it into Excel! Neat, isn’t it?
tableExport.jquery.plugin is a highly useful jQuery plugin for exporting table data to various formats (in addition to Excel, CSV, JSON, SQL, Microsoft Word and PowerPoint and some more are supported as well). It abstracts over the details and intricacies of the export process and allows you to easily export tabular data from your application on-the-fly and without any additional server roundtrips.
I’ve added a few improvements in my own fork of the plugin (improved UTF-8 support, enforced download filenames, new Base64 encoder etc.): BjoernKW/tableExport.jquery.plugin (original source: kayalshri/tableExport.jquery.plugin)
If you’d like to know more about various data processing techniques for web applications combined with Microsoft Excel please subscribe to this mailing list for more information on my upcoming guide on “Tables and Spreadsheets – Modern Data Processing Techniques for the Web”: