Converting a HTML table to an excel file is a standard requirement of reporting websites. It will be simple and easier if the conversion is taking place on the client side.
There are many client-side and server-side plugins to perform the excel export. For example, PHPSpreadSheet allows writing data into excel and exporting.
This article will give different options and approaches to achieving the HTML table to excel conversion with JavaScript.
This simple example includes a few lines of JavaScript that build the template for excel export.
It uses the following steps to convert the exported excel report of tabular data.
- Defines HTML template structure with required meta.
- Include the table’s inner HTML into the template.
- Marks the location by specifying the protocol to download the file via browser.
- Redirect via JavaScript to point to the location with the encoded excel content.
Quick example
A JavaScript handler to set export template and push HTML table data.
function exportToExcel() {
var location = 'data:application/vnd.ms-excel;base64,';
var excelTemplate="<html> " +
'<head> ' +
'<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/> ' +
'</head> ' +
'<body> ' +
document.getElementById("table-conatainer").innerHTML +
'</body> ' +
'</html>'
window.location.href = location + window.btoa(excelTemplate);
}
View demo
The below HTML table code displays a product listing and it is static. Refer jsPDF AutoTables examples to render a dynamic table by loading row by row.
The button below this table triggers the export to excel process on the click event. The exportToExcel() function handles the event and proceeds the client-side export.
<div id="table-conatainer">
<table class="striped">
<thead>
<tr>
<th>S.No</th>
<th>Product Name</th>
<th>Price</th>
<th>Model</th>
</tr>
</thead>
<tbody>
<tr>
<td>1q</td>
<td>GIZMORE Multimedia Speaker with Remote Control, Black</td>
<td>2300</td>
<td>2020</td>
</tr>
<tr>
<td>2</td>
<td>Black Google Nest Mini</td>
<td>3400</td>
<td>2021</td>
</tr>
</tbody>
</table>
</div>
Let us see more options available for converting HTML table data into an excel file.
Option 2: Export as CSV to view in excel
This example is for parsing the HTML table content via JavaScript. It follows the below steps to export an HTML table to excel as CSV.
- Accessing HTML table element object.
- Convert the object into an array of row data.
- Iterate row data array to prepare comma-separated values of records.
- Set the protocol and content type to export the CSV data prepared from the table.
The below JavaScript function implements the above steps to export table data on the client side.
function exportCSVExcel() {
var tableElement = document.getElementById("table-product-list");
var sourceData = "data:text/csv;charset=utf-8,";
var i = 0;
while (row = tableElement.rows[i]) {
sourceData += ([
row.cells[0].innerText,
row.cells[1].innerText,
row.cells[2].innerText,
row.cells[3].innerText
]).join(",") + "rn";
i++;
}
window.location.href = encodeURI(sourceData);
}
Option 3 – Export HTML table to excel using jQuery-based plugin
The jQuery table2excel plugin is a popular solution to arrive HTML to excel export.
It has many features with the export core functionalities. Those are,
- Excludes/includes HTML tags like inputs, links, or images that are in the source table HTML.
- Excludes some table components with the reference of that particular element’s class or id selectors.
- Provides properties to set file name with which the exported data will be downloaded to the browser.
Include the jQuery and the table2excel plugin file in the <head> section as below.
There are alternative methods to install the table2excel plugin. Its Github page provides documentation of installation and usage methodologies.
<script
src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script
src="https://cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>
Then, initiate the plugin class by pointing to the source HTML table element.
The below JavaScript does the initiation. During instantiation, it sets the options array to specify a file name, extension and applicable flags.
function exportCSVExcel() {
$('#table-product-list').table2excel({
exclude: ".no-export",
filename: "download.xls",
fileext: ".xls",
exclude_links: true,
exclude_inputs: true
});
}
This example uses the same HTML table source for the export operation. The difference is that the source table content marks some of the rows with no-export class.
This class is configured in the above script with exclude property of this plugin class.
Conclusion:
So, we have seen three simple implementations of adding HTML table to excel feature. Though the export feature is vital, we must have a component that provides a seamless outcome.
I hope, the above solution can be a good base for creating such components. It is adaptable for sure to dock more features to have an efficient excel report generation tool.
View demoDownload
↑ Back to Top
Credit: Source link
