Introducing Templates to Create Excel Reports in .NET and Java
The Problem
Binding structured documents with data (from any data source) is a common need for many businesses. Whether your business is in logistics, shipping, sales, finance, marketing, healthcare, or education, you may find yourself generating several Excel reports daily. These reports may electronically distribute reports, invoices, shipping, delivery or purchase orders to vendors, buyers, sellers, clients, and customers.
Companies are moving from managing these tasks manually into automated processes, where developers only define a basic template, fill in data in a structured format, and generate several reports through minimal programming.
There are many template language tools available on the web. Just choose what suits your framework, language, or stack. Any developer will think of an efficient way to bridge the gap between data and the output file to accomplish several use cases.
There is still a need for a full-fledged template language, within the .NET Framework, which will solve many requirements:
- Developers do not want to learn complex API to generate reports
- Enable developers to easily edit the template files directly
- Cater to composite use cases to generate Excel files for any domain or situation
- Connect to data objects in any form - a DB, a string or ADO.NET objects
- Support Excel formulas for fast data calculation
The Solution
GrapeCity Documents for Excel (GcExcel) introduces new template support with a comprehensive syntax and API to bind Excel documents to data and generate Excel reports with advanced layouts. With a flexible syntax and easy notations for data expansion and formulas, the new template syntax and API makes it easy to define Excel templates and to generate Excel reports supporting numerous use cases.
You can also make use of the GcExcel API to directly save the Excel reports .xlsx files or as a PDF.
A developer can create a template in Excel using the new template syntax (which uses Mustache syntax braces "{{" and "}}" to define the database fields, variables, and formulas. At each location, if you want to replace the information, you can define the template fields. You can now add a data source to the workbook through the Workbook.AddDatasource() method in code and bind the data with the template using the Workbook.ProcessTemplate() method.
The template API will detect the template fields, matching with the database and replace them with actual data.
With the combination of Visual Studio Tools for Office (VSTO) based Excel API and the template support, you can generate full professional Excel reports for data analysis and distribution. This solution works well on any platform: Windows, Linux, or macOS.
A simple example of a business use case and how it is solved using the new templates support is demonstrated below:
How Excel Templates Work
A company is generating purchase orders. Purchase orders are generally sent electronically either through an Excel spreadsheet or PDF. There are several purchase orders produced in a day and the company wants to automate the process of deriving the vendor and shipping information from the database, to generate the purchase order as an Excel spreadsheet, for each vendor.
While working with a template syntax, the user should just design the template in MS Excel, apply simple code to bind the template with a database, and generate the Excel spreadsheets w.r.t, the purchase order for the particular vendor.
The company wants to generate a similar Excel spreadsheet for each purchase order:
Once you create this Excel template, load it in GcExcel's Workbook object.
var templateFile = this.GetResourceStream("xlsx\\Template.xlsx");
workbook.Open(templateFile);
Add the data source of Purchase Order table as a DataTable.
reportBook.AddDataSource("po", dt); //Assuming dt data table is defined in the code for the data
Just call 'ProcessTemplate()' method of the workbook - using which the data fields will bind automatically to the template.
reportBook.ProcessTemplate();
Save the workbook as an Excel file or as a PDF:
reportBook.Save("PurchaseOrder.xlsx");
or
reportBook.Save("PurchaseOrder.pdf");
You have generated a purchase order similiar to Figure 1 above.
Features of Excel Templates
Datasource support
The template API can bind Excel templates directly with data from the following objects.
- DataSet/DataTable in .Net and ResultSet in Java: Can handle data sources from any type of DB.
- Custom Object: These are user-defined objects. Can handle data sources from JSON String/File/XML, etc. That is, any data source that can be serialized as a custom object.
- Variable: User defined variable in code.
- Array/List: User defined array/list in code.
Simple Language Syntax
The template syntax follows the mustache syntax "{{" and "}}" as markers of the template. You can define the database field within these markers.
Here 'inv' stands for the 'Invoice' Table, while 'itemnumber' is the data field.
Define Data Expansion Direction
Data can expand both horizontally or vertically in your reports. The template syntax helps you define the direction for data expansion using the property - 'E=H' or 'E=V', where E stands for 'Expansion'. Default expansion is vertical.
The following data expands the item numbers vertically, by specifying E=V.
The following data expands the product names horizontally, by specifying E=H:
Define Context for Data Expansion
Data can expand w.r.t a context (or data field), either horizontally or vertically. When multiple cells are bound in the template, there will be a natural relationship of group or filter between the cells and the cells will expand in pairs. This is called the Context relationship.
Context can be one of the following types: none, default, and custom.
The default context is the adjacent cell on the left with E=V, or the adjacent cell on the top with E= H. If the context is not the left adjacent cell nor top adjacent cell, you must specify it explicitly. Context is defined with the 'C' property, like this C=E4 (where E4 is a cell denoting a data field).
See the following examples:
Context = None (C=None) Here you want Area to expand horizontally, but Salesman should expand by default without any dependence on the Area. So you define "Context" as "None" for Salesman expansion.
Context = Default
But if you want Salesman to expand as per Area (such that Area expands horizontally) and the Salesman expand vertically under Area, do not define any context for Salesman. The salesman cell (A5) will automatically take context as Area (A4) since A4 defines Area expansion as horizontal ({{ds.Area(E=H)}}).
Context = Custom (C=Custom) If you need your data to expand w.r.t. a context, but the context cell is not adjacent, just set the context explicitly for that cell. In the example below, context of Salesman is defined to A4.
Formulas can also be defined w.r.t. a context cell. In following example, the subtotal is calculated specific to the cell C5, (ProductType) so a subtotal is calculated for Fruits and a subtotal is calculated for vegetables. Hence context here is cell C5, i.e., 'ProductType.'
While the GrandTotal is calculated for all products sold by the Salesman, so context here is cell B5 (the Salesman).
Define Range of Data
If you do not specify any context, you can define the range of template cells. The cell that defines the range will be followed as a context for other cells to expand.
Example: This report displays student family details for every student, which means the data needs to expand w.r.t "Student Name." Then after a break of two lines, the family details need to be displayed for another student. Instead of adding context to every field, we define the range R=A3:B9 for the Student Name - {{report.student.name (R=A3:B9)}}, stating that the family details need to expand and group w.r.t student name.
Add Data from Multiple Data Sources
It is possible to retrieve data from multiple data sources or multiple data tables within a data source, and the syntax is simple. Just use the object of the data source followed by the data field.
This example merges data from two data sources, the employee information from one data table and department information from another table.
Group your Data
Sometimes, certain data is repeated and you want to group your table based on that data field. With the new template syntax, you can group the data in following ways: normal, merge, repeat, or list.
The example below shows how you can merge common data and group further columns w.r.t this group. Then also choose, whether to merge the data for other data fields or repeat it for every row.
Outline Group
If you do not want to merge your groups, you only want to create outline groups, then do not specify any group property for the groups and arrange the inner data by providing the context to expand as per the outline group. In this example, we want the Salesman to be displayed for every area, so we give context as C4 (or {{ds.Area}}).
Next, we want the sales to be displayed for every product. We just provide the context as D3 (displaying sales as per the product type).
The crux here is that using the cell style and presenting the data as an outline together with specifying the context for the inner rows, the data can be presented as an outline group.
Specify Nested Data Fields
If your data is nested and you want to arrange a report as per an inner object field, you can define nested data fields with the new template syntax.
For example, suppose your data (JSON) looks like this:
There is a report, which lists student information. The 'father' and 'mother' is a nested object within 'student'->'family', which lists both parent's name and occupation.
{
"report":[
{
"student": [
{
"name": "Jane",
"address": "101, Halford Avenue, Fremont, CA"
"family"[
{
"father":[
{
"name": "Patrick James",
"occupation": "Surgeon",
}
]
"mother":[
{
"name": "Diana James",
"occupation": "Surgeon",
}
]
}
{
"name": "Mark",
"address": "101, Halford Avenue, Fremont, CA"
"family"[
{
"father":[
{
"name": "Jonathan Williams",
"occupation": "Product Engineer",
}
]
"mother":[
{
"name": "Joanna Williams",
"occupation": "Surgeon",
}
]
}
]
}
]
}
]
}
You need to generate a report such that all Students in a grade are listed in the report with the Student's Family Information. The syntax to denote a nested object 'father', 'mother' in this case is real simple with the template syntax. Just separate the nested objects with a '.'. See the template below and resultant report.
Define Formula Functions
The templates feature supports some of the standard MS Excel formula functions, which you can use to perform calculations over your data. You can apply formula over a cell, a data field, or as an expression. Following example demonstrates varying types of formula applied over the Invoice data. Just start the expression with the mustache curly braces "{{" and type the Excel formula, just normally as you do in MS Excel and close the braces.
You can also choose to Sum over a cell express like this: {{=Sum(inv.price)}}.
Define Expressions Following operators: '+', '-', '*', '/' and '()' can be used to define the Expression field in the template.
For example:
\{{=ds.count*ds.price}}
\{{=A18*0.05}}
Define Inline Fields
With the template syntax, you can define inline fields along with the text within a cell. This generates a report like a Mail-merge report. For example, a template like this where employee information is merged with text in a cell will generate the output as shown.
Define Sort Expression
You can declare the sorting direction in the Excel template as Ascending, Descending, or None.
The following example shows how to use the three expressions:
Set Styles
The template support honors the cell and merged styles applied on the cell. The data will grow as per the style applied on the cells.
Add template field in Worksheet's name to generate multiple worksheets
You can insert the data field/expression field in the name of a worksheet and multiple worksheets would be generated at run time. For example, if you want to display the details of different employees on different sheets, you can assign {{ds.EmpName}} to the worksheet name, and a new worksheet would be generated for each employee with the names of the employees appearing in the worksheet.
Add Page Breaks
If you want to add a new page after a field, you can define set PageBreak=true while defining the data field. The behavior of Page break is similar to MS Excel 'Insert Page Break' behavior.
- If the template cell is located in the first column, a horizontal page break is added
- If the template cell is located in the first row, a vertical page break is added
- If the template cell is located in a location other than the first row and the first column, both a horizontal and a vertical page break is added.
Multiple Reports in One Sheet
Multiple reports can be supported in one sheet. These reports can be generated from the same or different data sources. The report below shows three reports in a single sheet: one report lists the travel details and the next report shows the Lodging details. The emergency contacts and the travel party changes for each customer and destination.
Excel Reports You Can Generate
You can create real time reports using the new template support. All you need is the right syntax and properties. With usage of data expansion, context and formula functions, you can generate most popular types of reports from simple to complex use cases.
Generate the following reports:
- Line reports
- Mail-merge reports
- Cross-table reports
- Multiple reports in one report
- Tablix reports
- Receipt reports
- Sales reports
- Purchase Order reports
- Invoice reports
Here's look on some of these reports solved using the templates support.
Invoices
Purchase Orders
Financial Dashboard
Shipping and Delivery orders
Travel Details Report
Sales Tablix Report
Vertically Grouped Report
Check out or demos and documentation:
Shilpa Sharma