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.

$ npm install xlsx-populate

Here is a basic example:

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

// Load the input workbook from file.
var workbook = Workbook.fromBlankSync();

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

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

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);

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");

You can set the cell value or formula:

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

You can set multiple values along a row:

// Operate on row 4, set values at A4 and D4
sheet.setColumnValues(4, { 'A': 'abc', 'D': 123 });

// Same operation
sheet.setColumnValues(4, { 1: 'abc', 4: 123 });

You can share a formula at a given cell, along a row or column:

// Share formula at A3 along row ending with C3
sheet.getCell('A3').shareFormulaUntil('C3');

// Share formula at C1 along column ending with C3
sheet.getCell('C1').shareFormulaUntil('C3');

You can get a region of cells:

// The following performs the same operation
sheet.getCellRange('A1:B2');
sheet.getCellRange(['A1', 'B2']);
sheet.getCellRange('A1', 'B2');
sheet.getCellRange(sheet.getCell('A1'), sheet.getCell('B2'));

// Each returns:
// [ sheet.getCell('A1'), sheet.getCell('A2'), sheet.getCell('B1'), sheet.getCell('B2') ]

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());
    });
});

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

$ npm test

JSHint and JSCS are used to ensure code quality. To run these, run:

$ npm run jshint
$ npm run jscs

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

$ npm run docs

Cell

Sheet

Workbook

• Cell
  • new Cell(sheet, row, column, cellNode)
  • .getSheet() ⇒ Sheet
  • .getRow() ⇒ number
  • .getColumn() ⇒ number
  • .getAddress() ⇒ string
  • .getFullAddress() ⇒ string
  • .setValue(value) ⇒ Cell
  • .setFormula(formula, calculatedValue, sharedIndex, sharedRef) ⇒ Cell
  • .isSharedFormula(isSource) ⇒ boolean
  • .hasSameRow(otherCell) ⇒ boolean
  • .hasSameColumn(otherCell) ⇒ boolean
  • .isSame(otherCell) ⇒ boolean
  • .shareFormulaUntil(lastSharedCell) ⇒ Cell
  • .getRelativeCell(rowOffset, columnOffset) ⇒ Cell

Initializes a new Cell.

Gets the parent sheet.

Kind: instance method of Cell

Gets the row of the cell.

Kind: instance method of Cell

Gets the column of the cell.

Kind: instance method of Cell

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

Kind: instance method of Cell

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

Kind: instance method of Cell

Sets the value of the cell. Returns itself.

Kind: instance method of Cell

Sets the formula for a cell (with optional precalculated value). Returns itself.

Kind: instance method of Cell

Check if the current cell is a shared formula. When isSource is set true, the function will check if the formula is defined.

Kind: instance method of Cell

Check if both cells share the same row. Return true if rows are equal to otherCell, otherwise false.

Kind: instance method of Cell

Check if both cells share the same column. Return true if columns are equal to otherCell, otherwise false.

Kind: instance method of Cell

Check if both cells share the same row and column. Return true if row and column are equal to otherCell, otherwise false.

Kind: instance method of Cell

If this cell is the source of a shared formula, then assign all cells from this cell to lastSharedCell. Note that lastSharedCell must share the same row or column. Returns itself.

Kind: instance method of Cell

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

Kind: instance method of Cell

Kind: global class

• Sheet
  • new Sheet(workbook, name, sheetNode, sheetXML)
  • .getWorkbook() ⇒ Workbook
  • .getName() ⇒ String
  • .getCell() ⇒ Cell
  • .getCellRange() ⇒ Array
  • .setColumnValues() ⇒ Sheet

Initializes a new Sheet.

Gets the parent workbook.

Kind: instance method of Sheet

Gets the name of the sheet.

Kind: instance method of Sheet

Gets the cell with either the provided row and column or address. If passed nothing, then null is returned. If passed a Cell, then the Cell is returned.

Kind: instance method of Sheet

Gets the cells within a described cell region. Input parameters are of two cells which are endpoints of a rectangluar region. Returns an array of Cells.

Kind: instance method of Sheet

Given a row, assign column values to the row for each column name and value pair provided.

Kind: instance method of Sheet

Kind: global class

• Workbook
  • new Workbook(data)
  • instance
    • .getSheet(sheetNameOrIndex) ⇒ Sheet
    • .getNamedCell(cellName) ⇒ Cell
    • .output() ⇒ Buffer
    • .toFile(path, cb)
    • .toFileSync(path)
  • static
    • .fromFile(path, cb)
    • .fromFileSync(path) ⇒ Workbook
    • .fromBlank(cb)
    • .fromBlankSync() ⇒ Workbook

Initializes a new Workbook.

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

Kind: instance method of Workbook

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

Kind: instance method of Workbook

Gets the output.

Kind: instance method of Workbook

Writes to file with the given path.

Kind: instance method of Workbook

Wirtes to file with the given path synchronously.

Kind: instance method of Workbook

Creates a Workbook from the file with the given path.

Kind: static method of Workbook

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

Kind: static method of Workbook

Creates a blank Workbook.

Kind: static method of Workbook

Creates a blank Workbook synchronously.

Kind: static method of Workbook