SharePoint 2010 Excel REST API and JQuery

SharePoint 2010 has a pretty cool feature that is the Excel REST API. There are plenty of articles explaining what links to use and how to view the data as html in our sites, but how can we as developers use this data in a a less static way on our websites and really improve the user experience?

Enter JQuery, and specifically the excellent DataTables plugin. JQuery DataTables provides a way of making any HTML table searchable/filterable/sortable/pageable plus other optional plugins.

Step 1: Upload Excel document to a document library

Ok, so I'm going to upload an Excel document that contains the staff contact list.  I've created columns: Name, Mobile, Phone No, Location and set a range around all the rows of data so that Excel web services can pick up this range.  You could also insert a table into Excel rather than using ranges, whatever suits.

Setting up the contact list in Excel

I saved this and uploaded to a Document library in my Team Site and tested that Excel REST picks up the document by browsing to it:

http://mywebapp/_vti_bin/ExcelRest.aspx/Shared%20Documents/ContactList.xlsx/model

I can then click Ranges, copy the link to my range name, paste it in the address bar and change the atom param to "html" to view the Html Table Excel Rest provides:

Excel REST Html Table of my contact list

Step 2: Use JQuery to edit the table for datatables use

If you look at the source of the page above, you would see a HTML table with a few things that need correcting for Datatables to be able to use it.  It has no THead elements for the first row, it also adds a blank column and row for some reason.  Also, if colours and styling have been applied in Excel, all these inline styles are encoded into the table:

Excel REST Html Table

So in our web page, I have added a blank div element, JQuery will fill up this element on document ready with our datatable contact list via an AJAX request to the REST link. On receipt of the HTML, we will get rid of all the styling elements, the extra columns and rows and ensure the first row is within a THEAD section with TH elements.  Then we will apply DataTables, as well as reporting an error if the link to the document has moved and the AJAX query fails:

$(document).ready(function () {
    jQuery.ajax({
        type: "GET",
        url: 'http://mywebapp/_vti_bin/ExcelRest.aspx/Shared%20Documents/contactlist.xlsx/model/Ranges%28%27Contact_List%27%29?$format=html',
        data: "",
        contentType: "text/html; charset=utf-8",
        dataType: "html",
        success: function (data) {
            // fill our empty div with the html table and assign it an id
            $('#section').html(data.replace("<table ", "<table id='contacts' "));
            
            // get our newly created table within the div
            var table = $('#contacts');

            //get rid of styling from all elements
            $(table).removeAttr("style").removeAttr("class").removeAttr("cellpadding").removeAttr("cellspacing").addClass("display");
            $('tr', table).removeAttr("style").removeAttr("class");
            $('td', table).removeAttr("style").removeAttr("class");
            $('div', table).removeAttr("style").removeAttr("class");
            
            // Now remove the blank first column and row
            $('tr', table).first().remove();
            $('tr td:first-child', table).remove();

    //Create a thead element and copy the first row contents to it, making each tr a th
            $('tr:first', table).html(function (index, oldHtml) {
                return oldHtml.replace(/<td/g, '<th').replace(/<\/td/g, '</th');
            });
            $(table).wrapInner('<thead />');
            $('tr', table).first().appendTo($('thead', table));
            $('tbody', table).appendTo(table);

            // apply datatables to it
            $(table).dataTable({
                "aaSorting": [],
                "sPaginationType": "full_numbers",
                "sDom": '<i>frt<"C_TABLE_BOTTOM NIHR_TOOLBAR"lp>',
                "bAutoWidth": false
            });
        },
        error: function (xhr, msg, e) {
            $('#section').html("<span style='color: red'>Error: The section data cannot be found. Has the underlying file been deleted or moved?</span>");
        }
    });});

Step 3: See if it works

If your web page has the JQuery and the jquery.datatables scripts loaded, then you should see your table working with datatables correctly. You should be able to sort, page and search through the table too.

Contact Excel table with JQuery DataTables applied

This is a scaled down version of what I have done in my own site where I use the simplerss plugin to retrieve the various Ranges, Tables, Charts and Pivots for the user to choose from an uploaded Excel file to broaden the range of what can be used from Excel REST services. 

I hope this has provided a good example of how we can let the user use a product they are comfortable with and then being able to easily embed data from their work into a website. It adds a lot of extra value for the end site users and updates to the data are automatically applied when the user updates the Excel document.