Creating Excel reports in .NET can be a pain, especially if we want to build something complex, like a financial report. Suppose we used only C# code to generate all the necessary formulas, calculations, and charts. That would be quite a cumbersome task, with plenty of trial-and-error as we couldn't visualize the results.

Libraries like ClosedXML can help, but they are low-level and require too much code to build a report.

We would instead create an Excel template, then use code to inject data into the template, and that's what we will do in this article’s tutorial. Fortunately, GrapeCity's Documents for Excel .NET Edition enables us to use templates to bind Excel documents to data and generate Excel reports with advanced layouts.

Custom Code Your Spreadsheets in .NET

Download the latest version of GrapeCity Documents for Excel

Download Now!

Creating a New .NET 5 Project

This tutorial uses a simple .NET 5 console application to create a stock portfolio report. This report combines actual stock data with a hypothetical portfolio containing five stocks (AAPL, AMZN, GOOG, FB, and MSFT). We’ll create a template for that finance report with plenty of code samples and screenshots highlighting the generation of spreadsheet numbers and charts.

To create a fully working .NET 5 report application from the beginning, follow the steps in this tutorial. Alternatively, you can download the source code from this GitHub repository.

System Requirements

You will need the following software on your development machine:

Creating a New Project in Visual Studio

Open Visual Studio, click Create New Project, then select Console App (.NET Core).

Doc

Click Next and name the project “StocksReport.”

Doc

Select the StocksReport project name and press Alt + Enter or right-click to see the project’s properties. Then select .NET 5 from the Target framework list.

Doc

Adding Stock Portfolio Data

Download the stock portfolio data from this GitHub URL. Then, right-click the project name, select Add / Existing Item, and find the portfolios.json file you just downloaded.

Right-click the portfolios.json file in Solution Explorer and change the Build Action property to Content and the Copy to Output property to Copy if newer.

Doc

Now open the portfolios.json file to see its contents.

Code

Code

The porfolios.json file has structured JSON content that you’ll use in the .NET project. Before using these data with the GrapeCity Document API, map the JSON structure into some .NET classes, as follows.

Doc

First, create a new Model folder in the project and double-click the Model folder. Then, click Add > New Item > Class and create two new class files named Portfolio.cs and SymbolPriceHistory.cs:

Doc

Now, include this class declaration in the Portfolio.cs file:

    public class Portfolio  
    {  
        public string Name { get; set; }  
        public string Symbol { get; set; }  
        public double LastPrice { get; set; }  
        public double Change { get; set; }  
        public int Shares { get; set; }  
        public double Price { get; set; }  
        public double Cost { get; set; }  
        public double Value { get; set; }  
        public double Gain { get; set; }  
        public List<SymbolPriceHistory> PriceHistory { get; set; }  
    }

Also, add this declaration to the SymbolPriceHistory.cs file:

    public class SymbolPriceHistory  
    {  
        public DateTime PriceDate { get; set; }  
        public string PriceMonth { get { return PriceDate.ToString("MMM-yyyy"); } }  
        public string Symbol { get; set; }  
        public double Price { get; set; }  
        public double PriceGrowth { get; set; }  
    }

Creating an Excel Template for a Finance Report

Generating reports is a fundamental task for many business departments, such as Accounting, Finance, Human Resources, and Marketing. A company periodically generates each type of Excel report with updated data sets. However, copying and pasting or loading data from external sources into Excel is often tedious and prone to errors, so Excel templates are helpful. A program can process these templates and generate accurate reports with minimal effort.

First, open MS Excel, create a new blank workbook, and save it in the project’s directory with the name “Stocks_Report_Template.xlsx.” Then, right-click the Stocks_Report_Template.xlsx file and change the Build Action property to Content and the Copy to Output property to Copy if newer.

Doc

Creating the Stock Portfolio Worksheet

In Microsoft Visual Studio, open the Stocks_Report_Template.xlsx file in Solution Explorer, and rename the first worksheet “Stock Portfolios.”

Doc

Then, create a title for the first row in the worksheet. In the third line, insert the name “Portfolio.”

Doc

Now, add text to the column headers for the report columns, as shown below.

Cell Value
A5 Company
B5 Symbol
C5 Last Price
D5 Change
E5 Shares
F5 Price
G5 Cost
H5 Value
I5 Gain

Doc

Next, add the following expressions to the corresponding cells.

Cell Value Format
A7 {{ds.Name}} -
B7 {{ds.Symbol}} -
C7 {{ds.LastPrice}} $
D7 {{ds.Change}} %
E7 {{ds.Shares}} -
F7 {{ds.Price}} $
G7 {{ds.Cost}} $
H7 {{ds.Value}} $
I7 {{ds.Gain}} %

Important: While copying these values to your worksheet, be sure to remove the blank spaces within the cells. Otherwise, Excel concatenates the numeric and date values with blank spaces and treats them as improperly-formatted strings. This result can affect other formulas that depend on those cells.

Note that we have enclosed the values above with pairs of braces (curly brackets) “{{” and “}}.” GrapeCity Documents for Excel uses this syntax to identify template fields within a worksheet and process them to generate the final values. The fields above also follow the syntax known as “data fields.” So, the fields map directly to a specific property of the data set (ds).

You can also combine data fields with functions or expressions. For example, you could fill other cells with values such as {{=Count(ds.Shares)}} or {{=ds.Shares * ds.Price}}.

Doc

Now, implement a formula to calculate the sum of all stock costs.

Modify these two cells as follows:

Cell Value Format
F9 Total Cost: -
G9 =SUM(G7:G7) $

Doc

Injecting Data into the Excel Template

This section shows you how to take data from an external source, load it in a .NET 5 app, and inject it into the template you created in the previous step.

First, install GrapeCity’s .NET 5 Excel Spreadsheet API.

Right-click the StocksReport project name, click Manage NuGet Packages, then look for GrapeCity.Documents.Excel on the Browse tab. Select it and click Install.

Doc

According to GrapeCity pages:

“A .NET Excel spreadsheet API library is a programming interface that allows developers to create and manipulate Excel documents programmatically at scale. GrapeCity Documents for Excel is a solution that allows developers to import/export, create reports & templates, and deploy spreadsheets across desktop applications.”

Open the Program.cs file in your Console application and include these declarations to import dependency namespaces:

using GrapeCity.Documents.Excel;  
using Newtonsoft.Json;  
using StocksReport.Model;  
using System.Data;  
using System.IO;  
using System.Linq;  
using System.Collections.Generic;

Now, add the following code to declare and initialize the workbook object using the Workbook class from GrapeCity Documents for Excel:

Workbook workbook = new Workbook();  

Add the following code to load the Stocks_Template.xlsx template workbook from the \bin\Debug\net5.0 folder:

var templateFile = "Stocks_Report_Template.xlsx";  
workbook.Open(templateFile);  

You can use GrapeCity Documents for Excel with multiple types of data sources, including custom objects, data sets, data tables, and JSON variables.

This project uses the custom object of the type List, so, add the following lines to deserialize the JSON file into a .NET list:

string stocksText = File.ReadAllText("portfolios.json");  
var portfolios = (List<Portfolio>)JsonConvert.DeserializeObject(stocksText, typeof(List<Portfolio>));

Now, add a data source to the workbook object. Here, "ds" is an alias for the data source, which you use throughout your template to define fields, such as {{ds.Symbol}}:

workbook.AddDataSource("ds", portfolios);  

Now, add the following line to process the template:

workbook.ProcessTemplate();  

You must still write the complete Excel report to a disk.

Include the following line to save to an Excel workbook file generated by the GrapeCity Documents API:

workbook.Save("Stocks_Report.xlsx");

The Stocks_Evolution_Template.xlsx template is now bound to your portfolio’s custom object and generated Stocks_Report.xlsx file.

Now run the console application to generate the report. Once the application finishes without errors, open the Stocks_Report.xlsx file in the \bin\Debug\net5.0 folder to view the result—a complete report based on the template, displaying all the data used as a data source:

Doc

You can see that the values you added remain precisely like those in the template. However, GrapeCity Documents for Excel replaces all the fields in the report with the actual data from the data set. Also, note that the report lines expand downward so that each row in the sheet represents a different item from the original data source. To accommodate the new lines, this expansion also vertically displaces the cells that display the total cost.

Creating Company Stock History Worksheets

To explore additional template capabilities, right-click the Stocks Portfolios sheet, then select Move or Copy. Then, select the Create a copy checkbox.

Doc

Rename the new sheet with the expression {{ds.Symbol}}.

Doc

As with the first worksheet, fill each of the following cells with their respective values and apply the appropriate formats. Note that some are literal text, while others are expressions the application resolves as it generates the report.

Cell Value Format
A3 Price History - {{ds.Symbol}} -
A5 Date DateTime
B5 Month-Year -
C5 Price -
D5 Price Growth (%) -
A7 {{ds.PriceHistory.PriceDate}} -
B7 {{ds.PriceHistory.PriceMonth}} -
C7 {{ds.PriceHistory.Price}} $
D7 {{ds.PriceHistory.PriceGrowth}} %

Doc

Again, run the application to see how it generates the new worksheet.

Doc

As you can see, the application generated not one but five new worksheets. Therefore, the {{ds.Symbol}} expression you defined as the worksheet’s name is evaluated using the data. Since the data source is a collection with five symbols, the application generates one new worksheet for each.

Using expressions as worksheet names is particularly helpful to segment and organize data in multiple worksheets instead of a single, long report.

Doc

Creating a Compared Price Growth Worksheet

To create a third template worksheet, repeat the previous step by right-clicking the last worksheet name and selecting Move or Copy. Then, select the Create a copy checkbox.

Doc

Rename the third worksheet "Price Growth.”

Doc

Now, fill the cells in the worksheet template with the following values and formats:

Cell Value Format
A3 Compared Price Growth -
A5 Date -
B5 Month-Year -
C5 {{ds.Symbol(E=H)}} -
A6 {{ds.PriceHistory.PriceDate}} -
B6 {{ds.PriceHistory.PriceMonth}} -
C6 {{ds.PriceHistory.PriceGrowth}} %

Note that the C5 cell has a strange value, {{ds.Symbol(E=H)}}. The fields expand vertically by default, but in this case, you’re telling the GrapeCity component to expand horizontally (E=H).

Doc

Once you run the app and open the generated report, you can see how each stock symbol (C5 cell) has produced a new column expanded to the right. Also, note how the expression in the C5 cell replicates itself and extends the C6 cell horizontally.

Doc

Creating a Stock Price Growth Chart

To see how GrapeCity Documents for Excel quickly generates charts from templates, select the range of cells from A5 to C6.

Doc

After selecting the range, click Insert on the ribbon, click the Insert Line or Area Chart icon, and select the Line icon.

Doc

This action creates a line chart. Drag the new graph to the worksheet’s right margin.

Doc

Right-click a blank space within the chart and click Select Data.

Doc

You must ensure the {{ds.Symbol(E=H)}} cell falls into the Legend Entries (Series) box. Click the Switch Row/Column button to make that change.

Doc

Doc

You also must modify the Horizontal (Category) Axis Labels box to display a few price months instead of each price date. First, click the Edit button on the right.

Doc

Next, change the Axis label range field to =’Price Growth'!$B$6:$B$6.

Doc

Doc

Double-click the chart title and replace it with “Stock Price Growth (%)”:

Doc

Now, right-click the bottom label and select Format Axis.

Doc

In the Labels section, change Specify interval to 20 and Label Position to Low.

Doc

Now, click the plus sign beside the chart to open the Chart Elements window. Click the Legend option, and choose the Bottom position.

Doc

Next, save the Excel workbook and rerun the Visual Studio project. Open the workbook that the template generates in the \bin\Debug\net5.0 folder and select the new line chart.

Doc

Suppose you remove the series and leave only AMZN. In that case, you can compare it to the AMZN stock performance that a financial service somewhere on the web generates. You can verify how the curves match in the same period.

Doc

Next Steps

You’ve learned how to easily create a simple .NET solution that takes stock information from a data source and injects it into a flexible, templated Excel workbook. From there, you’ve seen it generate a complete Excel-based stock portfolio report with formulas and charts. And, you have experienced how GrapeCity Documents for Excel .NET Edition (GcExcel.NET) provides developers with a comprehensive API to manipulate Microsoft Excel spreadsheets easily.

GcExcel.NET is compatible with Console Application, WinForms, WPF, ASP.NET, and ASP.NET Core. It enables you to leverage Excel features such as filter tables and filter pivot tables and add chart sparklines, conditional formats, and dashboard reports.

If you are ready to learn more about a spreadsheet solution for developers that works with all the leading platforms while maintaining a small footprint and high performance, consider GrapeCity Documents for Excel .NET Edition.

Custom Code Your Spreadsheets in .NET

Download the latest version of GrapeCity Documents for Excel

Download Now!