Excel Report Generation with ExcelJS

The Judicial Backend generates .xlsx files using ExcelJS through a shared, abstract ExcelBuilder class that lives in src/shared/excel/. Module-level report classes extend this builder, declare their column layout and section definitions, then call build() to receive a ready-to-stream Buffer. Controllers pipe that buffer directly to the HTTP response with the appropriate download headers.

ExcelBuilder

Abstract base class in src/shared/excel/excel.builder.ts. Manages the ExcelJS workbook and worksheet, exposes protected helpers for ranged rows, flat rows, subtotals, vertical merges, and the final toBuffer() call.

Styles

src/shared/excel/excel.styles.ts — pre-built Partial<ExcelJS.Style> objects for header bands, data rows, subtotals, and zebra striping, all built from a shared color palette.

Types

src/shared/excel/excel.types.ts — ColumnRange, ReportSection<T>, and ColumnWidth interfaces that describe the shape of every section in a report.

Module reports

Concrete subclasses in src/modules/*/reports/excel/ extend ExcelBuilder, define their ReportSection constants, and implement build().

Shared Types

All column and section metadata is expressed with three interfaces from excel.types.ts.

// A horizontal span that forms one "visual cell" (false merge)
export interface ColumnRange {
  start: number; // 1-based column index
  end: number;
}

// Full description of one logical section of the report
export interface ReportSection<T> {
  columns: ColumnRange[];         // spans that map to visual columns
  headers: string[];              // header labels, same order as columns
  getValues: (item: T) => unknown[];           // extracts values from a data row
  rowStyle: (rowIndex: number) => Partial<ExcelJS.Style>; // per-row style callback
  headerStyle: Partial<ExcelJS.Style>;         // style applied to the header row
}

// Column width entry (1-based index)
export interface ColumnWidth {
  col: number;
  width: number;
}

ColumnRange uses a start/end pair instead of a single index. Cells between start and end are physically merged after the row is written, giving the appearance of wide columns even though ExcelJS works cell-by-cell internally.

Styles

excel.styles.ts exports a Styles constant and a zebraStyle helper. The full color palette is:

Token	ARGB	Usage
`primary`	`FF9BC2E6`	Primary header fill (blue)
`primaryLight`	`FFDDEBF7`	Even data rows
`secondary`	`FFD9D9D9`	Secondary header / subtotal fill (grey)
`background`	`FFFFFFFF`	White — odd rows and vertical merge cells
`text`	`FF000000`	All font colors

export const Styles = {
  headerPrimary:     { font: { bold: true, ... }, fill: fill(COLORS.primary),      alignment: alignCenter },
  headerSecondary:   { font: { bold: true, ... }, fill: fill(COLORS.secondary),    alignment: alignCenter },
  headerPrimaryLeft: { font: { bold: true, ... }, fill: fill(COLORS.primary),      alignment: alignLeft },
  subtotal:          { font: { bold: true, ... }, fill: fill(COLORS.secondary),    alignment: alignLeft },
  mergedVertical:    { font: { ... },              fill: fill(COLORS.background),   alignment: alignTopLeft },
  rowEven:           { fill: fill(COLORS.primaryLight), alignment: alignCenter },
  rowOdd:            { fill: fill(COLORS.background),   alignment: alignCenter },
  dataCenter:        { alignment: alignCenter },
  dataLeft:          { alignment: alignLeft },
} satisfies Record<string, Partial<ExcelJS.Style>>;

/** Zebra helper — alternates rowEven / rowOdd by row index */
export const zebraStyle = (index: number): Partial<ExcelJS.Style> =>
  index % 2 === 0 ? Styles.rowEven : Styles.rowOdd;

Use zebraStyle as the rowStyle callback on any ReportSection where you want alternating row colors automatically. See EstadisticaPlanoExcelReport for an example.

ExcelBuilder — Abstract Class

ExcelBuilder is declared abstract so it can never be instantiated directly. Every concrete report class must implement build(data: unknown[]): Promise<Buffer>.

Constructor

constructor(
  sheetName: string,
  columnWidths: ColumnWidth[],
  sharedWorkbook?: ExcelJS.Workbook,
)

Passing sharedWorkbook lets multiple report sheets share the same .xlsx workbook (used by the statistics multi-sheet export).

Abstract method

abstract build(data: unknown[]): Promise<Buffer>;

Protected methods available to subclasses

// Write the header row for a section
protected addSectionHeader(section: ReportSection<unknown>): void

// Write a data row for a section item
protected addSectionData<T>(
  section: ReportSection<T>,
  item: T,
  rowIndex: number,
): void

// Write a flat row (no ranged merges), returns the ExcelJS Row
protected addFlatRow(
  values: unknown[],
  totalCols: number,
  style?: Partial<ExcelJS.Style>,
): ExcelJS.Row

// Write the "Expedido: <date>" summary row
protected addExpedidoRow(
  textoTotal: string,
  colTotal: number,
  colExpedido: number,
  totalCols?: number,
): ExcelJS.Row

// Write a labelled subtotal row with a merged label span
protected addSubtotalRow(
  label: string,
  totalValue: number | string,
  startCol: number,
  endMergeCol: number,
  totalCols: number,
  style: Partial<ExcelJS.Style>,
): void

// Merge vertically repeated values in specified columns
protected applyVerticalMerges(
  primeraFila: number,
  columnasAFusionar: number[],
): void

// Safe cell merge — silently ignores accidental overlaps
protected mergeCustomCells(
  startRow: number,
  startCol: number,
  endRow: number,
  endCol: number,
): void

// Serialize the workbook to a Buffer
protected async toBuffer(): Promise<Buffer>

addSectionHeader and addSectionData delegate to the private addRangedRow method, which writes the physical ExcelJS row, applies styles cell-by-cell, sets thin borders, and then calls mergeCells for every ColumnRange with start !== end.

Module-Level Reports

`ApelacionExcelReport` (búsqueda completa)

Located at src/modules/busqueda/reports/excel/apelacion-excel.report.ts.

export class ApelacionExcelReport extends ExcelBuilder {
  constructor() {
    super('Resultados', [
      { col: 1, width: 5 },
      { col: 2, width: 15 },
      // … 30 column widths total
    ]);
  }

  async build(apelaciones: ApelacionDTO[]): Promise<Buffer>
}

The report contains three ReportSection constants defined at module scope:

Constant	Type param	Columns	Purpose
`SECCION_PRINCIPAL`	`ApelacionDTO`	1–30 (17 ranges)	Core apelación fields
`SECCION_PARTES`	`ParteDTO`	2–18 (5 ranges)	Associated parties
`SECCION_ANEXOS`	`AnexoDTO`	2–18 (9 ranges)	Attached annexes

build() calls escribirPrimeraFila() (total + expedition date), then loops over apelaciones printing the main section followed by inline partes and anexos sub-sections for each record.

`EstadisticaPlanoExcelReport` (estadísticas)

Located at src/modules/estadistica/reports/excel/estadistica-plano-excel.report.ts.

export class EstadisticaPlanoExcelReport extends ExcelBuilder {
  constructor(sharedWorkbook?: ExcelJS.Workbook) {
    super('Resultados Planos', [ /* 15 column widths */ ], sharedWorkbook);
  }

  async build(apelaciones: EstadisticaDto[]): Promise<Buffer>
}

This report uses zebraStyle as the rowStyle callback and accepts an optional sharedWorkbook so the statistics endpoint can bundle multiple sheets (plano, agrupado, gráfico) into a single .xlsx file.

Export Endpoints

All export routes are protected by the JWT protect middleware. Query parameters follow the same filter shape as the corresponding search endpoints.

Method	Path	Report class	Description
`GET`	`/api/busquedas/exportar-excel`	`ApelacionExcelReport`	Full apelaciones with partes and anexos
`GET`	`/api/busquedas/plano/exportar-excel`	`ApelacionPlanoExcelReport`	Flat apelaciones (scalars only)
`GET`	`/api/busquedas/historico/exportar-excel`	`ApelacionHistoricoExcelReport`	Historical apelaciones
`POST`	`/api/estadisticas/exportar`	`EstadisticaPlanoExcelReport` + others	Statistics — plano, agrupado, gráfico sheets

HTTP Response Headers

Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Disposition: attachment; filename="reporte.xlsx"

Example — download with curl

curl -o reporte.xlsx \
  'http://localhost:4000/api/busquedas/exportar-excel?idSala=1' \
  -H 'Authorization: Bearer <token>'

# Statistics export (POST with filter body)
curl -o reporte.xlsx \
  -X POST \
  'http://localhost:4000/api/estadisticas/exportar' \
  -H 'Authorization: Bearer <token>' \
  -H 'Content-Type: application/json' \
  -d '{"idSala": 1, "anio": 2024}'

Data Flow

The sequence from HTTP request to downloaded file is:

GET /api/busquedas/exportar-excel?idSala=1
  │
  ▼
protect middleware  (JWT RS256 verify)
  │
  ▼
BusquedaApelacionController.exportarExcel(filtros)
  │
  ▼
BusquedaApelacionService.exportarExcel(filtros)
  ├─► BusquedaApelacionRepository.buscar(filtros)  → ApelacionDTO[]
  └─► new ApelacionExcelReport().build(apelaciones) → Buffer
  │
  ▼
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
res.setHeader('Content-Disposition', 'attachment; filename="reporte.xlsx"')
res.send(buffer)

The Buffer returned by toBuffer() is produced by ExcelJS’s workbook.xlsx.writeBuffer(). No temporary files are written to disk — the workbook is serialized entirely in memory before being streamed to the client.

Get Started

Architecture

Authentication

Core Concepts

Report Generation

Excel Report Generation with ExcelJS

ExcelBuilder

Styles

Types

Module reports

Shared Types

Styles

ExcelBuilder — Abstract Class

Constructor

Abstract method

Protected methods available to subclasses

Module-Level Reports

`ApelacionExcelReport` (búsqueda completa)

`EstadisticaPlanoExcelReport` (estadísticas)

Export Endpoints

HTTP Response Headers

Example — download with curl

Data Flow

Build docs developers (and LLMs) love

Get Started

Architecture

Authentication

Core Concepts

Report Generation

Documentation Index

ExcelBuilder

Styles

Types

Module reports

​Shared Types

​Styles

​ExcelBuilder — Abstract Class

​Constructor

​Abstract method

​Protected methods available to subclasses

​Module-Level Reports

​ApelacionExcelReport (búsqueda completa)

​EstadisticaPlanoExcelReport (estadísticas)

​Export Endpoints

​HTTP Response Headers

​Example — download with curl

​Data Flow

Build docs developers (and LLMs) love

Shared Types

Styles

ExcelBuilder — Abstract Class

Constructor

Abstract method

Protected methods available to subclasses

Module-Level Reports

`ApelacionExcelReport` (búsqueda completa)

`EstadisticaPlanoExcelReport` (estadísticas)

Export Endpoints

HTTP Response Headers

Example — download with curl

Data Flow