Sometimes you may need to export HTML table to Excel with formatting for the convenience of your work. To gain a clear grasp of this topic, you have to know about Hypertext Markup Language (HTML). This article will provide you with two quick steps to export HTML tables to Excel with formatting effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Quick Steps to Export HTML Table with Formatting to Excel
Let’s assume we have a DOCTYPE HTML code that contains the information about several Customers. We will create an HTML table using Notepad. Hence, we will export the HTML table to Excel with formatting. Here’s an overview of the dataset for today’s task.
Step 1: Create HTML Table Using Notepad
In this section, we will create an HTML table using Notepad. This is an easy and time-saving task. Let’s follow the instructions below to learn!
- First of all, paste the below HTML code into Notepad to create a table.
<!DOCTYPE HTML>
<html>
<head>
<title>Customers Information</title>
<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>
<!-- Favicon -->
<link rel="shortcut icon" href="/favicon.ico">
<!-- Bootstrap -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
<!-- Fontawsome -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
</head>
<body>
<div class="d-flex justify-content-center profile-container">
<div class='col-md-10 text-center sort-profile' id='sort-profile'>
<div class='row'>
<div class='col-md-12 text-center' ><br/>
<h4><b>Customer Details</b></h4><hr/>
<table class='table table-light table-striped table-bordered' id='excel-table' style="background-color: transparent; border:2px solid black; margin-top:15px;">
<tr>
<th class="text-center">Name</th>
<th class="text-center">Age</th>
<th class="text-center" >Profession</th>
<th class="text-center">Address</th>
</tr>
<tr>
<td class="text-center">Jhon Doe</td>
<td class="text-center">29Y</td>
<td class="text-center">Engineer</td>
<td class="text-center">Sherman Oaks, California</td>
</tr>
<tr>
<td class="text-center">Devid Jones</td>
<td class="text-center">35Y</td>
<td class="text-center">Doctor</td>
<td class="text-center">Tampa, Florida</td>
</tr>
<tr>
<td class="text-center">Liana Lawrence</td>
<td class="text-center">25Y</td>
<td class="text-center">Architect</td>
<td class="text-center">Illinois, Chicago</td>
</tr>
<tr>
<td class="text-center">Ava Hawkins</td>
<td class="text-center" >45Y</td>
<td class="text-center">Accountant</td>
<td class="text-center">Buffalo, New York</td>
</tr>
</table>
</div>
</div>
<hr/>
<button type="button" style='font-size:23px;border:2px solid black;' class="btn btn-danger col-md-4" onclick="tableToExcel('excel-table', 'W3C Excel Table')"><i class="fa-sharp fa-solid fa-file-excel"></i>  Export to Excel</button>
</div>
</div>
</body>
</html>
- Now, paste the below HTML code in Notepad to generate the download button.
<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
<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]--><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]; }) }
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>
- Hence, save the below Doctype HTML code as a .html extension.
<!DOCTYPE HTML>
<html>
<head>
<title>Customers Information</title>
<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>
<!-- Favicon -->
<link rel="shortcut icon" href="/favicon.ico">
<!-- Bootstrap -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
<!-- Fontawsome -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
</head>
<body>
<div class="d-flex justify-content-center profile-container">
<div class='col-md-10 text-center sort-profile' id='sort-profile'>
<div class='row'>
<div class='col-md-12 text-center' ><br/>
<h4><b>Customer Details</b></h4><hr/>
<table class='table table-light table-striped table-bordered' id='excel-table' style="background-color: transparent; border:2px solid black; margin-top:15px;">
<tr>
<th class="text-center">Name</th>
<th class="text-center">Age</th>
<th class="text-center" >Profession</th>
<th class="text-center">Address</th>
</tr>
<tr>
<td class="text-center">Jhon Doe</td>
<td class="text-center">29Y</td>
<td class="text-center">Engineer</td>
<td class="text-center">Sherman Oaks, California</td>
</tr>
<tr>
<td class="text-center">Devid Jones</td>
<td class="text-center">35Y</td>
<td class="text-center">Doctor</td>
<td class="text-center">Tampa, Florida</td>
</tr>
<tr>
<td class="text-center">Liana Lawrence</td>
<td class="text-center">25Y</td>
<td class="text-center">Architect</td>
<td class="text-center">Illinois, Chicago</td>
</tr>
<tr>
<td class="text-center">Ava Hawkins</td>
<td class="text-center" >45Y</td>
<td class="text-center">Accountant</td>
<td class="text-center">Buffalo, New York</td>
</tr>
</table>
</div>
</div>
<hr/>
<button type="button" style='font-size:23px;border:2px solid black;' class="btn btn-danger col-md-4" onclick="tableToExcel('excel-table', 'W3C Excel Table')"><i class="fa-sharp fa-solid fa-file-excel"></i>  Export to Excel</button>
</div>
</div>
<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
<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]--><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]; }) }
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>
</body>
</html>
- To save the HTML code, from the File tab, go to,
File → Save As
- Hence, give the File name as Export HTML Table to Excel with .html After that, press the Save option.
- Finally, we will be able to save the code to create an HTML table with a .html extension.
Read More: How to Make a Table in Excel (With Customization)
Step 2: Export HTML Table with Formatting to Excel
Now, we will export the HTML table to Excel with formatting. To do that, follow the instructions below.
- Go back to the file where you save the HTML Hence, press the double-click on the file.
- As a result, an HTML table will open in a browser with a download button named Export to Excel.
- Further, click on the download button named Export to Excel to export the HTML table to Excel.
- Finally, you will be able to export the DOCTYPE HTML table to Excel which has been given in the below screenshot.
Read More: Excel Table Formatting Tips – Change the Look of the Table
Things to Remember
➜ Save the Notepad file as a .html extension.
➜ You can also save the HTML code with a .html extension using the Ctrl + Shift + S keyboard shortcuts.
Conclusion
I hope all of the suitable steps mentioned above to export the HTML table to Excel will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- How to Make a Table in Excel (2 Simple Ways)
- Excel Table Name: All You Need to Know
- How to Use Formula in Excel Table (4 Suitable Examples)
- Remove Table Formatting in Excel (2 Easy Methods)
- How to Make Excel Tables Look Good (8 Effective Tips)
- Add New Row Automatically in an Excel Table
- How to Extend Table in Excel (4 Ways)
