Javascript code table to excel data
<script type="text/javascript">
var tableToExcel = (function () {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-:office:office" xmlns:x="urn:schemas-microsoft-:office:excel" xmlns=""><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]--><meta http-equiv="content-type" content="text/plain; 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 function (table, name,action) {
if (!table.nodeType) table = document.getElementById(table)
var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
window.location.href = uri + base64(format(template, ctx))
}
})();
</script>
Action Button
<input type="button" class="btn btn-success" value="Export" onclick="tableToExcel('tblConsolidate','Report','Fifth Batch')" />
table design
<table class="table table-bordered" id="tblConsolidate">
<thead>
<tr>
<th>
Head 1
</th>
<th>
Head 2
</th>
</tr>
</thead>
<tbody style="text-align:center;">
<tr>
<td>
body 1
</td>
<td>
body 2
</td>
</tr>
</tbody>
Question
when above table export to excel function worked good for small data but when rows above 800 rows and columns almost 20 or more then it showed about:blank#blocked error and export failed
what is the problem why this blocked and what should be change in my code ?
Javascript code table to excel data
<script type="text/javascript">
var tableToExcel = (function () {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-:office:office" xmlns:x="urn:schemas-microsoft-:office:excel" xmlns="http://www.w3/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]--><meta http-equiv="content-type" content="text/plain; 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 function (table, name,action) {
if (!table.nodeType) table = document.getElementById(table)
var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
window.location.href = uri + base64(format(template, ctx))
}
})();
</script>
Action Button
<input type="button" class="btn btn-success" value="Export" onclick="tableToExcel('tblConsolidate','Report','Fifth Batch')" />
table design
<table class="table table-bordered" id="tblConsolidate">
<thead>
<tr>
<th>
Head 1
</th>
<th>
Head 2
</th>
</tr>
</thead>
<tbody style="text-align:center;">
<tr>
<td>
body 1
</td>
<td>
body 2
</td>
</tr>
</tbody>
Question
when above table export to excel function worked good for small data but when rows above 800 rows and columns almost 20 or more then it showed about:blank#blocked error and export failed
what is the problem why this blocked and what should be change in my code ?
Share Improve this question asked Mar 2, 2020 at 6:46 UserUser 1,3735 gold badges29 silver badges61 bronze badges 1- I face the same - anybody knows help? – David Mason Commented Mar 18, 2021 at 15:46
3 Answers
Reset to default 4I had the same issue, I solved it by saving it as a blob.
I believe the issue was caused by hitting the data URL data limit (2 mb on chrome?), by storing it as a blob increases your data limit by a lot (2 gigs?).
This answer was pretty helpful: [https://stackoverflow./a/36502388][1]
var tableToExcel = (function () {
var myBlob = new Blob( [table.innerHTML] , {type:'application/vnd.ms-excel'});
var url = window.URL.createObjectURL(myBlob);
var a = document.createElement("a");
document.body.appendChild(a);
a.href = url;
a.download = "export.xls";
a.click();
//adding some delay in removing the dynamically created link solved the problem in FireFox
setTimeout(function() {window.URL.revokeObjectURL(url);},0);
});
//and call the function:
tableToExcel();I solved it using this code
var tableToExcel = (function () {
var encabezado = '<html><head><meta http-equiv="content-type"
content="text/plain; charset=UTF-8"/><style> table, td {border:thin solid
black} table {border-collapse:collapse}</style></head><body><table>';
var dataTable = table.innerHTML
var piePagina = "</table></body></html>";
var tabla = encabezado + dataTable + piePagina;
var myBlob = new Blob( [tabla] , {type:'text/html'});
var url = window.URL.createObjectURL(myBlob);
var a = document.createElement("a");
document.body.appendChild(a);
a.href = url;
a.download = "export.xls";
a.click();
setTimeout(function() {window.URL.revokeObjectURL(url);},0);
});
You can update your original function into below function
var tableToExcel = (function() {
var template = '<html xmlns:o="urn:schemas-microsoft-:office:office" xmlns:x="urn:schemas-microsoft-:office:excel" xmlns="http://www.w3/TR/REC-html40"><head></head><body><table>{table}</table></body></html>';
var format = function(s, c) {
return s.replace(/{(\w+)}/g,
function(m, p) {
return c[p];
});
};
return function(table, name) {
if (!table.nodeType) table = document.getElementById(table);
var ctx = {
worksheet: name || 'Worksheet',
table: table.innerHTML
};
var html = format(template, ctx);
// Convert HTML to Blob
var blob = new Blob([html], {
type: 'application/vnd.ms-excel'
});
// Create a download link
var link = document.createElement('a');
link.href = window.URL.createObjectURL(blob); // Use Blob URL
link.download = name + '.xls' || 'export.xls'; // Set filename
document.body.appendChild(link);
link.click(); // Simulate click to trigger download
document.body.removeChild(link); // Remove the link
};})();