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.
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:
- Visual Studio Community or similar
- Microsoft Office
Creating a New Project in Visual Studio
Open Visual Studio, click Create New Project, then select Console App (.NET Core).
Click Next and name the project “StocksReport.”
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.
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.
Now open the portfolios.json file to see its contents.
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.
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:
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.
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.”
Then, create a title for the first row in the worksheet. In the third line, insert the name “Portfolio.”
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 |
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}}.
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) | $ |
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.
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:
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.
Rename the new sheet with the expression {{ds.Symbol}}.
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}} | % |
Again, run the application to see how it generates the new worksheet.
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.
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.
Rename the third worksheet "Price Growth.”
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).
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.
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.
After selecting the range, click Insert on the ribbon, click the Insert Line or Area Chart icon, and select the Line icon.
This action creates a line chart. Drag the new graph to the worksheet’s right margin.
Right-click a blank space within the chart and click Select Data.
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.
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.
Next, change the Axis label range field to =’Price Growth'!$B$6:$B$6.
Double-click the chart title and replace it with “Stock Price Growth (%)”:
Now, right-click the bottom label and select Format Axis.
In the Labels section, change Specify interval to 20 and Label Position to Low.
Now, click the plus sign beside the chart to open the Chart Elements window. Click the Legend option, and choose the Bottom position.
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.
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.
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.
Shilpa Sharma