Export Only SharePoint Filtered List Items To Excel

Introduction

This article demonstrates how to export SharePoint Filtered list items to Excel. SharePoint gives an option out of the box to export SharePoint views directly, however, if we apply any filter on the SharePoint view and then export the SharePoint list then instead of the filtered view we get all the results in the view in Excel. Hence, to just get those filtered items in Excel we add a content editor web part and link it with our code to export filtered items.

To export only filtered list items in a SharePoint view to Excel

Firstly create a .txt file and paste the following code in the txt file. You can name the file as ExportToExcel.txt.

  1. <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>  
  2. <script>  
  3.     function fnExcelReport() {  
  4.         $('.ms-listviewtable').css({  
  5.             'border-collapse''collapse',  
  6.             'border''1px solid #ddd'  
  7.         });  
  8.         $('.ms-listviewtable tr td').css({  
  9.             'border''1px solid #ddd'  
  10.         });  
  11.         var tab_text = $('.ms-listviewtable')[0].outerHTML;  
  12.         debugger;  
  13.         $(tab_text).find("tr td:first-child").remove();  
  14.         tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, ""); //remove if u want links in your table  
  15.         tab_text = tab_text.replace(/<img[^>]*>/gi, ""); // remove if u want images in your table  
  16.         tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params  
  17.         var ua = window.navigator.userAgent;  
  18.         var msie = ua.indexOf("MSIE ");  
  19.         if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer  
  20.         {  
  21.             txtArea1.document.open("txt/html""replace");  
  22.             txtArea1.document.write(tab_text);  
  23.             txtArea1.document.close();  
  24.             txtArea1.focus();  
  25.             sa = txtArea1.document.execCommand("SaveAs"true"ActionPoints.xls");  
  26.         } else //other browser not tested on IE 11  
  27.             tab_text = excelExportHtml(tab_text, true)  
  28.         sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  
  29.         return (sa);  
  30.     }  
  31.   
  32.     function excelExportHtml(table, includeCss) {  
  33.         var html = "<html><head>";  
  34.         html += "</head><body>" + table + "</body></html>";  
  35.         return html;  
  36.     }  
  37. </script>  
  38.   
  39. <body> <input type="button" value="Export To Excel" onclick="fnExcelReport();" /> <iframe id="txtArea1"></iframe> </body>  

Now, upload this file to Site Assets library in SharePoint.

Go to the list view in SharePoint where you would like to filter the list view and then click on Edit Page as shown in the screenshot below.

SharePoint

Then, click on Add a Web part >> Media And Content >> Content Editor and then click on Add button as shown in the image below.

SharePoint

After adding the web part click on the Down Arrow and then click on Edit Web Part as shown in the screenshot below.

SharePoint

Scroll to the right of the page and in the Content Link property paste the link of the txt file that was uploaded to the Site Assets Gallery, namely ExporttoExcel.txt and then click OK.

SharePoint

Now, you will see  Export to Excel button on the list. Now if you filter the list and then click on “Export to Excel” button Excel will be downloaded with only filtered view records that are visible on the screen.

Summary

In this article, we discussed how we can export SharePoint Filtered Items to Excel. This is of great help as we can directly export filtered view items which are currently not provided by SharePoint out of the box features.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now