psjinx's blog


Parsing excel workbooks using Javascript

xlsxjs xlsx javascript excel workbook   Programming Comments http://psjinx.com/programming/2014/01/04/parsing-excel-workbooks-using-javascript/#disqus_thread
A Javascript library for parsing .xlsx files. It includes sample code along with demo for integration with AngularJS. Use cases include listing sheet names for selection in a form, showing preview of selected sheet etc.

Recently, I had to create a new web app, which would generate PDF reports based on data stored in excel worksheets. As usual, I used Django along with many other Python libraries for this.

It has following parts:

  1. HTML Form - To select excel files and upload it to server
  2. Parsing uploaded excel file - Basic validation followed by reading data from worksheets. I used xlrd module from Python-Excel project for this.
  3. Data analysis and plots - I used pandas, Python Data Analysis Library, and matplotlib, a python 2D plotting library, for this.
  4. PDF Generation - RML from ReportLab along with awesomeness of Django Templates seems suitable for this job. I used z3c.rml, an open source implementation of RML standard by Zope Foundation.

So, everything was working like a charm, except the fact that web app was reading data from first worksheet by index whenever an excel workbook was uploaded. But, user should be able to tell which worksheet to read from, right?

For this, I needed to know sheet names for selected excel workbook and then show a select box to choose one sheet name. I also wanted to show data stored in that worksheet in a table, a preview.

After couple of Google searches for "reading excel files using javascript", I landed up on homepage of SheetJS project. They have two projects, js-xlsx and js-xls, but I did not get "for human beings" feeling from this (if you have see requests vs urllib2 then you know what I mean).

After 1-2 hours of walking through source code of js-xlsx, I ended up writing XLSX reader using javascript for humans. Example code using this new library looks like:

$(function() {
    $("#xlsxFile").change(function(event) {
        var file = this.files[0],
            sheets;
        XLSXReader(file, true, function(xlsxData) {
            sheets = xlsxData.sheets;
            // Do somehting with sheets. It's a
            // Javascript object with sheet names
            // as keys and data as value in form of 2D array
        });
    });
});

I have integrated this XLSXReader in my web app along with AngularJS. It consists of following parts:

  1. XLSXReaderService, an angular app service, written on top of XLSXReader,
  2. a web worker for reading excel files in a separate thread Firefox does not support FileReader api for web workers, so not using it anymore,
  3. an AngularJS controller along with HTML template.

Code snippet for this integration is available at https://gist.github.com/psjinx/8252417.

Demo

You can see a demo of above code snippet at http://code.psjinx.com/xlsx.js/.

Use cases for this kind of library

  1. Showing list of sheet names to choose from
  2. Preview of data stored in worksheets
  3. Integration with excel like data grid editors, e.g. Handsontable
  4. Send JSON data to server instead of excel files
  5. Integration with D3.js and a lot more

P.S. - I have tested everything on Google Chrome and Mozilla Firefox only. Since, this web appliaction is for internal use, I don't have to deal with browser compatibility issues. But, it should work in all browsers supported by Lodash and SheetJS.

blog comments powered by Disqus

Follow Me





Subscribe To My Feed

While you're here, why not add my atom feed to your RSS reader or subscribe via email ?

Tags

django django-tastypie rest-api xlsxjs xlsx javascript excel workbook


Creative Commons License Using Jekyll, Pygments, Foundation | Sitemap