exceljs · guyonroche · Apr 25, 2017 · Mar 31, 2017 · Mar 31, 2017 · Apr 4, 2017
diff --git a/lib/doc/cell.js b/lib/doc/cell.js
@@ -836,6 +836,56 @@ JSONValue.prototype = {
   }
 };
 
+var SharedFormulaValue = function(cell, value) {
+  this.model = {
+    address: cell.address,
+    type: Cell.Types.SharedFormula,
+    sharedFormula: value ? value.sharedFormula : undefined,
+    result: value ? value.result : undefined
+  };
+};
+SharedFormulaValue.prototype = {
+  get value() {
+    return {
+      sharedFormula: this.model.sharedFormula,
+      result: this.model.result
+    };
+  },
+  set value(value) {
+    this.model.sharedFormula = value.sharedFormula;
+    this.model.result = value.result;
+  },
+  get sharedFormula() {
+    return this.model.sharedFormula;
+  },
+  set sharedFormula(value) {
+    this.model.sharedFormula = value;
+  },
+  get result() {
+    return this.model.result;
+  },
+  set result(value) {
+    this.model.result = value;
+  },
+  get type() {
+    return Cell.Types.SharedFormula;
+  },
+  get address() {
+    return this.model.address;
+  },
+  set address(value) {
+    this.model.address = value;
+  },
+  toCsvString: function() {
+    return '' + (this.model.result || '');
+  },
+  release: function() {
+  },
+  toString: function() {
+    return this.model.result ? this.model.result.toString() : '';
+  }
+};
+
 // Value is a place to hold common static Value type functions
 var Value = {
   getType:  function(value) {
@@ -880,6 +930,7 @@ var Value = {
     {t:Cell.Types.RichText, f:RichTextValue},
     {t:Cell.Types.Boolean, f:BooleanValue},
     {t:Cell.Types.Error, f:ErrorValue},
+    {t:Cell.Types.SharedFormula, f:SharedFormulaValue},
   ].reduce(function(p,t){p[t.t]=t.f; return p;}, []),
 
   create: function(type, cell, value) {

diff --git a/lib/doc/enums.js b/lib/doc/enums.js
@@ -35,6 +35,7 @@ module.exports = {
     RichText: 8,
     Boolean: 9,
     Error: 10,
+    SharedFormula: 11
   },
   RelationshipType: {
     None: 0,

diff --git a/lib/xlsx/xform/sheet/cell-xform.js b/lib/xlsx/xform/sheet/cell-xform.js
@@ -44,6 +44,8 @@ function getValueType(v) {
     return Enums.ValueType.Hyperlink;
   } else if (v.formula) {
     return Enums.ValueType.Formula;
+  } else if (v.SharedFormula) {
+    return Enums.ValueType.SharedFormula;
   } else if (v.error) {
     return Enums.ValueType.Error;
   } else {
@@ -211,6 +213,15 @@ utils.inherits(CellXform, BaseXform, {
 
       case 'f':
         this.currentNode = 'f';
+        if (node.attributes.t && node.attributes.t == 'shared') {
+          if (node.attributes.ref) {
+            this.model.definesSi = node.attributes.si;
+            this.sis = this.sis || {};
+            this.sis[this.model.definesSi] = this.model;
+          } else {
+            this.model.usesSi = node.attributes.si;
+          }
+        }
         return true;
 
       case 'v':
@@ -235,6 +246,23 @@ utils.inherits(CellXform, BaseXform, {
     switch(name) {
       case 'c':
         var model = this.model;
+
+        if (model.definesSi) {
+          this.sis = this.sis || {};
+          this.sis[model.definesSi] = model;
+        }
+        if (model.usesSi) {
+          model.type = Enums.ValueType.SharedFormula;
+          var definition = (this.sis || {})[model.usesSi];
+          if (definition) {
+            model.sharedFormula = definition.address;
+            model.result = isNaN(definition.result) ? model.value : parseFloat(model.value);
+            delete model.value;
+          } else {
+            throw new Error('The cell with address ' + model.address + ' uses not yet defined shared formula ' + model.usesSi);
+          }
+          return;
+        }
         // first guess on cell type
         if (model.formula) {
           model.type = Enums.ValueType.Formula;

diff --git a/spec/integration/data/fibonacci.xlsx b/spec/integration/data/fibonacci.xlsx
diff --git a/spec/integration/issues.spec.js b/spec/integration/issues.spec.js
@@ -6,6 +6,7 @@ chai.use(require('chai-datetime'));
 
 var Excel = require('../../excel');
 var PromishLib = require('../../lib/utils/promish');
+var Enums = require('../../lib/doc/enums');
 
 // this file to contain integration tests created from github issues
 var TEST_XLSX_FILE_NAME = './spec/out/wb.test.xlsx';
@@ -192,4 +193,47 @@ describe('github issues', function() {
         });
     });
   });
+
+
+  describe('issue xyz - cells copied as a block treat formulas as values', function() {
+    var explain = 'this fails, although the cells look the same in excel. Both cells are created by copying A3:B3 to A4:F19. The first row in the new block work as espected, the rest only has values (when seen through exceljs)';
+    it('copied cells should have the right formulas', function () {
+      var wb = new Excel.Workbook();
+      return wb.xlsx.readFile('./spec/integration/data/fibonacci.xlsx')
+        .then(function () {
+          var ws = wb.getWorksheet('fib');
+          expect(ws.getCell('A4').value).to.deep.equal({ formula: 'A3+1', result: 4 });
+          expect(ws.getCell('A5').value).to.deep.equal({ sharedFormula: 'A4', result: 5 }, explain);
+        });
+    });
+    it('copied cells should have the right types', function () {
+      var wb = new Excel.Workbook();
+      return wb.xlsx.readFile('./spec/integration/data/fibonacci.xlsx')
+        .then(function () {
+          var ws = wb.getWorksheet('fib');
+          expect(ws.getCell('A4').type).to.equal(Enums.ValueType.Formula);
+          expect(ws.getCell('A5').type).to.equal(Enums.ValueType.SharedFormula);
+        });
+    });
+    it('copied cells should have the right _value', function () {
+      var wb = new Excel.Workbook();
+      return wb.xlsx.readFile('./spec/integration/data/fibonacci.xlsx')
+        .then(function () {
+          var ws = wb.getWorksheet('fib');
+          expect(JSON.stringify(ws.getCell('A4')._value)).to.deep.equal(JSON.stringify({"model":{"address":"A4","definesSi":"0","formula":"A3+1","type":6,"result":4,"value":undefined}}));
+          expect(JSON.stringify(ws.getCell('A5')._value)).to.deep.equal(JSON.stringify({"model":{"address":"A5","usesSi":"0","type":Enums.ValueType.SharedFormula,"sharedFormula":"A4","result":5}}), explain);
+        });
+    });
+    it('copied cells should have the same fields', function () { // to see if there are other fields on the object worth comparing
+      var wb = new Excel.Workbook();
+      return wb.xlsx.readFile('./spec/integration/data/fibonacci.xlsx')
+        .then(function () {
+          var ws = wb.getWorksheet('fib');
+          var A4 = ws.getCell('A4');
+          var A5 = ws.getCell('A5');
+          expect(Object.keys(A4).join()).to.equal(Object.keys(A5).join());
+        });
+    });
+  });
+
 });