XlsxMediaTypeFormatter

Project page: WebApiContrib.Formatting.Xlsx

XlsxMediaTypeFormatter is a smart, customisable Excel MediaTypeFormatter implementation for ASP.NET Web API. It uses EPPlus for serialisation.

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 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 for future work.

Setting it up

First, you will need to add a reference to EPPlus to your project—either download it from CodePlex or grab the package on NuGet.

Next, 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 improving 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 (i.e. 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 Excel­Document­Attribute

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

[ExcelDocument("Column header")]
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. 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:

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

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

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

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

// This will be third, because it has an implicit Order of -1 and is the
// second item with that Order value.
public string Value5 { 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.