Excelsior

Excelsior is a Excel spreadsheet generation library with a distinctive data-driven approach.

See Milestones for release notes.

Supported libraries

The architecture is designed to support multiple spreadsheet creation libraries.

Currently supported libraries include:

ClosedXML via the ExcelsiorClosedXml nuget
Aspose.Cells via the ExcelsiorAspose nuget
Syncfusion XlsIO via the ExcelsiorSyncfusion nuget

Usage

Model

Given an input class:

using Excelsior;

public class Employee
{
    [Column(Heading = "Employee ID", Order = 1)]
    public required int Id { get; init; }

    [Column(Heading = "Full Name", Order = 2)]
    public required string Name { get; init; }

    [Column(Heading = "Email Address", Order = 3)]
    public required string Email { get; init; }

    [Column(Heading = "Hire Date", Order = 4)]
    public Date? HireDate { get; init; }

    [Column(Heading = "Annual Salary", Order = 5)]
    public int Salary { get; init; }

    public bool IsActive { get; init; }

    public EmployeeStatus Status { get; init; }
}

^{snippet source | anchor}

[ColumnAttribute] is optional. If omitted:

Order is based on the order of the properties defined in the class. Order can be programmatically controlled
Heading text is based on the property names that is camel case split. Headings can be programmatically controlled

Builder

BookBuilder is the root entry point.

Once instantiated, the data for multiple sheets can be added.

var builder = new BookBuilder();

List<Employee> data =
[
    new()
    {
        Id = 1,
        Name = "John Doe",
        Email = "john@company.com",
        HireDate = new(2020, 1, 15),
        Salary = 75000,
        IsActive = true,
        Status = EmployeeStatus.FullTime
    },
    new()
    {
        Id = 2,
        Name = "Jane Smith",
        Email = "jane@company.com",
        HireDate = new(2019, 3, 22),
        Salary = 120000,
        IsActive = true,
        Status = EmployeeStatus.FullTime
    },
];
builder.AddSheet(data);

using var book = await builder.Build();

^{snippet source | anchor}

The above sample builds an instance of the Workbook for the target library:

Aspose.Cells.Workbook for Aspose
ClosedXML.Excel.IXLWorkbook for ClosedXml.
ExcelsiorSyncfusion.IDisposableBook for Syncfusion. Which implements both Syncfusion.XlsIO.IWorkbook, and System.IDisposable

Result:

Worksheet Name

Worksheet defaults to SheetN, when N is a counter. So the first sheet is Sheet1, the second is Sheet2, etc.

The name can be controlled by passing an explicit value.

var builder = new BookBuilder();
builder.AddSheet(employees, "Employee Report");

^{snippet source | anchor}

DataAnnotations Attributes

DisplayAttribute and DisplayNameAttribute from System.ComponentModel.DataAnnotations are supported.

DisplayAttribute and DisplayNameAttribute are support for scenarios where it is not convenient to reference Excelsior from that assembly.

public class Employee
{
    [Display(Name = "Employee ID", Order = 1)]
    public required int Id { get; init; }

    [Display(Name = "Full Name", Order = 2)]
    public required string Name { get; init; }

    [Display(Name = "Email Address", Order = 3)]
    public required string Email { get; init; }

    [Display(Name = "Hire Date", Order = 4)]
    public Date? HireDate { get; init; }

    [Display(Name = "Annual Salary", Order = 5)]
    public int Salary { get; init; }

    [DisplayName("IsActive")]
    public bool IsActive { get; init; }

    public EmployeeStatus Status { get; init; }
}

public enum EmployeeStatus
{
    [Display(Name = "Full Time")]
    FullTime,

    [Display(Name = "Part Time")]
    PartTime,

    [Display(Name = "Contract")]
    Contract,

    [Display(Name = "Terminated")]
    Terminated
}

^{snippet source | anchor}

Saving to a stream

To save to a stream use ToStream().

var builder = new BookBuilder();
builder.AddSheet(data);

var stream = new MemoryStream();
await builder.ToStream(stream);

^{snippet source | anchor}

Custom Headings

The heading text for a column can be overridden:

Fluent

var builder = new BookBuilder();
builder.AddSheet(employees)
    .Column(
        _ => _.Name,
        _ => _.Heading = "Employee Name");

^{snippet source | anchor}

ColumnAttribute

public class Employee
{
    [Column(Heading = "Employee Name")]
    public required string Name { get; init; }

DisplayNameAttribute

public class Employee
{
    [DisplayName("Employee Name")]
    public required string Name { get; init; }

DisplayAttribute

public class Employee
{
    [Display(Name = "Employee Name")]
    public required string Name { get; init; }

Result:

Order of precedence

Fluent
ColumnAttribute
DisplayAttribute
DisplayNameAttribute

Column Ordering

The column order can be overridden:

var builder = new BookBuilder();
builder.AddSheet(employees)
    .Column(_ => _.Email, _ => _.Order = 1)
    .Column(_ => _.Name, _ => _.Order = 2)
    .Column(_ => _.Salary, _ => _.Order = 3);

^{snippet source | anchor}

Result:

Heading Style

var builder = new BookBuilder(
    headingStyle: style =>
    {
        style.Font.Bold = true;
        style.Font.FontColor = XLColor.White;
        style.Fill.BackgroundColor = XLColor.DarkBlue;
    });
builder.AddSheet(data);

^{snippet source | anchor}

Result:

Global Style

var builder = new BookBuilder(
    globalStyle: style =>
    {
        style.Font.Bold = true;
        style.Font.FontColor = XLColor.White;
        style.Fill.BackgroundColor = XLColor.DarkBlue;
    });
builder.AddSheet(data);

^{snippet source | anchor}

Result:

Cell Styling

var builder = new BookBuilder();
builder.AddSheet(employees)
    .Column(
        _ => _.Salary,
        config =>
        {
            config.CellStyle = (style, employee, salary) =>
            {
                if (salary > 100000)
                {
                    style.Font.FontColor = XLColor.DarkGreen;
                    style.Font.Bold = true;
                }
            };
        })
    .Column(
        _ => _.IsActive,
        config =>
        {
            config.CellStyle = (style, employee, isActive) =>
            {
                var fill = style.Fill;
                if (isActive)
                {
                    fill.BackgroundColor = XLColor.LightGreen;
                }
                else
                {
                    fill.BackgroundColor = XLColor.LightPink;
                }
            };
        });

^{snippet source | anchor}

Result:

Render

var builder = new BookBuilder();
builder.AddSheet(employees)
    .Column(
        _ => _.Name,
        _ => _.Render = (employee, name) => name.ToUpper())
    .Column(
        _ => _.IsActive,
        _ => _.Render = (employee, active) => active ? "Active" : "Inactive")
    .Column(
        _ => _.HireDate,
        _ => _.Format = "yyyy-MM-dd");

^{snippet source | anchor}

Result:

Column Widths

Fluent

var builder = new BookBuilder();
builder.AddSheet(employees)
    .Column(_ => _.Name, _ => _.Width = 25)
    .Column(_ => _.Email, _ => _.Width = 30)
    .Column(_ => _.HireDate, _ => _.Width = 15);

^{snippet source | anchor}

ColumnAttribute

public class Employee
{
    [Column(Width = 25)]
    public required string Name { get; init; }

Result:

Order of precedence

Fluent
ColumnAttribute

Complex Types

For complex types, by default is to render via .ToString().

Models

public record Person(string Name, Address Address);

public record Address(int Number, string Street, string City, State State, ushort PostCode);

^{snippet source | anchor}

Build

var builder = new BookBuilder();

List<Person> data =
[
    new("John Doe",
        new Address(
            Number: 900,
            Street: "Victoria Square",
            City: "Adelaide",
            State: State.SA,
            PostCode: 5000)),
];
builder.AddSheet(data);

^{snippet source | anchor}

Result:

Custom render for Complex Types

[ModuleInitializer]
public static void Init() =>
    ValueRenderer.For<Address>(_ => $"{_.Number}, {_.Street}, {_.City}, {_.State}, {_.PostCode}");

^{snippet source | anchor}

var builder = new BookBuilder();

List<Person> data =
[
    new("John Doe",
        new Address(
            Number: 900,
            Street: "Victoria Square",
            City: "Adelaide",
            State: State.SA,
            PostCode: 5000)),
];
builder.AddSheet(data);

^{snippet source | anchor}

Result:

Whitespace

By default whitespace is trimmed

var builder = new BookBuilder();

List<Employee> data =
[
    new()
    {
        Id = 1,
        Name = "    John Doe   ",
        Email = "    john@company.com    ",
    }
];
builder.AddSheet(data);

using var book = await builder.Build();

^{snippet source | anchor}

Result

Disable whitespace trimming

[ModuleInitializer]
public static void DisableTrimWhitespace() =>
    ValueRenderer.DisableWhitespaceTrimming();

^{snippet source | anchor}

Result

Enumerable string properties

Properties that are castable to an IEnumerable<string> will automatically be rendered as a point form list.

Model

public record Person(string Name, IEnumerable<string> PhoneNumbers);

^{snippet source | anchor}

Render

List<Person> data =
[
    new("John Doe",
        PhoneNumbers:
        [
            "+1 3057380950",
            "+1 5056169368",
            "+1 8634446859"
        ]),
];

var builder = new BookBuilder();
builder.AddSheet(data);

^{snippet source | anchor}

Result

Binding Model

The recommended approach is to use a specific type for binding.

This will make configuration and rendering simpler. It will often also result in better performance. The reason being that the projection into the binding type can be done by the database via an ORM. This will result in a faster query response and less data being transferred from the database.

Take for example of rendering employees to a sheet. A potential model could be Company, Employee, and Address.

public class Address
{
    public required int StreetNumber { get; init; }
    public required string Street { get; init; }
}

public class Company
{
    public required int Id { get; init; }
    public required string Name { get; init; }
}

public class Employee
{
    public required int Id { get; init; }
    public required string Name { get; init; }
    public required Company Company { get; init; }
    public required Address Address { get; init; }
    public required string Email { get; init; }
}

^{snippet source | anchor}

Then a custom binding type can be used.

public class EmployeeBindingModel
{
    public required string Name { get; init; }
    public required string Email { get; init; }
    public required string Company { get; init; }
    public required string Address { get; init; }
}

^{snippet source | anchor}

The custom binding type can be queried and rendered into a sheet.

var employees = dbContext
    .Employees
    .Select(_ =>
        new EmployeeBindingModel
        {
            Name = _.Name,
            Email = _.Email,
            Company = _.Company.Name,
            Address = $"{_.Address.StreetNumber} {_.Address.Street}",
        });
var builder = new BookBuilder();
builder.AddSheet(employees);

^{snippet source | anchor}

ColumnAttribute

ColumnAttribute allows customization of rendering at the model level.

It is intended as the preferred approach over usage of DisplayAttribute and DisplayNameAttribute.

DisplayAttribute and DisplayNameAttribute are support for scenarios where it is not convenient to reference Excelsior from that assembly.

ColumnAttribute definition

namespace Excelsior;

[AttributeUsage(AttributeTargets.Property | AttributeTargets.Parameter)]
public sealed class ColumnAttribute :
    Attribute
{
    public string? Heading { get; set; }
    public int Order { get; set; } = -1;
    public int Width { get; set; } = -1;
    public string? Format { get; set; }
    public string? NullDisplay { get; set; }
    public bool IsHtml { get; set; }
}

^{snippet source | anchor}

Usage

public class Employee
{
    [Column(Heading = "Employee ID", Order = 1, Format = "0000")]
    public required int Id { get; init; }

    [Column(Heading = "Full Name", Order = 2, Width = 20)]
    public required string Name { get; init; }

    [Column(Heading = "Email Address", Width = 30)]
    public required string Email { get; init; }

    [Column(Heading = "Hire Date", Order = 3, NullDisplay = "unknown")]
    public Date? HireDate { get; init; }
}

^{snippet source | anchor}

var builder = new BookBuilder();

List<Employee> data =
[
    new()
    {
        Id = 1,
        Name = "John Doe",
        Email = "john@company.com",
        HireDate = new(2020, 1, 15),
    },
    new()
    {
        Id = 2,
        Name = "Jane Smith",
        Email = "jane@company.com",
        HireDate = null,
    }
];

builder.AddSheet(data);

^{snippet source | anchor}

Result

ValueRenderer.ForEnums

ValueRenderer.ForEnums can be used to control the rendering for all enums

Config in a ModuleInitializer

[ModuleInitializer]
public static void CustomEnumRender() =>
    ValueRenderer.ForEnums(_ => _.ToString().ToUpper());

^{snippet source | anchor}

Example use

var builder = new BookBuilder();

List<Car> data =
[
    new()
    {
        Manufacturer = Manufacturer.BuildYourDream,
        Color = Color.AntiqueWhite,
    }
];
builder.AddSheet(data);

^{snippet source | anchor}

Result

Date formats

DateTime and DateOnly are passed directly in to the respective library.

Excel is directed (using a format string) to render the value using the following:

yyyy-MM-dd HH:mm:ss for DateTimes
yyyy-MM-dd for DateOnlys

Excel has no direct support for DateTimeOffset. So DateTimeOffsets are stored as strings using the yyyy-MM-dd HH:mm:ss z format and CultureInfo.InvariantCulture

Custom Date formats

Date formats can be customized:

[ModuleInitializer]
public static void CustomDateFormats()
{
    ValueRenderer.DefaultDateFormat = "yyyy/MM/dd" ;
    ValueRenderer.DefaultDateTimeFormat = "yyyy/MM/dd HH:mm:ss" ;
    ValueRenderer.DefaultDateTimeOffsetFormat = "yyyy/MM/dd HH:mm:ss z" ;
}

^{snippet source | anchor}

Result

Splitting

SplitAttribute can be used push properties up.

public record Person(
    string Name,
    [Split] Address Address);

public record Address(int StreetNumber, string Street, string City, State State, ushort PostCode);

^{snippet source | anchor}

Result

UseHierachyForName

SplitAttribute.UseHierachyForName can be used to prefix members with the parent property name.

public record Person(
    string Name,
    [Split(UseHierachyForName = true)]
    Address Address);

public record Address(int Number, string Street, string City, State State, ushort PostCode);

^{snippet source | anchor}

Result

Icon

Grim Fandango from Papirus Icons.

The Excelsior Line is a travel package sold by Manuel Calavera in the Lucas Arts game "Grim Fandango". The package consists of nothing more than a walking stick with a compass in the handle.

Name		Name	Last commit message	Last commit date
Latest commit History 331 Commits
.github		.github
docs		docs
src		src
.gitattributes		.gitattributes
.gitignore		.gitignore
code_of_conduct.md		code_of_conduct.md
license.txt		license.txt
readme.md		readme.md

Uh oh!

License

SimonCropp/Excelsior

Folders and files

Latest commit

History

Repository files navigation

Excelsior

Supported libraries

Usage

Model

Builder

Result:

Worksheet Name

DataAnnotations Attributes

Saving to a stream

Custom Headings

Fluent

ColumnAttribute

DisplayNameAttribute

DisplayAttribute

Result:

Order of precedence

Column Ordering

Result:

Heading Style

Result:

Global Style

Result:

Cell Styling

Result:

Render

Result:

Column Widths

Fluent

ColumnAttribute

Result:

Order of precedence

Complex Types

Models

Build

Result:

Custom render for Complex Types

Result:

Whitespace

Result

Disable whitespace trimming

Result

Enumerable string properties

Model

Render

Result

Binding Model

ColumnAttribute

ColumnAttribute definition

Usage

Result

ValueRenderer.ForEnums

Config in a ModuleInitializer

Example use

Result

Date formats

Custom Date formats

Result

Splitting

Result

UseHierachyForName

Result

Icon

About

Resources

License

Code of conduct

Uh oh!

Stars

Watchers

Forks

Sponsor this project

Uh oh!

Uh oh!