In the previous mail service, I take explained how we tin read csv file using javascript and HTML 5 filereader, now in this post, I have explained how nosotros tin utilize 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 every bit Binary string initially, and so binary data is read using xlsx plugin.

Read XLSX using Javascript

Let'due south beging by adding simple HTML file input and push button to upload file

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

I accept likewise included empty HTML div to create table inside it from our Excel file.

Now, we will create the part to upload the file and process the Excel file to get data from it and catechumen it into HTML table.

          <script blazon="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.xiii.v/xlsx.full.min.js"></script> <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script> <script type="text/javascript">     part UploadProcess() {         //Reference the FileUpload chemical 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 (e) {                         GetTableFromExcel(east.target.issue);                     };                     reader.readAsBinaryString(fileUpload.files[0]);                 } else {                     //For IE Browser.                     reader.onload = office (e) {                         var information = "";                         var bytes = new Uint8Array(e.target.effect);                         for (var i = 0; i < bytes.byteLength; i++) {                             data += Cord.fromCharCode(bytes[i]);                         }                         GetTableFromExcel(information);                     };                     reader.readAsArrayBuffer(fileUpload.files[0]);                 }             } else {                 alert("This browser does not support HTML5.");             }         } else {             alarm("Please upload a valid Excel file.");         }     };     part GetTableFromExcel(information) {         //Read the Excel File information in binary         var workbook = XLSX.read(data, {             type: 'binary'         });           //get the name of Showtime Sheet.         var Canvass = workbook.SheetNames[0];           //Read all rows from Start Sheet into an JSON array.         var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[Sail]);           //Create a HTML Table chemical 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 = certificate.createElement("TH");         headerCell.innerHTML = "Name";         row.appendChild(headerCell);           headerCell = document.createElement("TH");         headerCell.innerHTML = "Country";         row.appendChild(headerCell);                  headerCell = document.createElement("Th");         headerCell.innerHTML = "Age";         row.appendChild(headerCell);                  headerCell = document.createElement("TH");         headerCell.innerHTML = "Date";         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 information row.             var row = myTable.insertRow(-1);               //Add the data cells.             var jail cell = row.insertCell(-1);             jail cell.innerHTML = excelRows[i].Id;               cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Proper name;               prison cell = row.insertCell(-1);             jail cell.innerHTML = excelRows[i].Country;                          prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Age;                          prison cell = row.insertCell(-one);             cell.innerHTML = excelRows[i].Date;                          cell = row.insertCell(-one);             jail cell.innerHTML = excelRows[i].Gender;         }                    var ExcelTable = certificate.getElementById("ExcelTable");         ExcelTable.innerHTML = "";         ExcelTable.appendChild(myTable);     }; </script>

In the above Javascript lawmaking, we are outset adding references of XLSX plugin files and so calculation two functions

  1. UploadProcess: Uploads the file on push click and convert information technology into Binary data, information technology also check if Browser is IE so process appropriately to catechumen 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 have explained lines of code using Comment.

Suppose our sample Excel file looks like this

And then, if use the above lawmaking in HTML/Javascript, output will be as below

javascript-read-xlsx-example.gif

Here is the dabble link https://jsfiddle.net/abj98oxf/three/

Read XLS file using Javascript

In Like style, we tin can read .xls (excel) file also and show information technology in HTML table, I will echo the aforementioned code, just few lines of code is different, likewise nosotros will be using diferent plugin in it which is for .xls

          <input type="file" id="fileUpload" /> <input type="push" id="upload" value="Upload" onclick="UploadProcess()" /> <br/>  <div id="ExcelTable"></div>  <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.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 element.         var fileUpload = certificate.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 = function (e) {                         GetTableFromExcel(eastward.target.result);                     };                     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++) {                             data += String.fromCharCode(bytes[i]);                         }                         GetTableFromExcel(data);                     };                     reader.readAsArrayBuffer(fileUpload.files[0]);                 }             } else {                 alert("This browser does not back up HTML5.");             }         } else {             alert("Please upload a valid Excel file.");         }     };     role 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 Start Sheet.         var Sheet = workbook.SheetNames[0];           //Read all rows from Get-go Sheet into an JSON array.         var excelRows = XLS.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);           //Create a HTML Table element.         var myTable  = document.createElement("table");         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 = "Proper noun";         row.appendChild(headerCell);           headerCell = document.createElement("TH");         headerCell.innerHTML = "Country";         row.appendChild(headerCell);                  headerCell = document.createElement("Thursday");         headerCell.innerHTML = "Age";         row.appendChild(headerCell);                  headerCell = certificate.createElement("TH");         headerCell.innerHTML = "Engagement";         row.appendChild(headerCell);                    headerCell = document.createElement("Th");         headerCell.innerHTML = "Gender";         row.appendChild(headerCell);             //Add together the data rows from Excel file.         for (var i = 0; i < excelRows.length; i++) {             //Add the information row.             var row = myTable.insertRow(-1);               //Add the information cells.             var cell = row.insertCell(-1);             jail cell.innerHTML = excelRows[i].Id;               cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Name;               cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Country;                          cell = row.insertCell(-one);             cell.innerHTML = excelRows[i].Age;                          prison cell = row.insertCell(-ane);             prison cell.innerHTML = excelRows[i].Date;                          cell = row.insertCell(-i);             cell.innerHTML = excelRows[i].Gender;         }                    var ExcelTable = document.getElementById("ExcelTable");         ExcelTable.innerHTML = "";         ExcelTable.appendChild(myTable);     }; </script>

The XLS file upload lawmaking is same as .XLSX one was, here are the changes

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

Rest of the code remains same.

Here is the working fiddle link : https://jsfiddle.net/y3tx8wk4/1/

Note: You need to pass .xls file in this code to brand information technology work.