node-xlsx2json - npm Package Compare versions

Comparing version 0.9.0 to 0.9.1

app.js

@@ -21,6 +21,6 @@ var os = require('os');
   console.log("Options:");
-  console.log("  -f              Force overwrite");
+  console.log("  --force         Force overwrite file");
   console.log("  --help          Print this message");
   console.log("  --verbose       Enable detailed logging");
-  console.log("  --version, -v   Print version number");
+  console.log("  --version       Print version number");
   console.log("");
@@ -30,3 +30,3 @@ console.log("Examples:");
   console.log("  xlsx2json --verbose file.xlsx");
-  console.log("  xlsx2json -f file.xlsx file.json");
+  console.log("  xlsx2json --force file.xlsx file.json");
 }
@@ -44,2 +44,3 @@
     case "-f":
+    case "--force":
       config.force = true;
@@ -61,2 +62,3 @@ break;
         console.error("Unknown command line argument: " + args[i]);
+        process.exit(2);
       }
@@ -63,0 +65,0 @@ else if (!config.source) {

lib/index.js

@@ -7,5 +7,77 @@ var fs = require('fs');
 
+Array.prototype.contains = function(obj) {
+  var i = this.length;
+  while (i--) {
+    if (this[i] === obj) {
+      return true;
+    }
+  }
+};
+
+var Excel = { };
+
+//Gets a column letter from a number, e.g. 1 = A, 2 = B, 27 = AA, etc.
+Excel.colname = function(column) {
+  var columnString = "";
+  var columnNumber = column;
+  while (columnNumber > 0) {
+    var currentLetterNumber = (columnNumber - 1) % 26;
+    var currentLetter = String.fromCharCode(currentLetterNumber + 65);
+    columnString = currentLetter + columnString;
+    columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
+  }
+  return columnString;
+}
+
+//Gets a column number from a letter, e.g. A = 1, B = 2, AA = 27, etc.
+Excel.colnum = function(column) {
+  var retVal = 0;
+  var col = column.toUpperCase();
+  for (var iChar = col.length - 1; iChar >= 0; iChar--) {
+    var colPiece = col[iChar];
+    var colNum = colPiece.charCodeAt(0) - 64;
+    retVal = retVal + colNum * Math.floor(Math.pow(26, col.length - (iChar + 1)));
+  }
+  return retVal;
+}
+
+Excel.name = function(pos) {
+  if (arguments.length == 0) throw new Error("Formula error");
+  return Excel.colname(pos.column) + pos.row;
+};
+
+Excel.pos = function(name) {
+  if (arguments.length == 0) throw new Error("Formula error");
+  var m = /^([A-Z]+)([0-9]+)$/.exec(name);
+  if (!m) throw new Error("Argument");
+  var column = Excel.colnum(m[1]);
+  var row = parseInt(m[2]);
+  return { row: row, column: column };
+};
+
+Excel.range = function(range) {
+  var parts = range.split(":");
+  var start = Excel.pos(parts[0]);
+  var stop = Excel.pos(parts[1]);
+  
+  var result = [ ];
+  for (var r = Math.min(start.row, stop.row); r <= Math.max(start.row, stop.row); r++) {
+    for (var c = Math.min(start.column, stop.column); c <= Math.max(start.column, stop.column); c++) {
+      var cell = { row: r, column: c };
+      cell.name = Excel.name(cell);
+      result.push(cell);
+    }
+  }
+  return result;
+};
+
+
 /** Converts XLSX file to JSON object */
 function xlsx2json(file, options, callback) {
+  //Private members
   var worksheets = { };
+  var strings = [ ];
+  var styles = [ ];
+  var table = [ ];
+  var after = [ ];
   
@@ -22,3 +94,206 @@ //Shift arguments
   
+  //Default options
   options = options || { };
+  
+  //Converts Excel value
+  function getValueType(t) {
+    switch (t) {
+      case "s":
+      case "str":
+      case "inlineStr":
+        return "string";
+        
+      case "b":
+        return "bool";
+        
+      case "d":
+        return "date";
+        
+      case "e":
+        return "error";
+        
+      case "n":
+        return "number";
+    }
+    
+    return undefined; //General
+  }
+  
+  //Formats a value to display to the user
+  function getValueToDisplay(value, style) {
+    var match, format = style.formatCode;
+    
+    //Format number: 0
+    if (format == "0") {
+      return "" + Math.round(parseFloat(value));
+    }
+    
+    //Format number: 0.00, 0.000, 0.0000, etc.
+    match = /^0\.(0+)$/g.exec(format);
+    if (match) {
+      var digits = match[1].length;
+      return "" + parseFloat(value).toFixed(digits);
+    }
+    
+    //Exponential number: 0.00, 0.000, 0.0000, etc.
+    match = /^0\.(0+)[Ee]([\+\-]0+)$/g.exec(format);
+    if (match) {
+      var digits = match[1].length;
+      //var powerDigits = match[2].length;
+      return "" + parseFloat(value).toExponential(digits).toString().toUpperCase() + "%";
+    }
+    
+    //Format percent: 0%
+    if (format == "0%") {
+      return "" + Math.round(parseFloat(100 * value)) + "%";
+    }
+    
+    //Format percent: 0.00%, 0.000%, 0.0000%, etc.
+    match = /^0\.(0+)\%$/g.exec(format);
+    if (match) {
+      var digits = match[1].length;
+      return "" + parseFloat(100 * value).toFixed(digits);
+    }
+  }
+  
+  //Reads an xml entry and responds with json object
+  function readEntry(entry, done) {
+    if (options.verbose) {
+      console.log("Found " + entry.path);
+    }
+    
+    var ws = new MemoryStream();
+    ws.on('finish', function() {
+      var xml = ws.toString();
+      xml2js(xml, function(error, result) {
+        try {
+          if (error) {
+            throw error;
+          }
+          
+          done(result);
+        }
+        catch (e) {
+          if (options.verbose) {
+            console.error(e);
+          }
+          var func = callback;
+          callback = function(error, result) { };
+          func(e);
+        }
+      });
+      
+    });      
+    entry.pipe(ws);
+  }
+  
+  //Process the worksheet
+  function readWorksheet(ws, done) {
+    var sheetId = ws.sheetId;
+    var result = ws.result;
+    
+    //Define the worksheet record
+    worksheets[sheetId] = {
+      sheetId: sheetId,
+      //file: "sheet" + sheetId,
+      name: "Sheet " + sheetId,
+      data: [ ]
+    };
+    
+    //Only specific worksheet
+    //if (options.sheet != worksheets[sheetId].name) {
+    //  return done();
+    //}
+    
+    //Find the sheet name
+    for (var i = 0; i < sheets.length; i++) {
+      //if (sheetId == sheets[i].$.sheetId) {
+      if (sheetId == i + 1) {
+        worksheets[sheetId].name = sheets[i].$.name;
+        break;
+      }
+    }
+    
+    if (typeof result.worksheet.sheetData[0] != "object") {
+      result.worksheet.sheetData[0] = { row: [ ] };
+    }
+    
+    var rows = result.worksheet.sheetData[0].row;
+    if (options.verbose) {
+      console.log("Found " + rows.length + " rows on sheet " + sheetId + " [" + worksheets[sheetId].name + "]");
+    }
+    
+    //For each row
+    for (var i = 0; i < rows.length; i++) {
+      var r = parseInt(rows[i].$.r);
+      var cells = rows[i].c;
+      if (!cells) continue;
+      
+      //For each cell in a row
+      for (var j = 0; j < cells.length; j++) {
+        var cell = cells[j];
+        
+        var name = cell.$.r;
+        var pos = Excel.pos(name);
+        //if (pos.row <= capacity.row && pos.column <= capacity.column) {
+        
+        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);
+        if (formula || value) {
+          
+          var item = { };
+          var type = getValueType(cell.$.t);
+          var style = cell.$.s ? styles[parseInt(cell.$.s)] : null;
+          
+          //Shared string
+          if (cell.$.t == "s") {
+            var index = parseInt(value);
+            value = strings[index];
+          }
+          
+          item.cell = cell.$.r;
+          item.type = type;
+          if (formula) {
+            item.formula = "=" + formula;
+          }
+          if (value) {
+            item.value = value;
+          }
+          if (value && style && style.formatCode) {
+            item.display = getValueToDisplay(value, style);
+            item.format = style.formatCode;
+          }
+          worksheets[sheetId].data.push(item);
+        }
+      }
+    }
+    
+    done();
+  }
+  
+  //Conversion complete, let's invoke the callback function
+  function readComplete() {
+    if (options.verbose) {
+      console.log("Finished reading document");
+    }
+      
+    //Convert dictionary to sorted array
+    var sheets = [ ];
+    var keys = Object.keys(worksheets);
+    for (var i = 0; i < keys.length; i++) {
+      var worksheet = worksheets[keys[i]]; //Sorted as in Excel
+      //var worksheet = worksheets[(i + 1).toString()]; //Sorted by sheetId
+      sheets.push(worksheet);
+    }
+    
+    if (keys.length == 0 && options.verbose) {
+      console.log("No records found. May not be an XLSX file.");
+    }
+    
+    var result = {
+      worksheets: sheets
+    };
+    
+    callback(null, result);
+  }
  
@@ -38,40 +313,93 @@ try {
     .on('entry', function (entry) {
-      if (options.verbose) {
-        //console.log(entry.path);  
-      }
       
       //Worksheet names
       if (entry.path.indexOf("xl/workbook.xml") == 0) {
-        if (options.verbose) {
-          console.log("Found wl/workbook.xml");
-        }
-        var ws = new MemoryStream();
-        ws.on('finish', function() {
-          var xml = ws.toString();
-          xml2js(xml, function(error, result) {
-            try {
-              if (error) {
-                throw error;
-              }
-              
-              sheets = result.workbook.sheets[0].sheet;
-              if (options.verbose) {
-                console.log("Found " + sheets.length + " worksheets");
-              }
-            }
-            catch (e) {
-              if (options.verbose) {
-                console.error(e);
-              }
-              var func = callback;
-              callback = function(error, result) { };
-              func(e);
-            }
-          });
+        readEntry(entry, function(result) {
+          sheets = result.workbook.sheets[0].sheet;
+          if (options.verbose) {
+            console.log("Found " + sheets.length + " worksheets");
+          }
+        });
+        return;
+      }
+      
+      //Shared strings
+      if (entry.path.indexOf("xl/sharedStrings.xml") == 0) {
+        readEntry(entry, function(result) {
+          var length = result.sst.si.length;
+          if (options.verbose) {
+            console.log("Found " + length + "  shared strings");
+          }
           
-        });      
-        entry.pipe(ws);
+          for (var i = 0; i < length; i++) {
+            var text = result.sst.si[i].t[0];
+            strings.push(text);
+          }
+        });
         return;
       }
       
+      //Styles
+      if (entry.path.indexOf("xl/styles.xml") == 0) {
+        readEntry(entry, function(result) {
+          var formats = { };
+          
+          //Built-in formats
+          var i = 0;
+          formats[++i] = { numFmtId: 1, formatCode: "0" };
+          formats[++i] = { numFmtId: 2, formatCode: "0.00" };
+          formats[++i] = { numFmtId: 3, formatCode: "#,##0" };
+          formats[++i] = { numFmtId: 4, formatCode: "#,##0.00" };
+          formats[++i] = { numFmtId: 5, formatCode: "$#,##0_);($#,##0)" };
+          formats[++i] = { numFmtId: 6, formatCode: "$#,##0_);[Red]($#,##0)" };
+          formats[++i] = { numFmtId: 7, formatCode: "$#,##0.00_);($#,##0.00)" };
+          formats[++i] = { numFmtId: 8, formatCode: "$#,##0.00_);[Red]($#,##0.00)" };
+          formats[++i] = { numFmtId: 9, formatCode: "0%" };
+          formats[++i] = { numFmtId: 10, formatCode: "0.00%" };
+          formats[++i] = { numFmtId: 11, formatCode: "0.00E+00" };
+          formats[++i] = { numFmtId: 12, formatCode: "# ?/?" };
+          formats[++i] = { numFmtId: 13, formatCode: "# ??/??" };
+          formats[++i] = { numFmtId: 14, formatCode: "m/d/yyyy" };
+          formats[++i] = { numFmtId: 15, formatCode: "d-mmm-yy" };
+          formats[++i] = { numFmtId: 16, formatCode: "d-mmm" };
+          formats[++i] = { numFmtId: 17, formatCode: "mmm-yy" };
+          formats[++i] = { numFmtId: 18, formatCode: "h:mm AM/PM" };
+          formats[++i] = { numFmtId: 19, formatCode: "h:mm:ss AM/PM" };
+          formats[++i] = { numFmtId: 20, formatCode: "h:mm" };
+          formats[++i] = { numFmtId: 21, formatCode: "h:mm:ss" };
+          formats[++i] = { numFmtId: 22, formatCode: "m/d/yyyy h:mm" };
+          formats[++i] = { numFmtId: 37, formatCode: "#,##0_);(#,##0)" };
+          formats[++i] = { numFmtId: 38, formatCode: "#,##0_);[Red](#,##0)" };
+          formats[++i] = { numFmtId: 39, formatCode: "#,##0.00_);(#,##0.00)" };
+          formats[++i] = { numFmtId: 40, formatCode: "#,##0.00_);[Red](#,##0.00)" };
+          formats[++i] = { numFmtId: 45, formatCode: "mm:ss" };
+          formats[++i] = { numFmtId: 46, formatCode: "[h]:mm:ss" };
+          formats[++i] = { numFmtId: 47, formatCode: "mm:ss.0" };
+          formats[++i] = { numFmtId: 48, formatCode: "##0.0E+0" };
+          formats[++i] = { numFmtId: 49, formatCode: "@" };
+
+          //Additional formats
+          var numFmts = result.styleSheet.numFmts[0].numFmt;
+          for (var i = 0; i < numFmts.length; i++) {
+            formats[numFmts[i].$.numFmtId] = numFmts[i].$;
+          }
+          
+          var xfs = result.styleSheet.cellXfs[0].xf;
+          var length = xfs.length;
+          if (options.verbose) {
+            console.log("Found " + length + " value formats");
+          }
+          
+          for (var i = 0; i < length; i++) {
+            var style = xfs[i].$;
+            var format = formats[style.numFmtId];
+            if (format) {
+              style.formatCode = format.formatCode;
+            }
+            styles.push(style);
+          }
+        });
+        return;
+      }
+      
       //Worksheet
@@ -81,60 +409,10 @@ var match = /^xl\/worksheets\/sheet(\d+)\.xml$/g.exec(entry.path);
         var sheetId = parseInt(match[1]);
-        
-        if (options.verbose) {
-          console.log("Found xl/worksheets/sheet" + sheetId + ".xml");
-        }
-        
-        //Define the worksheet record
-        worksheets[sheetId] = {
-          sheetId: sheetId,
-          //file: "sheet" + sheetId,
-          name: "Sheet " + sheetId,
-          data: [ ]
-        };
-        
-        //Find the sheet name
-        for (var i = 0; i < sheets.length; i++) {
-          //if (sheets[i].$.sheetId == sheetId) {
-          if (i + 1 == sheetId) {
-            worksheets[sheetId].name = sheets[i].$.name;
-            break;
-          }
-        }
-
-        //Parse the worksheet file
-        var ws = new MemoryStream();
-        ws.on('finish', function() {
-          var xml = ws.toString();
-          xml2js(xml, function(error, result) {
-            if (typeof result.worksheet.sheetData[0] != "object") {
-              result.worksheet.sheetData[0] = { row: [ ] };
-            }
-            var rows = result.worksheet.sheetData[0].row;
-            if (options.verbose) {
-              console.log("Found " + rows.length + " rows on sheet " + sheetId + " [" + worksheets[sheetId].name + "]");
-            }
-            for (var i = 0; i < rows.length; i++) {
-              var r = parseInt(rows[i].$.r);
-              var cells = rows[i].c;
-              if (!cells) continue;
-              for (var j = 0; j < cells.length; j++) {
-                var cell = cells[j];
-                var item = { };
-                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);
-                if (formula || value) {
-                  item.cell = cell.$.r;
-                  if (formula) {
-                    item.formula = "=" + formula;
-                  }
-                  if (value) {
-                    item.value = value;
-                  }
-                  worksheets[sheetId].data.push(item);
-                }
-              }
-            }
+        readEntry(entry, function(result) {
+          //Process worksheet after the shared strings, styles, etc. are done
+          after.push({
+            sheetId: sheetId,
+            entry: entry,
+            result: result
           });
-        });      
-        entry.pipe(ws);
+        });
         return;
@@ -145,24 +423,14 @@ }
     })
-    .on('close', function() { //close, finish
-      if (options.verbose) {
-        console.log("Finished reading document");
-      }
-        
-      //Convert dictionary to sorted array
-      var sheets = [ ];
-      var keys = Object.keys(worksheets);
-      for (var i = 0; i < keys.length; i++) {
-        var worksheet = worksheets[keys[i]]; //Sorted as in Excel
-        //var worksheet = worksheets[(i + 1).toString()]; //Sorted by sheetId
-        sheets.push(worksheet);
-      }
+    .on('close', function() {
+    
+      //Read worksheets
+      var i = 0;
+      (function next() {
+        var item = after[i++];
+        if (!item) return readComplete();
+        readWorksheet(item, function() {
+          next();
+        });
+      })();
       
-      if (keys.length == 0 && options.verbose) {
-        console.log("No records found. May not be an XLSX file.");
-      }
-      
-      var result = {
-        worksheets: sheets
-      };
-      callback(null, result);
     });
@@ -169,0 +437,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.0",
+  "version": "0.9.1",
   "author": "Papn Kukn",
@@ -6,0 +6,0 @@ "license": "MIT",

README.md

@@ -24,6 +24,6 @@ ## Introduction
 Options:
-  -f              Force overwrite
+  --force         Force overwrite file
   --help          Print this message
   --verbose       Enable detailed logging
-  --version, -v   Print version number
+  --version       Print version number
 
@@ -33,3 +33,3 @@ Examples:
   xlsx2json --verbose file.xlsx
-  xlsx2json -f file.xlsx file.json
+  xlsx2json --force file.xlsx file.json
 ```
@@ -87,3 +87,4 @@
           "cell": "A1",
-          "value": "0"
+          "type": "string",
+          "value": "xlsx2json"
         },
@@ -96,3 +97,5 @@ {
           "cell": "A3",
-          "value": "1"
+          "value": "1",
+          "display": "1",
+          "format": "0"
         },
@@ -105,3 +108,5 @@ {
           "cell": "A5",
-          "value": "0.99"
+          "value": "0.99",
+          "display": "99%",
+          "format": "0%"
         },
@@ -108,0 +113,0 @@ {

No alert changes

Improved metrics

Total package byte prevSize

31991

increased by35.68%

Lines of code

518

increased by77.4%

Number of lines in readme file

116

increased by4.5%

No dependency changes