In the previous mail service, I accept explained how we can read csv file using javascript and HTML 5 filereader, now in this post, I have explained how nosotros tin use external library like xlsx to parse or read excel file using javascript and show it's contents in HTML table. When file is uploaded using Javascript, it is read as Binary string initially, and then binary data is read using xlsx plugin.

Read XLSX using Javascript

Let's beging by adding simple HTML file input and push to upload file

          <input type="file" id="fileUpload" /> <input type="button" id="upload" value="Upload" onclick="UploadProcess()" /> <br/>  <div id="ExcelTable"></div>

I have also included empty HTML div to create table within it from our Excel file.

Now, we will create the function to upload the file and procedure the Excel file to get data from it and convert it into HTML tabular array.

          <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script> <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.thirteen.five/jszip.js"></script> <script type="text/javascript">     function UploadProcess() {         //Reference the FileUpload element.         var fileUpload = document.getElementById("fileUpload");           //Validate whether File is valid Excel file.         var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;         if (regex.test(fileUpload.value.toLowerCase())) {             if (typeof (FileReader) != "undefined") {                 var reader = new FileReader();                   //For Browsers other than IE.                 if (reader.readAsBinaryString) {                     reader.onload = part (eastward) {                         GetTableFromExcel(e.target.outcome);                     };                     reader.readAsBinaryString(fileUpload.files[0]);                 } else {                     //For IE Browser.                     reader.onload = function (e) {                         var data = "";                         var bytes = new Uint8Array(e.target.upshot);                         for (var i = 0; i < bytes.byteLength; i++) {                             information += Cord.fromCharCode(bytes[i]);                         }                         GetTableFromExcel(data);                     };                     reader.readAsArrayBuffer(fileUpload.files[0]);                 }             } else {                 alert("This browser does not back up HTML5.");             }         } else {             alarm("Delight upload a valid Excel file.");         }     };     function GetTableFromExcel(data) {         //Read the Excel File data in binary         var workbook = XLSX.read(data, {             type: 'binary'         });           //get the proper noun of First Sheet.         var Sheet = workbook.SheetNames[0];           //Read all rows from Get-go Canvas into an JSON array.         var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);           //Create a HTML Tabular array element.         var myTable  = document.createElement("table");         myTable.border = "1";           //Add the header row.         var row = myTable.insertRow(-1);           //Add together the header cells.         var headerCell = document.createElement("TH");         headerCell.innerHTML = "Id";         row.appendChild(headerCell);           headerCell = document.createElement("TH");         headerCell.innerHTML = "Proper name";         row.appendChild(headerCell);           headerCell = document.createElement("TH");         headerCell.innerHTML = "Country";         row.appendChild(headerCell);                  headerCell = certificate.createElement("TH");         headerCell.innerHTML = "Age";         row.appendChild(headerCell);                  headerCell = document.createElement("Th");         headerCell.innerHTML = "Engagement";         row.appendChild(headerCell);                    headerCell = certificate.createElement("Thursday");         headerCell.innerHTML = "Gender";         row.appendChild(headerCell);             //Add together the data rows from Excel file.         for (var i = 0; i < excelRows.length; i++) {             //Add together the data row.             var row = myTable.insertRow(-i);               //Add together the data cells.             var cell = row.insertCell(-i);             prison cell.innerHTML = excelRows[i].Id;               jail cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Name;               cell = row.insertCell(-1);             prison cell.innerHTML = excelRows[i].Country;                          prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Historic period;                          jail cell = row.insertCell(-ane);             prison cell.innerHTML = excelRows[i].Appointment;                          jail cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Gender;         }                    var ExcelTable = document.getElementById("ExcelTable");         ExcelTable.innerHTML = "";         ExcelTable.appendChild(myTable);     }; </script>

In the above Javascript lawmaking, we are start calculation references of XLSX plugin files and then adding 2 functions

  1. UploadProcess: Uploads the file on button click and convert information technology into Binary data, it also check if Browser is IE so procedure accordingly to convert file into binary.
  2. ProcessExcel: this function takes the binary data, reads the Sheet proper noun, create Table element and append each row in it.

I accept explained lines of code using Comment.

Suppose our sample Excel file looks like this

So, if use the higher up code in HTML/Javascript, output will be as below

javascript-read-xlsx-example.gif

Hither is the dabble link https://jsfiddle.internet/abj98oxf/3/

Read XLS file using Javascript

In Similar way, we can read .xls (excel) file also and show it in HTML table, I will repeat the same code, just few lines of lawmaking is different, besides nosotros will be using diferent plugin in information technology which is for .xls

          <input blazon="file" id="fileUpload" /> <input blazon="button" id="upload" value="Upload" onclick="UploadProcess()" /> <br/>  <div id="ExcelTable"></div>  <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.seven.4-a/xls.js"></script> <script blazon="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.thirteen.5/jszip.js"></script> <script type="text/javascript">     function UploadProcess() {         //Reference the FileUpload chemical element.         var fileUpload = document.getElementById("fileUpload");           //Validate whether File is valid Excel file.         var regex = /^([a-zA-Z0-nine\s_\\.\-:])+(.xls|.xlsx)$/;         if (regex.exam(fileUpload.value.toLowerCase())) {             if (typeof (FileReader) != "undefined") {                 var reader = new FileReader();                   //For Browsers other than IE.                 if (reader.readAsBinaryString) {                     reader.onload = office (e) {                         GetTableFromExcel(e.target.upshot);                     };                     reader.readAsBinaryString(fileUpload.files[0]);                 } else {                     //For IE Browser.                     reader.onload = function (due east) {                         var data = "";                         var bytes = new Uint8Array(e.target.result);                         for (var i = 0; i < bytes.byteLength; i++) {                             information += String.fromCharCode(bytes[i]);                         }                         GetTableFromExcel(data);                     };                     reader.readAsArrayBuffer(fileUpload.files[0]);                 }             } else {                 alarm("This browser does not support HTML5.");             }         } else {             alert("Please upload a valid Excel file.");         }     };     function GetTableFromExcel(data) {         //Read the Excel File data in binary         var cfb = XLS.CFB.read(data, {type: 'binary'});         var workbook = XLS.parse_xlscfb(cfb);           //get the name of Offset Sail.         var Sheet = workbook.SheetNames[0];           //Read all rows from Beginning Sheet into an JSON array.         var excelRows = XLS.utils.sheet_to_row_object_array(workbook.Sheets[Sail]);           //Create a HTML Table element.         var myTable  = document.createElement("tabular array");         myTable.border = "1";           //Add the header row.         var row = myTable.insertRow(-ane);           //Add together the header cells.         var headerCell = document.createElement("TH");         headerCell.innerHTML = "Id";         row.appendChild(headerCell);           headerCell = document.createElement("TH");         headerCell.innerHTML = "Name";         row.appendChild(headerCell);           headerCell = document.createElement("Thursday");         headerCell.innerHTML = "State";         row.appendChild(headerCell);                  headerCell = document.createElement("Th");         headerCell.innerHTML = "Age";         row.appendChild(headerCell);                  headerCell = certificate.createElement("Thursday");         headerCell.innerHTML = "Engagement";         row.appendChild(headerCell);                    headerCell = document.createElement("Th");         headerCell.innerHTML = "Gender";         row.appendChild(headerCell);             //Add the data rows from Excel file.         for (var i = 0; i < excelRows.length; i++) {             //Add the data row.             var row = myTable.insertRow(-1);               //Add the data cells.             var jail cell = row.insertCell(-1);             prison cell.innerHTML = excelRows[i].Id;               prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Proper name;               prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].State;                          cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Age;                          cell = row.insertCell(-one);             cell.innerHTML = excelRows[i].Engagement;                          cell = row.insertCell(-one);             cell.innerHTML = excelRows[i].Gender;         }                    var ExcelTable = certificate.getElementById("ExcelTable");         ExcelTable.innerHTML = "";         ExcelTable.appendChild(myTable);     }; </script>

The XLS file upload code is same equally .XLSX one was, here are the changes

  1. We included difference JS plugin file : <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js"></script>
  2. Changed few lines of code for function "GetTableFromExcel"
                                  //Read the Excel File information in binary          var cfb = XLS.CFB.read(data, {blazon: 'binary'});         var workbook = XLS.parse_xlscfb(cfb);           //go the name of First Canvass.         var Sheet = workbook.SheetNames[0];           //Read all rows from Get-go Sheet into an JSON assortment.         var excelRows = XLS.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);?

Rest of the code remains aforementioned.

Here is the working fiddle link : https://jsfiddle.internet/y3tx8wk4/ane/

Note: You need to pass .xls file in this lawmaking to brand it piece of work.