Reading An Excel File Using HTML 5 And jQuery

Introduction

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)$/;  
    /* Checks whether the file is a valid excel file */  
    if (regex.test($("#excelfile").val().toLowerCase())) {  
        var xlsxflag = false; /* Flag for checking whether excel is .xls format or .xlsx format */  
        if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {  
            xlsxflag = true;  
        }  
        /* Checks whether the browser supports HTML5 */  
        if (typeof (FileReader) != "undefined") {  
            var reader = new FileReader();  
            reader.onload = function (e) {  
                var data = e.target.result;  
                /* Converts the excel data into object */  
                if (xlsxflag) {  
                    var workbook = XLSX.read(data, { type: 'binary' });  
                }  
                else {  
                    var workbook = XLS.read(data, { type: 'binary' });  
                }  
                /* Gets all the sheet names of excel into a variable */  
                var sheet_name_list = workbook.SheetNames;  
  
                var cnt = 0; /* This is used for restricting the script to consider only the first sheet of excel */  
                sheet_name_list.forEach(function (y) { /* Iterate through all sheets */  
                    /* Convert the cell value to JSON */  
                    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) { /* If the excel file is .xlsx extension, then create an Array Buffer from excel */  
                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!");  
    }  
}