exceljs · muscapades · Mar 31, 2017 · Mar 31, 2017 · Apr 4, 2017 · Apr 7, 2017
diff --git a/lib/doc/cell.js b/lib/doc/cell.js
@@ -836,6 +836,65 @@ 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,
+    formula: value ? value.formula : undefined
+  };
+};
+SharedFormulaValue.prototype = {
+  get value() {
+    return {
+      sharedFormula: this.model.sharedFormula,
+      result: this.model.result,
+      formula: this.model.formula
+    };
+  },
+  set value(value) {
+    this.model.sharedFormula = value.sharedFormula;
+    this.model.result = value.result;
+    this.model.formula = value.formula;
+  },
+  get sharedFormula() {
+    return this.model.sharedFormula;
+  },
+  set sharedFormula(value) {
+    this.model.sharedFormula = value;
+  },
+  get formula() {
+    return this.model.formula;
+  },
+  set formula(value) {
+    this.model.formula = 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 +939,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/utils/shared-formula.js b/lib/utils/shared-formula.js
@@ -0,0 +1,65 @@
+/**
+ * Copyright (c) 2017 Morten Ulrik Sørensen
+ *
+ * Permission is hereby granted, free of charge, to any person obtaining a copy
+ * of this software and associated documentation files (the "Software"), to deal
+ * in the Software without restriction, including without limitation the rights
+ * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+ * copies of the Software, and to permit persons to whom the Software is
+ * furnished to do so, subject to the following conditions:
+ *
+ * The above copyright notice and this permission notice shall be included in
+ * all copies or substantial portions of the Software.
+ *
+ * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+ * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL THE
+ * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+ * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+ * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+ * THE SOFTWARE.
+ *
+ */
+'use strict';
+
+var colCache = require('./col-cache');
+
+var cellRefRegex = /(([a-z\_\-0-9]*)\!)?\$?([a-z]+)\$?([1-9][0-9]*)/i;
+var replacementCandidateRx = /(([a-z\_\-0-9]*)\!)?([a-z0-9_$]{2,})(\()?/ig;
+var CRrx = /^(\$)?([a-z]+)(\$)?([1-9][0-9]*)$/i;
+
+var slideFormula = function(formula, fromCell, toCell){
+  var offset = colCache.decode(fromCell);
+  var to = colCache.decode(toCell);
+  return formula.replace(replacementCandidateRx, function(refMatch, sheet, sheetMaybe, addrPart, trailingParen) {
+    if (trailingParen) {
+        return refMatch;
+    }
+    var match = CRrx.exec(addrPart)
+    if (match) {
+      var colDollar = match[1];
+      var colStr = match[2].toUpperCase();
+      var rowDollar = match[3];
+      var rowStr = match[4];
+      if (colStr.length > 3 || (colStr.length == 3 && colStr > "XFD")) { // > XFD is the highest col number in excel 2007 and beyond, so this is a named range
+        return refMatch;
+      }
+      var col = colCache.l2n(colStr);
+      var row = parseInt(rowStr);
+      if (!colDollar) {
+        col += to.col - offset.col;
+      }
+      if (!rowDollar) {
+        row += to.row - offset.row;
+      }
+      var res = (sheet ? sheet : '') + (colDollar||'') + colCache.n2l(col) + (rowDollar||'') + row;
+      return res;
+    } else {
+      return refMatch;
+    }
+  });
+}
+
+module.exports = {
+  slideFormula: slideFormula
+};
diff --git a/lib/xlsx/xform/sheet/cell-xform.js b/lib/xlsx/xform/sheet/cell-xform.js
@@ -27,6 +27,7 @@ var BaseXform = require('../base-xform');
 
 var Enums = require('../../../doc/enums');
 var RelType = require('../../rel-type');
+var SharedFormula = require('../../../utils/shared-formula');
 
 
 function getValueType(v) {
@@ -44,6 +45,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 +214,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 +247,24 @@ 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);
+            model.formula = SharedFormula.slideFormula(definition.formula, definition.address, model.address);
+            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,48 @@ 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, formula: 'A4+1' }, explain);
+          expect(ws.getCell('B5').value).to.deep.equal({ sharedFormula: 'B4', result: 5, formula: 'B3+B4' }, 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,"formula":"A4+1"}}), 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());
+        });
+    });
+  });
+
 });
diff --git a/spec/unit/utils/shared-formula.spec.js b/spec/unit/utils/shared-formula.spec.js
@@ -0,0 +1,53 @@
+'use strict';
+
+var expect = require('chai').expect;
+
+var SharedFormula = require('../../../lib/utils/shared-formula');
+
+describe('SharedFormula', function() {
+
+  it("replaces in formulas", () => {
+    assertFormulaReplace({formula: 'B2', from: 'A1', to: 'B4'}, 'C5');
+    assertFormulaReplace({formula: 'B2', from: 'A1', to: 'C20'}, 'D21');
+    assertFormulaReplace({formula: 'r22', from: 'A1', to: 'B4'}, 'S25');
+
+    assertFormulaReplace({formula: 'min(loan!S45 * AD47,1)', from: 'B4', to: 'A5'}, 'min(loan!R46 * AC48,1)');
+
+    assertFormulaReplace({formula: 'longer', from: 'B4', to: 'A5'}, 'longer');
+
+  });
+
+  var assertFormulaReplace = (test, expected, explain) => {
+    expect(SharedFormula.slideFormula(
+        test.formula, 
+        test.from, 
+        test.to
+      ))
+      .to.equal(expected, "when the formula '"+ test.formula + "' is copied from "+ test.from + " to " + test.to + ", the formula should become "+ expected + (explain || ''));
+  }
+
+  it("replaces in formulas but leaves the $ signed", () => {
+    assertFormulaReplace({formula: '$B$2', from: 'A1', to: 'B4'}, '$B$2');
+    assertFormulaReplace({formula: 'B$2', from: 'A1', to: 'C20'}, 'D$2');
+    assertFormulaReplace({formula: '$R22', from: 'A1', to: 'B4'}, '$R25');
+
+    assertFormulaReplace({formula: 'min(loan!$S45 * ZD$47,1)', from: 'B4', to: 'A5'}, 'min(loan!$S46 * ZC$47,1)');
+  });
+
+  it("replaces in formulas but leaves range names alone", () => {
+    assertFormulaReplace({formula: 'XDF67', from: 'B4', to: 'A5'}, 'XDE68');
+    assertFormulaReplace({formula: 'LONGER', from: 'B4', to: 'A5'}, 'LONGER');
+    assertFormulaReplace({formula: 'LOGO10', from: 'B4', to: 'A5'}, 'LOGO10');
+  });
+
+  it("replaces in formulas but leaves functions and named ranges alone", () => {
+    assertFormulaReplace({formula: 'a2b4', from: 'A1', to: 'B4'}, 'a2b4');
+    assertFormulaReplace({formula: '2b4', from: 'A1', to: 'B4'}, '2b4');
+    assertFormulaReplace({formula: 'a2b', from: 'A1', to: 'B4'}, 'a2b');
+    assertFormulaReplace({formula: 'a2(2)', from: 'A1', to: 'B4'}, 'a2(2)',' - It cannot be a cell reference when followed by open paren');
+    var longI25a = 'OR(INDEX(cbalSI1CodeR1,1)=C25,INDEX(cbalSI2CodeR1,1)=C25,INDEX(cbalSI3CodeR1,1)=C25,INDEX(cbalSI4CodeR1,1)=C25,INDEX(cbalSI5CodeR1,1)=C25,INDEX(cbalSI1CodeR2,1)=C25,INDEX(cbalSI2CodeR2,1)=C25,INDEX(cbalSI3CodeR2,1)=C25,INDEX(cbalSI4CodeR2,1)=C25,INDEX(cbalSI5CodeR2,1)=C25,INDEX(cbalSI1CodeR3,1)=C25,INDEX(cbalSI2CodeR3,1)=C25,INDEX(cbalSI3CodeR3,1)=C25,INDEX(cbalSI4CodeR3,1)=C25,INDEX(cbalSI5CodeR3,1)=C25)';
+    var longI25b = 'OR(INDEX(cbalSI1CodeR1,1)=D27,INDEX(cbalSI2CodeR1,1)=D27,INDEX(cbalSI3CodeR1,1)=D27,INDEX(cbalSI4CodeR1,1)=D27,INDEX(cbalSI5CodeR1,1)=D27,INDEX(cbalSI1CodeR2,1)=D27,INDEX(cbalSI2CodeR2,1)=D27,INDEX(cbalSI3CodeR2,1)=D27,INDEX(cbalSI4CodeR2,1)=D27,INDEX(cbalSI5CodeR2,1)=D27,INDEX(cbalSI1CodeR3,1)=D27,INDEX(cbalSI2CodeR3,1)=D27,INDEX(cbalSI3CodeR3,1)=D27,INDEX(cbalSI4CodeR3,1)=D27,INDEX(cbalSI5CodeR3,1)=D27)';
+    assertFormulaReplace({formula: longI25a, from: 'A1', to: 'B3'}, longI25b);
+  });
+
+});