umidjons/export-html-table-to-excel.md

Created March 2, 2015 13:32

Star () You must be signed in to star a gist
Fork () You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/umidjons/352da2a4209691d425d4.js"></script>
Save umidjons/352da2a4209691d425d4 to your computer and use it in GitHub Desktop.

Download ZIP

Export HTML table to Excel in AngularJS

Raw

export-html-table-to-excel.md

Export HTML table to Excel in AngularJS

myApp.factory('Excel',function($window){
		var uri='data:application/vnd.ms-excel;base64,',
			template='<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
			base64=function(s){return $window.btoa(unescape(encodeURIComponent(s)));},
			format=function(s,c){return s.replace(/{(\w+)}/g,function(m,p){return c[p];})};
		return {
			tableToExcel:function(tableId,worksheetName){
				var table=$(tableId),
					ctx={worksheet:worksheetName,table:table.html()},
					href=uri+base64(format(template,ctx));
				return href;
			}
		};
	})
	.controller('MyCtrl',function(Excel,$timeout){
	  $scope.exportToExcel=function(tableId){ // ex: '#my-table'
			$scope.exportHref=Excel.tableToExcel(tableId,'sheet name');
			$timeout(function(){location.href=$scope.fileData.exportHref;},100); // trigger download
		}
	});

How to use:

	<button class="btn btn-link" ng-click="exportToExcel('#table1')">
		<span class="glyphicon glyphicon-share"></span> Export to Excel
	</button>

prasanthiperimena commented Apr 6, 2017

$scope.exportToExcel=function(tableId){
var exportHref=Excel.tableToExcel(tableId,'report');
var a = document.createElement('a');
a.href = exportHref;
a.download = 'Exported_Table.xls';
a.click();
}

After using the above code ,I am getting warning message before opening the file saying "The file could be uncorrupted or unsafe,Unless you trust its source, don't open it. Do you want to open it anyway"?
If I m clicking on yes, file is getting opened.
Can any one please help me, how to resolve this warning message before opening the file

valeriopisapia commented May 10, 2017

I have the same problem of @prasanthiperimena.
Any feedbacks?

aayushpatidar commented Jul 11, 2017

This is working great in Chrome but not in Internet Explorer and Microsoft Edge. It is also not working in MAC OS. Please resolve the issue as soon as possible.

BarryPiccinni commented Jul 13, 2017

Hi, this works very well, but I'm having trouble getting it to work for larger tables. It works perfectly up to about 2100 rows. Any bigger and the download fails. Any thoughts on how to get around this?

sandeepacharya93 commented Aug 11, 2017

@CliffyMk ,
Your code works after including id in table instead of div .
eg:

Instead of

.
and if the downloaded file doesnt open in apache open office just try changing extension name of your file to .xls

ashokbabumiriyala commented Aug 29, 2017

it's working fine with chrome but IE it's not working.
suggest me any one why it's not working.

here is my code

   var uri = 'data:application/vnd.ms-excel;base64,',
           template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
           base64 = function (s) { return $window.btoa(unescape(encodeURIComponent(s))); },
           format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) };
        var exportHref = tableToExcel($("#tbl_pcmreport"), $("#rptName"));
        function tableToExcel(tableId, worksheetName) {
                var table = $(tableId),
                    ctx = { worksheet: worksheetName, table: table.html() },
                    href = uri + base64(format(template, ctx));
                return href;
           }                     
        $timeout(function () { location.href = exportHref; }, 100);

VanarajV commented Oct 16, 2017

For IE please replace with window.navigator.msSaveOrOpenBlob(blob, name);

Anirudhk94 commented Oct 20, 2017

Hi,

I want to export a table into xls. The size of the file is around 4MB - 5MB.
The script works fine in Firefox. But in Chrome, the export to excel button is redirecting me to about:blank page.

Any idea why?

Anil-bohorya commented Jan 15, 2018 •

edited

Loading

getting issue: -

'The file format and extension of 'abc.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?' while opening downloaded file.
Background is totally white. Not showing grids with black border. Do I need to give styling inside code?

Please help.

foyzulkarim commented Jan 24, 2018

working code snippet (use raw javascript instead of jquery)

tableToExcel: function (tableId, worksheetName) { var table = document.getElementById(tableId); var ctx = { worksheet: worksheetName, table: table.innerHTML }; var href = uri + base64(format(template, ctx)); return href; }

but thanks for the snippet.

emanzuetaj commented Feb 2, 2018 •

edited

Loading

AngularJS working version with all of the above

    angular
        .module('app')
        .factory('excel', ['$window', '$document', function($window, $document) {
            var uri = 'data:application/vnd.ms-excel;base64,';
            var template =
                '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>';
            var base64 = function(s) { return $window.btoa(unescape(encodeURIComponent(s))); };
            var format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }); };
            return {
                tableToExcel: function(tableId, worksheetName, fileName) {
                    var table = angular.element($document[0].getElementById(tableId));
                    var ctx = { worksheet: worksheetName, table: table.html() };
                    var browser = window.navigator.appVersion;
                    if ((browser.indexOf('Trident') !== -1 && browser.indexOf('rv:11') !== -1) ||
                        (browser.indexOf('MSIE 10') !== -1)) {
                        var builder = new window.MSBlobBuilder();
                        builder.append(uri + format(template, ctx));
                        var blob = builder.getBlob('data:application/vnd.ms-excel');
                        window.navigator.msSaveOrOpenBlob(blob, fileName);
                    } else {
                        var link = $document[0].createElement('a');
                        link.download = fileName;
                        link.href = uri + base64(format(template, ctx));
                        link.click();
                    }
                }
            };
        }]);

Can anyone tell me if there's a way to get rid of the security pop up? It's Microsoft's desired behavior per this post

Anyone able to get custom CSS working for this solution?
Update: answering my own CSS question... CSS must be inline for it to persist

ankitpachori commented Feb 6, 2018

Dropdowns are also coming in the excel sheet,which is not expected..........

Can anyone guide me on this

neerajchhatani commented Feb 6, 2018

Dropdowns are also coming in the excel sheet,which is not expected..........

Can anyone guide me on this

rahiakela commented Feb 7, 2018 •

edited

Loading

HI,
Could you give me some reference of html template for Excel that you have used above example. So that I can customise it according to my requirement.

Thanks

vamsikrishna007 commented Feb 15, 2018

Neerajchhatani

Just include only column names and their values. If you are using any dropdown, then take the value of that which is stored in the Scope variable.

royanon commented Apr 9, 2018 •

edited

Loading

This work in IE, Edge, FF and Chrome. Test on IE11.309.16299.0, Edge 41.16299.248.0, FF 59.2.3, Chrome 65.0.3325.181. Hope this help.
But it doesn't work in chrome when the file exceed 2Mb. It's very easy to hit 2Mb when the html consists of a lot of html.

// factory MyApp.factory('Excel', ["$window", function ($window) { var uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body><table>{table}</table></body></html>', base64 = function (s) { return $window.btoa(unescape(encodeURIComponent(s))); }, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }; return { tableToExcel: function (tableId, worksheetName, isBase64) { var table = document.querySelector(tableId); var ctx = { worksheet: worksheetName, table: table.innerHTML }; var href = ""; if (isBase64) { href = uri + base64(format(template, ctx)); } else { href = uri + format(template, ctx); } return href; } }; }]); // controller MyApp.controller('MyController', ["$scope", "$timeout", "Excel", function ($scope, $timeout, Excel) { $scope.exportToExcel = function (tableId) { var browser = window.navigator.appVersion; var useBlobBuilder = false; // IE, Edge if ((browser.indexOf('Trident') !== -1 && browser.indexOf('rv:11') !== -1) || (browser.indexOf('MSIE 10') !== -1) || (document.documentMode || /Edge/.test(browser))) { useBlobBuilder = true; $scope.exportHref = Excel.tableToExcel(tableId, 'sheet name', false); } else { //FF, Chrome useBlobBuilder = false; $scope.exportHref = Excel.tableToExcel(tableId, 'sheet name', true); } $timeout(function () { var fileName = "testing.xls"; if (useBlobBuilder) { // IE, Edge var builder = new window.MSBlobBuilder(); builder.append($scope.exportHref); var blob = builder.getBlob('data:application/vnd.ms-excel'); window.navigator.msSaveOrOpenBlob(blob, fileName); } else { //FF, Chrome var link = document.createElement('a'); link.download = fileName; link.href = $scope.exportHref; if (document.body != null) { document.body.appendChild(link); } link.click(); link.remove(); } }, 100); } }]);

saravanajagan commented May 18, 2018

Working fine in Firefox. But not fully working in chrome... if the file size is more its redirecting to blank page.

Any solution?

paunkumar commented Jun 4, 2018

Working fine.
how to change as filename.

muadib commented Mar 30, 2019

if the code for download with a custom filename don't work in Firefox, change the else part in tableToExcel with:

var link = document.createElement('a');
link.setAttribute("type", "hidden"); // hide link
link.download = fileName;
link.href = uri + base64(format(template, ctx));
document.body.appendChild(link); //Required for Firefox
link.click();
link.remove();

And add in template, before </head>
<style></style>
For specify thousands separator and decimal separator.

sri15941 commented Aug 12, 2019

i am getting only current page data downloaded. but i want all pages data to download.
what can i do.....?

Noushadkanniyan commented Jan 22, 2021

how to change extenction .xls to xlsx

umidjons/export-html-table-to-excel.md

Export HTML table to Excel in AngularJS

prasanthiperimena commented Apr 6, 2017

valeriopisapia commented May 10, 2017

aayushpatidar commented Jul 11, 2017

BarryPiccinni commented Jul 13, 2017

sandeepacharya93 commented Aug 11, 2017

ashokbabumiriyala commented Aug 29, 2017

VanarajV commented Oct 16, 2017

Anirudhk94 commented Oct 20, 2017

Anil-bohorya commented Jan 15, 2018 • edited Loading

foyzulkarim commented Jan 24, 2018

emanzuetaj commented Feb 2, 2018 • edited Loading

ankitpachori commented Feb 6, 2018

neerajchhatani commented Feb 6, 2018

rahiakela commented Feb 7, 2018 • edited Loading

vamsikrishna007 commented Feb 15, 2018

royanon commented Apr 9, 2018 • edited Loading

saravanajagan commented May 18, 2018

paunkumar commented Jun 4, 2018

muadib commented Mar 30, 2019

sri15941 commented Aug 12, 2019

Noushadkanniyan commented Jan 22, 2021

Anil-bohorya commented Jan 15, 2018 •

edited

Loading

emanzuetaj commented Feb 2, 2018 •

edited

Loading

rahiakela commented Feb 7, 2018 •

edited

Loading

royanon commented Apr 9, 2018 •

edited

Loading