exceljs · agdevbridge · May 26, 2016
diff --git a/lib/doc/defined-names.js b/lib/doc/defined-names.js
@@ -26,6 +26,7 @@ var utils = require('../utils/utils');
 var colCache = require('../utils/col-cache');
 var CellMatrix = require('../utils/cell-matrix');
 var Range = require('./range');
+var rangeRegexp = /\$(\w+)\$(\d+)(:\$(\w+)\$(\d+))?/;
 
 var DefinedNames = module.exports = function() {
   this.matrixMap = {};
@@ -173,7 +174,9 @@ DefinedNames.prototype = {
     _.each(value, function(definedName) {
       var matrix = matrixMap[definedName.name] = new CellMatrix();
       _.each(definedName.ranges, function(rangeStr) {
-        matrix.addCell(rangeStr);
+        if(rangeRegexp.test(rangeStr.split('!').pop() || '')) {
+          matrix.addCell(rangeStr);
+        }
       });
     });
   }

diff --git a/lib/utils/col-cache.js b/lib/utils/col-cache.js
@@ -170,8 +170,8 @@ var colCache = module.exports = {
 
     var parts = value.split('!');
     if (parts.length > 1) {
-      sheetName = parts[0];
-      value = parts[1];
+      value = parts.pop();
+      sheetName = parts.join('!').replace(/^'|'$/g, '');
     }
 
     parts = value.split(':');

diff --git a/lib/xlsx/xform/defined-names-xform.js b/lib/xlsx/xform/defined-names-xform.js
@@ -23,7 +23,6 @@
 'use strict';
 var _ = require('underscore');
 
-var XmlStream = require('../../utils/xml-stream');
 var utils = require('../../utils/utils');
 var BaseXform = require('./base-xform');
 
@@ -40,7 +39,7 @@ utils.inherits(DefinedNamesXform, BaseXform, {
 
     if (model && model.length) {
       xmlStream.openNode('definedNames');
-      _.each(this.model, function(definedName) {
+      _.each(model, function(definedName) {
         xmlStream.openNode('definedName');
         xmlStream.addAttribute('name', definedName.name);
         xmlStream.writeText(definedName.ranges.join(','));
@@ -58,13 +57,43 @@ utils.inherits(DefinedNamesXform, BaseXform, {
   parseClose: function(name) {
     this.model.push({
       name: this._parsedName,
-      ranges: this._parsedText.join('')
-        .split(',')
-        .filter(function (range) {
-          return range;
-        })
+      ranges: extractRanges(this._parsedText.join(''))
     });
     this._parsedName = undefined;
     this._parsedText = undefined;
   }
-});
+});
+
+function extractRanges(parsedText) {
+  var ranges = [];
+  var quotesOpened = false;
+  var last = '';
+  parsedText.split(',').forEach(function (item) {
+    if(!item){
+      return;
+    }
+    var quotes = (item.match(/'/g) || []).length;
+
+    if (!quotes) {
+      if (quotesOpened) {
+        last += item + ',';
+      } else {
+        ranges.push(item);
+      }
+      return;
+    }
+    var quotesEven = quotes % 2 === 0;
+
+    if (!quotesOpened && quotesEven) {
+      ranges.push(item);
+    } else if (quotesOpened && !quotesEven) {
+      quotesOpened = false;
+      ranges.push(last + item);
+      last = '';
+    } else {
+      quotesOpened = true;
+      last += item + ',';
+    }
+  });
+  return ranges;
+}
diff --git a/spec/unit/doc/defined-names.spec.js b/spec/unit/doc/defined-names.spec.js
@@ -17,6 +17,11 @@ describe('DefinedNames', function() {
     dn.add('blort!$B$4','bar');
     expect(dn.getNames('blort!B4')).to.deep.equal(['bar']);
     expect(dn.getNames('blort!$B$4')).to.deep.equal(['bar']);
+
+    dn.add("'blo rt'!$B$4",'bar');
+    expect(dn.getNames("'blo rt'!$B$4")).to.deep.equal(['bar']);
+    dn.add("'blo ,!rt'!$B$4",'bar');
+    expect(dn.getNames("'blo ,!rt'!$B$4")).to.deep.equal(['bar']);
   });
 
   it('removes names for cells', function() {
@@ -54,4 +59,29 @@ describe('DefinedNames', function() {
     expect(dn.getRanges('single')).to.deep.equal({name: 'single', ranges: ['other!$A$1']});
   });
 
+  it('creates matrix from model', function() {
+    var dn = new DefinedNames();
+
+    dn.model = [];
+    dn.add('blort!A1','bar');
+    dn.remove('blort!A1','foo');
+
+    expect(dn.getNames('blort!A1')).to.deep.equal(['bar']);
+  });
+
+  it('skips values with invalid range', function() {
+    var dn = new DefinedNames();
+    dn.model = [
+      {name: 'eq', ranges: ['"="']},
+      {name: 'ref', ranges: ['#REF!']},
+      {name: 'single', ranges: ['Sheet3!$A$1']},
+      {name: 'range', ranges: ['Sheet3!$A$2:$F$2228']}
+    ];
+
+    expect(dn.model).to.deep.equal([
+      {name: 'single', ranges: ['Sheet3!$A$1']},
+      {name: 'range', ranges: ['Sheet3!$A$2:$F$2228']}
+    ]);
+  });
+
 });
diff --git a/spec/unit/utils/col-cache.spec.js b/spec/unit/utils/col-cache.spec.js
@@ -70,6 +70,12 @@ describe('colCache', function() {
     expect(colCache.decodeAddress('AA11')).to.deep.equal({address:'AA11',col:27, row: 11, $col$row: '$AA$11'});
   });
 
+  it('convert [sheetName!][$]col[$]row[[$]col[$]row] into address or range structures', function() {
+    expect(colCache.decodeEx('Sheet1!$H$1')).to.deep.equal({'$col$row':'$H$1', address:'H1', col:8, row:1, sheetName:'Sheet1'});
+    expect(colCache.decodeEx("'Sheet 1'!$H$1")).to.deep.equal({'$col$row':'$H$1', address:'H1', col:8, row:1, sheetName:'Sheet 1'});
+    expect(colCache.decodeEx("'Sheet !$:1'!$H$1")).to.deep.equal({'$col$row':'$H$1', address:'H1', col:8, row:1, sheetName:'Sheet !$:1'});
+  });
+
   it('gets address structures (and caches them)', function() {
     var addr = colCache.getAddress('D5');
     expect(addr.address).to.equal('D5');

diff --git a/spec/unit/xlsx/xform/defined-names-xform.spec.js b/spec/unit/xlsx/xform/defined-names-xform.spec.js
@@ -18,4 +18,34 @@ describe('DefinedNamesXform', function() {
     var dnx = new DefinedNamesXform(model);
       expect(dnx.xml).to.equal(xml);
   });
+
+  it('parses ranges', function() {
+    var dnx = new DefinedNamesXform();
+    dnx.parseOpen({attributes: {name: 'single'}});
+    dnx.parseText("'Sheet name'',$''!$'!$A$2");
+    dnx.parseClose();
+
+    dnx.parseOpen({attributes: {name: 'range'}});
+    dnx.parseText("'Sheet name'',$''!$'!$A$1:$B$1");
+    dnx.parseClose();
+
+    dnx.parseOpen({attributes: {name: 'multiple'}});
+    dnx.parseText("'Sheet name''$''!$'!$A$2,'Sheet name''$''!$'!$H$7,'Sheet3!'!$A$1,Sheet3!$A$1");
+    dnx.parseClose();
+
+    expect(dnx.model).to.deep.equal([
+      {name: 'single', ranges: ["'Sheet name'',$''!$'!$A$2"]},
+      {name: 'range', ranges: ["'Sheet name'',$''!$'!$A$1:$B$1"]},
+      {
+        name: 'multiple',
+        ranges: [
+          "'Sheet name''$''!$'!$A$2",
+          "'Sheet name''$''!$'!$H$7",
+          "'Sheet3!'!$A$1",
+          "Sheet3!$A$1"
+        ]
+      }
+    ]);
+  });
+
 });