1. Introduction
This document describes how to export the HTML table data into excel using the jquery plugin table2Excel. This plugin is used to convert and download HTML tables to an xlsx-file that can be opened in Microsoft Excel. Data present between the <table> </table> tag will be exported only. All the detailed steps for using this library are defined later.
2. How to use table2excel
1. Create the html table and provide a button there through which user will able to export the data.
<body> <div> <button onclick=”exportReport()” style=”background-color:#4CAF50;color:white;”> Export to Excel </button> <br /> <br /> </div> <div> <table id=”studentTable”> <thead> <tr> <th> Student Name </th> <th> Age </th> <th> Marks </th> <th> Board </th> </tr> </thead> <tbody> <tr> <td> Rajeev </td> <td> 17 </td> <td> 88 </td> <td> CBSE </td> </tr> <tr> <td> Sandhya </td> <td> 18 </td> <td> 67 </td> <td> CBSE </td> </tr> <tr> <td> Ramesh </td> <td> 16 </td> <td> 78 </td> <td> Bihar </td> </tr> <tr> <td> Sanjay </td> <td> 17 </td> <td> 89 </td> <td> CBSE </td> </tr> <tr> <td> Ramya </td> <td> 18 </td> <td> 90 </td> <td> UP </td> </tr> </tbody> </table> </div> </body> |
2. Add some css and scripts in the header section. Add jquery and table2excel.js file under script section in the <head> tag. Create exportReport() method which will export the data of the table in the excel.
<head> <style> #studentTable { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; } #studentTable td, #studentTable th { border: 1px solid #ddd; padding: 8px; } #studentTable th { padding-top: 6px; padding-bottom: 6px; text-align: left; background-color: #4CAF50; color: white; } </style> <script src=”jquery.min.js” type=”text/javascript”></script> <script src=”table2excelmodified.js” type=”text/javascript”></script> <script type=”text/javascript”> function exportReport() { var table = $(“#studentTable”); $(table).table2excel({ // exclude CSS class exclude: “.noExl”, name: “Student_List”, filename: “Student_List_” + $.now(),//do not include extension fileext: “.xls”, // file extension preserveColors: true, sheetName: “Student_List_” }); } </script> </head> |
3. Under table2excel method following are the options.
exclude: “.noExl”, -> the data will not export where ever the class is applied
name: “Student_List”,
filename: “Student_List_” + $.now(), -> provide the file name here
fileext: “.xls”, -> provide the file extension here
preserveColors: true, -> true when wants to preserve colors
sheetName: “Student_List_” -> provide the sheet name here
4. Open the html in browser and click on export button. The excel will be downloaded as mentioned in the below screenshot.
3. Source Code
<!Doctype> <html> <head> <style> #studentTable { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; } #studentTable td, #studentTable th { border: 1px solid #ddd; padding: 8px; } #studentTable th { padding-top: 6px; padding-bottom: 6px; text-align: left; background-color: #4CAF50; color: white; } </style> <script src=”jquery.min.js” type=”text/javascript”></script> <script src=”table2excelmodified.js” type=”text/javascript”></script> <script type=”text/javascript”> function exportReport() { var table = $(“#studentTable”); $(table).table2excel({ // exclude CSS class exclude: “.noExl”, name: “Student_List”, filename: “Student_List_” + $.now(),//do not include extension fileext: “.xls”, // file extension preserveColors: true, sheetName: “Student_List_” }); } </script> </head> <body> <div> <button onclick=”exportReport()” style=”background-color:#4CAF50;color:white;”> Export to Excel </button> <br /> <br /> </div> <div> <table id=”studentTable”> <thead> <tr> <th> Student Name </th> <th> Age </th> <th> Marks </th> <th> Board </th> </tr> </thead> <tbody> <tr> <td> Rajeev </td> <td> 17 </td> <td> 88 </td> <td> CBSE </td> </tr> <tr> <td> Sandhya </td> <td> 18 </td> <td> 67 </td> <td> CBSE </td> </tr> <tr> <td> Ramesh </td> <td> 16 </td> <td> 78 </td> <td> Bihar </td> </tr> <tr> <td> Sanjay </td> <td> 17 </td> <td> 89 </td> <td> CBSE </td> </tr> <tr> <td> Ramya </td> <td> 18 </td> <td> 90 </td> <td> UP </td> </tr> </tbody> </table> </div> </body> </html> |
3. Advantages of the plugin
1. No need to code on the server-side for exporting any data which is available at web pages in tabular format.
2. Reduces the chances of error occurs.
3. Time-saving process.
4. Disadvantages of the plugin
1. This plugin will only export only first page if server-side pagination is using in the table.
Check out more at velsof.com
