How to export/download the HTML table to Excel using Javascript?
Contents
HTML table
HTML providing the <table> tag to show the data in row and column format. We might have seen those tables in several web pages. Example: Stock Price list,Employee salary reports, Student marks report and so on. The below image is showing the corona cases report in HTML table format.
If we have the option to download those tables in excel, It would be helpful to analyze the data. Lets create the sample table using HTML and enable the download option using Javascript.
Javascript array
Javascript array is used to store collection of values in a single variable. As we know, the HTML table has a collection of rows and columns. We can add those values into array. Then the delimiters will be added for each row and column values to create the excel record.
var result = [‘Company’,’Group’,’Previous Value’,’Current Value’]
- Each row in a table delimited by new line character (\n)
- Each column in a table delimited by comma character (,)
Example : Download the HTML table in Excel (.csv) format
In this example, we are going to create the stock price list table with the download button – To export the table in csv file.
Step 1 : Create the HTML table
The below HTML code creates the stock price list table.
- <table> – Defines HTML table which contains set of rows and column values
- <tr> – It creates row element in the table.
- <th> – The header cell value created in this tag.
- <td> – Defines data cell/column of the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<html> <head> <title>Export HTML data to excel </title> <h2>US NASDAQ Stocks price list </h2> </head> <body> <table id="tblStocks" cellpadding="0" cellspacing="0"> <tr> <th>Company</th> <th>Group</th> <th>Prev Close($)</th> <th>Current Price($)</th> <th>Change(%)</th> </tr> <tr> <td>Life HealthCare INC</td> <td>A</td> <td>35.58</td> <td>38.45</td> <td>+8.06</td> </tr> <tr> <td>Den Networks Ltd</td> <td>A</td> <td>72.55</td> <td>77.60</td> <td>+6.96</td> </tr> <tr> <td>RMG Cements</td> <td>B</td> <td>140</td> <td>135</td> <td>-3.57</td> </tr> </table> <br /> </body> </html> |
Step 2 : Add CSS properties to style the table
In the HTML code, id (tblStocks) is defined for the <table> tag. For that id, We can add the CSS properties (margin,colour,font style) which will change the table style.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<style> #tblStocks { font-family: "Trebuchet MS", Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #tblStocks td, #tblStocks th { border: 1px solid #ddd; padding: 8px; } #tblStocks tr:nth-child(even){background-color: #f2f2f2;} #tblStocks tr:hover {background-color: #ddd;} #tblStocks th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #294c67;; color: white; } </style> |
Bootstrap providing the graphical icons from the Glyphicon Halflings set. Lets create the button with download icon using Glypicons.
|
1 2 3 4 5 |
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css"> <button> <span class="glyphicon glyphicon-download"></span> Download list </button> |
Step 4 : Implement Javascript function to download the HTML table in CSV file.
- Get the HTML table into Javascript variable using id “tblStocks” which is assigned for <table> tag.
- Loop through the variable to fetch cell values and add those values into javascript array. Here we are having array inside a array which means the outer array contains number of rows and each inner array contains number of columns for each row.
|
1 2 3 4 |
rows = [ [column1,column2,column3,column4,column5], [column1,column2,column3,column4,column5],... ] |
- Create <a> tag element with download attribute which used to download the data when user clicks the button. Here we are specifying data format as csv.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
function exportData(){ /* Get the HTML data using Element by Id */ var table = document.getElementById("tblStocks"); /* Declaring array variable */ var rows =[]; //iterate through rows of table for(var i=0,row; row = table.rows[i];i++){ //rows would be accessed using the "row" variable assigned in the for loop //Get each cell value/column from the row column1 = row.cells[0].innerText; column2 = row.cells[1].innerText; column3 = row.cells[2].innerText; column4 = row.cells[3].innerText; column5 = row.cells[4].innerText; /* add a new records in the array */ rows.push( [ column1, column2, column3, column4, column5 ] ); } csvContent = "data:text/csv;charset=utf-8,"; /* add the column delimiter as comma(,) and each row splitted by new line character (\n) */ rows.forEach(function(rowArray){ row = rowArray.join(","); csvContent += row + "\r\n"; }); /* create a hidden <a> DOM node and set its download attribute */ var encodedUri = encodeURI(csvContent); var link = document.createElement("a"); link.setAttribute("href", encodedUri); link.setAttribute("download", "Stock_Price_Report.csv"); document.body.appendChild(link); /* download the data file named "Stock_Price_Report.csv" */ link.click(); } |
Step 5 : Call the Javascript function using onclick event
Here we have created the javascript function in different file which named as “downloadFile.js”. Include that file in HTML using <script> tag. This script tag should be placed inside the <head> tag.
|
1 |
<script type="text/javascript" src="downloadFile.js"></script> |
Finally create the onclick event for the Download List button to call the javascript function exportData().
|
1 2 3 4 |
<button onclick="exportData()"> <span class="glyphicon glyphicon-download"></span> Download list </button> |
As you can see below, the HTML table downloaded in the csv file(Stock_Price_Report.csv) when user clicks the Download list button
HTML table downloaded in CSV file (Stock_Price_Report.csv)
Complete code for reference :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
<html> <head> <title>Export HTML data to excel </title> <h2>US NASDAQ Stocks price list </h2> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css"> <script type="text/javascript" src="downloadFile.js"></script> <style> #tblStocks { font-family: "Trebuchet MS", Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #tblStocks td, #tblStocks th { border: 1px solid #ddd; padding: 8px; } #tblStocks tr:nth-child(even){background-color: #f2f2f2;} #tblStocks tr:hover {background-color: #ddd;} #tblStocks th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #294c67;; color: white; } </style> </head> <body> <table id="tblStocks" cellpadding="0" cellspacing="0"> <tr> <th>Company</th> <th>Group</th> <th>Prev Close($)</th> <th>Current Price($)</th> <th>Change(%)</th> </tr> <tr> <td>Life HealthCare INC</td> <td>A</td> <td>35.58</td> <td>38.45</td> <td>+8.06</td> </tr> <tr> <td>Den Networks Ltd</td> <td>A</td> <td>72.55</td> <td>77.60</td> <td>+6.96</td> </tr> <tr> <td>RMG Cements</td> <td>B</td> <td>140</td> <td>135</td> <td>-3.57</td> </tr> </table> <br /> <button onclick="exportData()"> <span class="glyphicon glyphicon-download"></span> Download list</button> </body> </html> |
Javascript file : downloadFile.js
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
function exportData(){ /* Get the HTML data using Element by Id */ var table = document.getElementById("tblStocks"); /* Declaring array variable */ var rows =[]; //iterate through rows of table for(var i=0,row; row = table.rows[i];i++){ //rows would be accessed using the "row" variable assigned in the for loop //Get each cell value/column from the row column1 = row.cells[0].innerText; column2 = row.cells[1].innerText; column3 = row.cells[2].innerText; column4 = row.cells[3].innerText; column5 = row.cells[4].innerText; /* add a new records in the array */ rows.push( [ column1, column2, column3, column4, column5 ] ); } csvContent = "data:text/csv;charset=utf-8,"; /* add the column delimiter as comma(,) and each row splitted by new line character (\n) */ rows.forEach(function(rowArray){ row = rowArray.join(","); csvContent += row + "\r\n"; }); /* create a hidden <a> DOM node and set its download attribute */ var encodedUri = encodeURI(csvContent); var link = document.createElement("a"); link.setAttribute("href", encodedUri); link.setAttribute("download", "Stock_Price_Report.csv"); document.body.appendChild(link); /* download the data file named "Stock_Price_Report.csv" */ link.click(); } |
Recommended Articles
