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

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 item to export. Due to some restrictions, we can not use any server-side code, we can use only client-side object model code like JavaScript client object model or jQuery object model etc.

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 selected record to excel in SharePoint 2013 using JavaScript client object model and SPServices

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

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 SPServices SharePoint 2013/2010 tutorials:

Hope this will be helpful to export selected record to excel in SharePoint 2013 using JavaScript client object model and SPServices.

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope

Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

  • Stater says:

    Is it possible to make without activex control

  • >