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:
Lets see how to work with each of them.
JS-XLS
Installing it is very easy. For usage in the browser grab this script file and load it using the script tags:
[html]
<!– https://github.com/SheetJS/js-xls/blob/master/xls.js –>
<script src="/path/to/xls.js"></script>
[/html]
For Node.js, just install via NPM:
[bash]
$ npm install xlsjs
$ node
> require(‘xlsjs’).readFile(‘excel_file.xls’);
[/bash]
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.
[js]
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);
}
[/js]
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:
[js]
var cfb = XLS.CFB.read(data, {type: ‘binary’});
var wb = XLS.parse_xlscfb(cfb);
[/js]
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:
[html]
<!– 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>
[/html]
Node.js installation and usage is like this:
[bash]
$ npm install xlsx
$ node
> require(‘xlsx’).readFile(‘excel_file.xlsx’);
[/bash]
We can again use the code provided in the project’s index.html and convert the XLSX to JSON, CSV or Formulae based output.
[js]
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);
}
[/js]
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.
