Exporting table data in web applications as CSV or Excel files

Home » Blog » Software » Exporting table data in web applications as CSV or Excel files

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”:

* indicates required




Leave a Comment

* Checkbox GDPR is required

*

I agree

By continuing to browse the site you agree to our use of cookies. Privacy Policy

Privacy Preference Center

Strictly necessary

These cookies are necessary for the site to function.

PHPSESSID: Preserves user session state across page requests.

__cfduid: Used by the content network, Cloudflare, to identify trusted web traffic.

PHPSESSID
__cfduid

Preferences

Remembers the user's submitted data when a comment is submitted in a blog post. The purpose is to aut o-populate form fields for subsequent comments, in order to save time for the user.

wfvt_#

Statistics

Statistic cookies help us to understand how visitors interact with our websites by collecting and reporting information anonymously.

_ga: Registers a unique ID that is used to generate statistical data on how the visitor uses the website.

_gat: Used by Google Analytics to throttle request rate.

_gid: Registers a unique ID that is used to generate statistical data on how the visitor uses the website.

collect: Used to send data to Google Analytics about the visitor's device and behaviour. Tracks the visitor across d evices and marketing channels.

_ga,_gat,_gid
collect

Security

We use Wordfence to secure our website against hacking attempts: https://www.wordfence.com/

wordfence_verifiedHuman

Close your account?

Your account will be closed and all data will be permanently deleted and cannot be recovered. Are you sure?