Generate Excel (.xlsx) reports from templates with dynamic data substitution. Create beautifully formatted Excel files using Excel as your template designer, then populate them with data from your Node.js application.

• Real Excel files - Generate actual .xlsx files
• Template-based - Design templates in Excel with all formatting, formulas, and styles
• Dynamic data - Replace placeholders with values, arrays, tables, and images
• Preserve formatting - Cell formatting, merged cells, formulas, named tables are maintained
• Image support - Insert images from file paths or Base64
• Multiple sheets - Work with multiple sheets, copy/delete sheets dynamically
• Lightweight & cross-platform - Only 3 dependencies (jszip, elementtree, image-size). Direct XML DOM manipulation with no superfluous overhead. Works seamlessly on Windows, Linux, and macOS.

npm install xlsx-template

1. Create an Excel template (template.xlsx) with placeholders:

2. Use the template in your code:

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

Replace a placeholder with a single value.

Excel template:

Code:

const values = {
    extractDate: new Date('2024-01-15')
};
template.substitute(1, values);

Result:

Notes:

• Placeholders can be standalone in a cell or part of text: "Total: ${amount}"
• Excel cell formatting (date, number, currency) is preserved

Access specific array elements directly in templates.

Excel template:

Code:

const values = {
    dates: [new Date('2024-01-01'), new Date('2024-02-01')]
};
template.substitute(1, values);

Result:

Expand an array horizontally across columns.

Excel template:

Code:

const values = {
    dates: [
        new Date('2024-01-01'),
        new Date('2024-02-01'),
        new Date('2024-03-01')
    ]
};
template.substitute(1, values);

Result:

Notes:

• The placeholder must be the only content in its cell

Generate multiple rows from an array of objects.

Excel template:

Code:

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:

Notes:

• Syntax: ${table:arrayName.propertyName}
• Each object in the array creates a new row
• If a property is an array, it expands horizontally

Insert images into cells.

Excel template:

Code:

const values = {
    companyLogo: '/path/to/logo.png'  // or Base64, Buffer
};
template.substitute(1, values);

Result:

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:

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:

const values = {
    products: [
        { name: 'Product 1', photo: 'product1.jpg' },
        { name: 'Product 2', photo: 'product2.jpg' }
    ]
};

Result:

Images automatically fit the size of merged cells.

Excel template with merged cells B1:C2:

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

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:

Code:

const values = {
    profilePicture: 'avatar.jpg'
};
template.substitute(1, values);

Result:

Features:

• Images automatically match cell/merged cell size
• Respects cell alignment and formatting
• Perfect for profile pictures, thumbnails, etc.

Table usage:

const values = {
    employees: [
        { name: 'Alice', avatar: 'alice.jpg' },
        { name: 'Bob', avatar: 'bob.jpg' }
    ]
};

Result:

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:

const template = new XlsxTemplate(data, {
    imageRootPath: __dirname + '/images',
    imageRatio: 80,
    moveImages: true
});

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:

template.substitute(1, { name: 'John', age: 30 });
template.substitute('Sales Report', { quarter: 'Q1', revenue: 50000 });

Replace placeholders on all sheets with the same values.

Parameters:

• values (Object) - Key-value pairs for placeholder substitution

Example:

template.substituteAll({
    companyName: 'Acme Corp',
    reportDate: new Date()
});

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:

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;

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:

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

Delete a sheet from the workbook.

Parameters:

• sheetName (String|Number) - Sheet name or index (1-based) to delete

Returns: this (for chaining)

Example:

template.deleteSheet('Sheet2');
template.deleteSheet(3);

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!');

• ✅ Only .xlsx format is supported
• ❌ .xls, .xlsb, .xlsm formats are not supported

• Merged cells are automatically adjusted when rows/columns are inserted
• Named ranges and tables are moved correctly

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

• 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)

• 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

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

MIT License - see LICENSE file for details

• Martin Aspeli - Original author
• Andrii Kurdiumov (@kant2002) - Maintainer
• And many contributors

• 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

• Move hyperlinks references on added rows and columns. (#184). Thanks @IagoSRL
• Fix line issue under table with merged cell. (#188). Thanks @muyoungko

• Fix potential issue when template has lot of images.
• Update image-size to 1.0.2

• Move to @kant2002/jszip which fix https://github.com/advisories/GHSA-36fh-84j7-cv5h
• Fix previously broken release.

• Move to @kant2002/jszip which fix https://github.com/advisories/GHSA-36fh-84j7-cv5h
• Also broke everything. DONT USE THIS VERSION

• substituteAll: Interpolate values for all the sheets using the given substitutions (#173) Thanks @jonathankeebler
• intandfloatdon't exist in Typescript, both are of typenumber`. This fixes it. (#169) Thanks @EHadoux
• Insert images. (#126). Thanks @jdugh
• Add customXml in the order list for rebuild. (#154). Thanks @jdugh
• Adding 2 options affect table substitution : subsituteAllTableRow and pushDownPageBreakOnTableSubstitution. (#124). Thanks @jdugh

• Fix import statement for jszip

• Added the imageRatio parameter like a percent ratio when insert images. (#121)
• Add new substitution for images. (#110)
• Fixing Defined Range Name with Sheet Name. (#150)
• Add binary option for copySheet : for header/footer in UTF-8 (#130)

• Added support for optional moving of the images together with table. (#109)

• Specify license field in addition to licenses field in the package.json (#102)

• Added TypeScript definitions. #101
• NodeJS 12, 14 support

Nothing to see here. Just I'm being brave and make version 1.0.0

• Placeholder in hyperlinks. #87
• NodeJS 10 support

• Fix wrongly replacing text in shared strings #81

• Add ability copy and delete sheets.

• Fix bug with calculating when adding columns

• You can now pass options to generate(), which are passed to JSZip
• Fix setting of sheet when growing the sheet
• Fix corruption of sheet when writing dates
• Fix corruption of sheet when calculating calcChain

• Mysterious

Merged pending pull requests

• Deletion of the sheets.

Merged a number of overdue pull requests, including:

• Windows support
• Support for table footers
• Documentation improvements

• Fix a potential issue with the typing of string indices that could cause the first string to not render correctly if it contained a substitution.

• Initial release