Generate .xlsx (Excel) files from templates built in Excel
npm install xlsx-template.xlsx files
bash
npm install xlsx-template
`
Quick Start
1. Create an Excel template (template.xlsx) with placeholders:
| | A | B |
|---|---|---|
| 1 | Report Date | ${reportDate} |
| 2 | Company | ${companyName} |
2. Use the template in your code:
`javascript
const XlsxTemplate = require('xlsx-template');
const fs = require('fs');
// Load the template
fs.readFile('template.xlsx', (err, data) => {
const template = new XlsxTemplate(data);
// Define replacement values
const values = {
reportDate: new Date(),
companyName: 'Acme Corporation'
};
// Perform substitution on sheet 1 (can also use sheet name as string: 'Sheet1')
template.substitute(1, values);
// Generate the output file
const output = template.generate();
// Save to disk
fs.writeFileSync('output.xlsx', output);
});
`
Placeholder Types
$3
Replace a placeholder with a single value.
Excel template:
| | A | B |
|---|---|---|
| 1 | Extracted on: | ${extractDate} |
Code:
`javascript
const values = {
extractDate: new Date('2024-01-15')
};
template.substitute(1, values);
`
Result:
| | A | B |
|---|---|---|
| 1 | Extracted on: | Jan-15-2024 |
Notes:
- Placeholders can be standalone in a cell or part of text: "Total: ${amount}"
- Excel cell formatting (date, number, currency) is preserved
$3
Access specific array elements directly in templates.
Excel template:
| | A | B |
|---|---|---|
| 1 | First date: | ${dates[0]} |
| 2 | Second date: | ${dates[1]} |
Code:
`javascript
const values = {
dates: [new Date('2024-01-01'), new Date('2024-02-01')]
};
template.substitute(1, values);
`
Result:
| | A | B |
|---|---|---|
| 1 | First date: | Jan-01-2024 |
| 2 | Second date: | Feb-01-2024 |
$3
Expand an array horizontally across columns.
Excel template:
| | A |
|---|---|
| 1 | ${dates} |
Code:
`javascript
const values = {
dates: [
new Date('2024-01-01'),
new Date('2024-02-01'),
new Date('2024-03-01')
]
};
template.substitute(1, values);
`
Result:
| | A | B | C |
|---|---|---|---|
| 1 | Jan-01-2024 | Feb-01-2024 | Mar-01-2024 |
Notes:
- The placeholder must be the only content in its cell
$3
Generate multiple rows from an array of objects.
Excel template:
| | A | B | C |
|---|---|---|---|
| 1 | Name | Age | Department |
| 2 | ${table:team.name} | ${table:team.age} | ${table:team.dept} |
Code:
`javascript
const values = {
team: [
{ name: 'Alice Johnson', age: 28, dept: 'Engineering' },
{ name: 'Bob Smith', age: 34, dept: 'Marketing' },
{ name: 'Carol White', age: 25, dept: 'Sales' }
]
};
template.substitute(1, values);
`
Result:
| | A | B | C |
|---|---|---|---|
| 1 | Name | Age | Department |
| 2 | Alice Johnson | 28 | Engineering |
| 3 | Bob Smith | 34 | Marketing |
| 4 | Carol White | 25 | Sales |
Notes:
- Syntax: ${table:arrayName.propertyName}
- Each object in the array creates a new row
- If a property is an array, it expands horizontally
$3
Insert images into cells.
Excel template:
| | A | B |
|---|---|---|
| 1 | Logo: | ${image:companyLogo} |
Code:
`javascript
const values = {
companyLogo: '/path/to/logo.png' // or Base64, Buffer
};
template.substitute(1, values);
`
Result:
| | A | B |
|---|---|---|
| 1 | Logo: | 🖼️ |
Supported image formats:
- File path (absolute or relative): '/path/to/image.png'
- Base64 string: 'data:image/png;base64,iVBORw0KG...'
- Buffer: fs.readFileSync('image.png')
- URL: Not supported - This library is synchronous and cannot fetch remote images. Fetch the image in your own code first, then pass it as one of the supported formats above.
Image options:
`javascript
const template = new XlsxTemplate(data, {
imageRootPath: '/absolute/path/to/images', // Base path for relative image paths
imageRatio: 75 // Scale images to 75% (only for non-merged cells)
});
`
Table images:
| | A | B |
|---|---|---|
| 1 | Product | Photo |
| 2 | ${table:products.name} | ${table:products.photo:image} |
`javascript
const values = {
products: [
{ name: 'Product 1', photo: 'product1.jpg' },
{ name: 'Product 2', photo: 'product2.jpg' }
]
};
`
Result:
| | A | B |
|---|---|---|
| 1 | Product | Photo |
| 2 | Product 1 | 🖼️ |
| 3 | Product 2 | 🖼️ |
#### Images in Merged Cells
Images automatically fit the size of merged cells.
Excel template with merged cells B1:C2:
| | A | B-C (merged) |
|---|---|---|
| 1-2 (merged) | Large Image: | ${image:banner} |
`javascript
const values = {
banner: 'banner-image.png'
};
template.substitute(1, values);
`
Result: The image will be automatically resized to fit the merged cell area (B1:C2).
$3
Insert images that automatically fit cell size (requires Excel 2308+).
> ⚠️ Warning: This feature requires Excel version 2308 or later. Excel 2302 and earlier do not support this.
This is the equivalent of Excel's "Place in Cell" feature (right-click on image → "Place in Cell").
Excel template:
| | A |
|---|---|
| 1 | ${imageincell:profilePicture} |
Code:
`javascript
const values = {
profilePicture: 'avatar.jpg'
};
template.substitute(1, values);
`
Result:
| | A |
|---|---|
| 1 | 🖼️ |
Features:
- Images automatically match cell/merged cell size
- Respects cell alignment and formatting
- Perfect for profile pictures, thumbnails, etc.
Table usage:
| | A | B |
|---|---|---|
| 1 | Employee | Avatar |
| 2 | ${table:employees.name} | ${table:employees.avatar:imageincell} |
`javascript
const values = {
employees: [
{ name: 'Alice', avatar: 'alice.jpg' },
{ name: 'Bob', avatar: 'bob.jpg' }
]
};
`
Result:
| | A | B |
|---|---|---|
| 1 | Employee | Avatar |
| 2 | Alice | 🖼️ |
| 3 | Bob | 🖼️ |
API Reference
$3
Create a new template instance.
Parameters:
- data (Buffer|String) - The .xlsx file content (binary data)
- options (Object) - Optional configuration:
- imageRootPath (String) - Root directory for relative image paths
- imageRatio (Number) - Image scaling percentage (default: 100)
- moveImages (Boolean) - Move images when inserting table rows (default: false)
- moveSameLineImages (Boolean) - Move images on the same line as inserted rows (default: false)
- subsituteAllTableRow (Boolean) - Apply substitutions to all cells in table rows (default: false)
- pushDownPageBreakOnTableSubstitution (Boolean) - Adjust page breaks when tables grow (default: false)
Example:
`javascript
const template = new XlsxTemplate(data, {
imageRootPath: __dirname + '/images',
imageRatio: 80,
moveImages: true
});
`
$3
Replace placeholders with values on a specific sheet.
Parameters:
- sheetNumber (Number|String) - Sheet index (1-based) or sheet name
- values (Object) - Key-value pairs for placeholder substitution
Example:
`javascript
template.substitute(1, { name: 'John', age: 30 });
template.substitute('Sales Report', { quarter: 'Q1', revenue: 50000 });
`
$3
Replace placeholders on all sheets with the same values.
Parameters:
- values (Object) - Key-value pairs for placeholder substitution
Example:
`javascript
template.substituteAll({
companyName: 'Acme Corp',
reportDate: new Date()
});
`
$3
Generate the final Excel file.
Parameters:
- options (Object) - JSZip generation options:
- type (String) - Output format:
- 'nodebuffer' - Node.js Buffer (recommended for file I/O)
- 'base64' - Base64 string
- 'uint8array' - Uint8Array
- 'arraybuffer' - ArrayBuffer
- 'blob' - Blob (browser only)
Returns: The generated file in the specified format
Example:
`javascript
const buffer = template.generate({ type: 'nodebuffer' });
fs.writeFileSync('output.xlsx', buffer);
// Or for web download
const base64 = template.generate({ type: 'base64' });
const downloadLink = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + base64;
`
$3
Copy an existing sheet to a new sheet in the same workbook.
Parameters:
- sheetName (String|Number) - Source sheet name or index (1-based)
- newSheetName (String) - Name for the new sheet (optional, defaults to "SheetN")
Returns: this (for chaining)
> Note: The optional binary parameter (third parameter) is deprecated and should not be used. It will be removed in a future version. Always use the default behavior which preserves UTF-8 encoding correctly.
Example:
`javascript
template.copySheet('Template', 'January Report');
template.copySheet(1, 'Q1 Data');
// Chain operations
template.copySheet('Template', 'Report1')
.substitute('Report1', { month: 'January' })
.copySheet('Template', 'Report2')
.substitute('Report2', { month: 'February' });
`
Notes:
- Copies all content/Relation: data, formatting, formulas, comments, images, print settings
- Merged cells and named ranges are preserved
- Comments (including threaded comments) are copied with unique IDs
$3
Delete a sheet from the workbook.
Parameters:
- sheetName (String|Number) - Sheet name or index (1-based) to delete
Returns: this (for chaining)
Example:
`javascript
template.deleteSheet('Sheet2');
template.deleteSheet(3);
`
Complete Example
`javascript
const XlsxTemplate = require('xlsx-template');
const fs = require('fs');
// Load template
const templateData = fs.readFileSync('sales-template.xlsx');
const template = new XlsxTemplate(templateData, {
imageRootPath: __dirname + '/assets',
moveImages: true
});
// Prepare data
const data = {
reportDate: new Date(),
companyName: 'Acme Corporation',
region: 'North America',
// Table data
salesData: [
{ product: 'Widget A', qty: 150, price: 25.50, photo: 'widget-a.jpg' },
{ product: 'Widget B', qty: 200, price: 30.00, photo: 'widget-b.jpg' },
{ product: 'Gadget X', qty: 80, price: 55.75, photo: 'gadget-x.jpg' }
],
// Chart data (arrays)
months: ['Jan', 'Feb', 'Mar', 'Apr'],
revenues: [45000, 52000, 48000, 61000],
// Company logo
logo: 'company-logo.png',
// Formula
totalFormula: '=SUM(D2:D100)'
};
// Apply substitutions
template.substitute(1, data);
// Generate output
const output = template.generate({ type: 'nodebuffer' });
fs.writeFileSync('sales-report.xlsx', output);
console.log('Report generated successfully!');
`
Important Notes & Limitations
$3
- ✅ Only .xlsx format is supported
- ❌ .xls, .xlsb, .xlsm formats are not supported
$3
- Merged cells are automatically adjusted when rows/columns are inserted
- Named ranges and tables are moved correctly
$3
When using ${table:...} placeholders:
- Rows below the table are pushed down automatically
- Columns to the right are shifted if arrays expand horizontally
- Use Excel Named Tables for best formula compatibility
- Page breaks can be automatically adjusted with the pushDownPageBreakOnTableSubstitution option
$3
- Images in merged cells automatically fit the merged area
- Standard cells: use imageRatio option to scale
- The moveImages option shifts images when rows are inserted (move the anchor)
$3
- Large templates with many placeholders may take time to process
- Consider splitting very large reports across multiple sheets
- Image processing (especially Base64) can be memory-intensive
---
Contributing
Contributions are welcome! Please feel free to submit issues or pull requests.
Pull Request Requirements:
- All PRs must include unit tests for new features or bug fixes
- Ensure all existing tests pass (npm test)
- Follow the existing code style and conventions
License
MIT License - see LICENSE file for details
Authors
- Martin Aspeli - Original author
- Andrii Kurdiumov (@kant2002) - Maintainer
- And many contributors
---
Changelog History
$3
* Revert dynamic file extension detection to hardcoded 'jpg' (#218) (@jdugh)
$3
* feat: preserve metadata types when adding images in cells (#216) (@jdugh)
* Update deleteSheet for delete also relationships of sheet (#215) (@jdugh)
* Fix: Handle missing drawing rels file when worksheet contains shapes only (#214) (@atsu0127)
* create a new relationships element if the rels file does not exist.
* Add test for image substitution in worksheets with shapes lacking drawing rels
$3
* Fixed UTF-8 encoding in copySheet() - sheet content now properly preserved in binary mode
* copySheet() now properly copies comments (including threaded comments)
* Note: Do not use binary=false parameter as it corrupts UTF-8 characters. May be deprecated in future versions.
* Added comprehensive sheet copying tests
* Excel Column hiding functionality. (#199) Thanks @thelunarwolf
$3
* Move hyperlinks references on added rows and columns. (#184). Thanks @IagoSRL
* Fix line issue under table with merged cell. (#188). Thanks @muyoungko
$3
* Fix potential issue when template has lot of images.
* Update image-size to 1.0.2
$3
* Move to @kant2002/jszip which fix https://github.com/advisories/GHSA-36fh-84j7-cv5h
* Fix previously broken release.
$3
* Move to @kant2002/jszip which fix https://github.com/advisories/GHSA-36fh-84j7-cv5h
* Also broke everything. DONT USE THIS VERSION
$3
* substituteAll: Interpolate values for all the sheets using the given substitutions (#173) Thanks @jonathankeebler
* int and float don't exist in Typescript, both are of type number. This fixes it. (#169) Thanks @EHadoux
options to generate(), which are passed to JSZip