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

Classes

Workbook

Sheet

Cell

• 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

new Workbook(data)

Initializes a new Workbook.

workbook.getSheet(sheetNameOrIndex) ⇒ Sheet

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

Kind: instance method of Workbook

workbook.getNamedCell(cellName) ⇒ Cell

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

Kind: instance method of Workbook

workbook.output() ⇒ Buffer

Gets the output.

Kind: instance method of Workbook

workbook.toFile(path, cb)

Writes to file with the given path.

Kind: instance method of Workbook

workbook.toFileSync(path)

Wirtes to file with the given path synchronously.

Kind: instance method of Workbook

Workbook.fromFile(path, cb)

Creates a Workbook from the file with the given path.

Kind: static method of Workbook

Workbook.fromFileSync(path) ⇒ Workbook

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

Kind: static method of Workbook

Sheet

Kind: global class

• Sheet
  • new Sheet(workbook, name, sheetNode, sheetXML)
  • .getWorkbook() ⇒ Workbook
  • .getName() ⇒ string
  • .getCell() ⇒ Cell

new Sheet(workbook, name, sheetNode, sheetXML)

Initializes a new Sheet.

sheet.getWorkbook() ⇒ Workbook

Gets the parent workbook.

Kind: instance method of Sheet

sheet.getName() ⇒ string

Gets the name of the sheet.

Kind: instance method of Sheet

sheet.getCell() ⇒ Cell

Gets the cell with either the provided row and column or address.

Kind: instance method of Sheet

Cell

Kind: global class

• Cell
  • new Cell(sheet, row, column, cellNode)
  • .getSheet() ⇒ Sheet
  • .getRow() ⇒ number
  • .getColumn() ⇒ number
  • .getAddress() ⇒ string
  • .getFullAddress() ⇒ string
  • .setValue(value) ⇒ Cell
  • .setFormula(formula, [calculatedValue]) ⇒ Cell

new Cell(sheet, row, column, cellNode)

Initializes a new Cell.

cell.getSheet() ⇒ Sheet

Gets the parent sheet.

Kind: instance method of Cell

cell.getRow() ⇒ number

Gets the row of the cell.

Kind: instance method of Cell

cell.getColumn() ⇒ number

Gets the column of the cell.

Kind: instance method of Cell

cell.getAddress() ⇒ string

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

Kind: instance method of Cell

cell.getFullAddress() ⇒ string

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

Kind: instance method of Cell

cell.setValue(value) ⇒ Cell

Sets the value of the cell.

Kind: instance method of Cell

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

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

Kind: instance method of Cell