Export SharePoint List to Excel, Word, JSON, XML, SQL, CSV, TXT or PDF

Using JQuery we can export SharePoint list to Excel, Word, JSON, XML, SQL, CSV, TXT or PDF. Here I’m going to explain step by step explanation to implement this same in your environment. This will work for SharePoint 2013, 2016 and SharePoint online.

You can download complete source code from the below URL

zip_iconhttps://code.msdn.microsoft.com/Export-SharePoint-List-to-a802fd93

2016-02-17_23-46-38

Create “App for SharePoint” project in visual studio, in the new project creation wizard is select options based on your requirement. After project created rename your SharePoint App page, which is located under Pages folder, here I renamed as JQueryExport.aspx

Double click AppManifest.xml file and select SharePoint list view permission, because here we are just going read data from a SharePoint list, I’ve selected “Documents” list. List name and column name are hard coded  in the App.js file,


'use strict';

ExecuteOrDelayUntilScriptLoaded(initializePage, "sp.js");

function initializePage() {
var context = SP.ClientContext.get_current();
var user = context.get_web().get_currentUser();
var hostweburl;
var appweburl;
var appContextSite;
var list;
var listItems;
var web;
// 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 () {
getUrl();
});

// This function get the URL informations
function getUrl() {
hostweburl = getQueryStringParameter("SPHostUrl");
appweburl = getQueryStringParameter("SPAppWebUrl");
hostweburl = decodeURIComponent(hostweburl);
appweburl = decodeURIComponent(appweburl).toString().replace("#","");
var scriptbase = hostweburl + "/_layouts/15/";
$.getScript(scriptbase + "SP.RequestExecutor.js", execOperation);
}

// This function get list data from SharePoint
function execOperation() {
var factory = new SP.ProxyWebRequestExecutorFactory(appweburl);
context.set_webRequestExecutorFactory(factory);
appContextSite = new SP.AppContextSite(context, hostweburl);
web = appContextSite.get_web();
context.load(web);
var camlQuery = new SP.CamlQuery();
list = web.get_lists().getByTitle("Documents");
listItems = list.getItems(camlQuery);
context.load(list);
context.load(listItems);
context.executeQueryAsync(onGetSPListSuccess, onGetSPListFail);
}
// This function is executed if the above call is successful
function onGetSPListSuccess() {
$("#DivSPGrid").empty();
var listInfo = '';
var listEnumerator = listItems.getEnumerator();
listInfo += "
<table id='SPTable' class='display'>
<thead>
<tr>" +
"
<th>Id</th>
" +
"
<th>Title</th>
" +
"
<th>Colour</th>
" +
"
<th>Modified By</th>
" +
"
<th>Modified date</th>
" +
"</tr>
</thead>
<tbody>";
while (listEnumerator.moveNext()) {
var listItem = listEnumerator.get_current();
listInfo += '
<tr>
<td>' + listItem.get_item('ID') + '</td>
'
+ '
<td>' + listItem.get_item('FileLeafRef') + '</td>
'
+ '
<td>' + listItem.get_item('Colour') + '</td>
'
+ '
<td>' + listItem.get_item('Editor').get_lookupValue() + '</td>
'
+ '
<td>' + listItem.get_item('Modified').format('dd MMM yyyy, hh:ss') + '</td>
'
+ '</tr>
';
}
listInfo += '</tbody>
</table>
';
$("#DivSPGrid").html(listInfo);
$('#SPTable').dataTable();
}

// This function is executed if the above call fails
function onGetSPListFail(sender, args) {
alert('Failed to get list data. Error:' + args.get_message());
}

//This function split the url and trim the App and Host web URLs
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];
}
}
}
<pre><!-- End --></pre>

Add CSS files into the ASPX


<!-- Add your CSS styles to the following file -->
	<link rel="Stylesheet" type="text/css" href="../Content/App.css" />
	<link href="../Content/ionicons.min.css" type="text/css" rel="stylesheet" />
	<link href="../Content/bootstrap.min.css" type="text/css" rel="stylesheet" />
	<link href="../Content/jquery.dataTables.css" type="text/css" rel="stylesheet" />

Add set of JS files as reference in your ASPX page


<!-- Add your JavaScript to the following file -->
 <!--our custom js file-->
 <script type="text/javascript" src="../Scripts/App.js"></script>

<script type="text/javascript" src="../Scripts/jquery-1.9.1.min.js"></script>
 <script type="text/javascript" src="../Scripts/bootstrap.min.js"></script>
 <!--For export PDF file-->
 <script type="text/javascript" src="../Scripts/jspdf/libs/base64.js"></script>
 <script type="text/javascript" src="../Scripts/jspdf/libs/sprintf.js"></script>
 <script type="text/javascript" src="../Scripts/jspdf/jspdf.js"></script>
 <!--For export PNG file-->
 <script type="text/javascript" src="../Scripts/html2canvas.js"></script>
 <!--For export all other formats-->
 <script type="text/javascript" src="../Scripts/tableExport.js"></script>
 <script type="text/javascript" src="../Scripts/jquery.base64.js"></script>
 <!--For HTML Table format-->
 <script type="text/javascript" src="../Scripts/jquery.dataTables.js"></script>

I have used bootstrap drop down for listing export options,

<div class="dropdown">
<button class="btn btn-warning btn-sm dropdown-toggle" data-toggle="dropdown"><i class="fa fa-bars"></i>Export Table Data</button>
<ul class="dropdown-menu " role="menu">
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'json',escape:'false'});">
<img src="../Images/json.png" width='24px' />
JSON</a></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'json',escape:'false',ignoreColumn:'[0]'});">
<img src='../Images/json.png' width='24px' />
JSON (ignoreColumn)</a></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'json',escape:'true'});">
<img src='../Images/json.png' width='24px' />
JSON (with Escape)</a></li>
	<li class="divider"></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'xml',escape:'false'});">
<img src='../Images/xml.png' width='24px' />
XML</a></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'sql'});">
<img src='../Images/sql.png' width='24px' />
SQL</a></li>
	<li class="divider"></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'csv',escape:'false'});">
<img src='../Images/csv.png' width='24px' />
CSV</a></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'txt',escape:'false'});">
<img src='../Images/txt.png' width='24px' />
TXT</a></li>
	<li class="divider"></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'excel',escape:'false'});">
<img src='../Images/xls.png' width='24px' />
XLS</a></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'doc',escape:'false'});">
<img src='../Images/word.png' width='24px' />
Word</a></li>
	<li class="divider"></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'png',escape:'false'});">
<img src='../Images/png.png' width='24px' />
PNG</a></li>
	<li><a href="#" onclick="$('#SPTable').tableExport({type:'pdf',pdfFontSize:'7',escape:'false'});">
<img src='../Images/pdf.png' width='24px' />
PDF</a></li>
</ul>
</div>
<div id="DivSPGrid">
</div>
<script type="text/javascript">
 $(document).ready(function () {
 $('.dropdown-toggle').dropdown();
 });
 </script>

Let me know if you have any queries,

zip_iconhttps://code.msdn.microsoft.com/Export-SharePoint-List-to-a802fd93

2 thoughts on “Export SharePoint List to Excel, Word, JSON, XML, SQL, CSV, TXT or PDF

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s