Exporting huge (3000+ rows) HTML tables to Excel using javascript

Question

I have a problem exporting an HTML table with quite hefty number of rows to excel. As i have pointed out in the title, its 3000+ rows and around 6 columns.

I have tried this one, How to export html table to excel using javascript

Works fine! Well, atleast on small tables. But when i tie it up with the table that I want to convert, it loads for a while then stops.

Can anyone help me out here?

PS, I don't want to use ActiveXObject because i don't want to be restricted to just using IE.

Thanks in advance!

EDIT: The code

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]--></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) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()

works on IE and Firefox, but not on chrome. Why is that?

possible duplicate of How to export html table to excel using javascript — GergelyPolonkai
– GergelyPolonkai, Commented Sep 29, 2015 at 8:24
@JunchaoGu, I don't have anything like that. He has a pop-up "download" but not in XLS format but I have nothing like that. — cedric
– cedric, Commented Sep 29, 2015 at 8:51
@GergelyPolonkai, I am trying to avoid using ActiveXObject as it restricts my user to only using IE for his browser. — cedric
– cedric, Commented Sep 29, 2015 at 8:52
You should reconsider your requirement. How is the table generated? Server-side? Then use server-side to generate the xls. Passing a worksheet on the url will always fail at some point and will be dependent on the browser. Read this for more info: What is the maximum length of a URL in different browsers? (SO) — fdomn-m
– fdomn-m, Commented Sep 29, 2015 at 8:57
When you say "it loads for a while then stops" - do you mean it builds the data for a while then the script stops or that the script works fine but not all of it is loaded into excel? — fdomn-m
– fdomn-m, Commented Sep 29, 2015 at 8:58

Cat_S · Accepted Answer · 2020-03-19 16:15:02Z

I viewed the answer form there, credits for: how to export table as excel with 10000 to 40000 rows

It works on Chrome(not test on IE yet), saved 3867 rows to .xls file was ok;

Hope this could help someone. The code:

.html

<input type="button" class="col-2 btn btn-info" onclick="tableToExcel(myTableId)" value="Excel">

.js

function tableToExcel(tableId) {

  let 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="https://www.w3.org/TR/html401/"><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]; })}

  let table = document.getElementById(tableId)
  if (!table) {
    return;
  }
  let ctx = {worksheet: tableId || 'Worksheet', table: table.innerHTML}
  var str = base64(format(template, ctx));
  var blob = b64toBlob(str, "application/vnd.ms-excel");
  var blobUrl = URL.createObjectURL(blob);

  let link = document.createElement('a');
  link.download = openedTabId + '.xls'; // the fileName for download
  link.href = blobUrl;
  link.click();
  // window.location = blobUrl; // instead of using a link, could also do this;

  function b64toBlob(b64Data, contentType='', sliceSize=512) {
      var byteCharacters = atob(b64Data);
      var byteArrays = [];

      for (var offset = 0; offset < byteCharacters.length; offset += sliceSize) {
          var slice = byteCharacters.slice(offset, offset + sliceSize);

          var byteNumbers = new Array(slice.length);
          for (var i = 0; i < slice.length; i++) {
              byteNumbers[i] = slice.charCodeAt(i);
          }

          var byteArray = new Uint8Array(byteNumbers);
          byteArrays.push(byteArray);
      }

      var blob = new Blob(byteArrays, {type: contentType});
      return blob;
  }
}

Akhilesh · Accepted Answer · 2017-06-20 11:43:37Z

Below code is working for me in chrome for table more than 2000 records.

csv = []
rows = $('#YourHTMLtableId tr');
for(i =0;i < rows.length;i++) {
cells = $(rows[i]).find('td,th');
csv_row = [];
for (j=0;j<cells.length;j++) {
    txt = cells[j].innerText;
    csv_row.push(txt.replace(",", "-"));
}
csv.push(csv_row.join(","));
}
output = csv.join("\n")
var textToSave = output;
var textToSaveAsBlob = new Blob([textToSave], {type:"text/plain"});
var textToSaveAsURL = window.URL.createObjectURL(textToSaveAsBlob);
var fileNameToSaveAs = "something.csv";
var downloadLink = document.createElement("a");
downloadLink.download = fileNameToSaveAs;

downloadLink.innerHTML = "Download File";

downloadLink.href = textToSaveAsURL;    
downloadLink.onclick = destroyClickedElement;
function destroyClickedElement(event)
  {
   document.body.removeChild(event.target);
   }
downloadLink.style.display = "none";
document.body.appendChild(downloadLink);


downloadLink.click();

Collectives™ on Stack Overflow

Exporting huge (3000+ rows) HTML tables to Excel using javascript

2 Answers 2

.html

.js

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

.html

.js

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related