A recent project I’ve been working, read about the latest version here, on presents users with a fair amount of data in tabular format. I wanted to provide a way for the user to be able to sort the tabulated data on any of the columns in the table. Similar to the way that they could if they chose to export the data as a Microsoft Excel spreadsheet. There were two ways of achieving this that I could see:
- Implement code on the server side to sort the data.
- Implement some sort of JavaScript on the client side to the sort the data.
The downside of using server side code is that it requires a round trip to the server every time the user wanted to sort the data. This increases load on the server, not a really big issue in this small application, and it makes the user wait while the page is refreshed. Even if I’d use some sort of AJAX script this would still be the case, except less of the page would be refreshed.
Using client side JavaScript for the sort was a more attractive option as it didn’t have the server side issues. I did come with some issues of its own though. Namely I haven’t written much JavaScript in a very long time, and the thought of trying to write cross browser JavaScript for this from scratch gave me the horrors. So I went looking for a suitably licensed JavaScript library that I could use.
After a little bit of searching I found the Table Sorting and Filtering library by Matt Kruse. The script provides much more functionality than I required. I chose it though because it had the capability to sort numeric data that had commas in it. Other libraries I tried failed on this requirement.
Any one else considering this type of functionality I would encourage to look into the library by Matt. There is a wealth of open source JavaScript libraries around and many are of very high quality. Using this library meant I had more time to implement other features like the export of data to a Microsoft Excel spreadsheet.
June 28, 2007 at 2:17 pm
When you export to excel, do you use the XML Spreadsheet format? I’ve found it to be pretty good compared to CSV as it supports UTF-8 and you can do a few tricks that CSV doesn’t do. Or do you use a specific Excel writer class?
June 28, 2007 at 3:45 pm
@Matt,
For the past year or so I’ve been writing in Perl. With this in mind I’ve used the Spreadsheet:WriteExcel Perl module from CPAN. It creates a binary Excel file.
If I remember correctly the XML excel file format wasn’t really supported until Office 2003, and I can’t guarantee in my environment here that everyone will have that version. So the binary format is the best way to go as it is supported by all of the versions that may be in use here.
Using the Perl module I was able to do things such as a style cells, apply number formats, and write formulas. The only thing I wasn’t able to do is build charts. Experimental support is, I believe, available in the module for this. But I ran out of time.