Document Solutions for Excel, .NET Edition | Document Solutions
Templates / Create Excel Report using Template
In This Topic
    Create Excel Report using Template
    In This Topic

    This walkthrough considers the use case to create a Marketing Report of a company which is launching a new series of smartphones. Hence, an Excel report for the planned marketing activities needs to be created. The report details out the planned events for the launch, its budget and expenses. The datasource used for binding the data, in this case, is DataTable. The template layout is created in different Excel tabs to generate multiple reports. 

    The below steps describe how to create an Excel report using template. You can also download the Excel template layout here.

    1. Create template layouts in different Excel worksheets of a workbook. Define the template layout of Marketing report using different types of fields:
      • Static Fields: Define the static fields in template layout, that is, the fields whose values will remain constant in the final report. For example, the header fields or template header like Marketing Report, SmartPhone, Event etc.
      • Bound Fields: Specify the datasource bound fields in mustache braces {{ }}. For DataTable datasource, define the bound fields as {{ds.FieldName}} where ds is the alias of the datasource, specified in code using AddDataSource method.
      • Expression Fields: Specify the functions in fields whose value will be calculated using formulas.
      • Sheet Name: Create multiple template layouts in different Excel tabs, namely, Marketing Report, Smartphone expenses and Launch events. Specify a data bound FieldName for last sheet, {{ds.Country}}, which will generate multiple reports based on the values of 'Country' field in the data source. 

      Template Layout: Marketing Report

      The below layout uses the Group property (G=Merge), which will group the smartphones against the corresponding records by displaying it once per group. The merge value merges the cells of each group.          

      Group property

      Template Layout: SmartPhone Expenses

      The below layout uses two template properties, Cell expansion (E=H) and Cell context (C=A3)

      • The cell expansion property will expand the smartphone field horizontally.
      • The cell context property will make sure that the expense field expands horizontally depending upon the smartphone field. 

      Cell expansion and context propeorties

      Template Layout: Launch Events

      The below layout uses four template properties, Range (R=A3:B5), Sort (S=None), Cell expansion (E=H) and Page break (PageBreak=True)

      • The Range property acts as the fallback context for the fields in specified range, which means, that the fields which have no default or explicit context will use this current field as their context.
      • The Sort property will not sort the events based on its 'none' value
      • The event field will expand horizontally based on the cell expansion property
      • The Page Break property will add a vertical and a horizontal page break

      Range, sort, and page break properties

      Template Layout: {{ds.Country}}

      Template Layout


    2. Load the template in DsExcel.
      C#
      Copy Code
      Console.WriteLine("Generating Marketing Report using Templates");
      // Initialize Workbook
      var workbook = new Workbook();
      // Load BudgetPlan_DataTable.xlsx Template in workbook from Resource
      var templateFile = "../../../Resources/BudgetPlan_DataTable.xlsx";
      workbook.Open(templateFile);

    3. Configure DataSource and add DataColumns and data to the DataTable.
    4. C#
      Copy Code
      // We can have mutiple types of DataSources like Custom Object/ DataSet/ DataTable/ Json/ Variable.
      // Here dataSource is a DataTable
      var dataSource = new DataTable();
      
      // Adding DataColumns in DataTable according to the Template fields
      dataSource.Columns.Add(new DataColumn("SmartPhone", typeof(string)));
      dataSource.Columns.Add(new DataColumn("Event", typeof(string)));
      dataSource.Columns.Add(new DataColumn("Budget", typeof(Int32)));
      dataSource.Columns.Add(new DataColumn("Expense", typeof(Int32)));
      dataSource.Columns.Add(new DataColumn("City", typeof(string)));
      dataSource.Columns.Add(new DataColumn("Country", typeof(string)));
      
      // Adding Data in DataTable
      dataSource.Rows.Add("Apple iPhone 11", "Phone Launch", 1000, 950, "Seattle", "USA");
      dataSource.Rows.Add("Apple iPhone 11", "CEO Meet", 2000, 1800, "New York", "USA");
      dataSource.Rows.Add("Samsung Galaxy S10", "CEO Meet", 1600, 1550, "Paris", "France");
      dataSource.Rows.Add("Apple iPhone XR", "Phone Launch", 1800, 1650, "Cape Town", "South Africa");
      dataSource.Rows.Add("Samsung Galaxy S9", "Phone Launch", 1500, 1300, "Paris", "France");
      dataSource.Rows.Add("Apple iPhone XR", "CEO Meet", 1600, 1500, "New Jersey", "USA");
      dataSource.Rows.Add("Samsung Galaxy S9", "CEO Meet", 1200, 1150, "Seattle", "USA");
      dataSource.Rows.Add("Samsung Galaxy S10", "Phone Launch", 1100, 1070, "Durban", "South Africa");

    5. Add DataSource in DsExcel, using the AddDataSource method.
      C#
      Copy Code
      // Add DataSource
      // Here "ds" is the name of dataSource which is used in templates to define fields like {{ds.SmartPhone}}
      workbook.AddDataSource("ds", dataSource);

    6. Execute the template using ProcessTemplate method.
      C#
      Copy Code
      // Invoke to process the template
      workbook.ProcessTemplate();

    7. Save the final report.
      C#
      Copy Code
      // Save to an excel file
      Console.WriteLine("BudgetPlan_DataTable.xlsx Template is now bound to DataTable and generated MarketingReport_DataTable.xlsx file");
      workbook.Save("MarketingReport_DataTable.xlsx");

      The output of the Marketing Report is shown as below.

      Excel Report: Marketing Report

      Marketing Report

      Excel Report: Smartphone Expenses

      Smartphone Expenses report

      Excel Report: Launch Events

      Launch Events report

      Excel Report: Countries (Multiple reports are created)

      Countries report

    In the above process, the template is overwritten while generating the Excel report. To create a report while keeping the template intact, see the Create Report While Retaining Template section below.

    Create Report While Retaining Template

    DsExcel provides GenerateReport method in the IWorkbook interface that returns an instance of a new workbook report while retaining the template. The method also provides an overload so that you can generate report for a specific worksheet only.

    To generate report using the GenerateReport method, see the example code below:

    C#
    Copy Code
    //Process the template and return the instance of report workbook 
    IWorkbook report = workbook.GenerateReport();  
                            
    // Process the template for a specific worksheet only
    // IWorkbook report = workbook.GenerateReport(workbook.Worksheets["Sales"]); 

     To view the code in action, see Generate Report demo.