xlsx-populate

    1.21.0 • Public • Published

    view on npm npm module downloads per month Build Status Dependency Status

    xlsx-populate

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

    Table of Contents

    Installation

    Node.js

    npm install xlsx-populate

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

    Browser

    A functional browser example can be found in examples/browser/index.html.

    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-env.

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

    Note: in cells that contain values calculated by formulas, Excel will store the calculated value in the workbook. The value method will return the value of the cells at the time the workbook was saved. xlsx-populate will not recalculate the values as you manipulate the workbook and will not write the values to the output.

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

    Alternatively, you can set the values in a range with only the top-left cell in the range:

    workbook.sheet(0).cell("A1").value([
        [1, 2, 3],
        [4, 5, 6],
        [7, 8, 9]
    ]);

    The set range is returned.

    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.

    Managing Sheets

    xlsx-populate supports a number of options for managing sheets.

    You can get a sheet by name or index or get all of the sheets as an array:

    // Get sheet by index
    const sheet1 = workbook.sheet(0);
     
    // Get sheet by name
    const sheet2 = workbook.sheet("Sheet2");
     
    // Get all sheets as an array
    const sheets = workbook.sheets();

    You can add new sheets:

    // Add a new sheet named 'New 1' at the end of the workbook
    const newSheet1 = workbook.addSheet('New 1');
     
    // Add a new sheet named 'New 2' at index 1 (0-based)
    const newSheet2 = workbook.addSheet('New 2', 1);
     
    // Add a new sheet named 'New 3' before the sheet named 'Sheet1'
    const newSheet3 = workbook.addSheet('New 3', 'Sheet1');
     
    // Add a new sheet named 'New 4' before the sheet named 'Sheet1' using a Sheet reference.
    const sheet = workbook.sheet('Sheet1');
    const newSheet4 = workbook.addSheet('New 4', sheet);

    Note: the sheet rename method does not rename references to the sheet so formulas, etc. can be broken. Use with caution!

    You can rename sheets:

    // Rename the first sheet.
    const sheet = workbook.sheet(0).name("new sheet name");

    You can move sheets:

    // Move 'Sheet1' to the end
    workbook.moveSheet("Sheet1");
     
    // Move 'Sheet1' to index 2
    workbook.moveSheet("Sheet1", 2);
     
    // Move 'Sheet1' before 'Sheet2'
    workbook.moveSheet("Sheet1", "Sheet2");

    The above methods can all use sheet references instead of names as well. And you can also move a sheet using a method on the sheet:

    // Move the sheet before 'Sheet2'
    sheet.move("Sheet2");

    You can delete sheets:

    // Delete 'Sheet1'
    workbook.deleteSheet("Sheet1");
     
    // Delete sheet with index 2
    workbook.deleteSheet(2);
     
    // Delete from sheet reference
    workbook.sheet(0).delete();

    You can get/set the active sheet:

    // Get the active sheet
    const sheet = workbook.activeSheet();
     
    // Check if the current sheet is active
    sheet.active() // returns true or false
     
    // Activate the sheet
    sheet.active(true);
     
    // Or from the workbook
    workbook.activeSheet("Sheet2");

    Defined Names

    Excel supports creating defined names that refer to addresses, formulas, or constants. These defined names can be scoped to the entire workbook or just individual sheets. xlsx-populate supports looking up defined names that refer to cells or ranges. (Dereferencing other names will result in an error.) Defined names are particularly useful if you are populating data into a known template. Then you do not need to know the exact location.

    // Look up workbook-scoped name and set the value to 5.
    workbook.definedName("some name").value(5);
     
    // Look of a name scoped to the first sheet and set the value to "foo".
    workbook.sheet(0).definedName("some other name").value("foo");

    You can also create, modify, or delete defined names:

    // Create/modify a workbook-scope defined name
    workbook.definedName("some name", "TRUE");
     
    // Delete a sheet-scoped defined name:
    workbook.sheet(0).definedName("some name", null);

    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 get/set a cell style:

    // Get a single style
    const bold = cell.style("bold"); // true
     
    // Get multiple styles
    const styles = cell.style(["bold", "italic"]); // { bold: true, italic: true }
     
    // Set a single style
    cell.style("bold", true);
     
    // Set multiple styles
    cell.style({ 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
    });

    If you are setting styles for many cells, performance is far better if you set for an entire row or column:

    // Set a single style
    sheet.row(1).style("bold", true);
     
    // Set multiple styles
    sheet.column("A").style({ bold: true, italic: true });
     
    // Get a single style
    const bold = sheet.column(3).style("bold");
     
    // Get multiple styles
    const styles = sheet.row(5).style(["bold", "italic"]);

    Note that the row/column style behavior mirrors Excel. Setting a style on a column will apply that style to all existing cells and any new cells that are populated. Getting the row/column style will return only the styles that have been applied to the entire row/column, not the styles of every cell in the row or column.

    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.

    Rich Texts

    You can read/write rich texts to cells.

    Supported styles

    bold, italic, underline, strikethrough, subscript, fontSize, fontFamily, fontGenericFamily, fontScheme, fontColor. See the Style Reference for the various options.

    Usage

    You can read and modify rich texts on an existing rich text cell:

    // assume A1 is a rich text cell
    const RichText = require('xlsx-Populate').RichText;
    const cell = workbook.sheet(0).cell('A1');
    cell.value() instanceof RichText // returns true
    const richtext = cell.value();
    // get the concatenate text
    richtext.text();
     
    // loop through each rich text fragment
    for (let i = 0; i < richtext.length; i++) {
        const fragment = richtext.get(i);
        // Get the style
        fragment.style('bold');
        // Get many styles
        fragment.style(['bold', 'italic']);
        // Set one style
        fragment.style('bold', true);
        // Set many styles
        fragment.style({ 'bold': true, 'italic': true });
        // Get the value
        fragment.value();
        // Set the value
        fragment.value('hello');
    }
     
    // remove the first rich text fragment
    richtext.remove(0);
     
    // clear this rich texts
    richtext.clear();

    How to set a cell to rich texts:

    const RichText = require('xlsx-Populate').RichText;
    const cell = workbook.sheet(0).cell('A1');
    // set a cell value to rich text
    cell.value(new RichText());
     
    // add two rich text fragments
    cell.value()
        .add('hello ', { italic: true, bold: true })
        .add('world!', { fontColor: 'FF0000' });

    You can specify the index when adding rich text fragment.

    // add before the first fragment
    cell.value().add('text', { bold: true }, 0);
    // add before the second fragment
    cell.value().add('text', { bold: true }, 1);
    // add after the last fragment
    cell.value().add('text', { bold: true });

    Notes

    We make a deep copy of the richtext instance when assign it to a cell, which means you can only modify the content of the richtext before calling cell.value(richtext). Any modification to the richtext instance after calling cell.value(richtext) will not save to the cell. i.e.

    const richtext = new RichText();
    richtext.add('hello');
    cell.value(richtext);
    cell.value().text(); // returns 'hello'
     
    richtext.add(' world')
    richtext.text(); // returns 'hello world' 
    cell.value().text(); // returns 'hello'
    cell.value() === richtext; // returns false
     
    cell.value().add(' world');
    cell.value().text(); // returns 'hello world'

    This means you can create a rich text instance and assign it to any cells! Each cell does not share the same instance but creates a deep copy of the instance.

    const sheet = workbook.sheet(0);
    const richtext = new RichText();
    richtext.add('hello');
    const range = sheet.range("A1:C3");
    range.value(richtext);
    // they do not share the same instance
    sheet.cell('A1').value() === sheet.cell('C1').value() // returns false

    You can get the rich text from a cell and set it to anoher cell.

    const richtext = cell1.value();
    cell2.value(richtext);
    cell1.value() === cell2.value() // returns false

    Whenever you call richtext.add(text, styles, index), we will detect if the given text contains line separators (\n, \r, \r\n), if it does, we will call cell.style('wrapText', true) for you. MS Excel needs wrapText to be true to have the new lines displayed, otherwise you will see the texts in one line. You may also need to set row height to have all lines displayed.

    cell.value()
        // it support all line separators
        .add('123\n456\r789\r\n10', { italic: true, fontColor: '123456' })
    // remember to set height to show the whole row
    workbook.sheet(0).row(1).height(100);

    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)

    Data Validation

    Data validation is also supported. To set/get/remove a cell data validation:

    // Set the data validation
    cell.dataValidation({
        type: 'list',
        allowBlank: false,
        showInputMessage: false,
        prompt: false,
        promptTitle: 'String',
        showErrorMessage: false,
        error: 'String',
        errorTitle: 'String',
        operator: 'String',
        formula1: '$A:$A',//Required
        formula2: 'String'
    });
     
    //Here is a short version of the one above.
    cell.dataValidation('$A:$A');
     
    // Get the data validation
    const obj = cell.dataValidation(); // Returns an object
     
    // Remove the data validation
    cell.dataValidation(null); //Returns the cell

    Similarly for ranges:

     
    // Set all cells in range with a single shared data validation
    range.dataValidation({
        type: 'list',
        allowBlank: false,
        showInputMessage: false,
        prompt: false,
        promptTitle: 'String',
        showErrorMessage: false,
        error: 'String',
        errorTitle: 'String',
        operator: 'String',
        formula1: 'Item1,Item2,Item3,Item4',//Required
        formula2: 'String'
    });
     
    //Here is a short version of the one above.
    range.dataValidation('Item1,Item2,Item3,Item4');
     
    // Get the data validation
    const obj = range.dataValidation(); // Returns an object
     
    // Remove the data validation
    range.dataValidation(null); //Returns the Range

    Please note, the data validation gets applied to the entire range, not each Cell in the range.

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

    Hyperlinks

    Hyperlinks are also supported on cells using the Cell.hyperlink method. The method will not style the content to look like a hyperlink. You must do that yourself:

    // Set a hyperlink
    cell.value("Link Text")
        .style({ fontColor: "0563c1", underline: true })
        .hyperlink("http://example.com");
     
    // Set a hyperlink with tooltip
    cell.value("Link Text")
        .style({ fontColor: "0563c1", underline: true })
        .hyperlink({ hyperlink: "http://example.com", tooltip: "example.com" });
     
    // Get the hyperlink
    const value = cell.hyperlink(); // Returns 'http://example.com'
     
    // Set a hyperlink to email
    cell.value("Click to Email Jeff Bezos")
        .hyperlink({ email: "jeff@amazon.com", emailSubject: "I know you're a busy man Jeff, but..." });
     
    // Set a hyperlink to an internal cell using an address string.
    cell.value("Click to go to an internal cell")
        .hyperlink("Sheet2!A1");
     
    // Set a hyperlink to an internal cell using a cell object.
    cell.value("Click to go to an internal cell")
        .hyperlink(workbook.sheet(0).cell("A1"));

    Print Options

    Print options are accessed using the Sheet.printOptions method. Defaults are all assumed to be false, so if the attribute is missing, then the method returns false. A method Sheet.printGridLines is provided to offer the convenience of setting both gridLines and gridLinesSet.

    // Print row and column headings
    sheet.printOptions('headings', true);
     
    // Get the headings flag
    const headings = sheet.printOptions('headings'); // Returns true
     
    // Clear flag for center on page vertically when printing
    sheet.printOptions('verticalCentered', undefined);
     
    // Get the verticalCentered flag
    const verticalCentered = sheet.printOptions('verticalCentered'); // Returns false
     
    // Enable grid lines in print
    sheet.printGridLines(true);
     
    // Now both gridLines and gridLinesSet print options are set
    sheet.printOptions('gridLines') === sheet.printOptions('gridLinesSet') === true; // Returns true
     
    // To disable, just disable one of gridLines or gridLinesSet
    sheet.printOptions('gridLineSets', false);
     
    const isPrintGridLinesEnabled = sheet.printGridLines(); // Returns false

    Page Margins

    Excel requires that all page margins are defined or none at all. To ensure this, please choose an existing or custom preset. See Sheet.pageMarginsPreset.

    // Get the current preset
    sheet.pageMarginsPreset(); // Returns undefined
     
    // Switch to an existing preset
    sheet.pageMarginsPreset('normal');

    Page margins are accessed using the Sheet.pageMargins method. If a page margin is not set, the preset will fill in the gaps.

    // Get top margin in inches, note that the current preset is currently set to normal (see above)
    sheet.pageMargins('top'); // Returns 0.75
     
    // Set top page margin in inches
    sheet.pageMargins('top', 1.1);
     
    // Get top page margin in inches.
    const topPageMarginInInches = sheet.pageMargins('top'); // Returns 1.1

    SheetView Panes

    SheetView Panes are accessed using the Sheet.panes method. For convenience, we have Sheet.freezePanes, Sheet.splitPanes, Sheet.resetPanes, and type PaneOptions.

    // access Pane options
    sheet.panes(); // return PaneOptions Object
     
    // manually Set Pane options, WARNING: setting wrong options may result in excel fails to open.
    const paneOptions = { state: 'frozen', topLeftCell: 'B2', xSplit: 1, ySplit: 1, activePane: 'bottomRight' }
    sheet.panes(paneOptions); // return PaneOptions Object
     
    // freeze panes (freeze first column and first two rows)
    sheet.freezePanes(1, 2);
    // OR
    sheet.freezePanes('B3');
     
    // split panes (Horizontal Split Position: 1000 / 20 pt, Vertical Split Position: 2000 / 20 pt)
    sheet.splitPanes(1000, 2000);
     
    // reset to normal panes (no freeze panes and split panes)
    sheet.resetPanes();

    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:

    workbook.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:

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

    Promises

    xlsx-populate uses promises to manage async input/output. By default it uses the Promise defined in the browser or Node.js. In browsers that don't support promises (IE) a polyfill is used via JSZip.

    // Get the current promise library in use.
    // Helpful for getting a usable Promise library in IE.
    var Promise = XlsxPopulate.Promise;

    If you prefer, you can override the default Promise library used with another ES6 compliant library like bluebird.

    const Promise = require("bluebird");
    const XlsxPopulate = require("xlsx-populate");
    XlsxPopulate.Promise = Promise;

    Encryption

    XLSX Agile encryption and descryption are supported so you can read and write password-protected workbooks. To read a protected workbook, pass the password in as an option:

    XlsxPopulate.fromFileAsync("./Book1.xlsx", { password: "S3cret!" })
        .then(workbook => {
            // ...
        });

    Similarly, to write a password encrypted workbook:

    workbook.toFileAsync("./out.xlsx", { password: "S3cret!" });

    The password option is supported in all output methods. N.B. Workbooks will only be encrypted if you supply a password when outputting even if they had a password when reading.

    Encryption support is also available in the browser, but take care! Any password you put in browser code can be read by anyone with access to your code. You should only use passwords that are supplied by the end-user. Also, the performance of encryption/decryption in the browser is far worse than with Node.js. IE, in particular, is extremely slow. xlsx-populate is bundled for browsers with and without encryption support as the encryption libraries increase the size of the bundle a lot.

    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!

    Submitting an Issue

    If you happen to run into a bug or an issue, please feel free to submit an issue. I only ask that you please include sample JavaScript code that demonstrates the issue. If the problem lies with modifying some template, it is incredibly difficult to debug the issue without the template. So please attach the template if possible. If you have confidentiality concerns, please attach a different workbook that exhibits the issue or you can send your workbook directly to dtjohnson after creating the issue.

    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.
    • unit-browser - Run the unit tests in real browsers using Karma.
    • e2e-parse - End-to-end tests of parsing data out of sample workbooks that were created in Microsoft Excel.
    • e2e-generate - End-to-end tests of generating workbooks using xlsx-populate. To verify the workbooks were truly generated correctly they need to be opened in Microsoft Excel and verified. This task automates this verification using the .NET Excel Interop library with Edge.js acting as a bridge between Node.js and C#. Note that these tests will only run on Windows with Microsoft Excel and the Primary Interop Assemblies installed.
    • e2e-browser - End-to-end tests of usage of the browserify bundle 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

    Style Name Type Description
    bold boolean true for bold, false for not bold
    italic boolean true for italic, false for not italic
    underline boolean|string true for single underline, false for no underline, 'double' for double-underline
    strikethrough boolean true for strikethrough false for not strikethrough
    subscript boolean true for subscript, false for not subscript (cannot be combined with superscript)
    superscript boolean true for superscript, false for not superscript (cannot be combined with subscript)
    fontSize number Font size in points. Must be greater than 0.
    fontFamily string Name of font family.
    fontGenericFamily number 1: Serif, 2: Sans Serif, 3: Monospace,
    fontScheme string 'minor'|'major'|'none'
    fontColor Color|string|number Color of the font. If string, will set an RGB color. If number, will set a theme color.
    horizontalAlignment string Horizontal alignment. Allowed values: 'left', 'center', 'right', 'fill', 'justify', 'centerContinuous', 'distributed'
    justifyLastLine boolean a.k.a Justified Distributed. Only applies when horizontalAlignment === 'distributed'. A boolean value indicating if the cells justified or distributed alignment should be used on the last line of text. (This is typical for East Asian alignments but not typical in other contexts.)
    indent number Number of indents. Must be greater than or equal to 0.
    verticalAlignment string Vertical alignment. Allowed values: 'top', 'center', 'bottom', 'justify', 'distributed'
    wrapText boolean true to wrap the text in the cell, false to not wrap.
    shrinkToFit boolean true to shrink the text in the cell to fit, false to not shrink.
    textDirection string Direction of the text. Allowed values: 'left-to-right', 'right-to-left'
    textRotation number Counter-clockwise angle of rotation in degrees. Must be [-90, 90] where negative numbers indicate clockwise rotation.
    angleTextCounterclockwise boolean Shortcut for textRotation of 45 degrees.
    angleTextClockwise boolean Shortcut for textRotation of -45 degrees.
    rotateTextUp boolean Shortcut for textRotation of 90 degrees.
    rotateTextDown boolean Shortcut for textRotation of -90 degrees.
    verticalText boolean Special rotation that shows text vertical but individual letters are oriented normally. true to rotate, false to not rotate.
    fill SolidFill|PatternFill|GradientFill|Color|string|number The cell fill. If Color, will set a solid fill with the color. If string, will set a solid RGB fill. If number, will set a solid theme color fill.
    border Borders|Border|string|boolean The border settings. If string, will set outside borders to given border style. If true, will set outside border style to 'thin'.
    borderColor Color|string|number Color of the borders. If string, will set an RGB color. If number, will set a theme color.
    borderStyle string Style of the outside borders. Allowed values: 'hair', 'dotted', 'dashDotDot', 'dashed', 'mediumDashDotDot', 'thin', 'slantDashDot', 'mediumDashDot', 'mediumDashed', 'medium', 'thick', 'double'
    leftBorder, rightBorder, topBorder, bottomBorder, diagonalBorder Border|string|boolean The border settings for the given side. If string, will set border to the given border style. If true, will set border style to 'thin'.
    leftBorderColor, rightBorderColor, topBorderColor, bottomBorderColor, diagonalBorderColor Color|string|number Color of the given border. If string, will set an RGB color. If number, will set a theme color.
    leftBorderStyle, rightBorderStyle, topBorderStyle, bottomBorderStyle, diagonalBorderStyle string Style of the given side.
    diagonalBorderDirection string Direction of the diagonal border(s) from left to right. Allowed values: 'up', 'down', 'both'
    numberFormat string Number format code. See docs here.

    Color

    An object representing a color.

    Property Type Description
    [rgb] string RGB color code (e.g. 'ff0000'). Either rgb or theme is required.
    [theme] number Index of a theme color. Either rgb or theme is required.
    [tint] number Optional tint value of the color from -1 to 1. Particularly useful for theme colors. 0.0 means no tint, -1.0 means 100% darken, and 1.0 means 100% lighten.

    Borders

    An object representing all of the borders.

    Property Type Description
    [left] Border|string|boolean The border settings for the left side. If string, will set border to the given border style. If true, will set border style to 'thin'.
    [right] Border|string|boolean The border settings for the right side. If string, will set border to the given border style. If true, will set border style to 'thin'.
    [top] Border|string|boolean The border settings for the top side. If string, will set border to the given border style. If true, will set border style to 'thin'.
    [bottom] Border|string|boolean The border settings for the bottom side. If string, will set border to the given border style. If true, will set border style to 'thin'.
    [diagonal] Border|string|boolean The border settings for the diagonal side. If string, will set border to the given border style. If true, will set border style to 'thin'.

    Border

    An object representing an individual border.

    Property Type Description
    style string Style of the given border.
    color Color|string|number Color of the given border. If string, will set an RGB color. If number, will set a theme color.
    [direction] string For diagonal border, the direction of the border(s) from left to right. Allowed values: 'up', 'down', 'both'

    SolidFill

    An object representing a solid fill.

    Property Type Description
    type 'solid'
    color Color|string|number Color of the fill. If string, will set an RGB color. If number, will set a theme color.

    PatternFill

    An object representing a pattern fill.

    Property Type Description
    type 'pattern'
    pattern string Name of the pattern. Allowed values: 'gray125', 'darkGray', 'mediumGray', 'lightGray', 'gray0625', 'darkHorizontal', 'darkVertical', 'darkDown', 'darkUp', 'darkGrid', 'darkTrellis', 'lightHorizontal', 'lightVertical', 'lightDown', 'lightUp', 'lightGrid', 'lightTrellis'.
    foreground Color|string|number Color of the foreground. If string, will set an RGB color. If number, will set a theme color.
    background Color|string|number Color of the background. If string, will set an RGB color. If number, will set a theme color.

    GradientFill

    An object representing a gradient fill.

    Property Type Description
    type 'gradient'
    [gradientType] string Type of gradient. Allowed values: 'linear' (default), 'path'. With a path gradient, a path is drawn between the top, left, right, and bottom values and a graident is draw from that path to the outside of the cell.
    stops Array.<{}>
    stops[].position number The position of the stop from 0 to 1.
    stops[].color Color|string|number Color of the stop. If string, will set an RGB color. If number, will set a theme color.
    [angle] number If linear gradient, the angle of clockwise rotation of the gradient.
    [left] number If path gradient, the left position of the path as a percentage from 0 to 1.
    [right] number If path gradient, the right position of the path as a percentage from 0 to 1.
    [top] number If path gradient, the top position of the path as a percentage from 0 to 1.
    [bottom] number If path gradient, the bottom position of the path as a percentage from 0 to 1.

    API Reference

    Classes

    Cell

    A cell

    Column

    A column.

    FormulaError

    A formula error (e.g. #DIV/0!).

    PageBreaks

    PageBreaks

    Range

    A range of cells.

    RichText

    A RichText class that contains many RichTextFragment.

    RichTextFragment

    A Rich text fragment.

    Row

    A row.

    Sheet

    A worksheet.

    Workbook

    A workbook.

    Objects

    XlsxPopulate : object

    Constants

    _

    OOXML uses the CFB file format with Agile Encryption. The details of the encryption are here: https://msdn.microsoft.com/en-us/library/dd950165(v=office.12).aspx

    Helpful guidance also take from this Github project: https://github.com/nolze/ms-offcrypto-tool

    Typedefs

    PaneOptions : Object

    https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.pane?view=openxml-2.8.1

    Cell

    A cell

    Kind: global class

    cell.active() ⇒ boolean

    Gets a value indicating whether the cell is the active cell in the sheet.

    Kind: instance method of Cell
    Returns: boolean - True if active, false otherwise.

    cell.active(active) ⇒ Cell

    Make the cell the active cell in the sheet.

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

    Param Type Description
    active boolean Must be set to true. Deactivating directly is not supported. To deactivate, you should activate a different cell instead.

    cell.address([opts]) ⇒ string

    Get the address of the column.

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

    Param Type Description
    [opts] Object Options
    [opts.includeSheetName] boolean Include the sheet name in the address.
    [opts.rowAnchored] boolean Anchor the row.
    [opts.columnAnchored] boolean Anchor the column.
    [opts.anchored] boolean Anchor both the row and the column.

    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() ⇒ string

    Gets the column name of the cell.

    Kind: instance method of Cell
    Returns: string - 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.

    Param Type Description
    pattern string | RegExp The pattern to look for. Providing a string will result in a case-insensitive substring search. Use a RegExp for more sophisticated searches.
    [replacement] string | function The text to replace or a String.replace callback function. If pattern is a string, all occurrences of the pattern in the cell will be replaced.

    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.

    Param Type Description
    formula string The formula to set.

    cell.hyperlink() ⇒ string | undefined

    Gets the hyperlink attached to the cell.

    Kind: instance method of Cell
    Returns: string | undefined - The hyperlink or undefined if not set.

    cell.hyperlink(hyperlink) ⇒ Cell

    Set or clear the hyperlink on the cell.

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

    Param Type Description
    hyperlink string | Cell | undefined The hyperlink to set or undefined to clear.

    cell.hyperlink(opts) ⇒ Cell

    Set the hyperlink options on the cell.

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

    Param Type Description
    opts Object | Cell Options or Cell. If opts is a Cell then an internal hyperlink is added.
    [opts.hyperlink] string | Cell The hyperlink to set, can be a Cell or an internal/external string.
    [opts.tooltip] string Additional text to help the user understand more about the hyperlink.
    [opts.email] string Email address, ignored if opts.hyperlink is set.
    [opts.emailSubject] string Email subject, ignored if opts.hyperlink is set.

    cell.dataValidation() ⇒ object | undefined

    Gets the data validation object attached to the cell.

    Kind: instance method of Cell
    Returns: object | undefined - The data validation or undefined if not set.

    cell.dataValidation(dataValidation) ⇒ Cell

    Set or clear the data validation object of the cell.

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

    Param Type Description
    dataValidation object | undefined Object or null to clear.

    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.

    Param Type Description
    callback tapCallback The callback function.

    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.

    Param Type Description
    callback thruCallback The callback function.

    cell.rangeTo(cell) ⇒ Range

    Create a range from this cell and another.

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

    Param Type Description
    cell Cell | string The other cell or cell address to range to.

    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.

    Param Type Description
    rowOffset number The row offset (0 for the current row).
    columnOffset number The column offset (0 for the current column).

    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.

    Param Type Description
    name string The name of 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.

    Param Type Description
    names Array.<string> The names of the style.

    cell.style(name, value) ⇒ Cell

    Sets an individual style.

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

    Param Type Description
    name string The name of the style.
    value * The value to set.

    cell.style(name) ⇒ Range

    Sets the styles in the range starting with the cell.

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

    Param Type Description
    name string The name of the style.
    Array.<Array.<*>> 2D array of values to set.

    cell.style(styles) ⇒ Cell

    Sets multiple styles.

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

    Param Type Description
    styles object.<string, *> Object whose keys are the style names and values are the styles to set.

    cell.style(style) ⇒ Cell

    Sets to a specific style

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

    Param Type Description
    style Style Style object given from stylesheet.createStyle

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

    Gets the value of the cell.

    Kind: instance method of Cell
    Returns: string | boolean | number | Date | RichText | 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.

    Param Type Description
    value string | boolean | number | null | undefined | RichText The value to set.

    cell.value() ⇒ Range

    Sets the values in the range starting with the cell.

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

    Param Type Description
    Array.<Array.<(string|boolean|number|null|undefined)>> 2D array of values to set.

    cell.workbook() ⇒ Workbook

    Gets the parent workbook.

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

    cell.addHorizontalPageBreak() ⇒ Cell

    Append horizontal page break after the cell.

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

    Cell~tapCallback ⇒ undefined

    Callback used by tap.

    Kind: inner typedef of Cell

    Param Type Description
    cell Cell The cell

    Cell~thruCallback ⇒ *

    Callback used by thru.

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

    Param Type Description
    cell Cell The cell

    Column

    A column.

    Kind: global class

    column.address([opts]) ⇒ string

    Get the address of the column.

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

    Param Type Description
    [opts] Object Options
    [opts.includeSheetName] boolean Include the sheet name in the address.
    [opts.anchored] boolean Anchor 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.

    Param Type Description
    rowNumber number The 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.

    Param Type Description
    hidden boolean A flag indicating whether to hide the column.

    column.sheet() ⇒ Sheet

    Get the parent sheet.

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

    column.style(name) ⇒ *

    Gets an individual style.

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

    Param Type Description
    name string The name of the style.

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

    Gets multiple styles.

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

    Param Type Description
    names Array.<string> The names of the style.

    column.style(name, value) ⇒ Cell

    Sets an individual style.

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

    Param Type Description
    name string The name of the style.
    value * The value to set.

    column.style(styles) ⇒ Cell

    Sets multiple styles.

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

    Param Type Description
    styles object.<string, *> Object whose keys are the style names and values are the styles to set.

    column.style(style) ⇒ Cell

    Sets to a specific style

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

    Param Type Description
    style Style Style object given from stylesheet.createStyle

    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.

    Param Type Description
    width number The width of the column.

    column.workbook() ⇒ Workbook

    Get the parent workbook.

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

    column.addPageBreak() ⇒ Column

    Append vertical page break after the column.

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

    FormulaError

    A formula error (e.g. #DIV/0!).

    Kind: global class

    formulaError.error() ⇒ string

    Get the error code.

    Kind: instance method of FormulaError
    Returns: string - The error code.

    FormulaError.DIV0 : FormulaError

    #DIV/0! error.

    Kind: static property of FormulaError

    FormulaError.NA : FormulaError

    #N/A error.

    Kind: static property of FormulaError

    FormulaError.NAME : FormulaError

    #NAME? error.

    Kind: static property of FormulaError

    FormulaError.NULL : FormulaError

    #NULL! error.

    Kind: static property of FormulaError

    FormulaError.NUM : FormulaError

    #NUM! error.

    Kind: static property of FormulaError

    FormulaError.REF : FormulaError

    #REF! error.

    Kind: static property of FormulaError

    FormulaError.VALUE : FormulaError

    #VALUE! error.

    Kind: static property of FormulaError

    PageBreaks

    PageBreaks

    Kind: global class

    pageBreaks.count ⇒ number

    get count of the page-breaks

    Kind: instance property of PageBreaks
    Returns: number - the page-breaks' count

    pageBreaks.list ⇒ Array

    get list of page-breaks

    Kind: instance property of PageBreaks
    Returns: Array - list of the page-breaks

    pageBreaks.add(id) ⇒ PageBreaks

    add page-breaks by row/column id

    Kind: instance method of PageBreaks
    Returns: PageBreaks - the page-breaks

    Param Type Description
    id number row/column id (rowNumber/colNumber)

    pageBreaks.remove(index) ⇒ PageBreaks

    remove page-breaks by index

    Kind: instance method of PageBreaks
    Returns: PageBreaks - the page-breaks

    Param Type Description
    index number index of list

    Range

    A range of cells.

    Kind: global class

    range.address([opts]) ⇒ string

    Get the address of the range.

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

    Param Type Description
    [opts] Object Options
    [opts.includeSheetName] boolean Include the sheet name in the address.
    [opts.startRowAnchored] boolean Anchor the start row.
    [opts.startColumnAnchored] boolean Anchor the start column.
    [opts.endRowAnchored] boolean Anchor the end row.
    [opts.endColumnAnchored] boolean Anchor the end column.
    [opts.anchored] boolean Anchor all row and columns.

    range.cell(ri, ci) ⇒ Cell

    Gets a cell within the range.

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

    Param Type Description
    ri number Row index relative to the top-left corner of the range (0-based).
    ci number Column index relative to the top-left corner of the range (0-based).

    range.autoFilter() ⇒ Range

    Sets sheet autoFilter to this range.

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

    range.cells() ⇒ Array.<Array.<Cell>>

    Get the cells in the range as a 2D array.

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

    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.

    Param Type Description
    callback forEachCallback Function called for each cell in 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.

    Param Type Description
    formula string The formula to set.

    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.

    Param Type Description
    callback mapCallback Function called for each cell in the range.

    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.

    Param Type Description
    merged boolean True to merge, false to unmerge.

    range.dataValidation() ⇒ object | undefined

    Gets the data validation object attached to the Range.

    Kind: instance method of Range
    Returns: object | undefined - The data validation object or undefined if not set.

    range.dataValidation(dataValidation) ⇒ Range

    Set or clear the data validation object of the entire range.

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

    Param Type Description
    dataValidation object | undefined Object or null to clear.

    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.

    Param Type Description
    callback reduceCallback Function called for each cell in the range.
    [initialValue] * The initial 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.

    Param Type Description
    name string The name of the style.

    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.

    Param Type Description
    names Array.<string> The names of the styles.

    range.style(name, callback) ⇒ 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.

    Param Type Description
    name string The name of the style.
    callback mapCallback The callback to provide value for the cell.

    range.style(name, values) ⇒ 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.

    Param Type Description
    name string The name of the style.
    values Array.<Array.<*>> The style values to set.

    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.

    Param Type Description
    name string The name of the style.
    value * The value to set.

    range.style(styles) ⇒ Range

    Set multiple styles for the cells in the range.

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

    Param Type Description
    styles object.<string, (Range~mapCallback|Array.<Array.<*>>|*)> Object whose keys are style names and values are either function callbacks, 2D arrays of style values, or a single value for all the cells.

    range.style(style) ⇒ Range

    Sets to a specific style

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

    Param Type Description
    style Style Style object given from stylesheet.createStyle

    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.

    Param Type Description
    callback tapCallback The callback function.

    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.

    Param Type Description
    callback thruCallback The callback function.

    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(callback) ⇒ 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.

    Param Type Description
    callback mapCallback The callback to provide value for the cell.

    range.value(values) ⇒ 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.

    Param Type Description
    values Array.<Array.<*>> The values to set.

    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.

    Param Type Description
    value * The value to set.

    range.workbook() ⇒ Workbook

    Gets the parent workbook.

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

    Range~forEachCallback ⇒ undefined

    Callback used by forEach.

    Kind: inner typedef of Range

    Param Type Description
    cell Cell The cell.
    ri number The relative row index.
    ci number The relative column index.
    range Range The range.

    Range~mapCallback ⇒ *

    Callback used by map.

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

    Param Type Description
    cell Cell The cell.
    ri number The relative row index.
    ci number The relative column index.
    range Range The range.

    Range~reduceCallback ⇒ *

    Callback used by reduce.

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

    Param Type Description
    accumulator * The accumulated value.
    cell Cell The cell.
    ri number The relative row index.
    ci number The relative column index.
    range Range The range.

    Range~tapCallback ⇒ undefined

    Callback used by tap.

    Kind: inner typedef of Range

    Param Type Description
    range Range The range.

    Range~thruCallback ⇒ *

    Callback used by thru.

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

    Param Type Description
    range Range The range.

    RichText

    A RichText class that contains many RichTextFragment.

    Kind: global class

    new RichText([node])

    Creates a new instance of RichText. If you get the instance by calling Cell.value(), adding a text contains line separator will trigger Cell.style('wrapText', true), which will make MS Excel show the new line. i.e. In MS Excel, Tap "alt+Enter" in a cell, the cell will set wrap text to true automatically.

    Param Type Description
    [node] undefined | null | Object The node stored in the shared string

    richText.cell ⇒ Cell | undefined

    Gets which cell this RichText instance belongs to.

    Kind: instance property of RichText
    Returns: Cell | undefined - The cell this instance belongs to.

    richText.length ⇒ number

    Gets the how many rich text fragment this RichText instance contains

    Kind: instance property of RichText
    Returns: number - The number of fragments this RichText instance has.

    richText.text() ⇒ string

    Gets concatenated text without styles.

    Kind: instance method of RichText
    Returns: string - concatenated text

    richText.getInstanceWithCellRef(cell) ⇒ RichText

    Gets the instance with cell reference defined.

    Kind: instance method of RichText
    Returns: RichText - The instance with cell reference defined.

    Param Type Description
    cell Cell Cell reference.

    richText.copy([cell]) ⇒ RichText

    Returns a deep copy of this instance. If cell reference is provided, it checks line separators and calls cell.style('wrapText', true) when needed.

    Kind: instance method of RichText
    Returns: RichText - A deep copied instance

    Param Type Description
    [cell] Cell | undefined The cell reference.

    richText.get(index) ⇒ RichTextFragment

    Gets the ith fragment of this RichText instance.

    Kind: instance method of RichText
    Returns: RichTextFragment - A rich text fragment

    Param Type Description
    index number The index

    richText.remove(index) ⇒ RichText

    Removes a rich text fragment. This instance will be mutated.

    Kind: instance method of RichText
    Returns: RichText - the rich text instance

    Param Type Description
    index number the index of the fragment to remove

    richText.add(text, [styles], [index]) ⇒ RichText

    Adds a rich text fragment to the last or after the given index. This instance will be mutated.

    Kind: instance method of RichText
    Returns: RichText - the rich text instance

    Param Type Description
    text string the text
    [styles] Object the styles js object, i.e. {fontSize: 12}
    [index] number | undefined | null the index of the fragment to add

    richText.clear() ⇒ RichText

    Clears this rich text

    Kind: instance method of RichText
    Returns: RichText - the rich text instance

    richText.removeUnsupportedNodes() ⇒ undefined

    Remove all unsupported nodes (phoneticPr, rPh for Japanese language).

    Kind: instance method of RichText

    RichTextFragment

    A Rich text fragment.

    Kind: global class

    new RichTextFragment(value, [styles], richText)

    Creates a new instance of RichTextFragment.

    Param Type Description
    value string | Object Text value or XML node
    [styles] object | undefined | null Multiple styles.
    richText RichText The rich text instance where this fragment belongs to.

    richTextFragment.value() ⇒ string

    Gets the value of this part of rich text

    Kind: instance method of RichTextFragment
    Returns: string - text

    richTextFragment.value(text) ⇒ RichTextFragment

    Sets the value of this part of rich text

    Kind: instance method of RichTextFragment
    Returns: RichTextFragment - - RichTextFragment

    Param Type Description
    text string the text to set

    richTextFragment.style(name) ⇒ *

    Gets an individual style.

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

    Param Type Description
    name string The name of the style.

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

    Gets multiple styles.

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

    Param Type Description
    names Array.<string> The names of the style.

    richTextFragment.style(name, value) ⇒ RichTextFragment

    Sets an individual style.

    Kind: instance method of RichTextFragment
    Returns: RichTextFragment - This RichTextFragment.

    Param Type Description
    name string The name of the style.
    value * The value to set.

    richTextFragment.style(styles) ⇒ RichTextFragment

    Sets multiple styles.

    Kind: instance method of RichTextFragment
    Returns: RichTextFragment - This RichTextFragment.

    Param Type Description
    styles object.<string, *> Object whose keys are the style names and values are the styles to set.

    Row

    A row.

    Kind: global class

    row.address([opts]) ⇒ string

    Get the address of the row.

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

    Param Type Description
    [opts] Object Options
    [opts.includeSheetName] boolean Include the sheet name in the address.
    [opts.anchored] boolean Anchor the address.

    row.cell(columnNameOrNumber) ⇒ Cell

    Get a cell in the row.

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

    Param Type Description
    columnNameOrNumber string | number The name or number of the column.

    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.

    Param Type Description
    height number The height of 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.

    Param Type Description
    hidden boolean A flag indicating whether to hide 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.style(name) ⇒ *

    Gets an individual style.

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

    Param Type Description
    name string The name of the style.

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

    Gets multiple styles.

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

    Param Type Description
    names Array.<string> The names of the style.

    row.style(name, value) ⇒ Cell

    Sets an individual style.

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

    Param Type Description
    name string The name of the style.
    value * The value to set.

    row.style(styles) ⇒ Cell

    Sets multiple styles.

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

    Param Type Description
    styles object.<string, *> Object whose keys are the style names and values are the styles to set.

    row.style(style) ⇒ Cell

    Sets to a specific style

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

    Param Type Description
    style Style Style object given from stylesheet.createStyle

    row.workbook() ⇒ Workbook

    Get the parent workbook.

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

    row.addPageBreak() ⇒ Row

    Append horizontal page break after the row.

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

    Sheet

    A worksheet.

    Kind: global class

    sheet.active() ⇒ boolean

    Gets a value indicating whether the sheet is the active sheet in the workbook.

    Kind: instance method of Sheet
    Returns: boolean - True if active, false otherwise.

    sheet.active(active) ⇒ Sheet

    Make the sheet the active sheet in the workkbok.

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

    Param Type Description
    active boolean Must be set to true. Deactivating directly is not supported. To deactivate, you should activate a different sheet instead.

    sheet.activeCell() ⇒ Cell

    Get the active cell in the sheet.

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

    sheet.activeCell(cell) ⇒ Sheet

    Set the active cell in the workbook.

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

    Param Type Description
    cell string | Cell The cell or address of cell to activate.

    sheet.activeCell(rowNumber, columnNameOrNumber) ⇒ Sheet

    Set the active cell in the workbook by row and column.

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

    Param Type Description
    rowNumber number The row number of the cell.
    columnNameOrNumber string | number The column name or number of the cell.

    sheet.cell(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.cell(rowNumber, columnNameOrNumber) ⇒ 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.
    columnNameOrNumber string | number The column name or number of the cell.

    sheet.column(columnNameOrNumber) ⇒ Column

    Gets a column in the sheet.

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

    Param Type Description
    columnNameOrNumber string | number The name or number of the column.

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

    Gets a defined name scoped to the sheet.

    Kind: instance method of Sheet
    Returns: undefined | string | Cell | Range | Row | Column - What the defined name refers to or undefined if not found. Will return the string formula if not a Row, Column, Cell, or Range.

    Param Type Description
    name string The defined name.

    sheet.definedName(name, refersTo) ⇒ Workbook

    Set a defined name scoped to the sheet.

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

    Param Type Description
    name string The defined name.
    refersTo string | Cell | Range | Row | Column What the name refers to.

    sheet.delete() ⇒ Workbook

    Deletes the sheet and returns the parent workbook.

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

    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.

    Param Type Description
    pattern string | RegExp The pattern to look for. Providing a string will result in a case-insensitive substring search. Use a RegExp for more sophisticated searches.
    [replacement] string | function The text to replace or a String.replace callback function. If pattern is a string, all occurrences of the pattern in each cell will be replaced.

    sheet.gridLinesVisible() ⇒ boolean

    Gets a value indicating whether this sheet's grid lines are visible.

    Kind: instance method of Sheet
    Returns: boolean - True if selected, false if not.

    sheet.gridLinesVisible(selected) ⇒ Sheet

    Sets whether this sheet's grid lines are visible.

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

    Param Type Description
    selected boolean True to make visible, false to hide.

    sheet.hidden() ⇒ boolean | string

    Gets a value indicating if the sheet is hidden or not.

    Kind: instance method of Sheet
    Returns: boolean | string - True if hidden, false if visible, and 'very' if very hidden.

    sheet.hidden(hidden) ⇒ Sheet

    Set whether the sheet is hidden or not.

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

    Param Type Description
    hidden boolean | string True to hide, false to show, and 'very' to make very hidden.

    sheet.move([indexOrBeforeSheet]) ⇒ Sheet

    Move the sheet.

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

    Param Type Description
    [indexOrBeforeSheet] number | string | Sheet The index to move the sheet to or the sheet (or name of sheet) to move this sheet before. Omit this argument to move to the end of the workbook.

    sheet.name() ⇒ string

    Get the name of the sheet.

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

    sheet.name(name) ⇒ Sheet

    Set the name of the sheet. Note: this method does not rename references to the sheet so formulas, etc. can be broken. Use with caution!

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

    Param Type Description
    name string The name to set to the sheet.

    sheet.range(address) ⇒ Range

    Gets a range from the given range address.

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

    Param Type Description
    address string The range address (e.g. 'A1:B3').

    sheet.range(startCell, endCell) ⇒ Range

    Gets a range from the given cells or cell addresses.

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

    Param Type Description
    startCell string | Cell The starting cell or cell address (e.g. 'A1').
    endCell string | Cell The ending cell or cell address (e.g. 'B3').

    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.

    Param Type Description
    startRowNumber number The starting cell row number.
    startColumnNameOrNumber string | number The starting cell column name or number.
    endRowNumber number The ending cell row number.
    endColumnNameOrNumber string | number The ending cell column name or number.

    sheet.autoFilter() ⇒ Sheet

    Unsets sheet autoFilter.

    Kind: instance method of Sheet
    Returns: Sheet - This sheet.

    sheet.autoFilter(range) ⇒ Sheet

    Sets sheet autoFilter to a Range.

    Kind: instance method of Sheet
    Returns: Sheet - This sheet.

    Param Type Description
    range Range The autoFilter 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.

    Param Type Description
    rowNumber number The row number.

    sheet.tabColor() ⇒ undefined | Color

    Get the tab color. (See style Color.)

    Kind: instance method of Sheet
    Returns: undefined | Color - The color or undefined if not set.

    sheet.tabColor() ⇒ Color | string | number

    Sets the tab color. (See style Color.)

    Kind: instance method of Sheet
    Returns: Color | string | number - color - Color of the tab. If string, will set an RGB color. If number, will set a theme color.

    sheet.tabSelected() ⇒ boolean

    Gets a value indicating whether this sheet is selected.

    Kind: instance method of Sheet
    Returns: boolean - True if selected, false if not.

    sheet.tabSelected(selected) ⇒ Sheet

    Sets whether this sheet is selected.

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

    Param Type Description
    selected boolean True to select, false to deselected.

    sheet.rightToLeft() ⇒ boolean

    Gets a value indicating whether this sheet is rtl (Right To Left).

    Kind: instance method of Sheet
    Returns: boolean - True if rtl, false if ltr.

    sheet.rightToLeft(rtl) ⇒ Sheet

    Sets whether this sheet is rtl.

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

    Param Type Description
    rtl boolean True to rtl, false to ltr (Left To Right).

    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() ⇒ Workbook

    Gets the parent workbook.

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

    sheet.pageBreaks() ⇒ Object

    Gets all page breaks.

    Kind: instance method of Sheet
    Returns: Object - the object holds both vertical and horizontal PageBreaks.

    sheet.verticalPageBreaks() ⇒ PageBreaks

    Gets the vertical page breaks.

    Kind: instance method of Sheet
    Returns: PageBreaks - vertical PageBreaks.

    sheet.horizontalPageBreaks() ⇒ PageBreaks

    Gets the horizontal page breaks.

    Kind: instance method of Sheet
    Returns: PageBreaks - horizontal PageBreaks.

    sheet.hyperlink(address) ⇒ string | undefined

    Get the hyperlink attached to the cell with the given address.

    Kind: instance method of Sheet
    Returns: string | undefined - The hyperlink or undefined if not set.

    Param Type Description
    address string The address of the hyperlinked cell.

    sheet.hyperlink(address, hyperlink, [internal]) ⇒ Sheet

    Set the hyperlink on the cell with the given address.

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

    Param Type Description
    address string The address of the hyperlinked cell.
    hyperlink string The hyperlink to set or undefined to clear.
    [internal] boolean The flag to force hyperlink to be internal. If true, then autodetect is skipped.

    sheet.hyperlink(address, opts) ⇒ Sheet

    Set the hyperlink on the cell with the given address and options.

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

    Param Type Description
    address string The address of the hyperlinked cell.
    opts Object | Cell Options or Cell. If opts is a Cell then an internal hyperlink is added.
    [opts.hyperlink] string | Cell The hyperlink to set, can be a Cell or an internal/external string.
    [opts.tooltip] string Additional text to help the user understand more about the hyperlink.
    [opts.email] string Email address, ignored if opts.hyperlink is set.
    [opts.emailSubject] string Email subject, ignored if opts.hyperlink is set.

    sheet.printOptions(attributeName) ⇒ boolean

    Get the print option given a valid print option attribute.

    Kind: instance method of Sheet

    Param Type Description
    attributeName string Attribute name of the printOptions. gridLines - Used in conjunction with gridLinesSet. If both gridLines and gridlinesSet are true, then grid lines shall print. Otherwise, they shall not (i.e., one or both have false values). gridLinesSet - Used in conjunction with gridLines. If both gridLines and gridLinesSet are true, then grid lines shall print. Otherwise, they shall not (i.e., one or both have false values). headings - Print row and column headings. horizontalCentered - Center on page horizontally when printing. verticalCentered - Center on page vertically when printing.

    sheet.printOptions(attributeName, attributeEnabled) ⇒ Sheet

    Set the print option given a valid print option attribute and a value.

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

    Param Type Description
    attributeName string Attribute name of the printOptions. See get print option for list of valid attributes.
    attributeEnabled undefined | boolean If undefined or false then the attribute is removed, otherwise the print option is enabled.

    sheet.printGridLines() ⇒ boolean

    Get the print option for the gridLines attribute value.

    Kind: instance method of Sheet

    sheet.printGridLines(enabled) ⇒ Sheet

    Set the print option for the gridLines attribute value.

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

    Param Type Description
    enabled undefined | boolean If undefined or false then attribute is removed, otherwise gridLines is enabled.

    sheet.pageMargins(attributeName) ⇒ number

    Get the page margin given a valid attribute name. If the value is not yet defined, then it will return the current preset value.

    Kind: instance method of Sheet
    Returns: number - the attribute value.

    Param Type Description
    attributeName string Attribute name of the pageMargins. left - Left Page Margin in inches. right - Right page margin in inches. top - Top Page Margin in inches. buttom - Bottom Page Margin in inches. footer - Footer Page Margin in inches. header - Header Page Margin in inches.

    sheet.pageMargins(attributeName, attributeStringValue) ⇒ Sheet

    Set the page margin (or override the preset) given an attribute name and a value.

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

    Param Type Description
    attributeName string Attribute name of the pageMargins. See get page margin for list of valid attributes.
    attributeStringValue undefined | number | string If undefined then set back to preset value, otherwise, set the given attribute value.

    sheet.pageMarginsPreset() ⇒ string

    Page margins preset is a set of page margins associated with a name. The page margin preset acts as a fallback when not explicitly defined by Sheet.pageMargins. If a sheet already contains page margins, it attempts to auto-detect, otherwise they are defined as the template preset. If no page margins exist, then the preset is undefined and will not be included in the output of Sheet.toXmls. Available presets include: normal, wide, narrow, template.

    Get the page margins preset name. The registered name of a predefined set of attributes.

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

    sheet.pageMarginsPreset(presetName) ⇒ Sheet

    Set the page margins preset by name, clearing any existing/temporary attribute values.

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

    Param Type Description
    presetName undefined | string The preset name. If undefined, page margins will not be included in the output of Sheet.toXmls.

    sheet.pageMarginsPreset(presetName, presetAttributes) ⇒ Sheet

    Set a new page margins preset by name and attributes object.

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

    Param Type Description
    presetName string The preset name.
    presetAttributes object The preset attributes.

    sheet.panes() ⇒ PaneOptions

    Gets sheet view pane options

    Kind: instance method of Sheet
    Returns: PaneOptions - sheet view pane options

    sheet.panes(paneOptions) ⇒ Sheet

    Sets sheet view pane options

    Kind: instance method of Sheet
    Returns: Sheet - The sheet

    Param Type Description
    paneOptions PaneOptions | null | undefined sheet view pane options

    sheet.freezePanes(xSplit, ySplit) ⇒ Sheet

    Freezes Panes for this sheet.

    Kind: instance method of Sheet
    Returns: Sheet - The sheet

    Param Type Description
    xSplit number the number of columns visible in the top pane. 0 (zero) if none.
    ySplit number the number of rows visible in the left pane. 0 (zero) if none.

    sheet.freezePanes(topLeftCell) ⇒ Sheet

    freezes Panes for this sheet.

    Kind: instance method of Sheet
    Returns: Sheet - The sheet

    Param Type Description
    topLeftCell string Top Left Visible Cell. Location of the top left visible cell in the bottom right pane (when in Left-To-Right mode).

    sheet.splitPanes(xSplit, ySplit) ⇒ Sheet

    Splits Panes for this sheet.

    Kind: instance method of Sheet
    Returns: Sheet - The sheet

    Param Type Description
    xSplit number (Horizontal Split Position) Horizontal position of the split, in 1/20th of a point; 0 (zero) if none.
    ySplit number (Vertical Split Position) VVertical position of the split, in 1/20th of a point; 0 (zero) if none.

    sheet.resetPanes() ⇒ Sheet

    resets to default sheet view panes.

    Kind: instance method of Sheet
    Returns: Sheet - The sheet

    Workbook

    A workbook.

    Kind: global class

    workbook.activeSheet() ⇒ Sheet

    Get the active sheet in the workbook.

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

    workbook.activeSheet(sheet) ⇒ Workbook

    Set the active sheet in the workbook.

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

    Param Type Description
    sheet Sheet | string | number The sheet or name of sheet or index of sheet to activate. The sheet must not be hidden.

    workbook.addSheet(name, [indexOrBeforeSheet]) ⇒ Sheet

    Add a new sheet to the workbook.

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

    Param Type Description
    name string The name of the sheet. Must be unique, less than 31 characters, and may not contain the following characters: \ / * [ ] : ?
    [indexOrBeforeSheet] number | string | Sheet The index to move the sheet to or the sheet (or name of sheet) to move this sheet before. Omit this argument to move to the end of the workbook.

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

    Gets a defined name scoped to the workbook.

    Kind: instance method of Workbook
    Returns: undefined | string | Cell | Range | Row | Column - What the defined name refers to or undefined if not found. Will return the string formula if not a Row, Column, Cell, or Range.

    Param Type Description
    name string The defined name.

    workbook.definedName(name, refersTo) ⇒ Workbook

    Set a defined name scoped to the workbook.

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

    Param Type Description
    name string The defined name.
    refersTo string | Cell | Range | Row | Column What the name refers to.

    workbook.deleteSheet(sheet) ⇒ Workbook

    Delete a sheet from the workbook.

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

    Param Type Description
    sheet Sheet | string | number The sheet or name of sheet or index of sheet to move.

    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.

    Param Type Description
    pattern string | RegExp The pattern to look for. Providing a string will result in a case-insensitive substring search. Use a RegExp for more sophisticated searches.
    [replacement] string | function The text to replace or a String.replace callback function. If pattern is a string, all occurrences of the pattern in each cell will be replaced.

    workbook.moveSheet(sheet, [indexOrBeforeSheet]) ⇒ Workbook

    Move a sheet to a new position.

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

    Param Type Description
    sheet Sheet | string | number The sheet or name of sheet or index of sheet to move.
    [indexOrBeforeSheet] number | string | Sheet The index to move the sheet to or the sheet (or name of sheet) to move this sheet before. Omit this argument to move to the end of the workbook.

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

    Generates the workbook output.

    Kind: instance method of Workbook
    Returns: Promise.<(string|Uint8Array|ArrayBuffer|Blob|Buffer)> - The data.

    Param Type Description
    [type] string The type of the data to return: base64, binarystring, uint8array, arraybuffer, blob, nodebuffer. Defaults to 'nodebuffer' in Node.js and 'blob' in browsers.

    workbook.outputAsync([opts]) ⇒ Promise.<(string|Uint8Array|ArrayBuffer|Blob|Buffer)>

    Generates the workbook output.

    Kind: instance method of Workbook
    Returns: Promise.<(string|Uint8Array|ArrayBuffer|Blob|Buffer)> - The data.

    Param Type Description
    [opts] Object Options
    [opts.type] string The type of the data to return: base64, binarystring, uint8array, arraybuffer, blob, nodebuffer. Defaults to 'nodebuffer' in Node.js and 'blob' in browsers.
    [opts.password] string The password to use to encrypt the workbook.

    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.

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

    workbook.sheets() ⇒ Array.<Sheet>

    Get an array of all the sheets in the workbook.

    Kind: instance method of Workbook
    Returns: Array.<Sheet> - The sheets.

    workbook.property(name) ⇒ *

    Gets an individual property.

    Kind: instance method of Workbook
    Returns: * - The property.

    Param Type Description
    name string The name of the property.

    workbook.property(names) ⇒ object.<string, *>

    Gets multiple properties.

    Kind: instance method of Workbook
    Returns: object.<string, *> - Object whose keys are the property names and values are the properties.

    Param Type Description
    names Array.<string> The names of the properties.

    workbook.property(name, value) ⇒ Workbook

    Sets an individual property.

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

    Param Type Description
    name string The name of the property.
    value * The value to set.

    workbook.property(properties) ⇒ Workbook

    Sets multiple properties.

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

    Param Type Description
    properties object.<string, *> Object whose keys are the property names and values are the values to set.

    workbook.properties() ⇒ CoreProperties

    Get access to core properties object

    Kind: instance method of Workbook
    Returns: CoreProperties - The core properties.

    workbook.toFileAsync(path, [opts]) ⇒ Promise.<undefined>

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

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

    Param Type Description
    path string The path of the file to write.
    [opts] Object Options
    [opts.password] string The password to encrypt the workbook.

    workbook.cloneSheet(from, name, [indexOrBeforeSheet]) ⇒ Sheet

    Add a new sheet to the workbook.

    WARN: this function has limits: if you clone a sheet with some images or other things link outside the Sheet object, these things in the cloned sheet will be locked when you open in MS Excel app.

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

    Param Type Description
    from Sheet The sheet to be cloned.
    name string The name of the new sheet. Must be unique, less than 31 characters, and may not contain the following characters: \ / * [ ] : ?
    [indexOrBeforeSheet] number | string | Sheet The index to move the sheet to or the sheet (or name of sheet) to move this sheet before. Omit this argument to move to the end of the workbook.

    XlsxPopulate : object

    Kind: global namespace

    XlsxPopulate.Promise : Promise

    The Promise library.

    Kind: static property of XlsxPopulate

    XlsxPopulate.MIME_TYPE : string

    The XLSX mime type.

    Kind: static property of XlsxPopulate

    XlsxPopulate.FormulaError : FormulaError

    Formula error class.

    Kind: static property of XlsxPopulate

    XlsxPopulate.RichText : RichText

    RichTexts class

    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.

    Param Type Description
    date Date The date.

    XlsxPopulate.fromBlankAsync() ⇒ Promise.<Workbook>

    Create a new blank workbook.

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

    XlsxPopulate.fromDataAsync(data, [opts]) ⇒ 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.

    Param Type Description
    data string | Array.<number> | ArrayBuffer | Uint8Array | Buffer | Blob | Promise.<*> The data to load.
    [opts] Object Options
    [opts.password] string The password to decrypt the workbook.

    XlsxPopulate.fromFileAsync(path, [opts]) ⇒ Promise.<Workbook>

    Loads a workbook from file.

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

    Param Type Description
    path string The path to the workbook.
    [opts] Object Options
    [opts.password] string The password to decrypt the workbook.

    XlsxPopulate.numberToDate(number) ⇒ Date

    Convert an Excel number to a date.

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

    Param Type Description
    number number The number.

    _

    OOXML uses the CFB file format with Agile Encryption. The details of the encryption are here: https://msdn.microsoft.com/en-us/library/dd950165(v=office.12).aspx

    Helpful guidance also take from this Github project: https://github.com/nolze/ms-offcrypto-tool

    Kind: global constant

    PaneOptions : Object

    https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.pane?view=openxml-2.8.1

    Kind: global typedef
    Properties

    Name Type Default Description
    activePane string "bottomRight" Active Pane. The pane that is active.
    state string Split State. Indicates whether the pane has horizontal / vertical splits, and whether those splits are frozen.
    topLeftCell string Top Left Visible Cell. Location of the top left visible cell in the bottom right pane (when in Left-To-Right mode).
    xSplit number (Horizontal Split Position) Horizontal position of the split, in 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value indicates the number of columns visible in the top pane.
    ySplit number (Vertical Split Position) Vertical position of the split, in 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value indicates the number of rows visible in the left pane.

    Install

    npm i xlsx-populate

    DownloadsWeekly Downloads

    36,976

    Version

    1.21.0

    License

    MIT

    Unpacked Size

    15.1 MB

    Total Files

    51

    Last publish

    Collaborators

    • dtjohnson