Monday 11 July 2011

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.

8 comments:

  1. thanks for info !!

    ReplyDelete
  2. Thank you very much ! Useful and informative article. I was bit confused for Jquery that is used for editing the table in DB. But now, I got lot of confidence and will do it easily :)

    ReplyDelete
  3. Thanks for informative articles. I am bookmarking your blog for future reference.

    ReplyDelete
  4. Great sample!
    I tried this in office 365 and get a privilege issue - I get returned html asking me to request access. I'm pretty sure I have access.

    do you have a sample for office 365?

    ReplyDelete
    Replies
    1. I'm sorry, no sample for office 365. In this instance though, you should be able to check access by navigating directly to the excel file through excel services. e.g. Put the link into the browser:

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

      (or the office365 equivalent) and see if you still get permission problems. At least you will know where the problem lies.

      Delete
  5. Do you have a version that works for SP 2013?

    ReplyDelete
  6. Hello Gareth,
    Not sure if you still follow this blog but I've tried the above procedure. I'm using a Content Editor webpart to host the HTML page. Inside the html page, I have an empty div. Where I dump the content from the REST pull. However, there is no table? You refer to replacing "<table " in the script but there is no table. Everything is dumped into a div and then into divs within that. Am I doing something wrong? Thanks again for the article.

    ReplyDelete