XLSX Templater

https://github.com/yangguichun/xlsx-templater
Admin
XLSX Templater is a powerful Excel template engine that uses JavaScript objects or JSON to render...
Tokens:6,898
Snippets:53
Trust Score:5.3
License:Apache-2.0
Update:6 months ago
Show doc for...
Tokens:
Raw
### 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
<!DOCTYPE html>
<html>
<head>
  <title>Excel Template Generator</title>
</head>
<body>
  <input type="file" id="templateInput" accept=".xlsx" />
  <button onclick="generateReport()">Generate Report</button>

  <script src="node_modules/@sailimuhu/xlsxtemplater/dist/xlsxtemplater.umd.js"></script>
  <script>
    async function generateReport() {
      // Get template file from input
      const fileInput = document.getElementById('templateInput');
      const templateFile = await fileInput.files[0].arrayBuffer();

      // Or fetch template from URL
      // const response = await fetch('template.xlsx');
      // const templateFile = await response.arrayBuffer();

      const templater = new XlsxTemplater(templateFile);

      await templater.render({
        reportTitle: 'Monthly Sales Report',
        generatedDate: new Date().toLocaleDateString(),
        sales: [
          { region: 'North', amount: 50000 },
          { region: 'South', amount: 45000 },
          { region: 'East', amount: 60000 }
        ]
      });

      // Get result as Blob and trigger download
      const resultBlob = await templater.renderToBlob();
      const link = document.createElement('a');
      link.href = URL.createObjectURL(resultBlob);
      link.download = 'sales-report.xlsx';
      link.click();
      URL.revokeObjectURL(link.href);
    }
  </script>
</body>
</html>
```

--------------------------------

### 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
<!-- UMD version -->
<script src="node_modules/@sailimuhu/xlsxtemplater/dist/xlsxtemplater.umd.js"></script>
<script>
  const templater = new XlsxTemplater(file); // file can be Blob/ArrayBuffer
</script>

<!-- ES Module -->
<script type="module">
  import XlsxTemplater from '@sailimuhu/xlsxtemplater';
  const templater = new XlsxTemplater(file);
</script>
```

--------------------------------

### 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;
        }
      }
    }
```
Tokens:
Raw