Excel Generation Library

A TypeScript library for generating Excel (.xlsx) files with custom formatting, headers, subheaders, body data, and footers.

Installation

npm install @jjr-group/xlsx
# or
yarn add @jjr-group/xlsx
# or
pnpm add @jjr-group/xlsx

Quick Start

import { fileBuilder, IHeader, IBody, IFooter, TYPECELL } from 'excel-generation-library';

// Create headers
const headers: IHeader[] = [
  {
    key: "title",
    type: TYPECELL.STRING,
    value: "SALES REPORT 2024",
    rowHeight: 40,
    mergeCell: true
  }
];

// Create subheaders with merged cells
const subHeaders: IHeader[] = [
  {
    key: "company",
    type: TYPECELL.STRING,
    value: "COMPANY",
    colWidth: 30
  },
  {
    key: "q1",
    type: TYPECELL.STRING,
    value: "Q1 2024",
    mergeCell: true,
    mergeTo: 3,
    childrens: [
      { key: "sales", type: TYPECELL.STRING, value: "SALES", colWidth: 15 },
      { key: "costs", type: TYPECELL.STRING, value: "COSTS", colWidth: 15 },
      { key: "profit", type: TYPECELL.STRING, value: "PROFIT", colWidth: 15 }
    ]
  }
];

// Create body data
const body: IBody[] = [
  {
    key: "company1",
    type: TYPECELL.STRING,
    value: "Company A",
    header: "company",
    jump: true,
    styles: { stripped: true },
    childrens: [
      {
        key: "q1_sales",
        type: TYPECELL.NUMBER,
        value: 150000,
        header: "sales",
        mainHeaderKey: "q1",
        styles: { stripped: true }
      }
    ]
  }
];

// Create footers
const footers: IFooter[] = [
  {
    key: "total",
    type: TYPECELL.STRING,
    value: "TOTAL",
    header: "company",
    mergeCell: true,
    mergeTo: 1
  }
];

// Generate Excel file
const generateReport = async () => {
  const worksheets = [{
    name: "Sales Report",
    tables: [{
      headers,
      subHeaders,
      body,
      footers,
      descriptionValues: [{
        title: "GENERATED ON",
        values: new Date().toLocaleDateString(),
        styles: { fontStyle: "bold", fontSize: 12 }
      }]
    }]
  }];

  await fileBuilder(worksheets, "Sales_Report_2024.xlsx");
};

// Use in button click
document.getElementById("download")?.addEventListener("click", generateReport);

API Reference

Core Functions

fileBuilder(worksheets, filename)

Generates the final Excel file.

Parameters:

• worksheets: IWorksheets[] - Array of worksheet objects
• filename: string - Output filename

Returns: Promise<void>

Interfaces

IHeader

interface IHeader {
  key: string;                    // Unique identifier
  type: TYPECELL;                // Cell type
  value: string | number;        // Display value
  colWidth?: number;             // Column width
  rowHeight?: number;            // Row height
  mergeCell?: boolean;           // Whether to merge cells
  mergeTo?: number;              // Number of columns to merge
  childrens?: IHeader[];         // Child subheaders
  mainHeaderKey?: string;        // Parent header key
  styles?: IStyles;              // Custom styles
}

IBody

interface IBody {
  key: string;                    // Unique identifier
  type: TYPECELL;                // Cell type
  value: string | number;        // Display value
  header: string;                // Corresponding header key
  jump?: boolean;                // Whether to jump to next row
  childrens?: IBody[];           // Child data
  mainHeaderKey?: string;        // Parent header key
  styles?: IStyles;              // Custom styles
  numberFormat?: NUMBERFORMATS;  // Number format
  link?: string;                 // Link (for TYPECELL.LINK)
}

IFooter

interface IFooter {
  key: string;                    // Unique identifier
  type: TYPECELL;                // Cell type
  value: string | number;        // Display value
  header: string;                // Corresponding header key
  mergeCell?: boolean;           // Whether to merge cells
  mergeTo?: number;              // Number of columns to merge
  styles?: IStyles;              // Custom styles
  numberFormat?: NUMBERFORMATS;  // Number format
  link?: string;                 // Link (for TYPECELL.LINK)
}

IStyles

interface IStyles {
  fontStyle?: "bold" | "italic" | "normal";
  fontSize?: number;
  fontColor?: string;
  backgroundColor?: string;
  stripped?: boolean;            // For alternating rows
  alignment?: "left" | "center" | "right";
  border?: {
    style?: "thin" | "medium" | "thick";
    color?: string;
  };
}

Enums

TYPECELL

enum TYPECELL {
  STRING = "string",
  NUMBER = "number",
  LINK = "link"
}

Features

• ✅ Multiple worksheet support
• ✅ Header and subheader merging
• ✅ Custom cell formatting
• ✅ Alternating row styles
• ✅ Number formatting
• ✅ Hyperlinks
• ✅ Custom borders and colors
• ✅ Description values for metadata
• ✅ TypeScript support

Examples

Basic Table

const table = {
  headers: [{
    key: "title",
    type: TYPECELL.STRING,
    value: "Simple Table",
    mergeCell: true
  }],
  subHeaders: [{
    key: "name",
    type: TYPECELL.STRING,
    value: "Name",
    colWidth: 20
  }],
  body: [{
    key: "row1",
    type: TYPECELL.STRING,
    value: "John Doe",
    header: "name"
  }],
  footers: []
};

Complex Report with Merged Cells

const complexTable = {
  headers: [{
    key: "title",
    type: TYPECELL.STRING,
    value: "Financial Report",
    rowHeight: 40,
    mergeCell: true
  }],
  subHeaders: [
    {
      key: "company",
      type: TYPECELL.STRING,
      value: "Company",
      colWidth: 30
    },
    {
      key: "q1",
      type: TYPECELL.STRING,
      value: "Q1 2024",
      mergeCell: true,
      mergeTo: 3,
      childrens: [
        { key: "revenue", type: TYPECELL.STRING, value: "Revenue" },
        { key: "expenses", type: TYPECELL.STRING, value: "Expenses" },
        { key: "profit", type: TYPECELL.STRING, value: "Profit" }
      ]
    }
  ],
  body: [{
    key: "company1",
    type: TYPECELL.STRING,
    value: "Tech Corp",
    header: "company",
    jump: true,
    childrens: [
      {
        key: "q1_revenue",
        type: TYPECELL.NUMBER,
        value: 1000000,
        header: "revenue",
        mainHeaderKey: "q1",
        numberFormat: "#,##0"
      }
    ]
  }]
};

Browser Support

• Chrome 60+
• Firefox 55+
• Safari 12+
• Edge 79+

License

MIT

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request