Reading Excel Sheet on client side and processing it
If you ever want to process excel sheets on the client side (on the browser) then this will be useful for you.
- This uses http://oss.sheetjs.com coed and the source files are in (https://github.com/SheetJS/js-xls, https://github.com/SheetJS/js-xlsx) it is licensed under Apache Licence, Version 2.0 (refer attached licence in the libraries in github)
JS Files required:
- Copy the code for following files and make corresponding files locally, or download them from github,
https://raw.github.com/SheetJS/js-xls/master/xls.js
http://stuk.github.io/jszip/
- We will be taking some javascript already in oss.sheetjs.com for our example particularly on the main page of oss.sheetjs.com.
Sample Excel Sheet:
- Our excel sheet will be as follows, will have 3 sheets named, "ClientDetails", "Prices", "ResourceTypes"
- The content of each sheet will be as follows,
"ClientDetails" - A table with the following values,
"Prices" - A table as follows, "ResourceTypes" -
- Note we are going to make use of xlsx as the file type and ignore xls, but doing xls would be easy as well (need to look at the processing using the xls.js library good reference in oss.sheetjs.com look at the code behind the page)
- So we have Template.xlsx which contains the above sheets and values,
Index.html and js file entries
- We have a directory with xls.js and xlsx.js and now we create an index.html with the following content
<!DOCTYPE html>
<html><head>
<style>
#drop{
border:2px dashed #bbb;
-moz-border-radius:5px;
-webkit-border-radius:5px;
border-radius:5px;
padding:25px;
text-align:center;
font:20pt bold,"Vollkorn";color:#bbb
}
</style>
</head><body><b>JS-XLSX Demo</b><br>
<a href="https://github.com/SheetJS/js-xlsx">Github Repo-xlsx</a><br>
<a href="https://github.com/SheetJS/js-xls">Github Repo-xls</a><br>
<input type="radio" name="format" value="csv" > CSV<br>
<input type="radio" name="format" value="json" checked=""> JSON<br>
<input type="radio" name="format" value="form"> FORMULAE<br>
<div id="drop">Drop an XLSX file here to see sheet data.</div>
<pre id="out"></pre>
<br>
<script src="http://codeorigin.jquery.com/jquery-2.0.3.js"></script> // Used for object manipulations
<script src="jszip.js"></script> // used by xlsx for file reading
<script src="xlsx.js"></script> // used for xlsx processing
<script src="processDataXlsx.js"></script> // load file manipulate validate, output results...
</body>
</html>
--------------------------------
The radio buttons allows to get the output as csv or json or in form we are only concerned about json
- Now you will be wondering whats in processDataXlsx.js where did it come from, It is where we will be writing the Excel processing stuff and handling of the file load etc...
processDataXlsx.js (code from oss.sheetjs.com) slight modifications for our use
The most important methods are handleDrop, to_json and process_wb where we have our validation and output results.
A brief description of each method,
// This method returns each sheet in result array in json format with the data associated with each sheet
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;
}
// This method returns each sheet in result array but in csv format
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");
}
// get hold of "drop" div to handle the excel sheet droped on to the drop location of the browser
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;
arr = String.fromCharCode.apply(null, new Uint8Array(data));
var wb = XLSX.read(btoa(arr), {type: 'base64'});
// provide the read results to the process method to process it according to what we want
process_wb(wb);
};
reader.readAsArrayBuffer(f);
}
}
--------------------------------------------------
// In this method we process the result of reading the excel sheets here we do some simple stuff, we get the result in json format and then
function process_wb(wb) {
var output = "";
var resultObj = "";
switch(get_radio_value("format")) {
case "json":
resultObj = to_json(wb);
customValidateSheets(resultObj);
output = JSON.stringify(resultObj, 2, 2);
break;
case "form":
output = to_formulae(wb);
break;
default:
output = to_csv(wb);
}
var clientDetails = JSON.stringify(resultObj.ClientDetails,2,2);
// assume we need price changed resources only
var resourcePrices = $.map(resultObj.Prices,
function(obj) { if(obj.Price_Change !== undefined) return obj; }
);
resourcePrices = JSON.stringify(resourcePrices,2,2);
// assume we need price changed resource types only
var resourceTypePrices = $.map(resultObj.ResourceTypes,
function(obj) { if(obj.Change_To !== undefined) return obj; }
);
resourceTypePrices = JSON.stringify(resourceTypePrices,2,2);
// display this information on the out tag
out.innerText= clientDetails + resourcePrices + resourceTypePrices;
}
--------------------------------------------------
// This function is to validate few information on the sheets
function customValidateSheets(resultObj){
// validate Client Information is not null
$(resultObj.ClientDetails).each(function(index, element){
if (element.Client_Name === undefined){
alert("Client Name Missing");
}
});
// validate if resources have missing identifiers
$(resultObj.Prices).each(function(index, element){
if(element.Identifier === undefined && element.Price != undefined ){
alert("Resources with Missing Identifiers exist");
}
});
// validate if resource types missing identifiers
$(resultObj.ResourceTypes).each(function(index, element){
if(element.RESOURCE_TYPE === undefined
&& element.Price != undefined ){
alert("Resource Types with Missing Identifiers exist");
}
});
}
----------------------------------------------------------------------------------------
// These two functions handle the drag/drop of the file onto the browser
function handleDragover(e) {
e.stopPropagation();
e.preventDefault();
e.dataTransfer.dropEffect = 'copy';
}
// This check adds an event listner for the "drop" div
if(drop.addEventListener) {
drop.addEventListener('dragenter', handleDragover, false);
drop.addEventListener('dragover', handleDragover, false);
drop.addEventListener('drop', handleDrop, false);
}
Sample Files in Google Drive:
Wonderful work man. Though an old post, it saved me lot of time to figure out how to use XLSX.JS lib. Could you please help me understand what does
ReplyDelete" var clientDetails = JSON.stringify(resultObj.ClientDetails,2,2);" do?
I only have one sheet with 5 cols and want to read them, one row at a time for some row and column level processing (one col is URL for Ajax call).