Intro
Excerpt |
---|
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.
To learn how to create a template for Grouping and Nesting, see our in-depth tutorai Using Grouping and Nesting with ExcelTemplate.
Note | ||||
---|---|---|---|---|
| ||||
|
Code
Code Block |
---|
public class StringDataSourceGroupingAndNesting { private string recipientName; private string recipientCompany; /// <summary> /// Build the report with ExcelTemplate /// </summary> public void GenerateReport() { this.recipientName = "Jon Smith"; //--- Create a new ExcelTemplate object and open a template file this.recipientCompany = "SoftArtisans"; ExcelTemplate xlt = new ExcelTemplate(); // Create an instance of SoftArtisans ExcelTemplate //Open the template ExcelTemplate xlt = new ExcelTemplate( xlt.Open(@"..\..\ExcelTemplateFiles\GroupingAndNestingTemplate.xlsx"); //--- OpenFill the templatedataset workbookwith the data from the CSV file string templatePath DataSet ds = GetCSVData(@"..\..\templatesExcelData\StringDataSourceTemplateGroupingandNestingData.xlsxCSV"); xlt.Open(templatePath); //--- Bind the data to the template file and save it. // Bind the variables to the template datamarkers xlt.BindData(ds, "Data", xlt.CreateDataBindingProperties()); // %%=$RecipientName xlt.Process(); xlt.BindCellDataSave(recipientName, "RecipientName", xlt.CreateDataBindingProperties())@"..\..\ExcelOutputFiles\GroupingandNesting_output.xlsx"); } //Parse %%=$RecipientCompanythe data from the CSV file xlt.BindCellData(recipientCompany, "RecipientCompany", xlt.CreateDataBindingProperties()) System.Data.DataSet GetCSVData(string csvFileName) { DataSet ds; using //(GenericParserAdapter Processparser the= template to populate it with the Data Source datanew GenericParserAdapter(csvFileName)) { xlt.Process() parser.ColumnDelimiter = ','; // Save the reportparser.FirstRowHasHeader by= streamingtrue; to the client xlt.Save(@"..\..\Output\StringDataSource_output.xlsx" ds = parser.GetDataSet(); } return ds; } |
...
}
|
Downloads
- Template: GroupingandNestingTemplate.xlsx
- Output: GroupingandNesting_output.xlsx