Convert HTML table to Excel file in pure JavaScript using sheets.js plugin

Question

I couldn't find an existing question/answers on Stack Overflow for my particular issue so I'm posting this. I have an application written in HTML and jQuery/JavaScript. I need to export an HTML table into an Excel sheet using the jsxlsx (sheetsjs) plugin. I tried replicating their example to convert HTML table to Excel but the program errors out. Here's what I currently have:

My HTML code is inside a coldfusion page called books.cfm. I have my JavaScript in a file called Utils.js. These are:

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 convertToExcel(event){
  event.preventDefault();
  console.log("button clicked");
  var wb = XLSX.utils.table_to_book( $("#exceltable"), {sheet:"Exported 
    table"} );
  saveAs(new Blob([s2ab(wb)],{type:"application/octet-stream"}), 
   'test.xlsx');
}

<html>
<head>
  <script src="libs/jquery.min.js"></script>
  <script src="libs/sheets/xlsx.full.min.js"></script>
  <script src="libs/FileSaver/FileSaver.min.js"></script>
  <script src="scripts/Utils.js"></script>
</head>
<body>
  <table id="myTable">
    <tr>
      <td>ID</td>
      <td>Name</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Johnny</td>
    </tr>
  </table>
 <button id="myBtn" onclick="convertToExcel(event)">Export</button>
</body>
</html>

When I run the above code and click on the button to convert to Excel, I get the following error:

**Uncaught TypeError: e.getElementsByTagName is not a function
at GC (xlsx.full.min.js:20)
at Object.jC [as table_to_book] (xlsx.full.min.js:20)
at exportErrsToExcel (Util.js:1227)
at HTMLButtonElement.onclick (books.cfm:18)**

Note: I got the above JavaScript code from this site.

This is not a duplicate of that question. Other than saying that you are passing a jQuery Object which is supposed to reference the element with the `id` = `exceltable` and there is no such element in the HTML you've shown, we can't tell you much because you have not provided us with a MCVE. For instance, you need to provide us with links to the exact libraries you are including. The best way to do this would be to use a snippet and have a fully working MCVE which demonstrates the problem. — Makyen, Jun 29 '18 at 23:44

PierreN · Accepted Answer · 2018-06-29T18:48:14.803

1

New Answer With SheetJS Working code :

<script type="text/javascript" src="//unpkg.com/xlsx/dist/shim.min.js"></script>
<script type="text/javascript" src="//unpkg.com/xlsx/dist/xlsx.full.min.js"></script>

<script type="text/javascript" src="//unpkg.com/blob.js@1.0.1/Blob.js"></script>
<script type="text/javascript" src="//unpkg.com/file-saver@1.3.3/FileSaver.js"></script>


<div id="container2">
  <title>SheetJS Table Export</title>
  <table id="data-table">
    <tr>
      <td>ID</td>
      <td>Name</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Johnny</td>
    </tr>
  </table>
</div>
<p id="xportxlsx" class="xport"><input type="submit" value="Export to XLSX!" onclick="doit('xlsx');"></p>


<script type="text/javascript">

function doit(type, fn, dl) {
    var elt = document.getElementById('data-table');
    var wb = XLSX.utils.table_to_book(elt, {sheet:"Sheet JS"});
    return dl ?
        XLSX.write(wb, {bookType:type, bookSST:true, type: 'base64'}) :
        XLSX.writeFile(wb, fn || ('test.' + (type || 'xlsx')));
}


function tableau(pid, iid, fmt, ofile) {
    if(typeof Downloadify !== 'undefined') Downloadify.create(pid,{
            swf: 'downloadify.swf',
            downloadImage: 'download.png',
            width: 100,
            height: 30,
            filename: ofile, data: function() { return doit(fmt, ofile, true); },
            transparent: false,
            append: false,
            dataType: 'base64',
            onComplete: function(){ alert('Your File Has Been Saved!'); },
            onCancel: function(){ alert('You have cancelled the saving of this file.'); },
            onError: function(){ alert('You must put something in the File Contents or there will be nothing to save!'); }
    });
}
tableau('xlsxbtn',  'xportxlsx',  'xlsx',  'test.xlsx');

</script>

Old answer : You can use a workaround if sheetjs doesn't work. Datatable tool for instance has a plugin for exporting an HTML table to Excel. Demo : https://datatables.net/extensions/buttons/examples/initialisation/export.html

You could load Datatable in a hidden div for instance, and then your button "myBtn" launches the Export thanks to Excel Datatable export button

edited Jun 29 '18 at 18:48

answered Jun 29 '18 at 15:33

PierreN

968
4
11

I'm having enough trouble getting sheetsjs to work - adding another plugin like Datatables will add to this complexity and I don't want to do that. – Roger Dodger Jun 29 '18 at 15:39
1

I understood you use sheetjs only for doing this export, that's why i proposed you Datatable... – PierreN Jun 29 '18 at 15:42
I tried your above solution using sheets.js but get following error: Uncaught TypeError: Cannot read property 'writeFileSync' of undefined – Roger Dodger Jul 02 '18 at 20:45
1

@RogerDodger i'm sorry it doesn't work for you. My sample code works in jsfiddle, i've just rechecked. Maybe can you give a jsfiddle link with your error ? – PierreN Jul 02 '18 at 20:51
I tried your code in fiddle but get an error; link to the fiddle: https://jsfiddle.net/vpo0tL97/1/ – Roger Dodger Jul 02 '18 at 21:03
1

i'm checking...when you copy paste all my code in the "HTML" textarea of jsfiddle, it works and the export button provokes the download of the file...i think it's a problem of loading specific to jsfiddle, the code isn't downloaded in the good order – PierreN Jul 02 '18 at 21:12
1

in the JS section, you click on "JavaScript + No-Library (pure JS)", and choose Load Type "No wrap - bottom of " instead of "On Load", and your problem is solved ;-) – PierreN Jul 02 '18 at 21:17
thanks, it works now! I changed the load order of the js libs to exactly the way you had 'em and it works now. PS: I did accept your earlier post as the answer, but someone actually downvoted it - as you've seen from all the negative posts, there are a lot of malcontents on this site. Thanks for your help! – Roger Dodger Jul 02 '18 at 21:36
1

@RogerDodger thanks ;-) I'm new on Stackoverflow, many rules to learn for contributing here, very interesting ;-) – PierreN Jul 02 '18 at 22:09

Convert HTML table to Excel file in pure JavaScript using sheets.js plugin

1 Answers1

Linked