Export SharePoint list data to excel programmatically using JavaScript and SPServices

by
Complete SharePoint Training Course ($46 OFF)

This SharePoint tutorial explains, how to export sharepoint list data to excel programmatically using JavaScript object model (jsom) and SPServices in SharePoint Online or SharePoint 2013/2016.

Recently we got one requirement to export selected list items to excel in SharePoint 2013. By default, the export to excel will export all the records from the list view.

But here the requirement was a bit different, we need only selected items to export. Due to some restrictions, we can not use any server-side code, we can use only a client-side object model code like JavaScript client object model or jQuery object model, etc.

Export sharepoint list data to excel programmatically using JavaScript and SPServices

In the list view page, we have added an HTML button and when a user clicks on that, we are exporting. Here we have used jQuery and SPServices for this purpose.

You can put the below code in the list view page inside a script editor web part.

<script type="text/javascript" src="https://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js"></script>

<div>
<input type='button' id='btbdiv' value='Generate Excel' onclick="DownloadListItems();"/>
</div>
<script language="javascript" type="text/javascript">
var projectItem1,projectItem,id;
function DownloadListItems()
{
var item;
i=0;
var table = document.getElementById('cntryTable');
while (table.rows.length > 1) {
table.deleteRow(1);
}

var context = SP.ClientContext.get_current();
var selectedItems = SP.ListOperation.Selection.getSelectedItems(context);
var list = context.get_web().get_lists().getByTitle("TestList");
for (item in selectedItems){
projectItem1 = selectedItems[item].id;
var CamlQuery = "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Number'>" + projectItem1 + "</Value></Eq></Where></Query>";
$().SPServices({
operation: "GetListItems",
async: false,
listName: "TestList",
CAMLViewFields: "<ViewFields><FieldRef Name='Title' /><FieldRef Name='First_x0020_Name' /></ViewFields>",
CAMLQuery: CamlQuery,
completefunc: function (xData, Status) {

$(xData.responseXML).SPFilterNode("z:row").each(function() {
var table = document.getElementById('cntryTable');
var row = table.insertRow(1);
var cell1 = row.insertCell(0);
var cell2 = row.insertCell(1);
cell1.innerHTML =$(this).attr("ows_Title");
cell2.innerHTML =$(this).attr("ows_First_x0020_Name");
});
}
});

}
generateexcel();

}

</script>

<table id="cntryTable" border="1″ style="display:none;">
<tbody>
<tr width='250px'>
<th>Title</th>
<th>First Name</th>
</tr>
</tbody>
</table>
<br/>

<script language="javascript" type="text/javascript">
function generateexcel() {
var x = document.getElementById('cntryTable').rows;
var xls = new ActiveXObject("Excel.Application");
xls.visible = true
xls.Workbooks.Add
for (i = 0; i < x.length; i++) {
var y = x[i].cells;
for (j = 0; j < y.length; j++) {
xls.cells(i + 1, j + 1).value = y[j].innerText;
}
}

}
</script>

Save this code and when you click on the button, it should appear like below:

export sharepoint list data to excel programmatically

If you want to retrieve individual records in different different sheet then you can modify the generateexcel() method like below:

<script language="javascript" type="text/javascript">
function generateexcel() {
var x = document.getElementById('cntryTable').rows;
var xls = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");

for (i = 0; i < x.length; i++) {
//xls.Workbooks.Add(z);
ExcelSheet.sheets.Add;

ExcelSheet.Application.Visible=true;
var y = x[i].cells;

var z=x[0].cells;

for (j = 0; j < y.length; j++) {
// xls.cells(i + 1, j + 1).value = y[j].innerText;
ExcelSheet.ActiveSheet.cells(1, j+1).value = z[j].innerText;
ExcelSheet.ActiveSheet.cells(2, j + 1).value = y[j].innerText;
}

}
xls.visible = true
}

</script>

This is how we can export selected records to excel in SharePoint 2013 using the JavaScript client object model and SPServices.

Export selected record to excel in SharePoint 2013 using JavaScript client object model

Here we got one requirement to export selected list items to excel in SharePoint 2013 JavaScript client object model. We can not use the default export to excel because it will export all the list view items.

we have used jQuery to export which works on Asynchronous mode. Put the below code in a script editor web part in the list view page.

<script type="text/javascript" src="https://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js"></script>

<input type='button' id='btbdiv1′ value='Generate Excel' onclick="GetProjectenGoedkeuren();"/>
</div>
<script language="javascript" type="text/javascript">
var projectItem1, projectItem2, projectItem3;
var field2, field1;
var countrvalue,i,id;
var tempctx, tempItem, fieldCollection, tempList;

function GetProjectenGoedkeuren()
{
var table = document.getElementById('cntryTable');
while (table.rows.length > 1) {
table.deleteRow(1);
}

var item;
i=0;
var qryString;

var context = SP.ClientContext.get_current();
var selectedItems = SP.ListOperation.Selection.getSelectedItems(context);
var list = context.get_web().get_lists().getByTitle("TestList");
qryString ="<View><Query><Where><In><FieldRef Name='ID' /><Values>";

for (item in selectedItems){
projectItem = list.getItemById(selectedItems[item].id);
projectItem1 = selectedItems[item].id;
qryString=qryString+"<Value Type='Text'>"+projectItem1+"</Value>";
}
qryString=qryString+"</Values></In></Where></Query></View>";
camlQuery =new SP.CamlQuery();
camlQuery.set_viewXml(qryString,'Include(Title,First_x0020_Name)');
this.objitems = list.getItems(camlQuery);
context.load(objitems);
context.executeQueryAsync(Function.createDelegate(this, this.createListItem), Function.createDelegate(this, this.onQueryFailed));
generateexcel();
}

//create a new item and load it
function createListItem(sender, args) {
var listItemEnumerator = objitems.getEnumerator();
while (listItemEnumerator.moveNext()) {
var oListItem = listItemEnumerator.get_current();
var table = document.getElementById('cntryTable');
var row = table.insertRow(1);
var cell1 = row.insertCell(0);
var cell2 = row.insertCell(1);

if(oListItem.get_item('Title'))
{
cell1.innerHTML =oListItem.get_item('Title');
}
else
{
cell1.innerHTML =";
}

if(oListItem.get_item('First_x0020_Name'))
{
cell2.innerHTML =oListItem.get_item('First_x0020_Name');
}
else
{
cell2.innerHTML =";
}
}
}

function onQueryFailed(sender, args) { alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace()); } </script>

<table id="cntryTable" border="1″ style="display:none;">
<tbody>
<tr width='250px'>
<th>Title</th>
<th>First Name</th>
</tr>
</tbody>
</table>
<br/>

<script language="javascript" type="text/javascript">
function generateexcel() {
var x = document.getElementById('cntryTable').rows;
var xls = new ActiveXObject("Excel.Application");
xls.visible = true
xls.Workbooks.Add
for (i = 0; i < x.length; i++) {
var y = x[i].cells;
for (j = 0; j < y.length; j++) {
xls.cells(i + 1, j + 1).value = y[j].innerText;
}
}
window.location.href="https://onlysharepoint2013.sharepoint.com/Lists/TestList/AllItems.aspx";
}
</script>

If you want to retrieve individual records in different different sheet then you can modify the generateexcel() method like below:

<script language="javascript" type="text/javascript">
function generateexcel() {
var x = document.getElementById('cntryTable').rows;
var xls = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");

for (i = 0; i < x.length; i++) {
//xls.Workbooks.Add(z);
ExcelSheet.sheets.Add;

ExcelSheet.Application.Visible=true;
var y = x[i].cells;

var z=x[0].cells;

for (j = 0; j < y.length; j++) {
// xls.cells(i + 1, j + 1).value = y[j].innerText;
ExcelSheet.ActiveSheet.cells(1, j+1).value = z[j].innerText;
ExcelSheet.ActiveSheet.cells(2, j + 1).value = y[j].innerText;
}

}
xls.visible = true
}
</script>

You may like following SharePoint tutorials:

Here we learned how to export sharepoint list data to excel programmatically using JavaScipt and SPServies in SharePoint Online or SharePoint 2013/2016.

    • >