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
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:
Step 3: See if it works
$(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 |
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.
thanks for info !!
ReplyDeleteThank 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 :)
ReplyDeleteNice blog.
ReplyDeleteThanks for informative articles. I am bookmarking your blog for future reference.
ReplyDeleteGreat sample!
ReplyDeleteI 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?
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:
Deletehttp://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.
Do you have a version that works for SP 2013?
ReplyDeleteHello Gareth,
ReplyDeleteNot 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.