Table of Contents

Setting Up the Template

In the downloadable ExcelWriter_Basic_Tutorials.zip, there is a completed template file located in CompleteFinancialReport/templates/Part1_Financial_Template.xlsx.

Getting Started

In this tutorial ExcelTemplate is being used to populate data and ExcelApplication is being used to format the data. This part of the tutorial will make use of data marker modifiers.

This example assumes an understanding of ExcelTemplate. If you are not familiar with how to set up an Excel template with data markers, please go through the Simple Expense Summary first.

Using Modifiers

I would have probably started with a template that has static, fully-named data markers (with screen shot). User should be provided with a copy of the static template. Then slowly make the page more dynamic by switching in the ordinal syntax for the columns of data and the column headers. Then add the field name modifier to the column headers. After that, switch to the header and show off the optional modifier. Provide the user with a screen shot of the completed template.

Here is the starting template: ExcelWriter v8 Docs > Part 1 - Using Modifiers and Ordinal Syntax > BeforeModifiers.png
The next few steps will demonstrate adding modifiers.

This template uses two different data marker modifiers - fieldname and optional. Modifiers are added in parentheses at the end of a data marker. They alter the binding behavior of the data marker.

The fieldname modifier shows the fieldname of the column being bound. It will not bind any additional data. It is used like this:
ExcelWriter v8 Docs > Part 1 - Using Modifiers and Ordinal Syntax > datamarkers.png

The optional modifier allows that data marker to be ignored on data binding. The optional modifier allows you to bind data if the column might be empty. It is used like this:

The optional modifier allows the data marker to be ignored if there is no data corresponding with that column. << This should be the first thing covered. Saying that the marker will be ignored is misleading because it sounds like it will be ignored or removed all the time. Also, it is important to note that the physical columns will still be there - ExcelWriter doesn't remove the extra cells.

ExcelWriter v8 Docs > Part 1 - Using Modifiers and Ordinal Syntax > OptionalModifier.png

After the modifiers are added, the template should resemble this: ExcelWriter v8 Docs > Part 1 - Using Modifiers and Ordinal Syntax > FullTemplate.png

Move to part 2

Conditional Formatting

ExcelTemplate will persist conditional formatting in a template. In this tutorial, conditional formatting is applied to the "Other" table. It sets negative numbers to be red and bold.

1. On the "Home" tab in Excel, click on "Conditional Formatting"

2. Select "New Rule..."

3. In this tutorial the condition type is "Format only cells that contain..." The rule is "Cell value less than 0"

4. Click on "Format..." Set the text to be dark red. Set the typeface to be bold.

5. Click OK to save the rule.

ExcelWriter v8 Docs > Part 1 - Using Modifiers and Ordinal Syntax > ConditionalFormatting.png

Adding an ExcelWriter Reference in Visual Studio

In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll has already been added to the CompleteFinancialReport project.

Create a .NET project and add a reference to the ExcelWriter library.

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

using SoftArtisans.OfficeWriter.ExcelWriter;

2. In the method that will run the report, instantiate the ExcelTemplate object.

ExcelTemplate XLT = new ExcelTemplate();

3. Open the template file with the ExcelTemplate.Open method.

XLT.Open(Page.MapPath("//templates//Part1_Financial_Template.xlsx"));

4. Create a DataBindingProperties object. None of the binding properties will be changed for this tutorial, but DataBindingProperties is a required parameter in ExcelTemplate data binding methods.

DataBindingProperties dataProps = XLT.CreateDataBindingProperties();

Data Binding

1.Get the data for the Assets, Losses, and Other datasets

In the sample project, we are parsing CSV files with query results, rather than querying a live database. The CSV files are available under the data directory. There is a copy of the CSV parser, GenericParsing.dll in the bin directory of the project GetCSVData is defined in Part1.aspx.cs in a region marked Utility Methods.

These calls are to a helper method GetCSVData that parses the CSV files and returns a DataTable with the values.

If you are following in your own project and would like to parse the CSV files as well, you will need to:

Add a reference to GenericParsing.dll
Include GeneringParsing at the top of your code.
Add the GetCSVData method that can be found in the sample code.

DataTable dtAssets = GetCSVData("//data//Assets.csv");
DataTable dtLosses = GetCSVData("//data//Losses.csv");
DataTable dtOther = GetCSVData("//data//Other.csv");

2. Create the datasets for the header row. Recall the optional modifier for the "Division" tag. This tutorial will not bind any data for that tag to demonstrate the function.

//Create the array of header values. This example only binds a single item
string[] headerValues = { "2011" };

//Create the array of header names.
string[] headerNames = { "FiscalYear" };

3. Use ExcelTemplate.BindData to bind the data for the Assets, Losses, and Other data sets.

XLT.BindData(dtAssets, "Assets", bindingProps);
XLT.BindData(dtLosses, "Losses", bindingProps);
XLT.BindData(dtOther, "Other", bindingProps);

4. Use the ExcelTemplate.BindRowData method to bind the header data to the data markers in the template file (i.e. %%=Header.FiscalYear).

XLT.BindRowData(headerValues, headerNames, "Header", bindingProps);

5. Call ExcelTemplate.Process() to import all data into the file.

XLT.Process();

6. Call ExcelTemplate.Save() to save the final output

XLT.Save(Page.Response, "temp.xlsx", false);

The final output should look something like this: ExcelWriter v8 Docs > Part 1 - Using Modifiers and Ordinal Syntax > PostProcessing.png

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");


//Declare the row data. This tutorial uses a single item array to demonstrate the
//optional modifier
string\[\] headerValues = { "2011" };
string\[\] headerNames = { "FiscalYear" };


//Bind each datatable
XLT.BindData(dtAssets, "Assets", bindingProps);
XLT.BindData(dtLosses, "Losses", bindingProps);
XLT.BindData(dtOther, "Other", bindingProps);

//Bind the single row data
XLT.BindRowData(headerValues, headerNames, "Header", bindingProps);


//Call process to import data to file
XLT.Process();


//Save the file
XLT.Save(Page.Response, "temp.xlsx", false);