Every organization, irrespective of the domain, undergoes certain routine tasks, like planning and managing projects, placing orders, maintaining inventory, and generating invoices. To keep the business flow organized, these tasks require many hours of dedicated work. Organizations are looking for ways to cut this time and automate their processes. This is where bound structured documents (templates) can help.

A template is a pre-defined and formatted workbook that is used to help create the final report. Once you have the template, it just needs only minor tweaks to adjust according to the purpose and can thereafter be used time and again, saving you a lot of wasted effort.

Introducing GcExcel Templates

GrapeCity Documents for Excel (GcExcel) introduces new template support with comprehensive syntax and API to bind Excel documents to data and create Excel reports with advance layouts. With flexible template syntax, easy notations, support for various types of data sources, and need for minimal programming, GcExcel Templates are easy to use to define Excel templates and create Excel reports.

To understand how easy it is to use GcExcel Templates, let’s generate a quarterly products sales report.

Generate Quarterly Products Sales Report using Templates

A sales report gives an overview of the state of the sales activity within a company. It shows different trends happening in the sales volume and alerts management to any potential issues. Taking regular snapshots of the product-wise sales helps to assess whether they're generating expected revenue.

Let’s say there are several products sold by an organization. To analyze how the sales trend has changed over the years, they wish to generate a sales report. Instead of manually preparing the sales report, the company wants to automate the process of fetching products and their sales information from the database and generate a quarterly sales report as an Excel spreadsheet.

Using GcExcel Templates, the above requirement is straightforward to implement as the developer. Design the template in MS Excel, apply simple code to bind the template with the database, and GcExcel will generate separate Excel spreadsheet w.r.t product sales for each financial year.

The following image shows how a sales report for a specific quarter looks:

Styling Pivot Tables in Excel Files .Net Core

To create this report, first you need to create a template in MS-Excel. How your final report will look depends on this template. Before getting started, let’s first familiarize ourselves with creating templates using GcExcel template syntax.

Generating XLSX Template in .NET Application

Template creation is undoubtedly the most important step in dynamic report generation. While creating a template, you’ll need to follow a certain GcExcel template syntax used to define database fields. Therefore, in the sales template wherever you want to replace the product and sales information, you must define the database fields following the syntax.

Template creation shall broadly be divided into the following steps:

  • Define data
  • Define data fields
  • Set data field template properties
  • Use formula functions

1. Define data - (C#)

As you see in the image above, the data fields: area, city and items' category, name, and sale are present in the final expected report; therefore, they will need to be added.

The following classes: region, item and sale, are created which will act as the blueprint for sales report generation:

Data Field Declaration - (C#)
public class Item
{
    public Int32 ProductID { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public double Revenue { get; set; }
}
public class Region
{
    public string Area { get; set; }
    public string City { get; set; }
}
public class Sale
{
    private Region _region;
    private List<Item> _items;
    public int Year { get; set; }
    public Region Region
    {
        get { return _region; }
        set { region = value; }
    }
    public List<Item> Items
    {
        get { return _item; }
        set { items = value; }
    }
    public Sale()
    {
        Items = new List<Item>();
        Region = new Region();
    }
}

2. Creating Templates in MS-Excel

Once the data definitions are ready, you can start creating a template in Excel and map individual data field cells to their definition in the blueprint.

The following steps guide you through the template creation process.

2.1 Defining Data Fields

Styling Pivot Tables in Excel Files .Net Core

2.2 Set Data Fields Properties

Apart from replacing data fields dynamically, GcExcel also facilitates how they get replaced. This is done by properties like Expansion, Context, Range, Group, Sort and more. GcExcel uses brackets ‘(‘ and ‘)‘ to organize all template properties. A property is set in key-value format and multiple properties get separated through a comma. For developer’s convenience, property names and values are case-insensitive and support shorthand notations.

Now, let’s see how to use some of these properties in the generation of sales reports.

Expansion

Above, data field cells were defined in a way that they get replaced dynamically by the field value, but when there are multiple values of the same field in data, you'll want them to expand automatically, either vertically or horizontally. Like in the final sales report, Areas and Cities appear horizontally along with each other whereas Item's Categories and Names appear vertically.

The template syntax helps you define the direction for data expansion using the Expansion property. Expansion of a field can happen in any of the directions, horizontal or vertical, the default being vertical expansion.

Styling Pivot Tables in Excel Files .Net Core

Context

Styling Pivot Tables in Excel Files .Net Core

2.3 Using formula functions

Styling Pivot Tables in Excel Files .Net Core

Following is an image of how the template with GcExcel template syntax should look:

Styling Pivot Tables in Excel Files .Net Core

Sales_Template.xlsx

After you know the syntax and have created a template for your sales report, just load this template, add data, process template, and that’s it, your report will be ready.

Steps to use GcExcel Templates

Templates can be incredibly useful in your business and GcExcel Templates helps in automating these processes. Now you’re ready to get started with implementing GcExcel Templates in your application.

Following are the steps guiding you right from loading the GcExcel assembly, to generating the Excel report.

1. Installation

1.1 In your .Net Core Console application, right-click ‘Dependencies’ and select ‘Manage NuGet Packages’.

1.2 Under the ‘Browse’ tab search for GrapeCity.Documents.Excel and click Install.

1.3 While installing, you’ll receive two confirmation dialogs: ‘Preview Changes’ and ‘License Acceptance’, click ‘Ok’ and ‘I Agree’ respectively to continue.

2. Setup your project

2.1 Add namespace

In the Program file, import following namespace:

using GrapeCity.Documents.Excel;

2.2 Create a new workbook

In the main function, add following code to create a new GcExcel workbook.

Workbook book = new Workbook();

2.3 Load Excel Template

Use Open method to load existing Excel Template file (shown in Fig.1) in the GcExcel workbook:

book.Open("Sales_Template.xlsx");

2.4 Initialize data

Prepare data that will fill in the data fields in loaded Excel Template:

var sales = new List<Sale>();
Sale sale1 = new Sale();
Item item1 = new Item()
{
     ProductID = 1140,
     Category = "Mobile",
     Name = "Iphone XR",
     Revenue = 2334091
};
sale1.Items.Add(item1);       
sale1.Duration = "2018-19";
sale1.Region = new Region() { Area = "South China", City = "Macau" }; ;
sales.Add(sale1);

NOTE: GcExcel Templates can bind Excel templates directly with data from any of the following objects: DataSet/DataTable, Custom Object, Variable, and Array/List.

2.5 Add datasource

Use the AddDataSource method, to load the data in workbook.

book.AddDataSource("Sales", sales);

2.6 Process Template

Use ProcessTemplate to execute the template. Invoking this method, the data fields will bind automatically to the Excel template loaded in the workbook.

book.ProcessTemplate();

2.7 Save report to XLSX

Finally, export it all to an Excel file so you can see how database fields get bound to the template dynamically, giving you the final report.

book.Save("../../../SalesReport.xlsx");

Be sure that this line of code comes AFTER all your other code, so that all of it processes before it gets saved.

Download the Templates GcExcel.zip

Demos and documentation:

Help .NET | Help Java | Demo .NET | Demo Java

If you have a question about this tutorial, leave it for us in the comment thread below!

Try GcExcel .NET free for 30 days

Download the latest version of GrapeCity Documents for Excel, .NET

Download Now!