How to Export HTML Table with Formatting to Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Export HTML Table with Formatting to Excel


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>&nbsp Export to Excel</button>
         </div>
      </div>
</body>
</html>

Create HTML Table Using Notepad

  • 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>&nbsp 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

Create HTML Table Using Notepad

  • 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.

Create HTML Table Using Notepad

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.

Export HTML Table with Formatting to Excel

  • Finally, you will be able to export the DOCTYPE HTML table to Excel which has been given in the below screenshot.

Export HTML Table with Formatting to Excel

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

Tags:

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo