Read Excel file using Javascript (XLSX or XLS)

: 23555

Last Updated : 21/05/2024

Posted By :- vikas_jk

In the previous post, I have explained how we can read csv file using javascript and HTML 5 filereader, now in this post, 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, 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 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 it from our Excel file.

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

<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.13.5/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 = function (e) {
                        GetTableFromExcel(e.target.result);
                    };
                    reader.readAsBinaryString(fileUpload.files[0]);
                } else {
                    //For IE Browser.
                    reader.onload = function (e) {
                        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 {
                alert("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 workbook = XLSX.read(data, {
            type: 'binary'
        });
 
        //get the name of First Sheet.
        var Sheet = workbook.SheetNames[0];
 
        //Read all rows from First Sheet into an JSON array.
        var excelRows = XLSX.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(-1);
 
        //Add 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("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 data row.
            var row = myTable.insertRow(-1);
 
            //Add the data cells.
            var cell = row.insertCell(-1);
            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(-1);
            cell.innerHTML = excelRows[i].Age;
            
            cell = row.insertCell(-1);
            cell.innerHTML = excelRows[i].Date;
            
            cell = row.insertCell(-1);
            cell.innerHTML = excelRows[i].Gender;
        }
        
 
        var ExcelTable = document.getElementById("ExcelTable");
        ExcelTable.innerHTML = "";
        ExcelTable.appendChild(myTable);
    };
</script>

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

UploadProcess: Uploads the file on button click and convert it into Binary data, it also check if Browser is IE then process accordingly to convert file into binary.
ProcessExcel: this function takes the binary data, reads the Sheet name, 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

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

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

Read XLS file using Javascript

In a 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 code is different, also we will be using different 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.13.5/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 = function (e) {
                        GetTableFromExcel(e.target.result);
                    };
                    reader.readAsBinaryString(fileUpload.files[0]);
                } else {
                    //For IE Browser.
                    reader.onload = function (e) {
                        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 {
                alert("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 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 = "1";
 
        //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("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 data row.
            var row = myTable.insertRow(-1);
 
            //Add the data cells.
            var cell = row.insertCell(-1);
            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(-1);
            cell.innerHTML = excelRows[i].Age;
            
            cell = row.insertCell(-1);
            cell.innerHTML = excelRows[i].Date;
            
            cell = row.insertCell(-1);
            cell.innerHTML = excelRows[i].Gender;
        }
        
 
        var ExcelTable = document.getElementById("ExcelTable");
        ExcelTable.innerHTML = "";
        ExcelTable.appendChild(myTable);
    };
</script>

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

We included difference JS plugin file : <script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js"></script>

Changed a few lines of code for the function "GetTableFromExcel"

       //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 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]);?

The rest of the code remains the same.

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

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

You can also use external library like https://catamphetamine.gitlab.io/read-excel-file/

Convert Excel to JSON using Javascript

You can also upload excel and then convert it to JSON data and proceed with it further.

<form enctype="multipart/form-data">
  <input id="upload" type=file name="files[]">
</form>

<textarea class="form-control" rows=35 cols=120 id="xlx_json"></textarea>



<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
  document.getElementById('upload').addEventListener('change', handleFileSelect, false);
  var ExcelToJSON = function() {

    this.parseExcel = function(file) {
      var reader = new FileReader();

      reader.onload = function(e) {
        var data = e.target.result;
        var workbook = XLSX.read(data, {
          type: 'binary'
        });
        workbook.SheetNames.forEach(function(sheetName) {
          // Here is your object
          var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
          var json_object = JSON.stringify(XL_row_object);
          console.log(JSON.parse(json_object));
          jQuery('#xlx_json').val(json_object);
        })
      };

      reader.onerror = function(ex) {
        console.log(ex);
      };

      reader.readAsBinaryString(file);
    };
  };

  function handleFileSelect(evt) {

    var files = evt.target.files; // FileList object
    var xl2json = new ExcelToJSON();
    xl2json.parseExcel(files[0]);
  }
</script>

Here is the fiddle: https://jsfiddle.net/aszchkr0/ using which you can test the above code, just to search excel file and once value is changed you will find JSON result in textarea.

In the above code, we are reading excel using XLSX plugin, then we loop through each sheet and get XL row object and further convert it to JSON.

You may also like to read:
Read PDF file using Javascript

Convert Image to base64 string using Javascript

Convert seconds to hh:mm:ss using Javascript

Uncaught Error: "Cannot use import statement outside a module"
Encrypt and Decrypt string in Javascript
Email validation using jQuery (Various Methods)

Toggle Password input field using Javascript (Show/hide password)

Buy us a coffee

Become a Patron Q Follow our Quora space

Comment

Comment's

Sudhanshu21

hi,

I used the same coding, but it is not working. It just displays Header information and marks each cell value as null. Please assist

7/28/2020 12:03:50 PM

Add Comment

vikas_jk

Hi, can you share your code here? or in Fiddle ? It would be helpful to check. ( The slack image which you have attached requires login, and cannot be directly fetched.)

Try this fiddle link: https://jsfiddle.net/y3tx8wk4/1/ which shows demo output.

7/29/2020 12:36:08 PM

Sudhanshu21

The code is given below. And file is simple 4 column 4 rows.

<html>
<head>
<meta charset="ISO-8859-1">
<title>Employee Details</title>

</head>
<body>

Upload File to View<br/>

function Upload() {
//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 = function (e) {
ProcessExcel(e.target.result);
};
reader.readAsBinaryString(fileUpload.files[0]);
} else {
//For IE Browser.
reader.onload = function (e) {
var data = "";
var bytes = new Uint8Array(e.target.result);
for (var i = 0; i < bytes.byteLength; i++) {
data += String.fromCharCode(bytes[i]);
}
ProcessExcel(data);
};
reader.readAsArrayBuffer(fileUpload.files[0]);
}
} else {
alert("This browser does not support HTML5.");
}
} else {
alert("Please upload a valid Excel file.");
}
};

function ProcessExcel(data) {
//Read the Excel File data.
var workbook = XLSX.read(data, {
type: 'binary'
});

//Navigating sheets
//for (var s= 0; s<=3; s++) {
//Fetch the name of First Sheet.
var firstSheet = workbook.SheetNames[0];

//Read all rows from First Sheet into an JSON array.
var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);

//Create a HTML Table element.
var table = document.createElement("table");
table.border = "1";

//Add the header row.
var row = table.insertRow(-1);

//Update 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("TH");
headerCell.innerHTML = "Post";
row.appendChild(headerCell);

headerCell = document.createElement("TH");
headerCell.innerHTML = "Experience";
row.appendChild(headerCell);

//var len = excelRows.length
//Add the data rows from Excel file.
for (var i = 0; i <5 ; i++) {
//Add the data row.
var row = table.insertRow(-1);

var cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Id;

cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Name;

cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Post;

cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Experience;

}
//} --End Navigating sheets

var dvExcel = document.getElementById("dvExcel");
dvExcel.innerHTML = "";
dvExcel.appendChild(table);
};

</script>
</body>
</html>

7/29/2020 2:47:20 PM

Add Comment

vikas_jk

I was able to upload file similar to your's here is fiddle sample link ( Note: you need to upload .xlsx file in this example)

https://jsfiddle.net/sL8gfoph/

Demo output

7/30/2020 3:17:49 PM
Edited at :- 7/30/2020 3:18:24 PM

Sudhanshu21

Thank you for your help. It seems to be an issue from my end.

7/31/2020 11:45:23 AM

Add Comment

manjoo

It is very useful script and I was searching it from long time.

I want to load file:///D:/Addresses/address.xlsx automatically when I open html file in browser, don't want to select/browse file and then read/show the Excel output. Kinldy help me for this.

Furthermore, input text-box with ability to search excel data displayed table can be added to this page.

10/20/2020 9:19:30 AM
Edited at :- 10/20/2020 9:44:47 AM

Add Comment

vikas_jk

Replied to your question here https://qawithexperts.com/questions/476/read-excel-file-on-page-load-from-url-and-show-contents

You can take a look at reference also, thanks

10/21/2020 11:33:12 AM

GratisKesehatan

Thank you very much this is so helpful. Can we bunlde the XLSX and XLS function in a same button?

1/28/2021 7:21:00 AM

Add Comment

vikas_jk

Yes, You can do this, in the below explained way.

Create one function, which will be called when file is uploaded (xlsx or xls)
Check if file extension ends with .xlsx or .xls using Javascript
If it is .xlsx call related function, else call .xls function

Thanks

1/28/2021 11:36:51 AM
Edited at :- 1/28/2021 11:37:09 AM

GratisKesehatan

Thanks a lot !

1/29/2021 4:58:55 AM

YokeswaranGandhimohan

I am using XlSX.Read Javascript API to upload excel file, while trying to upload the excel file i need to un protect or remove the password, or any other way to un protect or input the password in Javascript, i have attached the code below for your reference, please check.

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
    var ExcelToJSON = function() {

    this.parseExcel = function(file) {
    var reader = new FileReader();

    reader.onload = function(e) {
    var data = e.target.result;
    var workbook = XLSX.read(data, {
       type: 'binary',
       password: NULL
  });

    workbook.SheetNames.forEach(function(sheetName) {
    // Here is your object
    var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
    var json_object = JSON.stringify(XL_row_object);
    console.log(json_object);

   })

  };

   reader.onerror = function(ex) {
    console.log(ex);
  };

   reader.readAsBinaryString(file);
   };
 };

4/30/2021 9:18:12 AM

Add Comment

YokeswaranGandhimohan

Hi All,

Can anyone please help.

4/30/2021 10:06:52 AM

vikas_jk

Hello, Sorry for late reply, but I googled your issue and wasn't able to find anything helpful.

I see it is not directly possible to read password protected excel using Javascript, but from some questions on other website, you can give try to this library https://www.npmjs.com/package/circular-json

OR try using this NodeJS package https://www.npmjs.com/package/xlsx-populate#encryption

XlsxPopulate.fromFileAsync("./Book1.xlsx", { password: "S3cret!" })
    .then(workbook => {
       //
    });

Hope it might help

5/5/2021 12:05:39 PM

Login or Register to comment

8407

Export html table to excel using jQuery / Javascript

10056

How to get user location using Javascript / HTML5 Geolocation?

4054

Implementing Javascript Drag and Drop using HTML5

5382

Making all fields of Form as Read only (Disabled) using jQuery on page load.

10375

Convert Text to HTML using Javascript

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly

Read Excel file using Javascript (XLSX or XLS)

Read XLSX using Javascript

Read XLS file using Javascript

Convert Excel to JSON using Javascript

Comment's

Sudhanshu21

vikas_jk

Sudhanshu21

vikas_jk

Sudhanshu21

manjoo

vikas_jk

GratisKesehatan

vikas_jk

GratisKesehatan

YokeswaranGandhimohan

YokeswaranGandhimohan

vikas_jk

Related Articles

Subscribe Now

Related Questions