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:
- Create SharePoint hosted app using VS ( i used 2013).
- Added below code to default.aspx page.
- 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:
