OfficeWriter Documentation
.
ExcelWriter v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools

Versions Compared

Old Version 5

changes.mady.by.user Alison Bird [softartisans]

Saved on

compared with

New Version Current

changes.mady.by.user Gabe Goodwin [softartisans]

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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
iconfalse
titleRequirements

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.

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