ModelContainer = function (model) {
this.model = model;
}n/a
Workbook = function () {
this.created = new Date();
this.modified = this.created;
this.properties = {};
this._worksheets = [];
this.views = [];
this._definedNames = new DefinedNames();
}...
```javascript
var Excel = require('exceljs');
```
## Create a Workbook
```javascript
var workbook = new Excel.Workbook();
```
## Set Workbook Properties
```javascript
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
...auto_drain = function () {
}n/a
base_xform = function (model, name) {
}n/a
cell_matrix = function (template) {
this.template = template;
this.sheets = {};
}n/a
composite_xform = function (options) {
this.tag = options.tag;
this.attrs = options.attrs;
this.children = options.children;
this.map = this.children.reduce(function(map, child) {
var name = child.name || child.tag;
var tag = child.tag || child.name;
map[tag] = child;
child.name = name;
child.tag = tag;
return map;
}, {});
}n/a
csv = function (workbook) {
this.workbook = workbook;
this.worksheet = null;
}n/a
flow_control = function (options) {
this.options = options = options || {};
// Buffer queue
this.queue = [];
// Consumer streams
this.pipes = [];
// Down-stream flow-control instances
this.children = [];
// Up-stream flow-control instances
this.parent = options.parent;
// Ensure we don't flush more than once at a time
this.flushing = false;
// determine timeout for flow control delays
if (options.gc) {
var gc = options.gc;
if (gc.getTimeout) {
this.getTimeout = gc.getTimeout;
} else {
// heap size below which we don't bother delaying
var threshold = gc.threshold !== undefined ? gc.threshold : 150000000;
// convert from heapsize to ms timeout
var divisor = gc.divisor !== undefined ? gc.divisor : 500000;
this.getTimeout = function() {
var memory = process.memoryUsage();
var heapSize = memory.heapTotal;
return (heapSize < threshold) ? 0 : Math.floor(heapSize / divisor);
};
}
} else {
this.getTimeout = null;
}
}n/a
hyperlink_reader = function (workbook, id) {
// in a workbook, each sheet will have a number
this.id = id;
this._workbook = workbook;
}n/a
hyperlink_writer = function (options) {
// in a workbook, each sheet will have a number
this.id = options.id;
// keep record of all hyperlinks
this._hyperlinks = [];
this._workbook = options.workbook;
}n/a
line_buffer = function (options) {
events.EventEmitter.call(this);
this.encoding = options.encoding;
this.buffer = null;
// part of cork/uncork
this.corked = false;
this.queue = [];
}n/a
list_xform = function (options) {
this.tag = options.tag;
this.count = options.count;
this.empty = options.empty;
this.$count = options.$count || 'count';
this.$ = options.$;
this.childXform = options.childXform;
}n/a
shared_strings = function () {
this._values = [];
this._totalRefs = 0;
this._hash = {}
}n/a
static_xform = function (model) {
// This class is an optimisation for static (unimportant and unchanging) xml
// It is stateless - apart from its static model and so can be used as a singleton
// Being stateless - it will only track entry to and exit from it's root xml tag during parsing and nothing else
// Known issues:
// since stateless - parseOpen always returns true. Parent xform must know when to start using this xform
// if the root tag is recursive, the parsing will behave unpredictably
this._model = model;
}n/a
stream_base64 = function (options) {
options = options || {};
// consuming pipe streams go here
this.pipes = [];
}n/a
stream_buf = function (options) {
options = options || {};
this.bufSize = options.bufSize || 1024 * 1024;
this.buffers = [];
// batch mode fills a buffer completely before passing the data on
// to pipes or 'readable' event listeners
this.batch = options.batch || false;
this.corked = false;
// where in the current writable buffer we're up to
this.inPos = 0;
// where in the current readable buffer we've read up to
this.outPos = 0;
// consuming pipe streams go here
this.pipes = [];
// controls emit('data')
this.paused = false;
this.encoding = null;
}n/a
stream_converter = function (inner, options) {
this.inner = inner;
options = options || {};
this.innerEncoding = (options.innerEncoding || 'UTF8').toUpperCase();
this.outerEncoding = (options.outerEncoding || 'UTF8').toUpperCase();
this.innerBOM = options.innerBOM || null;
this.outerBOM = options.outerBOM || null;
this.writeStarted = false;
}n/a
string_buf = function (options) {
this._buf = new Buffer((options && options.size) || 16384);
this._encoding = (options && options.encoding) || 'utf8';
// where in the buffer we are at
this._inPos = 0;
// for use by toBuffer()
this._buffer = undefined;
}n/a
string_builder = function (options) {
this.reset();
}n/a
stuttered_pipe = function (readable, writable, options) {
var self = this;
options = options || {};
this.readable = readable;
this.writable = writable;
this.bufSize = options.bufSize || 16384;
this.autoPause = options.autoPause || false;
this.paused = false;
this.eod = false;
this.scheduled = null;
readable.on('end', function() {
self.eod = true;
writable.end();
});
// need to have some way to communicate speed of stream
// back from the consumer
readable.on('readable', function() {
if (!self.paused) {
self.resume();
}
});
this._schedule();
}n/a
typed_stack = function (type) {
this._type = type;
this._stack = [];
}n/a
workbook_reader = function (options) {
this.options = options = options || {};
// until we actually parse a styles.xml file, just assume we're not handling styles
// (but we do need to handle dates)
this.styles = new StyleManager.Mock();
// worksheet readers, indexed by sheetNo
this.worksheetReaders = {};
// hyperlink readers, indexed by sheetNo
this.hyperlinkReaders = {};
// count the open readers
this.readers = 0;
// end of stream check
this.atEnd = false;
}n/a
workbook_writer = function (options) {
options = options || {};
this.created = options.created || new Date();
this.modified = options.modified || this.created;
this.creator = options.creator || 'ExcelJS';
this.lastModifiedBy = options.lastModifiedBy || 'ExcelJS';
this.lastPrinted = options.lastPrinted;
// using shared strings creates a smaller xlsx file but may use more memory
this.useSharedStrings = options.useSharedStrings || false;
this.sharedStrings = new SharedStrings();
// style manager
this.styles = options.useStyles ? new StylesXform(true) : new StylesXform.Mock(true);
// defined names
this._definedNames = new DefinedNames();
this._worksheets = [];
this.views = [];
this.zip = Archiver('zip');
if (options.stream) {
this.stream = options.stream;
} else if (options.filename) {
this.stream = fs.createWriteStream(options.filename);
} else {
this.stream = new StreamBuf();
}
this.zip.pipe(this.stream);
// these bits can be added right now
this.promise = PromishLib.Promish.all([
this.addThemes(),
this.addOfficeRels()
]);
}n/a
worksheet_reader = function (workbook, id) {
this.workbook = workbook;
this.id = id;
// and a name
this.name = 'Sheet' + this.id;
// column definitions
this._columns = null;
this._keys = {};
// keep a record of dimensions
this._dimensions = new Dimensions();
}n/a
worksheet_writer = function (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 are stored here while they need to be worked on.
// when they are committed, they will be deleted.
this._rows = [];
// column definitions
this._columns = null;
// column keys (addRow convenience): key ==> this._columns index
this._keys = {};
// keep record of all merges
this._merges = [];
this._merges.add = function() {}; // ignore cell instruction
// keep record of all hyperlinks
this._hyperlinkWriter = new HyperlinkWriter(options);
// keep a record of dimensions
this._dimensions = new Dimensions();
// first uncommitted row
this._rowZero = 1;
// committed flag
this.committed = false;
// for data validations
this.dataValidations = new DataValidations();
// for sharing formulae
this._formulae = {};
this._siFormulae = 0;
// for default row height, outline levels, etc
this.properties = Object.assign({}, {
defaultRowHeight: 15,
dyDescent: 55,
outlineLevelCol: 0,
outlineLevelRow: 0
}, options.properties);
// for all things printing
this.pageSetup = Object.assign({}, {
margins: {left: 0.7, right: 0.7, top: 0.75, bottom: 0.75, header: 0.3, footer: 0.3 },
orientation: 'portrait',
horizontalDpi: 4294967295,
verticalDpi: 4294967295,
fitToPage: !!(options.pageSetup && ((options.pageSetup.fitToWidth || options.pageSetup.fitToHeight) && !options.pageSetup.scale
)),
pageOrder: 'downThenOver',
blackAndWhite: false,
draft: false,
cellComments: 'None',
errors: 'displayed',
scale: 100,
fitToWidth: 1,
fitToHeight: 1,
paperSize: undefined,
showRowColHeaders: false,
showGridLines: false,
horizontalCentered: false,
verticalCentered: false,
rowBreaks: null,
colBreaks: null
}, options.pageSetup);
// using shared strings creates a smaller xlsx file but may use more memory
this.useSharedStrings = options.useSharedStrings || false;
this._workbook = options.workbook;
// views
this._views = options.views || [];
// start writing to stream now
this._writeOpenWorksheet();
this.startedData = false;
}n/a
xlsx = function (workbook) {
this.workbook = workbook;
}n/a
xml_stream = function () {
this._xml = [];
this._stack = [];
this._rollbacks = [];
}n/a
Workbook = function () {
this.created = new Date();
this.modified = this.created;
this.properties = {};
this._worksheets = [];
this.views = [];
this._definedNames = new DefinedNames();
}...
```javascript
var Excel = require('exceljs');
```
## Create a Workbook
```javascript
var workbook = new Excel.Workbook();
```
## Set Workbook Properties
```javascript
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
..._removeWorksheet = function (worksheet) {
delete this._worksheets[worksheet.id];
}...
get workbook() {
return this._workbook;
},
// when you're done with this worksheet, call this to remove from workbook
destroy: function destroy() {
this._workbook._removeWorksheet(this);
},
// Get the bounding range of the cells in this worksheet
get dimensions() {
var dimensions = new Range();
this._rows.forEach(function (row) {
if (row) {
...addWorksheet = function (name, options) {
var id = this.nextId;
name = name || 'sheet' + id;
// if options is a color, call it tabColor (and signal deprecated message)
if (options) {
if (typeof options === 'string') {
console.trace('tabColor argument is now deprecated. Please use workbook.addWorksheet(name, {properties: { tabColor: { argb
: "rbg value" } }');
options = {
properties: {
tabColor: {argb: options}
}
};
} else if (options.argb || options.theme || options.indexed) {
console.trace('tabColor argument is now deprecated. Please use workbook.addWorksheet(name, {properties: { tabColor: { ... } }');
options = {
properties: {
tabColor: options
}
};
}
}
var worksheetOptions = Object.assign({}, options, {
id: id,
name: name,
workbook: this
});
var worksheet = new Worksheet(worksheetOptions);
this._worksheets[id] = worksheet;
return worksheet;
}...
}
]
```
## Add a Worksheet
```javascript
var sheet = workbook.addWorksheet('My Sheet');
```
Use the second parameter of the addWorksheet function to specify options for the worksheet.
For Example:
```javascript
...clearThemes = function () {
// Note: themes are not an exposed feature, meddle at your peril!
this._themes = undefined;
}n/a
eachSheet = function (iteratee) {
this._worksheets.forEach(function(sheet) {
iteratee(sheet, sheet.id);
});
}...
var sheet = workbook.addWorksheet('My Sheet', {views:[{xSplit: 1, ySplit:1}]});
```
## Access Worksheets
```javascript
// Iterate over all sheets
// Note: workbook.worksheets.forEach will still work but this is better
workbook.eachSheet(function(worksheet, sheetId) {
// ...
});
// fetch sheet by name
var worksheet = workbook.getWorksheet('My Sheet');
// fetch sheet by id
...getWorksheet = function (id) {
if (id === undefined) {
return this._worksheets.find(function(worksheet) { return worksheet; });
} else if (typeof(id) === 'number') {
return this._worksheets[id];
} else if (typeof id === 'string') {
return this._worksheets.find(function(worksheet) {
return worksheet && worksheet.name == id;
});
} else {
return undefined;
}
}...
// Iterate over all sheets
// Note: workbook.worksheets.forEach will still work but this is better
workbook.eachSheet(function(worksheet, sheetId) {
// ...
});
// fetch sheet by name
var worksheet = workbook.getWorksheet('My Sheet');
// fetch sheet by id
var worksheet = workbook.getWorksheet(1);
```
## Worksheet Properties
...removeWorksheet = function (id) {
var worksheet = this.getWorksheet(id);
if (worksheet) {
worksheet.destroy();
}
}n/a
auto_drain = function () {
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
end = function () {
this.emit('end');
}...
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
},
writeFile: function writeFile(filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
...write = function (chunk) {
this.emit('data', chunk);
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...base_xform = function (model, name) {
}n/a
parse = function (parser) {
var self = this;
return new PromishLib.Promish(function(resolve, reject){
parser.on('opentag', function(node) {
self.parseOpen(node);
});
parser.on('text', function(text) {
self.parseText(text);
});
parser.on('closetag', function(name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function() {
resolve(self.model);
});
parser.on('error', function(error) {
reject(error);
});
});
}...
// column 1 is string
return value;
case 1:
// column 2 is a date
return new Date(value);
case 2:
// column 3 is JSON of a formula value
return JSON.parse(value);
default:
// the rest are numbers
return parseFloat(value);
}
}
};
workbook.csv.readFile(filename, options)
...parseClose = function (name) {
// Sax Close Node event
}...
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
resolve(self.model);
});
parser.on('error', function (error) {
...parseOpen = function (node) {
// Sax Open Node event
}...
// to make sure parses don't bleed to next iteration
this.model = model;
},
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
...parseStream = function (stream) {
var parser = Sax.createStream(true, {});
var promise = this.parse(parser);
stream.pipe(parser);
return promise;
}...
.then(function (workbook) {
stream.close();
return workbook;
});
},
parseRels: function (stream) {
var xform = new RelationshipsXform();
return xform.parseStream(stream);
},
parseWorkbook: function (stream) {
var xform = new WorkbookXform();
return xform.parseStream(stream);
},
parseSharedStrings: function (stream) {
var xform = new SharedStringsXform();
...parseText = function (node) {
// Sax Text event
}...
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
...prepare = function (model, options) {
// optional preparation (mutation) of model so it is ready for write
}...
};
utils.inherits(ListXform, BaseXform, {
prepare: function prepare(model, options) {
var childXform = this.childXform;
if (model) {
model.forEach(function (childModel) {
childXform.prepare(childModel, options);
});
}
},
render: function render(xmlStream, model) {
if (model && model.length) {
xmlStream.openNode(this.tag, this.$);
...reconcile = function (model, options) {
// optional post-parse step (opposite to prepare)
}...
return false;
}
},
reconcile: function reconcile(model, options) {
if (model) {
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.reconcile(childModel, options);
});
}
}
});
},{"../../utils/utils":20,"./base-xform":25}],39:[function(require,module,exports){
...render = function (xmlStream, model) {
// convert model to xml
}...
// convenience function to get the xml of this.model
// useful for manager types that are built during the prepare phase
return this.toXml(this.model);
},
toXml: function toXml(model) {
var xmlStream = new XmlStream();
this.render(xmlStream, model);
return xmlStream.xml;
}
};
},{"../../utils/promish":15,"../../utils/xml-stream":21,"sax":188}],26:[function(require,module,exports
){
/**
...reset = function (model) {
// to make sure parses don't bleed to next iteration
this.model = model;
}...
},
parseOpen: function parseOpen(node) {
if (this.parser) {
this.parser.parseOpen(node);
return true;
} else if (node.name === this.tag) {
this.map.tabColor.reset();
this.map.pageSetUpPr.reset();
return true;
} else if (this.map[node.name]) {
this.parser = this.map[node.name];
this.parser.parseOpen(node);
return true;
} else {
...toXml = function (model) {
var xmlStream = new XmlStream();
this.render(xmlStream, model);
return xmlStream.xml;
}...
stream.pipe(parser);
return promise;
},
get xml() {
// convenience function to get the xml of this.model
// useful for manager types that are built during the prepare phase
return this.toXml(this.model);
},
toXml: function toXml(model) {
var xmlStream = new XmlStream();
this.render(xmlStream, model);
return xmlStream.xml;
}
...cell_matrix = function (template) {
this.template = template;
this.sheets = {};
}n/a
addCell = function (addressStr) {
this.addCellEx(colCache.decodeEx(addressStr));
}...
set model(value) {
// value is [ { name, ranges }, ... ]
var matrixMap = this.matrixMap = {};
value.forEach(function (definedName) {
var matrix = matrixMap[definedName.name] = new CellMatrix();
definedName.ranges.forEach(function (rangeStr) {
if (rangeRegexp.test(rangeStr.split('!').pop() || '')) {
matrix.addCell(rangeStr);
}
});
});
}
};
//<definedNames>
...addCellEx = function (address) {
if (address.top) {
for (var row = address.top; row <= address.bottom; row++) {
for (var col = address.left; col <= address.right; col++) {
this.getCellAt(address.sheetName, row, col);
}
}
} else {
this.findCellEx(address, true);
}
}...
var address = {
sheetName: location.sheetName,
address: colCache.n2l(col) + row,
row: row,
col: col
};
matrix.addCellEx(address);
}
}
} else {
matrix.addCellEx(location);
}
},
...findCell = function (addressStr) {
return this.findCellEx(colCache.decodeEx(addressStr), false);
}...
},
set address(value) {
this.model.address = value;
},
_getTranslatedFormula: function _getTranslatedFormula() {
if (!this._translatedFormula && this.model.sharedFormula) {
var worksheet = this.cell._row.worksheet;
var master = worksheet.findCell(this.model.sharedFormula);
this._translatedFormula = master && slideFormula(master.formula, master.address, this.model.address);
}
return this._translatedFormula;
},
toCsvString: function toCsvString() {
return '' + (this.model.result || '');
...findCellAt = function (sheetName, rowNumber, colNumber) {
var sheet = this.sheets[sheetName];
var row = sheet && sheet[rowNumber];
return row && row[colNumber];
}...
var sheetName = cell.sheetName;
var range = new Range(cell.row, cell.col, cell.row, cell.col, sheetName);
var x, y;
// grow vertical - only one col to worry about
function vGrow(y, edge) {
var c = matrix.findCellAt(sheetName, y, cell.col);
if (!c || !c.mark) {
return false;
}
range[edge] = y;
c.mark = false;
return true;
}
...findCellEx = function (address, create) {
var sheet = this.findSheet(address, create);
var row = this.findSheetRow(sheet, address, create);
return this.findRowCell(row, address, create);
}...
// get all the names of a cell
getNames: function getNames(addressStr) {
return this.getNamesEx(colCache.decodeEx(addressStr));
},
getNamesEx: function getNamesEx(address) {
return _.map(this.matrixMap, function (matrix, name) {
if (matrix.findCellEx(address)) {
return name;
}
}).filter(function (name) {
return name;
});
},
...findRowCell = function (row, address, create) {
var col = address.col;
if (row && row[col]) {
return row[col];
}
if (create) {
return (row[col] = this.template ? Object.assign(address, JSON.parse(JSON.stringify(this.template))) : address);
}
}...
},
getCellEx: function getCellEx(address) {
return this.findCellEx(address, true);
},
findCellEx: function findCellEx(address, create) {
var sheet = this.findSheet(address, create);
var row = this.findSheetRow(sheet, address, create);
return this.findRowCell(row, address, create);
},
getCellAt: function getCellAt(sheetName, rowNumber, colNumber) {
var sheet = this.sheets[sheetName] || (this.sheets[sheetName] = []);
var row = sheet[rowNumber] || (sheet[rowNumber] = []);
return row[colNumber] || (row[colNumber] = {
sheetName: sheetName,
address: colCache.n2l(colNumber) + rowNumber,
...findSheet = function (address, create) {
var name = address.sheetName;
if (this.sheets[name]) {
return this.sheets[name];
}
if (create) {
return (this.sheets[name] = []);
}
}...
this.findCellEx(address, true);
}
},
getCellEx: function getCellEx(address) {
return this.findCellEx(address, true);
},
findCellEx: function findCellEx(address, create) {
var sheet = this.findSheet(address, create);
var row = this.findSheetRow(sheet, address, create);
return this.findRowCell(row, address, create);
},
getCellAt: function getCellAt(sheetName, rowNumber, colNumber) {
var sheet = this.sheets[sheetName] || (this.sheets[sheetName] = []);
var row = sheet[rowNumber] || (sheet[rowNumber] = []);
return row[colNumber] || (row[colNumber] = {
...findSheetRow = function (sheet, address, create) {
var row = address.row;
if (sheet && sheet[row]) {
return sheet[row];
}
if (create) {
return (sheet[row] = []);
}
}...
}
},
getCellEx: function getCellEx(address) {
return this.findCellEx(address, true);
},
findCellEx: function findCellEx(address, create) {
var sheet = this.findSheet(address, create);
var row = this.findSheetRow(sheet, address, create);
return this.findRowCell(row, address, create);
},
getCellAt: function getCellAt(sheetName, rowNumber, colNumber) {
var sheet = this.sheets[sheetName] || (this.sheets[sheetName] = []);
var row = sheet[rowNumber] || (sheet[rowNumber] = []);
return row[colNumber] || (row[colNumber] = {
sheetName: sheetName,
...forEach = function (callback) {
_.each(this.sheets, function(sheet) {
if (sheet) sheet.forEach(function(row) {
if (row) row.forEach(function(cell) {
if (cell) callback(cell);
});
});
});
}...
get names() {
return this.workbook.definedNames.getNamesEx(this.fullAddress);
},
set names(value) {
var self = this;
var definedNames = this.workbook.definedNames;
this.workbook.definedNames.removeAllNames(self.fullAddress);
value.forEach(function (name) {
definedNames.addEx(self.fullAddress, name);
});
},
addName: function addName(name) {
this.workbook.definedNames.addEx(this.fullAddress, name);
},
removeName: function removeName(name) {
...getCell = function (addressStr) {
return this.findCellEx(colCache.decodeEx(addressStr), true);
}...
worksheet.getRow(5).outlineLevel = 1;
// rows support a readonly field to indicate the collapsed state based on outlineLevel
expect(worksheet.getRow(4).collapsed).to.equal(false);
expect(worksheet.getRow(5).collapsed).to.equal(true);
row.getCell(1).value = 5; // A5's value set to 5
row.getCell('name').value = 'Zeb'; // B5's value set to 'Zeb' - assuming column 2 is still keyed
by name
row.getCell('C').value = new Date(); // C5's value set to now
// Get a row as a sparse array
// Note: interface change: worksheet.getRow(4) ==> worksheet.getRow(4).values
row = worksheet.getRow(4).values;
expect(row[5]).toEqual('Kyle');
...getCellAt = function (sheetName, rowNumber, colNumber) {
var sheet = this.sheets[sheetName] || (this.sheets[sheetName] = []);
var row = sheet[rowNumber] || (sheet[rowNumber] = []);
return row[colNumber] || (row[colNumber] = {
sheetName: sheetName,
address: colCache.n2l(colNumber) + rowNumber,
row: rowNumber,
col: colNumber
});
}...
var row = sheet && sheet[rowNumber];
return row && row[colNumber];
},
addCellEx: function addCellEx(address) {
if (address.top) {
for (var row = address.top; row <= address.bottom; row++) {
for (var col = address.left; col <= address.right; col++) {
this.getCellAt(address.sheetName, row, col);
}
}
} else {
this.findCellEx(address, true);
}
},
getCellEx: function getCellEx(address) {
...getCellEx = function (address) {
return this.findCellEx(address, true);
}n/a
map = function (callback) {
var results = [];
this.forEach(function(cell) { results.push(callback(cell)); });
return results;
}...
if (special !== undefined) {
return special;
}
return datum;
};
var csvStream = csv(options).on('data', function (data) {
worksheet.addRow(data.map(map));
}).on('end', function () {
csvStream.emit('worksheet', worksheet);
});
return csvStream;
},
write: function write(stream, options) {
...removeCellEx = function (address) {
var sheet = this.findSheet(address);
if (!sheet) { return; }
var row = this.findSheetRow(sheet, address);
if (!row) { return; }
delete row[address.col];
}...
remove: function remove(locStr, name) {
var location = colCache.decodeEx(locStr);
this.removeEx(location, name);
},
removeEx: function removeEx(location, name) {
var matrix = this.getMatrix(name);
matrix.removeCellEx(location);
},
removeAllNames: function removeAllNames(location) {
_.each(this.matrixMap, function (matrix) {
matrix.removeCellEx(location);
});
},
..._fill = function (level) {
var c, v, l1, l2, l3;
var n = 1;
if (level >= 1) {
while (n <= 26) {
c = this._dictionary[n-1];
this._n2l[n] = c;
this._l2n[c] = n;
n++;
}
}
if (level >= 2) {
while (n <= 26 + 26 * 26) {
v = n-(26+1);
l1 = v % 26;
l2 = Math.floor(v / 26);
c = this._dictionary[l2] + this._dictionary[l1];
this._n2l[n] = c;
this._l2n[c] = n;
n++;
}
}
if (level >= 3) {
while (n <= 16384) {
v = n-(26*26+26+1);
l1 = v % 26;
l2 = Math.floor(v / 26) % 26;
l3 = Math.floor(v / (26*26));
c = this._dictionary[l3] + this._dictionary[l2] + this._dictionary[l1];
this._n2l[n] = c;
this._l2n[c] = n;
n++;
}
}
}...
this._l2n[c] = n;
n++;
}
}
},
l2n: function l2n(l) {
if (!this._l2n[l]) {
this._fill(l.length);
}
if (!this._l2n[l]) {
throw new Error('Out of bounds. Invalid column letter: ' + l);
}
return this._l2n[l];
},
n2l: function n2l(n) {
..._level = function (n) {
if (n <= 26) { return 1; }
if (n <= 26*26) { return 2; }
return 3;
}...
return this._l2n[l];
},
n2l: function n2l(n) {
if (n < 1 || n > 16384) {
throw new Error('' + n + ' is out of bounds. Excel supports columns from 1 to 16384');
}
if (!this._n2l[n]) {
this._fill(this._level(n));
}
return this._n2l[n];
},
// =========================================================================
// Address processing
_hash: {},
...decode = function (value) {
var parts = value.split(':');
if (parts.length == 2) {
var tl = this.decodeAddress(parts[0]);
var br = this.decodeAddress(parts[1]);
var result = {
top: Math.min(tl.row, br.row),
left: Math.min(tl.col, br.col),
bottom: Math.max(tl.row, br.row),
right: Math.max(tl.col, br.col)
};
// reconstruct tl, br and dimensions
result.tl = this.n2l(result.left) + result.top;
result.br = this.n2l(result.right) + result.bottom;
result.dimensions = result.tl + ':' + result.br;
return result;
} else {
return this.decodeAddress(value);
}
}...
*/
'use strict';
var colCache = require('./../utils/col-cache');
// used by worksheet to calculate sheet dimensions
var Range = module.exports = function () {
this.decode(arguments);
};
Range.prototype = {
_set_tlbr: function _set_tlbr(t, l, b, r, s) {
this.model = {
top: Math.min(t, b),
left: Math.min(l, r),
...decodeAddress = function (value) {
var addr = this._hash[value];
if (addr) {
return addr;
}
var col = value.match(/[A-Z]+/)[0];
var colNumber = this.l2n(col);
var row = value.match(/\d+/)[0];
var rowNumber = parseInt(row);
// in case $row$col
value = col + row;
var address = {
address: value,
col: colNumber,
row: rowNumber,
$col$row: '$' + col + '$' + row
};
// mem fix - cache only the tl 100x100 square
if ((colNumber <= 100) && (rowNumber <= 100)) {
this._hash[value] = address;
this._hash[address.$col$row] = address;
}
return address;
}...
left: Math.min(l, r),
bottom: Math.max(t, b),
right: Math.max(l, r),
sheetName: s
};
},
_set_tl_br: function _set_tl_br(tl, br, s) {
tl = colCache.decodeAddress(tl);
br = colCache.decodeAddress(br);
this._set_tlbr(tl.row, tl.col, br.row, br.col, s);
},
decode: function decode(argv) {
switch (argv.length) {
case 5:
// [t,l,b,r,s]
...decodeEx = function (value) {
var groups = value.match(/(?:(?:(?:'((?:[^']|'')*)')|([^'^ ^!]*))!)?(.*)/);
var sheetName = groups[1] || groups[2]; // Qouted and unqouted groups
var reference = groups[3]; // Remaining address
var parts = reference.split(':');
if (parts.length > 1) {
var tl = this.decodeAddress(parts[0]);
var br = this.decodeAddress(parts[1]);
var top = Math.min(tl.row, br.row);
var left = Math.min(tl.col, br.col);
var bottom = Math.max(tl.row, br.row);
var right = Math.max(tl.col, br.col);
tl = this.n2l(left) + top;
br = this.n2l(right) + bottom;
return {
top: top, left: left, bottom: bottom, right: right,
sheetName: sheetName,
tl: { address: tl, col: left, row: top, $col$row: '$' + this.n2l(left) + '$' + top, sheetName: sheetName },
br: { address: br, col: right, row: bottom, $col$row: '$' + this.n2l(right) + '$' + bottom, sheetName: sheetName },
dimensions: tl + ':' + br
};
} else if (reference.startsWith('#')) {
return sheetName ? {sheetName: sheetName, error: reference} : {error: reference};
} else {
var address = this.decodeAddress(reference);
return sheetName ? Object.assign({sheetName: sheetName}, address) : address;
}
}...
DefinedNames.prototype = {
getMatrix: function getMatrix(name) {
return this.matrixMap[name] || (this.matrixMap[name] = new CellMatrix());
},
// add a name to a cell. locStr in the form SheetName!$col$row or SheetName!$c1$r1:$c2:$r2
add: function add(locStr, name) {
var location = colCache.decodeEx(locStr);
this.addEx(location, name);
},
addEx: function addEx(location, name) {
var matrix = this.getMatrix(name);
if (location.top) {
for (var col = location.left; col <= location.right; col++) {
for (var row = location.top; row <= location.bottom; row++) {
...encode = function () {
switch(arguments.length) {
case 2:
return colCache.encodeAddress(arguments[0], arguments[1]);
case 4:
return colCache.encodeAddress(arguments[0], arguments[1]) + ':' + colCache.encodeAddress(arguments[2], arguments[3]);
default:
throw new Error('Can only encode with 2 or 4 arguments');
}
}n/a
encodeAddress = function (row, col) {
return colCache.n2l(col) + row;
}...
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
});
},
// remove cell(s) and shift all higher cells down by count
splice: function splice(start, count) {
...getAddress = function (r, c) {
if (c) {
var address = this.n2l(c) + r;
return this.decodeAddress(address);
} else {
return this.decodeAddress(r);
}
}...
},
// =========================================================================
// Cells
// returns the cell at [r,c] or address given by r. If not found, return undefined
findCell: function findCell(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 getCell(r, c) {
var address = colCache.getAddress(r, c);
...l2n = function (l) {
if (!this._l2n[l]) {
this._fill(l.length);
}
if (!this._l2n[l]) {
throw new Error('Out of bounds. Invalid column letter: ' + l);
}
return this._l2n[l];
}...
getCell: function getCell(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
});
...n2l = function (n) {
if ((n < 1) || (n > 16384)) {
throw new Error('' + n + ' is out of bounds. Excel supports columns from 1 to 16384');
}
if (!this._n2l[n]) {
this._fill(this._level(n));
}
return this._n2l[n];
}...
get number() {
return this._number;
},
get worksheet() {
return this._worksheet;
},
get letter() {
return colCache.n2l(this._number);
},
get isCustomWidth() {
return this.width !== undefined && this.width !== 8;
},
get defn() {
return {
header: this._header,
...validateAddress = function (value) {
if (!value.match(/^[A-Z]+\d+$/)) {
throw new Error('Invalid Address: ' + value);
}
return true;
}...
if (!row || !column) {
throw new Error('A Cell needs a Row');
}
this._row = row;
this._column = column;
colCache.validateAddress(address);
this._address = address;
// TODO: lazy evaluation of this._value
this._value = Value.create(Cell.Types.Null, this);
this.style = this._mergeStyle(row.style, column.style, {});
...composite_xform = function (options) {
this.tag = options.tag;
this.attrs = options.attrs;
this.children = options.children;
this.map = this.children.reduce(function(map, child) {
var name = child.name || child.tag;
var tag = child.tag || child.name;
map[tag] = child;
child.name = name;
child.tag = tag;
return map;
}, {});
}n/a
super_ = function (model, name) {
}n/a
parseClose = function (name) {
if (this.parser) {
if (!this.parser.xform.parseClose(name)) {
this.model[this.parser.name] = this.parser.xform.model;
this.parser = undefined;
}
return true;
} else {
return false;
}
}...
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
resolve(self.model);
});
parser.on('error', function (error) {
...parseOpen = function (node) {
if (this.parser) {
this.parser.xform.parseOpen(node);
return true;
} else {
switch(node.name) {
case this.tag:
this.model = {};
return true;
default:
this.parser = this.map[node.name];
if (this.parser) {
this.parser.xform.parseOpen(node);
return true;
}
}
return false;
}
}...
// to make sure parses don't bleed to next iteration
this.model = model;
},
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
...parseText = function (text) {
if (this.parser) {
this.parser.xform.parseText(text);
}
}...
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
...prepare = function (model, options) {
this.children.forEach(function (child) {
child.xform.prepare(model[child.tag], options);
});
}...
};
utils.inherits(ListXform, BaseXform, {
prepare: function prepare(model, options) {
var childXform = this.childXform;
if (model) {
model.forEach(function (childModel) {
childXform.prepare(childModel, options);
});
}
},
render: function render(xmlStream, model) {
if (model && model.length) {
xmlStream.openNode(this.tag, this.$);
...reconcile = function (model, options) {
this.children.forEach(function (child) {
child.xform.prepare(model[child.tag], options);
});
}...
return false;
}
},
reconcile: function reconcile(model, options) {
if (model) {
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.reconcile(childModel, options);
});
}
}
});
},{"../../utils/utils":20,"./base-xform":25}],39:[function(require,module,exports){
...render = function (xmlStream, model) {
xmlStream.openNode(this.tag, this.attrs);
this.children.forEach(function (child) {
child.xform.render(xmlStream, model[child.name]);
});
xmlStream.closeNode();
}...
// convenience function to get the xml of this.model
// useful for manager types that are built during the prepare phase
return this.toXml(this.model);
},
toXml: function toXml(model) {
var xmlStream = new XmlStream();
this.render(xmlStream, model);
return xmlStream.xml;
}
};
},{"../../utils/promish":15,"../../utils/xml-stream":21,"sax":188}],26:[function(require,module,exports
){
/**
...function setValue(key, value, overwrite) {
if (overwrite === undefined) {
// only avoid overwrite if explicitly disabled
overwrite = true;
}
switch(key.toLowerCase()) {
case 'promise':
if (!overwrite && PromishLib.Promish) return;
PromishLib.Promish = value;
break;
}
}...
# Config
ExcelJS now supports dependency injection for the promise library.
You can restore Bluebird promises by including the following code in your module...
```javascript
ExcelJS.config.setValue('promise', require('bluebird'));
```
Please note: I have tested ExcelJS with bluebird specifically (since up until recently this was the library it used).
From the tests I have done it will not work with Q.
# Known Issues
...csv = function (workbook) {
this.workbook = workbook;
this.worksheet = null;
}n/a
createInputStream = function (options) {
options = options || {};
var worksheet = this.workbook.addWorksheet(options.sheetName);
var dateFormats = options.dateFormats || [
moment.ISO_8601,
'MM-DD-YYYY',
'YYYY-MM-DD'
];
var map = options.map || function(datum) {
if (datum == '') {
return null;
}
if (!isNaN(datum)) {
return parseFloat(datum);
}
var dt = moment(datum, dateFormats, true);
if (dt.isValid()) {
return new Date(dt.valueOf());
}
var special = SpecialValues[datum];
if (special !== undefined) {
return special;
}
return datum;
};
var csvStream = csv(options)
.on('data', function(data){
worksheet.addRow(data.map(map));
})
.on('end', function() {
csvStream.emit('worksheet', worksheet);
});
return csvStream;
}...
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
...read = function (stream, options) {
options = options || {};
var self = this;
return new PromishLib.Promish(function(resolve, reject) {
var csvStream = self.createInputStream(options)
.on('worksheet', function(worksheet) {
resolve(worksheet);
})
.on('error', function(error) {
reject(error);
});
stream.pipe(csvStream);
});
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...readFile = function (filename, options) {
var self = this;
options = options || {};
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, options);
})
.then(function(worksheet) {
stream.close();
return worksheet;
});
}...
### XLSX
#### Reading XLSX
```javascript
// read from a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
...write = function (stream, options) {
var self = this;
return new PromishLib.Promish(function(resolve, reject) {
options = options || {};
//var encoding = options.encoding || 'utf8';
//var separator = options.separator || ',';
//var quoteChar = options.quoteChar || '\'';
var worksheet = self.workbook.getWorksheet(options.sheetName || options.sheetId);
var csvStream = csv.createWriteStream(options);
stream.on('finish', function() {
resolve();
});
csvStream.on('error', function(error) {
reject(error);
});
csvStream.pipe(stream);
var dateFormat = options.dateFormat;
var map = options.map || function(value) {
if (value) {
if (value.text || value.hyperlink) {
return value.hyperlink || value.text || '';
}
if (value.formula || value.result) {
return value.result || '';
}
if (value instanceof Date) {
return dateFormat ? moment(value).format(dateFormat) : moment(value).format();
}
if (value.error) {
return value.error;
}
if (typeof value === 'object') {
return JSON.stringify(value);
}
}
return value;
};
var includeEmptyRows = (options.includeEmptyRows === undefined) || options.includeEmptyRows;
var lastRow = 1;
if (worksheet) {
worksheet.eachRow(function (row, rowNumber) {
if (includeEmptyRows) {
while (lastRow++ < rowNumber - 1) {
csvStream.write([]);
}
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...writeFile = function (filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
};
var stream = fs.createWriteStream(filename, options);
return this.write(stream, options);
}...
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
...Workbook = function () {
this.created = new Date();
this.modified = this.created;
this.properties = {};
this._worksheets = [];
this.views = [];
this._definedNames = new DefinedNames();
}...
```javascript
var Excel = require('exceljs');
```
## Create a Workbook
```javascript
var workbook = new Excel.Workbook();
```
## Set Workbook Properties
```javascript
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
...ModelContainer = function (model) {
this.model = model;
}n/a
Workbook = function () {
this.created = new Date();
this.modified = this.created;
this.properties = {};
this._worksheets = [];
this.views = [];
this._definedNames = new DefinedNames();
}...
```javascript
var Excel = require('exceljs');
```
## Create a Workbook
```javascript
var workbook = new Excel.Workbook();
```
## Set Workbook Properties
```javascript
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
...flow_control = function (options) {
this.options = options = options || {};
// Buffer queue
this.queue = [];
// Consumer streams
this.pipes = [];
// Down-stream flow-control instances
this.children = [];
// Up-stream flow-control instances
this.parent = options.parent;
// Ensure we don't flush more than once at a time
this.flushing = false;
// determine timeout for flow control delays
if (options.gc) {
var gc = options.gc;
if (gc.getTimeout) {
this.getTimeout = gc.getTimeout;
} else {
// heap size below which we don't bother delaying
var threshold = gc.threshold !== undefined ? gc.threshold : 150000000;
// convert from heapsize to ms timeout
var divisor = gc.divisor !== undefined ? gc.divisor : 500000;
this.getTimeout = function() {
var memory = process.memoryUsage();
var heapSize = memory.heapTotal;
return (heapSize < threshold) ? 0 : Math.floor(heapSize / divisor);
};
}
} else {
this.getTimeout = null;
}
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
_animate = function () {
var count = 0;
var seq = ['|', '/', '-', '\\'];
//var cr = '\033[0G';
var cr = '\u001b[0G';
return setInterval(function() {
process.stdout.write(seq[count++ % 4] + cr);
}, 100);
}...
},
_delay: function() {
// in certain situations it may be useful to delay processing (e.g. for GC)
var timeout = this.getTimeout && this.getTimeout();
var self = this;
if (timeout) {
return new PromishLib.Promish(function(resolve, reject) {
var anime = self._animate();
setTimeout(function() {
clearInterval(anime);
resolve();
}, timeout);
});
} else {
return PromishLib.Promish.resolve();
..._delay = function () {
// in certain situations it may be useful to delay processing (e.g. for GC)
var timeout = this.getTimeout && this.getTimeout();
var self = this;
if (timeout) {
return new PromishLib.Promish(function(resolve, reject) {
var anime = self._animate();
setTimeout(function() {
clearInterval(anime);
resolve();
}, timeout);
});
} else {
return PromishLib.Promish.resolve();
}
}...
_flush: function() {
// If/while not corked and we have buffers to send, send them
var self = this;
if (this.queue && !this.flushing && !this.corked) {
if (this.queue.length) {
this.flushing = true;
self._delay()
.then(function() {
return self._pipe(self.queue.shift());
})
.then(function() {
setImmediate(function() {
self.flushing = false;
self._flush();
..._flush = function () {
// If/while not corked and we have buffers to send, send them
var self = this;
if (this.queue && !this.flushing && !this.corked) {
if (this.queue.length) {
this.flushing = true;
self._delay()
.then(function() {
return self._pipe(self.queue.shift());
})
.then(function() {
setImmediate(function() {
self.flushing = false;
self._flush();
});
});
}
if (!this.stem) {
// Signal up-stream that we're ready for more data
self.emit('drain');
}
}
}...
while (this.buffers.length) {
this._pipe(this.buffers.shift());
}
}
},
uncork: function uncork() {
this.corked = false;
this._flush();
},
end: function end(chunk, encoding, callback) {
var self = this;
var writeComplete = function writeComplete(error) {
if (error) {
callback(error);
} else {
..._pipe = function (chunk) {
var self = this;
// Write chunk to all pipes. A chunk with no data is the end
var promises = [];
this.pipes.forEach(function(pipe) {
if (chunk.data) {
if (pipe.sync) {
pipe.stream.write(chunk.data, chunk.encoding);
} else {
promises.push(self._write(pipe.stream, chunk.data, chunk.encoding));
}
} else {
pipe.stream.end();
}
});
if (!promises.length) {
promises.push(PromishLib.Promish.resolve());
}
return PromishLib.Promish.all(promises)
.then(function() {
try {
chunk.callback();
}
catch(e) {
}
});
}...
}
// now, do something with the chunk
if (this.pipes.length) {
if (this.batch) {
this._writeToBuffers(chunk);
while (!this.corked && this.buffers.length > 1) {
this._pipe(this.buffers.shift());
}
} else {
if (!this.corked) {
this._pipe(chunk).then(callback);
} else {
this._writeToBuffers(chunk);
process.nextTick(callback);
..._write = function (dst, data, encoding) {
// Write to a single destination and return a promise
return new PromishLib.Promish(function(resolve, reject) {
dst.write(data, encoding, function(error) {
if (error) {
reject(error);
} else {
resolve();
}
});
});
}...
// Write chunk to all pipes. A chunk with no data is the end
var promises = [];
this.pipes.forEach(function(pipe) {
if (chunk.data) {
if (pipe.sync) {
pipe.stream.write(chunk.data, chunk.encoding);
} else {
promises.push(self._write(pipe.stream, chunk.data, chunk.encoding));
}
} else {
pipe.stream.end();
}
});
if (!promises.length) {
promises.push(PromishLib.Promish.resolve());
...createChild = function () {
// Create a down-stream flow-control
var self = this;
var options = Object.assign({parent: this}, this.options);
var child = new FlowControl(options);
this.children.push(child);
child.on('drain', function() {
// a child is ready for more
self._flush();
});
child.on('finish', function() {
// One child has finished it's stream. Remove it and continue
self.children = self.children.filter(function(item) {
return item !== child;
});
self._flush();
});
return child;
}...
}
});
parser.on('end', function() {
self.emit('finished');
});
// create a down-stream flow-control to regulate the stream
var flowControl = this.workbook.flowControl.createChild();
flowControl.pipe(parser, {sync: true});
entry.pipe(flowControl);
}
});
...end = function (chunk, encoding, callback) {
// Signal from up-stream
var self = this;
this.queue.push({
callback: function() {
self.queue = null;
self.emit('finish');
}
});
this._flush();
}...
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
},
writeFile: function writeFile(filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
...pipe = function (stream, options) {
options = options || {};
// some streams don't call callbacks
var sync = options.sync || false;
this.pipes.push({
stream: stream,
sync: sync
});
}...
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
...unpipe = function (stream) {
this.pipes = this.pipes.filter(function(pipe) {
return pipe.stream !== stream;
});
}...
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
...write = function (data, encoding, callback) {
// Called by up-stream pipe
if (encoding instanceof Function) {
callback = encoding;
encoding = 'utf8';
}
callback = callback || utils.nop;
if (!this.queue) {
throw new Error('Cannot write to stream after end');
}
// Always queue chunks and then flush
this.queue.push({
data: data,
encoding: encoding,
callback: callback
});
this._flush();
// restrict further incoming data if we have backed up buffers or
// the children are still busy
var stemFlow = this.corked || (this.queue.length > 3);
return !stemFlow;
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...hyperlink_reader = function (workbook, id) {
// in a workbook, each sheet will have a number
this.id = id;
this._workbook = workbook;
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
each = function (fn) {
return this.hyperlinks.forEach(fn);
}...
this.removeEx(location, name);
},
removeEx: function removeEx(location, name) {
var matrix = this.getMatrix(name);
matrix.removeCellEx(location);
},
removeAllNames: function removeAllNames(location) {
_.each(this.matrixMap, function (matrix) {
matrix.removeCellEx(location);
});
},
forEach: function forEach(callback) {
_.each(this.matrixMap, function (matrix, name) {
matrix.forEach(function (cell) {
...read = function (entry, options) {
var self = this;
var emitHyperlinks = false;
var hyperlinks = null;
switch(options.hyperlinks) {
case 'emit':
emitHyperlinks = true;
break;
case 'cache':
this.hyperlinks = hyperlinks = {};
break;
}
if (!emitHyperlinks && !hyperlinks) {
entry.autodrain();
return;
}
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.Hyperlink:
var relationship = {
type: Enums.RelationshipType.Styles,
rId: rId,
target: node.attributes.Target,
targetMode: node.attributes.TargetMode
};
if (emitHyperlinks) {
self.emit('hyperlink', relationship);
} else {
hyperlinks[relationship.rId] = relationship;
}
break;
}
}
});
parser.on('end', function() {
self.emit('finished');
});
// create a down-stream flow-control to regulate the stream
var flowControl = this.workbook.flowControl.createChild();
flowControl.pipe(parser, {sync: true});
entry.pipe(flowControl);
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...hyperlink_writer = function (options) {
// in a workbook, each sheet will have a number
this.id = options.id;
// keep record of all hyperlinks
this._hyperlinks = [];
this._workbook = options.workbook;
}n/a
_writeClose = function () {
this.stream.write('</Relationships>');
}...
// and write to stream
this._writeRelationship(hyperlink);
},
commit: function() {
if (this._hyperlinks.length) {
// write xml utro
this._writeClose();
// and close stream
this.stream.end();
}
},
// ================================================================================
_writeOpen: function() {
..._writeOpen = function () {
this.stream.write(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
}...
each: function(fn) {
return this._hyperlinks.forEach(fn);
},
push: function(hyperlink) {
// if first hyperlink, must open stream and write xml intro
if (!this._hyperlinks.length) {
this._writeOpen();
}
// store sheet stuff for later
this._hyperlinks.push({
address: hyperlink.address,
rId: hyperlink.rId
});
..._writeRelationship = function (hyperlink) {
this.stream.write(
'<Relationship' +
' Id="' + hyperlink.rId + '"' +
' Type="' + hyperlink.type + '"' +
' Target="' + utils.xmlEncode(hyperlink.target) + '"' +
' TargetMode="' + hyperlink.targetMode + '"' +
'/>');
}...
// store sheet stuff for later
this._hyperlinks.push({
address: hyperlink.address,
rId: hyperlink.rId
});
// and write to stream
this._writeRelationship(hyperlink);
},
commit: function() {
if (this._hyperlinks.length) {
// write xml utro
this._writeClose();
// and close stream
...commit = function () {
if (this._hyperlinks.length) {
// write xml utro
this._writeClose();
// and close stream
this.stream.end();
}
}...
// Note: this operation will not affect other rows
row.splice(3,2);
// remove one cell and insert two more (cells to the right of the cut cell will be shifted right)
row.splice(4,1,'new value 1', 'new value 2');
// Commit a completed row to stream
row.commit();
// row metrics
var rowSize = row.cellCount;
var numValues = row.actualCellCount;
```
## Handling Individual Cells
...each = function (fn) {
return this._hyperlinks.forEach(fn);
}...
this.removeEx(location, name);
},
removeEx: function removeEx(location, name) {
var matrix = this.getMatrix(name);
matrix.removeCellEx(location);
},
removeAllNames: function removeAllNames(location) {
_.each(this.matrixMap, function (matrix) {
matrix.removeCellEx(location);
});
},
forEach: function forEach(callback) {
_.each(this.matrixMap, function (matrix, name) {
matrix.forEach(function (cell) {
...push = function (hyperlink) {
// if first hyperlink, must open stream and write xml intro
if (!this._hyperlinks.length) {
this._writeOpen();
}
// store sheet stuff for later
this._hyperlinks.push({
address: hyperlink.address,
rId: hyperlink.rId
});
// and write to stream
this._writeRelationship(hyperlink);
}...
width: column.width,
style: column.style,
isCustomWidth: column.isCustomWidth,
hidden: column.hidden,
outlineLevel: column.outlineLevel,
collapsed: column.collapsed
};
cols.push(col);
} else {
col.max = index + 1;
}
}
});
}
return cols.length ? cols : undefined;
...line_buffer = function (options) {
events.EventEmitter.call(this);
this.encoding = options.encoding;
this.buffer = null;
// part of cork/uncork
this.corked = false;
this.queue = [];
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
_flush = function () {
if (!this.corked) {
this.queue.forEach(function(line) {
this.emit('line', line);
});
this.queue = [];
}
}...
while (this.buffers.length) {
this._pipe(this.buffers.shift());
}
}
},
uncork: function uncork() {
this.corked = false;
this._flush();
},
end: function end(chunk, encoding, callback) {
var self = this;
var writeComplete = function writeComplete(error) {
if (error) {
callback(error);
} else {
...cork = function () {
this.corked = true;
}...
// ==========================================================================
// Stream.Writable interface
write: function write(data, encoding, callback) {
return this.stream.write(data, encoding, callback);
},
cork: function cork() {
return this.stream.cork();
},
uncork: function uncork() {
return this.stream.uncork();
},
end: function end() {
return this.stream.end();
}
...end = function (chunk, encoding, callback) {
if (this.buffer) {
this.emit('line', this.buffer);
this.buffer = null;
}
this.emit('done');
}...
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
},
writeFile: function writeFile(filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
...setDefaultEncoding = function (encoding) {
// ?
}n/a
uncork = function () {
this.corked = false;
this._flush();
// tell the source I'm ready again
this.emit('drain');
}...
write: function write(data, encoding, callback) {
return this.stream.write(data, encoding, callback);
},
cork: function cork() {
return this.stream.cork();
},
uncork: function uncork() {
return this.stream.uncork();
},
end: function end() {
return this.stream.end();
}
});
// =============================================================================
...write = function (chunk, encoding, callback) {
// find line or lines in chunk and emit them if not corked
// or queue them if corked
var data = this.buffer ? this.buffer + chunk : chunk;
var lines = data.split(/\r?\n/g);
// save the last line
this.buffer = lines.pop();
lines.forEach(function(line) {
if (this.corked) {
this.queue.push(line);
} else {
this.emit('line', line);
}
});
return !this.corked;
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...list_xform = function (options) {
this.tag = options.tag;
this.count = options.count;
this.empty = options.empty;
this.$count = options.$count || 'count';
this.$ = options.$;
this.childXform = options.childXform;
}n/a
super_ = function (model, name) {
}n/a
parseClose = function (name) {
if (this.parser) {
if (!this.parser.parseClose(name)) {
this.model.push(this.parser.model);
this.parser = undefined;
}
return true;
} else {
return false;
}
}...
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
resolve(self.model);
});
parser.on('error', function (error) {
...parseOpen = function (node) {
if (this.parser) {
this.parser.parseOpen(node);
return true;
} else {
switch(node.name) {
case this.tag:
this.model = [];
return true;
default:
if (this.childXform.parseOpen(node)) {
this.parser = this.childXform;
return true;
} else {
return false;
}
}
}
}...
// to make sure parses don't bleed to next iteration
this.model = model;
},
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
...parseText = function (text) {
if (this.parser) {
this.parser.parseText(text);
}
}...
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
...prepare = function (model, options) {
var childXform = this.childXform;
if (model) {
model.forEach(function (childModel) {
childXform.prepare(childModel, options);
});
}
}...
};
utils.inherits(ListXform, BaseXform, {
prepare: function prepare(model, options) {
var childXform = this.childXform;
if (model) {
model.forEach(function (childModel) {
childXform.prepare(childModel, options);
});
}
},
render: function render(xmlStream, model) {
if (model && model.length) {
xmlStream.openNode(this.tag, this.$);
...reconcile = function (model, options) {
if (model) {
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.reconcile(childModel, options);
});
}
}...
return false;
}
},
reconcile: function reconcile(model, options) {
if (model) {
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.reconcile(childModel, options);
});
}
}
});
},{"../../utils/utils":20,"./base-xform":25}],39:[function(require,module,exports){
...render = function (xmlStream, model) {
if (model && model.length) {
xmlStream.openNode(this.tag, this.$);
if (this.count) {
xmlStream.addAttribute(this.$count, model.length);
}
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.render(xmlStream, childModel);
});
xmlStream.closeNode();
} else if (this.empty) {
xmlStream.leafNode(this.tag);
}
}...
// convenience function to get the xml of this.model
// useful for manager types that are built during the prepare phase
return this.toXml(this.model);
},
toXml: function toXml(model) {
var xmlStream = new XmlStream();
this.render(xmlStream, model);
return xmlStream.xml;
}
};
},{"../../utils/promish":15,"../../utils/xml-stream":21,"sax":188}],26:[function(require,module,exports
){
/**
...class Promish extends Base {
constructor(f) {
if (f instanceof Promish) {
return f;
} else if ((f instanceof Promise) || (f.then instanceof Function)) {
super((resolve, reject) => f.then(resolve, reject));
} else if (f instanceof Error) {
// sugar for 'rethrow'
super((resolve, reject) => reject(f));
} else if (f instanceof Function) {
super(f);
} else {
// anything else, resolve with value
super(resolve => resolve(f));
}
}
finally(h) {
return this.then(
value => Promish.resolve(h()).then(() => value),
error => Promish.resolve(h()).then(() => Promish.reject(error))
);
}
catch() {
// extend catch with type-aware or matcher handling
var args = Array.from(arguments);
var h = args.pop();
return this.then(undefined, function(error) {
// default catch - no matchers. Just return handler result
if (!args.length) {
return h(error);
}
//console.log('catch matcher', error)
// search for a match in argument order and return handler result if found
for (var i = 0; i < args.length; i++) {
var matcher = args[i];
if (isErrorClass(matcher)) {
if (error instanceof matcher) {
return h(error);
}
} else if (matcher instanceof Function) {
//console.log('matcher function')
if (matcher(error)) {
//console.log('matched!!')
return h(error);
}
}
}
// no match was found send this error to the next promise handler in the chain
return new Promish((resolve, reject) => reject(error));
});
}
delay(timeout) {
return this.then(function(value) {
return new Promish(function(resolve) {
setTimeout(function() {
resolve(value);
}, timeout);
});
});
}
map(f) {
return this.then(function(values) {
return Promish.map(values, f);
});
}
static map(values, f) {
return Promish.all(
values.map((v,i) => Promish.resolve(v).then(v2 => f(v2, i, values.length)))
);
}
reduce(f, val0) {
return this.then(function(values) {
return Promish.reduce(values, f, val0);
});
}
static reduce(values, f, val0) {
var promise;
var count = 0;
if (val0 !== undefined) {
promise = Promish.resolve(val0);
} else if (values.length > 1) {
promise = Promish.resolve(values[count++]);
} else {
return Promish.resolve(values[0]);
}
while (count < values.length) (function(i) {
promise = promise.then(function(value) {
return Promish.resolve(values[i]).then(v2 => f(value, v2, i));
});
})(count++);
return promise;
}
spread(f) {
return this.then(function(values) {
return Promish.all(values);
}).then(function(values) {
return f.apply(undefined, values);
});
}
static delay(timeout, value) {
return new Promish(resolve => {
setTimeout(function() {
resolve(value);
}, timeout);
});
}
static resolve(value) {
return new Promish(resolve => {
resolve(value);
});
}
static reject(error) {
return new Promish((resolve, reject) => {
reject(error);
});
}
// Wrap a synchronous method and resolve with its return value
static method(f) {
return function() {
var self = this; // is this necessary?
var args = Array.from(arguments);
return new Promish(resolve => resolve(f.apply(self, args)));
};
}
//
static apply(f, args) {
// take a copy of args because a) might not be Array and b) no side-effects
args = Array.from(args);
return new Promish(function(resolve, reject) {
args.push(function () {
var error = Array.prototype.shift.apply(arguments);
if (error) {
reject(error);
} else {
if (arguments.length === 1) { ......
stream.close();
return worksheet;
});
},
read: function read(stream, options) {
options = options || {};
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
var csvStream = self.createInputStream(options).on('worksheet', function (worksheet) {
resolve(worksheet);
}).on('error', function (error) {
reject(error);
});
...slideFormula = function (formula, fromCell, toCell) {
var offset = colCache.decode(fromCell);
var to = colCache.decode(toCell);
return formula.replace(replacementCandidateRx, function(refMatch, sheet, sheetMaybe, addrPart, trailingParen) {
if (trailingParen) {
return refMatch;
}
var match = CRrx.exec(addrPart)
if (match) {
var colDollar = match[1];
var colStr = match[2].toUpperCase();
var rowDollar = match[3];
var rowStr = match[4];
if (colStr.length > 3 || (colStr.length === 3 && colStr > 'XFD')) {
// > XFD is the highest col number in excel 2007 and beyond, so this is a named range
return refMatch;
}
var col = colCache.l2n(colStr);
var row = parseInt(rowStr, 10);
if (!colDollar) {
col += to.col - offset.col;
}
if (!rowDollar) {
row += to.row - offset.row;
}
var res = (sheet || '') + (colDollar || '') + colCache.n2l(col) + (rowDollar || '') + row;
return res;
}
return refMatch;
});
}n/a
shared_strings = function () {
this._values = [];
this._totalRefs = 0;
this._hash = {}
}n/a
add = function (value) {
var index = this._hash[value];
if (index === undefined) {
index = this._hash[value] = this._values.length;
this._values.push(value);
}
this._totalRefs++;
return index;
}...
},
get dataValidation() {
return this._dataValidations.find(this.address);
},
set dataValidation(value) {
this._dataValidations.add(this.address, value);
},
// =========================================================================
// Model stuff
get model() {
var model = this._value.model;
...getString = function (index) {
return this._values[index];
}...
if (model.styleId !== undefined) {
model.styleId = undefined;
}
switch (model.type) {
case Enums.ValueType.String:
if (typeof model.value === 'number') {
model.value = options.sharedStrings.getString(model.value);
}
if (model.value.richText) {
model.type = Enums.ValueType.RichText;
}
break;
case Enums.ValueType.Number:
if (style && utils.isDateFmt(style.numFmt)) {
...static_xform = function (model) {
// This class is an optimisation for static (unimportant and unchanging) xml
// It is stateless - apart from its static model and so can be used as a singleton
// Being stateless - it will only track entry to and exit from it's root xml tag during parsing and nothing else
// Known issues:
// since stateless - parseOpen always returns true. Parent xform must know when to start using this xform
// if the root tag is recursive, the parsing will behave unpredictably
this._model = model;
}n/a
super_ = function (model, name) {
}n/a
parseClose = function (name) {
switch(name) {
case this._model.tag:
return false;
default:
return true;
}
}...
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
resolve(self.model);
});
parser.on('error', function (error) {
...parseOpen = function () {
return true;
}...
// to make sure parses don't bleed to next iteration
this.model = model;
},
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
...parseText = function () {
}...
parse: function parse(parser) {
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
parser.on('opentag', function (node) {
self.parseOpen(node);
});
parser.on('text', function (text) {
self.parseText(text);
});
parser.on('closetag', function (name) {
if (!self.parseClose(name)) {
resolve(self.model);
}
});
parser.on('end', function () {
...render = function (xmlStream) {
if (!this._xml) {
var stream = new XmlStream();
build(stream, this._model);
this._xml = stream.xml;
}
xmlStream.writeXml(this._xml);
}...
// convenience function to get the xml of this.model
// useful for manager types that are built during the prepare phase
return this.toXml(this.model);
},
toXml: function toXml(model) {
var xmlStream = new XmlStream();
this.render(xmlStream, model);
return xmlStream.xml;
}
};
},{"../../utils/promish":15,"../../utils/xml-stream":21,"sax":188}],26:[function(require,module,exports
){
/**
...stream_base64 = function (options) {
options = options || {};
// consuming pipe streams go here
this.pipes = [];
}n/a
function Duplex(options) {
if (!(this instanceof Duplex))
return new Duplex(options);
Readable.call(this, options);
Writable.call(this, options);
if (options && options.readable === false)
this.readable = false;
if (options && options.writable === false)
this.writable = false;
this.allowHalfOpen = true;
if (options && options.allowHalfOpen === false)
this.allowHalfOpen = false;
this.once('end', onend);
}n/a
cork = function () {
}...
// ==========================================================================
// Stream.Writable interface
write: function write(data, encoding, callback) {
return this.stream.write(data, encoding, callback);
},
cork: function cork() {
return this.stream.cork();
},
uncork: function uncork() {
return this.stream.uncork();
},
end: function end() {
return this.stream.end();
}
...end = function (chunk, encoding, callback) {
}...
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
},
writeFile: function writeFile(filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
...isPaused = function () {
}...
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
...pause = function () {
}...
read: function read(size) {
return this.stream.read(size);
},
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
...pipe = function (destination, options) {
// add destination to pipe list & write current buffer
this.pipes.push(destination);
}...
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
...read = function (size) {
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...resume = function () {
}...
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
...setEncoding = function (encoding) {
// causes stream.read or stream.on('data) to return strings of encoding instead of Buffer objects
this.encoding = encoding;
}...
// ==========================================================================
// Stream.Readable interface
read: function read(size) {
return this.stream.read(size);
},
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
...uncork = function () {
}...
write: function write(data, encoding, callback) {
return this.stream.write(data, encoding, callback);
},
cork: function cork() {
return this.stream.cork();
},
uncork: function uncork() {
return this.stream.uncork();
},
end: function end() {
return this.stream.end();
}
});
// =============================================================================
...unpipe = function (destination) {
// remove destination from pipe list
this.pipes = this.pipes.filter(function(pipe) {
return pipe !== destination;
});
}...
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
...unshift = function (chunk) {
// some numpty has read some data that's not for them and they want to put it back!
// Might implement this some day
throw new Error('Not Implemented');
}...
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
});
// =============================================================================
...wrap = function (stream) {
// not implemented
throw new Error('Not Implemented');
}...
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
});
// =============================================================================
module.exports = {
ZipReader: ZipReader,
...write = function (data, encoding, callback) {
if (encoding instanceof Function) {
callback = encoding;
encoding = 'utf8';
}
callback = callback || utils.nop;
return true;
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...stream_buf = function (options) {
options = options || {};
this.bufSize = options.bufSize || 1024 * 1024;
this.buffers = [];
// batch mode fills a buffer completely before passing the data on
// to pipes or 'readable' event listeners
this.batch = options.batch || false;
this.corked = false;
// where in the current writable buffer we're up to
this.inPos = 0;
// where in the current readable buffer we've read up to
this.outPos = 0;
// consuming pipe streams go here
this.pipes = [];
// controls emit('data')
this.paused = false;
this.encoding = null;
}n/a
function Duplex(options) {
if (!(this instanceof Duplex))
return new Duplex(options);
Readable.call(this, options);
Writable.call(this, options);
if (options && options.readable === false)
this.readable = false;
if (options && options.writable === false)
this.writable = false;
this.allowHalfOpen = true;
if (options && options.allowHalfOpen === false)
this.allowHalfOpen = false;
this.once('end', onend);
}n/a
_flush = function (destination) {
// if we have comsumers...
if (this.pipes.length) {
// and there's stuff not written
while (this.buffers.length) {
this._pipe(this.buffers.shift());
}
}
}...
while (this.buffers.length) {
this._pipe(this.buffers.shift());
}
}
},
uncork: function uncork() {
this.corked = false;
this._flush();
},
end: function end(chunk, encoding, callback) {
var self = this;
var writeComplete = function writeComplete(error) {
if (error) {
callback(error);
} else {
..._getWritableBuffer = function () {
if (this.buffers.length) {
var last = this.buffers[this.buffers.length - 1];
if (!last.full) {
return last;
}
}
var buf = new ReadWriteBuf(this.bufSize);
this.buffers.push(buf);
return buf;
}...
return promises.length ? PromishLib.Promish.all(promises).then(utils.nop) : PromishLib.Promish.resolve();
},
_writeToBuffers: function _writeToBuffers(chunk) {
var inPos = 0;
var inLen = chunk.length;
while (inPos < inLen) {
// find writable buffer
var buffer = this._getWritableBuffer();
// write some data
inPos += buffer.write(chunk, inPos, inLen - inPos);
}
},
write: function write(data, encoding, callback) {
if (encoding instanceof Function) {
..._pipe = function (chunk) {
var write = function(pipe) {
return new PromishLib.Promish(function(resolve, reject) {
pipe.write(chunk.toBuffer(), function() {
resolve();
});
});
};
var promises = this.pipes.map(write);
return promises.length ?
PromishLib.Promish.all(promises).then(utils.nop) :
PromishLib.Promish.resolve();
}...
}
// now, do something with the chunk
if (this.pipes.length) {
if (this.batch) {
this._writeToBuffers(chunk);
while (!this.corked && this.buffers.length > 1) {
this._pipe(this.buffers.shift());
}
} else {
if (!this.corked) {
this._pipe(chunk).then(callback);
} else {
this._writeToBuffers(chunk);
process.nextTick(callback);
..._writeToBuffers = function (chunk) {
var inPos = 0;
var inLen = chunk.length;
while (inPos < inLen) {
// find writable buffer
var buffer = this._getWritableBuffer();
// write some data
inPos += buffer.write(chunk, inPos, inLen - inPos);
}
}...
// assume string
chunk = new StringChunk(data, encoding);
}
// now, do something with the chunk
if (this.pipes.length) {
if (this.batch) {
this._writeToBuffers(chunk);
while (!this.corked && this.buffers.length > 1) {
this._pipe(this.buffers.shift());
}
} else {
if (!this.corked) {
this._pipe(chunk).then(callback);
} else {
...cork = function () {
this.corked = true;
}...
// ==========================================================================
// Stream.Writable interface
write: function write(data, encoding, callback) {
return this.stream.write(data, encoding, callback);
},
cork: function cork() {
return this.stream.cork();
},
uncork: function uncork() {
return this.stream.uncork();
},
end: function end() {
return this.stream.end();
}
...end = function (chunk, encoding, callback) {
var self = this;
var writeComplete = function(error) {
if (error) {
callback(error);
} else {
self._flush();
self.pipes.forEach(function(pipe) {
pipe.end();
});
self.emit('finish');
}
};
if (chunk) {
this.write(chunk, encoding, writeComplete);
} else {
writeComplete();
}
}...
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
},
writeFile: function writeFile(filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
...isPaused = function () {
return this.paused ? true : false;
}...
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
...pause = function () {
this.paused = true;
}...
read: function read(size) {
return this.stream.read(size);
},
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
...pipe = function (destination, options) {
// add destination to pipe list & write current buffer
this.pipes.push(destination);
if (!this.paused && this.buffers.length) {
this.end();
}
}...
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
...read = function (size) {
// read min(buffer, size || infinity)
if (size) {
var buffers = [];
while (size && this.buffers.length && !this.buffers[0].eod) {
var first = this.buffers[0];
var buffer = first.read(size);
size -= buffer.length;
buffers.push(buffer);
if (first.eod && first.full) {
this.buffers.shift();
}
}
return Buffer.concat(buffers);
} else {
var buffers = this.buffers.map(function(buffer) {
return buffer.toBuffer();
}).filter(function(data) {
return data;
});
this.buffers = [];
return Buffer.concat(buffers);
}
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...resume = function () {
this.paused = false;
}...
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
...setEncoding = function (encoding) {
// causes stream.read or stream.on('data) to return strings of encoding instead of Buffer objects
this.encoding = encoding;
}...
// ==========================================================================
// Stream.Readable interface
read: function read(size) {
return this.stream.read(size);
},
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
...uncork = function () {
this.corked = false;
this._flush();
}...
write: function write(data, encoding, callback) {
return this.stream.write(data, encoding, callback);
},
cork: function cork() {
return this.stream.cork();
},
uncork: function uncork() {
return this.stream.uncork();
},
end: function end() {
return this.stream.end();
}
});
// =============================================================================
...unpipe = function (destination) {
// remove destination from pipe list
this.pipes = this.pipes.filter(function(pipe) {
return pipe !== destination;
});
}...
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
...unshift = function (chunk) {
// some numpty has read some data that's not for them and they want to put it back!
// Might implement this some day
throw new Error('Not Implemented');
}...
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
});
// =============================================================================
...wrap = function (stream) {
// not implemented
throw new Error('Not Implemented');
}...
unpipe: function unpipe(destination) {
return this.stream.unpipe(destination);
},
unshift: function unshift(chunk) {
return this.stream.unshift(chunk);
},
wrap: function wrap(stream) {
return this.stream.wrap(stream);
}
});
// =============================================================================
module.exports = {
ZipReader: ZipReader,
...write = function (data, encoding, callback) {
if (encoding instanceof Function) {
callback = encoding;
encoding = 'utf8';
}
callback = callback || utils.nop;
// encapsulate data into a chunk
var chunk;
if (data instanceof StringBuf) {
chunk = new StringBufChunk(data);
} else if (data instanceof Buffer) {
chunk = new BufferChunk(data);
} else {
// assume string
chunk = new StringChunk(data, encoding);
}
// now, do something with the chunk
if (this.pipes.length) {
if (this.batch) {
this._writeToBuffers(chunk);
while (!this.corked && (this.buffers.length > 1)) {
this._pipe(this.buffers.shift());
}
} else {
if (!this.corked) {
this._pipe(chunk).then(callback);
} else {
this._writeToBuffers(chunk);
process.nextTick(callback);
}
}
} else {
if (!this.paused) {
this.emit('data', chunk.toBuffer());
}
this._writeToBuffers(chunk);
this.emit('readable');
}
return true;
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...stream_converter = function (inner, options) {
this.inner = inner;
options = options || {};
this.innerEncoding = (options.innerEncoding || 'UTF8').toUpperCase();
this.outerEncoding = (options.outerEncoding || 'UTF8').toUpperCase();
this.innerBOM = options.innerBOM || null;
this.outerBOM = options.outerBOM || null;
this.writeStarted = false;
}n/a
addListener = function (event, handler) {
this.inner.addListener(event, handler);
}...
if (this.innerEncoding != this.outerEncoding) {
data = jconv.convert(data, this.innerEncoding, this.outerEncoding);
}
return data;
};
StreamConverter.prototype.addListener = function(event, handler) {
this.inner.addListener(event, handler);
};
StreamConverter.prototype.removeListener = function(event, handler) {
this.inner.removeListener(event, handler);
}
StreamConverter.prototype.write = function(data, encoding, callback) {
...close = function () {
this.inner.close();
}...
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, options);
}).then(function (worksheet) {
stream.close();
return worksheet;
});
},
read: function read(stream, options) {
options = options || {};
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
...convertInwards = function (data) {
var self = this;
if (data) {
if (typeof(data) == 'string') {
data = new Buffer(data, this.outerEncoding);
}
if (this.innerEncoding != this.outerEncoding) {
data = jconv.convert(data, this.outerEncoding, this.innerEncoding);
}
}
return data;
}...
data = bomless;
}
this.writeStarted = true;
}
this.inner.write(
this.convertInwards(data),
encoding ? this.innerEncoding : undefined,
callback);
};
StreamConverter.prototype.read = function() {
// TBD
};
...convertOutwards = function (data) {
var self = this;
if (typeof(data) == 'string') {
data = new Buffer(data, this.innerEncoding);
}
if (this.innerEncoding != this.outerEncoding) {
data = jconv.convert(data, this.innerEncoding, this.outerEncoding);
}
return data;
}...
};
StreamConverter.prototype.on = function(type, callback) {
var self = this;
switch (type) {
case 'data':
this.inner.on('data', function(chunk) {
callback(self.convertOutwards(chunk));
});
return this;
default:
this.inner.on(type, callback);
return this;
}
};
...emit = function (type, value) {
this.inner.emit(type, value);
}...
}
return datum;
};
var csvStream = csv(options).on('data', function (data) {
worksheet.addRow(data.map(map));
}).on('end', function () {
csvStream.emit('worksheet', worksheet);
});
return csvStream;
},
write: function write(stream, options) {
var self = this;
...end = function (chunk, encoding, callback) {
this.inner.end(
this.convertInwards(chunk),
this.innerEncoding,
callback);
}...
}
var values = row.values;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
},
writeFile: function writeFile(filename, options) {
options = options || {};
var streamOptions = {
encoding: options.encoding || 'utf8'
...on = function (type, callback) {
var self = this;
switch (type) {
case 'data':
this.inner.on('data', function(chunk) {
callback(self.convertOutwards(chunk));
});
return this;
default:
this.inner.on(type, callback);
return this;
}
}...
});
},
read: function read(stream, options) {
options = options || {};
var self = this;
return new PromishLib.Promish(function (resolve, reject) {
var csvStream = self.createInputStream(options).on('worksheet', function (
worksheet) {
resolve(worksheet);
}).on('error', function (error) {
reject(error);
});
stream.pipe(csvStream);
});
...once = function (type, callback) {
this.inner.once(type, callback);
}...
default:
this.inner.on(type, callback);
return this;
}
};
StreamConverter.prototype.once = function(type, callback) {
this.inner.once(type, callback);
};
StreamConverter.prototype.end = function(chunk, encoding, callback) {
this.inner.end(
this.convertInwards(chunk),
this.innerEncoding,
callback);
...pipe = function (destination, options) {
var reverseConverter = new StreamConverter(destination, {
innerEncoding: this.outerEncoding,
outerEncoding: this.innerEncoding,
innerBOM: this.outerBOM,
outerBOM: this.innerBOM
});
this.inner.pipe(reverseConverter, options)
}...
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
...read = function () {
// TBD
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...removeListener = function (event, handler) {
this.inner.removeListener(event, handler);
}...
};
StreamConverter.prototype.addListener = function(event, handler) {
this.inner.addListener(event, handler);
};
StreamConverter.prototype.removeListener = function(event, handler) {
this.inner.removeListener(event, handler);
}
StreamConverter.prototype.write = function(data, encoding, callback) {
if (encoding instanceof Function) {
callback = encoding;
encoding = undefined;
}
...write = function (data, encoding, callback) {
if (encoding instanceof Function) {
callback = encoding;
encoding = undefined;
}
if (!this.writeStarted) {
// if inner encoding has BOM, write it now
if (this.innerBOM) {
this.inner.write(this.innerBOM);
}
// if outer encoding has BOM, delete it now
if (this.outerBOM) {
if (data.length <= this.outerBOM.length) {
if (callback) { callback(); }
return;
}
var bomless = new Buffer(data.length - this.outerBOM.length);
data.copy(bomless, 0, this.outerBOM.length, data.length);
data = bomless;
}
this.writeStarted = true;
}
this.inner.write(
this.convertInwards(data),
encoding ? this.innerEncoding : undefined,
callback);
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...string_buf = function (options) {
this._buf = new Buffer((options && options.size) || 16384);
this._encoding = (options && options.encoding) || 'utf8';
// where in the buffer we are at
this._inPos = 0;
// for use by toBuffer()
this._buffer = undefined;
}n/a
_grow = function (min) {
for (var size = this._buf.length * 2; size < min; size *= 2);
var buf = new Buffer(size);
this._buf.copy(buf, 0);
this._buf = buf;
}...
addText: function addText(text) {
this._buffer = undefined;
var inPos = this._inPos + this._buf.write(text, this._inPos, this._encoding);
// if we've hit (or nearing capacity), grow the buf
while (inPos >= this._buf.length - 4) {
this._grow(this._inPos + text.length);
// keep trying to write until we've completely written the text
inPos = this._inPos + this._buf.write(text, this._inPos, this._encoding);
}
this._inPos = inPos;
},
...addStringBuf = function (inBuf) {
if (inBuf.length) {
this._buffer = undefined;
if (this.length + inBuf.length > this.capacity) {
this._grow(this.length + inBuf.length);
}
inBuf._buf.copy(this._buf, this._inPos, 0, inBuf.length);
this._inPos += inBuf.length;
}
}n/a
addText = function (text) {
this._buffer = undefined;
var inPos = this._inPos + this._buf.write(text, this._inPos, this._encoding);
// if we've hit (or nearing capacity), grow the buf
while (inPos >= this._buf.length - 4) {
this._grow(this._inPos + text.length);
// keep trying to write until we've completely written the text
inPos = this._inPos + this._buf.write(text, this._inPos, this._encoding);
}
this._inPos = inPos;
}...
// index merge
this._merges.push(dimensions);
},
// ================================================================================
_write: function(text) {
xml.reset();
xml.addText(text);
this.stream.write(xml);
},
_writeSheetProperties: function(xmlBuf, properties, pageSetup) {
var sheetPropertiesModel = {
tabColor: properties && properties.tabColor,
pageSetup: pageSetup && pageSetup.fitToPage ? {
fitToPage: pageSetup.fitToPage
...reset = function (position) {
position = position || 0;
this._buffer = undefined;
this._inPos = position;
}...
},
parseOpen: function parseOpen(node) {
if (this.parser) {
this.parser.parseOpen(node);
return true;
} else if (node.name === this.tag) {
this.map.tabColor.reset();
this.map.pageSetUpPr.reset();
return true;
} else if (this.map[node.name]) {
this.parser = this.map[node.name];
this.parser.parseOpen(node);
return true;
} else {
...toBuffer = function () {
// return the current data as a single enclosing buffer
if (!this._buffer) {
this._buffer = new Buffer(this.length);
this._buf.copy(this._buffer, 0, 0, this.length);
}
return this._buffer;
}...
// data chunks - encapsulating incoming data
var StringChunk = function StringChunk(data, encoding) {
this._data = data;
this._encoding = encoding;
};
StringChunk.prototype = {
get length() {
return this.toBuffer().length;
},
// copy to target buffer
copy: function copy(target, targetOffset, offset, length) {
return this.toBuffer().copy(target, targetOffset, offset, length);
},
toBuffer: function toBuffer() {
if (!this._buffer) {
...string_builder = function (options) {
this.reset();
}n/a
addStringBuf = function (inBuf) {
this._buf.push(inBuf.toString());
}n/a
addText = function (text) {
this._buf.push(text);
}...
// index merge
this._merges.push(dimensions);
},
// ================================================================================
_write: function(text) {
xml.reset();
xml.addText(text);
this.stream.write(xml);
},
_writeSheetProperties: function(xmlBuf, properties, pageSetup) {
var sheetPropertiesModel = {
tabColor: properties && properties.tabColor,
pageSetup: pageSetup && pageSetup.fitToPage ? {
fitToPage: pageSetup.fitToPage
...reset = function (position) {
if (position) {
while (this._buf.length > position) {
this._buf.pop();
}
} else {
this._buf = [];
}
}...
},
parseOpen: function parseOpen(node) {
if (this.parser) {
this.parser.parseOpen(node);
return true;
} else if (node.name === this.tag) {
this.map.tabColor.reset();
this.map.pageSetUpPr.reset();
return true;
} else if (this.map[node.name]) {
this.parser = this.map[node.name];
this.parser.parseOpen(node);
return true;
} else {
...toString = function () {
return this._buf.join('');
}...
this._value.release();
// assign value
this._value = Value.create(Value.getType(v), this, v);
},
get text() {
return this._value.toString();
},
toString: function toString() {
return this.text;
},
_upgradeToHyperlink: function _upgradeToHyperlink(hyperlink) {
// if this cell is a string, turn it into a Hyperlink
...stuttered_pipe = function (readable, writable, options) {
var self = this;
options = options || {};
this.readable = readable;
this.writable = writable;
this.bufSize = options.bufSize || 16384;
this.autoPause = options.autoPause || false;
this.paused = false;
this.eod = false;
this.scheduled = null;
readable.on('end', function() {
self.eod = true;
writable.end();
});
// need to have some way to communicate speed of stream
// back from the consumer
readable.on('readable', function() {
if (!self.paused) {
self.resume();
}
});
this._schedule();
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
_schedule = function () {
var self = this;
this.scheduled = setImmediate(function() {
self.scheduled = null;
if (!self.eod && !self.paused) {
var data = self.readable.read(self.bufSize);
if (data && data.length) {
self.writable.write(data);
if (!self.paused && !self.autoPause) {
self._schedule();
}
} else {
if (!self.paused) {
self._schedule();
}
}
}
});
}...
// need to have some way to communicate speed of stream
// back from the consumer
readable.on('readable', function() {
if (!self.paused) {
self.resume();
}
});
this._schedule();
};
utils.inherits(StutteredPipe, events.EventEmitter, {
pause: function() {
this.paused = true;
},
resume: function() {
...pause = function () {
this.paused = true;
}...
read: function read(size) {
return this.stream.read(size);
},
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
...resume = function () {
if (!this.eod) {
if (this.scheduled !== null) {
clearImmediate(this.scheduled);
}
this._schedule();
}
}...
setEncoding: function setEncoding(encoding) {
return this.stream.setEncoding(encoding);
},
pause: function pause() {
return this.stream.pause();
},
resume: function resume() {
return this.stream.resume();
},
isPaused: function isPaused() {
return this.stream.isPaused();
},
pipe: function pipe(destination, options) {
return this.stream.pipe(destination, options);
},
...typed_stack = function (type) {
this._type = type;
this._stack = [];
}n/a
pop = function () {
var tos = this._stack.pop();
return tos || new (this._type)();
}...
},
set model(value) {
// value is [ { name, ranges }, ... ]
var matrixMap = this.matrixMap = {};
value.forEach(function (definedName) {
var matrix = matrixMap[definedName.name] = new CellMatrix();
definedName.ranges.forEach(function (rangeStr) {
if (rangeRegexp.test(rangeStr.split('!').pop() || '')) {
matrix.addCell(rangeStr);
}
});
});
}
};
...push = function (instance) {
if (!(instance instanceof this._type)) {
throw new Error('Invalid type pushed to TypedStack');
}
this._stack.push(instance);
}...
width: column.width,
style: column.style,
isCustomWidth: column.isCustomWidth,
hidden: column.hidden,
outlineLevel: column.outlineLevel,
collapsed: column.collapsed
};
cols.push(col);
} else {
col.max = index + 1;
}
}
});
}
return cols.length ? cols : undefined;
...function each(obj, cb) {
if (obj) {
if (Array.isArray(obj)) {
obj.forEach(cb);
} else {
Object.keys(obj).forEach(function (key) {
cb(obj[key], key);
});
}
}
}...
this.removeEx(location, name);
},
removeEx: function removeEx(location, name) {
var matrix = this.getMatrix(name);
matrix.removeCellEx(location);
},
removeAllNames: function removeAllNames(location) {
_.each(this.matrixMap, function (matrix) {
matrix.removeCellEx(location);
});
},
forEach: function forEach(callback) {
_.each(this.matrixMap, function (matrix, name) {
matrix.forEach(function (cell) {
...function every(obj, cb) {
if (obj) {
if (Array.isArray(obj)) {
return obj.every(cb);
} else {
return Object.keys(obj).every(function (key) {
return cb(obj[key], key);
});
}
} else {
return true;
}
}...
}
}
},
every: function every(obj, cb) {
if (obj) {
if (Array.isArray(obj)) {
return obj.every(cb);
} else {
return Object.keys(obj).every(function (key) {
return cb(obj[key], key);
});
}
} else {
return true;
...function isEqual(a, b) {
var aType = typeof a;
var bType = typeof b;
var aArray = Array.isArray(a);
var bArray = Array.isArray(b);
if (aType !== bType) return false;
switch(typeof a) {
case 'object':
if (aArray || bArray) {
if (aArray && bArray) {
return (a.length === b.length) &&
a.every(function(aValue, index) {
var bValue = b[index];
return _.isEqual(aValue, bValue);
});
} else {
return false;
}
} else {
return _.every(a, function(aValue, key) {
var bValue = b[key];
return _.isEqual(aValue, bValue);
});
}
default:
return a === b;
}
}...
return JSON.stringify({
key: this.key,
width: this.width,
headers: this.headers.length ? this.headers : undefined
});
},
equivalentTo: function equivalentTo(other) {
return this.width == other.width && this.hidden == other.hidden && this.outlineLevel == other.outlineLevel
&& _.isEqual(this.style, other.style);
},
get isDefault() {
if (this.isCustomWidth) {
return false;
}
if (this.hidden) {
return false;
...function map(obj, cb) {
if (obj) {
if (Array.isArray(obj)) {
return obj.map(cb);
} else {
return Object.keys(obj).map(function (key) {
return cb(obj[key], key);
});
}
} else {
return [];
}
}...
if (special !== undefined) {
return special;
}
return datum;
};
var csvStream = csv(options).on('data', function (data) {
worksheet.addRow(data.map(map));
}).on('end', function () {
csvStream.emit('worksheet', worksheet);
});
return csvStream;
},
write: function write(stream, options) {
...function some(obj, cb) {
if (obj) {
if (Array.isArray(obj)) {
return obj.some(cb);
} else {
return Object.keys(obj).some(function (key) {
return cb(obj[key], key);
});
}
}
}...
}
});
}
},
// returns true if the row includes at least one cell with a value
get hasValues() {
return _.some(this._cells, function (cell) {
return cell && cell.type != Enums.ValueType.Null;
});
},
get cellCount() {
return this._cells.length;
},
...dateToExcel = function (d, date1904) {
return 25569 + d.getTime() / (24 * 3600 * 1000) - (date1904 ? 1462 : 0);
}...
case Enums.ValueType.Error:
xmlStream.addAttribute('t', 'e');
xmlStream.leafNode('f', attrs, model.formula);
xmlStream.leafNode('v', null, model.result.error);
break;
case Enums.ValueType.Date:
xmlStream.leafNode('f', attrs, model.formula);
xmlStream.leafNode('v', null, utils.dateToExcel(model.result, model.date1904
));
break;
// case Enums.ValueType.Hyperlink: // ??
// case Enums.ValueType.Formula:
default:
throw new Error('I could not understand type of value');
}
},
...excelToDate = function (v, date1904) {
return new Date((v - 25569 + (date1904 ? 1462 : 0)) * 24 * 3600 * 1000);
}...
if (model.value.richText) {
model.type = Enums.ValueType.RichText;
}
break;
case Enums.ValueType.Number:
if (style && utils.isDateFmt(style.numFmt)) {
model.type = Enums.ValueType.Date;
model.value = utils.excelToDate(model.value, options.date1904);
}
break;
case Enums.ValueType.Formula:
if (model.result !== undefined && style && utils.isDateFmt(style.numFmt)) {
model.result = utils.excelToDate(model.result, options.date1904);
}
if (model.sharedFormula) {
...getRelsPath = function (filepath) {
var path = utils.parsePath(filepath);
return path.path + '/_rels/' + path.name + '.rels';
}n/a
inherits = function (cls, superCtor, statics, prototype) {
cls.super_ = superCtor;
if (!prototype) {
prototype = statics;
statics = null;
}
if (statics) {
for (var i in statics) {
Object.defineProperty(cls, i, Object.getOwnPropertyDescriptor(statics, i));
}
}
var properties = {
constructor: {
value: cls,
enumerable: false,
writable: false,
configurable: true
}
};
if (prototype) {
for (var i in prototype) {
properties[i] = Object.getOwnPropertyDescriptor(prototype, i);
}
}
cls.prototype = Object.create(superCtor.prototype, properties);
}...
// controls emit('data')
this.paused = false;
this.encoding = null;
};
utils.inherits(StreamBuf, Stream.Duplex, {
// writable
// event drain - if write returns false (which it won't), indicates when safe to write again.
// finish - end() has been called
// pipe(src) - pipe() has been called on readable
// unpipe(src) - unpipe() has been called on readable
// error - duh
...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;
}...
model.value = options.sharedStrings.getString(model.value);
}
if (model.value.richText) {
model.type = Enums.ValueType.RichText;
}
break;
case Enums.ValueType.Number:
if (style && utils.isDateFmt(style.numFmt)) {
model.type = Enums.ValueType.Date;
model.value = utils.excelToDate(model.value, options.date1904);
}
break;
case Enums.ValueType.Formula:
if (model.result !== undefined && style && utils.isDateFmt(style.numFmt)) {
model.result = utils.excelToDate(model.result, options.date1904);
...nop = function () {}n/a
parsePath = function (filepath) {
var last = filepath.lastIndexOf('/');
return {
path: filepath.substring(0, last),
name: filepath.substring(last + 1)
};
}...
var last = filepath.lastIndexOf('/');
return {
path: filepath.substring(0, last),
name: filepath.substring(last + 1)
};
},
getRelsPath: function getRelsPath(filepath) {
var path = utils.parsePath(filepath);
return path.path + '/_rels/' + path.name + '.rels';
},
xmlEncode: function xmlEncode(text) {
return text.replace(/[<>&'"\x7F\x00-\x08\x0A-\x0C\x0E-\x1F]/g, function (c) {
switch (c) {
case '<':
return '<';
...promiseImmediate = function (value) {
return new PromishLib.Promish(function(resolve, reject) {
if (global.setImmediate) {
setImmediate(function() {
resolve(value);
});
} else {
// poorman's setImmediate - must wait at least 1ms
setTimeout(function() {
resolve(value);
},1);
}
});
}n/a
toIsoDateString = function (dt) {
return dt.toIsoString().subsstr(0,10);
}n/a
validInt = function (value) {
var i = parseInt(value);
return !isNaN(i) ? i : 0;
}n/a
xmlDecode = function (text) {
return text.replace(/&([a-z]*);/, function(c) {
switch (c) {
case '<': return '<';
case '>': return '>';
case '&': return '&';
case ''': return '\'';
case '"': return '"';
default: return c;
}
});
}...
'r:id': model.rId
});
},
parseOpen: function parseOpen(node) {
if (node.name === 'sheet') {
this.model = {
name: utils.xmlDecode(node.attributes.name),
id: parseInt(node.attributes.sheetId),
rId: node.attributes['r:id']
};
return true;
} else {
return false;
}
...xmlEncode = function (text) {
return text.replace(/[<>&'"\x7F\x00-\x08\x0A-\x0C\x0E-\x1F]/g, function (c) {
switch (c) {
case '<': return '<';
case '>': return '>';
case '&': return '&';
case '\'': return ''';
case '"': return '"';
default: return '';
}
});
}...
var QUOTE = '"';
var SPACE = ' ';
function pushAttribute(xml, name, value) {
xml.push(SPACE);
xml.push(name);
xml.push(EQUALS_QUOTE);
xml.push(utils.xmlEncode(value.toString()));
xml.push(QUOTE);
}
function pushAttributes(xml, attributes) {
if (attributes) {
_.each(attributes, function (value, name) {
if (value !== undefined) {
pushAttribute(xml, name, value);
...workbook_reader = function (options) {
this.options = options = options || {};
// until we actually parse a styles.xml file, just assume we're not handling styles
// (but we do need to handle dates)
this.styles = new StyleManager.Mock();
// worksheet readers, indexed by sheetNo
this.worksheetReaders = {};
// hyperlink readers, indexed by sheetNo
this.hyperlinkReaders = {};
// count the open readers
this.readers = 0;
// end of stream check
this.atEnd = false;
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
_emitEntry = function (options, payload) {
if (options.entries === 'emit') {
this.emit('entry', payload);
}
}...
},
_emitEntry: function(options, payload) {
if (options.entries === 'emit') {
this.emit('entry', payload);
}
},
_parseSharedStrings: function(entry, options) {
this._emitEntry(options, {type: 'shared-strings'});
var self = this;
var sharedStrings = null;
switch(options.sharedStrings) {
case 'cache':
sharedStrings = this.sharedStrings = [];
break;
case 'emit':
..._getReader = function (Type, collection, sheetNo) {
var self = this;
var reader = collection[sheetNo];
if (!reader) {
reader = new Type(this, sheetNo);
self.readers++;
reader.on('finished', function() {
if (!--self.readers) {
if (self.atEnd) {
self.emit('finished');
}
}
});
collection[sheetNo] = reader;
}
return reader;
}...
});
collection[sheetNo] = reader;
}
return reader;
},
_parseWorksheet: function(entry, sheetNo, options) {
this._emitEntry(options, {type: 'worksheet', id: sheetNo});
var worksheetReader = this._getReader(WorksheetReader, this.worksheetReaders, sheetNo
);
if (options.worksheets === 'emit') {
this.emit('worksheet', worksheetReader);
}
worksheetReader.read(entry, options, this.hyperlinkReaders[sheetNo]);
},
_parseHyperlinks: function(entry, sheetNo, options) {
this._emitEntry(options, {type: 'hyerlinks', id: sheetNo});
..._getStream = function (input) {
if (input instanceof Stream.Readable) {
return input;
}
if (typeof input === 'string') {
return fs.createReadStream(input);
}
throw new Error('Could not recognise input');
}...
sharedStrings: ['cache', 'emit'],
styles: ['cache'],
hyperlinks: ['cache', 'emit'],
worksheets: ['emit']
},
read: function(input, options) {
var self = this;
var stream = this.stream = this._getStream(input);
var zip = this.zip = unzip.Parse();
zip.on('entry',function (entry) {
var match, sheetNo;
switch(entry.path) {
case '_rels/.rels':
case 'xl/workbook.xml':
..._parseHyperlinks = function (entry, sheetNo, options) {
this._emitEntry(options, {type: 'hyerlinks', id: sheetNo});
var hyperlinksReader = this._getReader(HyperlinkReader, this.hyperlinkReaders, sheetNo);
if (options.hyperlinks === 'emit') {
this.emit('hyperlinks', hyperlinksReader);
}
hyperlinksReader.read(entry, options);
}...
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/);
sheetNo = match[1];
self._parseWorksheet(entry, sheetNo, options);
} else if (entry.path.match(/xl\/worksheets\/_rels\/sheet\d+\.xml.rels/)) {
match = entry.path.match(/xl\/worksheets\/_rels\/sheet(\d+)\.xml.rels/);
sheetNo = match[1];
self._parseHyperlinks(entry, sheetNo, options);
} else {
entry.autodrain();
}
break;
}
});
..._parseSharedStrings = function (entry, options) {
this._emitEntry(options, {type: 'shared-strings'});
var self = this;
var sharedStrings = null;
switch(options.sharedStrings) {
case 'cache':
sharedStrings = this.sharedStrings = [];
break;
case 'emit':
break;
default:
entry.autodrain();
return;
}
var parser = Sax.createStream(true, {});
var inT = false;
var t = null;
var index = 0;
parser.on('opentag', function(node) {
if (node.name === 't') {
t = null;
inT = true;
}
});
parser.on('closetag', function (name) {
if (inT && (name === 't')) {
if (sharedStrings) {
sharedStrings.push(t);
} else {
self.emit('shared-string', { index: index++, text: t});
}
t = null;
}
});
parser.on('text', function (text) {
t = t ? t + text : text;
});
parser.on('error', function (error) {
self.emit('error', error);
});
entry.pipe(parser);
}...
switch(entry.path) {
case '_rels/.rels':
case 'xl/workbook.xml':
case 'xl/_rels/workbook.xml.rels':
entry.autodrain();
break;
case 'xl/sharedStrings.xml':
self._parseSharedStrings(entry, options);
break;
case 'xl/styles.xml':
self._parseStyles(entry, options);
break;
default:
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/);
..._parseStyles = function (entry, options) {
this._emitEntry(options, {type: 'styles'});
if (options.styles !== 'cache') {
entry.autodrain();
return;
}
this.styles = new StyleManager();
this.styles.parse(entry);
}...
case 'xl/_rels/workbook.xml.rels':
entry.autodrain();
break;
case 'xl/sharedStrings.xml':
self._parseSharedStrings(entry, options);
break;
case 'xl/styles.xml':
self._parseStyles(entry, options);
break;
default:
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/);
sheetNo = match[1];
self._parseWorksheet(entry, sheetNo, options);
} else if (entry.path.match(/xl\/worksheets\/_rels\/sheet\d+\.xml.rels/)) {
..._parseWorksheet = function (entry, sheetNo, options) {
this._emitEntry(options, {type: 'worksheet', id: sheetNo});
var worksheetReader = this._getReader(WorksheetReader, this.worksheetReaders, sheetNo);
if (options.worksheets === 'emit') {
this.emit('worksheet', worksheetReader);
}
worksheetReader.read(entry, options, this.hyperlinkReaders[sheetNo]);
}...
case 'xl/styles.xml':
self._parseStyles(entry, options);
break;
default:
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/);
sheetNo = match[1];
self._parseWorksheet(entry, sheetNo, options);
} else if (entry.path.match(/xl\/worksheets\/_rels\/sheet\d+\.xml.rels/)) {
match = entry.path.match(/xl\/worksheets\/_rels\/sheet(\d+)\.xml.rels/);
sheetNo = match[1];
self._parseHyperlinks(entry, sheetNo, options);
} else {
entry.autodrain();
}
...read = function (input, options) {
var self = this;
var stream = this.stream = this._getStream(input);
var zip = this.zip = unzip.Parse();
zip.on('entry',function (entry) {
var match, sheetNo;
switch(entry.path) {
case '_rels/.rels':
case 'xl/workbook.xml':
case 'xl/_rels/workbook.xml.rels':
entry.autodrain();
break;
case 'xl/sharedStrings.xml':
self._parseSharedStrings(entry, options);
break;
case 'xl/styles.xml':
self._parseStyles(entry, options);
break;
default:
if (entry.path.match(/xl\/worksheets\/sheet\d+\.xml/)) {
match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/);
sheetNo = match[1];
self._parseWorksheet(entry, sheetNo, options);
} else if (entry.path.match(/xl\/worksheets\/_rels\/sheet\d+\.xml.rels/)) {
match = entry.path.match(/xl\/worksheets\/_rels\/sheet(\d+)\.xml.rels/);
sheetNo = match[1];
self._parseHyperlinks(entry, sheetNo, options);
} else {
entry.autodrain();
}
break;
}
});
zip.on('close', function () {
self.emit('end');
self.atEnd = true;
if (!self.readers) {
self.emit('finished');
}
});
// Pipe stream into top flow-control
// this.flowControl.pipe(zip);
stream.pipe(zip);
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...workbook_writer = function (options) {
options = options || {};
this.created = options.created || new Date();
this.modified = options.modified || this.created;
this.creator = options.creator || 'ExcelJS';
this.lastModifiedBy = options.lastModifiedBy || 'ExcelJS';
this.lastPrinted = options.lastPrinted;
// using shared strings creates a smaller xlsx file but may use more memory
this.useSharedStrings = options.useSharedStrings || false;
this.sharedStrings = new SharedStrings();
// style manager
this.styles = options.useStyles ? new StylesXform(true) : new StylesXform.Mock(true);
// defined names
this._definedNames = new DefinedNames();
this._worksheets = [];
this.views = [];
this.zip = Archiver('zip');
if (options.stream) {
this.stream = options.stream;
} else if (options.filename) {
this.stream = fs.createWriteStream(options.filename);
} else {
this.stream = new StreamBuf();
}
this.zip.pipe(this.stream);
// these bits can be added right now
this.promise = PromishLib.Promish.all([
this.addThemes(),
this.addOfficeRels()
]);
}n/a
_commitWorksheets = function () {
var commitWorksheet = function(worksheet) {
if (!worksheet.committed) {
return new PromishLib.Promish(function(resolve) {
worksheet.stream.on('zipped', function() {
resolve();
});
worksheet.commit();
});
} else {
return PromishLib.Promish.resolve();
}
};
// if there are any uncommitted worksheets, commit them now and wait
var promises = this._worksheets.map(commitWorksheet);
if (promises.length) {
return PromishLib.Promish.all(promises);
} else {
return PromishLib.Promish.resolve();
}
}...
}
},
commit: function() {
var self = this;
// commit all worksheets, then add suplimentary files
return this.promise.then(function() {
return self._commitWorksheets();
})
.then(function() {
return PromishLib.Promish.all([
self.addContentTypes(),
self.addApp(),
self.addCore(),
self.addSharedStrings(),
..._finalize = function () {
var self = this;
return new PromishLib.Promish(function(resolve, reject) {
self.stream.on('error', function(error){
reject(error);
});
self.stream.on('finish', function(){
resolve(self);
});
self.zip.on('error', function(error){
reject(error);
});
self.zip.finalize();
});
}...
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
return self._finalize(zip);
});
},
writeFile: function (filename, options) {
var self = this;
var stream = fs.createWriteStream(filename);
return new PromishLib.Promish(function(resolve, reject) {
..._openStream = function (path) {
var self = this;
var stream = new StreamBuf({bufSize: 65536, batch: true});
self.zip.append(stream, { name: path });
stream.on('finish', function() {
stream.emit('zipped');
});
return stream;
}...
this._workbook = options.workbook;
};
HyperlinkWriter.prototype = {
get stream() {
if (!this._stream) {
this._stream = this._workbook._openStream('/xl/worksheets/_rels/sheet' +
this.id + '.xml.rels');
}
return this._stream;
},
get length() {
return this._hyperlinks.length;
},
...addApp = function () {
var self = this;
return new PromishLib.Promish(function(resolve) {
var model = {
worksheets: self._worksheets.filter(Boolean)
};
var xform = new AppXform();
var xml = xform.toXml(model);
self.zip.append(xml, {name: 'docProps/app.xml'});
resolve();
});
}...
model.worksheets.forEach(function (worksheet) {
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
...addContentTypes = function () {
var self = this;
return new PromishLib.Promish(function(resolve) {
var model = {
worksheets: self._worksheets.filter(Boolean)
};
var xform = new ContentTypesXform();
var xml = xform.toXml(model);
self.zip.append(xml, {name: '[Content_Types].xml'});
resolve();
});
}...
workbookXform.prepare(model);
model.worksheets.forEach(function (worksheet) {
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
...addCore = function () {
var self = this;
return new PromishLib.Promish(function(resolve) {
var coreXform = new CoreXform();
var xml = coreXform.toXml(self);
self.zip.append(xml, {name: 'docProps/core.xml'});
resolve();
});
}...
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
...addOfficeRels = function () {
var self = this;
return new PromishLib.Promish(function(resolve) {
var xform = new RelationshipsXform();
var xml = xform.toXml([
{rId: 'rId1', type: RelType.OfficeDocument, target: 'xl/workbook.xml'}
]);
self.zip.append(xml, {name: '/_rels/.rels'});
resolve();
});
}...
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
...addSharedStrings = function () {
var self = this;
if (this.sharedStrings.count) {
return new PromishLib.Promish(function(resolve) {
var sharedStringsXform = new SharedStringsXform();
var xml = sharedStringsXform.toXml(self.sharedStrings);
self.zip.append(xml, {name: '/xl/sharedStrings.xml'});
resolve();
});
} else {
return PromishLib.Promish.resolve();
}
}...
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
...addStyles = function () {
var self = this;
return new PromishLib.Promish(function(resolve) {
self.zip.append(self.styles.xml, {name: 'xl/styles.xml'});
resolve();
});
}...
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
...addThemes = function () {
var self = this;
return new PromishLib.Promish(function(resolve) {
self.zip.append(theme1Xml, { name: 'xl/theme/theme1.xml' });
resolve();
});
}...
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
...addWorkbook = function () {
var zip = this.zip;
var model = {
worksheets: this._worksheets.filter(Boolean),
definedNames: this._definedNames.model,
views: this.views,
properties: {}
};
return new PromishLib.Promish(function(resolve) {
var xform = new WorkbookXform();
xform.prepare(model);
zip.append(xform.toXml(model), {name: '/xl/workbook.xml'});
resolve();
});
}...
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
return self._finalize(zip);
});
},
writeFile: function (filename, options) {
var self = this;
...addWorkbookRels = function () {
var self = this;
var count = 1;
var relationships = [
{rId: 'rId' + (count++), type: RelType.Styles, target: 'styles.xml'},
{rId: 'rId' + (count++), type: RelType.Theme, target: 'theme/theme1.xml'}
];
if (this.sharedStrings.count) {
relationships.push(
{rId: 'rId' + (count++), type: RelType.SharedStrings, target: 'sharedStrings.xml'}
);
}
this._worksheets.forEach(function (worksheet) {
if (worksheet) {
worksheet.rId = 'rId' + (count++);
relationships.push(
{rId: worksheet.rId, type: RelType.Worksheet, target: 'worksheets/sheet' + worksheet.id + '.xml'}
);
}
});
return new PromishLib.Promish(function(resolve) {
var xform = new RelationshipsXform();
var xml = xform.toXml(relationships);
self.zip.append(xml, {name: '/xl/_rels/workbook.xml.rels'});
resolve();
});
}...
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
...addWorksheet = function (name, options) {
// it's possible to add a worksheet with different than default
// shared string handling
// in fact, it's even possible to switch it mid-sheet
options = options || {};
var useSharedStrings = options.useSharedStrings !== undefined ?
options.useSharedStrings :
this.useSharedStrings;
if (options.tabColor) {
console.trace('tabColor option has moved to { properties: tabColor: {...} }');
options.properties = Object.assign({
tabColor: options.tabColor
}, options.properties);
}
var id = this.nextId;
name = name || 'sheet' + id;
var worksheet = new WorksheetWriter({
id: id,
name: name,
workbook: this,
useSharedStrings: useSharedStrings,
properties: options.properties,
pageSetup: options.pageSetup,
views: options.views
});
this._worksheets[id] = worksheet;
return worksheet;
}...
}
]
```
## Add a Worksheet
```javascript
var sheet = workbook.addWorksheet('My Sheet');
```
Use the second parameter of the addWorksheet function to specify options for the worksheet.
For Example:
```javascript
...commit = function () {
var self = this;
// commit all worksheets, then add suplimentary files
return this.promise.then(function() {
return self._commitWorksheets();
})
.then(function() {
return PromishLib.Promish.all([
self.addContentTypes(),
self.addApp(),
self.addCore(),
self.addSharedStrings(),
self.addStyles(),
self.addWorkbookRels()
]);
})
.then(function() {
return self.addWorkbook();
})
.then(function(){
return self._finalize();
});
}...
// Note: this operation will not affect other rows
row.splice(3,2);
// remove one cell and insert two more (cells to the right of the cut cell will be shifted right)
row.splice(4,1,'new value 1', 'new value 2');
// Commit a completed row to stream
row.commit();
// row metrics
var rowSize = row.cellCount;
var numValues = row.actualCellCount;
```
## Handling Individual Cells
...getWorksheet = function (id) {
if (id === undefined) {
return this._worksheets.find(function() { return true; });
} else if (typeof(id) === 'number') {
return this._worksheets[id];
} else if (typeof id === 'string') {
return this._worksheets.find(function(worksheet) {
return worksheet && worksheet.name == id;
});
} else {
return undefined;
}
}...
// Iterate over all sheets
// Note: workbook.worksheets.forEach will still work but this is better
workbook.eachSheet(function(worksheet, sheetId) {
// ...
});
// fetch sheet by name
var worksheet = workbook.getWorksheet('My Sheet');
// fetch sheet by id
var worksheet = workbook.getWorksheet(1);
```
## Worksheet Properties
...worksheet_reader = function (workbook, id) {
this.workbook = workbook;
this.id = id;
// and a name
this.name = 'Sheet' + this.id;
// column definitions
this._columns = null;
this._keys = {};
// keep a record of dimensions
this._dimensions = new Dimensions();
}n/a
function EventEmitter() {
EventEmitter.init.call(this);
}n/a
_emitRow = function (row) {
this.emit('row', row);
}...
break;
case 'sheetData':
inRows = false;
break;
case 'row':
self._dimensions.expandRow(row);
self._emitRow(row);
row = null;
break;
case 'c':
if (row && c) {
var address = colCache.decodeAddress(c.ref);
var cell = row.getCell(address.col);
...destroy = function () {
throw new Error('Invalid Operation: destroy');
}...
},
_removeWorksheet: function _removeWorksheet(worksheet) {
delete this._worksheets[worksheet.id];
},
removeWorksheet: function removeWorksheet(id) {
var worksheet = this.getWorksheet(id);
if (worksheet) {
worksheet.destroy();
}
},
getWorksheet: function getWorksheet(id) {
if (id === undefined) {
return this._worksheets.find(function (worksheet) {
return worksheet;
...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];
}...
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];
// Access an individual columns by key, letter and 1-based column number
var idCol = worksheet.getColumn('id');
var nameCol = worksheet.getColumn('B');
var dobCol = worksheet.getColumn(3);
// set column properties
// Note: will overwrite cell value C1
dobCol.header = 'Date of Birth';
...read = function (entry, options, hyperlinkReader) {
var self = this;
var emitSheet = false;
var prepSheet = false;
var emitHyperlinks = false;
var hyperlinks = null;
switch (options.worksheets) {
case 'emit':
emitSheet = true;
break;
case 'prep':
prepSheet = true;
break;
}
switch(options.hyperlinks) {
case 'emit':
emitHyperlinks = true;
break;
case 'cache':
this.hyperlinks = hyperlinks = {};
break;
}
if (!emitSheet && !emitHyperlinks && !hyperlinks) {
entry.autodrain();
return;
}
// references
var sharedStrings = this.workbook.sharedStrings;
var styles = this.workbook.styles;
// xml position
var inCols = false;
var inRows = false;
var inHyperlinks = false;
// parse state
var cols = null;
var row = null;
var c = null;
var current = null;
var parser = Sax.createStream(true, {});
parser.on('opentag', function(node) {
if (emitSheet) {
switch (node.name) {
case 'cols':
inCols = true;
cols = [];
break;
case 'sheetData':
inRows = true;
break;
case 'col':
if (inCols) {
cols.push({
min: parseInt(node.attributes.min),
max: parseInt(node.attributes.max),
width: parseFloat(node.attributes.width),
styleId: parseInt(node.attributes.style || '0')
});
}
break;
case 'row':
if (inRows) {
var r = parseInt(node.attributes.r);
row = new Row(self, r);
if (node.attributes.ht) {
row.height = parseFloat(node.attributes.ht);
}
if (node.attributes.s) {
var styleId = parseInt(node.attributes.s);
row.style = styles.getStyleModel(styleId);
}
}
break;
case 'c':
if (row) {
c = {
ref: node.attributes.r,
s: parseInt(node.attributes.s),
t: node.attributes.t
};
}
break;
case 'f':
if (c) {
current = c.f = { text: '' };
}
break;
case 'v':
if (c) {
current = c.v = { text: '' };
}
break;
case 'mergeCell':
}
}
// =================================================================
//
if (emitHyperlinks || hyperlinks) {
switch (node.name) {
case 'hyperlinks':
inHyperlinks = true;
break;
case 'hyperlink':
if (inHyperlinks) {
var hyperlink = {
ref: node.attributes.ref,
rId: node.attributes['r:id']
};
if (emitHyperlinks) {
self.emit('hyperlink', hyperlink);
} else {
hyperlinks[hyperlink.ref] = hyperlink;
}
}
break;
}
}
});
// only text data is for sheet values
parser.on('text', function (text) {
if (emitSheet) {
if (current) {
current.text += text;
}
}
});
parser.on('closetag', function(name) {
if (emitSheet) {
switch (name) {
case 'cols':
inCols = false;
self._columns = Column.fromModel(cols);
break;
case 'sheetData':
inRows = false;
break;
case 'row':
self._dimensions.expandRow(row);
self._emitRow(row);
row = null;
break;
case 'c':
if (row && c) {
var address = colCache.decodeAddress(c.ref);
var cell = row.getCell(address.col);
if (c.s) {
cell.style = self.workbook.styles.getStyleModel(c.s);
}
if (c.f) {
var value = {
formula: c.f.text ......
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...worksheet_writer = function (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 are stored here while they need to be worked on.
// when they are committed, they will be deleted.
this._rows = [];
// column definitions
this._columns = null;
// column keys (addRow convenience): key ==> this._columns index
this._keys = {};
// keep record of all merges
this._merges = [];
this._merges.add = function() {}; // ignore cell instruction
// keep record of all hyperlinks
this._hyperlinkWriter = new HyperlinkWriter(options);
// keep a record of dimensions
this._dimensions = new Dimensions();
// first uncommitted row
this._rowZero = 1;
// committed flag
this.committed = false;
// for data validations
this.dataValidations = new DataValidations();
// for sharing formulae
this._formulae = {};
this._siFormulae = 0;
// for default row height, outline levels, etc
this.properties = Object.assign({}, {
defaultRowHeight: 15,
dyDescent: 55,
outlineLevelCol: 0,
outlineLevelRow: 0
}, options.properties);
// for all things printing
this.pageSetup = Object.assign({}, {
margins: {left: 0.7, right: 0.7, top: 0.75, bottom: 0.75, header: 0.3, footer: 0.3 },
orientation: 'portrait',
horizontalDpi: 4294967295,
verticalDpi: 4294967295,
fitToPage: !!(options.pageSetup && ((options.pageSetup.fitToWidth || options.pageSetup.fitToHeight) && !options.pageSetup.scale
)),
pageOrder: 'downThenOver',
blackAndWhite: false,
draft: false,
cellComments: 'None',
errors: 'displayed',
scale: 100,
fitToWidth: 1,
fitToHeight: 1,
paperSize: undefined,
showRowColHeaders: false,
showGridLines: false,
horizontalCentered: false,
verticalCentered: false,
rowBreaks: null,
colBreaks: null
}, options.pageSetup);
// using shared strings creates a smaller xlsx file but may use more memory
this.useSharedStrings = options.useSharedStrings || false;
this._workbook = options.workbook;
// views
this._views = options.views || [];
// start writing to stream now
this._writeOpenWorksheet();
this.startedData = false;
}n/a
_commitRow = function (cRow) {
// since rows must be written in order, we commit all rows up till and including cRow
var found = false;
while (this._rows.length && !found) {
var row = this._rows.shift();
this._rowZero++;
if (row) {
this._writeRow(row);
found = (row.number === cRow.number);
this._rowZero = row.number + 1;
}
}
}...
get worksheet() {
return this._worksheet;
},
// 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 commit() {
this._worksheet._commitRow(this);
},
// helps GC by breaking cyclic references
destroy: function destroy() {
delete this._worksheet;
delete this._cells;
delete this.style;
..._write = function (text) {
xml.reset();
xml.addText(text);
this.stream.write(xml);
}...
// Write chunk to all pipes. A chunk with no data is the end
var promises = [];
this.pipes.forEach(function(pipe) {
if (chunk.data) {
if (pipe.sync) {
pipe.stream.write(chunk.data, chunk.encoding);
} else {
promises.push(self._write(pipe.stream, chunk.data, chunk.encoding));
}
} else {
pipe.stream.end();
}
});
if (!promises.length) {
promises.push(PromishLib.Promish.resolve());
..._writeCloseSheetData = function () {
this._write('</sheetData>');
}...
// we _cannot_ accept new rows from now on
this._rows = null;
if (!this.startedData) {
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
..._writeCloseWorksheet = function () {
this._write('</worksheet>');
}...
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
// signal end of stream to workbook
this.stream.end();
// also commit the hyperlinks if any
this._hyperlinkWriter.commit();
..._writeColumns = function () {
var cols = Column.toModel(this.columns);
if (cols) {
xform.columns.prepare(cols, {styles: this._workbook.styles});
this.stream.write(xform.columns.toXml(cols));
}
}...
_writeOpenSheetData: function() {
this._write('<sheetData>');
},
_writeRow: function(row) {
var self = this;
if (!this.startedData) {
this._writeColumns();
this._writeOpenSheetData();
this.startedData = true;
}
if (row.hasValues || row.height) {
var model = row.model;
var options = {
..._writeDataValidations = function () {
this.stream.write(xform.dataValidations.toXml(this.dataValidations.model));
}...
if (!this.startedData) {
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
// signal end of stream to workbook
this.stream.end();
..._writeDimensions = function () {
// for some reason, Excel can't handle dimensions at the bottom of the file
// and we don't know the dimensions until the commit, so don't write them.
// this._write('<dimension ref="' + this._dimensions + '"/>');
}n/a
_writeHyperlinks = function () {
this.stream.write(xform.hyperlinks.toXml(this._hyperlinkWriter._hyperlinks));
}...
if (!this.startedData) {
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
// signal end of stream to workbook
this.stream.end();
..._writeMergeCells = function () {
if (this._merges.length) {
xml.reset();
xml.addText('<mergeCells count="' + this._merges.length + '">');
this._merges.forEach(function(merge) {
xml.addText('<mergeCell ref="' + merge + '"/>');
});
xml.addText('</mergeCells>');
this.stream.write(xml);
}
}...
// we _cannot_ accept new rows from now on
this._rows = null;
if (!this.startedData) {
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
..._writeOpenSheetData = function () {
this._write('<sheetData>');
}...
}
});
// we _cannot_ accept new rows from now on
this._rows = null;
if (!this.startedData) {
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
..._writeOpenWorksheet = function () {
xml.reset();
xml.addText('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
xml.addText('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats
.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac
" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">'); // eslint-disable-line max-len
this._writeSheetProperties(xml, this.properties, this.pageSetup);
xml.addText(xform.sheetViews.toXml(this.views));
this._writeSheetFormatProperties(xml, this.properties);
this.stream.write(xml);
}...
this._workbook = options.workbook;
// views
this._views = options.views || [];
// start writing to stream now
this._writeOpenWorksheet();
this.startedData = false;
};
WorksheetWriter.prototype = {
get workbook() {
return this._workbook;
..._writePageMargins = function () {
this.stream.write(xform.pageMargins.toXml(this.pageSetup.margins));
}...
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
// signal end of stream to workbook
this.stream.end();
// also commit the hyperlinks if any
..._writePageSetup = function () {
this.stream.write(xform.pageSeteup.toXml(this.pageSetup));
}...
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
// signal end of stream to workbook
this.stream.end();
// also commit the hyperlinks if any
this._hyperlinkWriter.commit();
..._writeRow = function (row) {
var self = this;
if (!this.startedData) {
this._writeColumns();
this._writeOpenSheetData();
this.startedData = true;
}
if (row.hasValues || row.height) {
var model = row.model;
var options = {
styles: this._workbook.styles,
sharedStrings: self.useSharedStrings ? self._workbook.sharedStrings : undefined,
hyperlinks: this._hyperlinkWriter,
merges: this._merges,
formulae: this._formulae,
siFormulae: this._siFormulae,
};
xform.row.prepare(model, options);
this.stream.write(xform.row.toXml(model));
}
}...
return;
}
var self = this;
// commit all rows
this._rows.forEach(function(cRow) {
if (cRow) {
// write the row to the stream
self._writeRow(cRow);
}
});
// we _cannot_ accept new rows from now on
this._rows = null;
if (!this.startedData) {
..._writeSheetFormatProperties = function (xmlBuf, properties) {
var sheetFormatPropertiesModel = properties ? {
defaultRowHeight: properties.defaultRowHeight,
dyDescent: properties.dyDescent,
outlineLevelCol: properties.outlineLevelCol,
outlineLevelRow: properties.outlineLevelRow
} : undefined;
xmlBuf.addText(xform.sheetFormatProperties.toXml(sheetFormatPropertiesModel));
}...
xml.addText('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
xml.addText('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http
://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility
/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"&#
x3e;'); // eslint-disable-line max-len
this._writeSheetProperties(xml, this.properties, this.pageSetup);
xml.addText(xform.sheetViews.toXml(this.views));
this._writeSheetFormatProperties(xml, this.properties);
this.stream.write(xml);
},
_writeColumns: function() {
var cols = Column.toModel(this.columns);
if (cols) {
xform.columns.prepare(cols, {styles: this._workbook.styles});
..._writeSheetProperties = function (xmlBuf, properties, pageSetup) {
var sheetPropertiesModel = {
tabColor: properties && properties.tabColor,
pageSetup: pageSetup && pageSetup.fitToPage ? {
fitToPage: pageSetup.fitToPage
} : undefined
};
xmlBuf.addText(xform.sheetProperties.toXml(sheetPropertiesModel));
}...
},
_writeOpenWorksheet: function() {
xml.reset();
xml.addText('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
xml.addText('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http
://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility
/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"&#
x3e;'); // eslint-disable-line max-len
this._writeSheetProperties(xml, this.properties, this.pageSetup);
xml.addText(xform.sheetViews.toXml(this.views));
this._writeSheetFormatProperties(xml, this.properties);
this.stream.write(xml);
},
...addRow = function (value) {
var row = new Row(this, this._nextRow);
this._rows[row.number - this._rowZero] = row;
row.values = value;
return row;
}...
```
## Rows
```javascript
// Add a couple of Rows by key-value, after the last current row, using the column keys
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
// Add a row by contiguous Array (assign to columns A, B & C)
worksheet.addRow([3, 'Sam', new Date()]);
// Add a row by sparse Array (assign to columns A, E & I)
var rowValues = [];
...commit = function () {
if (this.committed) {
return;
}
var self = this;
// commit all rows
this._rows.forEach(function(cRow) {
if (cRow) {
// write the row to the stream
self._writeRow(cRow);
}
});
// we _cannot_ accept new rows from now on
this._rows = null;
if (!this.startedData) {
this._writeOpenSheetData();
}
this._writeCloseSheetData();
this._writeMergeCells();
this._writeHyperlinks();
this._writeDataValidations();
this._writePageMargins();
this._writePageSetup();
this._writeCloseWorksheet();
// signal end of stream to workbook
this.stream.end();
// also commit the hyperlinks if any
this._hyperlinkWriter.commit();
this.committed = true;
}...
// Note: this operation will not affect other rows
row.splice(3,2);
// remove one cell and insert two more (cells to the right of the cut cell will be shifted right)
row.splice(4,1,'new value 1', 'new value 2');
// Commit a completed row to stream
row.commit();
// row metrics
var rowSize = row.cellCount;
var numValues = row.actualCellCount;
```
## Handling Individual Cells
...destroy = function () {
throw new Error('Invalid Operation: destroy');
}...
},
_removeWorksheet: function _removeWorksheet(worksheet) {
delete this._worksheets[worksheet.id];
},
removeWorksheet: function removeWorksheet(id) {
var worksheet = this.getWorksheet(id);
if (worksheet) {
worksheet.destroy();
}
},
getWorksheet: function getWorksheet(id) {
if (id === undefined) {
return this._worksheets.find(function (worksheet) {
return worksheet;
...eachRow = function (options, iteratee) {
if (!iteratee) {
iteratee = options;
options = undefined;
}
if (options && options.includeEmpty) {
var n = this._nextRow;
for (var i = this._rowZero; i < n; i++) {
iteratee(this.getRow(i), i);
}
} else {
this._rows.forEach(function(row) {
if (row.hasValues) {
iteratee(row, row.number);
}
});
}
}...
row.values = {
id: 13,
name: 'Thing 1',
dob: new Date()
};
// Iterate over all rows that have values in a worksheet
worksheet.eachRow(function(row, rowNumber) {
console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
// Iterate over all rows (including empty rows) in a worksheet
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
...findCell = function (r, c) {
var address = colCache.getAddress(r, c);
var row = this.findRow(address.row);
return row ? row.findCell(address.column) : undefined;
}...
},
set address(value) {
this.model.address = value;
},
_getTranslatedFormula: function _getTranslatedFormula() {
if (!this._translatedFormula && this.model.sharedFormula) {
var worksheet = this.cell._row.worksheet;
var master = worksheet.findCell(this.model.sharedFormula);
this._translatedFormula = master && slideFormula(master.formula, master.address, this.model.address);
}
return this._translatedFormula;
},
toCsvString: function toCsvString() {
return '' + (this.model.result || '');
...findRow = function (rowNumber) {
var index = rowNumber - this._rowZero;
return this._rows[index];
}...
// ================================================================================
// 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.findRow(address.row);
return row ? row.findCell(address.column) : 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);
...getCell = function (r, c) {
var address = colCache.getAddress(r, c);
var row = this.getRow(address.row);
return row._getCell(address);
}...
worksheet.getRow(5).outlineLevel = 1;
// rows support a readonly field to indicate the collapsed state based on outlineLevel
expect(worksheet.getRow(4).collapsed).to.equal(false);
expect(worksheet.getRow(5).collapsed).to.equal(true);
row.getCell(1).value = 5; // A5's value set to 5
row.getCell('name').value = 'Zeb'; // B5's value set to 'Zeb' - assuming column 2 is still keyed
by name
row.getCell('C').value = new Date(); // C5's value set to now
// Get a row as a sparse array
// Note: interface change: worksheet.getRow(4) ==> worksheet.getRow(4).values
row = worksheet.getRow(4).values;
expect(row[5]).toEqual('Kyle');
...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;
// otherwise, 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];
}...
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];
// Access an individual columns by key, letter and 1-based column number
var idCol = worksheet.getColumn('id');
var nameCol = worksheet.getColumn('B');
var dobCol = worksheet.getColumn(3);
// set column properties
// Note: will overwrite cell value C1
dobCol.header = 'Date of Birth';
...getRow = function (rowNumber) {
var index = rowNumber - this._rowZero;
// may fail if rows have been comitted
if (index < 0) {
throw new Error('Out of bounds: this row has been committed');
}
var row = this._rows[index];
if (!row) {
this._rows[index] = row = new Row(this, rowNumber);
}
return row;
}...
var rows = [
[5,'Bob',new Date()], // row by array
{id:6, name: 'Barbara', dob: new Date()}
];
worksheet.addRows(rows);
// Get a row object. If it doesn't already exist, a new empty one will be returned
var row = worksheet.getRow(5);
// Get the last editable row in a worksheet (or undefined if there are none)
var row = worksheet.lastRow;
// Set a specific row height
row.height = 42.5;
...mergeCells = function () {
// may fail if rows have been comitted
var dimensions = new Dimensions(Array.prototype.slice.call(arguments, 0)); // convert arguments into Array
// check cells aren't already merged
this._merges.forEach(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++) {
if ((i > dimensions.top) || (j > dimensions.left)) {
this.getCell(i,j).merge(master);
}
}
}
// index merge
this._merges.push(dimensions);
}...
expect(worksheet.getCell('C3').type).toEqual(Excel.ValueType.Date);
```
## Merged Cells
```javascript
// merge a range of cells
worksheet.mergeCells('A4:B5');
// ... merged cells are linked
worksheet.getCell('B5').value = 'Hello, World!';
expect(worksheet.getCell('B5').value).toBe(worksheet.getCell('A4').value);
expect(worksheet.getCell('B5').master).toBe(worksheet.getCell('A4'));
// ... merged cells share the same style object
...xlsx = function (workbook) {
this.workbook = workbook;
}n/a
_finalize = function (zip) {
var self = this;
return new PromishLib.Promish(function(resolve, reject) {
zip.on('finish', function () {
resolve(self);
});
zip.on('error', function (error) {
reject(error);
});
zip.finalize();
});
}...
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
return self._finalize(zip);
});
},
writeFile: function (filename, options) {
var self = this;
var stream = fs.createWriteStream(filename);
return new PromishLib.Promish(function(resolve, reject) {
...addApp = function (zip, model) {
return new PromishLib.Promish(function(resolve) {
var xform = new AppXform();
var xml = xform.toXml(model);
zip.append(xml, {name: 'docProps/app.xml'});
resolve();
});
}...
model.worksheets.forEach(function (worksheet) {
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
...addContentTypes = function (zip, model) {
return new PromishLib.Promish(function(resolve) {
var xform = new ContentTypesXform();
var xml = xform.toXml(model);
zip.append(xml, {name: '[Content_Types].xml'});
resolve();
});
}...
workbookXform.prepare(model);
model.worksheets.forEach(function (worksheet) {
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
...addCore = function (zip, model) {
return new PromishLib.Promish(function(resolve) {
var coreXform = new CoreXform();
zip.append(coreXform.toXml(model), {name: 'docProps/core.xml'});
resolve();
});
}...
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
...addOfficeRels = function (zip) {
return new PromishLib.Promish(function(resolve) {
var xform = new RelationshipsXform();
var xml = xform.toXml([
{rId: 'rId1', type: XLSX.RelType.OfficeDocument, target: 'xl/workbook.xml'}
]);
zip.append(xml, {name: '_rels/.rels'});
resolve();
});
}...
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
...addSharedStrings = function (zip, model) {
if (!model.sharedStrings || !model.sharedStrings.count) {
return PromishLib.Promish.resolve();
} else {
return new PromishLib.Promish(function(resolve) {
zip.append(model.sharedStrings.xml, {name: 'xl/sharedStrings.xml'});
resolve();
});
}
}...
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
...addStyles = function (zip, model) {
return new PromishLib.Promish(function(resolve) {
var xml = model.styles.xml;
if (xml) {
zip.append(xml, {name: 'xl/styles.xml'});
}
resolve();
});
}...
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
...addThemes = function (zip, model) {
return new PromishLib.Promish(function(resolve) {
var themes = model.themes || { theme1: theme1Xml };
Object.keys(themes).forEach(function(name) {
var xml = themes[name];
var path = 'xl/theme/' + name + '.xml';
zip.append(xml, {name: path});
});
resolve();
});
}...
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
...addWorkbook = function (zip, model, xform) {
return new PromishLib.Promish(function(resolve) {
zip.append(xform.toXml(model), {name: 'xl/workbook.xml'});
resolve();
});
}...
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
return self._finalize(zip);
});
},
writeFile: function (filename, options) {
var self = this;
...addWorkbookRels = function (zip, model) {
var count = 1;
var 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) {
relationships.push(
{rId: 'rId' + (count++), type: XLSX.RelType.SharedStrings, target: 'sharedStrings.xml'}
);
}
model.worksheets.forEach(function (worksheet) {
worksheet.rId = 'rId' + (count++);
relationships.push(
{rId: worksheet.rId, type: XLSX.RelType.Worksheet, target: 'worksheets/sheet' + worksheet.id + '.xml'}
);
});
return new PromishLib.Promish(function(resolve) {
var xform = new RelationshipsXform();
var xml = xform.toXml(relationships);
zip.append(xml, {name: 'xl/_rels/workbook.xml.rels'});
resolve();
});
}...
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
...addWorksheets = function (zip, model, worksheetXform) {
return new PromishLib.Promish(function(resolve) {
// preparation phase
var relationshipsXform = new RelationshipsXform();
// write sheets
model.worksheets.forEach(function (worksheet) {
var xmlStream = new XmlStream();
worksheetXform.render(xmlStream, worksheet);
zip.append(xmlStream.xml, {name: 'xl/worksheets/sheet' + worksheet.id + '.xml'});
if (worksheet.hyperlinks && worksheet.hyperlinks.length) {
xmlStream = new XmlStream();
relationshipsXform.render(xmlStream, worksheet.hyperlinks);
zip.append(xmlStream.xml, {name: 'xl/worksheets/_rels/sheet' + worksheet.id + '.xml.rels'});
}
});
resolve();
});
}...
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
...createInputStream = function () {
var self = this;
var model = {
worksheets: [],
worksheetHash: {},
worksheetRels: [],
themes: {},
};
// we have to be prepared to read the zip entries in whatever order they arrive
var promises = [];
var stream = new ZipStream.ZipReader();
stream.on('entry', function (entry) {
var promise = null;
var entryPath = entry.path;
if (entryPath[0] === '/') {
entryPath = entryPath.substr(1);
}
switch (entryPath) {
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.sheets = workbook.sheets;
model.definedNames = workbook.definedNames;
model.views = workbook.views;
model.properties = workbook.properties;
});
break;
case 'xl/_rels/workbook.xml.rels':
promise = self.parseRels(entry)
.then(function (relationships) {
model.workbookRels = relationships;
});
break;
case 'xl/sharedStrings.xml':
model.sharedStrings = new SharedStringsXform();
promise = model.sharedStrings.parseStream(entry);
break;
case 'xl/styles.xml':
model.styles = new StylesXform();
promise = model.styles.parseStream(entry);
break;
case 'docProps/app.xml':
var appXform = new AppXform();
promise = appXform.parseStream(entry)
.then(function(appProperties) {
Object.assign(model, {
company: appProperties.company,
manager: appProperties.manager
});
});
break;
case 'docProps/core.xml':
var coreXform = new CoreXform();
promise = coreXform.parseStream(entry)
.then(function(coreProperties) {
Object.assign(model, coreProperties);
});
break;
default:
promise =
self.processWorksheetEntry(entry, model) ||
self.processWorksheetRelsEntry(entry, model) ||
self.processThemeEntry(entry, model) ||
self.processIgnoreEntry(entry);
break;
}
if (promise) {
promises.push(promise);
promise = null;
}
});
stream.on('finished', function () {
PromishLib.Promish.all(promises)
.then(function () {
self.reconcile(model);
// apply model
self.workbook.model = model;
})
.then(function () {
stream.emit('done');
})
.catch(function (error) {
stream.emit('error', error);
});
});
return stream;
}...
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
...load = function (data, options) {
var self = this;
if (options === undefined) {
options = {};
}
var zipStream = this.createInputStream();
return new PromishLib.Promish(function(resolve, reject) {
zipStream.on('done', function () {
resolve(self.workbook);
}).on('error', function (error) {
reject(error);
});
if (options.base64) {
var buffer = new Buffer(data.toString(), 'base64');
zipStream.write(buffer);
} else {
zipStream.write(data);
}
zipStream.end();
});
}n/a
parseRels = function (stream) {
var xform = new RelationshipsXform();
return xform.parseStream(stream);
}...
var entryPath = entry.path;
if (entryPath[0] === '/') {
entryPath = entryPath.substr(1);
}
switch (entryPath) {
case '_rels/.rels':
promise = self.parseRels(entry)
.then(function (relationships) {
model.globalRels = relationships;
});
break;
case 'xl/workbook.xml':
promise = self.parseWorkbook(entry)
...parseSharedStrings = function (stream) {
var xform = new SharedStringsXform();
return xform.parseStream(stream);
}n/a
parseWorkbook = function (stream) {
var xform = new WorkbookXform();
return xform.parseStream(stream);
}...
promise = self.parseRels(entry)
.then(function (relationships) {
model.globalRels = relationships;
});
break;
case 'xl/workbook.xml':
promise = self.parseWorkbook(entry)
.then(function (workbook) {
model.sheets = workbook.sheets;
model.definedNames = workbook.definedNames;
model.views = workbook.views;
model.properties = workbook.properties;
});
break;
...processIgnoreEntry = function (entry) {
entry.autodrain();
}...
break;
default:
promise =
self.processWorksheetEntry(entry, model) ||
self.processWorksheetRelsEntry(entry, model) ||
self.processThemeEntry(entry, model) ||
self.processIgnoreEntry(entry);
break;
}
if (promise) {
promises.push(promise);
promise = null;
}
...processThemeEntry = function (entry, model) {
var match = entry.path.match(/xl\/theme\/([a-zA-Z0-9]+)\.xml/);
if (match) {
return new PromishLib.Promish(function(resolve, reject) {
var name = match[1];
// TODO: stream entry into buffer and store the xml in the model.themes[]
var stream = new StreamBuf();
entry.on('error', reject);
stream.on('error', reject);
stream.on('finish', function() {
model.themes[name] = stream.read().toString();
resolve();
});
entry.pipe(stream);
});
}
}...
});
break;
default:
promise =
self.processWorksheetEntry(entry, model) ||
self.processWorksheetRelsEntry(entry, model) ||
self.processThemeEntry(entry, model) ||
self.processIgnoreEntry(entry);
break;
}
if (promise) {
promises.push(promise);
promise = null;
...processWorksheetEntry = function (entry, model) {
var match = entry.path.match(/xl\/worksheets\/sheet(\d+)\.xml/);
if (match) {
var sheetNo = match[1];
var xform = new WorksheetXform();
return xform.parseStream(entry)
.then(function (worksheet) {
worksheet.sheetNo = sheetNo;
model.worksheetHash[entry.path] = worksheet;
model.worksheets.push(worksheet);
});
}
}...
.then(function(coreProperties) {
Object.assign(model, coreProperties);
});
break;
default:
promise =
self.processWorksheetEntry(entry, model) ||
self.processWorksheetRelsEntry(entry, model) ||
self.processThemeEntry(entry, model) ||
self.processIgnoreEntry(entry);
break;
}
if (promise) {
...processWorksheetRelsEntry = function (entry, model) {
var match = entry.path.match(/xl\/worksheets\/_rels\/sheet(\d+)\.xml.rels/);
if (match) {
var sheetNo = match[1];
var xform = new RelationshipsXform();
return xform.parseStream(entry)
.then(function(relationships) {
model.worksheetRels[sheetNo] = relationships;
});
}
}...
Object.assign(model, coreProperties);
});
break;
default:
promise =
self.processWorksheetEntry(entry, model) ||
self.processWorksheetRelsEntry(entry, model) ||
self.processThemeEntry(entry, model) ||
self.processIgnoreEntry(entry);
break;
}
if (promise) {
promises.push(promise);
...read = function (stream) {
var self = this;
var zipStream = this.createInputStream();
return new PromishLib.Promish(function(resolve, reject) {
zipStream.on('done', function () {
resolve(self.workbook);
}).on('error', function (error) {
reject(error);
});
stream.pipe(zipStream);
});
}...
workbook.csv.readFile(filename)
.then(function(worksheet) {
// use workbook or worksheet
});
// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
.then(function(worksheet) {
// use workbook or worksheet
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.csv.createInputStream());
...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;
});
}...
### XLSX
#### Reading XLSX
```javascript
// read from a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
...reconcile = function (model) {
var workbookXform = new WorkbookXform();
var worksheetXform = new WorksheetXform();
workbookXform.reconcile(model);
var sheetOptions = {
styles: model.styles,
sharedStrings: model.sharedStrings,
date1904: model.properties.date1904
};
model.worksheets.forEach(function (worksheet) {
worksheet.relationships = model.worksheetRels[worksheet.sheetNo];
worksheetXform.reconcile(worksheet, sheetOptions);
});
// delete unnecessary parts
model.worksheetHash = undefined;
model.worksheetRels = undefined;
model.globalRels = undefined;
model.sharedStrings = undefined;
model.workbookRels = undefined;
model.sheetDefs = undefined;
model.styles = undefined;
}...
return false;
}
},
reconcile: function reconcile(model, options) {
if (model) {
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.reconcile(childModel, options);
});
}
}
});
},{"../../utils/utils":20,"./base-xform":25}],39:[function(require,module,exports){
...write = function (stream, options) {
options = options || {};
var self = this;
var model = self.workbook.model;
var zip = new ZipStream.ZipWriter();
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 SharedStringsXform();
// add a style manager to handle cell formats, fonts, etc.
model.styles = model.useStyles ? new StylesXform(true) : new StylesXform.Mock();
// prepare all of the things before the render
var workbookXform = new WorkbookXform();
var worksheetXform = new WorksheetXform();
var prepareOptions = {
sharedStrings: model.sharedStrings,
styles: model.styles,
date1904: model.properties.date1904
};
workbookXform.prepare(model);
model.worksheets.forEach(function (worksheet) {
worksheetXform.prepare(worksheet, prepareOptions);
});
// render
var promises = [
self.addContentTypes(zip, model),
self.addApp(zip, model),
self.addCore(zip, model),
self.addThemes(zip, model),
self.addOfficeRels(zip, model)
];
return PromishLib.Promish.all(promises)
.then(function () {
return self.addWorksheets(zip, model, worksheetXform);
})
.then(function () {
// Some things can only be done after all the worksheets have been processed
var afters = [
self.addSharedStrings(zip, model),
self.addStyles(zip, model),
self.addWorkbookRels(zip, model)
];
return PromishLib.Promish.all(afters);
})
.then(function () {
return self.addWorkbook(zip, model, workbookXform);
})
.then(function () {
return self._finalize(zip);
});
}...
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
```
### CSV
...writeBuffer = function (options) {
var self = this;
var stream = new StreamBuf();
return self.write(stream, options)
.then(function() {
return stream.read();
});
}n/a
writeFile = function (filename, options) {
var self = this;
var stream = fs.createWriteStream(filename);
return new PromishLib.Promish(function(resolve, reject) {
stream.on('finish', function () {
resolve();
});
stream.on('error', function (error) {
reject(error);
});
self.write(stream, options)
.then(function () {
stream.end();
})
.catch(function (error) {
reject(error);
});
});
}...
```
#### Writing XLSX
```javascript
// write to a file
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
...xml_stream = function () {
this._xml = [];
this._stack = [];
this._rollbacks = [];
}n/a
addAttribute = function (name, value) {
if (!this.open) {
throw new Error('Cannot write attributes to node if it is not open');
}
pushAttribute(this._xml, name, value);
}...
}
},
render: function render(xmlStream, model) {
if (model && model.length) {
xmlStream.openNode(this.tag, this.$);
if (this.count) {
xmlStream.addAttribute(this.$count, model.length);
}
var childXform = this.childXform;
model.forEach(function (childModel) {
childXform.render(xmlStream, childModel);
});
...addAttributes = function (attrs) {
if (!this.open) {
throw new Error('Cannot write attributes to node if it is not open');
}
pushAttributes(this._xml, attrs);
}...
utils.inherits(DateXform, BaseXform, {
render: function render(xmlStream, model) {
if (model) {
xmlStream.openNode(this.tag);
if (this.attrs) {
xmlStream.addAttributes(this.attrs);
}
if (this.attr) {
xmlStream.addAttribute(this.attr, this._format(model));
} else {
xmlStream.writeText(this._format(model));
}
xmlStream.closeNode();
...addRollback = function () {
this._rollbacks.push({
xml: this._xml.length,
stack: this._stack.length,
leaf: this.leaf,
open: this.open
});
}...
get tag() {
return 'sheetPr';
},
render: function render(xmlStream, model) {
if (model) {
xmlStream.addRollback();
xmlStream.openNode('sheetPr');
var inner = false;
inner = this.map.tabColor.render(xmlStream, model.tabColor) || inner;
inner = this.map.pageSetUpPr.render(xmlStream, model.pageSetup) || inner;
if (inner) {
...closeAll = function () {
while (this._stack.length) {
this.closeNode();
}
}...
this._stack.splice(r.stack, this._stack.length - r.stack);
}
this.leaf = r.leaf;
this.open = r.open;
},
get xml() {
this.closeAll();
return this._xml.join('');
}
};
},{"./under-dash":19,"./utils":20}],22:[function(require,module,exports){
/**
...closeNode = function () {
var node = this._stack.pop();
var xml = this._xml;
if (this.leaf) {
xml.push(CLOSE_SLASH_ANGLE);
} else {
xml.push(OPEN_ANGLE_SLASH);
xml.push(node);
xml.push(CLOSE_ANGLE);
}
this.open = false;
this.leaf = false;
}...
},
leafNode: function leafNode(name, attributes, text) {
this.openNode(name, attributes);
if (text !== undefined) {
// zeros need to be written
this.writeText(text);
}
this.closeNode();
},
closeAll: function closeAll() {
while (this._stack.length) {
this.closeNode();
}
},
...commit = function () {
this._rollbacks.pop();
}...
// Note: this operation will not affect other rows
row.splice(3,2);
// remove one cell and insert two more (cells to the right of the cut cell will be shifted right)
row.splice(4,1,'new value 1', 'new value 2');
// Commit a completed row to stream
row.commit();
// row metrics
var rowSize = row.cellCount;
var numValues = row.actualCellCount;
```
## Handling Individual Cells
...leafNode = function (name, attributes, text) {
this.openNode(name, attributes);
if (text !== undefined) { // zeros need to be written
this.writeText(text);
}
this.closeNode();
}...
var BaseXform = require('../base-xform');
var WorksheetXform = module.exports = function () {};
utils.inherits(WorksheetXform, BaseXform, {
render: function render(xmlStream, model) {
xmlStream.leafNode('sheet', {
sheetId: model.id,
name: model.name,
'r:id': model.rId
});
},
parseOpen: function parseOpen(node) {
...openNode = function (name, attributes) {
var parent = this.tos;
var xml = this._xml;
if (parent && this.open) {
xml.push(CLOSE_ANGLE);
}
this._stack.push(name);
// start streaming node
xml.push(OPEN_ANGLE);
xml.push(name);
pushAttributes(xml, attributes);
this.leaf = true;
this.open = true;
}...
xml.push(node);
xml.push(CLOSE_ANGLE);
}
this.open = false;
this.leaf = false;
},
leafNode: function leafNode(name, attributes, text) {
this.openNode(name, attributes);
if (text !== undefined) {
// zeros need to be written
this.writeText(text);
}
this.closeNode();
},
...openXml = function (docAttributes) {
var xml = this._xml;
// <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
xml.push('<?xml');
pushAttributes(xml, docAttributes);
xml.push('?>\n');
}...
});
if (printAreas.length) {
model.definedNames = model.definedNames.concat(printAreas);
}
},
render: function render(xmlStream, model) {
xmlStream.openXml(XmlStream.StdDocAttributes);
xmlStream.openNode('workbook', WorkbookXform.WORKBOOK_ATTRIBUTES);
this.map.fileVersion.render(xmlStream);
this.map.workbookPr.render(xmlStream, model.properties);
this.map.bookViews.render(xmlStream, model.views);
this.map.sheets.render(xmlStream, model.sheets);
this.map.definedNames.render(xmlStream, model.definedNames);
...rollback = function () {
var r = this._rollbacks.pop();
if (this._xml.length > r.xml) {
this._xml.splice(r.xml, this._xml.length - r.xml);
}
if (this._stack.length > r.stack) {
this._stack.splice(r.stack, this._stack.length - r.stack);
}
this.leaf = r.leaf;
this.open = r.open;
}...
inner = this.map.tabColor.render(xmlStream, model.tabColor) || inner;
inner = this.map.pageSetUpPr.render(xmlStream, model.pageSetup) || inner;
if (inner) {
xmlStream.closeNode();
xmlStream.commit();
} else {
xmlStream.rollback();
}
}
},
parseOpen: function parseOpen(node) {
if (this.parser) {
this.parser.parseOpen(node);
...writeText = function (text) {
var xml = this._xml;
if (this.open) {
xml.push(CLOSE_ANGLE);
this.open = false;
}
this.leaf = false;
xml.push(utils.xmlEncode(text.toString()));
}...
this.open = false;
this.leaf = false;
},
leafNode: function leafNode(name, attributes, text) {
this.openNode(name, attributes);
if (text !== undefined) {
// zeros need to be written
this.writeText(text);
}
this.closeNode();
},
closeAll: function closeAll() {
while (this._stack.length) {
this.closeNode();
...writeXml = function (xml) {
if (this.open) {
this._xml.push(CLOSE_ANGLE);
this.open = false;
}
this.leaf = false;
this._xml.push(xml);
}...
utils.inherits(StaticXform, BaseXform, {
render: function(xmlStream) {
if (!this._xml) {
var stream = new XmlStream();
build(stream, this._model);
this._xml = stream.xml;
}
xmlStream.writeXml(this._xml);
},
parseOpen: function() {
return true;
},
parseText: function() {
},
...ZipReader = function () {
var self = this;
this.count = 0;
this.jsZip = new JSZip();
this.stream = new StreamBuf();
this.stream.on('finish', function() {
self._process();
});
}...
worksheetHash: {},
worksheetRels: [],
themes: {},
};
// we have to be prepared to read the zip entries in whatever order they arrive
var promises = [];
var stream = new ZipStream.ZipReader();
stream.on('entry', function (entry) {
var promise = null;
var entryPath = entry.path;
if (entryPath[0] === '/') {
entryPath = entryPath.substr(1);
}
...ZipWriter = function () {
this.zip = new JSZip();
this.stream = new StreamBuf();
}...
zip.finalize();
});
},
write: function (stream, options) {
options = options || {};
var self = this;
var model = self.workbook.model;
var zip = new ZipStream.ZipWriter();
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();
...