@composize/excel

@composize/excel is a DSL for creating Excel files through a composable, declarative API.

It provides an intuitive interface to create complex Excel workbooks without directly dealing with the complexities of the underlying ExcelJS library.

Key Features

• Declarative Syntax: Create Excel files using a simple, readable syntax that mirrors the structure of the resulting document.
• Hierarchical Structure: The DSL follows a hierarchical structure that reflects Excel's document model, making it easy to understand and maintain.
• Composable Functions: Each level of the hierarchy is represented by a function that accepts callback functions to define its children, allowing for a natural nesting structure.
• Cell Merging: Support for merging cells both horizontally and vertically.
• Automatic Column Widths: Automatically adjusts column widths based on the content of each cell.

Installation

npm install @composize/excel
Copy

Basic Example

Here's a simple example of how to create an Excel file using the DSL:

import { cell, row, workbook } from '@composize/excel';

workbook(() => {
  row(() => {
    cell('title1');
    cell('title2');
  });
  row(() => {
    cell('value1');
    cell('value2');
  });
});
Copy

Core Concepts

The @composize/excel DSL follows a hierarchical structure that mirrors Excel's document model, with composable functions representing each level of the hierarchy.

graph LR
    workbook --> worksheet
    worksheet --> row
    row --> cell
    row --> borderedCell
    row --> centeredCell
    advance

The @composize/excel DSL uses a functional composition pattern where parent elements accept callback functions that define their children. This creates a natural nesting structure that makes the code's visual layout reflect the structure of the resulting Excel document.

API Reference

The @composize/excel DSL includes the following key features:

CellOptions

The cell() functions accept an optional options parameter that allows for cell styling and merging:

Basic Usage

The simplest way to create an Excel file is by nesting function calls that mirror the structure of your desired spreadsheet:

import { workbook, row, cell } from '@composize/excel';

const book = workbook(() => {
  row(() => {
    cell('Title 1');
    cell('Title 2');
  });
  row(() => {
    cell('Value 1');
    cell('Value 2');
  });
});

// Save the workbook to a file
book.xlsx.writeFile('./example.xlsx');
Copy

This creates a simple 2x2 table with headers and values.

Worksheets

By default, if no worksheet is explicitly created, the DSL automatically creates a worksheet named "Sheet1":

const book = workbook(() => {
  row(() => {
    cell('Data without explicit worksheet');
  });
});
Copy

You can explicitly create named worksheets using the worksheet() function:

const book = workbook(() => {
  worksheet('First Sheet', () => {
    row(() => {
      cell('Data in first sheet');
    });
  });

  worksheet('Second Sheet', () => {
    row(() => {
      cell('Data in second sheet');
    });
  });
});
Copy

Cell Merging

The @composize/excel DSL supports merging cells both horizontally (using colSpan) and vertically (using rowSpan):

Horizontal Merging (Column Span)

To merge cells horizontally, use the colSpan option:

workbook(() => {
  row(() => {
    cell('Wide Header', { colSpan: 3 });
  });
  row(() => {
    cell('Data 1');
    cell('Data 2');
    cell('Data 3');
  });
});
Copy

This creates a header cell that spans across three columns:

+--------+--------+--------+
|       Wide Header        |
+--------+--------+--------+
| Data 1 | Data 2 | Data 3 |
+--------+--------+--------+
Copy

Vertical Merging (Row Span)

To merge cells vertically, use the rowSpan option:

workbook(() => {
  row(() => {
    cell('Tall Cell', { rowSpan: 2 });
    cell('Top Cell');
  });
  row(() => {
    // No need to set the first cell, it's already covered by the merged cell
    cell('Bottom Cell');
  });
});
Copy

This creates a cell that spans two rows in the first column:

+--------+--------+-------------+
|                 | Top Cell    |
|    Tall Cell    +-------------+
|                 | Bottom Cell |
+--------+--------+-------------+
Copy

Combined Merging

You can combine both horizontal and vertical merging:

workbook(() => {
  row(() => {
    cell('Header 1', { rowSpan: 2, colSpan: 2 });
    cell('Header 2');
  });
  row(() => {
    cell('Header 3');
  });
  row(() => {
    cell('Data 1');
    cell('Data 2', { rowSpan: 2, colSpan: 2 });
  })
  row(() => {
    cell('Data 3');
  })
});
Copy

This creates a complex merged structure:

+--------+--------+----------+
|                 | Header 2 |
|     Header 1    +----------+
|                 | Header 3 |
+--------+--------+----------+
| Data 1 |                   |
+--------+      Data 2       +
| Data 3 |                   |
+--------+-------------------+
Copy

Advanced

The advance() function allows you to move the current position by a specified number of rows or columns. By default, it advances by one row or column:

row(() => {
  cell('Cell 1');
  advance();   // → Move back one column
  cell('Cell 3');
});
advance();     // ↓ Move down two rows
row(() => {
  cell('Cell 1');
  advance(2);  // → Move back one column
  cell('Cell 4');
  advance(-3); // ← Move forward three column
  cell('Cell 2');
});
Copy

This creates the following layout:

+--------+--------+--------+--------+
| Cell 1 |        | Cell 3 |        |
+--------+--------+--------+--------+
|        |        |        |        |
+--------+--------+--------+--------+
| Cell 1 | Cell 2 |        | Cell 4 |
+--------+--------+--------+--------+
Copy

Cell Styling

Basic Styling

The cell() function accepts styling options from ExcelJS's Cell object:

cell('Styled Text', {
  font: { bold: true, size: 14 },
  alignment: { vertical: 'middle', horizontal: 'center' },
});
Copy

Fill

You can apply background fills to cells using the fill option. For example, to apply a solid fill:

cell('Solid Fill', {
  fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: Color.Red } }
});
Copy

You can create fill patterns using the fillSolid() helper function:

cell('Solid Fill', {
  fill: fillSolid(Color.Red)
});
Copy

Bordered

For convenience, the DSL provides a borderedCell() function that applies thin borders to all sides of a cell:

row(() => {
  borderedCell('Bordered Content');
  borderedCell('Custom Format', { numFmt: '0.00' });
});
Copy

The borderedCell() function accepts the same options as cell(), but automatically adds thin borders.

Centered

For convenience, the DSL also provides a centeredCell() function, which builds on top of borderedCell() and applies centered alignment to the cell.

row(() => {
  centeredCell('Centered Text');
  centeredCell('Custom Format', { numFmt: '0.00' });
});
Copy

The centeredCell() function accepts the same options as borderedCell(), but automatically adds centered alignment.

Protected

You can protect cells from editing using the protection option:

cell('Protected Cell', {
  protection: { locked: true }
});
Copy

Automatic Column Widths

The @composize/excel DSL automatically adjusts column widths based on the content of each cell. For merged cells, auto-fitting enables text wrapping instead of increasing width.

Constants

Color

Predefined ARGB color values for Excel styling:

FontSize

Standardized font sizes organized by usage context:

Complete Example

Here's a more complex example that demonstrates many features of the @composize/excel DSL:

const headers = ['Category', 'Q1', 'Q2', 'Q3'];
const data = [
  { category: 'Revenue', q1: 10000, q2: 12000, q3: 15000 },
  { category: 'Expenses', q1: 8000, q2: 8500, q3: 9000 },
  { category: 'Profit', q1: 2000, q2: 3500, q3: 6000 }
];
const book = workbook(() => {
  worksheet('Report', () => {
    // Header row with merged title
    row(() => {
      centeredCell('Quarterly Report', {
        colSpan: headers.length,
        font: { bold: true, size: FontSize.TitleSmall }
      });
    });
    // Column headers
    row(() => {
      for (const header of headers) {
        borderedCell(header, { font: { bold: true } });
      }
    });
    // Data rows
    for (const item of data) {
      row(() => {
        borderedCell(item.category);
        borderedCell(item.q1, { numFmt: '$#,##0' });
        borderedCell(item.q2, { numFmt: '$#,##0' });
        borderedCell(item.q3, { numFmt: '$#,##0' });
      });
    }
  });
});

book.xlsx.writeFile('quarterly_report.xlsx');
Copy

Changelog

Learn about the latest improvements.

Support

Do you love ✨ composize ✨ ? Star for this project!

Special thanks

Thanks to JetBrains for supporting us free open source licenses.

License

MIT