Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

exceljs

Package Overview
Dependencies
Maintainers
1
Versions
165
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

exceljs - npm Package Compare versions

Comparing version 0.2.3 to 0.2.4

lib/stream/xlsx/hyperlink-reader.js
lib/stream/xlsx/workbook-reader.js
lib/stream/xlsx/worksheet-reader.js
lib/utils/auto-drain.js
lib/utils/flow-control.js
lib/utils/stuttered-pipe.js

3

excel.js

@@ -32,3 +32,4 @@ /**

xlsx: {
WorkbookWriter: require("./lib/stream/xlsx/workbook-writer")
WorkbookWriter: require("./lib/stream/xlsx/workbook-writer"),
WorkbookReader: require("./lib/stream/xlsx/workbook-reader")
}

@@ -35,0 +36,0 @@ }

49

lib/cell.js

@@ -55,3 +55,12 @@ /**

Cell.prototype = {
// help GC by removing cyclic (and other) references
destroy: function() {
delete this.style;
delete this._value;
delete this._row;
delete this._col;
delete this._address;
},
// =========================================================================

@@ -551,2 +560,35 @@ // Styles stuff

var SharedStringValue = function(cell, value) {
this.model = {
address: cell.address,
type: Cell.Types.SharedString,
value: value
};
}
SharedStringValue.prototype = {
get value() {
return this.model.value;
},
set value(value) {
return this.model.value = value;
},
get type() {
return Cell.Types.SharedString;
},
get effectiveType() {
return Cell.Types.SharedString;
},
get address() {
return this.model.address;
},
set address(value) {
return this.model.address = value;
},
toCsvString: function() {
return "" + this.model.value;
},
release: function() {
}
};
// Value is a place to hold common static Value type functions

@@ -567,2 +609,4 @@ var Value = {

return Cell.Types.Formula;
} else if (value.sharedString) {
return Cell.Types.SharedString;
} else {

@@ -582,3 +626,4 @@ //console.log("Error: value=" + value + ", type=" + typeof value)

{t:Cell.Types.Formula, f:FormulaValue},
{t:Cell.Types.Merge, f:MergeValue}
{t:Cell.Types.Merge, f:MergeValue},
{t:Cell.Types.SharedString, f:SharedStringValue}
].reduce(function(p,t){p[t.t]=t.f; return p;}, []),

@@ -585,0 +630,0 @@

397

lib/column.js
/**
* Copyright (c) 2014 Guyon Roche
*
*
* Permission is hereby granted, free of charge, to any person obtaining a copy

@@ -10,6 +10,6 @@ * of this software and associated documentation files (the "Software"), to deal

* 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

@@ -22,3 +22,3 @@ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

* THE SOFTWARE.
*
*
*/

@@ -32,187 +32,222 @@ "use strict";

// Worksheet will condense the columns as appropriate during serialization
var Column = module.exports = function(worksheet, number, defn) {
this._worksheet = worksheet;
this._number = number;
if (defn !== false) {
// sometimes defn will follow
this.defn = defn;
}
}
var Column = module.exports = function (worksheet, number, defn) {
this._worksheet = worksheet;
this._number = number;
if (defn !== false) {
// sometimes defn will follow
this.defn = defn;
}
};
Column.prototype = {
get number() {
return this._number;
},
get isCustomWidth() {
return (this.width !== undefined) && (this.width !== 8);
},
get defn() {
return {
header: this._header,
key: this.key,
width: this.width,
style: this.style
};
},
set defn(value) {
if (value) {
this.key = value.key;
this.width = value.width;
if (value.style) { this.style = value.style; }
else { this.style = {} };
// headers must be set after style
this.header = value.header;
} else {
delete this._header;
delete this.key;
delete this.width;
this.style = {};
}
},
get headers() {
return this._header && (this._header instanceof Array) ? this._header : [this._header];
},
get header() {
return this._header;
},
set header(value) {
if (value != undefined) {
var self = this;
this._header = value;
_.each(this.headers, function(text, index) {
self._worksheet.getCell(index + 1, self.number).value = text;
});
} else {
this._header = [];
}
return value;
},
get key() {
return this._key;
},
set key(value) {
if (this._key) {
delete this._worksheet._keys[this._key];
}
this._key = value;
if (value) {
this._worksheet._keys[this._key] = this;
}
return value;
},
toString: function() {
return JSON.stringify({
key: this.key,
width: this.width,
headers: this.headers.length ? this.headers : undefined
});
},
equivalentTo: function(other) {
return
(this.width == other.width) &&
get number() {
return this._number;
},
get isCustomWidth() {
return (this.width !== undefined) && (this.width !== 8);
},
get defn() {
return {
header: this._header,
key: this.key,
width: this.width,
style: this.style,
hidden: this.hidden
};
},
set defn(value) {
if (value) {
this.key = value.key;
this.width = value.width;
if (value.style) {
this.style = value.style;
}
else {
this.style = {}
}
// headers must be set after style
this.header = value.header;
this._hidden = !!value.hidden;
} else {
delete this._header;
delete this.key;
delete this.width;
this.style = {};
}
},
get headers() {
return this._header && (this._header instanceof Array) ? this._header : [this._header];
},
get header() {
return this._header;
},
set header(value) {
if (value != undefined) {
var self = this;
this._header = value;
_.each(this.headers, function (text, index) {
self._worksheet.getCell(index + 1, self.number).value = text;
});
} else {
this._header = [];
}
return value;
},
get key() {
return this._key;
},
set key(value) {
if (this._key) {
delete this._worksheet._keys[this._key];
}
this._key = value;
if (value) {
this._worksheet._keys[this._key] = this;
}
return value;
},
get hidden() {
return !!this._hidden;
},
set hidden(value) {
return this._hidden = value;
},
toString: function () {
return JSON.stringify({
key: this.key,
width: this.width,
headers: this.headers.length ? this.headers : undefined
});
},
equivalentTo: function (other) {
return (this.width == other.width) &&
(this.hidden != other.hidden) &&
_.isEqual(this.style, other.style);
},
get isDefault() {
if (this.isCustomWidth) {
return false;
},
get isDefault() {
if (this.isCustomWidth) {
return false;
}
if (this.hidden) {
return false;
}
var s = this.style;
if (s && (s.font || s.numFmt || s.alignment || s.border || s.fill)) {
return false;
}
return true;
},
get headerCount() {
return this.headers.length;
},
eachCell: function (options, iteratee) {
var colNumber = this.number;
if (!iteratee) {
iteratee = options;
options = null;
}
if (options && options.includeEmpty) {
this._worksheet.eachRow(options, function (row, rowNumber) {
iteratee(row.getCell(colNumber), rowNumber);
});
} else {
this._worksheet.eachRow(function (row, rowNumber) {
var cell = row.findCell(colNumber);
if (cell) {
iteratee(cell, rowNumber);
}
var s = this.style;
if (s && (s.font || s.numFmt || s.alignment || s.border || s.fill)) {
return false;
}
return true;
},
get headerCount() {
return this.headers.length;
},
eachCell: function(options, iteratee) {
var colNumber = this.number;
if (!iteratee) {
iteratee = options;
options = null;
}
if (options && options.includeEmpty) {
this._worksheet.eachRow(options, function(row, rowNumber) {
iteratee(row.getCell(colNumber), rowNumber);
});
} else {
this._worksheet.eachRow(function(row, rowNumber) {
var cell = row.findCell(colNumber);
if (cell) {
iteratee(cell, rowNumber);
}
});
}
},
// =========================================================================
// styles
_applyStyle: function(name, value) {
this.style[name] = value;
this.eachCell(function(cell) {
cell[name] = value;
});
return value;
},
get numFmt() {
return this.style.numFmt;
},
set numFmt(value) {
return this._applyStyle("numFmt", value);
},
get font() {
return this.style.font;
},
set font(value) {
return this._applyStyle("font", value);
},
get alignment() {
return this.style.alignment;
},
set alignment(value) {
return this._applyStyle("alignment", value);
},
get border() {
return this.style.border;
},
set border(value) {
return this._applyStyle("border", value);
},
get fill() {
return this.style.fill;
},
set fill(value) {
return this._applyStyle("fill", value);
});
}
}
},
// =========================================================================
// styles
_applyStyle: function (name, value) {
this.style[name] = value;
this.eachCell(function (cell) {
cell[name] = value;
});
return value;
},
get numFmt() {
return this.style.numFmt;
},
set numFmt(value) {
return this._applyStyle("numFmt", value);
},
get font() {
return this.style.font;
},
set font(value) {
return this._applyStyle("font", value);
},
get alignment() {
return this.style.alignment;
},
set alignment(value) {
return this._applyStyle("alignment", value);
},
get border() {
return this.style.border;
},
set border(value) {
return this._applyStyle("border", value);
},
get fill() {
return this.style.fill;
},
set fill(value) {
return this._applyStyle("fill", value);
}
};
// =============================================================================
// static functions
Column.toModel = function(columns) {
// Convert array of Column into compressed list cols
var cols = [];
var col = null;
_.each(columns, function(column, index) {
if (column.isDefault) {
if (col) { col = null; }
} else {
if (!col || !column.equivalentTo(col)) {
col = {
min: index + 1,
max: index + 1,
width: column.width,
style: column.style,
isCustomWidth: column.isCustomWidth
};
cols.push(col);
} else {
col.max = index + 1;
}
}
});
return cols.length ? cols : undefined;
}
Column.toModel = function (columns) {
// Convert array of Column into compressed list cols
var cols = [];
var col = null;
_.each(columns, function (column, index) {
if (column.isDefault) {
if (col) {
col = null;
}
} else {
if (!col || !column.equivalentTo(col)) {
col = {
min: index + 1,
max: index + 1,
width: column.width,
style: column.style,
isCustomWidth: column.isCustomWidth,
hidden: column.hidden
};
cols.push(col);
} else {
col.max = index + 1;
}
}
});
return cols.length ? cols : undefined;
};
Column.fromModel = function (worksheet, cols) {
cols = cols || [];
var columns = [];
var count = 1;
var index = 0;
while (index < cols.length) {
var col = cols[index++];
while (count < col.min) {
columns.push(new Column(worksheet, count++));
}
while (count <= col.max) {
columns.push(new Column(worksheet, count++, col));
}
}
return columns.length ? columns : null;
};

3

lib/enums.js

@@ -33,3 +33,4 @@ /**

Hyperlink: 5,
Formula: 6
Formula: 6,
SharedString: 7
},

@@ -36,0 +37,0 @@ RelationshipType: {

495

lib/row.js
/**
* Copyright (c) 2014 Guyon Roche
*
*
* Permission is hereby granted, free of charge, to any person obtaining a copy

@@ -10,6 +10,6 @@ * of this software and associated documentation files (the "Software"), to deal

* 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

@@ -22,3 +22,3 @@ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

* THE SOFTWARE.
*
*
*/

@@ -32,237 +32,268 @@ "use strict";

var Row = module.exports = function(worksheet, number) {
this._worksheet = worksheet;
this._number = number;
this._cells = [];
this.style = {};
}
var Row = module.exports = function (worksheet, number) {
this._worksheet = worksheet;
this._number = number;
this._cells = [];
this.style = {};
};
Row.prototype = {
// return the row number
get number() {
return this._number;
},
// Inform Streaming Writer that this row (and all rows before it) are complete
// and ready to write. Has no effect on Worsheet document
commit: function() {
this._worksheet._commitRow(this);
},
findCell: function(colNumber) {
return this._cells[colNumber-1];
},
// given {address, row, col}, find or create new cell
_getCell: function(address) {
var cell = this._cells[address.col-1];
if (!cell) {
var column = this._worksheet.getColumn(address.col);
cell = new Cell(this, column, address.address);
this._cells[address.col-1] = cell;
// return the row number
get number() {
return this._number;
},
// Inform Streaming Writer that this row (and all rows before it) are complete
// and ready to write. Has no effect on Worksheet document
commit: function () {
this._worksheet._commitRow(this);
},
// helps GC by breaking cyclic references
destroy: function () {
delete this._worksheet;
_.each(this._cells, function (cell) {
cell.destroy();
});
delete this._cells;
delete this.style;
},
findCell: function (colNumber) {
return this._cells[colNumber - 1];
},
// given {address, row, col}, find or create new cell
_getCell: function (address) {
var cell = this._cells[address.col - 1];
if (!cell) {
var column = this._worksheet.getColumn(address.col);
cell = new Cell(this, column, address.address);
this._cells[address.col - 1] = cell;
}
return cell;
},
// get cell by key, letter or column number
getCell: function (col) {
if (typeof col == "string") {
// is it a key?
var column = this._worksheet._keys[col];
if (column) {
col = column.number;
} else {
col = colCache.l2n(col);
}
}
return this._cells[col - 1] ||
this._getCell({
address: colCache.encodeAddress(this._number, col),
row: this._number,
col: col
});
},
// Iterate over all non-null cells in this row
eachCell: function (options, iteratee) {
if (!iteratee) {
iteratee = options;
options = null;
}
if (options && options.includeEmpty) {
var n = this._cells.length;
for (var i = 1; i <= n; i++) {
iteratee(this.getCell(i), i);
}
} else {
_.each(this._cells, function (cell, index) {
if (cell.type != Enums.ValueType.Null) {
iteratee(cell, index + 1);
}
return cell;
},
// get cell by key, letter or column number
getCell: function(col) {
if (typeof col == "string") {
// is it a key?
var column = this._worksheet._keys[col];
if (column) {
col = column.number;
} else {
col = colCache.l2n(col);
}
});
}
},
// return a sparse array of cell values
get values() {
var values = [];
_.each(this._cells, function (cell) {
if (cell.type != Enums.ValueType.Null) {
values[cell.col] = cell.value;
}
});
return values;
},
// set the values by contiguous or sparse array, or by key'd object literal
set values(value) {
var self = this;
// this operation is not additive - any prior cells are removed
this._cells = [];
if (!value) {
// empty row
} else if (value instanceof Array) {
var offset = 0;
if (value[0] != undefined) {
// contiguous array - start at column 1
offset = 1;
}
_.each(value, function (item, index) {
self._getCell({
address: colCache.encodeAddress(self._number, index + offset),
row: self._number,
col: index + offset
}).value = item;
});
} else {
// assume object with column keys
_.each(this._worksheet._keys, function (column, key) {
if (value[key] !== undefined) {
self._getCell({
address: colCache.encodeAddress(self._number, column.number),
row: self._number,
col: column.number
}).value = value[key];
}
return this._cells[col-1] ||
this._getCell({
address: colCache.encodeAddress(this._number, col),
row: this._number,
col: col
});
},
// Iterate over all non-null cells in this row
eachCell: function(options, iteratee) {
if (!iteratee) {
iteratee = options;
options = null;
});
}
},
// returns true if the row includes at least one cell with a value
get hasValues() {
return _.some(this._cells, function (cell) {
return cell.type != Enums.ValueType.Null;
});
},
// get the min and max column number for the non-null cells in this row or null
get dimensions() {
var min = 0;
var max = 0;
_.each(this._cells, function (cell) {
if (cell.type != Enums.ValueType.Null) {
if (!min || (min > cell.col)) {
min = cell.col;
}
if (options && options.includeEmpty) {
var n = this._cells.length;
for (var i = 1; i <= n; i++) {
iteratee(this.getCell(i), i);
}
} else {
_.each(this._cells, function(cell, index) {
if (cell.type != Enums.ValueType.Null) {
iteratee(cell, index + 1);
}
});
if (max < cell.col) {
max = cell.col;
}
},
// return a sparse array of cell values
get values() {
var values = [];
_.each(this._cells, function(cell) {
if (cell.type != Enums.ValueType.Null) {
values[cell.col] = cell.value;
}
});
return values;
},
// set the values by contiguous or sparse array, or by key'd object literal
set values(value) {
var self = this;
// this operation is not additive - any prior cells are removed
this._cells = [];
if (!value) {
// empty row
} else if (value instanceof Array) {
var offset = 0;
if (value[0] != undefined) {
// contiguous array - start at column 1
offset = 1;
}
_.each(value, function(item, index) {
self._getCell({
address: colCache.encodeAddress(self._number, index+offset),
row: self._number,
col: index+offset
}).value = item;
});
} else {
// assume object with column keys
_.each(this._worksheet._keys, function(column, key) {
if (value[key] !== undefined) {
self._getCell({
address: colCache.encodeAddress(self._number, column.number),
row: self._number,
col: column.number
}).value = value[key];
}
});
}
});
return min > 0 ? {
min: min,
max: max
} : null;
},
// =========================================================================
// styles
_applyStyle: function (name, value) {
this.style[name] = value;
_.each(this._cells, function (cell) {
cell[name] = value;
});
return value;
},
get numFmt() {
return this.style.numFmt;
},
set numFmt(value) {
return this._applyStyle("numFmt", value);
},
get font() {
return this.style.font;
},
set font(value) {
return this._applyStyle("font", value);
},
get alignment() {
return this.style.alignment;
},
set alignment(value) {
return this._applyStyle("alignment", value);
},
get border() {
return this.style.border;
},
set border(value) {
return this._applyStyle("border", value);
},
get fill() {
return this.style.fill;
},
set fill(value) {
return this._applyStyle("fill", value);
},
get hidden() {
return !!this._hidden;
},
set hidden(value) {
return this._hidden = value;
},
// =========================================================================
get model() {
var cells = [];
var min = 0;
var max = 0;
_.each(this._cells, function (cell) {
if (cell.type != Enums.ValueType.Null) {
var cellModel = cell.model;
if (cellModel) {
if (!min || (min > cell.col)) {
min = cell.col;
}
if (max < cell.col) {
max = cell.col;
}
cells.push(cellModel);
}
},
// returns true if the row includes at least one cell with a value
get hasValues() {
return _.some(this._cells, function(cell) {
return cell.type != Enums.ValueType.Null;
});
},
// get the min and max column number for the non-null cells in this row or null
get dimensions() {
var min = 0;
var max = 0;
_.each(this._cells, function(cell) {
if (cell.type != Enums.ValueType.Null) {
if (!min || (min > cell.col)) { min = cell.col; }
if (max < cell.col) { max = cell.col; }
}
});
return min > 0 ?{
min: min,
max: max
} : null;
},
// =========================================================================
// styles
_applyStyle: function(name, value) {
this.style[name] = value;
_.each(this._cells, function(cell) {
cell[name] = value;
});
return value;
},
get numFmt() {
return this.style.numFmt;
},
set numFmt(value) {
return this._applyStyle("numFmt", value);
},
get font() {
return this.style.font;
},
set font(value) {
return this._applyStyle("font", value);
},
get alignment() {
return this.style.alignment;
},
set alignment(value) {
return this._applyStyle("alignment", value);
},
get border() {
return this.style.border;
},
set border(value) {
return this._applyStyle("border", value);
},
get fill() {
return this.style.fill;
},
set fill(value) {
return this._applyStyle("fill", value);
},
// =========================================================================
get model() {
var cells = [];
var min = 0;
var max = 0;
_.each(this._cells, function(cell) {
if (cell.type != Enums.ValueType.Null) {
var cellModel = cell.model;
if (cellModel) {
if (!min || (min > cell.col)) { min = cell.col; }
if (max < cell.col) { max = cell.col; }
cells.push(cellModel);
}
}
});
return (this.height || cells.length) ? {
cells: cells,
number: this.number,
min: min,
max: max,
height: this.height,
style: this.style
}: null;
},
set model(value) {
if (value.number != this._number) {
throw new Error("Invalid row number in model");
}
var self = this;
this._cells = [];
_.each(value.cells, function(cellModel) {
//console.log(JSON.stringify(colCache.decodeAddress(cellModel.address)) + " ==> " + JSON.stringify(cellModel));
switch(cellModel.type) {
case Cell.Types.Null:
case Cell.Types.Merge:
// special case - don't add these types
break;
default:
self._getCell(colCache.decodeAddress(cellModel.address)).model = cellModel;
break;
}
});
if (value.height) {
this.height = value.height;
} else {
delete this.height;
}
this.style = value.style || {};
}
});
return (this.height || cells.length) ? {
cells: cells,
number: this.number,
min: min,
max: max,
height: this.height,
style: this.style,
hidden: this.hidden
} : null;
},
set model(value) {
var clone = JSON.parse(JSON.stringify(value));
delete clone.cells;
if (value.number != this._number) {
throw new Error("Invalid row number in model");
}
}
var self = this;
this._cells = [];
_.each(value.cells, function (cellModel) {
//console.log(JSON.stringify(colCache.decodeAddress(cellModel.address)) + " ==> " + JSON.stringify(cellModel));
switch (cellModel.type) {
case Cell.Types.Null:
case Cell.Types.Merge:
// special case - don't add these types
break;
default:
self._getCell(colCache.decodeAddress(cellModel.address)).model = cellModel;
break;
}
});
if (value.height) {
this.height = value.height;
} else {
delete this.height;
}
this.hidden = value.hidden;
this.style = value.style || {};
}
};

2

lib/stream/xlsx/hyperlink-writer.js

@@ -102,3 +102,3 @@ /**

this.stream.write('</Relationships>');
},
}
}

8

lib/utils/dimensions.js

@@ -117,2 +117,10 @@ /**

},
expandRow: function(row) {
if (row) {
var dimensions = row.dimensions;
if (dimensions) {
this.expand(row.number, dimensions.min, row.number, dimensions.max);
}
}
},

@@ -119,0 +127,0 @@ get tl() {

4

lib/utils/shared-strings.js

@@ -44,2 +44,6 @@ /**

getString: function(index) {
return this._values[index];
},
add: function(value) {

@@ -46,0 +50,0 @@ value = utils.xmlEncode(value);

7

lib/utils/stream-buf.js

@@ -32,5 +32,2 @@ /**

var nop = function() {};
// =============================================================================

@@ -206,3 +203,3 @@ // data chunks - encapuslating incoming data

return promises.length ?
Promise.all(promises).then(nop) :
Promise.all(promises).then(utils.nop) :
Promise.resolve();

@@ -226,3 +223,3 @@ },

}
callback = callback || nop;
callback = callback || utils.nop;

@@ -229,0 +226,0 @@ // encapsulate data into a chunk

13

lib/utils/utils.js

@@ -98,2 +98,3 @@ /**

var utils = module.exports = {
nop: function() {},
promiseImmediate: function(value) {

@@ -166,2 +167,13 @@ var deferred = Promise.defer();

isDateFmt: function(fmt) {
if (!fmt) return false;
// must remove all chars inside quotes and []
fmt = fmt.replace(/[\[][^\]]*[\]]/g,"");
fmt = fmt.replace(/"[^"]*"/g,"");
// then check for date formatting chars
var result = fmt.match(/[ymdhMsb]+/) != null;
return result;
},
fs: {

@@ -176,3 +188,2 @@ exists: function(path) {

}
};

5

lib/workbook.js

@@ -60,3 +60,3 @@ /**

},
addWorksheet: function(name) {
addWorksheet: function(name, tabColor) {

@@ -69,3 +69,4 @@ var id = this.nextId;

name: name,
workbook: this
workbook: this,
tabColor: tabColor
});

@@ -72,0 +73,0 @@

616

lib/worksheet.js
/**
* Copyright (c) 2014 Guyon Roche
*
*
* Permission is hereby granted, free of charge, to any person obtaining a copy

@@ -10,6 +10,6 @@ * of this software and associated documentation files (the "Software"), to deal

* 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

@@ -22,3 +22,3 @@ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

* THE SOFTWARE.
*
*
*/

@@ -28,3 +28,2 @@ "use strict";

var _ = require("underscore");
var Promise = require("bluebird");

@@ -36,3 +35,3 @@ var utils = require("./utils/utils");

var Column = require("./column");
var Cell = require("./cell");
//var Cell = require("./cell");
var Enums = require("./enums");

@@ -47,318 +46,303 @@

var Worksheet = module.exports = function(options) {
options = options || {};
// in a workbook, each sheet will have a number
this.id = options.id;
// and a name
this.name = options.name || "Sheet" + this.id;
// rows allows access organised by row. Sparse array of arrays indexed by row-1, col
// Note: _rows is zero based. Must subtract 1 to go from cell.row to index
this._rows = [];
// column definitions
this._columns = null;
// column keys (addRow convenience): key ==> this._collumns index
this._keys = {};
// keep record of all merges
this._merges = {};
this._workbook = options.workbook;
}
options = options || {};
// in a workbook, each sheet will have a number
this.id = options.id;
// and a name
this.name = options.name || "Sheet" + this.id;
// rows allows access organised by row. Sparse array of arrays indexed by row-1, col
// Note: _rows is zero based. Must subtract 1 to go from cell.row to index
this._rows = [];
// column definitions
this._columns = null;
// column keys (addRow convenience): key ==> this._collumns index
this._keys = {};
// keep record of all merges
this._merges = {};
this._workbook = options.workbook;
//set the tab background color of the current
this.tabColor = options.tabColor;
};
Worksheet.prototype = {
// when you're done with this worksheet, call this to remove from workbook
destroy: function() {
this._workbook._removeWorksheet(this);
},
// Get the bounding range of the cells in this worksheet
get dimensions() {
var dimensions = new Dimensions();
_.each(this._rows, function(row, index) {
var rowDims = row.dimensions;
if (rowDims) {
dimensions.expand(row.number, rowDims.min, row.number, rowDims.max);
}
});
return dimensions;
},
// =========================================================================
// Columns
// get the current columns array.
get columns() {
return this._columns;
},
// set the columns from an array of column definitions.
// Note: any headers defined will overwrite existing values.
set columns(value) {
var self = this;
// calculate max header row count
this._headerRowCount = value.reduce(function(pv,cv) {
var headerCount = cv.header ? 1 : (cv.headers ? cv.headers.length : 0);
return Math.max(pv, headerCount)
}, 0);
// construct Column objects
var count = 1;
var _columns = this._columns = [];
_.each(value, function(defn) {
var column = new Column(self, count++, false);
_columns.push(column);
column.defn = defn;
});
},
// get a single column by col number. If it doesn't exist, it and any gaps before it
// are created.
getColumn: function(c) {
if (typeof c == "string"){
// if it matches a key'd column, return that
var col = this._keys[c];
if (col) return col;
// otherise, assume letter
c = colCache.l2n(c);
// when you're done with this worksheet, call this to remove from workbook
destroy: function() {
this._workbook._removeWorksheet(this);
},
// Get the bounding range of the cells in this worksheet
get dimensions() {
var dimensions = new Dimensions();
_.each(this._rows, function(row, index) {
var rowDims = row.dimensions;
if (rowDims) {
dimensions.expand(row.number, rowDims.min, row.number, rowDims.max);
}
});
return dimensions;
},
// =========================================================================
// Columns
// get the current columns array.
get columns() {
return this._columns;
},
// set the columns from an array of column definitions.
// Note: any headers defined will overwrite existing values.
set columns(value) {
var self = this;
// calculate max header row count
this._headerRowCount = value.reduce(function(pv,cv) {
var headerCount = cv.header ? 1 : (cv.headers ? cv.headers.length : 0);
return Math.max(pv, headerCount)
}, 0);
// construct Column objects
var count = 1;
var _columns = this._columns = [];
_.each(value, function(defn) {
var column = new Column(self, count++, false);
_columns.push(column);
column.defn = defn;
});
},
// get a single column by col number. If it doesn't exist, it and any gaps before it
// are created.
getColumn: function(c) {
if (typeof c == "string"){
// if it matches a key'd column, return that
var col = this._keys[c];
if (col) return col;
// otherise, assume letter
c = colCache.l2n(c);
}
if (!this._columns) { this._columns = []; }
if (c > this._columns.length) {
var n = this._columns.length + 1;
while (n <= c) {
this._columns.push(new Column(this, n++));
}
}
return this._columns[c-1];
},
// =========================================================================
// Rows
_commitRow: function() {
// nop - allows streaming reader to fill a document
},
get _nextRow() {
return this._rows.length + 1;
},
get lastRow() {
if (this._rows.length) {
return this._rows[this._rows.length-1];
} else {
return undefined;
}
},
// find a row (if exists) by row number
findRow: function(r) {
return this._rows[r-1];
},
// get a row by row number.
getRow: function(r) {
var row = this._rows[r-1];
if (!row) {
row = this._rows[r-1] = new Row(this, r);
}
return row;
},
addRow: function(value) {
var row = this.getRow(this._nextRow);
row.values = value;
return row;
},
// iterate over every row in the worksheet, including maybe empty rows
eachRow: function(options, iteratee) {
if (!iteratee) {
iteratee = options;
options = undefined;
}
if (options && options.includeEmpty) {
var n = this._rows.length;
for (var i = 1; i <= n; i++) {
iteratee(this.getRow(i), i);
}
} else {
this._rows.forEach(function(row) {
if (row.hasValues) {
iteratee(row, row.number);
}
if (!this._columns) { this._columns = []; }
if (c > this._columns.length) {
var n = this._columns.length + 1;
while (n <= c) {
this._columns.push(new Column(this, n++));
}
});
}
},
// return all rows as sparse array
getSheetValues: function() {
var rows = [];
this._rows.forEach(function(row) {
rows[row.number] = row.values;
});
return rows;
},
// =========================================================================
// Cells
// returns the cell at [r,c] or address given by r. If not found, return undefined
findCell: function(r, c) {
var address = colCache.getAddress(r, c);
var row = this._rows[address.row-1];
return row ? row.findCell(address.col) : undefined;
},
// return the cell at [r,c] or address given by r. If not found, create a new one.
getCell: function(r, c) {
var address = colCache.getAddress(r, c);
var row = this.getRow(address.row);
return row._getCell(address);
},
// =========================================================================
// Merge
// convert the range defined by ["tl:br"], [tl,br] or [t,l,b,r] into a single "merged" cell
mergeCells: function() {
var dimensions = new Dimensions(Array.prototype.slice.call(arguments, 0)); // convert arguments into Array
// check cells aren't already merged
_.each(this._merges, function(merge) {
if (merge.intersects(dimensions)) {
throw new Error("Cannot merge alreay merged cells");
}
});
// apply merge
var master = this.getCell(dimensions.top, dimensions.left);
for (var i = dimensions.top; i <= dimensions.bottom; i++) {
for (var j = dimensions.left; j <= dimensions.right; j++) {
// merge all but the master cell
if ((i > dimensions.top) || (j > dimensions.left)) {
this.getCell(i,j).merge(master);
}
return this._columns[c-1];
},
// =========================================================================
// Rows
_commitRow: function() {
// nop - allows streaming reader to fill a document
},
get _nextRow() {
return this._rows.length + 1;
},
get lastRow() {
if (this._rows.length) {
return this._rows[this._rows.length-1];
} else {
return undefined;
}
}
// index merge
this._merges[master.address] = dimensions;
},
_unMergeMaster: function(master) {
// master is always top left of a rectangle
var merge = this._merges[master.address];
if (merge) {
for (var i = merge.top; i <= merge.bottom; i++) {
for (var j = merge.left; j <= merge.right; j++) {
this.getCell(i,j).unmerge();
}
},
// find a row (if exists) by row number
findRow: function(r) {
return this._rows[r-1];
},
// get a row by row number.
getRow: function(r) {
var row = this._rows[r-1];
if (!row) {
row = this._rows[r-1] = new Row(this, r);
}
delete this._merges[master.address];
}
},
// scan the range defined by ["tl:br"], [tl,br] or [t,l,b,r] and if any cell is part of a merge,
// un-merge the group. Note this function can affect multiple merges and merge-blocks are
// atomic - either they're all merged or all un-merged.
unMergeCells: function() {
var dimensions = new Dimensions(Array.prototype.slice.call(arguments, 0)); // convert arguments into Array
// find any cells in that range and unmerge them
for (var i = dimensions.top; i <= dimensions.bottom; i++) {
for (var j = dimensions.left; j <= dimensions.right; j++) {
var cell = this.findCell(i,j);
if (cell) {
if (cell.type === Enums.ValueType.Merge) {
// this cell merges to another master
this._unMergeMaster(cell.master);
} else if (this._merges[cell.address]) {
// this cell is a master
this._unMergeMaster(cell);
}
}
return row;
},
addRow: function(value) {
var row = this.getRow(this._nextRow);
row.values = value;
return row;
},
// iterate over every row in the worksheet, including maybe empty rows
eachRow: function(options, iteratee) {
if (!iteratee) {
iteratee = options;
options = undefined;
}
if (options && options.includeEmpty) {
var n = this._rows.length;
for (var i = 1; i <= n; i++) {
iteratee(this.getRow(i), i);
}
} else {
this._rows.forEach(function(row) {
if (row.hasValues) {
iteratee(row, row.number);
}
});
}
},
// return all rows as sparse array
getSheetValues: function() {
var rows = [];
this._rows.forEach(function(row) {
rows[row.number] = row.values;
});
return rows;
},
// =========================================================================
// Cells
// returns the cell at [r,c] or address given by r. If not found, return undefined
findCell: function(r, c) {
var address = colCache.getAddress(r, c);
var row = this._rows[address.row-1];
return row ? row.findCell(address.col) : undefined;
},
// return the cell at [r,c] or address given by r. If not found, create a new one.
getCell: function(r, c) {
var address = colCache.getAddress(r, c);
var row = this.getRow(address.row);
return row._getCell(address);
},
// =========================================================================
// Merge
// convert the range defined by ["tl:br"], [tl,br] or [t,l,b,r] into a single "merged" cell
mergeCells: function() {
var dimensions = new Dimensions(Array.prototype.slice.call(arguments, 0)); // convert arguments into Array
// check cells aren't already merged
_.each(this._merges, function(merge) {
if (merge.intersects(dimensions)) {
throw new Error("Cannot merge alreay merged cells");
}
});
// apply merge
var master = this.getCell(dimensions.top, dimensions.left);
for (var i = dimensions.top; i <= dimensions.bottom; i++) {
for (var j = dimensions.left; j <= dimensions.right; j++) {
// merge all but the master cell
if ((i > dimensions.top) || (j > dimensions.left)) {
this.getCell(i,j).merge(master);
}
}
}
// index merge
this._merges[master.address] = dimensions;
},
_unMergeMaster: function(master) {
// master is always top left of a rectangle
var merge = this._merges[master.address];
if (merge) {
for (var i = merge.top; i <= merge.bottom; i++) {
for (var j = merge.left; j <= merge.right; j++) {
this.getCell(i,j).unmerge();
}
}
delete this._merges[master.address];
}
},
// scan the range defined by ["tl:br"], [tl,br] or [t,l,b,r] and if any cell is part of a merge,
// un-merge the group. Note this function can affect multiple merges and merge-blocks are
// atomic - either they're all merged or all un-merged.
unMergeCells: function() {
var dimensions = new Dimensions(Array.prototype.slice.call(arguments, 0)); // convert arguments into Array
// find any cells in that range and unmerge them
for (var i = dimensions.top; i <= dimensions.bottom; i++) {
for (var j = dimensions.left; j <= dimensions.right; j++) {
var cell = this.findCell(i,j);
if (cell) {
if (cell.type === Enums.ValueType.Merge) {
// this cell merges to another master
this._unMergeMaster(cell.master);
} else if (this._merges[cell.address]) {
// this cell is a master
this._unMergeMaster(cell);
}
}
}
}
},
// ===========================================================================
// Model
get model() {
var self = this;
var model = {
id: this.id,
name: this.name
};
// =================================================
// columns
model.cols = Column.toModel(this.columns);
// ==========================================================
// Rows
var rows = model.rows = [];
var dimensions = model.dimensions = new Dimensions();
_.each(this._rows, function(row, index) {
var rowModel = row.model;
if (rowModel) {
dimensions.expand(rowModel.number, rowModel.min, rowModel.number, rowModel.max);
rows.push(rowModel);
}
});
// ==========================================================
// Merges
model.merges = [];
_.each(this._merges, function(merge) {
model.merges.push(merge.range);
});
return model;
},
_parseCols: function(model) {
if (model.cols) {
this._columns = [];
var count = 1;
var index = 0;
while (index < model.cols.length) {
var col = model.cols[index++];
while (count < col.min) {
this._columns.push(new Column(this, count++));
}
while (count <= col.max) {
this._columns.push(new Column(this, count++, col));
}
}
} else {
this._columns = null;
}
},
_parseRows: function(model) {
var self = this;
this._rows = [];
_.each(model.rows, function(rowModel) {
var row = new Row(self, rowModel.number);
self._rows[row.number-1] = row;
row.model = rowModel;
});
},
_parseMergeCells: function(model) {
var self = this;
_.each(model.merges, function(merge) {
self.mergeCells(merge);
});
},
set model(value) {
this.name = value.name;
this._parseCols(value);
this._parseRows(value);
this._parseMergeCells(value);
}
}
}
},
// ===========================================================================
// Model
get model() {
var self = this;
var model = {
id: this.id,
name: utils.xmlEncode(this.name),
tabColor: this.tabColor
};
// =================================================
// columns
model.cols = Column.toModel(this.columns);
// ==========================================================
// Rows
var rows = model.rows = [];
var dimensions = model.dimensions = new Dimensions();
_.each(this._rows, function(row, index) {
var rowModel = row.model;
if (rowModel) {
dimensions.expand(rowModel.number, rowModel.min, rowModel.number, rowModel.max);
rows.push(rowModel);
}
});
// ==========================================================
// Merges
model.merges = [];
_.each(this._merges, function(merge) {
model.merges.push(merge.range);
});
return model;
},
_parseRows: function(model) {
var self = this;
this._rows = [];
_.each(model.rows, function(rowModel) {
var row = new Row(self, rowModel.number);
self._rows[row.number-1] = row;
row.model = rowModel;
});
},
_parseMergeCells: function(model) {
var self = this;
_.each(model.merges, function(merge) {
self.mergeCells(merge);
});
},
set model(value) {
this.name = utils.xmlDecode(value.name);
this._columns = Column.fromModel(this, value.cols);
this._parseRows(value);
this._parseMergeCells(value);
}
};

27

lib/xlsx/stylemanager.js

@@ -25,2 +25,3 @@ /**

var events = require("events");
var _ = require("underscore");

@@ -38,3 +39,5 @@ var Promise = require("bluebird");

var Enums = require("../enums");
var AutoDrain = require("../utils/auto-drain");
// =============================================================================
// StyleManager is used to generate and parse the styles.xml file

@@ -45,2 +48,3 @@ // it manages the collections of fonts, number formats, alignments, etc

this.stylesIndex = {};
this.stylesMap = global.WeakMap && new WeakMap();

@@ -82,3 +86,3 @@ this.models = []; // styleId -> model

StyleManager.prototype = {
utils.inherits(StyleManager, events.EventEmitter, {
// =========================================================================

@@ -235,3 +239,8 @@ // Public Interface

}
// Note: a WeakMap --> styleId would be very useful here!
// if we have seen this style object before, assume it has the same styleId
if (this.weakMap && this.weakMap.has(model)) {
return this.weakMap.get(model);
}
var style = new Style();

@@ -269,3 +278,7 @@ cellType = cellType || Enums.ValueType.Number;

return this._addStyle(style);
var styleId = this._addStyle(style);
if (this.weakMap) {
this.weakMap.set(model, styleId);
}
return styleId;
},

@@ -392,3 +405,3 @@

// =========================================================================
};
});

@@ -418,3 +431,3 @@ // the stylemanager mock acts like StyleManager except that it always returns 0 or {}

StyleManager.Mock.prototype = {
utils.inherits(StyleManager.Mock, events.EventEmitter, {
// =========================================================================

@@ -428,3 +441,3 @@ // Public Interface

parse: function(stream) {
entry.autodrain();
stream.autodrain();
return Promise.resolve();

@@ -460,2 +473,2 @@ },

}
};
});

2372

lib/xlsx/xlsx.js
/**
* Copyright (c) 2014 Guyon Roche
*
*
* Permission is hereby granted, free of charge, to any person obtaining a copy

@@ -10,6 +10,6 @@ * of this software and associated documentation files (the "Software"), to deal

* 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

@@ -22,3 +22,3 @@ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

* THE SOFTWARE.
*
*
*/

@@ -42,4 +42,4 @@ "use strict";

var XLSX = module.exports = function(workbook) {
this.workbook = workbook;
var XLSX = module.exports = function (workbook) {
this.workbook = workbook;
}

@@ -53,1205 +53,1209 @@

// Shared Strings
//
//createInputStream: function() {
// var self = this;
// var parser = Sax.createStream(true, {});
// var inT = false;
// var t = "";
// var count = 0;
// parser.on('opentag', function(node) {
// if (node.name == "t") {
// inT = true;
// t = "";
// }
// });
// parser.on('closetag', function (name) {
// if (inT && (name == "t")) {
// var entry = self.getEntryAt(count++);
// entry._value = t;
// self.hash[t] = entry;
// inT = false;
// }
// });
// parser.on('text', function (text) {
// if (inT) {
// t += text;
// }
// });
//
// return parser;
//},
//
//read: function(stream) {
// var deferred = Promise.defer();
// var parser = this.createInputStream();
// parser.on('end', function (name) {
// deferred.resolve();
// });
// parser.on('error', function (err) {
// deferred.reject(err);
// });
// stream.pipe(parser);
// return deferred.promise;
//},
//
//toBuffer: function() {
// var self = this;
// return utils.fetchTemplate("./xlsx/sharedStrings.xml")
// .then(function(template){
// var model = {
// count: self.totalRefs,
// uniqueCount: self.index.length,
// texts: self.index.map(function(item) { return item.value; })
// };
// return template(model);
// });
//},
//write: function(stream) {
// return this.toBuffer()
// .then(function(data) {
// var deferred = Promise.defer();
// stream.write(data, function(err){
// deferred.resolve(stream);
// });
// return deferred.promise;
// });
//},
//
//createInputStream: function() {
// var self = this;
// var parser = Sax.createStream(true, {});
// var inT = false;
// var t = "";
// var count = 0;
// parser.on('opentag', function(node) {
// if (node.name == "t") {
// inT = true;
// t = "";
// }
// });
// parser.on('closetag', function (name) {
// if (inT && (name == "t")) {
// var entry = self.getEntryAt(count++);
// entry._value = t;
// self.hash[t] = entry;
// inT = false;
// }
// });
// parser.on('text', function (text) {
// if (inT) {
// t += text;
// }
// });
//
// return parser;
//},
//
//read: function(stream) {
// var deferred = Promise.defer();
// var parser = this.createInputStream();
// parser.on('end', function (name) {
// deferred.resolve();
// });
// parser.on('error', function (err) {
// deferred.reject(err);
// });
// stream.pipe(parser);
// return deferred.promise;
//},
//
//toBuffer: function() {
// var self = this;
// return utils.fetchTemplate("./xlsx/sharedStrings.xml")
// .then(function(template){
// var model = {
// count: self.totalRefs,
// uniqueCount: self.index.length,
// texts: self.index.map(function(item) { return item.value; })
// };
// return template(model);
// });
//},
//write: function(stream) {
// return this.toBuffer()
// .then(function(data) {
// var deferred = Promise.defer();
// stream.write(data, function(err){
// deferred.resolve(stream);
// });
// return deferred.promise;
// });
//},
//=======================================================================
// Relationships
//idTorId: function(id) {
// return "rId" + (id + 1);
//},
//rIdToId: function(rId) {
// return parseInt(rId.replace("rId","")) - 1;
//},
//
//createInputStream: function() {
// var self = this;
// this.index = [];
// var parser = Sax.createStream(true, {});
// parser.on('opentag', function(node) {
// if (node.name == "Relationship") {
// var id = Relationship.rIdToId(node.attributes.Id);
// switch (node.attributes.Type) {
// case OfficeDocumentRelationship.Type:
// self.index[id] = new OfficeDocumentRelationship(self, id, node.attributes.Target);
// break;
// case WorksheetRelationship.Type:
// var index = parseInt(node.attributes.Target.replace("worksheets/sheet", "").replace(".xml", ""));
// self.index[id] = new WorksheetRelationship(self, id, index);
// break;
// case CalcChainRelationship.Type:
// self.index[id] = new CalcChainRelationship(self, id);
// break;
// case SharedStringsRelationship.Type:
// self.index[id] = new SharedStringsRelationship(self, id);
// break;
// case StylesRelationship.Type:
// self.index[id] = new StylesRelationship(self, id);
// break;
// case ThemeRelationship.Type:
// var index = parseInt(node.attributes.Target.replace("theme/theme", "").replace(".xml", ""));
// self.index[id] = new ThemeRelationship(self, id, index);
// break;
// case HyperlinkRelationship.Type:
// self.index[id] = new HyperlinkRelationship(self, id, node.attributes.Target);
// break;
// default:
// // ignore this?
// break;
// }
// }
// });
// return parser;
//},
//read: function(stream) {
// var deferred = Promise.defer();
//
// var parser = this.createInputStream();
// parser.on('end', function(name) {
// deferred.resolve();
// });
// parser.on('error', function (err) {
// deferred.reject(err);
// });
// stream.pipe(parser);
//
// return deferred.promise;
//},
//
//
//toBuffer: function() {
// var self = this;
// return utils.fetchTemplate("./xlsx/.rels")
// .then(function(template){
// var model = {
// relationships: self.index
// };
// return template(model);
// });
//},
//write: function(stream) {
// return this.toBuffer()
// .then(function(data) {
// stream.write(data);
// return stream;
// });
//},
//idTorId: function(id) {
// return "rId" + (id + 1);
//},
//rIdToId: function(rId) {
// return parseInt(rId.replace("rId","")) - 1;
//},
//
//createInputStream: function() {
// var self = this;
// this.index = [];
// var parser = Sax.createStream(true, {});
// parser.on('opentag', function(node) {
// if (node.name == "Relationship") {
// var id = Relationship.rIdToId(node.attributes.Id);
// switch (node.attributes.Type) {
// case OfficeDocumentRelationship.Type:
// self.index[id] = new OfficeDocumentRelationship(self, id, node.attributes.Target);
// break;
// case WorksheetRelationship.Type:
// var index = parseInt(node.attributes.Target.replace("worksheets/sheet", "").replace(".xml", ""));
// self.index[id] = new WorksheetRelationship(self, id, index);
// break;
// case CalcChainRelationship.Type:
// self.index[id] = new CalcChainRelationship(self, id);
// break;
// case SharedStringsRelationship.Type:
// self.index[id] = new SharedStringsRelationship(self, id);
// break;
// case StylesRelationship.Type:
// self.index[id] = new StylesRelationship(self, id);
// break;
// case ThemeRelationship.Type:
// var index = parseInt(node.attributes.Target.replace("theme/theme", "").replace(".xml", ""));
// self.index[id] = new ThemeRelationship(self, id, index);
// break;
// case HyperlinkRelationship.Type:
// self.index[id] = new HyperlinkRelationship(self, id, node.attributes.Target);
// break;
// default:
// // ignore this?
// break;
// }
// }
// });
// return parser;
//},
//read: function(stream) {
// var deferred = Promise.defer();
//
// var parser = this.createInputStream();
// parser.on('end', function(name) {
// deferred.resolve();
// });
// parser.on('error', function (err) {
// deferred.reject(err);
// });
// stream.pipe(parser);
//
// return deferred.promise;
//},
//
//
//toBuffer: function() {
// var self = this;
// return utils.fetchTemplate("./xlsx/.rels")
// .then(function(template){
// var model = {
// relationships: self.index
// };
// return template(model);
// });
//},
//write: function(stream) {
// return this.toBuffer()
// .then(function(data) {
// stream.write(data);
// return stream;
// });
//},
//==================================================================================
// Worksheet
// ===================
// Read
//_read: function(stream) {
// // <cols><col min="1" max="1" width="25" style="?" />
// // <sheetData><row><c r="A1" s="1" t="s"><f>A2</f><v>value</v></c>
// // <mergeCells><mergeCell ref="A1:B2" />
// // <hyperlinks><hyperlink ref="A1" r:id="rId1" />
// var deferred = Promise.defer();
//
// var self = this;
// var parser = Sax.createStream(true, {});
//
// var cols = [];
//
// var hyperlinks = [];
//
// var current;
// var c; // cell stuff
// parser.on('opentag', function(node) {
// switch (node.name) {
// case "col":
// cols.push({
// // s: node.attributes.s
// min: parseInt(node.attributes.min),
// max: parseInt(node.attributes.max),
// width: parseFloat(node.attributes.width)
// });
// break;
// case "c":
// c = {
// ref: node.attributes.r,
// s: parseInt(node.attributes.s),
// t: node.attributes.t
// };
// break;
// case "f":
// current = c.f = { text: "" };
// break;
// case "v":
// current = c.v = { text: "" };
// break;
// case "mergeCell":
// self.mergeCells(node.attributes.ref);
// break;
// case "hyperlink":
// hyperlinks.push({
// ref: node.attributes.ref,
// rId: node.attributes["r:id"]
// });
// break;
// }
// });
// parser.on('text', function (text) {
// if (current) {
// current.text += text;
// }
// });
// parser.on('closetag', function(name) {
// switch (name) {
// case "c":
// var cell = self.getCell(c.ref);
// // Note: merged cells (if they've been merged already will not have v or f nodes)
// if (c.f) {
// c.v = c.v || {};
// if (c.t == "str") {
// cell.value = { formula: c.f.text, result: c.v.text };
// } else if (c.s == 1) {
// cell.value = { formula: c.f.text, result: utils.excelToDate(parseFloat(c.v.text)) };
// } else {
// cell.value = { formula: c.f.text, result: parseFloat(c.v.text) };
// }
// } else if (c.v) {
// if (c.t == "s") {
// var index = parseInt(c.v.text);
// var entry = self.sharedStrings.getEntryAt(index);
// cell.value = entry;
// } else if (c.s == 1) {
// cell.value = utils.excelToDate(parseFloat(c.v.text));
// } else {
// cell.value = parseFloat(c.v.text);
// }
// }
// break;
// case "cols":
// // define the columns (widths at least)
// if (cols.length) {
// var index = 1;
// var columns = self._columns = [];
// _.each(cols, function(column) {
// while (index++ < column.min) {
// columns.push(new Column());
// }
// for (index = column.min; index <= column.max; index++) {
// columns.push(new Column({
// width: column.width
// }));
// }
// });
// }
// break;
// case "worksheet":
// // upgrade all hyperlink cells
// _.each(hyperlinks, function(hyperlink) {
// // Note: special case - don't addRef here since refCount will already be 1
// var relationship = self.relationships.getByRId(hyperlink.rId);
//
// var cell = self.getCell(hyperlink.ref);
// cell._upgradeToHyperlink(relationship);
// });
//
// // all good!
// deferred.resolve();
// break;
// }
// });
// parser.on('error', function (err) {
// deferred.reject(err);
// });
// stream.pipe(parser);
//
// return deferred.promise;
//},
//read: function(stream, relStream) {
// var self = this;
// if (relStream) {
// return this.relationships.read(relStream)
// .then(function() {
// return self._read(stream);
// });
// } else {
// return self._read(stream);
// }
//},
//
//// =================
//// Write
//toBuffer: function() {
// var self = this;
// return utils.fetchTemplate("./xlsx/sheet.xml")
// .then(function(template){
// var model = {};
// self.buildCols(model);
// self.buildRows(model);
// self.buildMergeCells(model);
// self.buildHyperlinks(model);
// return template(model);
// });
//},
//write: function(stream) {
// return this.toBuffer()
// .then(function(data) {
// stream.write(data);
// return stream;
// });
//}
//
//==================================================================================
// Worksheet
// ===================
// Read
//_read: function(stream) {
// // <cols><col min="1" max="1" width="25" style="?" />
// // <sheetData><row><c r="A1" s="1" t="s"><f>A2</f><v>value</v></c>
// // <mergeCells><mergeCell ref="A1:B2" />
// // <hyperlinks><hyperlink ref="A1" r:id="rId1" />
// var deferred = Promise.defer();
//
// var self = this;
// var parser = Sax.createStream(true, {});
//
// var cols = [];
//
// var hyperlinks = [];
//
// var current;
// var c; // cell stuff
// parser.on('opentag', function(node) {
// switch (node.name) {
// case "col":
// cols.push({
// // s: node.attributes.s
// min: parseInt(node.attributes.min),
// max: parseInt(node.attributes.max),
// width: parseFloat(node.attributes.width)
// });
// break;
// case "c":
// c = {
// ref: node.attributes.r,
// s: parseInt(node.attributes.s),
// t: node.attributes.t
// };
// break;
// case "f":
// current = c.f = { text: "" };
// break;
// case "v":
// current = c.v = { text: "" };
// break;
// case "mergeCell":
// self.mergeCells(node.attributes.ref);
// break;
// case "hyperlink":
// hyperlinks.push({
// ref: node.attributes.ref,
// rId: node.attributes["r:id"]
// });
// break;
// }
// });
// parser.on('text', function (text) {
// if (current) {
// current.text += text;
// }
// });
// parser.on('closetag', function(name) {
// switch (name) {
// case "c":
// var cell = self.getCell(c.ref);
// // Note: merged cells (if they've been merged already will not have v or f nodes)
// if (c.f) {
// c.v = c.v || {};
// if (c.t == "str") {
// cell.value = { formula: c.f.text, result: c.v.text };
// } else if (c.s == 1) {
// cell.value = { formula: c.f.text, result: utils.excelToDate(parseFloat(c.v.text)) };
// } else {
// cell.value = { formula: c.f.text, result: parseFloat(c.v.text) };
// }
// } else if (c.v) {
// if (c.t == "s") {
// var index = parseInt(c.v.text);
// var entry = self.sharedStrings.getEntryAt(index);
// cell.value = entry;
// } else if (c.s == 1) {
// cell.value = utils.excelToDate(parseFloat(c.v.text));
// } else {
// cell.value = parseFloat(c.v.text);
// }
// }
// break;
// case "cols":
// // define the columns (widths at least)
// if (cols.length) {
// var index = 1;
// var columns = self._columns = [];
// _.each(cols, function(column) {
// while (index++ < column.min) {
// columns.push(new Column());
// }
// for (index = column.min; index <= column.max; index++) {
// columns.push(new Column({
// width: column.width
// }));
// }
// });
// }
// break;
// case "worksheet":
// // upgrade all hyperlink cells
// _.each(hyperlinks, function(hyperlink) {
// // Note: special case - don't addRef here since refCount will already be 1
// var relationship = self.relationships.getByRId(hyperlink.rId);
//
// var cell = self.getCell(hyperlink.ref);
// cell._upgradeToHyperlink(relationship);
// });
//
// // all good!
// deferred.resolve();
// break;
// }
// });
// parser.on('error', function (err) {
// deferred.reject(err);
// });
// stream.pipe(parser);
//
// return deferred.promise;
//},
//read: function(stream, relStream) {
// var self = this;
// if (relStream) {
// return this.relationships.read(relStream)
// .then(function() {
// return self._read(stream);
// });
// } else {
// return self._read(stream);
// }
//},
//
//// =================
//// Write
//toBuffer: function() {
// var self = this;
// return utils.fetchTemplate("./xlsx/sheet.xml")
// .then(function(template){
// var model = {};
// self.buildCols(model);
// self.buildRows(model);
// self.buildMergeCells(model);
// self.buildHyperlinks(model);
// return template(model);
// });
//},
//write: function(stream) {
// return this.toBuffer()
// .then(function(data) {
// stream.write(data);
// return stream;
// });
//}
//
// ===============================================================================
// Workbook
// =========================================================================
// Read
// =========================================================================
// Read
readFile: function(filename) {
var self = this;
var stream;
return utils.fs.exists(filename)
.then(function(exists) {
if (!exists) {
throw new Error("File not found: " + filename);
}
stream = fs.createReadStream(filename);
return self.read(stream)
})
.then(function(workbook) {
stream.close();
return workbook;
});
},
parseRels: function(stream) {
var deferred = Promise.defer();
var relationships = {};
var parser = Sax.createStream(true, {});
parser.on('opentag', function(node) {
var rId = node.attributes.Id;
if (node.name == "Relationship") {
switch (node.attributes.Type) {
case XLSX.RelType.OfficeDocument:
relationships[rId] = {
type: Enums.RelationshipType.OfficeDocument,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.Worksheet:
relationships[rId] = {
type: Enums.RelationshipType.Worksheet,
rId: rId,
target: node.attributes.Target,
index: parseInt(node.attributes.Target.replace("worksheets/sheet", "").replace(".xml", ""))
};
break;
case XLSX.RelType.CalcChain:
relationships[rId] = {
type: Enums.RelationshipType.CalcChain,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.SharedStrings:
relationships[rId] = {
type: Enums.RelationshipType.SharedStrings,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.Styles:
relationships[rId] = {
type: Enums.RelationshipType.Styles,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.Theme:
relationships[rId] = {
type: Enums.RelationshipType.Theme,
rId: rId,
target: node.attributes.Target,
index: parseInt(node.attributes.Target.replace("theme/theme", "").replace(".xml", ""))
};
break;
case XLSX.RelType.Hyperlink:
relationships[rId] = {
type: Enums.RelationshipType.Styles,
rId: rId,
target: node.attributes.Target,
targetMode: node.attributes.TargetMode
};
break;
default:
// ignore this?
break;
}
readFile: function (filename) {
var self = this;
var stream;
return utils.fs.exists(filename)
.then(function (exists) {
if (!exists) {
throw new Error("File not found: " + filename);
}
stream = fs.createReadStream(filename);
return self.read(stream)
})
.then(function (workbook) {
stream.close();
return workbook;
});
},
parseRels: function (stream) {
var deferred = Promise.defer();
var relationships = {};
var parser = Sax.createStream(true, {});
parser.on('opentag', function (node) {
if (node.name == "Relationship") {
var rId = node.attributes.Id;
switch (node.attributes.Type) {
case XLSX.RelType.OfficeDocument:
relationships[rId] = {
type: Enums.RelationshipType.OfficeDocument,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.Worksheet:
relationships[rId] = {
type: Enums.RelationshipType.Worksheet,
rId: rId,
target: node.attributes.Target,
index: parseInt(node.attributes.Target.replace("worksheets/sheet", "").replace(".xml", ""))
};
break;
case XLSX.RelType.CalcChain:
relationships[rId] = {
type: Enums.RelationshipType.CalcChain,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.SharedStrings:
relationships[rId] = {
type: Enums.RelationshipType.SharedStrings,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.Styles:
relationships[rId] = {
type: Enums.RelationshipType.Styles,
rId: rId,
target: node.attributes.Target
};
break;
case XLSX.RelType.Theme:
relationships[rId] = {
type: Enums.RelationshipType.Theme,
rId: rId,
target: node.attributes.Target,
index: parseInt(node.attributes.Target.replace("theme/theme", "").replace(".xml", ""))
};
break;
case XLSX.RelType.Hyperlink:
relationships[rId] = {
type: Enums.RelationshipType.Styles,
rId: rId,
target: node.attributes.Target,
targetMode: node.attributes.TargetMode
};
break;
default:
// ignore this?
break;
}
}
});
parser.on("end", function () {
deferred.resolve(relationships);
});
parser.on("error", function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
parseWorkbook: function (stream) {
var deferred = Promise.defer();
var self = this;
var parser = Sax.createStream(true, {});
var sheetDefs = {};
parser.on('opentag', function (node) {
if (node.name == "sheet") {
// add sheet to sheetDefs indexed by rId
// <sheet name="<%=worksheet.name%>" sheetId="<%=worksheet.id%>" r:id="<%=worksheet.rId%>"/>
var name = node.attributes.name;
var id = parseInt(node.attributes.sheetId);
var rId = node.attributes["r:id"];
sheetDefs[rId] = {
name: name,
id: id,
rId: rId
};
}
});
parser.on('end', function () {
deferred.resolve(sheetDefs);
});
parser.on('error', function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
parseSharedStrings: function (stream) {
var deferred = Promise.defer();
var parser = Sax.createStream(true, {});
var t = null;
var sharedStrings = [];
parser.on('opentag', function (node) {
if (node.name == "t") {
t = "";
}
});
parser.on('closetag', function (name) {
if ((t != null) && (name == "t")) {
sharedStrings.push(t);
t = null;
}
});
parser.on('text', function (text) {
if (t != null) {
t += text;
}
});
parser.on('end', function () {
deferred.resolve(sharedStrings);
});
parser.on('error', function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
parseWorksheet: function (stream) {
// <cols><col min="1" max="1" width="25" style="?" />
// <sheetData><row><c r="A1" s="1" t="s"><f>A2</f><v>value</v></c>
// <mergeCells><mergeCell ref="A1:B2" />
// <hyperlinks><hyperlink ref="A1" r:id="rId1" />
var deferred = Promise.defer();
var parser = Sax.createStream(true, {});
var model = {
cols: [],
rows: [],
hyperlinks: [],
merges: []
};
function parseBool(attribute) {
return !!attribute;
}
var current;
var c; // cell stuff
parser.on('opentag', function (node) {
switch (node.name) {
case "col":
model.cols.push({
min: parseInt(node.attributes.min),
max: parseInt(node.attributes.max),
width: parseFloat(node.attributes.width),
styleId: parseInt(node.attributes.style || "0"),
hidden: parseBool(node.attributes.hidden)
});
break;
case "row":
var r = parseInt(node.attributes.r);
var row = {
number: r,
cells: [],
min: 0,
max: 0,
hidden: parseBool(node.attributes.hidden)
};
if (node.attributes.ht) {
row.height = parseFloat(node.attributes.ht);
}
if (node.attributes.s) {
row.styleId = parseInt(node.attributes.s);
}
model.rows[r] = row;
break;
case "c":
c = {
ref: node.attributes.r,
s: parseInt(node.attributes.s),
t: node.attributes.t
};
break;
case "f":
current = c.f = {text: ""};
break;
case "v":
current = c.v = {text: ""};
break;
case "mergeCell":
model.merges.push(node.attributes.ref);
break;
case "hyperlink":
model.hyperlinks.push({
ref: node.attributes.ref,
rId: node.attributes["r:id"]
});
break;
}
});
parser.on('text', function (text) {
if (current) {
current.text += text;
}
});
parser.on('closetag', function (name) {
switch (name) {
case "c":
var address = colCache.decodeAddress(c.ref);
var cell = {
address: c.ref,
s: c.s
};
// Note: merged cells (if they've been merged already will not have v or f nodes)
if (c.f) {
cell.type = Enums.ValueType.Formula;
cell.formula = c.f.text;
if (c.v) {
if (c.t == "str") {
cell.result = utils.xmlDecode(c.v.text);
} else {
cell.result = parseFloat(c.v.text);
}
}
});
parser.on("end", function() {
deferred.resolve(relationships);
});
parser.on("error", function(error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
parseWorkbook: function(stream) {
var deferred = Promise.defer();
var self = this;
var parser = Sax.createStream(true, {});
var sheetDefs = {};
parser.on('opentag', function(node) {
if (node.name == "sheet") {
// add sheet to sheetDefs indexed by rId
// <sheet name="<%=worksheet.name%>" sheetId="<%=worksheet.id%>" r:id="<%=worksheet.rId%>"/>
var name = node.attributes.name;
var id = parseInt(node.attributes.sheetId);
var rId = node.attributes["r:id"];
sheetDefs[rId] = {
name: name,
id: id,
rId: rId
};
} else if (c.v) {
switch (c.t) {
case "s":
cell.type = Enums.ValueType.String;
cell.value = parseInt(c.v.text);
break;
case "str":
cell.type = Enums.ValueType.String;
cell.value = utils.xmlDecode(c.v.text);
break;
default:
cell.type = Enums.ValueType.Number;
cell.value = parseFloat(c.v.text);
break;
}
});
parser.on('end', function() {
deferred.resolve(sheetDefs);
});
parser.on('error', function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
parseSharedStrings: function(stream) {
var deferred = Promise.defer();
var parser = Sax.createStream(true, {});
var t = null;
var sharedStrings = [];
parser.on('opentag', function(node) {
if (node.name == "t") {
t = "";
} else {
cell.type = Enums.ValueType.Merge;
}
var row = model.rows[address.row];
row.cells[address.col] = cell;
row.min = Math.min(row.min, address.col) || address.col;
row.max = Math.max(row.max, address.col);
break;
}
});
parser.on('end', function () {
// if cols empty then can remove it from model
if (!model.cols.length) {
delete model.cols;
}
deferred.resolve(model);
});
parser.on('error', function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
prepareModel: function (model) {
// reconcile sheet ids, rIds and names
_.each(model.sheetDefs, function (sheetDef) {
var rel = model.workbookRels[sheetDef.rId];
var worksheet = model.worksheetHash["xl/" + rel.target];
worksheet.name = sheetDef.name;
worksheet.id = sheetDef.id;
});
// reconcile worksheets
_.each(model.worksheets, function (worksheet, sheetNo) {
// reconcile column styles
_.each(worksheet.cols, function (col) {
if (col.styleId) {
col.style = model.styles.getStyleModel(col.styleId);
}
});
// reconcile merge list with merge cells
_.each(worksheet.merges, function (merge) {
var dimensions = colCache.decode(merge);
for (var i = dimensions.top; i <= dimensions.bottom; i++) {
var row = worksheet.rows[i];
for (var j = dimensions.left; j <= dimensions.right; j++) {
var cell = row.cells[j];
if (!cell) {
// nulls are not included in document - so if master cell has no value - add a null one here
row.cells[j] = {
type: Enums.ValueType.Null,
address: colCache.encodeAddress(i, j)
};
} else if (cell.type == Enums.ValueType.Merge) {
cell.master = dimensions.tl;
}
});
parser.on('closetag', function (name) {
if ((t != null) && (name == "t")) {
sharedStrings.push(t);
t = null;
}
});
parser.on('text', function (text) {
if (t != null) {
t += text;
}
});
parser.on('end', function() {
deferred.resolve(sharedStrings);
});
parser.on('error', function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
parseWorksheet: function(stream) {
// <cols><col min="1" max="1" width="25" style="?" />
// <sheetData><row><c r="A1" s="1" t="s"><f>A2</f><v>value</v></c>
// <mergeCells><mergeCell ref="A1:B2" />
// <hyperlinks><hyperlink ref="A1" r:id="rId1" />
var deferred = Promise.defer();
var parser = Sax.createStream(true, {});
var model = {
cols: [],
rows: [],
hyperlinks: [],
merges: []
}
}
var current;
var c; // cell stuff
parser.on('opentag', function(node) {
switch (node.name) {
case "col":
model.cols.push({
// s: node.attributes.s
min: parseInt(node.attributes.min),
max: parseInt(node.attributes.max),
width: parseFloat(node.attributes.width),
styleId: parseInt(node.attributes.style || "0")
});
break;
case "row":
var r = parseInt(node.attributes.r);
var row = {
number: r,
cells: [],
min: 0,
max: 0
};
if (node.attributes.ht) {
row.height = parseFloat(node.attributes.ht);
}
if (node.attributes.s) {
row.styleId = parseInt(node.attributes.s);
}
model.rows[r] = row;
break;
case "c":
c = {
ref: node.attributes.r,
s: parseInt(node.attributes.s),
t: node.attributes.t
};
break;
case "f":
current = c.f = { text: "" };
break;
case "v":
current = c.v = { text: "" };
break;
case "mergeCell":
model.merges.push(node.attributes.ref);
break;
case "hyperlink":
model.hyperlinks.push({
ref: node.attributes.ref,
rId: node.attributes["r:id"]
});
break;
}
});
// if worksheet rels, merge them in
var relationships = model.worksheetRels[worksheet.sheetNo];
if (relationships) {
var hyperlinks = {};
_.each(relationships, function (relationship) {
hyperlinks[relationship.rId] = relationship.target;
});
parser.on('text', function (text) {
if (current) {
current.text += text;
}
_.each(worksheet.hyperlinks, function (hyperlink) {
var address = colCache.decodeAddress(hyperlink.ref);
var row = worksheet.rows[address.row];
var cell = row.cells[address.col];
cell.type = Enums.ValueType.Hyperlink;
cell.text = cell.value;
cell.hyperlink = hyperlinks[hyperlink.rId];
});
parser.on('closetag', function(name) {
switch (name) {
case "c":
var address = colCache.decodeAddress(c.ref);
var cell = {
address: c.ref,
s: c.s
};
// Note: merged cells (if they've been merged already will not have v or f nodes)
if (c.f) {
cell.type = Enums.ValueType.Formula;
cell.formula = c.f.text;
if (c.v) {
if (c.t == "str") {
cell.result = utils.xmlDecode(c.v.text);
} else {
cell.result = parseFloat(c.v.text);
}
}
} else if (c.v) {
switch(c.t) {
case "s":
cell.type = Enums.ValueType.String;
cell.value = parseInt(c.v.text);
break;
case "str":
cell.type = Enums.ValueType.String;
cell.value = utils.xmlDecode(c.v.text);
break;
default:
cell.type = Enums.ValueType.Number;
cell.value = parseFloat(c.v.text);
break;
}
} else {
cell.type = Enums.ValueType.Merge;
}
var row = model.rows[address.row];
row.cells[address.col] = cell;
row.min = Math.min(row.min, address.col) || address.col;
row.max = Math.max(row.max, address.col);
break;
}
// no need for them any more
delete worksheet.hyperlinks;
}
// compact the rows and calculate dimensions
var dimensions = new Dimensions();
worksheet.rows = worksheet.rows.filter(function (row) {
return row;
});
_.each(worksheet.rows, function (row) {
dimensions.expand(row.number, row.min, row.number, row.max);
row.cells = row.cells.filter(function (cell) {
return cell;
});
parser.on('end', function() {
// if cols empty then can remove it from model
if (!model.cols.length) {
delete model.cols;
}
deferred.resolve(model);
});
worksheet.dimensions = dimensions.model;
delete worksheet.sheetNo;
// reconcile cell values, styles
_.each(worksheet.rows, function (row) {
row.style = row.styleId ? model.styles.getStyleModel(row.styleId) : {}
_.each(row.cells, function (cell) {
// get style model from style manager
var style = cell.s ? model.styles.getStyleModel(cell.s) : {};
//console.log(cell.address + " " + cell.s + " " + JSON.stringify(style));
delete cell.s;
if (style) {
cell.style = style;
}
switch (cell.type) {
case Enums.ValueType.String:
if (!_.isString(cell.value)) {
cell.value = model.sharedStrings[cell.value];
}
break;
case Enums.ValueType.Hyperlink:
cell.text = _.isString(cell.value) ?
cell.value : model.sharedStrings[cell.value];
delete cell.value;
break;
case Enums.ValueType.Number:
if (style && utils.isDateFmt(style.numFmt)) {
cell.type = Enums.ValueType.Date;
cell.value = utils.excelToDate(cell.value);
}
break;
case Enums.ValueType.Formula:
if ((cell.result !== undefined) && style && utils.isDateFmt(style.numFmt)) {
cell.result = utils.excelToDate(cell.result);
}
break;
}
});
parser.on('error', function (error) {
deferred.reject(error);
});
stream.pipe(parser);
return deferred.promise;
},
prepareModel: function(model) {
// reconcile sheet ids, rIds and names
_.each(model.sheetDefs, function(sheetDef) {
var rel = model.workbookRels[sheetDef.rId];
var worksheet = model.worksheetHash["xl/" + rel.target];
worksheet.name = sheetDef.name;
worksheet.id = sheetDef.id;
});
var isDateFmt = function(fmt) {
if (!fmt) return false;
// must remove all chars inside quotes and []
fmt = fmt.replace(/[\[][^\]]*[\]]/g,"");
fmt = fmt.replace(/"[^"]*"/g,"");
// then check for date formatting chars
var result = fmt.match(/[ymdhMsb]+/) != null;
return result;
};
// reconcile worksheets
_.each(model.worksheets, function(worksheet, sheetNo) {
// reconcile column styles
_.each(worksheet.cols, function(col) {
if (col.styleId) {
col.style = model.styles.getStyleModel(col.styleId);
}
});
});
// delete unnecessary parts
delete model.worksheetHash;
delete model.worksheetRels;
delete model.globalRels;
delete model.sharedStrings;
delete model.workbookRels;
delete model.sheetDefs;
delete model.styles;
},
createInputStream: function () {
var self = this;
var model = {
worksheets: [],
worksheetHash: {},
worksheetRels: []
};
// we have to be prepared to read the zip entries in whatever order they arrive
var promises = [];
var stream = unzip.Parse();
stream.on('entry', function (entry) {
var promise = null;
switch (entry.path) {
case "_rels/.rels":
promise = self.parseRels(entry)
.then(function (relationships) {
model.globalRels = relationships;
});
// reconcile merge list with merge cells
_.each(worksheet.merges, function(merge) {
var dimensions = colCache.decode(merge);
for (var i = dimensions.top; i <= dimensions.bottom; i++) {
var row = worksheet.rows[i];
for (var j = dimensions.left; j <= dimensions.right; j++) {
var cell = row.cells[j];
if (!cell) {
// nulls are not included in document - so if master cell has no value - add a null one here
row.cells[j] = {
type: Enums.ValueType.Null,
address: colCache.encodeAddress(i,j)
};
} else if (cell.type == Enums.ValueType.Merge) {
cell.master = dimensions.tl;
}
}
}
break;
case "xl/workbook.xml":
promise = self.parseWorkbook(entry)
.then(function (workbook) {
model.sheetDefs = workbook;
});
// if worksheet rels, merge them in
var relationships = model.worksheetRels[worksheet.sheetNo];
if (relationships) {
var hyperlinks = {};
_.each(relationships, function(relationship) {
hyperlinks[relationship.rId] = relationship.target;
});
_.each(worksheet.hyperlinks, function(hyperlink) {
var address = colCache.decodeAddress(hyperlink.ref);
var row = worksheet.rows[address.row];
var cell = row.cells[address.col];
cell.type = Enums.ValueType.Hyperlink;
cell.text = cell.value;
cell.hyperlink = hyperlinks[hyperlink.rId];
});
// no need for them any more
delete worksheet.hyperlinks;
}
// compact the rows and calculate dimensions
var dimensions = new Dimensions();
worksheet.rows = worksheet.rows.filter(function(row) { return row; });
_.each(worksheet.rows, function(row) {
dimensions.expand(row.number, row.min, row.number, row.max);
row.cells = row.cells.filter(function(cell) { return cell; });
break;
case "xl/_rels/workbook.xml.rels":
promise = self.parseRels(entry)
.then(function (relationships) {
model.workbookRels = relationships;
});
worksheet.dimensions = dimensions.model;
delete worksheet.sheetNo;
// reconcile cell values, styles
_.each(worksheet.rows, function(row) {
row.style = row.styleId ? model.styles.getStyleModel(row.styleId) : {}
_.each(row.cells, function(cell) {
// get style model from style manager
var style = cell.s ? model.styles.getStyleModel(cell.s) : {};
//console.log(cell.address + " " + cell.s + " " + JSON.stringify(style));
delete cell.s;
if (style) {
cell.style = style;
}
switch(cell.type) {
case Enums.ValueType.String:
if (!_.isString(cell.value)) {
cell.value = model.sharedStrings[cell.value];
}
break;
case Enums.ValueType.Hyperlink:
cell.text = _.isString(cell.value) ?
cell.value : model.sharedStrings[cell.value];
delete cell.value;
break;
case Enums.ValueType.Number:
if (style && isDateFmt(style.numFmt)) {
cell.type = Enums.ValueType.Date;
cell.value = utils.excelToDate(cell.value);
}
break;
case Enums.ValueType.Formula:
if ((cell.result !== undefined) && style && isDateFmt(style.numFmt)) {
cell.result = utils.excelToDate(cell.result);
}
break;
}
});
break;
case "xl/sharedStrings.xml":
promise = self.parseSharedStrings(entry)
.then(function (sharedStrings) {
model.sharedStrings = sharedStrings;
});
break;
case "xl/styles.xml":
model.styles = new StyleManager();
promise = model.styles.parse(entry);
break;
default:
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
var match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/)
var sheetNo = match[1];
promise = self.parseWorksheet(entry)
.then(function (worksheet) {
worksheet.sheetNo = sheetNo;
model.worksheetHash[entry.path] = worksheet;
model.worksheets.push(worksheet);
});
} else if (entry.path.match(/xl\/worksheets\/_rels\/sheet\d+\.xml.rels/)) {
var match = entry.path.match(/xl\/worksheets\/_rels\/sheet(\d+)\.xml.rels/)
var sheetNo = match[1];
promise = self.parseRels(entry)
.then(function (relationships) {
model.worksheetRels[sheetNo] = relationships;
});
} else {
entry.autodrain();
}
break;
}
if (promise) {
promises.push(promise);
promise = null;
}
});
stream.on('close', function () {
Promise.all(promises)
.then(function () {
self.prepareModel(model);
// apply model
self.workbook.model = model;
})
.then(function () {
stream.emit("done");
})
.catch(function (error) {
stream.emit("error", error);
});
// delete unnecessary parts
delete model.worksheetHash;
delete model.worksheetRels;
delete model.globalRels;
delete model.sharedStrings;
delete model.workbookRels;
delete model.sheetDefs;
delete model.styles;
},
createInputStream: function() {
var self = this;
var model = {
worksheets: [],
worksheetHash: {},
worksheetRels: []
};
// we have to be prepared to read the zip entries in whatever order they arrive
var promises = [];
var stream = unzip.Parse();
stream.on('entry',function (entry) {
var promise = null;
switch(entry.path) {
case "_rels/.rels":
promise = self.parseRels(entry)
.then(function(relationships) {
model.globalRels = relationships;
});
break;
case "xl/workbook.xml":
promise = self.parseWorkbook(entry)
.then(function(workbook) {
model.sheetDefs = workbook;
});
break;
case "xl/_rels/workbook.xml.rels":
promise = self.parseRels(entry)
.then(function(relationships) {
model.workbookRels = relationships;
});
break;
case "xl/sharedStrings.xml":
promise = self.parseSharedStrings(entry)
.then(function(sharedStrings) {
model.sharedStrings = sharedStrings;
});
break;
case "xl/styles.xml":
model.styles = new StyleManager();
promise = model.styles.parse(entry);
break;
default:
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
var match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/)
var sheetNo = match[1];
promise = self.parseWorksheet(entry)
.then(function(worksheet) {
worksheet.sheetNo = sheetNo;
model.worksheetHash[entry.path] = worksheet;
model.worksheets.push(worksheet);
});
} else if (entry.path.match(/xl\/worksheets\/_rels\/sheet\d+\.xml.rels/)) {
var match = entry.path.match(/xl\/worksheets\/_rels\/sheet(\d+)\.xml.rels/)
var sheetNo = match[1];
promise = self.parseRels(entry)
.then(function(relationships) {
model.worksheetRels[sheetNo] = relationships;
});
} else {
entry.autodrain();
}
break;
}
if (promise) {
promises.push(promise);
promise = null;
}
});
return stream;
},
read: function (stream) {
var deferred = Promise.defer();
var self = this;
var zipStream = this.createInputStream();
zipStream.on("done", function () {
deferred.resolve(self.workbook);
}).on("error", function (error) {
deferred.reject(error);
});
stream.pipe(zipStream);
return deferred.promise;
},
// =========================================================================
// Write
addContentTypes: function (zip, model) {
var self = this;
return utils.fetchTemplate(require.resolve("./content-types.xml"))
.then(function (template) {
return template(model);
})
.then(function (data) {
zip.append(data, {name: "[Content_Types].xml"});
return zip;
});
},
addApp: function (zip, model) {
var self = this;
return utils.fetchTemplate(require.resolve("./app.xml"))
.then(function (template) {
return template(model);
})
.then(function (data) {
zip.append(data, {name: "docProps/app.xml"});
return zip;
});
},
addCore: function (zip, model) {
var self = this;
return utils.fetchTemplate(require.resolve("./core.xml"))
.then(function (template) {
return template(model);
})
.then(function (data) {
zip.append(data, {name: "docProps/core.xml"});
return zip;
});
},
addThemes: function (zip) {
var self = this;
return utils.readModuleFile(require.resolve("./theme1.xml"))
.then(function (data) {
zip.append(data, {name: "xl/theme/theme1.xml"});
return zip;
});
},
//<Relationship Id="rId1"
// Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
// Target="xl/workbook.xml"/>
addOfficeRels: function (zip, model) {
model._rels = {
relationships: [
{rId: "rId1", type: XLSX.RelType.OfficeDocument, target: "xl/workbook.xml"}
]
};
return utils.fetchTemplate(require.resolve("./.rels"))
.then(function (template) {
return template(model._rels);
})
.then(function (data) {
zip.append(data, {name: "/_rels/.rels"});
});
},
addWorkbookRels: function (zip, model) {
var self = this;
var count = 1;
model.workbookRels = {
relationships: [
{rId: "rId" + (count++), type: XLSX.RelType.Styles, target: "styles.xml"},
{rId: "rId" + (count++), type: XLSX.RelType.Theme, target: "theme/theme1.xml"}
]
};
if (model.sharedStrings.count) {
model.workbookRels.relationships.push(
{rId: "rId" + (count++), type: XLSX.RelType.SharedStrings, target: "sharedStrings.xml"}
);
}
_.each(model.worksheets, function (worksheet) {
worksheet.rId = "rId" + (count++);
model.workbookRels.relationships.push(
{rId: worksheet.rId, type: XLSX.RelType.Worksheet, target: "worksheets/sheet" + worksheet.id + ".xml"}
);
});
return utils.fetchTemplate(require.resolve("./.rels"))
.then(function (template) {
return template(model.workbookRels);
})
.then(function (data) {
zip.append(data, {name: "/xl/_rels/workbook.xml.rels"});
});
},
addSharedStrings: function (zip, model) {
if (!model.sharedStrings || !model.sharedStrings.count) {
return Promise.resolve();
} else {
return utils.fetchTemplate(require.resolve("./sharedStrings.xml"))
.then(function (template) {
return template(model.sharedStrings);
})
.then(function (data) {
zip.append(data, {name: "/xl/sharedStrings.xml"});
});
stream.on('close', function () {
Promise.all(promises)
.then(function() {
self.prepareModel(model);
// apply model
self.workbook.model = model;
})
.then(function() {
stream.emit("done");
})
.catch(function(error) {
stream.emit("error", error);
});
}
},
addWorkbook: function (zip, model) {
return utils.fetchTemplate(require.resolve("./workbook.xml"))
.then(function (template) {
return template(model);
})
.then(function (data) {
zip.append(data, {name: "/xl/workbook.xml"});
return zip;
});
},
getValueType: function (v) {
if ((v === null) || (v === undefined)) {
return Enums.ValueType.Null;
} else if ((v instanceof String) || (typeof v == "string")) {
return Enums.ValueType.String;
} else if (typeof v == "number") {
return Enums.ValueType.Number;
} else if (v instanceof Date) {
return Enums.ValueType.Date;
} else if (v.text && v.hyperlink) {
return Enums.ValueType.Hyperlink;
} else if (v.formula) {
return Enums.ValueType.Formula;
} else {
throw new Error("I could not understand type of value")
}
},
getEffectiveCellType: function (cell) {
switch (cell.type) {
case Enums.ValueType.Formula:
return this.getValueType(cell.result);
default:
return cell.type;
}
},
addWorksheets: function (zip, model) {
var self = this;
var promises = [];
function buildC(cell, t, close) {
var c = ['<c r="'];
c.push(cell.address);
c.push('"');
if (t) {
c.push(' t="' + t + '"');
}
var s = model.styles.addStyleModel(cell.style, self.getEffectiveCellType(cell));
if (s) {
c.push(' s="' + s + '"');
}
c.push(close ? '/>' : '>');
return c.join('');
}
_.each(model.worksheets, function (worksheet) {
// TODO: process worksheet model and prepare for template
if (!worksheet.cols) {
worksheet.cols = false;
} else {
_.each(worksheet.cols, function (column) {
column.styleId = model.styles.addStyleModel(column.style);
});
return stream;
},
read: function(stream) {
var deferred = Promise.defer();
var self = this;
var zipStream = this.createInputStream();
zipStream.on("done", function() {
deferred.resolve(self.workbook);
}).on("error", function(error) {
deferred.reject(error);
});
stream.pipe(zipStream);
return deferred.promise;
},
// =========================================================================
// Write
addContentTypes: function(zip, model) {
var self = this;
return utils.fetchTemplate(require.resolve("./content-types.xml"))
.then(function(template){
return template(model);
})
.then(function(data) {
zip.append(data, { name: "[Content_Types].xml" });
return zip;
});
},
addApp: function(zip, model) {
var self = this;
return utils.fetchTemplate(require.resolve("./app.xml"))
.then(function(template){
return template(model);
})
.then(function(data) {
zip.append(data, { name: "docProps/app.xml" });
return zip;
});
},
addCore: function(zip, model) {
var self = this;
return utils.fetchTemplate(require.resolve("./core.xml"))
.then(function(template){
return template(model);
})
.then(function(data) {
zip.append(data, { name: "docProps/core.xml" });
return zip;
});
},
addThemes: function(zip) {
var self = this;
return utils.readModuleFile(require.resolve("./theme1.xml"))
.then(function(data){
zip.append(data, { name: "xl/theme/theme1.xml" });
return zip;
});
},
//<Relationship Id="rId1"
// Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
// Target="xl/workbook.xml"/>
addOfficeRels: function(zip, model) {
model._rels = {
relationships: [
{ rId: "rId1", type: XLSX.RelType.OfficeDocument, target: "xl/workbook.xml" }
]
};
return utils.fetchTemplate(require.resolve("./.rels"))
.then(function(template) {
return template(model._rels);
})
.then(function(data) {
zip.append(data, { name: "/_rels/.rels" });
});
},
addWorkbookRels: function(zip, model) {
var self = this;
var count = 1;
model.workbookRels = {
relationships: [
{ rId: "rId" + (count++), type: XLSX.RelType.Styles, target: "styles.xml" },
{ rId: "rId" + (count++), type: XLSX.RelType.Theme, target: "theme/theme1.xml" }
]
};
if (model.sharedStrings.count) {
model.workbookRels.relationships.push(
{ rId: "rId" + (count++), type: XLSX.RelType.SharedStrings, target: "sharedStrings.xml" }
);
}
_.each(model.worksheets, function(worksheet) {
worksheet.rId = "rId" + (count++);
model.workbookRels.relationships.push(
{ rId: worksheet.rId, type: XLSX.RelType.Worksheet, target: "worksheets/sheet" + worksheet.id + ".xml" }
);
});
return utils.fetchTemplate(require.resolve("./.rels"))
.then(function(template) {
return template(model.workbookRels);
})
.then(function(data) {
zip.append(data, { name: "/xl/_rels/workbook.xml.rels" });
});
},
addSharedStrings: function(zip, model) {
if (!model.sharedStrings || !model.sharedStrings.count) {
return Promise.resolve();
} else {
return utils.fetchTemplate(require.resolve("./sharedStrings.xml"))
.then(function(template) {
return template(model.sharedStrings);
})
.then(function(data) {
zip.append(data, { name: "/xl/sharedStrings.xml" });
});
}
},
addWorkbook: function(zip, model) {
return utils.fetchTemplate(require.resolve("./workbook.xml"))
.then(function(template){
return template(model);
})
.then(function(data) {
zip.append(data, { name: "/xl/workbook.xml" });
return zip;
});
},
getValueType: function(v) {
if ((v === null) || (v === undefined)) {
return Enums.ValueType.Null;
} else if ((v instanceof String) || (typeof v == "string")) {
return Enums.ValueType.String;
} else if (typeof v == "number") {
return Enums.ValueType.Number;
} else if (v instanceof Date) {
return Enums.ValueType.Date;
} else if (v.text && v.hyperlink) {
return Enums.ValueType.Hyperlink;
} else if (v.formula) {
return Enums.ValueType.Formula;
} else {
throw new Error("I could not understand type of value")
}
},
getEffectiveCellType: function(cell) {
switch(cell.type) {
}
worksheet.hyperlinks = worksheet.relationships = [];
var hyperlinkCount = 1;
_.each(worksheet.rows, function (row) {
row.styleId = model.styles.addStyleModel(row.style);
_.each(row.cells, function (cell) {
switch (cell.type) {
case Enums.ValueType.Number:
cell.xml = buildC(cell) + '<v>' + cell.value + '</v></c>';
break;
case Enums.ValueType.String:
if (model.useSharedStrings) {
cell.xml = buildC(cell, 's') +
'<v>' + model.sharedStrings.add(cell.value) + '</v>' +
'</c>';
} else {
cell.xml = buildC(cell, 'str') +
'<v>' + utils.xmlEncode(cell.value) + '</v>' +
'</c>';
}
break;
case Enums.ValueType.Date:
cell.xml = buildC(cell) +
'<v>' + utils.dateToExcel(cell.value) + '</v>' +
'</c>';
break;
case Enums.ValueType.Hyperlink:
if (model.useSharedStrings) {
cell.xml = buildC(cell, 's') +
'<v>' + model.sharedStrings.add(cell.text) + '</v>' +
'</c>';
} else {
cell.xml = buildC(cell, 'str') +
'<v>' + utils.xmlEncode(cell.value) + '</v>' +
'</c>';
}
worksheet.hyperlinks.push({
address: cell.address,
rId: "rId" + (hyperlinkCount++),
type: XLSX.RelType.Hyperlink,
target: utils.xmlEncode(cell.hyperlink),
targetMode: "External"
});
break;
case Enums.ValueType.Formula:
return this.getValueType(cell.result);
default:
return cell.type;
}
},
addWorksheets: function(zip, model) {
var self = this;
var promises = [];
function buildC(cell, t, close) {
var c = ['<c r="'];
c.push(cell.address);
c.push('"');
if (t) { c.push(' t="' + t + '"'); }
var s = model.styles.addStyleModel(cell.style, self.getEffectiveCellType(cell));
if (s) { c.push(' s="' + s + '"'); }
c.push(close ? '/>' : '>');
return c.join('');
}
_.each(model.worksheets, function(worksheet) {
// TODO: process worksheet model and prepare for template
if (!worksheet.cols) {
worksheet.cols = false;
} else {
_.each(worksheet.cols, function(column) {
column.styleId = model.styles.addStyleModel(column.style);
});
}
worksheet.hyperlinks = worksheet.relationships = [];
var hyperlinkCount = 1;
_.each(worksheet.rows, function(row) {
row.styleId = model.styles.addStyleModel(row.style);
_.each(row.cells, function(cell) {
switch(cell.type) {
case Enums.ValueType.Number:
cell.xml = buildC(cell) + '<v>' + cell.value + '</v></c>';
break;
case Enums.ValueType.String:
if (model.useSharedStrings) {
cell.xml = buildC(cell, 's') +
'<v>' + model.sharedStrings.add(cell.value) + '</v>' +
'</c>';
} else {
cell.xml = buildC(cell, 'str') +
'<v>' + utils.xmlEncode(cell.value) + '</v>' +
'</c>';
}
break;
case Enums.ValueType.Date:
cell.xml = buildC(cell) +
'<v>' + utils.dateToExcel(cell.value) + '</v>' +
'</c>';
break;
case Enums.ValueType.Hyperlink:
if (model.useSharedStrings) {
cell.xml = buildC(cell, 's') +
'<v>' + model.sharedStrings.add(cell.text) + '</v>' +
'</c>';
} else {
cell.xml = buildC(cell, 'str') +
'<v>' + utils.xmlEncode(cell.value) + '</v>' +
'</c>';
}
worksheet.hyperlinks.push({
address: cell.address,
rId: "rId" + (hyperlinkCount++),
type: XLSX.RelType.Hyperlink,
target: utils.xmlEncode(cell.hyperlink),
targetMode: "External"
});
break;
case Enums.ValueType.Formula:
switch(self.getValueType(cell.result)) {
case Enums.ValueType.Null: // ?
cell.xml = buildC(cell) +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'</c>';
break;
case Enums.ValueType.String:
// oddly, formula results don't ever use shared strings
cell.xml = buildC(cell, 'str') +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'<v>' + utils.xmlEncode(cell.result) + '</v>' +
'</c>';
break;
case Enums.ValueType.Number:
cell.xml = buildC(cell) +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'<v>' + cell.result + '</v>' +
'</c>';
break;
case Enums.ValueType.Date:
cell.xml = buildC(cell) +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'<v>' + utils.dateToExcel(cell.result) + '</v>' +
'</c>';
break;
case Enums.ValueType.Hyperlink: // ??
case Enums.ValueType.Formula:
default:
throw new Error("I could not understand type of value");
}
break;
case Enums.ValueType.Merge:
cell.xml = buildC(cell, undefined, true);
break;
}
});
});
if (!worksheet.merges.length) {
worksheet.merges = false;
}
if (!worksheet.hyperlinks.length) {
worksheet.hyperlinks = false;
}
promises.push(utils.fetchTemplate(require.resolve("./sheet.xml"))
.then(function(template){
return template(worksheet);
})
.then(function(data) {
zip.append(data, { name: "/xl/worksheets/sheet" + worksheet.id + ".xml" });
return zip;
}));
if (worksheet.hyperlinks) {
promises.push(utils.fetchTemplate(require.resolve("./.rels"))
.then(function(template) {
return template(worksheet);
})
.then(function(data) {
zip.append(data, { name: "/xl/worksheets/_rels/sheet" + worksheet.id + ".xml.rels" });
}));
}
switch (self.getValueType(cell.result)) {
case Enums.ValueType.Null: // ?
cell.xml = buildC(cell) +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'</c>';
break;
case Enums.ValueType.String:
// oddly, formula results don't ever use shared strings
cell.xml = buildC(cell, 'str') +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'<v>' + utils.xmlEncode(cell.result) + '</v>' +
'</c>';
break;
case Enums.ValueType.Number:
cell.xml = buildC(cell) +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'<v>' + cell.result + '</v>' +
'</c>';
break;
case Enums.ValueType.Date:
cell.xml = buildC(cell) +
'<f>' + utils.xmlEncode(cell.formula) + '</f>' +
'<v>' + utils.dateToExcel(cell.result) + '</v>' +
'</c>';
break;
case Enums.ValueType.Hyperlink: // ??
case Enums.ValueType.Formula:
default:
throw new Error("I could not understand type of value");
}
break;
case Enums.ValueType.Merge:
cell.xml = buildC(cell, undefined, true);
break;
}
});
return Promise.all(promises);
},
_finalize: function(zip) {
var self = this;
var deferred = Promise.defer();
zip.on('end', function(){
deferred.resolve(self);
});
zip.on('error', function(error){
deferred.reject(error);
});
zip.finalize();
return deferred.promise;
},
write: function(stream, options) {
options = options || {};
var self = this;
var model = self.workbook.model;
var zip = Archiver("zip");
zip.pipe(stream);
// ensure following properties have sane values
model.creator = model.creator || "ExcelJS";
model.lastModifiedBy = model.lastModifiedBy || "ExcelJS";
model.created = model.created || new Date();
model.modified = model.modified || new Date();
model.useSharedStrings = options.useSharedStrings !== undefined ?
options.useSharedStrings :
true;
model.useStyles = options.useStyles !== undefined ?
options.useStyles :
true;
// Manage the shared strings
model.sharedStrings = new SharedStrings();
// add a style manager to handle cell formats, fonts, etc.
model.styles = model.useStyles ? new StyleManager() : new StyleManager.Mock();
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip),
self.addOfficeRels(zip, model)
});
if (!worksheet.merges.length) {
worksheet.merges = false;
}
if (!worksheet.hyperlinks.length) {
worksheet.hyperlinks = false;
}
promises.push(utils.fetchTemplate(require.resolve("./sheet.xml"))
.then(function (template) {
return template(worksheet);
})
.then(function (data) {
zip.append(data, {name: "/xl/worksheets/sheet" + worksheet.id + ".xml"});
return zip;
}));
if (worksheet.hyperlinks) {
promises.push(utils.fetchTemplate(require.resolve("./.rels"))
.then(function (template) {
return template(worksheet);
})
.then(function (data) {
zip.append(data, {name: "/xl/worksheets/_rels/sheet" + worksheet.id + ".xml.rels"});
}));
}
});
return Promise.all(promises);
},
_finalize: function (zip) {
var self = this;
var deferred = Promise.defer();
zip.on('end', function () {
deferred.resolve(self);
});
zip.on('error', function (error) {
deferred.reject(error);
});
zip.finalize();
return deferred.promise;
},
write: function (stream, options) {
options = options || {};
var self = this;
var model = self.workbook.model;
var zip = Archiver("zip");
zip.pipe(stream);
// ensure following properties have sane values
model.creator = model.creator || "ExcelJS";
model.lastModifiedBy = model.lastModifiedBy || "ExcelJS";
model.created = model.created || new Date();
model.modified = model.modified || new Date();
model.useSharedStrings = options.useSharedStrings !== undefined ?
options.useSharedStrings :
true;
model.useStyles = options.useStyles !== undefined ?
options.useStyles :
true;
// Manage the shared strings
model.sharedStrings = new SharedStrings();
// add a style manager to handle cell formats, fonts, etc.
model.styles = model.useStyles ? new StyleManager() : new StyleManager.Mock();
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip),
self.addOfficeRels(zip, model)
];
return Promise.all(promises)
.then(function () {
return self.addWorksheets(zip, model);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
model.styles.addToZip(zip),
self.addWorkbookRels(zip, model)
];
return Promise.all(promises)
.then(function() {
return self.addWorksheets(zip, model);
})
.then(function() {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
model.styles.addToZip(zip),
self.addWorkbookRels(zip, model)
];
return Promise.all(afters);
})
.then(function() {
return self.addWorkbook(zip, model);
})
.then(function(){
return self._finalize(zip);
});
},
writeFile: function(filename, options) {
var deferred = Promise.defer();
var stream = fs.createWriteStream(filename);
stream.on("finish", function() {
deferred.resolve();
});
stream.on("error", function(error) {
deferred.reject(error);
});
this.write(stream, options)
.then(function() {
stream.end();
})
.catch(function(error) {
deferred.reject(error);
});
return deferred.promise;
}
return Promise.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model);
})
.then(function () {
return self._finalize(zip);
});
},
writeFile: function (filename, options) {
var deferred = Promise.defer();
var stream = fs.createWriteStream(filename);
stream.on("finish", function () {
deferred.resolve();
});
stream.on("error", function (error) {
deferred.reject(error);
});
this.write(stream, options)
.then(function () {
stream.end();
})
.catch(function (error) {
deferred.reject(error);
});
return deferred.promise;
}
}

2

package.json
{
"name": "exceljs",
"version": "0.2.3",
"version": "0.2.4",
"description": "Excel Workbook Manager",

@@ -5,0 +5,0 @@ "private": false,

46

README.md

@@ -17,7 +17,5 @@ # ExcelJS

<li>
<a href="https://github.com/guyonroche/exceljs/issues/18">Merge Cell Styles</a>
<a href="https://github.com/guyonroche/exceljs/issues/27">Worksheets with Ampersand Names</a>
<ul>
<li>
Merged cells now persist (and parse) their styles.
</li>
<li>Worksheet names are now xml-encoded and should work with all xml compatable characters</li>
</ul>

@@ -28,29 +26,7 @@ </li>

<li>
<a href="#streaming-xlxs-writer">Streaming XLSX Writer</a>
<a href="#rows">Row.hidden</a> & <a href="#columns">Column.hidden</a>
<ul>
<li>
At long last ExcelJS can support writing massive XLSX files in a scalable
memory efficient manner. Performance has been optimised and even smaller spreadsheets
can be faster to write than the document writer. Options have been added to control
the use of shared strings and styles as these can both have a considerable effect on
performance
</li>
<li>Rows and Columns now support the hidden attribute.</li>
</ul>
</li>
<li>
<a href="#rows">Worksheet.lastRow</a>
<ul>
<li>Access the last editable row in a worksheet.</li>
</ul>
</li>
<li>
<a href="#rows">Row.commit()</a>
<ul>
<li>
For streaming writers, this method commits the row (and any previous rows) to the stream.
Committed rows will no longer be editable (and are typically deleted from the worksheet object).
For Document type workbooks, this method has no effect.
</li>
</ul>
</li>
</ul>

@@ -140,2 +116,9 @@

Use the second parameter of the addWorksheet function to create a new sheet with a specific tab color.
To add a new one with a red tab color use this example:
```javascript
var sheet = workbook.addWorksheet("My Sheet", "FFC0000");
```
## Access Worksheets

@@ -186,2 +169,5 @@ ```javascript

// Hide the column if you'd like
dobCol.hidden = true;
// iterate over all current cells in this column

@@ -227,2 +213,5 @@ dobCol.eachCell(function(cell, rowNumber) {

// make row hidden
row.hidden = true;
row.getCell(1).value = 5; // A5's value set to 5

@@ -917,2 +906,3 @@ row.getCell("name").value = "Zeb"; // B5's value set to "Zeb" - assuming column 2 is still keyed by name

| 0.2.2 | <ul><li><a href="https://pbs.twimg.com/profile_images/2933552754/fc8c70829ee964c5542ae16453503d37.jpeg">One Billion Cells</a><ul><li>Achievement Unlocked: A simple test using ExcelJS has created a spreadsheet with 1,000,000,000 cells. Made using random data with 100,000,000 rows of 10 cells per row. I cannot validate the file yet as Excel will not open it and I have yet to implement the streaming reader but I have every confidence that it is good since 1,000,000 rows loads ok.</li></ul></li></ul> |
| 0.2.3 |<ul><li>Bug Fixes<ul><li><a href="https://github.com/guyonroche/exceljs/issues/18">Merge Cell Styles</a><ul><li>Merged cells now persist (and parse) their styles.</li></ul></li></ul></li><li><a href="#streaming-xlxs-writer">Streaming XLSX Writer</a><ul><li>At long last ExcelJS can support writing massive XLSX files in a scalable memory efficient manner. Performance has been optimised and even smaller spreadsheets can be faster to write than the document writer. Options have been added to control the use of shared strings and styles as these can both have a considerable effect on performance</li></ul></li><li><a href="#rows">Worksheet.lastRow</a><ul><li>Access the last editable row in a worksheet.</li></ul></li><li><a href="#rows">Row.commit()</a><ul><li>For streaming writers, this method commits the row (and any previous rows) to the stream. Committed rows will no longer be editable (and are typically deleted from the worksheet object). For Document type workbooks, this method has no effect.</li></ul></li></ul> |
lib/xlsx/sheet.xml

Sorry, the diff of this file is not supported yet

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc