Menu
I have a webpage that has 3 tables and I'd like to export all 3 of them to same excel file. I'd like each table in its own sheet, but having them all on the same sheet is ok as well. After some googling, all I've seen is exporting one table to one excel sheet.
974
var tablesToExcel = (function () {
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>'
, templateend = '</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head>'
, body = '<body>'
, tablevar = '<table>{table'
, tablevarend = '}</table>'
, bodyend = '</body></html>'
, worksheet = '<x:ExcelWorksheet><x:Name>'
, worksheetend = '</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>'
, worksheetvar = '{worksheet'
, worksheetvarend = '}'
, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
, wstemplate = ''
, tabletemplate = '';
return function (table, name, filename) {
var tables = table;
for (var i = 0; i < tables.length; ++i) {
wstemplate += worksheet + worksheetvar + i + worksheetvarend + worksheetend;
tabletemplate += tablevar + i + tablevarend;
}
var allTemplate = template + wstemplate + templateend;
var allWorksheet = body + tabletemplate + bodyend;
var allOfIt = allTemplate + allWorksheet;
var ctx = {};
for (var j = 0; j < tables.length; ++j) {
ctx['worksheet' + j] = name[j];
}
for (var k = 0; k < tables.length; ++k) {
var exceltable;
if (!tables[k].nodeType) exceltable = document.getElementById(tables[k]);
ctx['table' + k] = exceltable.innerHTML;
}
//document.getElementById("dlink").href = uri + base64(format(template, ctx));
//document.getElementById("dlink").download = filename;
//document.getElementById("dlink").click();
window.location.href = uri + base64(format(allOfIt, ctx));
}
})();
And the HTML
<html>
<head>
<title>JS to Excel</title>
</head>
<body>
<table id="1">
<tr><td>Hi</td></tr>
<tr><td>Hey</td></tr>
<tr><td>Hello</td></tr>
</table>
<table id="2">
<tr><td>Night</td></tr>
<tr><td>Evening</td></tr>
<tr><td>Nite</td></tr>
</table>
<a id="dlink" style="display:none;"></a>
<input type="button" onclick="tablesToExcel(['1', '2'], ['first', 'second'], 'myfile.xls')" value="Export to Excel">
<script src="~/Views/JS/JSExcel.js" type="text/javascript"></script>
</body>
</html>
NOTE: this doesn't work on IE ('data too small' error) and on Firefox both tables are put on the same sheet.
Credit also to this thread - HTML Table to Excel Javascript
177
//function 1
$scope.exportXlsSheets = function (datasets) {
var xlsString = '<?xml version="1.0"?>\
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
for(var key in dict){
var arr_of_entities= dict[key].arr;
xlsString += $scope.getSheet(arr_of_entities);
}
xlsString += '</ss:Workbook>';
var a = document.createElement('a');
a.href = 'data:application/vnd.ms-excel;base64,' + $scope.base64(xlsString);
a.target = '_blank';
a.download = 'test1.xls';
document.body.appendChild(a);
a.click();
}
$scope.base64 = function (s) {
return window.btoa(unescape(encodeURIComponent(s)))
}
//function 2
$scope.getSheet = function (sheetName, entities) {
var res = '<ss:Worksheet ss:Name="'+sheetName+"></ss:Worksheet>\
<ss:Table>';
var row = '<ss:Row>';
for ( i = 0; i < entities.length; i++) {
var entity = entities[i];
row += '<ss:Cell>\
<ss:Data ss:Type="String">'+entity.value +'</ss:Data>\
</ss:Cell>';
}
row += '</ss:Row>';
res += row;
return res;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
