Export SharePoint online list to Excel

Question

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 ?

Answer 1

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

Answer 2

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>

Answer 3

With Powershell you can both get SharePoint list items (e.g. using Get-PnPListItems) and create an Excel file in xlsx format using the Export-Excel command. Here is a question and answer how to get large SharePoint lists.