Generating Excel Reports in C# with Enhanced Excel Templates

In the previous v3 release, Document Solutions for Excel (DsExcel, formerly known as GcExcel) introduced support for Excel templates. This support helped developers map the template fields with a database and generate dynamic Excel reports. In the v3.1 release, DsExcel takes things one step further by adding more features like charts, images, conditional formatting, and much more to Excel templates.

Read the full DsExcel v3.1 release.

Enhanced Excel Templates

With the latest DsExcel release, developers can now design professional Excel reports with minimal programming. Adding features such as charts, images, and conditional formatting to Excel templates makes creating these reports in DsExcel possible.

These newly added components not only help create a more visually appealing report but also make it easier to comprehend.

To understand how to incorporate these features in Excel templates, let's examine a typical business scenario: generating a catalog for a car dealership.

Creating a Dynamic Report with Enhanced Excel Templates

A new car retail outlet would like to publish a catalog that lists their inventory. In addition to listing the cars they have, they would like to include the vehicle's features, technical details, and price trends.

The enhanced template features in DsExcel would be helpful in the above situation. By designing an Excel template using any of the new component features listed earlier, you can bind the template with data from your .NET Core application and process the bound template to generate a car catalog.

To generate a catalog in Excel, the user needs to define the Excel template. Since we will only be focusing on enhanced Excel template features in this article, we will not be discussing how to create Excel templates with DsExcel. You can get started with Excel templates using DsExcel here.

Implementing Enhanced Excel Template Features

An Excel report will build on the Excel template if the template is designed well. Then the final Excel report will be user-friendly and easy to use. So, let's discuss the type of Excel template you would use to generate a dynamic car catalog.

Assuming you already know how to create Excel templates using DsExcel and are aware of its syntax, let's jump into the enhanced Excel template features. The following topics build off of an Excel template defined to map data fields to template cells.

Download the car catalog Excel template here.

Template Charts

Charts are one of the most popular and well-known mediums for data visualization in any professional report. With this feature, you can now define a chart in an Excel template file, bind it to data, and let DsExcel generate the chart at run-time.

To bind a chart in an Excel template file, DsExcel will detect the template and generate a bound chart in the Excel report. Additionally, DsExcel lets users specify the chart series data in the template cell during processing. Binding a chart to a template cell has significant advantages when the template is processed because the chart in the final report will always update as changes are made. DsExcel also allows the developer to choose whether to include the template charts in the same worksheet as the data.

To demonstrate last year's sales trends by brand, let's insert a chart in the car catalog template. To add a chart, select 'Insert' in the Excel menu. In the 'Charts' group, select the '2-D column chart'. Once the chart is placed on the sheet, specify the Chart Series by right-clicking on the chart, selecting the 'Select Data…' option, and clicking the 'Add' button to add a series. Since we want to show the total sales value for each car brand, set A15: O15 in 'Series name' and set C16 in 'Series values.' Now, specify the axis labels by setting 'Axis Label Range' to A16: B16. This axis label setting will show TotalSales as a label on the horizontal axis.

The final template chart defined above should look like the following:

Support for Images in Templates

Images not only grab a user's attention but in many data sources like product and client databases, they also provide essential information that is an integral part of the record. We are happy to announce that DsExcel supports images in Excel reports in this latest release.

Let's get back to our car catalog example. To show an image for each car model, allocate template cell 'A17' for the image in the Excel template, bind it to the image data field 'Picture,' and set its 'Image' property to true. To help define the layout, DsExcel includes the 'image.height' and 'image.width' properties, which allows you to specify the height and width of the image. When you're processing, DsExcel will automatically map the template cell to the database's image field and replace it with the image.

Therefore, defining cell A17 as {{dt.Picture(image=true, image.width=125px, image.height=150px)}}/ will add a picture of a car with width and height equal to 125 and 150 pixels for each record in the final Excel report.

Support for Conditional Formatting

Another common yet essential feature in an Excel report is conditional formatting. Without conditional formatting, reports appear dull, which is why DsExcel added support for this feature in Excel templates. You can apply conditional formatting in Excel templates the same way you would in a standard Excel file.

As we all know, engine capacity is a significant factor for car buyers. With that established, let's format the engine capacity field. We want the car models with a liter capacity of less than two to appear in red and the models that are more than or equal to four to appear in green.

To achieve this format, select the template cell 'J27' and select the 'Conditional Formatting' option (Excel -> Home -> Styles group) and select 'New Rule.' As you can see, you have multiple options to choose from in 'Rule Type.' We will choose the second option 'Format only cells that contain' and set the properties as shown below.

Process this template with DsExcel, and the final Excel report will maintain the conditional formatting. The conditional formatting rules applied to the template cells will also expand in synchronization with the template cells.

The above image also shows how conditional formatting similarly applies to the 'Transmission Automatic' data field.

By defining the Excel template using the above features, the final Excel template would look something like this

Download the final car catalog Excel template here.

Generating Professional Excel Reports

Now that we know how to use the enhanced Excel features and are aware of how to create an Excel template using DsExcel, let's build the Visual Studio application that will generate the final car catalog report in Excel.

Step 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' to continue.

Step 2. Setup C# Project

2.1 Add namespace

In the Program file, import the following namespace:

using GrapeCity.Documents.Excel;

2.2 Create a new workbook

In the primary function, add the following code to create a new DsExcel workbook.

Workbook workbook = new Workbook();

2.3 Load Excel Template

Use the Open method to load the existing Excel template file (placed in the Resources folder in the VS application) in the DsExcel workbook:

workbook.Open("Resources/CatalogTemplate.xlsx");

2.4 Initialize data

Load data that will fill in the data fields in the loaded Excel template:

DataProvider dataProvider = new DataProvider(); 
var data = dataProvider.GetCars();

Here, DataProvider is a custom class that connects to a database and returns a list of car models through the GetCars method.

For a complete list, you can download this file.

2.5 Add datasource

Use the AddDataSource method to load the data in workbook.

workbook.AddDataSource("dt", data);

2.6 Process Template

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

workbook.ProcessTemplate();

2.7 Save report to XLSX

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

workbook.Save("../../../CatalogReport.xlsx");

Invoking the Save method will dynamically generate a data-bound car catalog in XLSX format. Download the final car catalog here.

Visit the Help and Demo sections for more details.