JQuery

Export Hierarchical (Multi-Level) HTML Table With Styles Using jQuery

Hi. We are all familiar with HTML tables. What if we want to export that HTML table to Excel? What if we want to do it in the client side itself? Yeah, of course using jQuery. Please read here for the basic exporting technique: Export From HTML Table Using jQuery.


Background

In the preceding article you can determine whether we are exporting a single-level HTML table. What if we need to do that for a multi-level HTML table? The hierarchy must be applied to the Excel file we exported. That too is without using any third-party plug- ins! Sounds cool, right?

Using the code

So shall we start? I hope you said yes.

What we need first

Yeah you are right, we need a multi-level HTML table that we will now export. Let us say I have an HTML table as follows.
  1. <table id="multiLevelTable">  
  2.         <caption>My Multi Level Table</caption>  
  3.         <thead>  
  4.             <tr>  
  5.                 <th colspan="2" rowspan="2">Column 0</th>  
  6.                 <th rowspan="2">Column 1</th>  
  7.                 <th colspan="2">Column 2</th>  
  8.             </tr>  
  9.             <tr>  
  10.                 <th>Column 2a</th>  
  11.                 <th>Column 2b</th>  
  12.             </tr>  
  13.         </thead>  
  14.         <tbody>  
  15.             <tr>  
  16.                 <th rowspan="2">Row 1</th>  
  17.                 <th>Row 1a</th>  
  18.                 <td>123</td>  
  19.                 <td>456</td>  
  20.                 <td>789</td>  
  21.             </tr>  
  22.             <tr>  
  23.                 <th>Row 1b</th>  
  24.                 <td>123</td>  
  25.                 <td>456</td>  
  26.                 <td>789</td>  
  27.             </tr>  
  28.             <tr>  
  29.                 <th colspan="2">Row 2</th>  
  30.                 <td>123</td>  
  31.                 <td>456</td>  
  32.                 <td>789</td>  
  33.             </tr>  
  34.         </tbody>  
  35.     </table> 

What next?

Apply some styles

Please add the following styles to increase the alignment clarity.

  1. <style>  
  2.       
  3.     td,th,thead,caption,tr{  
  4.         text-align:center;border:1px solid #ccc;  
  5.     }  
  6.         caption {  
  7.             background-color:#ccc;  
  8.         } 

Is that done?

Add the jQuery reference:

  1. <script src="Contents/jquery-1.9.1.js"></script> 

Create an element on which we need to fire the click event.

  1. <div onclick="exportThis()" style="cursor: pointer; border: 1px solid #ccc; text-align: center;width:19%;">Export Multi Level Table to Excel</div> 

Please note that we have called the function exportThis in onclick. Shall we go and see what we can write in that function?

exportThis function

The following is the code inside in the function.

  1. var exportThis = (function () {  
  2.             var uri = 'data:application/vnd.ms-excel;base64,',   
  3. 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>',  
  4.                 base64 = function (s) {  
  5.                     return window.btoa(unescape(encodeURIComponent(s)))  
  6.                 },  
  7.                 format = function (s, c) {  
  8.                     return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; })  
  9.                 }  
  10.             return function () {  
  11.                 var ctx = { worksheet: 'Multi Level Export Table Example' || 'Worksheet', table: document.getElementById("multiLevelTable").innerHTML }  
  12.                 window.location.href = uri + base64(format(template, ctx))  
  13.             }  
  14.         })() 

In the preceding specified function, you can see the following three parts:

  1. Uri
  2. base64
  3. format
  4. template

So we will discuss those terms now.

If you are confused about the terms URI and URL please read here: What is the difference between URI, URL and URN?

To be familiar with the base 64 encoding please read: What is base 64 encoding used for?

The format part is cross-checking the data, whether it has some invalid characters or not. You can see a regex in the function.

The template is the structure of the Excel file; we are using the w3 format.

What it does

Once we clicked the div, the onclick event will be fired and it fetches the inner HTML of the element multiLevelTable. Once it does, we are formulating the data and assigning it to the location.href().

Please see here to understand the basic difference between the window.location.href() and window.open() : Window.location.href () and Window.open () methods in JavaScript.

Wow cool, we did it.

table

Figure: without style.

Figure

Figure: with style

Wait, we are not done yet

Now what if you have many HTML tables and you may need to export all the HTML tables? Of course we cannot create separate functions, right?

So can we do some changes to our function?

Let us do this.

New export function

  1. var exportThisWithParameter = (function () {  
  2.             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>',  
  3.                 base64 = function (s) {  
  4.                     return window.btoa(unescape(encodeURIComponent(s)))  
  5.                 },  
  6.                 format = function (s, c) {  
  7.                     return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; })  
  8.                 }  
  9.             return function (tableID, excelName) {  
  10.                 tableID = document.getElementById(tableID)  
  11.                 var ctx = { worksheet: excelName || 'Worksheet', table: tableID.innerHTML }  
  12.                 window.location.href = uri + base64(format(template, ctx))  
  13.             }  
  14.         })() 

In the preceding function you can see the parameters (tableID, excelName) . This is what we need to pass from the click event as follows.

  1. <div onclick="exportThisWithParameter('multiLevelTable', 'Multi Level Export Table Example')" style="cursor: pointer; border: 1px solid #ccc; text-align: center;width:19%;">Export Multi Level Table to Excel With Parameter</div> 

Complete HTML

  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3. <head>  
  4.     <title>Export Hierarchical HTML Using JQuery - Sibeesh|Passion</title>  
  5.     <script src="Contents/jquery-1.9.1.js"></script>  
  6.     <style>  
  7.       
  8.     td,th,thead,caption,tr{  
  9.         text-align:center;border:1px solid #ccc;  
  10.     }  
  11.         caption {  
  12.             background-color:#ccc;  
  13.         }  
  14.       
  15.       
  16. </style>  
  17.     <script type="text/javascript">  
  18.         var exportThis = (function () {  
  19.             var uri = 'data:application/vnd.ms-excel;base64,',  
  20.                 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>',  
  21.                 base64 = function (s) {  
  22.                     return window.btoa(unescape(encodeURIComponent(s)))  
  23.                 },  
  24.                 format = function (s, c) {  
  25.                     return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; })  
  26.                 }  
  27.             return function () {  
  28.                 var ctx = { worksheet: 'Multi Level Export Table Example' || 'Worksheet', table: document.getElementById("multiLevelTable").innerHTML }  
  29.                 window.location.href = uri + base64(format(template, ctx))  
  30.             }  
  31.         })()  
  32.         var exportThisWithParameter = (function () {  
  33.             var uri = 'data:application/vnd.ms-excel;base64,',  
  34.                 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>',  
  35.                 base64 = function (s) {  
  36.                     return window.btoa(unescape(encodeURIComponent(s)))  
  37.                 },  
  38.                 format = function (s, c) {  
  39.                     return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; })  
  40.                 }  
  41.             return function (tableID, excelName) {  
  42.                 tableID = document.getElementById(tableID)  
  43.                 var ctx = { worksheet: excelName || 'Worksheet', table: tableID.innerHTML }  
  44.                 window.location.href = uri + base64(format(template, ctx))  
  45.             }  
  46.         })()  
  47.     </script>  
  48. </head>  
  49. <body>  
  50.     <div onclick="exportThis()" style="cursor: pointer; border: 1px solid #ccc; text-align: center;width:19%;">Export Multi Level Table to Excel</div>  
  51.     <br />  
  52.     <div onclick="exportThisWithParameter('multiLevelTable', 'Multi Level Export Table Example')" style="cursor: pointer; border: 1px solid #ccc; text-align: center;width:19%;">Export Multi Level Table to Excel With Parameter</div>  
  53.     <br />  
  54.     <br />      
  55.     <table id="multiLevelTable">  
  56.         <caption>My Multi Level Table</caption>  
  57.         <thead>  
  58.             <tr>  
  59.                 <th colspan="2" rowspan="2" style="background-color:aqua;">Column 0</th>  
  60.                 <th rowspan="2">Column 1</th>  
  61.                 <th colspan="2">Column 2</th>  
  62.             </tr>  
  63.             <tr>  
  64.                 <th>Column 2a</th>  
  65.                 <th>Column 2b</th>  
  66.             </tr>  
  67.         </thead>  
  68.         <tbody>  
  69.             <tr>  
  70.                 <th rowspan="2">Row 1</th>  
  71.                 <th>Row 1a</th>  
  72.                 <td>123</td>  
  73.                 <td>456</td>  
  74.                 <td>789</td>  
  75.             </tr>  
  76.             <tr>  
  77.                 <th>Row 1b</th>  
  78.                 <td>123</td>  
  79.                 <td>456</td>  
  80.                 <td>789</td>  
  81.             </tr>  
  82.             <tr>  
  83.                 <th colspan="2">Row 2</th>  
  84.                 <td>123</td>  
  85.                 <td>456</td>  
  86.                 <td>789</td>  
  87.             </tr>  
  88.         </tbody>  
  89.     </table>  
  90. </body>  
  91. </html> 

Points of interests 

Hierarchical HTML, HTML, JQuery, Multi Level HTML 
 
History
 
First Version : 19-Jan-2015
 
That is all for the day. I hope you enjoyed this article. Thanks for reading. Please provide your valuable suggestions.

Kindest Regards
Sibeesh

http://www.sibeeshpassion.com/

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs