24

Convert / Export HTML table data into excel in JavaScript.

 2 years ago
source link: https://codepedia.info/javascript-export-html-table-data-to-excel
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Convert / Export HTML table data into excel in JavaScript.

Satinder Singh / June 11, 2021 / 0 Comments

JavaScript convert table data to excel XLS: Here in this tutorial we see, how on the client-side we can create an excel file from our HTML table data. i.e Export HTML table data into excel (.xlsx) using javascript.

There are many libraries available which create CSV file or xlsx file from HTML table, but all are giving a prompt message. That is when we open that excel file it prompts a message as The file format and extension of the filename don't match. The file could be corrupted or unsafe. 

Today in this article will use SheetJS, which allow us to create and open excel file without any prompt message and that's pure in javascript. The second advantage of using the SheetJs library is that it can easily export large HTML tables into excel, an example is provided below. You can also check my article on how to convert HTML to image at the client-side. 

Step to export HTML table to excel.

  1. HTML Markup: Add table with some data.
  2. Import SheetJS Library
  3. Javascript code: Using SheetJS library export table data into an excel file.

Output:

HTML Markup: Add table with data and button tag.

Here first we add an HTML table with some dummy data and a button tag. Our table HTML markup looks as written below.

<table id="tbl_exporttable_to_xls" border="1">
	<thead>
		<th>Sr</th>
		<th>Name</th>
		<th>Location</th>
		<th>Job Profile</th>
	</thead>
	<tbody>
		<tr>
			<td>1</td>
			<td><p>Amit Sarna</p></td>
			<td>Florida</td>
			<td>Data Scientist</td>
		</tr>
		<tr>
			<td>2</td>
			<td><p>Sagar Gada</p></td>
			<td>California</td>
			<td>Sr FullStack Dev</td>
		</tr>
		<tr>
			<td>3</td>
			<td><p>Ricky Marck</p></td>
			<td>Nevada</td>
			<td>Sr .Net Dev</td>
		</tr>           
	</tbody>
</table>
<button onclick="ExportToExcel('xlsx')">Export table to excel</button>

Download and Import SheetJS Library on our webpage

To convert HTML table data into excel, we need to use the SheetJS library. Using SheetJs we can easily convert our table data into an Xls file. We can download the js file from Github or directly use the CDN hosted file.

Import library code as written below:

<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>

We are done with HTML markup and import Sheetjs library. Next, we have to add and call javascript function i.e ExportToExcel on button click.

JavaScript code: Using Sheetjs library export table data into an excel file.

Here on the button tag, we add an onclick event and call the js method ie ExportToExcel. Final javascript method as written below:

function ExportToExcel(type, fn, dl) {
       var elt = document.getElementById('tbl_exporttable_to_xls');
       var wb = XLSX.utils.table_to_book(elt, { sheet: "sheet1" });
       return dl ?
         XLSX.write(wb, { bookType: type, bookSST: true, type: 'base64' }):
         XLSX.writeFile(wb, fn || ('MySheetName.' + (type || 'xlsx')));
    }

Here in the above javascript function, tbl_exporttable_to_xls is the id of our HTML table, whose data we want to export. Also, we set type base64, so there would be no problem for older browsers i.e IE browser.

In Internet Explorer ie browser using SheetJS, we can easily export HTML table data to excel.

View Demo

Conclusion: Here using SheetJS we able to export the table into an excel file. Also as other library displays popup message while open excel file, here with Sheetjs it gets open without any popup message. I find this best javascript library for converting data into an excel file.

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to do a favor, then Buy me a coffee


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK