.
Table of Contents | ||||
|---|---|---|---|---|
|
Setting Up the Template
| Info | ||
|---|---|---|
| ||
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.
| Note |
|---|
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
Here is the starting template: 
The next few steps will demonstrate adding modifiers.
...
After the modifiers are added, the template should resemble this: 
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.
...
5. Click OK to save the rule.
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
...
Without the post processing, the populated file will persist the column width and heights. It should look something like this: 
Post-Processing
1. In the post-processing method, instantiate the ExcelApplication object:
...
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");
//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();
/*This next section handles the post-processing*/
//Instantiate ExcelApplication
ExcelApplication XLA = new ExcelApplication();
//Open the XLT object as a new workbook
Workbook wb = XLA.Open(XLT);
//Get the first worksheet
Worksheet ws = wb.Worksheets[0];
//Set the autofit width and height
ws.PopulatedCells.AutoFitWidth();
ws.PopulatedCells.AutoFitHeight();
//Save the file
XLA.Save(wb, Page.Response, "temp.xlsx", false);
|
Downloads
You can download the code for the Financial Report here.