1

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.

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/

0

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/

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.