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.
Just in case anyone will face the same issue I had: the reason Excel was not calculating the results of formulas was that I had to Enable Editing. In fact these were automatically disabled due to the file being downloaded from a web page. Simply enabling editing solved my issue - also when re-downloading the file, since it kept the same name.
However, since I spent some time on this and someone could find it useful, if like me you're using SheetJs (https://unpkg.com/xlsx@0.16.9/dist/xlsx.full.min.js) to manipulate the excels with js, default calculation settings are written in the var op.