none
List view web part with Date filter is not showing data after Exporting it to Excel

    질문

  • Hi,

    I have added 2 date filter and modified the page so that it filters the List data based on Date range. Now  when I export the data to Excel, no data is shown except Headers.

    Thanks

    Chitra

    2018년 5월 7일 월요일 오전 5:06

답변

  • Hi Chitra,

    We create a new View with whatever filter you currently have and then exporting to spread sheet will only contain the relevant items.

    Or we can also use JavaScript to export only SharePoint filtered List Items to Excel, the following article for your reference:

    https://www.c-sharpcorner.com/article/export-to-excel-sharepoint-filtered-list-items-only/

    Code:

    <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
    <script>
    function fnExcelReport() {
    	$('.ms-listviewtable').css({
    		'border-collapse': 'collapse',
    		'border': '1px solid #ddd'
    	});
    	$('.ms-listviewtable tr td').css({
    		'border': '1px solid #ddd'
    	});
    	var tab_text = $('.ms-listviewtable')[0].outerHTML;
    	debugger;
    	$(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, "ActionPoints.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);
    }
    
    function excelExportHtml(table, includeCss) {
    	var html = "<html><head>";
    	html += "</head><body>" + table + "</body></html>";
    	return html;
    }
    </script>
    
    <body> <input type="button" value="Export To Excel" onclick="fnExcelReport();" /> <iframe id="txtArea1"></iframe> </body>
    

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • 답변으로 표시됨 Chitrakala 2018년 5월 9일 수요일 오전 9:39
    2018년 5월 9일 수요일 오전 1:14
    중재자

모든 응답

  • Hi Chitra,

    We create a new View with whatever filter you currently have and then exporting to spread sheet will only contain the relevant items.

    Or we can also use JavaScript to export only SharePoint filtered List Items to Excel, the following article for your reference:

    https://www.c-sharpcorner.com/article/export-to-excel-sharepoint-filtered-list-items-only/

    Code:

    <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
    <script>
    function fnExcelReport() {
    	$('.ms-listviewtable').css({
    		'border-collapse': 'collapse',
    		'border': '1px solid #ddd'
    	});
    	$('.ms-listviewtable tr td').css({
    		'border': '1px solid #ddd'
    	});
    	var tab_text = $('.ms-listviewtable')[0].outerHTML;
    	debugger;
    	$(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, "ActionPoints.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);
    }
    
    function excelExportHtml(table, includeCss) {
    	var html = "<html><head>";
    	html += "</head><body>" + table + "</body></html>";
    	return html;
    }
    </script>
    
    <body> <input type="button" value="Export To Excel" onclick="fnExcelReport();" /> <iframe id="txtArea1"></iframe> </body>
    

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • 답변으로 표시됨 Chitrakala 2018년 5월 9일 수요일 오전 9:39
    2018년 5월 9일 수요일 오전 1:14
    중재자
  • Hi Dennis,

    This script is useful in exporting the webpart content to Excel. Thanks for the solution.

    However I see this is adding additional dialogue boxes .

    1. It creates a table in the Page which is not required. Is there any way this can be removed.

    2. It says "Do you want to allow this website to open an app on your computer". If said yes, it tries to open Microsoft store for choosing the app else returns to navigation cancel page(res://ieframe.dll/navcancl.htm).

    Thanks

    Chitra


    • 편집됨 Chitrakala 2018년 5월 9일 수요일 오전 9:41
    2018년 5월 9일 수요일 오전 9:39
  • Hi Chitra,

    Replace the code below

    <body><input type="button" value="Export To Excel" onclick="fnExcelReport();" /><iframe id="txtArea1"></iframe></body>

    with

    <body><input type="button" value="Export To Excel" onclick="fnExcelReport();" /><div style="display:none;"><iframe id="txtArea1"></iframe><div></body>

    Or we can using jQuery plugin to export table to excel. 

    https://www.c-sharpcorner.com/blogs/exporting-html-table-to-excel-using-jquery

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    2018년 5월 9일 수요일 오전 10:08
    중재자
  • Hi Dennis,

    Is there any way where it is directly saved in the format of .xlsx than .xls . I tried updating the filename to .xlsx , but it didn't work.

    Also the script doesn't work in Microsoft edge. In chrome it doesn't save with the specified file name and downloads with default name "download.xls" . Is there any work around for this. User may not be using the IE browser all the time.

    Thanks

    Chitra

    2018년 6월 15일 금요일 오전 4:52