@memberjunction/export-engine

A versatile data export engine for MemberJunction that supports Excel (.xlsx), CSV, and JSON output formats. The package provides a unified API for exporting data with rich formatting, multi-sheet workbooks, row sampling, conditional formatting, data validation, and more.

Architecture

%%{init: {'theme': 'base', 'themeVariables': { 'fontSize': '14px' }}}%%
classDiagram
    direction TB

    class ExportEngine {
        +export(data, options) ExportResult
        +toExcel(data, options) ExportResult
        +toCSV(data, options) ExportResult
        +toJSON(data, options) ExportResult
        +toExcelMultiSheet(sheets, options) ExportResult
        +createExporter(format, options) BaseExporter
        +getSupportedFormats() ExportFormat[]
        +getFormatCapabilities(format) Capabilities
    }

    class BaseExporter {
        <<abstract>>
        #options: ExportOptions
        +export(data) ExportResult*
        +getMimeType() string*
        +getFileExtension() string*
        #deriveColumns(data) ExportColumn[]
        #applySampling(data) ExportData
        #extractRowValues(row, columns) unknown[]
        #formatValue(value, column) unknown
    }

    class ExcelExporter {
        +export(data) ExportResult
        +getMimeType() string
        +getFileExtension() string
    }

    class CSVExporter {
        -delimiter: string
        -lineEnding: string
        +export(data) ExportResult
        +getMimeType() string
        +getFileExtension() string
    }

    class JSONExporter {
        -prettyPrint: boolean
        -indent: number
        +export(data) ExportResult
        +getMimeType() string
        +getFileExtension() string
    }

    ExportEngine --> BaseExporter : creates
    BaseExporter <|-- ExcelExporter
    BaseExporter <|-- CSVExporter
    BaseExporter <|-- JSONExporter

    style ExportEngine fill:#2d6a9f,stroke:#1a4971,color:#fff
    style BaseExporter fill:#7c5295,stroke:#563a6b,color:#fff
    style ExcelExporter fill:#2d8659,stroke:#1a5c3a,color:#fff
    style CSVExporter fill:#2d8659,stroke:#1a5c3a,color:#fff
    style JSONExporter fill:#2d8659,stroke:#1a5c3a,color:#fff

Export Flow

%%{init: {'theme': 'base', 'themeVariables': { 'fontSize': '14px' }}}%%
flowchart TD
    A[Input Data] --> B[ExportEngine.export]
    B --> C{Format?}

    C -->|excel| D[ExcelExporter]
    C -->|csv| E[CSVExporter]
    C -->|json| F[JSONExporter]

    D --> G[Apply Sampling]
    E --> G
    F --> G

    G --> H[Derive Columns]
    H --> I[Format Values]
    I --> J{Multi-Sheet?}

    J -->|Yes| K[Create Workbook with Multiple Sheets]
    J -->|No| L[Create Single Sheet]

    K --> M[Apply Styling & Features]
    L --> M

    M --> N[Generate Buffer]
    N --> O[ExportResult]

    style A fill:#2d6a9f,stroke:#1a4971,color:#fff
    style B fill:#2d6a9f,stroke:#1a4971,color:#fff
    style C fill:#b8762f,stroke:#8a5722,color:#fff
    style D fill:#2d8659,stroke:#1a5c3a,color:#fff
    style E fill:#2d8659,stroke:#1a5c3a,color:#fff
    style F fill:#2d8659,stroke:#1a5c3a,color:#fff
    style G fill:#7c5295,stroke:#563a6b,color:#fff
    style H fill:#7c5295,stroke:#563a6b,color:#fff
    style I fill:#7c5295,stroke:#563a6b,color:#fff
    style J fill:#b8762f,stroke:#8a5722,color:#fff
    style K fill:#2d8659,stroke:#1a5c3a,color:#fff
    style L fill:#2d8659,stroke:#1a5c3a,color:#fff
    style M fill:#7c5295,stroke:#563a6b,color:#fff
    style N fill:#7c5295,stroke:#563a6b,color:#fff
    style O fill:#2d6a9f,stroke:#1a4971,color:#fff

Installation

npm install @memberjunction/export-engine

Quick Start

Basic Excel Export

import { ExportEngine } from '@memberjunction/export-engine';

const data = [
  { name: 'Alice', age: 30, department: 'Engineering' },
  { name: 'Bob', age: 25, department: 'Marketing' },
  { name: 'Carol', age: 35, department: 'Engineering' }
];

const result = await ExportEngine.toExcel(data, {
  fileName: 'employees'
});
// result.data is a Uint8Array containing the .xlsx file

CSV Export

const result = await ExportEngine.toCSV(data, {
  fileName: 'employees',
  includeHeaders: true
});

JSON Export

const result = await ExportEngine.toJSON(data, {
  fileName: 'employees'
});

Format-Agnostic Export

const result = await ExportEngine.export(data, {
  format: 'excel', // or 'csv' or 'json'
  fileName: 'report'
});

Supported Formats

| Format | Extension | Multi-Sheet | Styling | Formulas | Images | Data Validation | |--------|-----------|-------------|---------|----------|--------|-----------------| | Excel | .xlsx | Yes | Yes | Yes | Yes | Yes | | CSV | .csv | No | No | No | No | No | | JSON | .json | No | No | No | No | No |

Row Sampling

The engine supports five sampling modes to control which rows are exported from large datasets:

%%{init: {'theme': 'base', 'themeVariables': { 'fontSize': '14px' }}}%%
flowchart LR
    subgraph Modes["Sampling Modes"]
        direction TB
        A["all -- Export every row"]
        B["top -- First N rows"]
        C["bottom -- Last N rows"]
        D["every-nth -- Every Nth row"]
        E["random -- Random N rows"]
    end

    style Modes fill:#2d6a9f,stroke:#1a4971,color:#fff
    style A fill:#2d8659,stroke:#1a5c3a,color:#fff
    style B fill:#2d8659,stroke:#1a5c3a,color:#fff
    style C fill:#2d8659,stroke:#1a5c3a,color:#fff
    style D fill:#2d8659,stroke:#1a5c3a,color:#fff
    style E fill:#2d8659,stroke:#1a5c3a,color:#fff

// Export only the top 50 rows
const result = await ExportEngine.toExcel(data, {
  sampling: { mode: 'top', count: 50 }
});

// Export every 10th row
const result = await ExportEngine.toExcel(data, {
  sampling: { mode: 'every-nth', interval: 10 }
});

// Export 100 random rows
const result = await ExportEngine.toExcel(data, {
  sampling: { mode: 'random', count: 100 }
});

Column Definitions

Columns can be explicitly defined for fine-grained control over headers, widths, data types, and formatting:

import { ExportEngine, ExportColumn } from '@memberjunction/export-engine';

const columns: ExportColumn[] = [
  { name: 'id', displayName: 'ID', width: 10, hidden: true },
  { name: 'name', displayName: 'Full Name', width: 25 },
  { name: 'salary', displayName: 'Annual Salary', dataType: 'currency', numberFormat: '$#,##0.00' },
  { name: 'startDate', displayName: 'Start Date', dataType: 'date', numberFormat: 'yyyy-mm-dd' },
  { name: 'active', displayName: 'Active', dataType: 'boolean' }
];

const result = await ExportEngine.toExcel(data, { columns });

If columns are not provided, the engine derives them automatically from the data keys (for objects) or generates generic names (for arrays).

Excel Styling

Simple Styling Options

The ExportStyleOptions interface provides convenient shortcuts for common formatting:

const result = await ExportEngine.toExcel(data, {
  styling: {
    boldHeaders: true,
    headerBackgroundColor: '4472C4',     // hex color without #
    headerTextColor: 'FFFFFF',
    alternatingRowColors: true,
    alternateRowColor: 'F5F5F5',
    freezeHeader: true,
    autoFilter: true
  }
});

Advanced Cell Styling

For full control, use the CellStyle interface which supports font, fill, border, alignment, and number format:

import { CellStyle, CommonStyles } from '@memberjunction/export-engine';

const headerStyle: CellStyle = {
  font: { bold: true, size: 12, color: 'FFFFFF' },
  fill: { pattern: 'solid', fgColor: '2D6A9F' },
  alignment: { horizontal: 'center', vertical: 'middle', wrapText: true },
  border: {
    bottom: { style: 'medium', color: '000000' }
  }
};

Pre-built Common Styles

The CommonStyles object provides ready-to-use styles:

| Style | Description | |-------|-------------| | CommonStyles.bold | Bold text | | CommonStyles.italic | Italic text | | CommonStyles.redText | Red-colored text | | CommonStyles.greenText | Green-colored text | | CommonStyles.blueText | Blue-colored text | | CommonStyles.yellowHighlight | Yellow background highlight | | CommonStyles.lightGrayBg | Light gray background | | CommonStyles.centered | Center-aligned text | | CommonStyles.rightAligned | Right-aligned text | | CommonStyles.wrapped | Text wrapping enabled | | CommonStyles.currency | Currency number format ($#,##0.00) | | CommonStyles.percentage | Percentage format (0.00%) | | CommonStyles.date | Date format (yyyy-mm-dd) | | CommonStyles.dateTime | DateTime format (yyyy-mm-dd hh:mm:ss) | | CommonStyles.thinBorder | Thin border on all sides | | CommonStyles.header | Bold white text on blue background, centered |

Merging Styles

Use mergeCellStyles to combine multiple style objects:

import { mergeCellStyles, CommonStyles } from '@memberjunction/export-engine';

const combinedStyle = mergeCellStyles(
  CommonStyles.bold,
  CommonStyles.centered,
  CommonStyles.thinBorder
);

Multi-Sheet Excel Workbooks

Create workbooks with multiple sheets, each with independent data, styling, and features:

import { ExportEngine, SheetDefinition, CommonStyles } from '@memberjunction/export-engine';

const sheets: SheetDefinition[] = [
  {
    name: 'Sales Data',
    data: salesRecords,
    headerStyle: CommonStyles.header,
    alternateRowStyle: CommonStyles.lightGrayBg,
    freeze: { row: 2 },           // Freeze header row
    autoFilter: true,
    tabColor: '2D8659'
  },
  {
    name: 'Summary',
    data: summaryRecords,
    formulas: [
      { cell: 'B10', formula: 'SUM(B2:B9)' },
      { cell: 'B11', formula: 'AVERAGE(B2:B9)' }
    ],
    mergedCells: [
      { startRow: 1, startColumn: 1, endRow: 1, endColumn: 3 }
    ]
  }
];

const result = await ExportEngine.toExcelMultiSheet(sheets, {
  fileName: 'quarterly-report',
  metadata: {
    author: 'Finance Team',
    title: 'Q1 2024 Report',
    company: 'Acme Corp'
  }
});

Sheet Features

Each SheetDefinition supports:

| Feature | Description | |---------|-------------| | columns / headers | Column definitions or simple header names | | headerStyle / dataStyle | Styling for header and data rows | | alternateRowStyle | Style applied to alternating rows | | rowStyles | Style overrides for specific rows | | cellStyles | Style overrides for specific cells or ranges | | formulas | Cell formulas (e.g., SUM, AVERAGE) | | conditionalFormatting | Rules-based formatting | | dataValidation | Input validation rules (dropdowns, ranges) | | mergedCells | Merged cell ranges | | images | Embedded PNG, JPEG, or GIF images | | freeze | Frozen rows and/or columns | | autoFilter | Auto-filter dropdowns on headers | | pageSetup | Print layout (paper size, orientation, margins) | | protection | Sheet protection with granular permissions | | tabColor | Worksheet tab color | | outlineProperties | Row/column grouping configuration |

Data Validation

Add dropdown lists, numeric range checks, and other validation rules to cells:

const sheet: SheetDefinition = {
  name: 'Data Entry',
  data: [],
  dataValidation: [
    {
      range: 'C2:C100',
      type: 'list',
      allowedValues: ['Active', 'Inactive', 'Pending'],
      showError: true,
      errorTitle: 'Invalid Status',
      errorMessage: 'Please select a valid status.'
    },
    {
      range: 'D2:D100',
      type: 'whole',
      operator: 'between',
      formula1: 1,
      formula2: 100,
      showError: true,
      errorStyle: 'stop',
      errorMessage: 'Enter a number between 1 and 100.'
    }
  ]
};

Conditional Formatting

Apply dynamic styles based on cell values:

const sheet: SheetDefinition = {
  name: 'Performance',
  data: performanceData,
  conditionalFormatting: [
    {
      range: 'B2:B50',
      type: 'cellIs',
      operator: 'greaterThan',
      value: 90,
      style: { font: { bold: true, color: '008000' } },
      priority: 1
    },
    {
      range: 'B2:B50',
      type: 'cellIs',
      operator: 'lessThan',
      value: 50,
      style: { font: { color: 'FF0000' }, fill: { pattern: 'solid', fgColor: 'FFE0E0' } },
      priority: 2
    }
  ]
};

Embedded Images

Embed images directly into worksheets:

const sheet: SheetDefinition = {
  name: 'Report',
  data: reportData,
  images: [
    {
      data: logoBase64String,       // base64-encoded image or Buffer
      type: 'png',
      position: { col: 1, row: 1 },
      size: { width: 200, height: 60 }
    }
  ]
};

CSV Options

Fine-tune CSV output with format-specific settings:

const result = await ExportEngine.toCSV(data, {
  csv: {
    delimiter: ';',               // Default: ','
    lineTerminator: '\n',         // Default: '\r\n'
    quoteChar: '"',               // Default: '"'
    alwaysQuote: false,
    includeBOM: true,             // UTF-8 BOM for Excel compatibility
    encoding: 'utf-8'
  }
});

The CSV exporter automatically handles escaping of delimiters, quotes, and newlines within field values, and includes a UTF-8 BOM by default for compatibility with Microsoft Excel.

JSON Options

Configure JSON serialization:

const result = await ExportEngine.toJSON(data, {
  json: {
    pretty: true,                 // Pretty-print output (default: true)
    indent: 4,                    // Indentation size (default: 2)
    includeMetadata: true,        // Wrap data in metadata envelope
    dateFormat: 'iso'             // Date format: 'iso', 'timestamp', or 'locale'
  }
});

Workbook Metadata

Set document properties on Excel workbooks:

const result = await ExportEngine.toExcel(data, {
  metadata: {
    author: 'Data Team',
    title: 'Monthly Report',
    subject: 'Sales Analysis',
    description: 'Aggregated sales data for January 2024',
    keywords: ['sales', 'monthly', 'report'],
    category: 'Reports',
    company: 'Acme Corp',
    manager: 'Jane Smith'
  }
});

Export Result

Every export returns an ExportResult object:

interface ExportResult {
  success: boolean;               // Whether the export succeeded
  data?: Uint8Array;              // Exported file content as a byte array
  mimeType?: string;              // MIME type (e.g., 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
  fileName?: string;              // Suggested filename with extension
  rowCount?: number;              // Total rows exported (across all sheets)
  columnCount?: number;           // Number of columns
  sheetCount?: number;            // Number of sheets (Excel only)
  sheetStats?: Array<{            // Per-sheet statistics
    name: string;
    rowCount: number;
    columnCount: number;
  }>;
  sizeBytes?: number;             // File size in bytes
  error?: string;                 // Error message if success is false
}

Always check result.success before using the data:

const result = await ExportEngine.toExcel(data);

if (result.success) {
  console.log(`Exported ${result.rowCount} rows to ${result.fileName} (${result.sizeBytes} bytes)`);
  // Use result.data (Uint8Array) to save, download, or transmit
} else {
  console.error('Export failed:', result.error);
}

Type System

%%{init: {'theme': 'base', 'themeVariables': { 'fontSize': '14px' }}}%%
classDiagram
    direction LR

    class ExportOptions {
        format: ExportFormat
        columns: ExportColumn[]
        includeHeaders: boolean
        sampling: SamplingOptions
        fileName: string
        styling: ExportStyleOptions
        metadata: WorkbookMetadata
        sheets: SheetDefinition[]
        csv: CSVOptions
        json: JSONOptions
    }

    class SheetDefinition {
        name: string
        data: ExportData
        columns: ExportColumn[]
        headerStyle: CellStyle
        formulas: CellFormula[]
        conditionalFormatting: ConditionalFormatRule[]
        dataValidation: DataValidationRule[]
        mergedCells: MergedCellRange[]
        images: EmbeddedImage[]
        freeze: FreezeConfig
        protection: SheetProtection
        pageSetup: PageSetup
    }

    class CellStyle {
        font: FontStyle
        fill: FillStyle
        border: CellBorder
        alignment: AlignmentStyle
        numFmt: string
    }

    class ExportResult {
        success: boolean
        data: Uint8Array
        mimeType: string
        fileName: string
        rowCount: number
        sheetCount: number
        sizeBytes: number
        error: string
    }

    ExportOptions --> SheetDefinition
    SheetDefinition --> CellStyle
    ExportOptions --> CellStyle

    style ExportOptions fill:#2d6a9f,stroke:#1a4971,color:#fff
    style SheetDefinition fill:#7c5295,stroke:#563a6b,color:#fff
    style CellStyle fill:#2d8659,stroke:#1a5c3a,color:#fff
    style ExportResult fill:#b8762f,stroke:#8a5722,color:#fff

Union Types

The package uses union types for all enumerated values:

| Type | Values | |------|--------| | ExportFormat | 'excel' | 'csv' | 'json' | | SamplingMode | 'all' | 'top' | 'bottom' | 'every-nth' | 'random' | | ColumnDataType | 'string' | 'number' | 'date' | 'boolean' | 'currency' | 'percentage' | | BorderLineStyle | 'thin' | 'medium' | 'thick' | 'dotted' | 'dashed' | 'double' | | HorizontalAlignment | 'left' | 'center' | 'right' | 'fill' | 'justify' | | VerticalAlignment | 'top' | 'middle' | 'bottom' | | FillPattern | 'none' | 'solid' | 'darkGray' | 'mediumGray' | 'lightGray' | ... |

Format Capabilities Query

Programmatically check what features a format supports:

const capabilities = ExportEngine.getFormatCapabilities('excel');
// { multiSheet: true, formulas: true, styling: true, images: true,
//   dataValidation: true, conditionalFormatting: true, protection: true }

const csvCapabilities = ExportEngine.getFormatCapabilities('csv');
// { multiSheet: false, formulas: false, styling: false, images: false,
//   dataValidation: false, conditionalFormatting: false, protection: false }

Consumers Within MemberJunction

This package is used by several MemberJunction components:

• @memberjunction/ng-export-service -- Angular service wrapping the engine for UI-driven exports
• @memberjunction/ng-entity-viewer -- Entity data grid export integration
• @memberjunction/ng-query-viewer -- Query result grid export integration
• @memberjunction/core-actions -- Server-side Excel file writing action
• @memberjunction/ng-dashboards -- Data explorer dashboard export features

Dependencies

| Package | Purpose | |---------|---------| | exceljs | Excel workbook generation (.xlsx format) |

CSV and JSON exporters have zero external dependencies.

Building

cd packages/MJExportEngine
npm run build