### Install xlsxtemplater
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Install the package using npm or yarn.
```bash
npm install @sailimuhu/xlsxtemplater
# or
yarn add @sailimuhu/xlsxtemplater
```
--------------------------------
### Quick Start Rendering
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Render a template with data and save the output file.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Create a new instance with your template file
const templater = new XlsxTemplater('./template.xlsx');
// Render with your data
await templater.render({
company: 'Acme Corp',
date: '2024-03-20',
items: [
{ name: 'Item 1', price: 100 },
{ name: 'Item 2', price: 200 }
]
});
// Save the rendered file
await templater.save('./output.xlsx');
```
--------------------------------
### Full Data Rendering Example
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Demonstrates rendering with complex nested data structures.
```javascript
let XlsxTemplater = require('XlsxTemplater')
let templater = new XlsxTemplater('./data/month_sale_report.xlsx')
templater.render({
company: 'Huajie',
createTime: '2022-12-09 05:25:00',
reporters: ['Zhangsan', 'Lisi'],
summary:{
salesAmount: 5200000,
newCustomer: 3,
orderAmount: 6200000,
productList: ['productA', 'productB', 'productC']
},
orders: [
{
date: '2022-11-1',
number: 'X221101001',
customer: 'dazu',
products: ['productA', 'productB'],
salesAmount: 520000,
remark: ''
},
{
date: '2022-11-3',
number: 'X221103002',
customer: 'vivo',
products: ['productA', 'productC'],
salesAmount: 320000,
remark: ''
}
]
})
```
--------------------------------
### Single-line Loop Tag Example
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Use single-line loop tags for simple array data where start and end tags are on the same line. This example iterates over 'items' to display name, quantity, and price.
```plaintext
{#items} {name}| {quantity} | {@price}{value} {type}{/price} | {/items}
```
```javascript
templater.render({
items: [
{
name: "Product A",
quantity: 5,
price: {
type: "currency",
value: 10,
},
},
{
name: "Product B",
quantity: 1,
price: {
type: "currency",
value: 20,
},
},
],
});
```
--------------------------------
### Multi-line Loop Tag Example
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Multi-line loop tags are used for repeating blocks of content where start and end tags span multiple rows. This example iterates over 'defects' to display issue details.
```plaintext
{#defects}{description} | {rectify_plan} | {responsible_party.name} |
Contact: {responsible_party.tel} | | |
Result: {rectify_result} {/defects}
```
```javascript
{
defects: [
{
description: 'Issue 1',
rectify_plan: 'Fix 1',
rectify_result: 'Completed',
responsible_party: {
name: 'Zhang San',
tel: '13800000001'
}
},
{
description: 'Issue 2',
rectify_plan: 'Fix 2',
rectify_result: 'In Progress',
responsible_party: {
name: 'Li Si',
tel: '13800000002'
}
}
]
}
```
--------------------------------
### Browser Usage Example
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Demonstrates how to use XLSX Templater in a browser environment. It involves fetching a template file (e.g., from a file input or URL), rendering it with data, and then triggering a download of the resulting Excel file as a Blob.
```html
Excel Template Generator
```
--------------------------------
### Object Tag Example
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Object tags are used to access nested JSON data. They support nesting and can span multiple rows. This example accesses basic contact and company information.
```plaintext
{@basic}{hostCompany}| | | |
{createTime}|{contactName}|{contactPhone}|{/basic}
```
```javascript
templater.render({
basic:{
hostCompany: 'Huajie',
contactName: 'Zhangsan',
contactPhone: '13088888888',
createTime: '2022-12-09 05:25:00'
}
})
```
--------------------------------
### Innerloop Tag Example
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Innerloop tags fill a single cell with values from an array. Both start and end tags must be within the same cell. This example displays item names, tag values, quantity, and price.
```plaintext
{#items} {name} | {#tags}{value},{/} | {quantity} | {price} | {/items}
```
```javascript
templater.render({
"items": [
{
"name": "AcmeSoft",
"tags": [{ "value": "fun" }, { "value": "awesome" }],
"quantity": 10,
"price": "$100"
}
]
})
```
--------------------------------
### Image Tag Example
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Image tags are used to insert images into cells. The image will fill the entire cell, so cell size adjustments may be necessary.
```plaintext
{%beforePic}|{%afterPic}|
```
--------------------------------
### Complex Document Generation with Combined Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Combine normal tags, object tags, summary objects, and loops with nested data structures for comprehensive report generation. This example demonstrates nesting loops and objects within loops.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Complex template combining all tag types
const workbook = await XlsxTemplater.renderFromFile('./monthly-report.xlsx', {
// Normal tags
reportTitle: 'Monthly Sales Report',
reportDate: '2024-03-31',
// Object tag data
company: {
name: 'Global Sales Corp',
logo: 'https://example.com/logo.png', // Image inside object
address: '100 Commerce Street',
contact: {
manager: 'Sarah Johnson',
email: 'sarah@globalsales.com'
}
},
// Summary object
summary: {
totalRevenue: '$1,250,000',
totalOrders: 847,
topProducts: ['Widget Pro', 'Gadget Max', 'Tool Elite'] // Array as comma-string
},
// Loop data with nested objects and innerloops
regions: [
{
name: 'North America',
revenue: '$500,000',
manager: { name: 'John Smith', photo: 'https://example.com/john.jpg' },
products: [{ name: 'Widget' }, { name: 'Gadget' }], // Innerloop data
growth: '+15%'
},
{
name: 'Europe',
revenue: '$450,000',
manager: { name: 'Emma Wilson', photo: 'https://example.com/emma.jpg' },
products: [{ name: 'Tool' }, { name: 'Device' }],
growth: '+12%'
},
{
name: 'Asia Pacific',
revenue: '$300,000',
manager: { name: 'Wei Chen', photo: 'https://example.com/wei.jpg' },
products: [{ name: 'Widget' }, { name: 'Tool' }, { name: 'System' }],
growth: '+25%'
}
]
});
/*
Template structure:
Row 1: {reportTitle} - Generated: {reportDate}
Row 2: {@company}{name}
Row 3: {%logo}
Row 4: {@contact}Manager: {manager} | Email: {email}{/contact}{/company}
Row 5: {@summary}Total Revenue: {totalRevenue} | Orders: {totalOrders}
Row 6: Top Products: {topProducts}{/summary}
Row 7:
Row 8: | Region | Revenue | Manager | Products | Growth |
Row 9: | {#regions}{name} | {revenue} | {@manager}{name} {%photo}{/manager} | {#products}{name},{/} | {growth}{/regions} |
*/
await workbook.xlsx.writeFile('./monthly-report.xlsx');
```
--------------------------------
### Render Single-Line Loop Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Generates multiple rows from array data where the start and end tags are on the same row.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Single-line loop - start and end tags on same row
const workbook = await XlsxTemplater.renderFromFile('./invoice-template.xlsx', {
invoiceNo: 'INV-001',
items: [
{ name: 'Laptop', quantity: 2, price: 1200 },
{ name: 'Mouse', quantity: 5, price: 25 },
{ name: 'Keyboard', quantity: 3, price: 75 }
]
});
// Result generates 3 rows:
// | Laptop | 2 | 1200 |
// | Mouse | 5 | 25 |
// | Keyboard | 3 | 75 |
await workbook.xlsx.writeFile('./invoice.xlsx');
```
--------------------------------
### Render Innerloop Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Renders array items within a single cell. The end tag must be `{/}` and reside in the same cell as the start tag.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
const workbook = await XlsxTemplater.renderFromFile('./product-list.xlsx', {
products: [
{
name: 'Enterprise Server',
tags: [{ value: 'hardware' }, { value: 'enterprise' }, { value: 'high-performance' }],
quantity: 10,
price: '$5,000'
},
{
name: 'Developer Workstation',
tags: [{ value: 'hardware' }, { value: 'development' }],
quantity: 50,
price: '$2,500'
}
]
});
// Result:
// | Enterprise Server | hardware,enterprise,high-performance, | 10 | $5,000 |
// | Developer Workstation | hardware,development, | 50 | $2,500 |
await workbook.xlsx.writeFile('./product-list.xlsx');
```
--------------------------------
### Render Multi-Line Loop Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Handles complex repeating sections where tags span multiple rows. All rows between start and end tags are duplicated for each array item.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Multi-line loop - tags span multiple rows
const workbook = await XlsxTemplater.renderFromFile('./inspection-report.xlsx', {
projectName: 'Building Inspection',
defects: [
{
description: 'Cracked foundation wall',
rectify_plan: 'Inject epoxy resin',
rectify_result: 'Completed',
responsible_party: {
name: 'Zhang Wei',
tel: '13800138001'
}
},
{
description: 'Water damage on ceiling',
rectify_plan: 'Replace drywall, fix leak',
rectify_result: 'In Progress',
responsible_party: {
name: 'Li Ming',
tel: '13900139002'
}
}
]
});
// Result: Each 3-row block is duplicated per defect item
// All merged cells, styles, and conditional formatting are preserved
await workbook.xlsx.writeFile('./inspection-report.xlsx');
```
--------------------------------
### Initialize xlsxtemplater in Browser
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Load the library in the browser using UMD or ES modules.
```html
```
--------------------------------
### Initialize xlsxtemplater in Node.js
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Initialize the templater using CommonJS or ESM syntax.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Create a new instance with your template file
const templater = new XlsxTemplater('./template.xlsx');
```
```javascript
import XlsxTemplater from '@sailimuhu/xlsxtemplater';
// Create a new instance with your template file
const templater = new XlsxTemplater('./template.xlsx');
```
--------------------------------
### Render Excel in Browser
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Fetch a template file as an ArrayBuffer and render it to a Blob.
```javascript
// Browser example
const response = await fetch('template.xlsx');
const templateFile = await response.arrayBuffer();
const templater = new XlsxTemplater(templateFile);
await templater.render(data);
// Get result as Blob
const resultBlob = await templater.renderToBlob();
// Or download directly
const link = document.createElement('a');
link.href = URL.createObjectURL(resultBlob);
link.download = 'result.xlsx';
link.click();
```
--------------------------------
### Render from File (Node.js)
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Use renderFromFile to process a template file from the filesystem with provided data. The rendered workbook can then be saved to a new file or specific worksheets can be selected for rendering.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Render template from file path with data
const workbook = await XlsxTemplater.renderFromFile('./template.xlsx', {
company: 'Acme Corp',
date: '2024-03-20',
contact: {
name: 'John Doe',
email: 'john@acme.com'
},
items: [
{ name: 'Product A', quantity: 5, price: 100 },
{ name: 'Product B', quantity: 3, price: 200 }
]
});
// Save the rendered workbook
await workbook.xlsx.writeFile('./output.xlsx');
// Or render only specific worksheets by name
const workbookPartial = await XlsxTemplater.renderFromFile(
'./multi-sheet-template.xlsx',
data,
['Sheet1', 'Summary'] // Only render these worksheets
);
```
--------------------------------
### Insert Images from URLs using {%imageTag}
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Use the {%imageTag} to insert images into cells from absolute URLs. Images will automatically fill the cell dimensions. Supported formats include jpg, jpeg, png, and gif. This works within loops as well.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
const workbook = await XlsxTemplater.renderFromFile('./comparison-report.xlsx', {
projectName: 'Site Renovation',
beforePic: 'https://example.com/images/before-photo.jpg',
afterPic: 'https://example.com/images/after-photo.png',
// Images work inside loops too
inspectionItems: [
{
location: 'Main Entrance',
photo: 'https://example.com/images/entrance.jpg',
status: 'Approved'
},
{
location: 'Parking Area',
photo: 'https://example.com/images/parking.jpg',
status: 'Needs Work'
}
]
});
// Template with images in loop:
// | Location | Photo | Status |
// | {#inspectionItems}{location} | {%photo} | {status}{/inspectionItems} |
// Note: Image URLs must be absolute URLs (http:// or https://)
// Supported formats: jpg, jpeg, png, gif
await workbook.xlsx.writeFile('./comparison-report.xlsx');
```
--------------------------------
### Render from Buffer (Node.js)
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Use renderFromBuffer to process an Excel template provided as a Buffer, returning the result also as a Buffer. This is useful for handling data from sources like API requests or databases without direct filesystem access. The output buffer can be written to a file or sent directly in an HTTP response.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
const fs = require('fs');
// Read template file into buffer
const templateBuffer = fs.readFileSync('./template.xlsx');
// Render and get result as buffer
const resultBuffer = await XlsxTemplater.renderFromBuffer(templateBuffer, {
invoiceNumber: 'INV-2024-001',
customer: 'ACME Industries',
total: 15000,
lineItems: [
{ description: 'Consulting Services', amount: 10000 },
{ description: 'Software License', amount: 5000 }
]
});
// Write result buffer to file
fs.writeFileSync('./invoice.xlsx', resultBuffer);
// Or send as HTTP response in Express
app.get('/download-invoice', async (req, res) => {
const result = await XlsxTemplater.renderFromBuffer(templateBuffer, invoiceData);
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader('Content-Disposition', 'attachment; filename=invoice.xlsx');
res.send(result);
});
```
--------------------------------
### Normal Tag Syntax
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Basic usage of normal tags and rendering data into them.
```javascript
{someTag}
```
```javascript
templater.render({
hostCompany: 'Huajie',
createTime: '2022-12-09 05:25:00',
productList:['productA', 'productB']
})
```
--------------------------------
### Render Normal Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Replaces simple placeholders with values. Arrays are automatically converted to comma-separated strings, and missing tags result in empty cells.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
const workbook = await XlsxTemplater.renderFromFile('./template.xlsx', {
company: 'TechCorp Industries',
createTime: '2024-03-20 14:30:00',
productList: ['Widget Pro', 'Gadget Plus', 'Tool Master'], // Becomes: "Widget Pro,Gadget Plus,Tool Master"
// Missing tags are replaced with empty string automatically
// remark: undefined -> renders as empty cell
});
await workbook.xlsx.writeFile('./output.xlsx');
// Result:
// | TechCorp Industries | 2024-03-20 14:30:00 | Widget Pro,Gadget Plus,Tool Master |
```
--------------------------------
### Auto-filling Empty String for Missing Tags
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
If a data field is missing, it will be replaced with an empty string in the rendered template. This is useful for optional fields.
```javascript
templater.render({
hostCompany: 'Huajie',
createTime: '2022-12-09 05:25:00'
})
```
--------------------------------
### Render Object Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
Accesses nested object properties without repeating the parent path. Supports spanning multiple cells and rows.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
const workbook = await XlsxTemplater.renderFromFile('./contact-card.xlsx', {
contact: {
name: 'Jane Smith',
email: 'jane.smith@company.com',
phone: '+1-555-0123',
address: '123 Business Ave, Suite 100'
},
// Nested object tags are supported
company: {
name: 'Global Tech Inc',
details: {
industry: 'Technology',
employees: 5000,
founded: 2005
}
}
});
// Template with nested object tags:
// {@company}{name}
// {@details}{industry} | {employees} employees | Founded {founded}{/details}
// {/company}
await workbook.xlsx.writeFile('./contact-card.xlsx');
```
--------------------------------
### Preserve Conditional Formatting with Loop Tags
Source: https://context7.com/yangguichun/xlsx-templater/llms.txt
When loop tags add or delete rows, conditional formatting rules are automatically adjusted to maintain correct cell references. This ensures that formatting, such as highlighting cells based on a condition, is correctly applied to all generated rows.
```javascript
const XlsxTemplater = require('@sailimuhu/xlsxtemplater');
// Template has conditional formatting (e.g., highlight cells where value > 1000)
// When rows are duplicated, formatting rules are cloned and adjusted
const workbook = await XlsxTemplater.renderFromFile('./budget-template.xlsx', {
departments: [
{ name: 'Engineering', budget: 500000, spent: 480000 }, // Within budget
{ name: 'Marketing', budget: 200000, spent: 250000 }, // Over budget - highlighted
{ name: 'Operations', budget: 150000, spent: 145000 }, // Within budget
{ name: 'Sales', budget: 300000, spent: 350000 } // Over budget - highlighted
]
});
// Conditional formatting that highlights "spent > budget" is preserved
// and correctly applied to each generated row
await workbook.xlsx.writeFile('./budget-report.xlsx');
```
--------------------------------
### Patch Exceljs spliceRows function
Source: https://github.com/yangguichun/xlsx-templater/blob/main/readme.md
Provides the original and corrected implementation of the spliceRows function to fix merged cell issues when deleting rows in Exceljs versions 4.4.0 or earlier.
```js
if (nExpand < 0) {
// remove rows
if (start === nEnd) {
this._rows[nEnd - 1] = undefined;
}
for (i = nKeep; i <= nEnd; i++) {
rSrc = this._rows[i - 1];
if (rSrc) {
const rDst = this.getRow(i + nExpand);
rDst.values = rSrc.values;
rDst.style = rSrc.style;
rDst.height = rSrc.height;
// eslint-disable-next-line no-loop-func
rSrc.eachCell({includeEmpty: true}, (cell, colNumber) => {
rDst.getCell(colNumber).style = cell.style;
// remerge cells accounting for insert offset
if (cell._value.constructor.name === 'MergeValue') {
const cellToBeMerged = this.getRow(cell._row._number + nExpand).getCell(colNumber);
const prevMaster = cell._value._master;
const newMaster = this.getRow(prevMaster._row._number + nExpand).getCell(prevMaster._column._number);
cellToBeMerged.merge(newMaster);
}
});
this._rows[i - 1] = undefined;
} else {
this._rows[i + nExpand - 1] = undefined;
}
}
}
```
```js
if (nExpand < 0) {
// remove rows
if (start === nEnd) {
this._rows[nEnd - 1] = undefined;
}
for (i = nKeep; i <= nEnd; i++) {
rSrc = this._rows[i - 1];
if (rSrc) {
const rDst = this.getRow(i + nExpand);
rDst.values = rSrc.values;
rDst.style = rSrc.style;
rDst.height = rSrc.height;
// eslint-disable-next-line no-loop-func
rSrc.eachCell({includeEmpty: true}, (cell, colNumber) => {
rDst.getCell(colNumber).style = cell.style;
// new added, fix the unmerged cell bug
// remerge cells accounting for insert offset
if (cell._value.constructor.name === 'MergeValue') {
const cellToBeMerged = this.getRow(cell._row._number + nExpand).getCell(colNumber);
const prevMaster = cell._value._master;
const newMaster = this.getRow(prevMaster._row._number + nExpand).getCell(prevMaster._column._number);
cellToBeMerged.merge(newMaster);
}
});
this._rows[i - 1] = undefined;
} else {
this._rows[i + nExpand - 1] = undefined;
}
}
}
```
=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.