zExcelGenerator 0.2.1

📘 zExcelGenerator

A lightweight fluent API for generating Excel reports using ClosedXML.
Designed for clean, expressive, multi-sheet Excel report generation with support for simple and advanced column mappings.

Table of Contents

• Features
• Installation
• Quick Start
• Usage Examples
  • Simple Columns
  • Multiple Worksheets
  • Multiple Columns
  • Two Columns Per Field
  • Template With Named Ranges
  • Async & CancellationToken
• Returning Excel from ASP.NET Core
• Testing
• Troubleshooting
• License

Features

• Fluent API for building Excel reports
• Multi-worksheet support
• Simple and advanced column mappings
• Multiple-column and paired-column expansions
• Works with ILogger<T>
• Supports CancellationToken
• Compatible with netstandard2.1

Installation

dotnet add package zExcelGenerator

Quick Start

var logger = new NullLogger<ExcelGenerator>();
var generator = new ExcelGenerator(logger);

byte[] excelBytes = generator.GenerateExcel(workbook =>
{
    workbook.AddWorksheet("People", people, ws => ws
        .Column("Name", p => p.Name, 1)
        .Column("Age",  p => p.Age,  2)
        .Column("Birth Date", p => p.BirthDate, 3, "dd/MM/yyyy")
    );
});

Usage Examples

Simple Columns

workbook.AddWorksheet("Products", products, ws => ws
    .Column("Id",   p => p.Id,   1)
    .Column("Name", p => p.Name, 2)
    .Column("Price", p => p.Price, 3, "#,##0.00")
);

Output

Multiple Worksheets

workbook
    .AddWorksheet("People", people, ws => ws
        .Column("Name", p => p.Name, 1)
        .Column("Age",  p => p.Age,  2))
    .AddWorksheet("Orders", orders, ws => ws
        .Column("Order #", o => o.OrderNumber, 1)
        .Column("Total",   o => o.TotalAmount, 2, "#,##0.00"));

Multiple Columns

workbook.AddWorksheet("Survey", surveyResults, ws => ws
    .Column("Name", r => r.Name, 1)
    .MultipleColumns(
        description:  "Score",
        selector:     r => r.Scores.Cast<object>(),
        totalColumns: 3,
        order: 2,
        headerSuffix: new[] { "Q1", "Q2", "Q3" },
        format: "0")
);

Output Example

Two Columns Per Field

workbook.AddWorksheet("Monthly", monthlyData, ws => ws
    .TwoColumnsPerField(
        firstDescription:  "Planned",
        secondDescription: "Actual",
        firstSelector:     d => d.Planned.Cast<object>(),
        secondSelector:    d => d.Actual.Cast<object>(),
        totalColumns:      12,
        order: 1,
        firstHeaderSuffix:  new[] { "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" },
        secondHeaderSuffix: new[] { "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" },
        firstFormat:  "#,##0.00",
        secondFormat: "#,##0.00")
);

Template With Named Ranges

Use an existing .xlsx template and fill named ranges.

var invoice = new Invoice
{
    CustomerName = "Acme Corp",
    Lines = new List<InvoiceLine>
    {
        new InvoiceLine { Sku = "A-01", Qty = 2, Price = 10.5m },
        new InvoiceLine { Sku = "B-02", Qty = 1, Price = 5m }
    }
};

byte[] excelBytes = generator.GenerateExcelFromTemplate(tpl => tpl
    .UseTemplatePath("Templates/Invoice.xlsx")
    .SetData(invoice)
    .ForWorksheet("Cover", ws => ws
        .NamedRange("CustomerName", x => x.CustomerName)
        .NamedRange("InvoiceDate", x => x.Date, format: "dd/MM/yyyy"))
    .ForWorksheet("Lines", ws => ws
        .NamedRangeTable("LinesHeader", x => x.Lines, table => table
            .Column("Sku", l => l.Sku, 1)
            .Column("Qty", l => l.Qty, 2, format: "0")
            .Column("Price", l => l.Price, 3, format: "#,##0.00"),
            headerRowIsNamedRange: true,
            writeHeaders: false,
            insertRows: true))
    .AddWorksheet("Summary", invoice.Lines, ws => ws
        .Column("Sku", l => l.Sku, 1)
        .Column("Qty", l => l.Qty, 2, format: "0"))
);

Notes:

• Named ranges must exist in the template or an exception is thrown.
• NamedRangeTable anchors at the first cell of the named range.
• Use ForWorksheet to scope mappings to a specific sheet.
• AddWorksheet lets you append new worksheets on top of the template.
• If insertRows is true and there is more than one item, rows are inserted below the anchor row.
• Multi-range named ranges are supported; the same value/table is applied to each area.

Defining Named Ranges in Excel

1. Select the cell or range.
2. Go to Formulas → Name Manager → New.
3. Set the name (e.g., CustomerName) and scope (Workbook or Worksheet).

Sample project:

• src/samples/zExcelGenerator.Samples/README.md

Async & CancellationToken

var cts = new CancellationTokenSource(TimeSpan.FromSeconds(2));

byte[] excelBytes = await generator.GenerateExcelAsync(workbook =>
{
    workbook.AddWorksheet("People", people, ws => ws
        .Column("Name", p => p.Name, 1)
        .Column("Age",  p => p.Age,  2)
    );
}, cts.Token);

Returning Excel from ASP.NET Core

return File(
    excelBytes,
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "report.xlsx"
);

Testing

Run the unit tests:

dotnet test tests/zExcelGenerator.Tests/zExcelGenerator.Tests.csproj

Generate coverage (requires coverlet.collector):

dotnet test tests/zExcelGenerator.Tests/zExcelGenerator.Tests.csproj --collect:"XPlat Code Coverage"

Troubleshooting

• Dates stored as text: provide a date format (e.g. format: "dd/MM/yyyy") so ClosedXML stores a real date value.
• Empty or missing headers: make sure each worksheet has at least one column configured.
• Large reports: prefer fewer columns and avoid excessive string conversions inside selectors.

License

This project is licensed under the MIT License.
See the LICENSE file for details.