Parse and Read Excel Files (xls/xlsx) With JavaScript

Recently there was a requirement in one of my projects to parse Excel files (with xls/xlsx extensions) in the browser (using javascript ofcourse). So I looked around a bit on the internet and found these two useful libraries:

js-xls
js-xlsx

Lets see how to work with each of them.

What's the one thing every developer wants? More screens! Enhance your coding experience with an external monitor to increase screen real estate.

JS-XLS

Installing it is very easy. For usage in the browser grab this script file and load it using the script tags:

<!-- https://github.com/SheetJS/js-xls/blob/master/xls.js -->
<script src="/path/to/xls.js"></script>

For Node.js, just install via NPM:

$ npm install xlsjs
$ node
> require('xlsjs').readFile('excel_file.xls');

Then we can simply use the code that they provide in their index.html file for parsing and converting XLS files to JSON, CSV or a Formulae based output.

function get_radio_value( radioName ) {
  var radios = document.getElementsByName( radioName );
  for( var i = 0; i < radios.length; i++ ) {
    if( radios[i].checked ) {
      return radios[i].value;
    }
  }
}

function to_json(workbook) {
  var result = {};
  workbook.SheetNames.forEach(function(sheetName) {
    var roa = XLS.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
    if(roa.length > 0){
      result[sheetName] = roa;
    }
  });
  return result;
}

function to_csv(workbook) {
  var result = [];
  workbook.SheetNames.forEach(function(sheetName) {
    var csv = XLS.utils.make_csv(workbook.Sheets[sheetName]);
    if(csv.length > 0){
      result.push("SHEET: " + sheetName);
      result.push("");
      result.push(csv);
    }
  });
  return result.join("\n");
}

function to_formulae(workbook) {
  var result = [];
  workbook.SheetNames.forEach(function(sheetName) {
    var formulae = XLS.utils.get_formulae(workbook.Sheets[sheetName]);
    if(formulae.length > 0){
      result.push("SHEET: " + sheetName);
      result.push("");
      result.push(formulae.join("\n"));
    }
  });
  return result.join("\n");
}

var tarea = document.getElementById('b64data');
function b64it() {
  var cfb = XLS.CFB.read(tarea.value, {type: 'base64'});
  var wb = XLS.parse_xlscfb(cfb);
  process_wb(wb);
}

function process_wb(wb) {
  var output = "";
  switch(get_radio_value("format")) {
    case "json":
      output = JSON.stringify(to_json(wb), 2, 2);
      break;
    case "form":
      output = to_formulae(wb);
      break; 
    default:
      output = to_csv(wb);
  }
  if(out.innerText === undefined) out.textContent = output;
  else out.innerText = output;
}

var drop = document.getElementById('drop');
function handleDrop(e) {
  e.stopPropagation();
  e.preventDefault();
  var files = e.dataTransfer.files;
  var i,f;
  for (i = 0, f = files[i]; i != files.length; ++i) {
    var reader = new FileReader();
    var name = f.name;
    reader.onload = function(e) {
      var data = e.target.result;
      var cfb = XLS.CFB.read(data, {type: 'binary'});
      //var arr = String.fromCharCode.apply(null, new Uint8Array(data));
      //var cfb = XLS.CFB.read(btoa(arr), {type: 'base64'});
      var wb = XLS.parse_xlscfb(cfb);
      process_wb(wb);
    };
    reader.readAsBinaryString(f);
    //reader.readAsArrayBuffer(f);
  }
}

function handleDragover(e) {
  e.stopPropagation();
  e.preventDefault();
  e.dataTransfer.dropEffect = 'copy';
}

if(drop.addEventListener) {
  drop.addEventListener('dragenter', handleDragover, false);
  drop.addEventListener('dragover', handleDragover, false);
  drop.addEventListener('drop', handleDrop, false);
}

The JS code is quite easy to understand, it uses the native HTML5 drag and drop API to allow us to upload files and then read them as binary strings. The binary is then directly passed to the library and further parsed using this code:

var cfb = XLS.CFB.read(data, {type: 'binary'});
var wb = XLS.parse_xlscfb(cfb);

Eventually the wb (Workbook) object is processed and converted to the required format (JSON, CSV or Excel Formulas). This same code without the HTML5 Drag and Drop and File Reader API pieces can be used in Node.js.

Note: This module does not support XLSX. To overcome this limitation we can use another package by the same author called js-xlsx.

JS-XLSX

Again, installing and using is almost same as the previous section. For use in browser, load the jszip.js and xlsx files:

<!-- https://github.com/SheetJS/js-xlsx/blob/master/jszip.js -->
<script src="/path/to/jszip.js"></script>
<!-- https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js -->
<script src="/path/to/xlsx.js"></script>

Node.js installation and usage is like this:

$ npm install xlsx
$ node
> require('xlsx').readFile('excel_file.xlsx');

We can again use the code provided in the project’s index.html and convert the XLSX to JSON, CSV or Formulae based output.

function get_radio_value( radioName ) {
	var radios = document.getElementsByName( radioName );
	for( var i = 0; i < radios.length; i++ ) {
		if( radios[i].checked ) {
			return radios[i].value;
		}
	}
}

function to_json(workbook) {
	var result = {};
	workbook.SheetNames.forEach(function(sheetName) {
		var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
		if(roa.length > 0){
			result[sheetName] = roa;
		}
	});
	return result;
}

function to_csv(workbook) {
	var result = [];
	workbook.SheetNames.forEach(function(sheetName) {
		var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
		if(csv.length > 0){
			result.push("SHEET: " + sheetName);
			result.push("");
			result.push(csv);
		}
	});
	return result.join("\n");
}

function to_formulae(workbook) {
	var result = [];
	workbook.SheetNames.forEach(function(sheetName) {
		var formulae = XLSX.utils.get_formulae(workbook.Sheets[sheetName]);
		if(formulae.length > 0){
			result.push("SHEET: " + sheetName);
			result.push("");
			result.push(formulae.join("\n"));
		}
	});
	return result.join("\n");
}

var tarea = document.getElementById('b64data');
function b64it() {
	var wb = XLSX.read(tarea.value, {type: 'base64'});
	process_wb(wb);
}

function process_wb(wb) {
	var output = "";
	switch(get_radio_value("format")) {
		case "json":
		output = JSON.stringify(to_json(wb), 2, 2);
			break;
		case "form":
			output = to_formulae(wb);
			break; 
		default:
		output = to_csv(wb);
	}
	if(out.innerText === undefined) out.textContent = output;
	else out.innerText = output;
}

var drop = document.getElementById('drop');
function handleDrop(e) {
	e.stopPropagation();
	e.preventDefault();
	var files = e.dataTransfer.files;
	var i,f;
	for (i = 0, f = files[i]; i != files.length; ++i) {
		var reader = new FileReader();
		var name = f.name;
		reader.onload = function(e) {
			var data = e.target.result;
			//var wb = XLSX.read(data, {type: 'binary'});
			var arr = String.fromCharCode.apply(null, new Uint8Array(data));
			var wb = XLSX.read(btoa(arr), {type: 'base64'});
			process_wb(wb);
		};
		//reader.readAsBinaryString(f);
		reader.readAsArrayBuffer(f);
	}
}

function handleDragover(e) {
	e.stopPropagation();
	e.preventDefault();
	e.dataTransfer.dropEffect = 'copy';
}

if(drop.addEventListener) {
	drop.addEventListener('dragenter', handleDragover, false);
	drop.addEventListener('dragover', handleDragover, false);
	drop.addEventListener('drop', handleDrop, false);
}

Props to the author for writing these projects that makes our task so much more easier. That’s all! If you’ve any questions, feel free to ask them in the comments.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Author: Rishabh

Rishabh is a full stack web and mobile developer from India. Follow me on Twitter.

33 thoughts on “Parse and Read Excel Files (xls/xlsx) With JavaScript”

      1. Hi was trying to parse an excel file in order to convert it into .dat file and vice-a-versa. can you give me some suggestion as to how can I go ahead with this task. Thank you so much.

  1. In continuation to my above reply…
    In the current implementation, parser considers sheet as a single table while converting it to JSON, my requirement is bit different, I have multiple tables in one sheet , some columns are arranged vertically and some in horizontal.
    Is there any way to parse those multiple tables into one JSON
    Could you please help me to parse the same.
    Hope I conveyed the requirement correctly.
    Regards
    Manu

        1. SheetJS here.

          Rishabh, glad to hear this was useful to you 🙂 I’ve attempted to simplify the interface a bit (as reflected in the source of http://oss.sheetjs.com)

          Manu, you can design your own tools to parse the data. The actual sheet object (wb.Sheets[‘SheetName’]) has keys corresponding to cell addresses, so you can write your own function to walk through the cells and detect sub-tables. I looked into this a little bit, but without certain metadata it’s difficult to distinguish between a table where the rows are records and a table where the columns are records (think of a financial report, where the columns are the years and the rows are the data headers).

          In fact, the function that generates the objects is defined in the source, so you could start from there.

  2. Some one can help me load data from input type=”file”, I found only examples with drag&drop functionality. Thanks!

  3. Hello,

    I really like this plugin. However I want to use it for a commercial website. And I didn’t really understand the terms specified in the License Is there any way someone can tell me where I can use it and if I have to buy this plugin to use?

  4. I’m getting Uncaught ReferenceError: XLSX is not defined

    when I try to parse the excel to CSV.

    Any ideia why?

  5. Thanks for your code..
    How to implement the browse button using above code..
    Need to Implement browse button in-place of drag and drop

    Thanks,
    Vinay Chada

  6. I have a small requirement that to load the xlsx file from my server location in doucument.ready function.
    I have a URL with “http:/../../filename.xlsx” . Is it possible to read that file in ready function with out select input file option.

  7. For all those wondering how to fetch an excel (xls, xlsx) file directly from your own server and then parse that, the author has put up an example in the documentation file here.

    For those who want to use input type="file" instead of HTML5 Drag and Drop, consider using the FileReader API to read the file as soon as the browser button is clicked and some file is selected (or add the code for reading when a button is clicked after selection) and pass on the read data to this library (see the same example linked above in this comment, that should help).

    I’ll try to put together all the pieces and sort of revamp this post soon.

  8. when the program tries to read an empty cell
    I get an error “TypeError: cannot read property ‘v’ of undefined
    and the program crashes.
    is there a workaround for this?

  9. Hi Rishabh,Nice work !!
    Appreciate you effort here…
    Using your library, i was able to read XLS and XLXS file in google browser.
    But when i running same code in IE 9, it breaking and giving a below error inside handleDrop(e) event.

    Error:- e.target.files undefined Undefined

    Please suggest.

    Thanks
    Rahul

  10. Hi Rishabh,

    I am running script from Protractor :Taking data reference from Json file and I have created framework in xls and passing JSON key reference it in . While running the script it is taking reference as a string
    For example:
    Json file:
    {‘username’:’value’}

    passing my refer in excel as – usersname.
    SO instead of taking object reference of json, it taking reference as string (it is entering -username in text box)

    It is working fine If I directly pass username in the script.

    Please help me in resolving this issue

    Regards,
    Dilip

  11. Hi, I am new to web development. I have a task to import XLSX file to table I am converting XLSX to JSON this is working fine when I test it as a standalone. but when I give a href to this page from the main web it don’t convert . I set the libraries but still no luck can you help. Thanks

  12. Could you please help me read file as local?
    I want to read data from excel as local directory like D:\OneDrive\…\db.xlsx
    I have tried code as below

    function read_file(){
    if(typeof require !== ‘undefined’) XLSX = require(‘xlsx’);
    var workbook = XLSX.readFile(‘db.xlsx’);
    /* DO SOMETHING WITH workbook HERE */
    var first_sheet_name = workbook.SheetNames[0];
    var address_of_cell = ‘A1’;
    /* Get worksheet */
    var worksheet = workbook.Sheets[first_sheet_name];
    /* Find desired cell */
    var desired_cell = worksheet[address_of_cell];
    /* Get the value */
    var desired_value = desired_cell.v;
    alert(desired_value);
    }

    Unfortunately, it does not work. Please support me this issue
    Thank yo very much

    1. example: XLSX.readFile(‘file.xlsx’,{“password”:”123″});
      But as i know, library doesn’t supports all types of encryption.

  13. Hi

    We have a requirement that read xlsm file and insert value in the Salesforce VF page.But we are using Vbscript and it is not supported in IE 11.
    Can you please help with any other solution where I have to insert particular column with respect to each questions?

  14. Hi ,

    I’m having a task that parsing an xls or xlsx file and convert into json.
    I have achieved this but when the excel file having empty column , The process is not working.
    How can we achieve that json returns empty string instead of exiting the program.
    Could you please suggest a solution, I’m completely blocked here. 🙁

  15. This add-on is really useful, yet the License section making me nervous. Especially the fact that I dont have a lawyer. If he wants to make it commercial, he should just sell it. Rather than just let it float like that.
    So please, can anyone tell me in a much simpler words, can I use this add-on in my future project or not ?!

  16. I am new to this and I was wondering if you could help me out. I have to read text from an input box and match that to column B of an excel file, and corresponding to that value, I have to get first 10 values in column C. I have been stuck on this for a week now :/

Leave a Reply

Your email address will not be published. Required fields are marked *

*