For generating custom reports with complex formatting rules, SheetKraft's ExportToExcel Using Template is a comprehensive and efficient feature. Following article highlights the individual aspects of this feature.
The template section in itself is divided into two halves, the left half contains a tree-like relation indicating various levels of template which doubles up as a means of quick navigation across template.
As seen above, the templates can be nested into multiple levels. The containers are used to determine the direction of repetition i.e. right or down.
1. Using Template
A template is basically a sample - a cell range which acts as a blueprint for the final report, and is specified in the Template range. This has to be different from the columnar Data range, which contains the data to be fed into the template. Note that to avoid any irregularities, the template must not contain any merged cells. Merging can be achieved using anchors which is described in a later section. Format range can be left blank for most use cases.
2. Template Cells
A typical template can be separated out into two types of cell ranges, fixed and repeating. The fixed ones are called Template Cells. Reporting column or custom formula from data range can be mapped to a single cell or a cell range of template.
Anchors are used to fix template cells' position relative to the template. Checking the direction indicates that the number of cells between that edge of the template and the range will not change. This has a special use case where ticking opposite directions - i.e. top and bottom or left and right - together merges the cells in the range along that axis.
This allows for containers for repeating elements to be made if a template contains recurring patterns. The name of the container can change either to RepeatDown or RepeatRight depending on the direction of the repeat.
A container has information related to the repetition of child template. A cell range spanning all similar repeating patterns is to be given in Full Range.
The direction can be set to Down or Right. The basis of partitioning the data into these repeat elements is provided in PartitionBy.
3. Repeated Ranges
The repeating sections in the template having the same structure as the first repeat, except the first repeat itself, should be specified in repeated ranges. The cell spacing between the repeated ranges is rotated, i.e. if there are 3 repeated ranges, the 4th repeated range in the final report would be treated as first repeated range.