3

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.

Robin Mackenzie
  • 14,370
  • 7
  • 36
  • 44
Agricolo
  • 53
  • 8

1 Answers1

1

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.

Agricolo
  • 53
  • 8