3

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-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]--><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 ?

User
  • 1,334
  • 5
  • 29
  • 61

2 Answers2

3

I 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.com/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();
sifr_dot_in
  • 3,153
  • 2
  • 33
  • 42
Brett Riches
  • 56
  • 4
  • 1
    To improve the quality of your answer, consider editing to explain how/why key bits of your code solves the OP's issue.. Quality answers which help visitors learn something they can apply to their own coding are more likely to receive upvotes over time. Text also helps visitors quickly focus on key elements of your code. – SherylHohman Mar 30 '21 at 15:53
1

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);
});
Sleider Dávila
  • 11
  • 1