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.
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.
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
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>
I just get an uncaught reference error in the console, ‘XLSX is not defined’
What type actually mean in “type:”application/octet-stream”” in line 44?
Hi, I’m wondering how I can use this with a table that is paginated ?
i use you code , but cant work and return this “Uncaught ReferenceError: saveAs is not defined” in line 44
it works!… Thank you.
How do I format the columns when exporting?
how can I recolor header of table?
not working !! save as is not define!! already include filesaver.min.js
i think u either dont have filesaver js file or havent include them in your html properly
Can I create the drill down excel sheet from json data