WebApiContrib​.Formatting​.Xlsx

Make smart, beautiful Excel documents with ASP.NET Web API.

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

Features

Limitations

Xlsx­Media­Type­Formatter 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

  1. Grab WebApiContrib​.Formatting​.Xlsx from NuGet.
  2. Add the Xlsx­Media­Type­Formatter 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 Xlsx­Media­Type­Formatter 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​<Office­Open­Xml​.Style​.Excel­Style> that specifies visual formatting options (such as fonts and borders) for all cells.
headerStyle
Default null.
Can take an Action​<Office­Open­Xml​.Style​.Excel­Style> 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("File name")]
public class ItemType { // ...

Controlling serialisation output with Excel­Column­Attribute

You can control how data gets serialised into columns using Excel­Column­Attribute 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 Number­Format 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 Json­Property­Attribute​.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

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!