Table of Contents | ||||
---|---|---|---|---|
|
Introduction
Note |
---|
This is Part 2 of the three-part tutorial series Financial Report scenario. It is recommended that you complete Part 1 - Sub-Report with Modifiers before starting this section. |
...
In this tutorial ExcelTemplate is being used to populate data in a template set up with styles and formats. This part of the tutorial uses formulas and persists Excel styles.
Setting Up the Template
Data Sheets
This sub-report makes use of a data sheet. This is where the data markers will go. It should look something like this:
...
The sum formula is a standard Excel function, which gets updated when ExcelWriter populates the file with data. When the data is populated, the A1:A1 reference gets updated to include all the rows of data. The result should be something like this (highlighting added for demonstration):
Referencing the Data Sheet
The next step is adding references to the data sheet. This example references the "SUM" formulas on the data sheet. These sums are added to a percent change equation. This will result in a template resembling the following:
Note the formula in the formula bar, "Sheet2" is the data sheet.
...
8. Repeat with rows 4 and 6
Number Formats
The value returned by the percent change equation should be displayed as a percentage. The table cells all have to be formatted.
...
3. Select "Percentage" on the Number tab.
Setting Styles
The next step is setting up the table. Add a header and label the rows and columns to end up with a complete table:
Once the table is complete, add styles.
...
The final template should look like this:
Adding an ExcelWriter Reference in Visual Studio
Info | ||
---|---|---|
| ||
In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll has already been added to the CompleteFinancialReport project. |
...
- Open Visual Studio and create a .NET project.
- The sample code uses a web application.
- Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
- SoftArtisans.OfficeWriter.ExcelWriter.dll is located under Program Files > SoftArtisans > OfficeWriter > dotnet > bin
Writing the Code
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind
...
Code Block |
---|
DataBindingProperties dataProps = XLT.CreateDataBindingProperties(); |
Data Binding
1.Get the data for the Assets, Losses, and Other datasets
...
The final output should look something like this:
Final Code
Code Block |
---|
using SoftArtisans.OfficeWriter.ExcelWriter; ... //Instantiate the template object ExcelTemplate XLT = new ExcelTemplate(); //Open the file XLT.Open(Page.MapPath("//templates//Part1_Financial_Template.xlsx")); //Create data binding properties DataBindingProperties bindingProps = XLT.CreateDataBindingProperties(); //Get the data from the CSVs. More info about the generic parser is available //in the project and in the tutorial above. DataTable dtAssets = GetCSVData("//data//Assets.csv"); DataTable dtLosses = GetCSVData("//data//Losses.csv"); DataTable dtOther = GetCSVData("//data//Other.csv"); //Bind each datatable XLT.BindData(dtAssets, "Assets", bindingProps); XLT.BindData(dtLosses, "Losses", bindingProps); XLT.BindData(dtOther, "Other", bindingProps); //Call process to import data to file XLT.Process(); //Call save XLT.Save(Page.Response, "temp.xlsx", false); |
Downloads
TBA