Create Excel Report using Template

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.

BudgetPlan_DataTable.xlsx

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.

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.
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
Template Layout: {{ds.Country}}

Load the template in GcExcel.

Console.WriteLine("Generating Marketing Report using GcExcel 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);

Configure DataSource and add DataColumns and data to the DataTable.

// 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");

Add DataSource in GcExcel, using the AddDataSource method.

// Add DataSource
// Here "ds" is the name of dataSource which is used in templates to define fields like {{ds.SmartPhone}}
workbook.AddDataSource("ds", dataSource);

Execute the template using ProcessTemplate method.

// Invoke to process the template
workbook.ProcessTemplate();

Save the final report.
```
// 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

Excel Report: Smartphone Expenses

Excel Report: Launch Events

Excel Report: Countries (Multiple reports are created)

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

GcExcel 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:

//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.