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.
npm install xlsx-populateNote that xlsx-populate uses ES6 features so only Node.js v4+ is supported.
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-populateAfter 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.
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.
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");
});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.
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.
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.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");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);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 falseLike