@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 {
        <>
        #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
Copy

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
Copy

Installation

npm install @memberjunction/export-engine
Copy

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
Copy

CSV Export

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

JSON Export

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

Format-Agnostic Export

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

Supported Formats

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
Copy

// 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 }
});
Copy

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 });
Copy

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
  }
});
Copy

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' }
  }
};
Copy

Pre-built Common Styles

The CommonStyles object provides ready-to-use styles:

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
);
Copy

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'
  }
});
Copy

Sheet Features

Each SheetDefinition supports:

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.'
    }
  ]
};
Copy

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
    }
  ]
};
Copy

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 }
    }
  ]
};
Copy

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'
  }
});
Copy

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'
  }
});
Copy

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'
  }
});
Copy

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
}
Copy

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);
}
Copy

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
Copy

Union Types

The package uses union types for all enumerated values:

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 }
Copy

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

CSV and JSON exporters have zero external dependencies.

Building

cd packages/MJExportEngine
npm run build
Copy