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
• Usage
  • Populating Data
  • Parsing Data
  • Ranges
  • Rows and Columns
  • Managing Sheets
  • Defined Names
  • Find and Replace
  • Styles
  • Rich Texts
    • Supported styles
    • Usage
    • Notes
  • Dates
  • Data Validation
  • Method Chaining
  • Hyperlinks
  • Print Options
  • Page Margins
  • SheetView Panes
  • Serving from Express
  • Browser Usage
  • Promises
  • Encryption
• Missing Features
• Submitting an Issue
• Contributing
  • How xlsx-populate Works
  • Setting up your Environment
  • Pull Request Checklist
  • Gulp Tasks
• Style Reference
• API Reference

Installation

Node.js

1npm install xlsx-populate

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.

1const XlsxPopulate = require('xlsx-populate')
2
3// Load a new blank workbook
4XlsxPopulate.fromBlank().then((workbook) => {
5  // Modify the workbook.
6  workbook.sheet('Sheet1').cell('A1').value('This is neat!')
7
8  // Write to file.
9  return workbook.toFileAsync('./out.xlsx')
10})

Parsing Data

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

1const XlsxPopulate = require('xlsx-populate')
2
3// Load an existing workbook
4XlsxPopulate.fromFileAsync('./Book1.xlsx').then((workbook) => {
5  // Modify the workbook.
6  const value = workbook.sheet('Sheet1').cell('A1').value()
7
8  // Log the value.
9  console.log(value)
10})

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.

1const r = workbook.sheet(0).range('A1:C3')
2
3// Set all cell values to the same value:
4r.value(5)
5
6// Set the values using a 2D array:
7r.value([
8  [1, 2, 3],
9  [4, 5, 6],
10  [7, 8, 9],
11])
12
13// Set the values using a callback function:
14r.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.

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

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

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

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:

1// Get the B column, set its width and unhide it (assuming it was hidden).
2sheet.column('B').width(25).hidden(false)
3
4const 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:

1// Get sheet by index
2const sheet1 = workbook.sheet(0)
3
4// Get sheet by name
5const sheet2 = workbook.sheet('Sheet2')
6
7// Get all sheets as an array
8const sheets = workbook.sheets()

You can add new sheets:

1// Add a new sheet named 'New 1' at the end of the workbook
2const newSheet1 = workbook.addSheet('New 1')
3
4// Add a new sheet named 'New 2' at index 1 (0-based)
5const newSheet2 = workbook.addSheet('New 2', 1)
6
7// Add a new sheet named 'New 3' before the sheet named 'Sheet1'
8const newSheet3 = workbook.addSheet('New 3', 'Sheet1')
9
10// Add a new sheet named 'New 4' before the sheet named 'Sheet1' using a Sheet reference.
11const sheet = workbook.sheet('Sheet1')
12const 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:

1// Rename the first sheet.
2const sheet = workbook.sheet(0).name('new sheet name')

You can move sheets:

1// Move 'Sheet1' to the end
2workbook.moveSheet('Sheet1')
3
4// Move 'Sheet1' to index 2
5workbook.moveSheet('Sheet1', 2)
6
7// Move 'Sheet1' before 'Sheet2'
8workbook.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:

1// Move the sheet before 'Sheet2'
2sheet.move('Sheet2')

You can delete sheets:

1// Delete 'Sheet1'
2workbook.deleteSheet('Sheet1')
3
4// Delete sheet with index 2
5workbook.deleteSheet(2)
6
7// Delete from sheet reference
8workbook.sheet(0).delete()

You can get/set the active sheet:

1// Get the active sheet
2const sheet = workbook.activeSheet()
3
4// Check if the current sheet is active
5sheet.active() // returns true or false
6
7// Activate the sheet
8sheet.active(true)
9
10// Or from the workbook
11workbook.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.

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

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

1// Create/modify a workbook-scope defined name
2workbook.definedName('some name', 'TRUE')
3
4// Delete a sheet-scoped defined name:
5workbook.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.

1// Find all occurrences of the text "foo" in the workbook and replace with "bar".
2workbook.find('foo', 'bar') // Returns array of matched cells
3
4// Find the matches but don't replace.
5workbook.find('foo')
6
7// Just look in the first sheet.
8workbook.sheet(0).find('foo')
9
10// Check if a particular cell matches the value.
11workbook.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:

1// Use a RegExp to replace all lowercase letters with uppercase
2workbook.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:

1// Get a single style
2const bold = cell.style('bold') // true
3
4// Get multiple styles
5const styles = cell.style(['bold', 'italic']) // { bold: true, italic: true }
6
7// Set a single style
8cell.style('bold', true)
9
10// Set multiple styles
11cell.style({ bold: true, italic: true })

Similarly for ranges:

1// Set all cells in range with a single style
2range.style('bold', true)
3
4// Set with a 2D array
5range.style('bold', [
6  [true, false],
7  [false, true],
8])
9
10// Set with a callback function
11range.style('bold', (cell, ri, ci, range) => Math.random() > 0.5)
12
13// Set multiple styles using any combination
14range.style({
15  bold: true,
16  italic: [
17    [true, false],
18    [false, true],
19  ],
20  underline: (cell, ri, ci, range) => Math.random() > 0.5,
21})

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

1// Set a single style
2sheet.row(1).style('bold', true)
3
4// Set multiple styles
5sheet.column('A').style({ bold: true, italic: true })
6
7// Get a single style
8const bold = sheet.column(3).style('bold')
9
10// Get multiple styles
11const 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:

1cell.style('fill', {
2  type: 'pattern',
3  pattern: 'darkDown',
4  foreground: {
5    rgb: 'ff0000',
6  },
7  background: {
8    theme: 3,
9    tint: 0.4,
10  },
11})

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

1cell.style('fill', '0000ff')
2
3const fill = cell.style('fill')
4/*
5fill is now set to:
6{
7    type: "solid",
8    color: {
9        rgb: "0000ff"
10    }
11}
12*/

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

1cell.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:

1// assume A1 is a rich text cell
2const RichText = require('xlsx-Populate').RichText
3const cell = workbook.sheet(0).cell('A1')
4cell.value() instanceof RichText // returns true
5const richtext = cell.value()
6// get the concatenate text
7richtext.text()
8
9// loop through each rich text fragment
10for (let i = 0; i < richtext.length; i++) {
11  const fragment = richtext.get(i)
12  // Get the style
13  fragment.style('bold')
14  // Get many styles
15  fragment.style(['bold', 'italic'])
16  // Set one style
17  fragment.style('bold', true)
18  // Set many styles
19  fragment.style({ bold: true, italic: true })
20  // Get the value
21  fragment.value()
22  // Set the value
23  fragment.value('hello')
24}
25
26// remove the first rich text fragment
27richtext.remove(0)
28
29// clear this rich texts
30richtext.clear()

How to set a cell to rich texts:

1const RichText = require('xlsx-Populate').RichText
2const cell = workbook.sheet(0).cell('A1')
3// set a cell value to rich text
4cell.value(new RichText())
5
6// add two rich text fragments
7cell
8  .value()
9  .add('hello ', { italic: true, bold: true })
10  .add('world!', { fontColor: 'FF0000' })

You can specify the index when adding rich text fragment.

1// add before the first fragment
2cell.value().add('text', { bold: true }, 0)
3// add before the second fragment
4cell.value().add('text', { bold: true }, 1)
5// add after the last fragment
6cell.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.

1const richtext = new RichText()
2richtext.add('hello')
3cell.value(richtext)
4cell.value().text() // returns 'hello'
5
6richtext.add(' world')
7richtext.text() // returns 'hello world'
8cell.value().text() // returns 'hello'
9cell.value() === richtext // returns false
10
11cell.value().add(' world')
12cell.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.

1const sheet = workbook.sheet(0)
2const richtext = new RichText()
3richtext.add('hello')
4const range = sheet.range('A1:C3')
5range.value(richtext)
6// they do not share the same instance
7sheet.cell('A1').value() === sheet.cell('C1').value() // returns false

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

1const richtext = cell1.value()
2cell2.value(richtext)
3cell1.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.

1cell
2  .value()
3  // it support all line separators
4  .add('123\n456\r789\r\n10', { italic: true, fontColor: '123456' })
5// remember to set height to show the whole row
6workbook.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:

1cell.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:

1const num = cell.value() // 42788
2const 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:

1// Set the data validation
2cell.dataValidation({
3  type: 'list',
4  allowBlank: false,
5  showInputMessage: false,
6  prompt: false,
7  promptTitle: 'String',
8  showErrorMessage: false,
9  error: 'String',
10  errorTitle: 'String',
11  operator: 'String',
12  formula1: '$A:$A', //Required
13  formula2: 'String',
14})
15
16//Here is a short version of the one above.
17cell.dataValidation('$A:$A')
18
19// Get the data validation
20const obj = cell.dataValidation() // Returns an object
21
22// Remove the data validation
23cell.dataValidation(null) //Returns the cell

Similarly for ranges:

1// Set all cells in range with a single shared data validation
2range.dataValidation({
3  type: 'list',
4  allowBlank: false,
5  showInputMessage: false,
6  prompt: false,
7  promptTitle: 'String',
8  showErrorMessage: false,
9  error: 'String',
10  errorTitle: 'String',
11  operator: 'String',
12  formula1: 'Item1,Item2,Item3,Item4', //Required
13  formula2: 'String',
14})
15
16//Here is a short version of the one above.
17range.dataValidation('Item1,Item2,Item3,Item4')
18
19// Get the data validation
20const obj = range.dataValidation() // Returns an object
21
22// Remove the data validation
23range.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:

1workbook
2  .sheet(0)
3  .cell('A1')
4  .value('foo')
5  .style('bold', true)
6  .relativeCell(1, 0)
7  .formula('A1')
8  .style('italic', true)
9  .workbook()
10  .sheet(1)
11  .range('A1:B3')
12  .value(5)
13  .cell(0, 0)
14  .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:

1// Set a hyperlink
2cell
3  .value('Link Text')
4  .style({ fontColor: '0563c1', underline: true })
5  .hyperlink('http://example.com')
6
7// Set a hyperlink with tooltip
8cell
9  .value('Link Text')
10  .style({ fontColor: '0563c1', underline: true })
11  .hyperlink({ hyperlink: 'http://example.com', tooltip: 'example.com' })
12
13// Get the hyperlink
14const value = cell.hyperlink() // Returns 'http://example.com'
15
16// Set a hyperlink to email
17cell.value('Click to Email Jeff Bezos').hyperlink({
18  email: 'jeff@amazon.com',
19  emailSubject: "I know you're a busy man Jeff, but...",
20})
21
22// Set a hyperlink to an internal cell using an address string.
23cell.value('Click to go to an internal cell').hyperlink('Sheet2!A1')
24
25// Set a hyperlink to an internal cell using a cell object.
26cell
27  .value('Click to go to an internal cell')
28  .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.

1// Print row and column headings
2sheet.printOptions('headings', true)
3
4// Get the headings flag
5const headings = sheet.printOptions('headings') // Returns true
6
7// Clear flag for center on page vertically when printing
8sheet.printOptions('verticalCentered', undefined)
9
10// Get the verticalCentered flag
11const verticalCentered = sheet.printOptions('verticalCentered') // Returns false
12
13// Enable grid lines in print
14sheet.printGridLines(true)
15
16// Now both gridLines and gridLinesSet print options are set
17;(sheet.printOptions('gridLines') === sheet.printOptions('gridLinesSet')) ===
18  true // Returns true
19
20// To disable, just disable one of gridLines or gridLinesSet
21sheet.printOptions('gridLineSets', false)
22
23const 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.

1// Get the current preset
2sheet.pageMarginsPreset() // Returns undefined
3
4// Switch to an existing preset
5sheet.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.

1// Get top margin in inches, note that the current preset is currently set to normal (see above)
2sheet.pageMargins('top') // Returns 0.75
3
4// Set top page margin in inches
5sheet.pageMargins('top', 1.1)
6
7// Get top page margin in inches.
8const 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.

1// access Pane options
2sheet.panes() // return PaneOptions Object
3
4// manually Set Pane options, WARNING: setting wrong options may result in excel fails to open.
5const paneOptions = {
6  state: 'frozen',
7  topLeftCell: 'B2',
8  xSplit: 1,
9  ySplit: 1,
10  activePane: 'bottomRight',
11}
12sheet.panes(paneOptions) // return PaneOptions Object
13
14// freeze panes (freeze first column and first two rows)
15sheet.freezePanes(1, 2)
16// OR
17sheet.freezePanes('B3')
18
19// split panes (Horizontal Split Position: 1000 / 20 pt, Vertical Split Position: 2000 / 20 pt)
20sheet.splitPanes(1000, 2000)
21
22// reset to normal panes (no freeze panes and split panes)
23sheet.resetPanes()

Serving from Express

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

1router.get('/download', function (req, res, next) {
2  // Open the workbook.
3  XlsxPopulate.fromFileAsync('input.xlsx')
4    .then((workbook) => {
5      // Make edits.
6      workbook.sheet(0).cell('A1').value('foo')
7
8      // Get the output
9      return workbook.output()
10    })
11    .then((data) => {
12      // Set the output file name.
13      res.attachment('output.xlsx')
14
15      // Send the workbook.
16      res.send(data)
17    })
18    .catch(next)
19})

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:

1// Assuming there is a file input in the page with the id 'file-input'
2var file = document.getElementById('file-input').files[0]
3
4// A File object is a special kind of blob.
5XlsxPopulate.fromData(file).then(function (workbook) {
6  // ...
7})

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

1var req = new XMLHttpRequest()
2req.open('GET', 'http://...', true)
3req.responseType = 'arraybuffer'
4req.onreadystatechange = function () {
5  if (req.readyState === 4 && req.status === 200) {
6    XlsxPopulate.fromData(req.response).then(function (workbook) {
7      // ...
8    })
9  }
10}
11
12req.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:

1workbook.output().then(function (blob) {
2  if (window.navigator && window.navigator.msSaveOrOpenBlob) {
3    // If IE, you must uses a different method.
4    window.navigator.msSaveOrOpenBlob(blob, 'out.xlsx')
5  } else {
6    var url = window.URL.createObjectURL(blob)
7    var a = document.createElement('a')
8    document.body.appendChild(a)
9    a.href = url
10    a.download = 'out.xlsx'
11    a.click()
12    window.URL.revokeObjectURL(url)
13    document.body.removeChild(a)
14  }
15})

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

1workbook.output('base64').then(function (base64) {
2  location.href = 'data:' + XlsxPopulate.MIME_TYPE + ';base64,' + base64
3})

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.

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

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

1const Promise = require('bluebird')
2const XlsxPopulate = require('xlsx-populate')
3XlsxPopulate.Promise = Promise

Encryption

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

1XlsxPopulate.fromFileAsync('./Book1.xlsx', { password: 'S3cret!' }).then(
2  (workbook) => {
3    // ...
4  },
5)

Similarly, to write a password encrypted workbook:

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

1npm 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:

1npm 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:

1gulp

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:

1gulp 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 fromBlank.
• 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 gradient 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.

Range

A range of cells.

RichText

A RichText class that contains many RichTextFragment.

RichTextFragment

A Rich text fragment.

Row

A row.

SharedStrings

The shared strings table.

Sheet

A worksheet.

Style

A style.

StyleSheet

A style sheet.

Workbook

A workbook.

Constants

STANDARD_CODES

Standard number format codes Taken from http://polymathprogrammer.com/2011/02/15/built-in-styles-for-excel-open-xml/

STARTING_CUSTOM_NUMBER_FORMAT_ID

The starting ID for custom number formats. The first 163 indexes are reserved.

Typedefs

PaneOptions : Object

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

Workbook

A workbook.

Kind: global class

• Workbook
  • instance
    • ._sheets : Array.<Sheet>
    • .activeSheet(sheet) ⇒ Workbook
    • .addSheet(name, [indexOrBeforeSheet]) ⇒ Sheet
    • .definedName(name, refersTo) ⇒ Workbook
    • .deleteSheet(sheet) ⇒ Workbook
    • .find(pattern, [replacement]) ⇒ boolean
    • .moveSheet(sheet, [indexOrBeforeSheet]) ⇒ Workbook
    • .output([opts]) ⇒ Promise.<(string|Uint8Array|ArrayBuffer|Blob|Buffer)>
    • .sheet(sheetNameOrIndex) ⇒ Sheet | undefined
    • .sheets() ⇒ Array.<Sheet>
    • .property(properties) ⇒ Workbook
    • .properties() ⇒ CoreProperties
    • .toFileAsync(path, [opts]) ⇒ Promise.<undefined>
    • .scopedDefinedName(sheetScope, name, refersTo) ⇒ Workbook
    • .sharedStrings() ⇒ SharedStrings
    • .styleSheet() ⇒ StyleSheet
    • .cloneSheet(from, name, [indexOrBeforeSheet]) ⇒ Sheet
    • ._addSheet(name, [indexOrBeforeSheet], [getTemplateNodes]) ⇒ Sheet
    • ._initAsync(data, [opts]) ⇒ Promise.<Workbook>
    • ._parseNodesAsync(names) ⇒ Promise.<Array.<{}>>
    • ._parseSheetRefs() ⇒ void
    • ._setSheetRefs() ⇒ void
    • ._convertBufferToOutput(buffer, type) ⇒ Buffer | Blob | string | Uint8Array | ArrayBuffer
    • ._convertInputToBufferAsync(input, [base64]) ⇒ Promise.<(ArrayBuffer|string)>
  • static
    • .MIME_TYPE : string
    • .fromBlank() ⇒ Promise.<Workbook>
    • .fromData(data, [opts]) ⇒ Promise.<Workbook>
    • .fromFileAsync(path, [opts]) ⇒ Promise.<Workbook>

workbook._sheets : Array.<Sheet>

Kind: instance property of Workbook

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