How to Export Html table to Excel using JQuery

Share on:

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

Share on: