I have the following code which I call on a button click and which helps me pass an html table id and have it downloaded in a single Excel workbook. This works fine, but I want to pass multiple table ids to get the data of different tables in different sheets of the same Excel workbook. I am unable to modify this function to address that issue.
Moreover, I want to retain similar kind of formatting and use the customized file name as I have used here. Can anyone help me? Please find my code below:
<script>
function fnExcelReport()
{
var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
var textRange; var j=0;
tab = document.getElementById('data'); // id of table : I want to pass more than one ids here
for(j = 0 ; j < tab.rows.length ; j++)
{
tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
//tab_text=tab_text+"</tr>";
}
tab_text=tab_text+"</table>";
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();
var e = document.getElementById("configselect");
var strUser = e.options[e.selectedIndex].text;
var f = document.getElementById("configmonth");
var strUser1 = f.options[e.selectedIndex].text;
var filename = strUser+"_"+strUser1+"_"+document.getElementById('configkpi').value+"_"+document.getElementById('configyear').value+".xls";
//alert(filename);
sa=txtArea1.document.execCommand("SaveAs",true,filename);
}
// else //other browser not tested on IE 11
// sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
// return (sa);
else {//other browser
var a = document.createElement('a');
var data_type = 'data:application/vnd.ms-excel';
var table_div = tab_text; //Your tab_text
var table_html = table_div.replace(/ /g, '%20');
//alert(table_html)
a.href = data_type + ', ' + table_html;
//setting the file name
var e = document.getElementById("configselect");
var strUser = e.options[e.selectedIndex].text;
var f = document.getElementById("configmonth");
var strUser1 = f.options[e.selectedIndex].text;
var filename = strUser+"_"+strUser1+"_"+document.getElementById('configkpi').value+"_"+document.getElementById('configyear').value+".xls";
a.download = filename;
//triggering the function
a.click();
}
return (sa);
}
I have the following code which I call on a button click and which helps me pass an html table id and have it downloaded in a single Excel workbook. This works fine, but I want to pass multiple table ids to get the data of different tables in different sheets of the same Excel workbook. I am unable to modify this function to address that issue.
Moreover, I want to retain similar kind of formatting and use the customized file name as I have used here. Can anyone help me? Please find my code below:
<script>
function fnExcelReport()
{
var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
var textRange; var j=0;
tab = document.getElementById('data'); // id of table : I want to pass more than one ids here
for(j = 0 ; j < tab.rows.length ; j++)
{
tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
//tab_text=tab_text+"</tr>";
}
tab_text=tab_text+"</table>";
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();
var e = document.getElementById("configselect");
var strUser = e.options[e.selectedIndex].text;
var f = document.getElementById("configmonth");
var strUser1 = f.options[e.selectedIndex].text;
var filename = strUser+"_"+strUser1+"_"+document.getElementById('configkpi').value+"_"+document.getElementById('configyear').value+".xls";
//alert(filename);
sa=txtArea1.document.execCommand("SaveAs",true,filename);
}
// else //other browser not tested on IE 11
// sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
// return (sa);
else {//other browser
var a = document.createElement('a');
var data_type = 'data:application/vnd.ms-excel';
var table_div = tab_text; //Your tab_text
var table_html = table_div.replace(/ /g, '%20');
//alert(table_html)
a.href = data_type + ', ' + table_html;
//setting the file name
var e = document.getElementById("configselect");
var strUser = e.options[e.selectedIndex].text;
var f = document.getElementById("configmonth");
var strUser1 = f.options[e.selectedIndex].text;
var filename = strUser+"_"+strUser1+"_"+document.getElementById('configkpi').value+"_"+document.getElementById('configyear').value+".xls";
a.download = filename;
//triggering the function
a.click();
}
return (sa);
}
- 2 Can You provide html also ? – Chinmoy Samanta Commented May 29, 2018 at 10:23
- Not 100% sure what your doing but I would suggest you use CSV format instead. – user7886229 Commented May 30, 2018 at 4:55
- 1 Have you tried using TableExport.js ? tableexport.v5.travismclarke. – psyborg.eth Commented Jun 4, 2018 at 12:32
2 Answers
Reset to default 13 +25You can create an Excel workbook with multiple sheets and with formatting (including colspan and rowspan) using SheetJS. Here is a discussion thread and examples posted in that thread:
1) Workbook with Multiple Sheets
- Library: https://github./SheetJS/js-xlsx
- Discussion: https://github./SheetJS/js-xlsx/issues/664
- Demo (Click
Excellink): https://jsfiddle/97ajn9wm/1/ (by reviewher)
I've moved reviewher's example code from JSFiddle to Stack Overflow for easier viewing. Run the code snippet and then click the resulting Excel link to download an Excel file with two sheets.
function prepareTable(i) {
var str = "",
header = "",
graphImg;
header = '<html><h2 style="text-align:center;">Google' + i + '</h2>';
str = '<table border="1">'
+'<tr><td style="text-align:center" colspan="6">Yahoo' + i + '</td></tr>'
+'<tr><td style="font-weight:bold" colspan="6">(2017.03.20)</td></tr>'
+'<thead>'
+' <tr style="background-color:#788496; color: #ffffff">'
+' <th scope="col" rowspan="2">'
+' <div>Yahoo</div>'
+' </th>'
+' <th scope="col">'
+' <div class="tar">Yahoo(2017-01)</div>'
+' </th>'
+' <th scope="col" colspan="2">'
+' <div class="tar">Yahoo(2016-12)</div>'
+' </th>'
+' <th scope="col" colspan="2">'
+' <div class="tar">Yahoo(2016-12)</div>'
+' </th>'
+' </tr>'
+' <tr style="background-color:#788496; color: #ffffff">'
+' <th height="40" align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' </tr>'
+'</thead>'
+' <tbody>'
+' <tr style="text-align: right">'
+' <td style="padding:0 20px 0 0">'
+' <div>NAME</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210%</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210%</div>'
+' </td>'
+' </tr>'
+' </tbody>'
+'</table></html>';
return header + str;
}
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
function doExcel1 () {
var blob,
wb = {SheetNames:[], Sheets:{}};
var ws1 = XLSX.read(prepareTable(1), {type:"binary"}).Sheets.Sheet1;
wb.SheetNames.push("Sheet1"); wb.Sheets["Sheet1"] = ws1;
var ws2 = XLSX.read(prepareTable(2), {type:"binary"}).Sheets.Sheet1;
wb.SheetNames.push("Sheet2"); wb.Sheets["Sheet2"] = ws2;
console.log(ws1); console.log(ws2); console.log(wb);
blob = new Blob([s2ab(XLSX.write(wb, {bookType:'xlsx', type:'binary'}))], {
type: "application/octet-stream"
});
saveAs(blob, "test.xlsx");
}<script src="https://cdnjs.cloudflare./ajax/libs/xlsx/0.10.3/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare./ajax/libs/FileSaver.js/1.3.3/FileSaver.min.js"></script>
<a href="javascript:" class="btn_style1 excel" onclick="doExcel1()"><span>Excel</span></a>2) Worksheet with Formatting
Here is another demo the same GitHub thread that shows multiple colspan, multiple rowspan, background color, font color, font size, etc. This example is from HeroSony on GitHub.
As above, click Run code snippet and then click the resulting Excel link to download the Excel file.
- Library: https://github./SheetJS/js-xlsx (same as above)
- Discussion: https://github./SheetJS/js-xlsx/issues/664 (same as above)
- Demo (Click
Excellink): https://jsfiddle/Herosony/97ajn9wm/ (by HeroSony)
function prepareTable() {
var str = "",
header = "",
graphImg;
header = '\uFEFF<h2 style="text-align:center;">Google</h2>';
str = '<table border="1">'
+'<tr><td style="text-align:center" colspan="6">Yahoo</td></tr>'
+'<tr><td style="font-weight:bold" colspan="6">(2017.03.20)</td></tr>'
+'<thead>'
+' <tr style="background-color:#788496; color: #ffffff">'
+' <th scope="col" rowspan="2">'
+' <div>Yahoo</div>'
+' </th>'
+' <th scope="col">'
+' <div class="tar">Yahoo(2017-01)</div>'
+' </th>'
+' <th scope="col" colspan="2">'
+' <div class="tar">Yahoo(2016-12)</div>'
+' </th>'
+' <th scope="col" colspan="2">'
+' <div class="tar">Yahoo(2016-12)</div>'
+' </th>'
+' </tr>'
+' <tr style="background-color:#788496; color: #ffffff">'
+' <th height="40" align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' <th align="right">'
+' <div>Yahoo</div>'
+' </th>'
+' </tr>'
+'</thead>'
+' <tbody>'
+' <tr style="text-align: right">'
+' <td style="padding:0 20px 0 0">'
+' <div>NAME</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210%</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210</div>'
+' </td>'
+' <td style="width: 150px;">'
+' <div>311,210%</div>'
+' </td>'
+' </tr>';
+' </tbody>'
+'</table>';
return header + str;
}
function doExcel1 () {
var blob,
template = prepareTable();
blob = new Blob([template], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
});
saveAs(blob, "test.xls");
}<script src="https://cdnjs.cloudflare./ajax/libs/FileSaver.js/1.3.3/FileSaver.min.js"></script>
<a href="javascript:" class="btn_style1 excel" onclick="doExcel1()"><span>Excel</span></a>As per your code you have used data:application/vnd.ms-excel
You can do something like this .
JS Function
var tablesToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-:office:spreadsheet">'
+ '<DocumentProperties xmlns="urn:schemas-microsoft-:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
+ '<Styles>'
+ '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
+ '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
+ '</Styles>'
+ '{worksheets}</Workbook>'
, tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
, tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
, 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 function(tables, wsnames, wbname, appname) {
var ctx = "";
var workbookXML = "";
var worksheetsXML = "";
var rowsXML = "";
for (var i = 0; i < tables.length; i++) {
if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
for (var j = 0; j < tables[i].rows.length; j++) {
rowsXML += '<Row>'
for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;
var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;
ctx = { attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date')?' ss:StyleID="'+dataStyle+'"':''
, nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'
, data: (dataFormula)?'':dataValue
, attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''
};
rowsXML += format(tmplCellXML, ctx);
}
rowsXML += '</Row>'
}
ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
worksheetsXML += format(tmplWorksheetXML, ctx);
rowsXML = "";
}
ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
workbookXML = format(tmplWorkbookXML, ctx);
console.log(workbookXML);
var link = document.createElement("A");
link.href = uri + base64(workbookXML);
link.download = wbname || 'Workbook.xls';
link.target = '_blank';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
})();
HTML (sample html code for demonstration)
<table id="tbl1" class="table2excel">
<tr>
<td>Product</td>
<td>Price</td>
<td>Available</td>
<td>Count</td>
</tr>
<tr>
<td>Bred</td>
<td>1
</td>
<td>2
</td>
<td>3
</td>
</tr>
<tr>
<td>Butter</td>
<td>4
</td>
<td>5
</td>
<td>6
</td>
</tr>
</table>
<table id="tbl2" class="table2excel">
<tr>
<td>Product</td>
<td>Price</td>
<td>Available</td>
<td>Count</td>
</tr>
<tr>
<td>Bred</td>
<td>7
</td>
<td>8
</td>
<td>9
</td>
</tr>
<tr>
<td>Butter</td>
<td>14
</td>
<td>15
</td>
<td>16
</td>
</tr>
</table>
<button onclick="tablesToExcel(['tbl1','tbl2']
['ProductDay1','ProductDay2'], 'TestBook.xls', 'Excel')">Export to
Excel</button>
Link to JSFiddle