Export HTML Table as XLSX File
Contrary to some opinions floating around the Internet, it is in fact possible to export an HTML table as an .xlsx document (as well as .xls, .csv, and others), using only client side code, thanks to the useful Sheet JS library. The .xlsx file format is the one used by the most recent version of Excel, so having a native way to export data in this format directly in the client browser can eliminate the need for requests to be sent to and from servers, as is conventionally done, and has many other advantages.
A perfect use case is when you retrieve data from some endpoint and display it as an HTML table using DataTables, and then giving the user an option to filter, modify, and export this data to their local machine. Below is a very simple demo of data I took from that website, and below that you can specify the file name and export file type below.
| Name | Position | Office | Age | Start date | Salary |
|---|---|---|---|---|---|
| Airi Satou | Accountant | Tokyo | 33 | 2008/11/28 | $162,700 |
| Angelica Ramos | Chief Executive Officer (CEO) | London | 47 | 2009/10/09 | $1,200,000 |
| Ashton Cox | Junior Technical Author | San Francisco | 66 | 2009/01/12 | $86,000 |
| Bradley Greer | Software Engineer | London | 41 | 2012/10/13 | $132,000 |
| Brenden Wagner | Software Engineer | San Francisco | 28 | 2011/06/07 | $206,850 |
| Brielle Williamson | Integration Specialist | New York | 61 | 2012/12/02 | $372,000 |
| Bruno Nash | Software Engineer | London | 38 | 2011/05/03 | $163,500 |
| Caesar Vance | Pre-Sales Support | New York | 21 | 2011/12/12 | $106,450 |
| Cara Stevens | Sales Assistant | New York | 46 | 2011/12/06 | $145,600 |
| Cedric Kelly | Senior Javascript Developer | Edinburgh | 22 | 2012/03/29 | $433,060 |
Please use the two inputs below to test this out.
File name:
File type:
There are four JS dependencies needed:
-https://unpkg.com/xlsx/dist/shim.min.js
-https://unpkg.com/xlsx/dist/xlsx.full.min.js
-https://unpkg.com/blob.js@1.0.1/Blob.js
-https://unpkg.com/file-saver@1.3.3/FileSaver.js
The drawback of generating XLSX, XLS, and CSV files in this manner is the fact that the four dependencies total a whopping 943 KB. I recommend only loading that JS file on the pages that are required to use this exporting functionality.
The somewhat over-engineered script below can be used to add an event listener to generate the table:
function createXLSXTableDemo() {
var btn = document.getElementById('createXLSX');
var fileName = document.getElementById("fileName");
var fileType = document.getElementById("fileType");
btn.addEventListener('click', function() {
var table = document.getElementById('tableToExport');
var wb = XLSX.utils.table_to_book(table, {sheet: "Sheet JS"});
return XLSX.writeFile(wb, null || (fileName.value + '.' + (fileType.value || 'xlsx')));
});
}