View on GitHub ClosedXML.Report

Flat Tables

Variables or their properties of type IEnumerable may be bounded to regions (flat tables). To output all values from IEnumerable you should create a named range with the same name as the variable. ClosedXML.Report searches named ranges and maps variables to them. To establish binding to properties of collection elements use the built-in name item.

There are certain limitations on range configuration:

Range names

While building a document, ClosedXML.Report finds all named ranges and determines data sources by their name. Range name should coincide with the name of the variable serving a data source for this range. For nested tables, the range name is built using an underscore (_). E.g., to output values from Customers[].Orders[].Items[] the range name must be Customers_Orders_Items. This example may be found in the sample template.

Expressions within tables

To work with tabular data, ClosedXML.Report introduces special variables that you can use in expressions inside tables:

Vertical tables

Requirements for vertical tables

Each range specifying a vertical table must have at least two columns and two rows. The leftmost column and bottommost row serve to hold configuration information and are treated specially. After the report is built the service column is cleared, and the service row is deleted if it is empty.

When dealing with vertical tables, CLosedXML.Report performs the following actions:

Look at the example from the start page. As you can see in the picture, there is a range named Orders including a single row with expressions, a service row and a service column.

template

We applied custom styles to the cells in the range, i.e. we specified date formats for cells SaleDate and ShipDate and number formats with separators for cells Items total и Amount paid. In addition, we applied a conditional format to the Payment method cell.

To build a report from the template you simply have to run this code:

...
        var template = new XLTemplate('template.xslx');
        var cust = db.Customers.GetById(10);

        template.AddVariable(cust);
        template.Generate();
...

public class Customer
{
    ...
    public List<order> Orders { get; set; }
}

public class order
{
	public int OrderNo { get; set; } 
	public DateTime? SaleDate { get; set; } // DateTime
	public DateTime? ShipDate { get; set; } // DateTime
	public string ShipToAddr1 { get; set; } // text(30)
	public string ShipToAddr2 { get; set; } // text(30)
	public string PaymentMethod { get; set; } // text(7)
	public double? ItemsTotal { get; set; } // Double
	public double? TaxRate { get; set; } // Double
	public double? AmountPaid { get; set; } // Double
}

In the picture below you see the report produced from the specified template. Note that selected area now contains the data and is named Orders. You can use this name to access data in the result report.

result

Horizontal tables

Horizontal tables do not have such strict requirements as vertical tables. The named range consists of a single row (in other words, without an options row) and is assumed to be a horizontal table. A horizontal table does not need to have a service column either. In fact, the horizontal table may be defined by a single cell. To explicitly define a range as a horizontal table definition, put a special tag <<Range horizontal>> into any cell inside the range. You may find the example using the horizontal range on the GitHub.

There two ranges in that template - dates and PlanData_Hours. Each of these ranges consist of one cell. As has been said, ClosedXML.Report treats such ranges as horizontal table definitions.

horizontal template

The result report:

horizontal result

Service row

ClosedXML.Report offers nice features for data post-processing on building report: it sort the data, calculate totals by columns, apply grouping, etc. Which actions to perform may be defined by putting special tags to the template. Tag is a keyword put into double angle brackets along with configuration options. Tags controlling data in tables should be placed in the service row of the table template. Some of the tags are aplied to the range as a whole, the others affect only a column they are put in.

We will give detailed information on tags usage in the next chapters.

Now consider the following template.

simple template

The cell in the service row in the Amount paid column contains the tag <<sum>>. The cell next to it contains a static text “Total”. After the report is built the tag <<sum>> will be replaced with a formula calculating the sum of the amounts of the entire column. Tag <<sum>> belongs to the “column” tags. Such tags are applied to the column they are put in. Other examples of the “column” tags are <<sort>> that defines the ordering of the data set by the specified column, or <<group>> configuring grouping by the specified field.

Actions that must be performed on the whole range are defined with “range” tags. They are defined in the first (leftmost) cell of the service row. You may experiment a little with the described template. Try to open it and write tags <<Autofilter>> <<OnlyValues>> into the first cell of the service row. After you saved the template and rebuilt the report you may see that now it has the auto-filter turned on, and the formula =SUBTOTAL(9, ... in the Amount paid column has been replaced with the static value.

Multiple ranges support for Named Ranges

If we need to use one data source for several tables, then we can create a composite named range. In the example below, the Orders range includes two ranges $A$5:$I$6 и $A$10:$I$11.

image

When building a report, ClosedXML.Report will fill both of these ranges with data from the Orders variable.