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
Warning |
---|
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. |
...
After the modifiers are added, the template should resemble this:
Warning |
---|
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.
...
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
...
Warning |
---|
Move to part 3 with the rest of ExcelApplication. Mixing and matching too much doesn't make sense. |
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.