In this article, we will learn how to read an Excel file from the client side and display its contents in an HTML table by making use of the FileReader() API in HTML5 & jQuery.
The two important jQuery plugins we used here are "xlsx.core.min.js" and "xls.core.min.js" which are used to convert the data from Excel to a JSON array.
First, we will create a File Upload button, then an HTML table which is hidden first, and lastly ab Input button which upon clicking, calls the function to export Excel data to the HTML table.
- <input type="file" id="excelfile" />
- <input type="button" id="viewfile" value="Export To Table" onclick="ExportToTable()" />
- <br />
- <br />
- <table id="exceltable">
- </table>
Running the page will look like below.
Now, we reference the jQuery plugin files "xlsx.core.min.js" and "xls.core.min.js" in the head section. - <script src="jquery-1.10.2.min.js" type="text/javascript"></script>
- <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>
- <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js"></script>
The JavaScript function ExportToTable() is given below.
- function ExportToTable() {
- var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
-
- if (regex.test($("#excelfile").val().toLowerCase())) {
- var xlsxflag = false;
- if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {
- xlsxflag = true;
- }
-
- if (typeof (FileReader) != "undefined") {
- var reader = new FileReader();
- reader.onload = function (e) {
- var data = e.target.result;
-
- if (xlsxflag) {
- var workbook = XLSX.read(data, { type: 'binary' });
- }
- else {
- var workbook = XLS.read(data, { type: 'binary' });
- }
-
- var sheet_name_list = workbook.SheetNames;
-
- var cnt = 0;
- sheet_name_list.forEach(function (y) {
-
- if (xlsxflag) {
- var exceljson = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
- }
- else {
- var exceljson = XLS.utils.sheet_to_row_object_array(workbook.Sheets[y]);
- }
- if (exceljson.length > 0 && cnt == 0) {
- BindTable(exceljson, '#exceltable');
- cnt++;
- }
- });
- $('#exceltable').show();
- }
- if (xlsxflag) {
- reader.readAsArrayBuffer($("#excelfile")[0].files[0]);
- }
- else {
- reader.readAsBinaryString($("#excelfile")[0].files[0]);
- }
- }
- else {
- alert("Sorry! Your browser does not support HTML5!");
- }
- }
- else {
- alert("Please upload a valid Excel file!");
- }
- }
Other two functions which are called in the above function are BindTable() and BindTableHeader().
- function BindTable(jsondata, tableid) {
- var columns = BindTableHeader(jsondata, tableid);
- for (var i = 0; i < jsondata.length; i++) {
- var row$ = $('<tr/>');
- for (var colIndex = 0; colIndex < columns.length; colIndex++) {
- var cellValue = jsondata[i][columns[colIndex]];
- if (cellValue == null)
- cellValue = "";
- row$.append($('<td/>').html(cellValue));
- }
- $(tableid).append(row$);
- }
- }
- function BindTableHeader(jsondata, tableid) {
- var columnSet = [];
- var headerTr$ = $('<tr/>');
- for (var i = 0; i < jsondata.length; i++) {
- var rowHash = jsondata[i];
- for (var key in rowHash) {
- if (rowHash.hasOwnProperty(key)) {
- if ($.inArray(key, columnSet) == -1) {
- columnSet.push(key);
- headerTr$.append($('<th/>').html(key));
- }
- }
- }
- }
- $(tableid).append(headerTr$);
- return columnSet;
- }
The basic idea of the above scripts is that first we read the data from Excel file as an ArrayBuffer or raw binary data depending on the extension of the Excel file, using the FileReader() API of HTML5.
Then, we use jQuery plugins to convert that data into a JSON object. Next, we iterate through the JSON object and bind it to an HTML table. Our sample Excel file contains the data of certain employees, as given below.
Now, on selecting this Excel file and clicking on the "Export To Table" button, we will export the excel data to the table, as shown below.
That's it. Our Excel to the table is ready!
This is just a simple example of reading an Excel sheet and displaying the data from the first sheet into a table. You can always explore the script and change it to read multiple sheets and so on.
Please note that this method will be supported only on browsers which support HTML5. You can check the browsers which support HTML5
here.
Also, the above script is tested with Excel file having a maximum of 15 columns and 10,000 rows. Since this script uses iteration for binding, there may be performance issues while using huge Excel files.
Reference
https://github.com/SheetJS/js-xlsx https://github.com/SheetJS/js-xls
Summary
In this article, we have learned how to make use of FileReader() API of HTML5 and jQuery to export data from an Excel file to an HTML table.
Hope this will be helpful!