Introduction

...

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

...

1. Open Visual Studio and create a .NET project.
   • The sample code uses a web application.
2. 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

...

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

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

Next Steps

Continue to Part 3: Combine Reports with CopySheet