I wrote a JS code where I imported SheetJS and js-xlsx to perform actions on XLSX files (I can't use nodeJs nor npm, bower or any other solution that requires any installation on final user computer).
Shortly, the code has to do the following:
The problem with the code I wrote is that it works just fine with Excels written, in fact, via Excel, but crashes if a user imports an XLSX that was previously generated and downloaded by my code.
Here's a snippet of the code:
// user choose the source excel
$('#xlf').change(function(e) {
var reader = new FileReader();
reader.readAsArrayBuffer(e.target.files[0]);
reader.onload = function(e) {
var data = new Uint8Array(reader.result);
var wb = XLSX.read(data, {
type: 'array',
cellDates: true,
cellStyles: true,
sheetStubs: true,
raw: true
});
var fSN = wb.SheetNames[0];
var ws = wb.Sheets[fSN];
function findEmpty() {
// this function check all the values in a specific range of cells (C9:C25)
// In order to do so, I included them into an array where I stored the value of the cells;
// later I loop the array and add 1 to the counter *dataSize* anytime I found a value > 0
var dataRange = [ws["C9"].v, ws["C10"].v, ws["C11"].v, , ws["C12"].v, ws["C13"].v, ws["C14"].v, ws["C15"].v, ws["C16"].v, ws["C17"].v, ws["C18"].v, ws["C19"].v, ws["C20"].v, ws["C21"].v, ws["C22"].v, ws["C23"].v, ws["C24"].v, ws["C25"].v];
var dataSize = 0;
var row;
for (i = 0; i < dataRange.length; i++) {
if (dataRange[i] > 0)
dataSize++;
}
row = 8 + dataSize; // 8 is the row of C9 (0 index-based)
return row;
}
var firstEmpty = findEmpty();
var header = ["a", "b", "c", "d", "e", "f"];
//origin is firstEmpty
XLSX.utils.sheet_add_json(ws, [{
a: $('#from').val(),
b: $('#to').val(),
c: $("#differenza").val(),
e: $("#comm").val()
}], {
header: header,
origin: firstEmpty,
skipHeader: true
});
// save file
XLSX.writeFile(wb, "test.xlsx");
}
});
If I try storing in dataRange the cells without the value (.v):
var dataRange = [ws["C9"], ws["C10"], ws["C11"], ws["C12"], ws["C13"], ws["C14"], ws["C15"], ws["C16"], ws["C17"], ws["C18"], ws["C19"], ws["C20"], ws["C21"], ws["C22"], ws["C23"], ws["C24"], ws["C25"]];
it won't crash with neither Excel generated files nor this-code-generated files, however the function findEmpty() will not work as intended (dataSize will be 0, no matter what's inside the cells).
Looks like my code can't write proper cell objects. My guess is that there's something I should fix with the way I'm saving the file, but after several research and attempts I couldn't figure out a way to fix this - do you have a clue?
Thanks to all
Just in case this could help anyone, I solved my problem changing the IF condition within the FOR loop like this:
if((dataRange[i] !== undefined) && (dataRange[i].v > 0))
dataSize++;
Also, I'm using the dataRange array where I did not pass the .v property
(dataRange = [ws["C9"], ws["C10"], ... , ...]
)
After few attempts I found my code was not able to read within the .v of a cell of a file it generates, still it write and passes the whole cell object to it