Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Convert a HTML Table into Excel Spreadsheet using jQuery?
Overview
To convert the HTML Table into the Excel Spreadsheet can be done with the help of jQuery plugins. The “table2excel” is a lightweight plug-in of jQuery, which helps to achieve the solution to the problem. In this we will create a table using HTML <table> tag in that we will create the number of rows using <tr> tag. The data is inserted inside the rows using the <td> tag.
Syntax
The syntax used in this is −
$(selector).table2excel({
filename: “”,
fileext: “”
});
selector − It can be any HTML table tag, class or id.
table2excel − It is a function which will convert the HTML table. It contains certain arguments such as filename, fileext, etc.
Approach
To convert the table to excel sheet we will add some of the Content Delivery Network (CDN) links to our HTML page −
jQuery CDN
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
table2excel plug-in CDN
<script src="//cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>
Algorithm
Step 1 − Create a HTML table using <table> tag, add rows to the table using <tr> tag, insert data to the table rows using <td> tag.
Step 2 − Create a HTML button using <button> tag which will be used to export the table to excel spreadsheet.
Step 3 − Add the Content Delivery Network (CDN) of jQuery and jQuery plug-in “table2excel” to the head tag of the HTML page.
Step 4 − Create the <script> tag at the end of the body tag, select a selector as “button” and add click function as jQuery syntax which will trigger an arrow function.
Step 5 − Inside arrow function select the table through the given id inside the table tag as a selector and use the “table2excel” function. Inside this function pass two arguments as filename: “Write your filename” and fileext: “.xls”.
Step 6 − On clicking the button it will fire the table2excel function and will download a .xls file.
Example
In this example we have created an HTML table as student records which store the records of the student in the table format, student info as Student Name, Branch, Roll No. and Date Of Birth. So simply in this on clicking on the export button the table will be converted into the excel file and will be downloaded automatically.
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="//cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>
<title> Convert HTML table into Excel Spreadsheet </title>
</head>
<body>
<h1>Student Records</h1>
<table id="studtable" style="border-spacing: 0.6rem 0px;text-align: center;">
<tr>
<th>S.No.</th>
<th>Name</th>
<th>Branch</th>
<th>Roll No.</th>
<th>D.O.B</th>
</tr>
<tr>
<td>1</td>
<td>Arman</td>
<td>CSE</td>
<td>0001</td>
<td>01/01/2000</td>
</tr>
<tr>
<td>2</td>
<td>Ayush</td>
<td>Civil</td>
<td>0002</td>
<td>02/02/2000</td>
</tr>
<tr>
<td>3</td>
<td>Abhay</td>
<td>EE</td>
<td>0003</td>
<td>03/03/2000</td>
</tr>
<tr>
<td>4</td>
<td>Akshay</td>
<td>IT</td>
<td>0004</td>
<td>04/04/2000</td>
</tr>
</table>
<button style="margin-top: 0.5rem;">Export</button>
<script>
$('button').click(() => {
$("button").html("Exported");
$('#studtable').table2excel({ filename: "StudentRecords", fileext: ".xls"});
})
</script>
</body>
</html>
Conclusion
This solution can be helpful in many applications such as a CRUD (create, read, update, delete) application, student or employee management system or in an ecommerce log activity records etc. It is necessary to include CDN links in order to use the “table2excel” function. In the table2excel function there are more parameters which are passed in as object as key value.
The parameters are −
preserveColors − If passed true it includes the background color and font color of HTML.
exclude_img − It does not include the image of the table when true value is passed.
exclude_links − When the true value is stored this excludes the link inserted in the table.
exclude_input − The value of these are in Boolean form as true or false.
2K+ Views
