.
...
| Info | ||
|---|---|---|
| ||
In the downloadable ExcelWriter_Basic_Tutorials.zip, there is a completed template file located in CompleteFinancialReport/templates/Part1_Financial_Template.xlsx. |
...
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind
| Code Block |
|---|
using SoftArtisans.OfficeWriter.ExcelWriter;
|
...
3. Open the template file with the ExcelTemplate.Open method.
| Code Block |
|---|
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.
| Code Block |
|---|
DataBindingProperties dataProps = XLT.CreateDataBindingProperties();
|
...
These calls are to a helper method GetCSVData that parses the CSV files and returns a DataTable with the values.
| Code Block |
|---|
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.
| Code Block |
|---|
//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.
| Code Block |
|---|
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).
| Code Block |
|---|
XLT.BindRowData(headerValues, headerNames, "Header", bindingProps);
|
5. Call ExcelTemplate.Process() to import all data into the file.
| Code Block |
|---|
XLT.Process();
|
6. Call ExcelTemplate.Save() to save the final output
...
The final output should look something like this: 
Final Code
| Code Block |
|---|
using SoftArtisans.OfficeWriter.ExcelWriter;
using GenericParsing;
...
//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);
|
...
You can download the code for the Financial Report here.