I have a small code that imports an Excel workbook (xlsx) file, edits a cell value and finally saves the workbook in a different file.
The problem with my code is that: in the new xlsx, auto-calculation is not set. I have to manually open the file and pass through all the cells with formulas in order for it to be updated.
I came across this issue report in SheetJs's Github but found no solution: https://github.com/SheetJS/sheetjs/issues/306
My question is: how could I set calculation mode to automatic on the generated xlsx?
The following is a snippet of the code:
/* I use an input button (type="file", id="xlf") to get the file */
$('#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'});
var htmlstr = XLSX.write(wb,{type:'binary',bookType:'html'});
var fSN = wb.SheetNames[0];
var ws = wb.Sheets[fSN];
/* simple formula added for test purposes */
ws['D9'] = {t:'n', f:"E4+E5"};
if(!ws["E5"]) ws["E5"] = {};
ws["E5"].t = "n";
ws["E5"].v = $("#diff").val();
ws["E4"].t = "n";
/* save file */
XLSX.writeFile(wb, "test.xlsx");
}
});
This code makes my browser download a new excel where the value of the cell E5
is the one taken from the field #diff
. D9
stays empty until I manually go click on it and press enter (the formula is there).
Ideally I'm looking for a solution that does not involve node.js
or npm
since program will run on a computer where I can't install anything.