Skip to content

Instantly share code, notes, and snippets.

@umidjons

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

Created March 2, 2015 13:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save umidjons/352da2a4209691d425d4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
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>
@vamsikrishna007

vamsikrishna007 commented Feb 15, 2018

Copy link
Copy Markdown

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

royanon commented Apr 9, 2018 •
edited
Loading

Copy link
Copy Markdown

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"><!--[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, 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

saravanajagan commented May 18, 2018

Copy link
Copy Markdown

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

Any solution?

@paunkumar

paunkumar commented Jun 4, 2018

Copy link
Copy Markdown

Working fine.
how to change as filename.

@muadib

muadib commented Mar 30, 2019

Copy link
Copy Markdown

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><!-- table {mso-displayed-decimal-separator: "\.";mso-displayed-thousand-separator: "\,";} --></style>
For specify thousands separator and decimal separator.

@sri15941

sri15941 commented Aug 12, 2019

Copy link
Copy Markdown

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

@Noushadkanniyan

Noushadkanniyan commented Jan 22, 2021

Copy link
Copy Markdown

how to change extenction .xls to xlsx

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment