node-xlsx2json - npm Package Compare versions

Comparing version 0.9.1 to 0.9.2

lib/index.js

@@ -50,3 +50,3 @@ var fs = require('fs');
   if (arguments.length == 0) throw new Error("Formula error");
-  var m = /^([A-Z]+)([0-9]+)$/.exec(name);
+  var m = /^\$?([A-Z]+)\$?([0-9]+)$/.exec(name);
   if (!m) throw new Error("Argument");
@@ -74,3 +74,97 @@ var column = Excel.colnum(m[1]);
 
+Excel.offset = function(range) {
+  var parts = range.split(":");
+  var start = Excel.pos(parts[0]);
+  var stop = Excel.pos(parts[1]);
 
+  var result = {
+    r1: start.row,
+    c1: start.column,
+    r2: stop.row,
+    c2: stop.column
+  };
+  
+  //Distance between rows and columns
+  result.dr = result.r2 - result.r1;
+  result.dc = result.c2 - result.c1;
+  
+  return result;
+};
+
+function zeropad(num, size) {
+  var s = num+"";
+  while (s.length < size) s = "0" + s;
+  return s;
+}
+
+function getCells(formula) {
+  var cells = [ ];
+  var regex = /('?([\w\d\s]+)'?\!)?(\$?[A-Z]+\$?[0-9]+)/g;
+  
+  var match;
+  while (match = regex.exec(formula)) {
+    if (match.index === regex.lastIndex) {
+      regex.lastIndex++;
+    }
+  
+    var cell = match[0];
+    if (cell == "LOG10") {
+      continue;
+    }
+    cells.push(cell);
+  }
+  
+  return cells;
+}
+
+function shiftCells(cells, deltaRows, deltaColumns) {
+  var result = [ ];
+  for (var i = 0; i < cells.length; i++) {
+    var cell = cells[i];
+    if (cell.indexOf('!') > 0) {
+      continue; //Skip cross-sheet references
+    }
+    
+    var pcell = Excel.pos(cell);
+    
+    var lockColumn = cell.indexOf('$' == 0); //e.g. $A1
+    var lockRow = cell.indexOf('$' > 0); //e.g. A$1
+    
+    var shifted = Excel.name({
+      row: pcell.row + (lockRow ? 0 : deltaRows),
+      column: pcell.column + (lockColumn ? 0 : deltaColumns),
+    });
+    
+    var item = {
+      original: cell,
+      shifted: shifted
+    };
+    
+    result.push(item);
+  }
+  return result;
+}
+
+function shiftFormula(originalFormula, deltaRows, deltaColumns) {
+  var formula = "" + originalFormula; //Copy string
+  
+  var originalCells = getCells(originalFormula)   ;
+  var shiftedCells = shiftCells(originalCells, deltaRows, deltaColumns);
+  //console.log("Shifted cells", deltaRows, deltaColumns, shiftedCells);
+  
+  //Replace original cell names with constants
+  for (var i = 0; i < shiftedCells.length; i++) {
+    var regex = new RegExp('\\b' + shiftedCells[i].original + '\\b', 'g');
+    formula = formula.replace(regex, "SHIFTED_CELL_" + zeropad(i, 4));
+  }
+  
+  //Replace constants with shifted cell names
+  for (var i = 0; i < shiftedCells.length; i++) {
+    var regex = new RegExp("SHIFTED_CELL_" + zeropad(i, 4), 'g');
+    formula = formula.replace(regex, shiftedCells[i].shifted);
+  }
+  
+  return formula;
+}
+
 /** Converts XLSX file to JSON object */
@@ -195,2 +289,4 @@ function xlsx2json(file, options, callback) {
     
+    var sharedFormulas = { };
+    
     //Define the worksheet record
@@ -241,4 +337,55 @@ worksheets[sheetId] = {
         
-        var formula = (cell.f && cell.f.length && !cell.f[0].$ ? cell.f[0] : undefined);
-        var value = (cell.v && cell.v.length ? cell.v[0] : undefined);
+        //Resolve formula
+        var formula = undefined;
+        var f = cell.f;
+        if (f && f.length) { //Expected array
+          f = f[0];
+          if (typeof f == "string") {
+            formula = f;
+          }
+          else if (f.$) {
+            var attr = f.$;
+            if (attr.t == "shared") { //Shared formula
+              if (attr.ref) {
+                sharedFormulas[attr.si] = { formula: f._, ref: attr.ref };
+              }
+              if (f._) {
+                formula = f._;
+              }
+              else if (sharedFormulas[attr.si]) {
+                var o = sharedFormulas[attr.si].ref.split(':')[0];
+                var t = name;
+                var offset = Excel.offset(o + ':' + t);
+                formula = shiftFormula(sharedFormulas[attr.si].formula, offset.dr, offset.dc);
+                if (options.debug) {
+                  console.log("Shifting formula", '=' + sharedFormulas[attr.si].formula, "to", '=' + formula, "by offset", "R" + offset.dr + ",", "C" + offset.dc);
+                }
+              }
+            }
+            else if (f._) {
+              formula = f._;
+            }
+          }
+          
+          if (!formula && options.verbose) {
+            console.log("Unknown formula [" + worksheets[sheetId].name + "." + name + "]:", cell.f);
+          }
+        }
+        
+        //Resolve constant value
+        var value = undefined;
+        var v = cell.v;
+        if (v && v.length) {
+          v = v[0];
+          if (typeof v == "string") {
+            value = v;
+          }
+          else if (v["_"]) {
+            value = v["_"];
+          }
+          else if (options.verbose) {
+            console.log("Unknown value [" + worksheets[sheetId].name + "." + name + "]:", cell.v);
+          }
+        }
+        
         if (formula || value) {
@@ -245,0 +392,0 @@

package.json

 {
   "name": "node-xlsx2json",
   "description": "Excel XLSX document to JSON converter as a Node.js library and a command line utility",
-  "version": "0.9.1",
+  "version": "0.9.2",
   "author": "Papn Kukn",
@@ -6,0 +6,0 @@ "license": "MIT",

No alert changes

Improved metrics

Total package byte prevSize

36133

increased by12.95%

Lines of code

642

increased by23.94%

No dependency changes