WebApiContrib.Formatting.Xlsx

Generate formatted, customisable Excel documents straight from ASP.NET Web API.

Features

Control over column names, order, number format and visibility of properties in generated Excel documents via custom attribute.
Lots of formatting options:
- freeze header rows;
- add auto-filter;
- customize header row and cell styles; and
- autofit column widths to data.
Decent (and improving) unit test coverage.

Limitations

XlsxMediaTypeFormatter does not do deep serialisation of complex (i.e. nested) types, nor does it yet deserialise data from Excel. Both of these are major priorities; see the project milestones for a rough schedule.

Setting it up

Grab WebApiContrib.Formatting.Xlsx from NuGet.
Add the XlsxMediaTypeFormatter to the formatter collection in your Web API configuration. This will look something like:
```
config.Formatters.Add(new XlsxMediaTypeFormatter()); // Where config = System.Web.Http.HttpConfiguration.
```

You are now good to go forth and serialise; however, you may find the generated Excel output a tad boring. Enter the advanced formatter instantiation options!

Advanced setup options

The XlsxMediaTypeFormatter provides a number of options for customising the appearance of generated Excel files.

autoFit: Default true.; Fit columns to the maximum width of the data they contain.
autoFilter: Default false.; Set the column headers up as an auto-filter on the data.
freezeHeader: Default false.; Split the top row of cells so that the column headers stay at the top of the window while scrolling through data.
headerHeight: Default 0 (not set).; Set the height of the column header row.
cellStyle: Default null.; Can take an Action<OfficeOpenXml.Style.ExcelStyle> that specifies visual formatting options (such as fonts and borders) for all cells.
headerStyle: Default null.; Can take an Action<OfficeOpenXml.Style.ExcelStyle> that specifies visual formatting options (such as fonts and borders) for only the column header cells.

Advanced setup example

var formatter = new XlsxMediaTypeFormatter(
    autoFilter: true,
    freezeHeader: true,
    headerHeight: 25f,
    cellStyle: (ExcelStyle s) => {
        s.Font.SetFromFont(new Font("Segoe UI", 13f, FontStyle.Regular));
    },
    headerStyle: (ExcelStyle s) => {
        s.Fill.PatternType = ExcelFillStyle.Solid;
        s.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 114, 51));
        s.Font.Color.SetColor(Color.White);
        s.Font.Size = 15f;
    }
);

config.Formatters.Add(formatter);

Set the generated file name with `ExcelDocumentAttribute`

With the ExcelDocumentAttribute, you can set the file name used for an Excel document generated from a type.

[ExcelDocument("File name")]
public class ItemType { // ...

Controlling serialisation output with `ExcelColumnAttribute`

You can control how data gets serialised into columns using ExcelColumnAttribute on individual properties.

Set column header names

Header names can be provided using the Header parameter.

[ExcelColumn(Header = "Column header")]
public string Value { get; set; }

Set number format for data cells

The NumberFormat parameter allows you to provide a custom Excel number format to alter the number format used for data in a given column. For example, the following snippet will align numbers on the decimal point:

[ExcelColumn(NumberFormat = "???.???")]
public decimal Value { get; set; }

Ignore a column

To prevent a property from appearing as a column in the generated Excel file, set the Ignore parameter to true.

[ExcelColumn(Ignore = true)]
public string Value { get; set; }

Customise display order

By default, columns are ordered according to property order in the source, with properties in derived classes coming before those in base classes. However, sometimes you need more control over the order in which columns appear.

The Order parameter works similarly to JSON.NET's JsonPropertyAttribute.Order, on which implementation it was partially based. Properties are serialised from lowest to highest Order parameter value, and properties with the same Order value are in source order. By default, all properties are assumed to have an Order value of -1.

Confusing? Here's an example showing all of these rules at work:

// Second, because it has an implicit Order of -1 and is the
// first item with that Order value.
public string Value1 { get; set; }

// Last, because it has the highest Order value.
[ExcelColumn(Order = 2)]
public string Value2 { get; set; }

// Second-to-last, because it has the second-highest Order value.
[ExcelColumn(Order = 1)]
public string Value3 { get; set; }

// First, because it has the lowest Order value.
[ExcelColumn(Order = -2)]
public string Value4 { get; set; }

// Third, because it has an implicit Order of -1 and is the
// second item with that Order value.
public string Value5 { get; set; }

Change boolean property output

By default, boolean properties are serialised to either True or False. You can customise this behaviour on individual columns so that different values are output.

// Use "Yes" and "No" to represent the value of this property.
[ExcelColumn(TrueValue = "Yes", FalseValue="No")]
public bool Value { get; set; }

Use `DisplayFormat` attribute

This will apply any format string set on the property via the DisplayFormat (or DisplayFormat) attribute to this property’s value, saving the value as a formatted string in the generated Excel document. (By default, it will be ignored.)

[DisplayFormat(DataFormatString = "{0:D}")]
[ExcelColumn(UseDisplayFormatString = true)]
public DateTime Value { get; set; }

Future work

Allow serialisation of complex/nested types.
Allow reading from Excel documents.
Provide much, much finer control over serialisation of individual classes and properties.

For more details on planned work and the current project schedule, refer to the project milestones.

Have an idea that you don't see here? Can't figure out how to do something? Would like to use this, but it doesn't cover your needs? Go ahead and open an issue!