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 and a OOXML (.xlsx) template.
Introduction
If you have hierarchical data in an Excel report, grouping the data is a good way to make it easier to read. ExcelTemplate now features grouping a nesting options that make it easy to group hierarchical data. The basic feature of ExcelTemplate grouping and nesting is removing unnecessary repeated values from a field if it is given hierarchical data in a flat format. We'll focus on a single group in this post; for information on grouping and nesting multiple groups, you can check our documentation, and be sure to check back here for further blog posts. Advanced features such as headers and footers are also discussed in our documentation and will also be described in a future blog post.
Creating a Data Set for grouping
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. In this case, salesperson José Saraiva of PostOne Bicycles is faced with sales data that looks like this:
Grouping code
Grouping code is no different from regular ExcelTemplate code. In this case, José's programmers will write this code to process his templates:
Grouping by a single group
You'll need to use data markers in a Grouping and Nesting template, just as any other ExcelTemplate. You will, however, need to place the data markers for the grouped data in a single data marker row, and José has done in the example below:
If José binds the data above to this template, he'll see it exactly as you see it above – in flat form. He can group based on the first column – Territory Name – by adding a %%group tag to the cell above the data marker for that column. To mark the end of the grouping block, he add an %%endgroup tag below the data markers, as is required any time you use a %%group marker:
The resulting spreadsheet should look like the one below, with all of the extra repeated values from the Territory Name field removed. Now José is able to view his data grouped by Territory Name:
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.
As helpful as this simple grouping would be if José wants to easily see the sales he's made to each territory, it's just the tip of the iceberg for ExcelTemplate Grouping. In my next post, I show how José's colleague Jillian expands on ExcelTemplate grouping by adding nested groups.
Further Resources
Posts in this series:
Documentation:
