simonaberry/xlsx-populate

Node.js module to populate XLSX templates.

xlsx-populate

Node.js module to populate Excel XLSX templates. This module does not parse Excel workbooks. There are good modules for this already. The purpose of this module is to open existing Excel XLSX workbook templates that have styling in place and populate with data.

Installation

$ npm install xlsx-populate

Usage

Here is a basic example:

var Workbook = require('xlsx-populate');

// Load the input workbook from file.
var workbook = Workbook.fromFileSync("./Book1.xlsx");

// Modify the workbook.
workbook.getSheet("Sheet1").getCell("A1").setValue("This is neat!");

// Write to file.
workbook.toFileSync("./out.xlsx");

Getting Sheets

You can get sheets from a Workbook object by either name or index (0-based):

// Get sheet with name "Sheet1".
var sheet = workbook.getSheet("Sheet1");

// Get the first sheet.
var sheet = workbook.getSheet(0);

Getting Cells

You can get a cell from a sheet by either address or row and column:

// Get cell "A5" by address.
var cell = sheet.getCell("A5");

// Get cell "A5" by row and column.
var cell = sheet.getCell(5, 1);

You can also get named cells directly from the Workbook:

// Get cell named "Foo".
var cell = sheet.getNamedCell("Foo");

Setting Cell Contents

You can set the cell value or formula:

cell.setValue("foo");
cell.setValue(5.6);
cell.setFormula("SUM(A1:A5)");

Populating Lots of Data

If you need to populate lots of data, you should cache references to sheets and rows as each get method adds execution time:

var sheet = workbook.getSheet("Sheet1");
for (var rowNum = 1; rowNum < 1000; rowNum++) {
    var row = sheet.getRow(rowNum);
    for (var colNum = 1; colNum < 100; colNum++) {
        var cell = row.getCell(colNum);
        cell.setValue("foo");
    }
}

Setting Date Values

Excel stores date/times as the number of days since 1/1/1900 (almost). It just applies a number formatting to make the number appear as a date. Number formats are not yet supported by xlsx-populate so if you set a date value in a cell without a date format already, it will likely appear as a number. The solution is to simply apply the appropriate date/time format to the cell in your template before populating with xlsx-populate.

Serving from Express

You can serve the workbook with express with a route like this:

router.get("/download", function (req, res) {
    // Open the workbook.
    var workbook = Workbook.fromFile("input.xlsx", function (err, workbook) {
        if (err) return res.status(500).send(err);

        // Make edits.
        workbook.getSheet(0).getCell("A1").setValue("foo");

        // Set the output file name.
        res.attachment("output.xlsx");

        // Send the workbook.
        res.send(workbook.output());
    });
});

Development

Running Tests

Tests are run automatically on Travis CI. They can (and should) be triggered locally with:

$ npm test

Or they can be run when files change using gulp.js:

$ gulp

Code Linting

ESLintis used to ensure code quality. To run this:

$ npm run eslint

Generating Documentation

The API reference documentation below is generated by jsdoc-to-markdown. To generate an updated README.md, run:

$ npm run docs

API Reference

Classes

Cell

Row

Sheet

Workbook

Cell

Kind: global class

• Cell
  • .toString() ⇒ string
  • .getRow() ⇒ Row
  • .getSheet() ⇒ Sheet
  • .getAddress() ⇒ string
  • .getRowNumber() ⇒ number
  • .getColumnNumber() ⇒ number
  • .getColumnName() ⇒ number
  • .getFullAddress() ⇒ string
  • .setValue(value) ⇒ Cell
  • .getRelativeCell(rowOffset, columnOffset) ⇒ Cell
  • .setFormula(formula, [calculatedValue], [sharedIndex], [sharedRef]) ⇒ Cell
  • .shareFormulaUntil(lastSharedCell) ⇒ Cell

cell.toString() ⇒ string

Get node information.

Kind: instance method of Cell
Returns: string - The cell information.

cell.getRow() ⇒ Row

Gets the parent row of the cell.

Kind: instance method of Cell
Returns: Row - The parent row.

cell.getSheet() ⇒ Sheet

Gets the parent sheet.

Kind: instance method of Cell
Returns: Sheet - The parent sheet.

cell.getAddress() ⇒ string

Gets the address of the cell (e.g. "A5").

Kind: instance method of Cell
Returns: string - The cell address.

cell.getRowNumber() ⇒ number

Gets the row number of the cell.

Kind: instance method of Cell
Returns: number - The row number.

cell.getColumnNumber() ⇒ number

Gets the column number of the cell.

Kind: instance method of Cell
Returns: number - The column number.

cell.getColumnName() ⇒ number

Gets the column name of the cell.

Kind: instance method of Cell
Returns: number - The column name.

cell.getFullAddress() ⇒ string

Gets the full address of the cell including sheet (e.g. "Sheet1!A5").

Kind: instance method of Cell
Returns: string - The full address.

cell.setValue(value) ⇒ Cell

Sets the value of the cell.

Kind: instance method of Cell
Returns: Cell - The cell.

Param	Type	Description
value	*	The value to set.

cell.getRelativeCell(rowOffset, columnOffset) ⇒ Cell

Returns a cell with a relative position to the offsets provided.

Kind: instance method of Cell
Returns: Cell - The relative cell.

Param	Type	Description
rowOffset	number	Offset from this.getRowNumber().
columnOffset	number	Offset from this.getColumnNumber().

cell.setFormula(formula, [calculatedValue], [sharedIndex], [sharedRef]) ⇒ Cell

Sets the formula for a cell (with optional pre-calculated value).

Kind: instance method of Cell
Returns: Cell - The cell.

Param	Type	Description
formula	string	The formula to set.
[calculatedValue]	*	The pre-calculated value.
[sharedIndex]	number	Unique non-negative integer value to represent the formula.
[sharedRef]	string	Range of cells referencing this formala, for example: "A1:A3".

cell.shareFormulaUntil(lastSharedCell) ⇒ Cell

If this cell is the source of a shared formula, then assign all cells from this cell to lastSharedCell its shared index. Note that lastSharedCell must share the same row or column, such that this.getRowNumber() <= lastSharedCell.getRowNumber() AND, this.getColumnNumber() <= lastSharedCell.getColumnNumber()

Kind: instance method of Cell
Returns: Cell - The shared formula source cell.

Param	Type	Description
lastSharedCell	*	String address or cell to share formula up until.

Row

Kind: global class

• Row
  • .getSheet() ⇒ Sheet
  • .getRowNumber() ⇒ number
  • .getCell(columnNumber) ⇒ Cell

row.getSheet() ⇒ Sheet

Gets the parent sheet.

Kind: instance method of Row
Returns: Sheet - The parent sheet.

row.getRowNumber() ⇒ number

Gets the row number of the row.

Kind: instance method of Row
Returns: number - The row number.

row.getCell(columnNumber) ⇒ Cell

Gets the cell in the row with the provided column number.

Kind: instance method of Row
Returns: Cell - The cell with the provided column number.

Param	Type	Description
columnNumber	number	The column number.

Sheet

Kind: global class

• Sheet
  • .getWorkbook() ⇒ Workbook
  • .getName() ⇒ string
  • .setName(name) ⇒ undefined
  • .getRow(rowNumber) ⇒ Row
  • .getCell(address) ⇒ Cell
  • .getCell(rowNumber, columnNumber) ⇒ Cell

sheet.getWorkbook() ⇒ Workbook

Gets the parent workbook.

Kind: instance method of Sheet
Returns: Workbook - The parent workbook.

sheet.getName() ⇒ string

Gets the name of the sheet.

Kind: instance method of Sheet
Returns: string - The name of the sheet.

sheet.setName(name) ⇒ undefined

Set the name of the sheet.

Kind: instance method of Sheet

Param	Type	Description
name	string	The new name of the sheet.

sheet.getRow(rowNumber) ⇒ Row

Gets the row with the given number.

Kind: instance method of Sheet
Returns: Row - The row with the given number.

Param	Type	Description
rowNumber	number	The row number.

sheet.getCell(address) ⇒ Cell

Gets the cell with the given address.

Kind: instance method of Sheet
Returns: Cell - The cell.

Param	Type	Description
address	string	The address of the cell.

sheet.getCell(rowNumber, columnNumber) ⇒ Cell

Gets the cell with the given row and column numbers.

Kind: instance method of Sheet
Returns: Cell - The cell.

Param	Type	Description
rowNumber	number	The row number of the cell.
columnNumber	number	The column number of the cell.

Workbook

Kind: global class

• Workbook
  • instance
    • .createSheet(sheetName, [index]) ⇒ Sheet
    • .getSheet(sheetNameOrIndex) ⇒ Sheet
    • .getNamedCell(cellName) ⇒ Cell
    • .output() ⇒ Buffer
    • .toFile(path, cb) ⇒ undefined
    • .toFileSync(path) ⇒ undefined
  • static
    • .fromFile(path, cb) ⇒ undefined
    • .fromFileSync(path) ⇒ Workbook
    • .fromBlank(cb) ⇒ undefined
    • .fromBlankSync() ⇒ Workbook

workbook.createSheet(sheetName, [index]) ⇒ Sheet

Create a new sheet.

Kind: instance method of Workbook
Returns: Sheet - The new sheet.

Param	Type	Description
sheetName	string	The name of the sheet. Must be unique.
[index]	number	The position of the sheet (0-based). Omit to insert at the end.

workbook.getSheet(sheetNameOrIndex) ⇒ Sheet

Gets the sheet with the provided name or index (0-based).

Kind: instance method of Workbook
Returns: Sheet - The sheet, if found.

Param	Type	Description
sheetNameOrIndex	string | number	The sheet name or index.

workbook.getNamedCell(cellName) ⇒ Cell

Get a named cell. (Assumes names with workbook scope pointing to single cells.)

Kind: instance method of Workbook
Returns: Cell - The cell, if found.

Param	Type	Description
cellName	string	The name of the cell.

workbook.output() ⇒ Buffer

Gets the output.

Kind: instance method of Workbook
Returns: Buffer - A node buffer for the generated Excel workbook.

workbook.toFile(path, cb) ⇒ undefined

Writes to file with the given path.

Kind: instance method of Workbook

Param	Type	Description
path	string	The path of the file.
cb	function	A callback.

workbook.toFileSync(path) ⇒ undefined

Writes to file with the given path synchronously.

Kind: instance method of Workbook

Param	Type	Description
path	string	The path of the file.

Workbook.fromFile(path, cb) ⇒ undefined

Creates a Workbook from the file with the given path.

Kind: static method of Workbook

Param	Type	Description
path	string	The path of the file.
cb	function	A callback with the new workbook.

Workbook.fromFileSync(path) ⇒ Workbook

Creates a Workbook from the file with the given path synchronously.

Kind: static method of Workbook
Returns: Workbook - The parsed workbook.

Param	Type	Description
path	string	The path of the file.

Workbook.fromBlank(cb) ⇒ undefined

Creates a blank Workbook.

Kind: static method of Workbook

Param	Type	Description
cb	function	A callback with the new workbook.

Workbook.fromBlankSync() ⇒ Workbook

Creates a blank Workbook synchronously.

Kind: static method of Workbook
Returns: Workbook - The new workbook.