function Workbook(opts) {
_classCallCheck(this, Workbook);
opts = opts ? opts : {};
this.logger = new SlothLogger.Logger({
logLevel: Number.isNaN(parseInt(opts.logLevel)) ? 0 : parseInt(opts.logLevel)
});
this.opts = _.merge({}, workbookDefaultOpts, opts);
this.sheets = [];
this.sharedStrings = [];
this.styles = [];
this.stylesLookup = {};
this.dxfCollection = new DXFCollection(this);
this.mediaCollection = new MediaCollection();
this.definedNameCollection = new DefinedNameCollection();
this.styleData = {
'numFmts': [],
'fonts': [],
'fills': [new Fill({ type: 'pattern', patternType: 'none' }), new Fill({ type: 'pattern', patternType: 'gray125' })],
'borders': [new Border()],
'cellXfs': [{
'borderId': null,
'fillId': null,
'fontId': 0,
'numFmtId': null
}]
};
// Lookups for style components to quickly find existing entries
// - Lookup keys are stringified JSON of a style's toObject result
// - Lookup values are the indexes for the actual entry in the styleData arrays
this.styleDataLookup = {
'fonts': {},
'fills': this.styleData.fills.reduce(function (ret, fill, index) {
ret[JSON.stringify(fill.toObject())] = index;
return ret;
}, {}),
'borders': this.styleData.borders.reduce(function (ret, border, index) {
ret[JSON.stringify(border.toObject())] = index;
return ret;
}, {})
};
// Set Default Font and Style
this.createStyle({ font: this.opts.defaultFont });
}...
### Basic Usage
```javascript
// Require library
var xl = require('excel4node');
// Create a new instance of a Workbook class
var wb = new xl.Workbook();
// Add Worksheets to the workbook
var ws = wb.addWorksheet('Sheet 1');
var ws2 = wb.addWorksheet('Sheet 2');
// Create a reusable style
var style = wb.createStyle({
...function getExcelAlpha(colNum) {
var remaining = colNum;
var aCharCode = 65;
var columnName = '';
while (remaining > 0) {
var mod = (remaining - 1) % 26;
columnName = String.fromCharCode(aCharCode + mod) + columnName;
remaining = (remaining - 1 - mod) / 26;
}
return columnName;
}...
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
```javascript
xl.getExcelRowCol('B5');
// returns { row: 5, col: 2}
```
xl.getExcelAlpha(column)
Accepts column as integer and returns corresponding column reference as alpha
```javascript
xl.getExcelAlpha(10);
// returns 'J'
```
...function getExcelCellRef(rowNum, colNum) {
var remaining = colNum;
var aCharCode = 65;
var columnName = '';
while (remaining > 0) {
var mod = (remaining - 1) % 26;
columnName = String.fromCharCode(aCharCode + mod) + columnName;
remaining = (remaining - 1 - mod) / 26;
}
return columnName + rowNum;
}...
Accepts column as integer and returns corresponding column reference as alpha
```javascript
xl.getExcelAlpha(10);
// returns 'J'
```
xl.getExcelCellRef(row, column)
Accepts row and column as integers and returns Excel cell reference
```javascript
xl.getExcelCellRef(5, 3);
// returns 'C5'
```
...function getExcelRowCol(str) {
var numeric = str.split(/\D/).filter(function (el) {
return el !== '';
})[0];
var alpha = str.split(/\d/).filter(function (el) {
return el !== '';
})[0];
var row = parseInt(numeric, 10);
var col = alpha.toUpperCase().split('').reduce(function (a, b, index, arr) {
return a + (b.charCodeAt(0) - 64) * Math.pow(26, arr.length - index - 1);
}, 0);
return { row: row, col: col };
}...
ws.cell(3,1).bool(true).style(style).style({font: {size: 14}});
wb.write('Excel.xlsx');
```
## excelnode
excel4node comes with some generic functions and types
xl.getExcelRowCol(cellRef)
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
```javascript
xl.getExcelRowCol('B5');
// returns { row: 5, col: 2}
```
...function getExcelTS(date) {
var thisDt = new Date(date);
thisDt.setDate(thisDt.getDate() + 1);
// Take timezone into account when calculating date
thisDt.setMinutes(thisDt.getMinutes() - thisDt.getTimezoneOffset());
var epoch = new Date(1899, 11, 31);
// Take timezone into account when calculating epoch
epoch.setMinutes(epoch.getMinutes() - epoch.getTimezoneOffset());
// Get milliseconds between date sent to function and epoch
var diff2 = thisDt.getTime() - epoch.getTime();
var ts = diff2 / (1000 * 60 * 60 * 24);
return ts;
}...
Accepts row and column as integers and returns Excel cell reference
```javascript
xl.getExcelCellRef(5, 3);
// returns 'C5'
```
xl.getExcelTS(date)
Accepts Date object and returns an Excel timestamp
```javascript
var newDate = new Date('2015-01-01T00:00:00.0000Z');
xl.getExcelTS(newDate);
// Returns 42004.791666666664
```
...function writeToBuffer(wb) {
return new Promise(function (resolve, reject) {
var promiseObj = {
wb: wb,
xlsx: new JSZip(),
xmlOutVars: {}
};
if (promiseObj.wb.sheets.length === 0) {
promiseObj.wb.WorkSheet();
}
addRootContentTypesXML(promiseObj).then(addWorkSheetsXML).then(addRootRelsXML).then(addWorkBookXML).then(addWorkBookRelsXML
).then(addSharedStringsXML).then(addStylesXML).then(addDrawingsXML).then(function () {
wb.opts.jszip.type = 'nodebuffer';
promiseObj.xlsx.generateAsync(wb.opts.jszip).then(function (buf) {
resolve(buf);
}).catch(function (e) {
reject(e);
});
}).catch(function (e) {
wb.logger.error(e.stack);
reject(e);
});
});
}...
wb.write('ExcelFile.xlsx', res);
});
app.listen(3000, function () {
console.log('Example app listening on port 3000!');
});
```
wb.writeToBuffer();
The writeToBuffer() method access no parameters and returns a promise that resolves with the nodebuffer generated by the JSZip library
. This buffer can then be sent to other streams.
```javascript
var xl = require('excel4node');
var wb = new xl.Workbook();
wb.writeToBuffer().then(function (buffer) {
...class Workbook {
/**
* @class Workbook
* @param {Object} opts Workbook settings
* @param {Object} opts.jszip
* @param {String} opts.jszip.compression JSZip compression type. defaults to 'DEFLATE'
* @param {Object} opts.defaultFont
* @param {String} opts.defaultFont.color HEX value of default font color. defaults to #000000
* @param {String} opts.defaultFont.name Font name. defaults to Calibri
* @param {Number} opts.defaultFont.size Font size. defaults to 12
* @param {String} opts.defaultFont.family Font family. defaults to roman
* @param {String} opts.dataFormat Specifies the format for dates in the Workbook. defaults to 'm/d/yy'
* @returns {Workbook}
*/
constructor(opts) {
opts = opts ? opts : {};
this.logger = new SlothLogger.Logger({
logLevel: Number.isNaN(parseInt(opts.logLevel)) ? 0 : parseInt(opts.logLevel)
});
this.opts = _.merge({}, workbookDefaultOpts, opts);
this.sheets = [];
this.sharedStrings = [];
this.styles = [];
this.stylesLookup = {};
this.dxfCollection = new DXFCollection(this);
this.mediaCollection = new MediaCollection();
this.definedNameCollection = new DefinedNameCollection();
this.styleData = {
'numFmts': [],
'fonts': [],
'fills': [new Fill({ type: 'pattern', patternType: 'none' }), new Fill({ type: 'pattern', patternType: 'gray125' })],
'borders': [new Border()],
'cellXfs': [
{
'borderId': null,
'fillId': null,
'fontId': 0,
'numFmtId': null
}
]
};
// Lookups for style components to quickly find existing entries
// - Lookup keys are stringified JSON of a style's toObject result
// - Lookup values are the indexes for the actual entry in the styleData arrays
this.styleDataLookup = {
'fonts': {},
'fills': this.styleData.fills.reduce((ret, fill, index) => {
ret[JSON.stringify(fill.toObject())] = index;
return ret;
}, {}),
'borders': this.styleData.borders.reduce((ret, border, index) => {
ret[JSON.stringify(border.toObject())] = index;
return ret;
}, {})
};
// Set Default Font and Style
this.createStyle({ font: this.opts.defaultFont });
}
/**
* setSelectedTab
* @param {Number} tab number of sheet that should be displayed when workbook opens. tabs are indexed starting with 1
**/
setSelectedTab(id) {
this.sheets.forEach((s) => {
if (s.sheetId === id) {
s.opts.sheetView.tabSelected = 1;
} else {
s.opts.sheetView.tabSelected = 0;
}
});
}
/**
* writeToBuffer
* Writes Excel data to a node Buffer.
*/
writeToBuffer() {
return builder.writeToBuffer(this);
}
/**
* Generate .xlsx file.
* @param {String} fileName Name of Excel workbook with .xslx extension
* @param {http.response | callback} http response object or callback function (optional).
* If http response object is given, file is written to http response. Useful for web applications.
* If callback is given, callback called with (err, fs.Stats) passed
*/
write(fileName, handler) {
builder.writeToBuffer(this)
.then((buffer) => {
switch (typeof handler) {
// handler passed as http response object.
case 'object':
if (handler instanceof http.ServerResponse) {
handler.writeHead(200, {
'Content-Length': buffer.length,
'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Content-Disposition': 'attachment; filename="' + fileName + '"' ......
### Basic Usage
```javascript
// Require library
var xl = require('excel4node');
// Create a new instance of a Workbook class
var wb = new xl.Workbook();
// Add Worksheets to the workbook
var ws = wb.addWorksheet('Sheet 1');
var ws2 = wb.addWorksheet('Sheet 2');
// Create a reusable style
var style = wb.createStyle({
...(colNum) => {
let remaining = colNum;
let aCharCode = 65;
let columnName = '';
while (remaining > 0) {
let mod = (remaining - 1) % 26;
columnName = String.fromCharCode(aCharCode + mod) + columnName;
remaining = (remaining - 1 - mod) / 26;
}
return columnName;
}...
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
```javascript
xl.getExcelRowCol('B5');
// returns { row: 5, col: 2}
```
xl.getExcelAlpha(column)
Accepts column as integer and returns corresponding column reference as alpha
```javascript
xl.getExcelAlpha(10);
// returns 'J'
```
...(rowNum, colNum) => {
let remaining = colNum;
let aCharCode = 65;
let columnName = '';
while (remaining > 0) {
let mod = (remaining - 1) % 26;
columnName = String.fromCharCode(aCharCode + mod) + columnName;
remaining = (remaining - 1 - mod) / 26;
}
return columnName + rowNum;
}...
Accepts column as integer and returns corresponding column reference as alpha
```javascript
xl.getExcelAlpha(10);
// returns 'J'
```
xl.getExcelCellRef(row, column)
Accepts row and column as integers and returns Excel cell reference
```javascript
xl.getExcelCellRef(5, 3);
// returns 'C5'
```
...(str) => {
let numeric = str.split(/\D/).filter(function (el) {
return el !== '';
})[0];
let alpha = str.split(/\d/).filter(function (el) {
return el !== '';
})[0];
let row = parseInt(numeric, 10);
let col = alpha.toUpperCase().split('').reduce(function (a, b, index, arr) {
return a + (b.charCodeAt(0) - 64) * Math.pow(26, arr.length - index - 1);
}, 0);
return { row: row, col: col };
}...
ws.cell(3,1).bool(true).style(style).style({font: {size: 14}});
wb.write('Excel.xlsx');
```
## excelnode
excel4node comes with some generic functions and types
xl.getExcelRowCol(cellRef)
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
```javascript
xl.getExcelRowCol('B5');
// returns { row: 5, col: 2}
```
...(date) => {
let thisDt = new Date(date);
thisDt.setDate(thisDt.getDate() + 1);
// Take timezone into account when calculating date
thisDt.setMinutes(thisDt.getMinutes() - thisDt.getTimezoneOffset());
let epoch = new Date(1899, 11, 31);
// Take timezone into account when calculating epoch
epoch.setMinutes(epoch.getMinutes() - epoch.getTimezoneOffset());
// Get milliseconds between date sent to function and epoch
let diff2 = thisDt.getTime() - epoch.getTime();
let ts = diff2 / (1000 * 60 * 60 * 24);
return ts;
}...
Accepts row and column as integers and returns Excel cell reference
```javascript
xl.getExcelCellRef(5, 3);
// returns 'C5'
```
xl.getExcelTS(date)
Accepts Date object and returns an Excel timestamp
```javascript
var newDate = new Date('2015-01-01T00:00:00.0000Z');
xl.getExcelTS(newDate);
// Returns 42004.791666666664
```
...function arrayIntersectSafe(a, b) {
if (a instanceof Array && b instanceof Array) {
var ai = 0,
bi = 0;
var result = new Array();
while (ai < a.length && bi < b.length) {
if (a[ai] < b[bi]) {
ai++;
} else if (a[ai] > b[bi]) {
bi++;
} else {
result.push(a[ai]);
ai++;
bi++;
}
}
return result;
} else {
throw new TypeError('Both variables sent to arrayIntersectSafe must be arrays');
}
}...
var cellRange = excelRefs[0] + ':' + excelRefs[excelRefs.length - 1];
var rangeCells = excelRefs;
var okToMerge = true;
cellBlock.ws.mergedCells.forEach(function (cr) {
// Check to see if currently merged cells contain cells in new merge request
var curCells = utils.getAllCellsInExcelRange(cr);
var intersection = utils.arrayIntersectSafe(rangeCells, curCells);
if (intersection.length > 0) {
okToMerge = false;
cellBlock.ws.wb.logger.error('Invalid Range for: ' + cellRange + '. Some cells in this range are already
included in another merged cell range: ' + cr + '.');
}
});
if (okToMerge) {
cellBlock.ws.mergedCells.push(cellRange);
...function boolToInt(bool) {
if (bool === true) {
return 1;
}
if (bool === false) {
return 0;
}
if (parseInt(bool) === 1) {
return 1;
}
if (parseInt(bool) === 0) {
return 0;
}
throw new TypeError('Value sent to boolToInt must be true, false, 1 or 0');
}...
return new Promise(function (resolve, reject) {
var o = promiseObj.ws.opts.sheetFormat;
var ele = promiseObj.xml.ele('sheetFormatPr');
o.baseColWidth !== null ? ele.att('baseColWidth', o.baseColWidth) : null;
o.defaultColWidth !== null ? ele.att('defaultColWidth', o.defaultColWidth) : null;
o.defaultRowHeight !== null ? ele.att('defaultRowHeight', o.defaultRowHeight) : ele.att('defaultRowHeight',
16);
o.thickBottom !== null ? ele.att('thickBottom', utils.boolToInt(o.thickBottom
)) : null;
o.thickTop !== null ? ele.att('thickTop', utils.boolToInt(o.thickTop)) : null;
if (typeof o.defaultRowHeight === 'number') {
ele.att('customHeight', '1');
}
ele.up();
resolve(promiseObj);
...function generateRId() {
var text = 'R';
var possible = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
for (var i = 0; i < 16; i++) {
text += possible.charAt(Math.floor(Math.random() * possible.length));
}
return text;
}n/a
function getAllCellsInExcelRange(range) {
var cells = range.split(':');
var cell1props = getExcelRowCol(cells[0]);
var cell2props = getExcelRowCol(cells[1]);
return getAllCellsInNumericRange(cell1props.row, cell1props.col, cell2props.row, cell2props.col);
}...
var cellRange = excelRefs[0] + ':' + excelRefs[excelRefs.length - 1];
var rangeCells = excelRefs;
var okToMerge = true;
cellBlock.ws.mergedCells.forEach(function (cr) {
// Check to see if currently merged cells contain cells in new merge request
var curCells = utils.getAllCellsInExcelRange(cr);
var intersection = utils.arrayIntersectSafe(rangeCells, curCells);
if (intersection.length > 0) {
okToMerge = false;
cellBlock.ws.wb.logger.error('Invalid Range for: ' + cellRange + '. Some cells in this range are already
included in another merged cell range: ' + cr + '.');
}
});
if (okToMerge) {
...function getAllCellsInNumericRange(row1, col1, row2, col2) {
var response = [];
row2 = row2 ? row2 : row1;
col2 = col2 ? col2 : col1;
for (var i = row1; i <= row2; i++) {
for (var j = col1; j <= col2; j++) {
response.push(getExcelAlpha(j) + i);
}
}
return response.sort(sortCellRefs);
}n/a
function getExcelAlpha(colNum) {
var remaining = colNum;
var aCharCode = 65;
var columnName = '';
while (remaining > 0) {
var mod = (remaining - 1) % 26;
columnName = String.fromCharCode(aCharCode + mod) + columnName;
remaining = (remaining - 1 - mod) / 26;
}
return columnName;
}...
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
```javascript
xl.getExcelRowCol('B5');
// returns { row: 5, col: 2}
```
xl.getExcelAlpha(column)
Accepts column as integer and returns corresponding column reference as alpha
```javascript
xl.getExcelAlpha(10);
// returns 'J'
```
...function getExcelCellRef(rowNum, colNum) {
var remaining = colNum;
var aCharCode = 65;
var columnName = '';
while (remaining > 0) {
var mod = (remaining - 1) % 26;
columnName = String.fromCharCode(aCharCode + mod) + columnName;
remaining = (remaining - 1 - mod) / 26;
}
return columnName + rowNum;
}...
Accepts column as integer and returns corresponding column reference as alpha
```javascript
xl.getExcelAlpha(10);
// returns 'J'
```
xl.getExcelCellRef(row, column)
Accepts row and column as integers and returns Excel cell reference
```javascript
xl.getExcelCellRef(5, 3);
// returns 'C5'
```
...function getExcelRowCol(str) {
var numeric = str.split(/\D/).filter(function (el) {
return el !== '';
})[0];
var alpha = str.split(/\d/).filter(function (el) {
return el !== '';
})[0];
var row = parseInt(numeric, 10);
var col = alpha.toUpperCase().split('').reduce(function (a, b, index, arr) {
return a + (b.charCodeAt(0) - 64) * Math.pow(26, arr.length - index - 1);
}, 0);
return { row: row, col: col };
}...
ws.cell(3,1).bool(true).style(style).style({font: {size: 14}});
wb.write('Excel.xlsx');
```
## excelnode
excel4node comes with some generic functions and types
xl.getExcelRowCol(cellRef)
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
```javascript
xl.getExcelRowCol('B5');
// returns { row: 5, col: 2}
```
...function getExcelTS(date) {
var thisDt = new Date(date);
thisDt.setDate(thisDt.getDate() + 1);
// Take timezone into account when calculating date
thisDt.setMinutes(thisDt.getMinutes() - thisDt.getTimezoneOffset());
var epoch = new Date(1899, 11, 31);
// Take timezone into account when calculating epoch
epoch.setMinutes(epoch.getMinutes() - epoch.getTimezoneOffset());
// Get milliseconds between date sent to function and epoch
var diff2 = thisDt.getTime() - epoch.getTime();
var ts = diff2 / (1000 * 60 * 60 * 24);
return ts;
}...
Accepts row and column as integers and returns Excel cell reference
```javascript
xl.getExcelCellRef(5, 3);
// returns 'C5'
```
xl.getExcelTS(date)
Accepts Date object and returns an Excel timestamp
```javascript
var newDate = new Date('2015-01-01T00:00:00.0000Z');
xl.getExcelTS(newDate);
// Returns 42004.791666666664
```
...function getHashOfPassword(str) {
var curHash = '0000';
for (var i = str.length - 1; i >= 0; i--) {
curHash = _getHashForChar(str[i], curHash);
}
var curHashBin = parseInt(curHash, 16).toString(2);
var charCountBin = parseInt(str.length, 10).toString(2);
var saltBin = parseInt('CE4B', 16).toString(2);
var firstXOR = _bitXOR(curHashBin, charCountBin);
var finalHashBin = _bitXOR(firstXOR, saltBin);
var finalHash = String('0000' + parseInt(finalHashBin, 2).toString(16).toUpperCase()).slice(-4);
return finalHash;
}...
o.objects = o.objects !== null ? o.objects : true;
o.scenarios = o.scenarios !== null ? o.scenarios : true;
var ele = promiseObj.xml.ele('sheetProtection');
Object.keys(o).forEach(function (k) {
if (o[k] !== null) {
if (k === 'password') {
ele.att('password', utils.getHashOfPassword(o[k]));
} else {
ele.att(k, utils.boolToInt(o[k]));
}
}
});
ele.up();
})();
...function sortCellRefs(a, b) {
var aAtt = getExcelRowCol(a);
var bAtt = getExcelRowCol(b);
if (aAtt.col === bAtt.col) {
return aAtt.row - bAtt.row;
} else {
return aAtt.col - bAtt.col;
}
}n/a