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 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());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.
You can read/write rich texts to cells.
bold, italic, underline, strikethrough, subscript, fontSize,
fontFamily, fontGenericFamily, fontScheme, fontColor.
See the Style Reference for the various options.
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 });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 falseYou 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 falseWhenever 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);Excel stores date/times as the number of days since 1/1/1900 (