Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object.
In order to help organize hierarchical data in a more easily readable format, ExcelTemplate now features grouping and nesting options. The basic feature of grouping and nesting is that ExcelTemplate can remove unnecessary repeated values from a field if it is given hierarchical data in a flat format. This basic functionality is explained in further detail below, while advanced features such as headers and footers are discussed on the Advanced Grouping and Nesting page.
Though grouping and nesting is meant to show hierarchical data, the input data for a template with grouping and nesting must be in flat format. This means that a data set for nesting and grouping will have repeated values for fields that are higher in the hierarchy. For this tutorial, the data will look like this:
Territory Name |
Salesperson |
Product Category |
Product Subcategory |
Color |
Unit Price |
Order Qty |
Line Total |
---|---|---|---|---|---|---|---|
Central |
Jillian Carson |
Bikes |
Mountain Bikes |
Black |
$2024.99 |
1 |
$2024.99 |
Central |
Jillian Carson |
Bikes |
Mountain Bikes |
Black |
$2024.99 |
1 |
$2024.99 |
Central |
Jillian Carson |
Bikes |
Mountain Bikes |
Silver |
$2039.99 |
1 |
$2024.99 |
Central |
Jillian Carson |
Clothing |
Socks |
White |
$5.70 |
3 |
$17.10 |
Northwest |
David Campbell |
Accessories |
Helmets |
Red |
$20.19 |
2 |
$40.37 |
Northwest |
David Campbell |
Accessories |
Helmets |
Blue |
$20.19 |
1 |
$20.19 |
Northwest |
David Campbell |
Bikes |
Mountain Bikes |
Black |
$2024.99 |
2 |
$4049.99 |
Northwest |
David Campbell |
Bikes |
Mountain Bikes |
Black |
$2024.99 |
1 |
$2024.99 |
Grouping code is no different from regular ExcelTemplate code. In this tutorial, we use the following ExcelTemplate code:
DataSet ds = GetData(); //--- Get the Sales data ExcelTemplate xlt = new ExcelTemplate(); //--- Create a new ExcelTemplate object xlt.Open(Page.MapPath(@"templates\GroupingTemplate.xlsx")); //--- Open the template //--- Create the DataBindingProperties object DataBindingProperties props = xlt.CreateDataBindingProperties(); //--- Bind the data to the template xlt.BindData(ds, "AdventureWorks", props); //--- Process the template to import the data xlt.Process(); //--- Stream the output back to the user xlt.Save(Page.Response, "Grouped Sales Report.xlsx", false); |
Dim ds As DataSet = GetData() '--- Get the Sales data Dim xlt As New ExcelTemplate() '--- Create a new ExcelTemplate object xlt.Open(Page.MapPath("templates\GroupingTemplate.xlsx")) '--- Open the template '--- Create the DataBindingProperties object Dim props As DataBindingProperties = xlt.CreateDataBindingProperties() '--- Bind the data to the template xlt.BindData(ds, "AdventureWorks", props) '--- Process the template to import the data xlt.Process() '--- Stream the output back to the user xlt.Save(Page.Response, "Grouped Sales Report.xlsx", False) |
The first element needed for a grouping template is a data row that contains all of the data markers for your data set, as shown below:
If you bound the data above to this template, it would display exactly as you see it above – in flat form. You can group based on the first column – Territory Name – by adding a %%group tag to the cell above the data marker for that column. If you add any %%group tag, you must also add an %%endgroup tag below the data markers in order to mark the end of the grouping section:
The resulting spreadsheet should look like the one below, with all of the extra repeated values from the Territory Name field removed:
Notice that the rows with the %%group and %%endgroup tags are removed as well. Be sure not to place any content in these rows, as it will be automatically removed when data is bound to the template.
You can group by multiple groups by placing extra %%group markers in more columns. Additional %%endgroup markers are unnecessary. Each of the new fields will be grouped in order of hierarchy, from left to right unless otherwise specified. For example, if you want to group first by Territory Name, then by Salesperson, place an additional %%group marker in the second column next to your first %%group marker:
The resulting spreadsheet will be grouped first by Territory Name, then by Salesperson:
You can group even further by adding additional %%group tags:
The resulting spreadsheet:
Additional functionality such as headers and footers, are explained in the Advanced Grouping and Nesting page.