Hi there, how is your day Programmer? Have you ever tried to export your HTML table to an Excel sheet? If you tried and failed to manage that, here I am to solve your problem.
Before we get started, make sure that you have a bit of knowledge about HTML, CSS, and javascript. It is not necessary to be an expert on them. But it will be easier for you to understand this tutorial if you are already familiar with them.
HTML is awesome. It helps you to build the main structure of a website. It’s the main part of a website. Basically, everything we see in a website from the front-end is built with HTML. You can even create amazing pieces of stuff like form, tables, lists, and much more using just HTML.
The first thing that we need to create that function is an HTML file. An HTML file will contain all our HTML tables, JavaScript code, and a little CSS code. We will mix everything in that one file to export our HTML table to Excel. So, without further talking, let’s get started.
HTML Code
First of all, create a file named index.html or you can rename it as like as you want. Now, open that file using a text editor like sublime text, visual studio code, or any of your favorite text editors. After opening that file using text editor, add the following code to it.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>HTML Table To Excel</title>
</head>
<body>
<div class="blogdesire-wrapper">
<table id="testTable">
<thead>
<tr>
<th>No.</th>
<th>Person Name</th>
<th>Person Age</th>
<th>Person Hobby</th>
</tr>
</thead>
<tbody>
<tr>
<td>01</td>
<td>John Doe</td>
<td>18</td>
<td>Programming</td>
</tr>
<tr>
<td>02</td>
<td>Jonas Smith</td>
<td>22</td>
<td>Fishing</td>
</tr>
<tr>
<td>03</td>
<td>Ada Jonson</td>
<td>19</td>
<td>Travelling</td>
</tr>
</tbody>
</table>
<button onclick="tableToExcel('testTable', 'W3C Example Table')">Export</button>
</div>
</body>
</html>
Save the file, and open it with a web browser. You will find a table with some data like name, age, hobby, etc.
CSS Code
To make it a bit nicer, add the following code at the beginning of the body tag.
<style>
.blogdesire-wrapper{
margin: 20px auto;
padding: 20px;
box-shadow: 0 0 15px 0 rgba(0,0,0,.3);
text-align: center;
width:100vw;
max-width:400px;
}
td,th{
border-bottom: 1px solid rgba(0,0,0,.3);
padding:15px;
}
button{
border: none;
padding: 10px 20px;
border-radius:4px;
color:white;
background: orangered;
margin-top:20px;
}
</style>
This will add some style to your HTML table.
JavaScript Code
Now, it’s time to add the javascript code to export our HTML table to Excel. Copy the following code below and paste it between the header tags of your HTML file.
<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]--></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))
}
})()
</script>
After doing everything correctly, you should have an HTML table and a button colled Export in your HTML file. If you hit on the Export button of your HTML file, your HTML table should be converted and downloaded as an Excel file.
Here is a little demo of that.
See the Pen Export HTML table to Excel by MD Khokon Mia (@md-khokon) on CodePen.
I hope you have enjoyed this tutorial. If so, please share this withing social media to bring me a smile 🙂