sheetjs tutorial
Excel HTML Javascript

SheetJS Tutorial – Convert HTML Table to Excel

In previous tutorial, we’ve talked about SheetJS and how to create an excel file from JavaScript array. Now I’m going to show you how to convert HTML table to Excel with SheetJS which I believe is the best approach so far. As many tutorial or example code available out there (including mine) mostly utilize the XML to generate the excel file.

The problem of using XML is it’s a bit complicate, and also create a warning message when the user is trying to open it due to incorrect Excel content type.

sheetjs tutorial 2-3

However, with SheetJS, we can finally convert the HTML table to Excel file properly without any warning! (and pretty much easier too!) Let’s go check it out!

HTML Table to Workbook

We’ll assumed that you have downloaded and import SheetJS with a script tag to your webpage. Here is our sample table on the webpage that we’re going to convert to excel file.

sheetjs tutorial 2-1

Now let’s start with calling table_to_book() utilities function which will convert the HTML table to excel workbook; and then pass our table element, then follow by a new sheet name.

var wb = XLSX.utils.table_to_book(document.getElementById('mytable'), {sheet:"Sheet JS"});

And that’s it! Your workbook object will be created with the table inside. Next we’ll have to export it as downloadable xlsx file!

Same as previous tutorial, first we’ll need to export the workbook as xlsx binary using write function.
Then convert it to octet array using arrayBuffer and UInt8Array. And finally use Filesaver.js and blob object to trigger the browser download.

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;
            }
 $("#button-a").click(function(){
              saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), 'test.xlsx');
});

All good! Here is the generated Excel

sheetjs tutorial 2-2

HTML Table to Worksheet

Now suppose that you already have a workbook object and just want to convert the HTML table as a new sheet instead and add it to the existing workbook, you can do that, by simply replacing the table_to_book() with table_to_sheet() And then push the new sheet name and assign the sheet object to workbook Sheets array like we did on previous tutorial.

//var wb = XLSX.utils.book_new(); wb is existing workbook object

wb.SheetNames.push("Test Sheet2");
var ws2 = XLSX.utils.table_to_sheet(document.getElementById('mytable'));
wb.Sheets["Test Sheet2"] = ws2;

And that’s it! See the live result in video below.

In the next tutorial, we’ll do it backward. I’ll show you how to convert the excel file back to HTML table and display it on the webpage.

Check our other interesting tutorial from our Youtube Channel – Don’t forget to subscribe to stay updated for our new weekly tutorial!

Full Source Code

<html>
<head>
<script src="jquery-2.1.4.js"></script>
<script lang="javascript" src="xlsx.full.min.js"></script>
<script lang="javascript" src="FileSaver.min.js"></script>

<link rel="stylesheet" type="text/css" href="styles.css">

</head>
<body>
<div id="navbar"><span>Red Stapler - SheetJS </span></div>
<div id="wrapper">
        <table id="mytable">
                <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Company</th>
                </tr>
                <tr>
                <td>Harry</td>
                <td>Potter</td>
                <td>WB</td>
                </tr>
                <tr>
                <td>Captain</td>
                <td>America</td>
                <td>Marvel</td>
                </tr>
                </table>
        <button id="button-a">Create Excel</button>
</div>
<script>
        var wb = XLSX.utils.table_to_book(document.getElementById('mytable'), {sheet:"Sheet JS"});
        var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});

        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;
        }
        $("#button-a").click(function(){
        saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), 'test.xlsx');
        });

</script>
</body>
</html>
Written By

9 comments

Leave a Reply

Your email address will not be published. Required fields are marked *

error: