Search code examples
node.jsexcelnpmexcel-formulaexceljs

Node - exceljs: writing to file breaks fomulas in the file

I have an excel (xlsx) file that contains random columns. Some of these columns have formulas mapped to the sum of some cells; for example:

=J8+F9-H9

In my case I have the following three columns:

F: number
H: number
J: =sum of previous row's F and H cell's values.

I aim to get external data and store them cell by cell in this workbook. For this I am using Node module exceljs.

This is my code so far, I am harcoding values for now (which I will be getting from another file later on).

var workbook = new Excel.Workbook();
var filename = 'Bank Synoptic Journal.xlsx'
workbook
.xlsx
.readFile(filename)
.then(function() {
   var worksheet = workbook.getWorksheet('Bank Synoptic');

   var row = null;
   row = worksheet.getRow(8);
   row.getCell('J').value = Math.random();
   row.commit();

   for(var i=9; i<=305;i++) { //row 
      row = worksheet.getRow(i);
      row.getCell('F').value = Math.random();
      row.getCell('H').value = Math.random();
      row.commit();
  }
})
.then(function() {
   return workbook.xlsx.writeFile(filename + '_modified.xlsx');
})
.then(function() {
   console.log('Done!');
});

It prints the output into a new excel file. The problem I am facing is that for cells 'J' ie which contains the formulas; these cells are breaking with no consitency:

  • Some cells keep formulas and do the calculations
  • Others have no more formulas nor calculations done (have '0' instead of formula)
  • Recalculations are not done automatically using this injection mechanism

(Snapshots)

snapshot

with formula

no formula

What I am missing or doing wrong that is leading to this error?

Solution

  • After several trials and errors I moved to Apache POI and so built the script using Java.

    I downloaded and included the following JARs in my project:

    jars-used

    It manipulates rows/columns and keeps the formulas intact. Once you open the modified excel file all you have to do is refresh (On Windows: ctrl + alt + f9) and it will recalculate.