Data markers have multiple switches and options that are available for use in ExcelTemplate workbooks. |
ExcelTemplate Grouping and Nesting allows users to bind flat data to a specially formatted spreadsheet with the data being displayed in a grouped or nested format. Here are some data markers and a data marker modifier that are used in this demo:
%%group //
Signals the beginning of the grouping formatting rows. The grouping will be performed on the data field in this column and all rows below this row and above the %%endgroup row will be included in the grouping formatting. Use multiple tiered %%group and %%endgroup tags on separate columns to perform nested grouping.
%%endgroup //
Signals the end of the grouping formatting rows. This data marker should be placed in the same column as the original %%group tag. Use multiple tiered %%group and %%endgroup tags on separate columns to perform nested grouping.
%%header //
Signals the beginning of the header rows for a given group. All the rows between this row and the data markers row are repeated above the data rows for each group. In this sample, the header contains a single row with the values of the individual group categories.
%%footer
Signals the beginning of the footer rows for a given group. All the rows between this row and the %%endgroup row are repeated below the data rows for each group. In this sample, the footer row contains a subtotal row for the groups.
%%value() //
Used to reference a data marker from the header or footer rows. In the sample, this is used to place the value of the individual groups in the header for each group.
(hide) //
Used to hide a data marker value. In this sample, the data marker for the grouping categories has a hide modifier in order to keep the repeated values from showing in the resulting spreadsheet. The values are instead shown in the header row using a %%value() data marker.
This sample requires OfficeWriter Enterprise Edition to be installed because the OfficeWriter Grouping and Nesting is only available in the Enterprise Edition of the product.
public class StringDataSource { private string recipientName; private string recipientCompany; /// <summary> /// Build the report with ExcelTemplate /// </summary> public void GenerateReport() { this.recipientName = "Jon Smith"; this.recipientCompany = "SoftArtisans"; // Create an instance of SoftArtisans ExcelTemplate ExcelTemplate xlt = new ExcelTemplate(); // Open the template workbook string templatePath = @"..\..\templates\StringDataSourceTemplate.xlsx"; xlt.Open(templatePath); // Bind the variables to the template datamarkers // %%=$RecipientName xlt.BindCellData(recipientName, "RecipientName", xlt.CreateDataBindingProperties()); // %%=$RecipientCompany xlt.BindCellData(recipientCompany, "RecipientCompany", xlt.CreateDataBindingProperties()); // Process the template to populate it with the Data Source data xlt.Process(); // Save the report by streaming to the client xlt.Save(@"..\..\Output\StringDataSource_output.xlsx"); } } |
\
Template: GroupingandNestingTemplate.xlsx
Output: GroupingandNesting_output.xlsx