xlsx-populate

Excel XLSX parser/generator written in JavaScript with Node.js and browser support, jQuery/d3-style method chaining, and a focus on keeping existing workbook features and styles in tact.

Table of Contents

• Installation
  • Node.js
  • Browser
• Usage
  • Populating Data
  • Parsing Data
  • Ranges
  • Rows and Columns
  • Find and Replace
  • Styles
  • Dates
  • Method Chaining
  • Serving from Express
  • Browser Usage
• Missing Features
• Contributing
  • How xlsx-populate Works
  • Setting up your Environment
  • Pull Request Checklist
  • Gulp Tasks
• Style Reference
• API Reference

Installation

Node.js

npm install xlsx-populate

Note that xlsx-populate uses ES6 features so only Node.js v4+ is supported.

Browser

xlsx-populate is written first for Node.js. We use browserify and babelify to transpile and pack up the module for use in the browser.

You have a number of options to include the code in the browser. You can download the combined, minified code from the browser directory in this repository or you can install with bower:

bower install xlsx-populate

After including the module in the browser, it is available globally as XlsxPopulate.

Alternatively, you can require this module using browserify. Since xlsx-populate uses ES6 features, you will also need to use babelify with babel-preset-es2015.

Usage

xlsx-populate has an extensive API for working with Excel workbooks. This section reviews the most common functions and use cases. Examples can also be found in the examples directory of the source code.

Populating Data

To populate data in a workbook, you first load one (either blank, from data, or from file). Then you can access sheets and cells within the workbook to manipulate them.

const XlsxPopulate = require('xlsx-populate');

// Load a new blank workbook
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        // Modify the workbook.
        workbook.sheet("Sheet1").cell("A1").value("This is neat!");
        
        // Write to file.
        return workbook.toFileAsync("./out.xlsx");
    });

Parsing Data

You can pull data out of existing workbooks using Cell.value as a getter without any arguments:

const XlsxPopulate = require('xlsx-populate');

// Load an existing workbook
XlsxPopulate.fromFileAsync("./Book1.xlsx")
    .then(workbook => {
        // Modify the workbook.
        const value = workbook.sheet("Sheet1").cell("A1").value();
        
        // Log the value.
        console.log(value);
    });

Ranges

xlsx-populate also supports ranges of cells to allow parsing/manipulation of multiple cells at once.

const r = workbook.sheet(0).range("A1:C3");

// Set all cell values to the same value:
r.value(5);

// Set the values using a 2D array:
r.value([
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9]
]);

// Set the values using a callback function:
r.value((cell, ri, ci, range) => Math.random());

A common use case is to simply pull all of the values out all at once. You can easily do that with the Sheet.usedRange method.

// Get 2D array of all values in the worksheet.
const values = workbook.sheet("Sheet1").usedRange().value();

Rows and Columns

You can access rows and columns in order to change size, hide/show, or access cells within:

// Get the B column, set its width and unhide it (assuming it was hidden).
sheet.column("B").width(25).hidden(false);

const cell = sheet.row(5).cell(3); // Returns the cell at C5. 

Find and Replace

You can search for occurrences of text in cells within the workbook or sheets and optionally replace them.

// Find all occurrences of the text "foo" in the workbook and replace with "bar".
workbook.find("foo", "bar"); // Returns array of matched cells

// Find the matches but don't replace. 
workbook.find("foo");

// Just look in the first sheet.
workbook.sheet(0).find("foo");

// Check if a particular cell matches the value.
workbook.sheet("Sheet1").cell("A1").find("foo"); // Returns true or false

Like String.replace, the find method can also take a RegExp search pattern and replace can take a function callback:

// Use a RegExp to replace all lowercase letters with uppercase
workbook.find(/[a-z]+/g, match => match.toUpperCase());

Styles

xlsx-populate supports a wide range of cell formatting. See the Style Reference for the various options.

To set/set a cell style:

// Set a single style
cell.style("bold", true);

// Set multiple styles
cell.style({ bold: true, italic: true });

// Get a single style
const bold = cell.style("bold"); // true
 
// Get multiple styles
const styles = cell.style(["bold", "italic"]); // { bold: true, italic: true } 

Similarly for ranges:

// Set all cells in range with a single style
range.style("bold", true);

// Set with a 2D array
range.style("bold", [[true, false], [false, true]]);

// Set with a callback function
range.style("bold", (cell, ri, ci, range) => Math.random() > 0.5);

// Set multiple styles using any combination
range.style({
    bold: true,
    italic: [[true, false], [false, true]],
    underline: (cell, ri, ci, range) => Math.random() > 0.5
});

Some styles take values that are more complex objects:

cell.style("fill", {
    type: "pattern",
    pattern: "darkDown",
    foreground: {
        rgb: "ff0000"
    },
    background: {
        theme: 3,
        tint: 0.4
    }
});

There are often shortcuts for the setters, but the getters will always return the full objects:

cell.style("fill", "0000ff");

const fill = cell.style("fill");
/*
fill is now set to:
{
    type: "solid",
    color: {
        rgb: "0000ff"
    }
}
*/

Number formats are one of the most common styles. They can be set using the numberFormat style.

cell.style("numberFormat", "0.00");

Information on how number format codes work can be found here. You can also look up the desired format code in Excel:

• Right-click on a cell in Excel with the number format you want.
• Click on "Format Cells..."
• Switch the category to "Custom" if it is not already.
• The code in the "Type" box is the format you should copy.

Dates

Excel stores date/times as the number of days since 1/1/1900 (sort of). It just applies a number formatting to make the number appear as a date. So to set a date value, you will need to also set a number format for a date if one doesn't already exist in the cell:

cell.value(new Date(2017, 1, 22)).style("numberFormat", "dddd, mmmm dd, yyyy");

When fetching the value of the cell, it will be returned as a number. To convert it to a date use XlsxPopulate.numberToDate:

const num = cell.value(); // 42788
const date = XlsxPopulate.numberToDate(num); // Wed Feb 22 2017 00:00:00 GMT-0500 (Eastern Standard Time)

Method Chaining

xlsx-populate uses method-chaining similar to that found in jQuery and d3. This lets you construct large chains of setters as desired:

workbook
    .sheet(0)
        .cell("A1")
            .value("foo")
            .style("bold", true)
        .relativeCell(1, 0)
            .formula("A1")
            .style("italic", true)
.workbook()
    .sheet(1)
        .range("A1:B3")
            .value(5)
        .cell(0, 0)
            .style("underline", "double");
        

Serving from Express

You can serve the workbook from express or other web servers with something like this:

router.get("/download", function (req, res, next) {
    // Open the workbook.
    XlsxPopulate.fromFileAsync("input.xlsx")
        .then(workbook => {
            // Make edits.
            workbook.sheet(0).cell("A1").value("foo");
            
            // Get the output
            return workbook.outputAsync();
        })
        .then(data => {
            // Set the output file name.
            res.attachment("output.xlsx");
            
            // Send the workbook.
            res.send(data);
        })
        .catch(next);
});

Browser Usage

Usage in the browser is almost the same. A functional example can be found in examples/browser/index.html. The library is exposed globally as XlsxPopulate. Existing workbooks can be loaded from a file:

// Assuming there is a file input in the page with the id 'file-input'
var file = document.getElementById("file-input").files[0];

// A File object is a special kind of blob.
XlsxPopulate.fromDataAsync(file)
    .then(function (workbook) {
        // ...
    });

You can also load from AJAX if you set the responseType to 'arraybuffer':

var req = new XMLHttpRequest();
req.open("GET", "http://...", true);
req.responseType = "arraybuffer";
req.onreadystatechange = function () {
    if (req.readyState === 4 && req.status === 200){
        XlsxPopulate.fromDataAsync(req.response)
            .then(function (workbook) {
                // ...
            }
    }
};

req.send();

To download the workbook, you can either export as a blob (default behavior) or as a base64 string. You can then insert a link into the DOM and click it:

XlsxPopulate.outputAsync()
    .then(function (blob) {
        if (window.navigator && window.navigator.msSaveOrOpenBlob) {
            // If IE, you must uses a different method.
            window.navigator.msSaveOrOpenBlob(blob, "out.xlsx");
        } else {
            var url = window.URL.createObjectURL(blob);
            var a = document.createElement("a");
            document.body.appendChild(a);
            a.href = url;
            a.download = "out.xlsx";
            a.click();
            window.URL.revokeObjectURL(url);
            document.body.removeChild(a);
        }
    });

Alternatively, you can download via a data URI, but this is not supported by IE:

XlsxPopulate.outputAsync("base64")
    .then(function (base64) {
        location.href = "data:" + XlsxPopulate.MIME_TYPE + ";base64," + base64;
    });

Missing Features

There are many, many features of the XLSX format that are not yet supported. If your use case needs something that isn't supported please open an issue to show your support. Better still, feel free to contribute a pull request!

Contributing

Pull requests are very much welcome! If you'd like to contribute, please make sure to read this section carefully first.

How xlsx-populate Works

An XLSX workbook is essentially a zip of a bunch of XML files. xlsx-populate uses JSZip to unzip the workbook and sax-js to parse the XML documents into corresponding objects. As you call methods, xlsx-populate manipulates the content of those objects. When you generate the output, xlsx-populate uses xmlbuilder-js to convert the objects back to XML and then uses JSZip to rezip them back into a workbook.

The way in which xlsx-populate manipulates objects that are essentially the XML data is very different from the usual way parser/generator libraries work. Most other libraries will deserialize the XML into a rich object model. That model is then manipulated and serialized back into XML upon generation. The challenge with this approach is that the Office Open XML spec is HUGE. It is extremely difficult for libraries to be able to support the entire specification. So these other libraries will deserialize only the portion of the spec they support and any other content/styles in the workbook they don't support are lost. Since xlsx-populate just manipulates the XML data, it is able to preserve styles and other content while still only supporting a fraction of the spec.

Setting up your Environment

You'll need to make sure Node.js v4+ is installed (as xlsx-populate uses ES6 syntax). You'll also need to install gulp:

npm install -g gulp

Make sure you have git installed. Then follow this guide to see how to check out code, branch, and then submit your code as a pull request. When you check out the code, you'll first need to install the npm dependencies. From the project root, run:

npm install

The default gulp task is set up to watch the source files for updates and retest while you edit. From the project root just run:

gulp

You should see the test output in your console window. As you edit files the tests will run again and show you if you've broken anything. (Note that if you've added new files you'll need to restart gulp for the new files to be watched.)

Now write your code and make sure to add Jasmine unit tests. When you are finished, you need to build the code for the browser. Do that by running the gulp build command:

gulp build

Verify all is working, check in your code, and submit a pull request.

Pull Request Checklist

To make sure your code is consistent and high quality, please make sure to follow this checklist before submitting a pull request:

• Your code must follow the getter/setter pattern using a single function for both. Check arguments.length or use ArgHandler to distinguish.
• You must use valid JSDoc comments on all methods and classes. Use @private for private methods and @ignore for any public methods that are internal to xlsx-populate and should not be included in the public API docs.
• You must adhere to the configured ESLint linting rules. You can configure your IDE to display rule violations live or you can run gulp lint to see them.
• Use ES6 syntax. (This should be enforced by ESLint.)
• Make sure to have full Jasmine unit test coverage for your code.
• Make sure all tests pass successfully.
• Whenever possible, do not modify/break existing API behavior. This module adheres to the semantic versioning standard. So any breaking changes will require a major release.
• If your feature needs more documentation than just the JSDoc output, please add to the docs/template.md README file.

Gulp Tasks

xlsx-populate uses gulp as a build tool. There are a number of tasks:

• browser - Transpile and build client-side JavaScript project bundle using browserify and babelify.
• lint - Check project source code style using ESLint.
• unit - Run Jasmine unit tests.
• karma - Run unit tests in real browsers using Karma.
• blank - Convert a blank XLSX template into a JS buffer module to support fromBlankAsync.
• docs - Build this README doc by combining docs/template.md, API docs generated with jsdoc-to-markdown, and a table of contents generated with markdown-toc.
• watch - Watch files for changes and then run associated gulp task. (Used by the default task.)
• build - Run all gulp tasks, including linting and tests, and build the docs and browser bundle.
• default - Run blank, unit, and docs tasks and watch the source files for those tasks for changes.

Style Reference

Styles

Color

An object representing a color.

Borders

An object representing all of the borders.

Border

An object representing an individual border.

SolidFill

An object representing a solid fill.

PatternFill

An object representing a pattern fill.

GradientFill

An object representing a gradient fill.

API Reference

Classes

Cell

A cell

Column

A column.

Range

A range of cells.

Row

A row.

Sheet

A worksheet.

Workbook

A workbook.

Objects

XlsxPopulate : object

Cell

A cell

Kind: global class

• Cell
  • instance
    • .address([opts]) ⇒ string
    • .column() ⇒ Column
    • .clear() ⇒ Cell
    • .columnName() ⇒ number
    • .columnNumber() ⇒ number
    • .find(pattern, [replacement]) ⇒ boolean
    • .formula() ⇒ string
    • .formula(formula) ⇒ Cell
    • .tap(callback) ⇒ Cell
    • .thru(callback) ⇒ *
    • .rangeTo(cell) ⇒ Range
    • .relativeCell(rowOffset, columnOffset) ⇒ Cell
    • .row() ⇒ Row
    • .rowNumber() ⇒ number
    • .sheet() ⇒ Sheet
    • .style(name) ⇒ *
    • .style(names) ⇒ object.<string, *>
    • .style(name, value) ⇒ Cell
    • .style(styles) ⇒ Cell
    • .value() ⇒ string | boolean | number | Date | undefined
    • .value(value) ⇒ Cell
    • .workbook() ⇒ XlsxPopulate
  • inner
    • ~tapCallback ⇒ undefined
    • ~thruCallback ⇒ *

cell.address([opts]) ⇒ string

Get the address of the column.

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

cell.column() ⇒ Column

Gets the parent column of the cell.

Kind: instance method of Cell
Returns: Column - The parent column.

cell.clear() ⇒ Cell

Clears the contents from the cell.

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

cell.columnName() ⇒ number

Gets the column name of the cell.

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

cell.columnNumber() ⇒ number

Gets the column number of the cell (1-based).

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

cell.find(pattern, [replacement]) ⇒ boolean

Find the given pattern in the cell and optionally replace it.

Kind: instance method of Cell
Returns: boolean - A flag indicating if the pattern was found.

cell.formula() ⇒ string

Gets the formula in the cell. Note that if a formula was set as part of a range, the getter will return 'SHARED'. This is a limitation that may be addressed in a future release.

Kind: instance method of Cell
Returns: string - - The formula in the cell.

cell.formula(formula) ⇒ Cell

Sets the formula in the cell.

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

cell.tap(callback) ⇒ Cell

Invoke a callback on the cell and return the cell. Useful for method chaining.

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

cell.thru(callback) ⇒ *

Invoke a callback on the cell and return the value provided by the callback. Useful for method chaining.

Kind: instance method of Cell
Returns: * - The return value of the callback.

cell.rangeTo(cell) ⇒ Range

Create a range from this cell and another.

Kind: instance method of Cell
Returns: Range - The range.

cell.relativeCell(rowOffset, columnOffset) ⇒ Cell

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

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

cell.row() ⇒ Row

Gets the parent row of the cell.

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

cell.rowNumber() ⇒ number

Gets the row number of the cell (1-based).

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

cell.sheet() ⇒ Sheet

Gets the parent sheet.

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

cell.style(name) ⇒ *

Gets an individual style.

Kind: instance method of Cell
Returns: * - The style.

cell.style(names) ⇒ object.<string, *>

Gets multiple styles.

Kind: instance method of Cell
Returns: object.<string, *> - Object whose keys are the style names and values are the styles.

cell.style(name, value) ⇒ Cell

Sets an individual style.

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

cell.style(styles) ⇒ Cell

Sets multiple styles.

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

cell.value() ⇒ string | boolean | number | Date | undefined

Gets the value of the cell.

Kind: instance method of Cell
Returns: string | boolean | number | Date | undefined - The value of the cell.

cell.value(value) ⇒ Cell

Sets the value of the cell.

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

cell.workbook() ⇒ XlsxPopulate

Gets the parent workbook.

Kind: instance method of Cell
Returns: XlsxPopulate - The parent workbook.

Cell~tapCallback ⇒ undefined

Callback used by tap.

Kind: inner typedef of Cell

Cell~thruCallback ⇒ *

Callback used by thru.

Kind: inner typedef of Cell
Returns: * - The value to return from thru.

Column

A column.

Kind: global class

• Column
  • .address([opts]) ⇒ string
  • .cell(rowNumber) ⇒ Cell
  • .columnName() ⇒ string
  • .columnNumber() ⇒ number
  • .hidden() ⇒ boolean
  • .hidden(hidden) ⇒ Column
  • .sheet() ⇒ Sheet
  • .width() ⇒ undefined | number
  • .width(width) ⇒ Column
  • .workbook() ⇒ XlsxPopulate

column.address([opts]) ⇒ string

Get the address of the column.

Kind: instance method of Column
Returns: string - The address

column.cell(rowNumber) ⇒ Cell

Get a cell within the column.

Kind: instance method of Column
Returns: Cell - The cell in the column with the given row number.

column.columnName() ⇒ string

Get the name of the column.

Kind: instance method of Column
Returns: string - The column name.

column.columnNumber() ⇒ number

Get the number of the column.

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

column.hidden() ⇒ boolean

Gets a value indicating whether the column is hidden.

Kind: instance method of Column
Returns: boolean - A flag indicating whether the column is hidden.

column.hidden(hidden) ⇒ Column

Sets whether the column is hidden.

Kind: instance method of Column
Returns: Column - The column.

column.sheet() ⇒ Sheet

Get the parent sheet.

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

column.width() ⇒ undefined | number

Gets the width.

Kind: instance method of Column
Returns: undefined | number - The width (or undefined).

column.width(width) ⇒ Column

Sets the width.

Kind: instance method of Column
Returns: Column - The column.

column.workbook() ⇒ XlsxPopulate

Get the parent workbook.

Kind: instance method of Column
Returns: XlsxPopulate - The parent workbook.

Range

A range of cells.

Kind: global class

• Range
  • instance
    • .address([opts]) ⇒ string
    • .cell(ri, ci) ⇒ Cell
    • .clear() ⇒ Range
    • .endCell() ⇒ Cell
    • .forEach(callback) ⇒ Range
    • .formula() ⇒ string | undefined
    • .formula(formula) ⇒ Range
    • .map(callback) ⇒ Array.<Array.<*>>
    • .merged() ⇒ boolean
    • .merged(merged) ⇒ Range
    • .reduce(callback, [initialValue]) ⇒ *
    • .sheet() ⇒ Sheet
    • .startCell() ⇒ Cell
    • .style(name) ⇒ Array.<Array.<*>>
    • .style(names) ⇒ Object.<string, Array.<Array.<*>>>
    • .style(name) ⇒ Range
    • .style(name) ⇒ Range
    • .style(name, value) ⇒ Range
    • .style(styles) ⇒ Range
    • .tap(callback) ⇒ Range
    • .thru(callback) ⇒ *
    • .value() ⇒ Array.<Array.<*>>
    • .value() ⇒ Range
    • .value() ⇒ Range
    • .value(value) ⇒ Range
    • .workbook() ⇒ XlsxPopulate
  • inner
    • ~forEachCallback ⇒ undefined
    • ~mapCallback ⇒ *
    • ~reduceCallback ⇒ *
    • ~tapCallback ⇒ undefined
    • ~thruCallback ⇒ *

range.address([opts]) ⇒ string

Get the address of the range.

Kind: instance method of Range
Returns: string - The address.

range.cell(ri, ci) ⇒ Cell

Gets a cell within the range.

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

range.clear() ⇒ Range

Clear the contents of all the cells in the range.

Kind: instance method of Range
Returns: Range - The range.

range.endCell() ⇒ Cell

Get the end cell of the range.

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

range.forEach(callback) ⇒ Range

Call a function for each cell in the range. Goes by row then column.

Kind: instance method of Range
Returns: Range - The range.

range.formula() ⇒ string | undefined

Gets the shared formula in the start cell (assuming it's the source of the shared formula).

Kind: instance method of Range
Returns: string | undefined - The shared formula.

range.formula(formula) ⇒ Range

Sets the shared formula in the range. The formula will be translated for each cell.

Kind: instance method of Range
Returns: Range - The range.

range.map(callback) ⇒ Array.<Array.<*>>

Creates a 2D array of values by running each cell through a callback.

Kind: instance method of Range
Returns: Array.<Array.<*>> - The 2D array of return values.

range.merged() ⇒ boolean

Gets a value indicating whether the cells in the range are merged.

Kind: instance method of Range
Returns: boolean - The value.

range.merged(merged) ⇒ Range

Sets a value indicating whether the cells in the range should be merged.

Kind: instance method of Range
Returns: Range - The range.

range.reduce(callback, [initialValue]) ⇒ *

Reduces the range to a single value accumulated from the result of a function called for each cell.

Kind: instance method of Range
Returns: * - The accumulated value.

range.sheet() ⇒ Sheet

Gets the parent sheet of the range.

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

range.startCell() ⇒ Cell

Gets the start cell of the range.

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

range.style(name) ⇒ Array.<Array.<*>>

Gets a single style for each cell.

Kind: instance method of Range
Returns: Array.<Array.<*>> - 2D array of style values.

range.style(names) ⇒ Object.<string, Array.<Array.<*>>>

Gets multiple styles for each cell.

Kind: instance method of Range
Returns: Object.<string, Array.<Array.<*>>> - Object whose keys are style names and values are 2D arrays of style values.

range.style(name) ⇒ Range

Set the style in each cell to the result of a function called for each.

Kind: instance method of Range
Returns: Range - The range.

range.style(name) ⇒ Range

Sets the style in each cell to the corresponding value in the given 2D array of values.

Kind: instance method of Range
Returns: Range - The range.

range.style(name, value) ⇒ Range

Set the style of all cells in the range to a single style value.

Kind: instance method of Range
Returns: Range - The range.

range.style(styles) ⇒ Range

Set multiple styles for the cells in the range.

Kind: instance method of Range
Returns: Range - The range.

range.tap(callback) ⇒ Range

Invoke a callback on the range and return the range. Useful for method chaining.

Kind: instance method of Range
Returns: Range - The range.

range.thru(callback) ⇒ *

Invoke a callback on the range and return the value provided by the callback. Useful for method chaining.

Kind: instance method of Range
Returns: * - The return value of the callback.

range.value() ⇒ Array.<Array.<*>>

Get the values of each cell in the range as a 2D array.

Kind: instance method of Range
Returns: Array.<Array.<*>> - The values.

range.value() ⇒ Range

Set the values in each cell to the result of a function called for each.

Kind: instance method of Range
Returns: Range - The range.

range.value() ⇒ Range

Sets the value in each cell to the corresponding value in the given 2D array of values.

Kind: instance method of Range
Returns: Range - The range.

range.value(value) ⇒ Range

Set the value of all cells in the range to a single value.

Kind: instance method of Range
Returns: Range - The range.

range.workbook() ⇒ XlsxPopulate

Gets the parent workbook.

Kind: instance method of Range
Returns: XlsxPopulate - The parent workbook.

Range~forEachCallback ⇒ undefined

Callback used by forEach.

Kind: inner typedef of Range

Range~mapCallback ⇒ *

Callback used by map.

Kind: inner typedef of Range
Returns: * - The value to map to.

Range~reduceCallback ⇒ *

Callback used by reduce.

Kind: inner typedef of Range
Returns: * - The value to map to.

Range~tapCallback ⇒ undefined

Callback used by tap.

Kind: inner typedef of Range

Range~thruCallback ⇒ *

Callback used by thru.

Kind: inner typedef of Range
Returns: * - The value to return from thru.

Row

A row.

Kind: global class

• Row
  • .address([opts]) ⇒ string
  • .cell(columnNameOrNumber) ⇒ Cell
  • .height() ⇒ undefined | number
  • .height(height) ⇒ Row
  • .hidden() ⇒ boolean
  • .hidden(hidden) ⇒ Row
  • .rowNumber() ⇒ number
  • .sheet() ⇒ Sheet
  • .workbook() ⇒ XlsxPopulate

row.address([opts]) ⇒ string

Get the address of the row.

Kind: instance method of Row
Returns: string - The address

row.cell(columnNameOrNumber) ⇒ Cell

Get a cell in the row.

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

row.height() ⇒ undefined | number

Gets the row height.

Kind: instance method of Row
Returns: undefined | number - The height (or undefined).

row.height(height) ⇒ Row

Sets the row height.

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

row.hidden() ⇒ boolean

Gets a value indicating whether the row is hidden.

Kind: instance method of Row
Returns: boolean - A flag indicating whether the row is hidden.

row.hidden(hidden) ⇒ Row

Sets whether the row is hidden.

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

row.rowNumber() ⇒ number

Gets the row number.

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

row.sheet() ⇒ Sheet

Gets the parent sheet of the row.

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

row.workbook() ⇒ XlsxPopulate

Get the parent workbook.

Kind: instance method of Row
Returns: XlsxPopulate - The parent workbook.

Sheet

A worksheet.

Kind: global class

• Sheet
  • .cell(address) ⇒ Cell
  • .cell(rowNumber, columnNameOrNumber) ⇒ Cell
  • .column(columnNameOrNumber) ⇒ Column
  • .definedName(name) ⇒ undefined | Cell | Range | Row | Column
  • .find(pattern, [replacement]) ⇒ Array.<Cell>
  • .name() ⇒ string
  • .range(address) ⇒ Range
  • .range(startCell, endCell) ⇒ Range
  • .range(startRowNumber, startColumnNameOrNumber, endRowNumber, endColumnNameOrNumber) ⇒ Range
  • .row(rowNumber) ⇒ Row
  • .usedRange() ⇒ Range | undefined
  • .workbook() ⇒ XlsxPopulate

sheet.cell(address) ⇒ Cell

Gets the cell with the given address.

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

sheet.cell(rowNumber, columnNameOrNumber) ⇒ Cell

Gets the cell with the given row and column numbers.

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

sheet.column(columnNameOrNumber) ⇒ Column

Gets a column in the sheet.

Kind: instance method of Sheet
Returns: Column - The column.

sheet.definedName(name) ⇒ undefined | Cell | Range | Row | Column

Gets a defined name scoped to the sheet.

Kind: instance method of Sheet
Returns: undefined | Cell | Range | Row | Column - The named selection or undefined if name not found.
Throws:

• Error Will throw if address in defined name is not supported.

sheet.find(pattern, [replacement]) ⇒ Array.<Cell>

Find the given pattern in the sheet and optionally replace it.

Kind: instance method of Sheet
Returns: Array.<Cell> - The matching cells.

sheet.name() ⇒ string

Get the name of the sheet.

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

sheet.range(address) ⇒ Range

Gets a range from the given range address.

Kind: instance method of Sheet
Returns: Range - The range.

sheet.range(startCell, endCell) ⇒ Range

Gets a range from the given cells or cell addresses.

Kind: instance method of Sheet
Returns: Range - The range.

sheet.range(startRowNumber, startColumnNameOrNumber, endRowNumber, endColumnNameOrNumber) ⇒ Range

Gets a range from the given row numbers and column names or numbers.

Kind: instance method of Sheet
Returns: Range - The range.

sheet.row(rowNumber) ⇒ Row

Gets the row with the given number.

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

sheet.usedRange() ⇒ Range | undefined

Get the range of cells in the sheet that have contained a value or style at any point. Useful for extracting the entire sheet contents.

Kind: instance method of Sheet
Returns: Range | undefined - The used range or undefined if no cells in the sheet are used.

sheet.workbook() ⇒ XlsxPopulate

Gets the parent workbook.

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

Workbook

A workbook.

Kind: global class

• Workbook
  • .definedName(name) ⇒ undefined | Cell | Range | Row | Column
  • .find(pattern, [replacement]) ⇒ boolean
  • .outputAsync([type]) ⇒ string | Uint8Array | ArrayBuffer | Blob | Buffer
  • .sheet(sheetNameOrIndex) ⇒ Sheet | undefined
  • .toFileAsync(path) ⇒ Promise.<undefined>

workbook.definedName(name) ⇒ undefined | Cell | Range | Row | Column

Gets a defined name scoped to the workbook.

Kind: instance method of Workbook
Returns: undefined | Cell | Range | Row | Column - The named selection or undefined if name not found.
Throws:

• Error Will throw if address in defined name is not supported.

workbook.find(pattern, [replacement]) ⇒ boolean

Find the given pattern in the workbook and optionally replace it.

Kind: instance method of Workbook
Returns: boolean - A flag indicating if the pattern was found.

workbook.outputAsync([type]) ⇒ string | Uint8Array | ArrayBuffer | Blob | Buffer

Generates the workbook output.

Kind: instance method of Workbook
Returns: string | Uint8Array | ArrayBuffer | Blob | Buffer - The data.

workbook.sheet(sheetNameOrIndex) ⇒ Sheet | undefined

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

Kind: instance method of Workbook
Returns: Sheet | undefined - The sheet or undefined if not found.

workbook.toFileAsync(path) ⇒ Promise.<undefined>

Write the workbook to file. (Not supported in browsers.)

Kind: instance method of Workbook
Returns: Promise.<undefined> - A promise.

XlsxPopulate : object

Kind: global namespace

• XlsxPopulate : object
  • .MIME_TYPE : string
  • .dateToNumber(date) ⇒ number
  • .fromBlankAsync() ⇒ Promise.<Workbook>
  • .fromDataAsync(data) ⇒ Promise.<Workbook>
  • .fromFileAsync(path) ⇒ Promise.<Workbook>
  • .numberToDate(number) ⇒ Date

XlsxPopulate.MIME_TYPE : string

The XLSX mime type.

Kind: static property of XlsxPopulate

XlsxPopulate.dateToNumber(date) ⇒ number

Convert a date to a number for Excel.

Kind: static method of XlsxPopulate
Returns: number - The number.

XlsxPopulate.fromBlankAsync() ⇒ Promise.<Workbook>

Create a new blank workbook.

Kind: static method of XlsxPopulate
Returns: Promise.<Workbook> - The workbook.

XlsxPopulate.fromDataAsync(data) ⇒ Promise.<Workbook>

Loads a workbook from a data object. (Supports any supported JSZip data types.)

Kind: static method of XlsxPopulate
Returns: Promise.<Workbook> - The workbook.

XlsxPopulate.fromFileAsync(path) ⇒ Promise.<Workbook>

Loads a workbook from file.

Kind: static method of XlsxPopulate
Returns: Promise.<Workbook> - The workbook.

XlsxPopulate.numberToDate(number) ⇒ Date

Convert an Excel number to a date.

Kind: static method of XlsxPopulate
Returns: Date - The date.