Export HTML table data to excel using JavaScript

Export HTML Table Data to Excel using JavaScript

Export data to Excel is very useful on the data list for nearly every web application. The export feature helps to download the data list as a file format for offline use. Excel format is ideal for exporting data in a file. Mostly the server-side method is used for export data to excel using PHP. But if you want a client-side solution to export table data to excel, it can be easily done using JavaScript.

The client-side export functionality makes the web application user-friendly. Using JavaScript, the HTML table data can be easily exported without page refresh. In this tutorial, we will show you how to export HTML table data to excel using JavaScript. The JavaScript export functionality can be used in the member list, product list, or other lists to download the data list in excel file format.

Export HTML Table Data to Excel

JavaScript Code:
The exportTableToExcel() function convert HTML table data to excel and download as XLS file (.xls).

• tableID – Required. Specify the HTML table ID to export data from.
• filename – Optional. Specify the file name to download excel data.

function exportTableToExcel(tableID, filename = '')< var downloadLink; var dataType = 'application/vnd.ms-excel'; var tableSelect = document.getElementById(tableID); var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20'); // Specify file name filename = filename?filename+'.xls':'excel_data.xls'; // Create download link element downloadLink = document.createElement("a"); document.body.appendChild(downloadLink); if(navigator.msSaveOrOpenBlob)< var blob = new Blob(['\ufeff', tableHTML], < type: dataType >); navigator.msSaveOrOpenBlob( blob, filename); >else< // Create a link to the file downloadLink.href = 'data:' + dataType + ', ' + tableHTML; // Setting the file name downloadLink.download = filename; //triggering the function downloadLink.click(); > >

HTML Table Data:
The HTML table contains some users data with some basic fields like name, email, country.

table id="tblData"> tr> th>Nameth> th>Emailth> th>Countryth> tr> tr> td>John Doetd> td>john@gmail.comtd> td>USAtd> tr> tr> td>Michael Addisontd> td>michael@gmail.comtd> td>UKtd> tr> tr> td>Sam Farmertd> td>sam@gmail.comtd> td>Francetd> tr> table>

The button triggers exportTableToExcel() function to export HTML table data using JavaScript.

button onclick="exportTableToExcel('tblData')">Export Table Data To Excel Filebutton>

If you want to export data with the custom file name, pass your desired file name in the exportTableToExcel() function.

button onclick="exportTableToExcel('tblData', 'members-data')">Export Table Data To Excel Filebutton>

Conclusion

Our example code helps you to add export functionality in the HTML table without any third-party jQuery plugin or server-side script. You can easily export the table data using minimal JavaScript code. Also, the functionality of the example code can be enhanced or customized easily as per your needs.

Are you want to get implementation help, or modify or enhance the functionality of this script? Click Here to Submit Service Request

If you have any questions about this script, submit it to our QA community — Ask Question

Источник

Webslesson

PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Tuesday, 13 July 2021

How to Export Html Table to Excel Sheet using JavaScript

In this tutorial you can find the solution of How to Export or Download the HTML Table Data in Excel Sheet by using JavaScript. Exporting Data to Excel is required feature in our web application. Because by export data functionality will helps to download data from web application to different file format for offline use of data and for this excel format is an ideal for exporting data in file for offline use. There many tutorial we have published for export data to excel at server side scripting using PHP. But if we can perform at client-side for export data into Excel sheet, so it will reduce load on server. So for this for export data to excel , here we will use JavaScript for perform client-side export data to excel sheet.

The client-side export feature will makes our web application more user-friendly. So with the help of JavaScript, we can export HTML table data to Excel format without refresh of web page. Under this tutorial, you can learn How to export HTML table data to excel using JavaScript. In this tutorial, we will use SheetJS JavaScript Library for export HTML table data to Excel.

Steps to Export HTML Table Data to Excel using JavaScript

1. HTML Table Data

For Export HTML data to Excel, here first we have to load some data in HTML table. So here we have make fetch employee table data and load in HTML table with table column like name, address, gender, designation and age. Here we have create HTML table with id employee_data. So this id we will use for fetch this HTML table data in JavaScript code. Under this HTML code we have make one button tag with id export_button, so when use has click on this button, then HTML table data will be download in Excel file format without refresh of web page using JavaScript.

 query($query); ?>         
Export HTML table data to excel using JavaScript
 
 
 
Sample Data
 
Export 
 
 
 
  '; > ?>
 
Name
 
Address
 
Gender
 
Designation
 
Age
 
 
'.$row["name"].'
 
'.$row["address"].'
 
'.$row["gender"].'
 
'.$row["designation"].'
 
'.$row["age"].'
 
 
 
 
    

2. JavaScript Code

In this tutorial, we have use SheetJS JavaScript Library for export HTML table data to Excel using JavaScript. So first we have to include following SheetJS library link at header of this HTML web page.

In JavaScript code part, first we have make html_table_to_excel(type) function. This function has use SheetJS Library function and convert or Write HTML table data to Excel format and download in browser without refresh of web page.

Once function is ready then we have to called html_table_to_excel(type) function on button click event, so for trigger button click event, we have use addEventListener method. So when user has click on button then html_table_to_excel(type) function has been called with xlsx file type. So it will download HTML table data in .xlsx format Excel file in browser without refresh of web page at client-side.

 function html_table_to_excel(type) < var data = document.getElementById('employee_data'); var file = XLSX.utils.table_to_book(data, ); XLSX.write(file, < bookType: type, bookSST: true, type: 'base64' >); XLSX.writeFile(file, 'file.' + type); > const export_button = document.getElementById('export_button'); export_button.addEventListener('click', () => < html_table_to_excel('xlsx'); >); 

Conclusion

This tutorial will helps you to add export feature of download HTML table data in Excel sheet without using third-party jQuery plugin or any server-side script. By follow this tutorial you can easily export HTML table data to Excel using minimal JavaScript code.

If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

linways / table-to-excel Public archive

Javascript library to create «valid» excel file from html table with styles

License

linways/table-to-excel

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Export HTML table to valid excel file effortlessly. This library uses exceljs/exceljs under the hood to create the excel.
(Initial version of this library was using protobi/js-xlsx, it can be found here)

script type pl-s">text/javascript" src pl-s">../dist/tableToExcel.js">script>

npm install @linways/table-to-excel --save

import TableToExcel from "@linways/table-to-excel";

Create your HTML table as normal.
To export content of table #table1 run:

TableToExcel.convert(document.getElementById("table1"));

TableToExcel.convert(document.getElementById("table1"),  name: "table1.xlsx", sheet:  name: "Sheet 1" > >);

Check this pen for working example.

Cell types can be set using the following data attributes:

Attribute	Description	Possible Values
data-t	To specify the data type of a cell	s : String (Default) n : Number b : Boolean d : Date
data-hyperlink	To add hyper link to cell	External URL or hyperlink to another sheet
data-error	To add value of a cell as error

 td data-t pl-s">n">2500td> td data-t pl-s">d">05-23-2018td>  td data-t pl-s">b">truetd> td data-t pl-s">b">0td> td data-hyperlink pl-s">https://google.com">Googletd>

All styles are set using data attributes on td tags. There are 5 types of attributes: data-f-* , data-a-* , data-b-* , data-fill-* and data-num-fmt which corresponds to five top-level attributes font , alignment , border , fill and numFmt .

Category	Attribute	Description	Values
font	data-f-name	Font name	«Calibri» ,»Arial» etc.
data-f-sz	Font size	«11» // font size in points
data-f-color	Font color	A hex ARGB value. Eg: FFFFOOOO for opaque red.
data-f-bold	Bold	true or false
data-f-italic	Italic	true or false
data-underline	Underline	true or false
data-f-strike	Strike	true or false
Alignment	data-a-h	Horizontal alignment	left , center , right , fill , justify , centerContinuous , distributed
data-a-v	Vertical alignment	bottom , middle , top , distributed , justify
data-a-wrap	Wrap text	true or false
data-a-indent	Indent	Integer
data-a-rtl	Text direction: Right to Left	true or false
data-a-text-rotation	Text rotation	0 to 90
-1 to -90
vertical
Border	data-b-a-s	Border style (all borders)	Refer BORDER_STYLES
data-b-t-s	Border top style	Refer BORDER_STYLES
data-b-b-s	Border bottom style	Refer BORDER_STYLES
data-b-l-s	Border left style	Refer BORDER_STYLES
data-b-r-s	Border right style	Refer BORDER_STYLES
data-b-a-c	Border color (all borders)	A hex ARGB value. Eg: FFFFOOOO for opaque red.
data-b-t-c	Border top color	A hex ARGB value.
data-b-b-c	Border bottom color	A hex ARGB value.
data-b-l-c	Border left color	A hex ARGB value.
data-b-r-c	Border right color	A hex ARGB value.
Fill	data-fill-color	Cell background color	A hex ARGB value.
numFmt	data-num-fmt	Number Format	«0»
«0.00%»
«0.0%» // string specifying a custom format
«0.00%;\(0.00%\);\-;@» // string specifying a custom format, escaping special characters

BORDER_STYLES: thin , dotted , dashDot , hair , dashDotDot , slantDashDot , mediumDashed , mediumDashDotDot , mediumDashDot , medium , double , thick

To exclude a cell or a row from the exported excel add data-exclude=»true» to the corresponding td or tr .
Example:

 tr data-exclude pl-s">true"> td>Excluded rowtd> td>Somethingtd> tr> tr> td>Included Celltd> td data-exclude pl-s">true">Excluded Celltd> td>Included Celltd> tr>

Источник