0

I know there is an OOTB functionality to export list into .iqy file that can be opened in Excel.

But here we want to export the list using JSOM/REST to a file which has .xls format. I have gone through few articles online but could find any way to fulfill it, everywhere the code is either to export to csv or the code behind "Export to Excel" button which downloads the .iqy file.

Is there anyway I can achieve this ?

0

Yes, you can use Office XML SDK to create xslx files.

  • Can you post a sample code as well to export the list to xls file using JSOM/REST ? It's new for me and I am not finding a way to start with Office XML SDK. Also please note that I am trying to achieve it by using Javascript/REST because I have seen most of the examples with C#. – Ravi Feb 25 at 4:56
0

You can get list items using REST API and jQuery and export data to excel, the following code for your reference.

<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
function exportToExcel(){
    var listName="CustomList";
    $.ajax({
        url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getByTitle('"+listName+"')/items",
        type: "GET",
        headers: {
            "Accept": "application/json;odata=verbose",
        },
        success: function (data) {
            var htmlTable="<table><thead><tr><th>Title</th></tr></thead><tbody>";
            $.each(data.d.results,function(i,item){
                htmlTable+="<tr><td>"+item.Title+"</td></tr>";
            });
            htmlTable+="</tbody></table>"
            fnExcelReport(htmlTable);
        },
        error: function (data) {
            alert("Error");
        }
    });
}
function fnExcelReport(htmlTable) {      
    var tab_text = htmlTable;
    $(tab_text).find("tr td:first-child").remove();  
    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  
    {  
        txtArea1.document.open("txt/html", "replace");  
        txtArea1.document.write(tab_text);  
        txtArea1.document.close();  
        txtArea1.focus();  
        sa = txtArea1.document.execCommand("SaveAs", true, "ListItem.xls");  
    } else //other browser not tested on IE 11  
        tab_text = excelExportHtml(tab_text, true)  
    sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  
    return (sa);  
}  
</script>
<input type="button" value="Export to Excel" onclick="exportToExcel()"/><iframe id="txtArea1" style="display:none"></iframe>

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.