how to export sharepoint list items to excel

Question

I want to export sharepoint 2013 list items to excel on button click but not through OOTB "export to Excel" which is available on Ribbon of the list.

Because i have to provide a button to export list items in different "webpart page" or in "content editor webpart".

JSOM/REST is preferable.

Answer 1

There's a nice blog post describing how to do this using simply by grabbing REST query with Fiddler. Basically you catch the query when you press the button Export to excel.

Source: https://howtodowithsharepoint.wordpress.com/2015/05/23/sharepoint-2013-export-to-excel-using-rest-api/

Answer 2

You can use datatables.net for this. IT will render you list and then give you options to export as excel,csv,pdf and much more without much effort.

Check this link:

https://www.dsouzajoel.com/2015/12/17/convert-sharepoint-list-to-pdfcsvexcel-using-javascript/

Answer 3

We have to first create table object and then export it to excel using the function given below

this function is to export html table to excel

function fnExcelReport(tbl)
{
    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = tbl;//document.getElementById('headerTable'); // id of table

    for(j = 0 ; j < tab.rows.length ; j++) 
    {     
        tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text=tab_text+"</table>";
    tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
/*var _iframe=document.createElement("iframe");
_iframe.setAttribute("id","txtArea1");
_iframe.document.open("txt/html","replace");
        _iframe.document.write(tab_text);
        _iframe.document.close();
        _iframe.focus(); 
        sa=_iframe.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");*/
document.body.innerHTML = '<iframe id="txtArea1" style="display:none"></iframe>';

        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}

Below script is to get items from the list and then create table object and pass it to the function we just created.

var clientContext = new SP.ClientContext("http://url/");
    var oList = clientContext.get_web().get_lists().getByTitle('Configurations');

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml('<View><Query><Where><Geq><FieldRef Name=\'ID\'/>' + 
        '<Value Type=\'Number\'>1</Value></Geq></Where></Query><RowLimit>10</RowLimit></View>');
    this.collListItem = oList.getItems(camlQuery);

    clientContext.load(collListItem);

    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));        

function onQuerySucceeded(sender, args) {

    var listItemInfo = '';

    var listItemEnumerator = collListItem.getEnumerator();
        listItemInfo += '<tr><td></td><td></td><td></td></tr>'
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '<tr><td> ' + oListItem.get_id() + 
            '</td><td>' + oListItem.get_item('Title') + 
            '</td><td>' + oListItem.get_item('Value')+'</td></tr>';
    }
//listItemInfo +='</table>';
var tbl_obj=document.createElement("TABLE");
tbl_obj.innerHTML=listItemInfo;
tbl_obj.setAttribute("id","headerTable");

    //console.log(listItemInfo.toString());
fnExcelReport(tbl_obj);
}

function onQueryFailed(sender, args) {

    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

you can wrap the above code in a function and call on button click.