Bind Sharepoint list to Jquery Datatable in SharePoint Hosted App

In this post i am going to describe how you can bind SharePoint list to Jquery datatable (just like grid view) with rich features like Export to excel, Export to PDF etc.

Key Points:

• Querying people picker and lookup columns using REST API
• Getting 5000 items in one query
• Showing first column as hyperlink column to my list item display form.
• Change column header name dynamically after page load
• Export to excel, pdf, notepad, csv
• List view search
• Paging
• This grid can be added as app part in sharepoint page as well (Same like list view webpart with rich features)

Thanks to this article. 

Steps:

1. Create SharePoint hosted app using VS ( i used 2013).
2. Added below code to default.aspx page.
3. Add references to your datatable js files

 <link rel="stylesheet" href="../Scripts/css/DashboardStyle.css" />

     <link rel="stylesheet" href="../Scripts/css/dataTables.tableTools.css" />

     <link rel="stylesheet" href="../Scripts/css/jquery.dataTables.css" />

    <script type="text/javascript" src="../Scripts/jquery-1.9.1.min.js"></script>

    <script type="text/javascript" src="../Scripts/EmployeeLists.js"></script>

    <script type="text/javascript" src="../Scripts/jquery.datatables-1.9.4.min.js"></script>    

    <script type="text/javascript" src="/_layouts/15/MicrosoftAjax.js"></script>

    <script type="text/javascript" src="/_layouts/15/sp.runtime.js"></script>

    <script type="text/javascript" src="/_layouts/15/sp.js"></script>

   <script type="text/javascript" src="../Scripts/dataTables.tableTools.js"></script>

4. Add HTML in div tag

 <p id="message">

            <!-- The following content will be replaced with the user name when you run the app - see App.js -->

            initializing...

        </p>

         <table id="employeeList" class="table table-striped table-bordered1 table-condensed table-hover table-fullfixed">

            <thead>

                <tr>

                    <th >Title</th>

                    <th>Last Name</th>

                    <th>Email</th>

                    <th id="week1th">Week1Date</th>

                    <th id="week2th">Week2Date</th>

                    

               

                </tr>

            </thead>

            <tbody>

            </tbody>

        </table>

5. Created one js file and added below code, change column names and list name accordingly.

'use strict';

var hostweburl;

var appweburl;

var _listName;

var employeeListQueryUrl;

var EmployeesList = 'EmployeesList';

// This code runs when the DOM is ready and creates a context object which is   

// needed to use the SharePoint object model  

$(document).ready(function () {

    

   

    //Get the URI decoded URLs.   

    hostweburl =

        decodeURIComponent(

            getQueryStringParameter("SPHostUrl"));

    appweburl =

        decodeURIComponent(

            getQueryStringParameter("SPAppWebUrl"));

    // Resources are in URLs in the form:  

    // web_url/_layouts/15/resource  

    var scriptbase = hostweburl + "/_layouts/15/";

// Using below string i am querying 5000 items and getting values of People picker column, lookup column as well.

    employeeListQueryUrl = appweburl + "/_api/SP.AppContextSite(@target)/web/lists/getbyTitle('" + EmployeesList + "')/Items?@target='" + hostweburl + "'&$select=Title,LastName,Email,ContactNo,Address,ID,PP/ID,PP/Title,Lookup/ID,Lookup/Title&$expand=PP/ID,PP/TitleLookup/ID,Lookup/Title&$orderby=Created asc&$top=5000";

    // Load the js file and continue to load the page with information about the list top level folders.  

    // SP.RequestExecutor.js to make cross-domain requests  

    // Load the js files and continue to the successHandler  

    $.getScript(scriptbase + "SP.RequestExecutor.js", execCrossDomainRequest);

});

// Function to prepare and issue the request to get  

//  SharePoint data  

function execCrossDomainRequest() {

    var executor;

    executor = new SP.RequestExecutor(appweburl);

    executor.executeAsync(

        {

            url: employeeListQueryUrl,

            method: "GET",

            headers: { "Accept": "application/json; odata=verbose" },

            success: employeeSuccessHandler,

            error: errorHandler

        }

    );

   

}

function employeeSuccessHandler(data) {

    var jsonObject = JSON.parse(data.body);

    var results = jsonObject.d.results;

    fillData(results, EmployeesList);

    $('#employeeList').dataTable({

        "fnRowCallback": function (nRow, results, iDisplayIndex) {

            $('td:eq(0)', nRow).html('<a target="_blank" href="' + hostweburl + '/Lists/' + EmployeesList + '/Dispform.aspx?ID=' + results[0] + '">' +

            results[0] + '</a>');

            //$('td:eq(0)', nRow).html('<a target="_blank" href="' + hostweburl + '/Lists/' + EmployeesList + '/Dispform.aspx?ID=' + results[0] + '"><img src="../Images/View_icon.png" border="0"/></a>');

        return nRow;

        },

        //"createdRow": function (row, results, index) {

        //    if (results[2].indexOf('Y') == 1) {

        //        $('td', row).eq(2).addClass('highlight');

        //    }

        //},   

       

    });    

    var table = $('#employeeList').dataTable();

    var tableTools = new $.fn.dataTable.TableTools(table, {        

        aaSorting: [[4, 'desc']],

        'aButtons': [

            {

                'sExtends': 'xls',

                'sButtonText': 'Save to Excel',

                'sFileName': 'Data.xls'

            },

            {

                'sExtends': 'print',

                'bShowAll': true,

            },

            {

                'sExtends': 'pdf',

                'bFooter': false

            },

            'copy',

            'csv'

        ],       

      

        'sSwfPath': '//cdn.datatables.net/tabletools/2.2.4/swf/copy_csv_xls_pdf.swf'

    });

    document.getElementById('week1th').innerHTML = "Dynamic Column Name";

}

function errorHandler(data, errorCode, errorMessage) {

    alert(data + ' >> ' + errorCode + ' >> ' + errorMessage);

}

function fillData(results, _ListName) {

    var $appContent = $('#employeeList tbody');

    for (var i = 0; i < results.length; i++) {

        var $tr = $('<tr/>');

        $('<td/>').text(results[i].ID).appendTo($tr);

        $('<td/>').text(results[i].Title).appendTo($tr);

        $('<td/>').text(results[i].Email + "Y").appendTo($tr);

        $('<td/>').text(results[i].Lookup['Title']).appendTo($tr); // Set lookup text

        $('<td/>').text(results[i].PP['Title']).appendTo($tr); // Set people picker value

        $tr.appendTo($appContent);

    }

}

// This function prepares, loads, and then executes a SharePoint query to get   

// the current users information  

//Utilities   

// Retrieve a query string value.   

// For production purposes you may want to use   

// a library to handle the query string.   

function getQueryStringParameter(paramToRetrieve) {

    var params =

        document.URL.split("?")[1].split("&");

    for (var i = 0; i < params.length; i = i + 1) {

        var singleParam = params[i].split("=");

        if (singleParam[0] == paramToRetrieve)

            return singleParam[1];

    }

}

Final Output: