NestboltNestbolt

@nestbolt/excel

Template Exports

Fill existing Excel templates with dynamic data using FromTemplate and WithTemplateData -- placeholder bindings and repeating row data.

Template exports let you start from an existing .xlsx file -- complete with formatting, logos, merged cells, and formulas -- and fill it with dynamic data at runtime. This is ideal for invoices, branded reports, compliance documents, and any other output where the layout is designed ahead of time in Excel.

FromTemplate

The FromTemplate interface is the foundation of template exports. It provides two methods:

  • templatePath() -- returns the path to the .xlsx template file.
  • bindings() -- returns a map of placeholder keys to replacement values.
import { FromTemplate } from "@nestbolt/excel";

class InvoiceExport implements FromTemplate {
  constructor(private readonly invoice: Invoice) {}

  templatePath() {
    return "./templates/invoice-template.xlsx";
  }

  bindings() {
    return {
      "{{company}}": this.invoice.companyName,
      "{{invoiceNumber}}": this.invoice.number,
      "{{date}}": this.invoice.date,
      "{{dueDate}}": this.invoice.dueDate,
      "{{customerName}}": this.invoice.customer.name,
      "{{customerAddress}}": this.invoice.customer.address,
      "{{subtotal}}": this.invoice.subtotal,
      "{{tax}}": this.invoice.tax,
      "{{total}}": this.invoice.total,
    };
  }
}

How Placeholders Work

The library scans every cell in the template for placeholder patterns (e.g., {{company}}). When a match is found:

  • Exact match -- if the cell contains only the placeholder and nothing else, the binding value is written with its original type. Numbers remain numbers, dates remain dates, and formulas (if you return them) remain formulas. This preserves Excel formatting tied to the cell.

  • Embedded match -- if the placeholder is part of a longer string (e.g., "Invoice #{{invoiceNumber}}"), the placeholder is replaced within the string and the result is always a string.

// Template cell contains: "Invoice #{{invoiceNumber}}"
// Binding: { "{{invoiceNumber}}": "INV-2026-0042" }
// Result cell value: "Invoice #INV-2026-0042" (string)

// Template cell contains only: "{{total}}"
// Binding: { "{{total}}": 4200 }
// Result cell value: 4200 (number -- preserves type)

Template Path

The templatePath() method returns either an absolute path or a path relative to the process working directory:

templatePath() {
  return path.join(__dirname, "..", "templates", "invoice.xlsx");
}

Make sure the template file is included in your build output or deployment artifact.

WithTemplateData

The WithTemplateData interface extends template exports with repeating row data. This is the key feature for line items in invoices, row-based reports, or any section of the template that should be repeated for each data record.

WithTemplateData adds two methods:

  • dataStartCell() -- the cell reference where repeating rows begin.
  • templateData() -- a two-dimensional array of row data. Can be async.
import { FromTemplate, WithTemplateData } from "@nestbolt/excel";

class InvoiceExport implements FromTemplate, WithTemplateData {
  constructor(private readonly invoice: Invoice) {}

  templatePath() {
    return "./templates/invoice-template.xlsx";
  }

  bindings() {
    return {
      "{{company}}": this.invoice.companyName,
      "{{invoiceNumber}}": this.invoice.number,
      "{{date}}": this.invoice.date,
      "{{total}}": this.invoice.total,
    };
  }

  dataStartCell() {
    return "A10"; // line items start at row 10
  }

  async templateData() {
    return this.invoice.lineItems.map((item) => [
      item.description,
      item.quantity,
      item.unitPrice,
      item.quantity * item.unitPrice,
    ]);
  }
}

How Repeating Rows Work

The dataStartCell() specifies where the first row of data is written. Each subsequent array from templateData() is placed on the next row below. For example, if dataStartCell() returns "A10" and templateData() returns 5 rows:

  • Row 1: written to cells A10, B10, C10, D10
  • Row 2: written to cells A11, B11, C11, D11
  • Row 3: written to cells A12, B12, C12, D12
  • Row 4: written to cells A13, B13, C13, D13
  • Row 5: written to cells A14, B14, C14, D14

Any existing content in the template below the start cell is pushed down to make room for the inserted rows (depending on how the template is designed).

Combining Bindings and Data

Bindings and repeating data work together. A typical template layout might look like this:

Row 1:  [Company Logo]    {{company}}
Row 2:  Invoice #{{invoiceNumber}}
Row 3:  Date: {{date}}        Due: {{dueDate}}
Row 4:  Bill To: {{customerName}}
Row 5:  {{customerAddress}}
Row 6:  (empty)
Row 7:  (empty)
Row 8:  Description    Qty    Unit Price    Total
Row 9:  (empty - header styling row)
Row 10: (data starts here)
...
Row N+10: (end of line items)
Row N+12: Subtotal: {{subtotal}}
Row N+13: Tax: {{tax}}
Row N+14: Total: {{total}}

The bindings replace {{company}}, {{invoiceNumber}}, and all other placeholders. The repeating data fills in line items starting at A10.

Full Invoice Template Example

Here is a complete, production-style example bringing together FromTemplate, WithTemplateData, WithTitle, and WithProperties:

The Template

Design your template in Excel with the desired formatting, column widths, borders, and logos. Place {{placeholder}} markers where dynamic values should appear. Leave a row clear where line items will begin.

The Export Class

import {
  FromTemplate,
  WithTemplateData,
  WithTitle,
  WithProperties,
} from "@nestbolt/excel";
import * as path from "path";

interface LineItem {
  description: string;
  quantity: number;
  unitPrice: number;
}

interface Invoice {
  number: string;
  date: string;
  dueDate: string;
  company: string;
  customer: {
    name: string;
    address: string;
    city: string;
    email: string;
  };
  lineItems: LineItem[];
  subtotal: number;
  taxRate: number;
  tax: number;
  total: number;
  notes: string;
}

class InvoiceExport implements FromTemplate, WithTemplateData, WithTitle, WithProperties {
  constructor(private readonly invoice: Invoice) {}

  templatePath() {
    return path.join(__dirname, "..", "templates", "invoice-template.xlsx");
  }

  bindings() {
    return {
      "{{company}}": this.invoice.company,
      "{{invoiceNumber}}": this.invoice.number,
      "{{date}}": this.invoice.date,
      "{{dueDate}}": this.invoice.dueDate,
      "{{customerName}}": this.invoice.customer.name,
      "{{customerAddress}}": this.invoice.customer.address,
      "{{customerCity}}": this.invoice.customer.city,
      "{{customerEmail}}": this.invoice.customer.email,
      "{{subtotal}}": this.invoice.subtotal,
      "{{taxRate}}": `${this.invoice.taxRate}%`,
      "{{tax}}": this.invoice.tax,
      "{{total}}": this.invoice.total,
      "{{notes}}": this.invoice.notes,
    };
  }

  dataStartCell() {
    return "A6";
  }

  async templateData() {
    return this.invoice.lineItems.map((item) => [
      item.description,
      item.quantity,
      item.unitPrice,
      item.quantity * item.unitPrice,
    ]);
  }

  title() {
    return `Invoice ${this.invoice.number}`;
  }

  properties() {
    return {
      creator: this.invoice.company,
      title: `Invoice ${this.invoice.number}`,
      subject: `Invoice for ${this.invoice.customer.name}`,
    };
  }
}

The Controller

import { Controller, Get, Param } from "@nestjs/common";
import { ExcelService } from "@nestbolt/excel";
import { InvoicesService } from "./invoices.service";
import { InvoiceExport } from "./exports/invoice.export";

@Controller("invoices")
export class InvoicesController {
  constructor(
    private readonly excelService: ExcelService,
    private readonly invoicesService: InvoicesService,
  ) {}

  @Get(":id/download")
  async downloadInvoice(@Param("id") id: string) {
    const invoice = await this.invoicesService.findOne(id);
    const exportable = new InvoiceExport(invoice);

    return this.excelService.downloadAsStream(
      exportable,
      `invoice-${invoice.number}.xlsx`,
    );
  }
}

Storing the Invoice to Cloud Storage

Instead of downloading, you can store the generated invoice directly:

@Post(":id/generate")
async generateInvoice(@Param("id") id: string) {
  const invoice = await this.invoicesService.findOne(id);
  const exportable = new InvoiceExport(invoice);

  await this.excelService.store(
    exportable,
    `invoices/${invoice.number}.xlsx`,
    undefined,
    "s3",
  );

  return { message: "Invoice generated", path: `invoices/${invoice.number}.xlsx` };
}

Tips for Template Design

  • Use named styles in your template. Cell formatting (fonts, borders, number formats) defined in the template is preserved. The library only replaces cell values, not styles.
  • Pre-format data cells. If a column should display as currency, format those cells in the template as currency. When the binding writes a number to that cell, Excel uses the existing format.
  • Leave room for data rows. If using WithTemplateData, make sure the area below dataStartCell() is clear or designed to accommodate variable-length data.
  • Test with sample data. Open the template in Excel and manually enter sample values to verify that formulas, conditional formatting, and print areas work correctly before automating with @nestbolt/excel.

Concern Exports

Build advanced exports using concern interfaces -- composable features for data sources, headings, mapping, styling, multiple sheets, events, and more.

Imports

Read, transform, and validate spreadsheet data with the import pipeline -- ToArray, ToCollection, heading rows, mapping, validation, batching, and more.

On this page

FromTemplateHow Placeholders WorkTemplate PathWithTemplateDataHow Repeating Rows WorkCombining Bindings and DataFull Invoice Template ExampleThe TemplateThe Export ClassThe ControllerStoring the Invoice to Cloud StorageTips for Template Design