Skip to content

Source generator

Jump to bottom
Sveinung edited this page Nov 3, 2024 · 7 revisions

Introduction

The source generator is useful when you want to create rows in a worksheet based on a C# type (class, record or struct). With this approach, properties of the type are mapped to row cells, and code for this is auto-generated. This mapping code can be typed by hand, but there are some of the advantages of using the source generator:

  • Since the mapping code is generated automatically, changes to the C# type will immediately update the mapping code as well.
  • The generated mapping code can in some cases have better performance compared to manually typed mapping code. As one example, the generated code will use ArrayPool<T> to avoid some allocations.

Since the code is generated during compile-time, there is no additional penalty to runtime performance, which can otherwise be the case for e.g. code that use reflection.

Requirements

If you are using Visual Studio, you need to use Visual Studio 2022 version 17.9 or later. This is because the source generator depends on Roslyn APIs that were first made available in that version. The C# version used in your project must also be 8.0 or greater.

Getting started

In the examples we will create rows for this C# class:

namespace MyNamespace;

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
}

The source generator needs to know which C# type to generate code for, and it needs a class on which to put the generated code. Both are handled by declaring a context class.

The context class must:

  • Have the partial modifier.
  • Inherit WorksheetRowContext.
  • Have the WorksheetRow attribute with a type parameter.

Here is a context class for Person:

using SpreadCheetah.SourceGeneration;

namespace MyNamespace;

[WorksheetRow(typeof(Person))]
public partial class PersonContext : WorksheetRowContext;

Once this has been created, the source generator should automatically generate the implementation of PersonContext. PersonContext will contain a metadata type which contains information about the properties of Person. The implementation will be created in another file in the same project. If you're curious, you should be able to see into that file by navigating to declarations of the context class in your IDE.

We are now ready to create worksheet rows. To do so we will call AddAsRowAsync and pass an instance of Person as well as the metadata type generated by the source generator. Here is an example:

await using var spreadsheet = await Spreadsheet.CreateNewAsync(stream);
await spreadsheet.StartWorksheetAsync("Sheet 1");

var person = new Person
{
    FirstName = "Ola",
    LastName = "Nordmann"
};

// Here the metadata type is passed as the second argument.
// This will in turn use the code generated by the source generator.
await spreadsheet.AddAsRowAsync(person, PersonContext.Default.Person);

await spreadsheet.FinishAsync();

If we have multiple instances of Person, we can also use Spreadsheet.AddRangeAsRowsAsync which accepts and IEnumerable<T>.

Supported property types

The source generator only considers instance properties with public getters. These types (and their nullable variants) are supported:

  • string
  • int, long
  • decimal, double, float
  • bool
  • DateTime

Properties of these types will automatically become columns and row cells. Other types can be used if the property has a cell value converter.

Column header

A common use case is to first create a header row before creating rows with values. The source generator can be used to generate the header names as well. Spreadsheet has an AddHeaderRowAsync method that will add a row of header names to the worksheet. Similarly to AddAsRowAsync, the generated metadata type must be passed as an argument. An optional StyleId can also be passed as the second argument.

Here is an example:

await using var spreadsheet = await Spreadsheet.CreateNewAsync(stream);
await spreadsheet.StartWorksheetAsync("Sheet 1");

var style = new Style { Font = { Bold = true } };
var styleId = spreadsheet.AddStyle(style);

// This adds a row of header names generated by the source generator
await spreadsheet.AddHeaderRowAsync(PersonContext.Default.Person, styleId);

await spreadsheet.FinishAsync();

By default, the generated header names are based on the property names. You can use the ColumnHeader attribute to set your own header names.

Here is an example of that:

public class Person
{
    [ColumnHeader("First name")]
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int? Age { get; set; }
}

You can also reference resource (.resx) files. For example if we have MyResources.resx containing key Header_FirstName, then we can use it like this:

public class Person
{
    [ColumnHeader(typeof(MyResources), nameof(MyResources.Header_FirstName)]
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int? Age { get; set; }
}

If there are multiple translations for the resource, then similarly to a call to MyResources.Header_FirstName directly, the current culture will determine which translation gets used. Note that the resource file must have a public access modifier. This feature is not just limited to resource files, and can also similarly reference other public static properties.

Column width

The default width for columns is 8.43 (defined by Excel). A custom width can be set by using the ColumnWidth attribute on a property. The width must be between 0 and 255.

Here is an example:

public class Person
{
    [ColumnWidth(20)]
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int? Age { get; set; }
}

For ColumnWidth attributes to take effect, the metadata type created by the source generator must be used when starting the worksheet. There are two ways of doing that, one is to pass the metadata type to StartWorksheetAsync like in this example:

// Option 1: Pass the metadata type to StartWorksheetAsync.
await spreadsheet.StartWorksheetAsync("Sheet", PersonContext.Default.Person);

The other option is to first call CreateWorksheetOptions() on the metadata type. This will create a WorksheetOptions instance which have the column widths set from the ColumnWidth attributes. Then pass the instance to StartWorksheetAsync. This option allows setting other worksheet options if needed.

// Option 2: Create an instance of WorksheetOptions from the metadata type.
var options = PersonContext.Default.Person.CreateWorksheetOptions();

// Can now set additional options, for example frozen columns.
options.FrozenColumns = 1;

// Pass the instance to StartWorksheetAsync.
await spreadsheet.StartWorksheetAsync("Sheet", options);

Column order

By default, the column order is determined by the property order in the C# type. The column order can be changed by reordering the properties, or by using the ColumnOrder attribute.

The ColumnOrder attribute can be placed on one or more of the properties. The properties are then ordered by the attribute values in ascending order. In the following example, the column order becomes FirstName, Age, LastName:

public class Person
{
    [ColumnOrder(3)]
    public string LastName { get; set; }

    [ColumnOrder(1)]
    public string FirstName { get; set; }

    [ColumnOrder(2)]
    public int? Age { get; set; }
}

The column order values decides the ordering between the columns, and the values don't need to match the actual column numbers in the worksheet. Two properties can not have the same column order value, and attempting to have that will lead to a build error.

Properties without the attribute will get a column order value implicitly. The implicit value will be the lowest number greater than 0 that has not been used on any other column.

The following snippet shows an example which will create the same column order as in the previous example (FirstName, Age, LastName):

public class Person
{
    [ColumnOrder(10)]
    public string LastName { get; set; }

    [ColumnOrder(1)]
    public string FirstName { get; set; }

    public int? Age { get; set; }
}

Here the Age property implicitly gets a column order value of 2. Note that LastName will be in the third column, since column order values just decides the ordering, and not the actual column number.

Cell format

You can set a number format for cells by using the CellFormat attribute. The format can either be one of the standard number formats from the StandardNumberFormat enum, or you can set a custom format. Here is an example:

using SpreadCheetah.SourceGeneration;
using SpreadCheetah.Styling;

namespace MyNamespace;

public class Person
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    [CellFormat(StandardNumberFormat.LongDate)]
    public DateTime DateOfBirth { get; set; }

    [CellFormat("#.0#")]
    public decimal Age { get; set; }
}

Cell style

To have a more complex style on a cell when using the source generator, you need to create a named style on Spreadsheet, and then reference the style name with the CellStyle attribute. The named style must be created before the first call to AddAsRowAsync.

Here is an example of how to get the first name of Person in bold:

public class Person
{
    [CellStyle("My bold style")]
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int? Age { get; set; }
}
await using var spreadsheet = await Spreadsheet.CreateNewAsync(stream);
await spreadsheet.StartWorksheetAsync("Sheet 1");

var style = new Style { Font = { Bold = true } };

// This adds a named style
spreadsheet.AddStyle(style, "My bold style");

var person = new Person
{
    FirstName = "Ola",
    LastName = "Nordmann"
};

await spreadsheet.AddAsRowAsync(person, PersonContext.Default.Person);

await spreadsheet.FinishAsync();

Cell value converter

If you need more flexibility in how property values are mapped to cells, then you can create an implementation of CellValueConverter<T> which can be used by the generated code. CellValueConverter<T> is an abstract class and derived classes must implement a method that takes the property value as input, and returns a DataCell. To instruct the source generator to use your implementation, the property must have the CellValueConverter attribute with a typeof operator.

Here is an example where we will show N/A for a person when the age is null:

public class Person
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    [CellValueConverter(typeof(AgeConverter))]
    public int? Age { get; set; }
}

public class AgeConverter : CellValueConverter<int?>
{
    public override DataCell ConvertToDataCell(int? age)
    {
        return age is null
            ? new DataCell("N/A")
            : new DataCell(age.Value);
    }
}

Properties can also be of custom types when they have a declared CellValueConverter<T>. Here is an example where we are using a custom type for FirstName and LastName:

public record Name(string Value);

public class Person
{
    [CellValueConverter(typeof(NameConverter))]
    public Name FirstName { get; set; }

    [CellValueConverter(typeof(NameConverter))]
    public Name LastName { get; set; }

    public int? Age { get; set; }
}

public class NameConverter : CellValueConverter<Name>
{
    public override DataCell ConvertToDataCell(Name name) => new(name.Value);
}

Ignoring properties

Properties of supported types will automatically become columns and row cells. You can ignore properties with the ColumnIgnore attribute:

public class Person
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    [ColumnIgnore]
    public int? Age { get; set; }
}

Inheritance

By default, the source generator will only create columns from properties defined directly on the type passed to the WorksheetRow attribute. With the InheritColumns attribute, the base class will be taken into account as well.

In this example the Student class inherits from the Person class, and we use Student for the WorksheetRow attribute:

namespace MyNamespace;

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
}

[InheritColumns]
public class Student : Person
{
    public int StudentId { get; set; }
}

[WorksheetRow(typeof(Student))]
public partial class StudentContext : WorksheetRowContext;

The InheritColumns attribute will by default order the base class properties before derived class properties. In this example, the columns will be created in this order: FirstName, LastName, Age, StudentId.

The base class properties can instead be ordered after derived class properties by setting DefaultColumnOrder = InheritedColumnsOrder.InheritedColumnsLast on the InheritColumns attribute. The column order can also be customized further by using the ColumnOrder attribute.

Clone this wiki locally