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

Read XLSX using Javascript

Permit's beging past adding uncomplicated HTML file input and button 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 inside information technology from our Excel file.

At present, nosotros will create the part to upload the file and process the Excel file to get data from information technology and convert information technology into HTML table.

          <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.thirteen.5/xlsx.total.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-nine\s_\\.\-:])+(.xls|.xlsx)$/;         if (regex.examination(fileUpload.value.toLowerCase())) {             if (typeof (FileReader) != "undefined") {                 var reader = new FileReader();                   //For Browsers other than IE.                 if (reader.readAsBinaryString) {                     reader.onload = function (due east) {                         GetTableFromExcel(east.target.result);                     };                     reader.readAsBinaryString(fileUpload.files[0]);                 } else {                     //For IE Browser.                     reader.onload = part (e) {                         var data = "";                         var bytes = new Uint8Array(e.target.effect);                         for (var i = 0; i < bytes.byteLength; i++) {                             data += String.fromCharCode(bytes[i]);                         }                         GetTableFromExcel(information);                     };                     reader.readAsArrayBuffer(fileUpload.files[0]);                 }             } else {                 alert("This browser does not back up HTML5.");             }         } else {             warning("Delight upload a valid Excel file.");         }     };     function GetTableFromExcel(data) {         //Read the Excel File data in binary         var workbook = XLSX.read(information, {             type: 'binary'         });           //get the name of First Sheet.         var Sheet = workbook.SheetNames[0];           //Read all rows from First Sheet into an JSON assortment.         var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[Sail]);           //Create a HTML Table element.         var myTable  = certificate.createElement("table");         myTable.border = "one";           //Add the header row.         var row = myTable.insertRow(-1);           //Add the header cells.         var headerCell = document.createElement("Th");         headerCell.innerHTML = "Id";         row.appendChild(headerCell);           headerCell = document.createElement("Thursday");         headerCell.innerHTML = "Name";         row.appendChild(headerCell);           headerCell = certificate.createElement("Thursday");         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 together the information row.             var row = myTable.insertRow(-1);               //Add the data cells.             var prison cell = row.insertCell(-1);             jail cell.innerHTML = excelRows[i].Id;               prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Proper noun;               prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Country;                          cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Age;                          cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Date;                          prison cell = row.insertCell(-one);             cell.innerHTML = excelRows[i].Gender;         }                    var ExcelTable = document.getElementById("ExcelTable");         ExcelTable.innerHTML = "";         ExcelTable.appendChild(myTable);     }; </script>

In the above Javascript code, nosotros are first adding references of XLSX plugin files and then adding 2 functions

  1. UploadProcess: Uploads the file on button click and convert it into Binary information, information technology likewise check if Browser is IE then process accordingly to convert file into binary.
  2. ProcessExcel: this function takes the binary data, reads the Canvass proper name, create Tabular array element and append each row in it.

I have explained lines of code using Comment.

Suppose our sample Excel file looks like this

So, if utilize the above lawmaking in HTML/Javascript, output will exist equally below

javascript-read-xlsx-example.gif

Here is the fiddle link https://jsfiddle.net/abj98oxf/3/

Read XLS file using Javascript

In Similar manner, we can read .xls (excel) file also and bear witness it in HTML table, I will repeat the same code, only few lines of code is dissimilar, also we volition be using diferent plugin in it which is for .xls

          <input type="file" id="fileUpload" /> <input type="button" 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 type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.xiii.v/jszip.js"></script> <script blazon="text/javascript">     part UploadProcess() {         //Reference the FileUpload element.         var fileUpload = document.getElementById("fileUpload");           //Validate whether File is valid Excel file.         var regex = /^([a-zA-Z0-nine\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(e.target.issue);                     };                     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++) {                             data += 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(information, {blazon: 'binary'});         var workbook = XLS.parse_xlscfb(cfb);           //get the name of First Sheet.         var Sheet = workbook.SheetNames[0];           //Read all rows from First 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 = "one";           //Add the header row.         var row = myTable.insertRow(-1);           //Add the header cells.         var headerCell = certificate.createElement("Thursday");         headerCell.innerHTML = "Id";         row.appendChild(headerCell);           headerCell = document.createElement("TH");         headerCell.innerHTML = "Name";         row.appendChild(headerCell);           headerCell = document.createElement("Thursday");         headerCell.innerHTML = "Country";         row.appendChild(headerCell);                  headerCell = certificate.createElement("TH");         headerCell.innerHTML = "Historic period";         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 data row.             var row = myTable.insertRow(-one);               //Add the data cells.             var prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Id;               prison cell = row.insertCell(-1);             cell.innerHTML = excelRows[i].Proper name;               cell = row.insertCell(-1);             prison cell.innerHTML = excelRows[i].Country;                          jail cell = row.insertCell(-ane);             cell.innerHTML = excelRows[i].Age;                          cell = row.insertCell(-i);             cell.innerHTML = excelRows[i].Date;                          cell = row.insertCell(-ane);             cell.innerHTML = excelRows[i].Gender;         }                    var ExcelTable = certificate.getElementById("ExcelTable");         ExcelTable.innerHTML = "";         ExcelTable.appendChild(myTable);     }; </script>

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

  1. We included deviation JS plugin file : <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js"></script>
  2. Inverse few lines of code for function "GetTableFromExcel"
                                  //Read the Excel File data in binary          var cfb = XLS.CFB.read(data, {type: 'binary'});         var workbook = XLS.parse_xlscfb(cfb);           //go the name of Showtime Sheet.         var Canvas = 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]);?

Balance of the code remains same.

Here is the working dabble link : https://jsfiddle.net/y3tx8wk4/one/

Note: You need to pass .xls file in this code to make it work.