...

ExcelTemplate XLT = new ExcelTemplate();

3. Open the template file from earlier with the ExcelTemplate.Open() method.

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

4. Create a DataBindingProperties object. Although we won't be changing any of the binding properties, a DataBindingProperties is a required parameter in all ExcelTemplate data binding methods.

DataBindingProperties dataProps = XLT.CreateDataBindingProperties();

5. Create a object array for the header values and a string array for the column names.

ExcelTemplate can be bound to numerous types of .NET data structures: single variables, arrays (1-D, jagged, multi-dimensional), DataSet, DataTable, IDataReader etc. The source of the data can come from anywhere.

Some of the aforementioned structures have built in column names, such as the DataTable. When working with arrays, which don't have built in column names, you have to define the column names in a separate string array.

//This report is for FiscalYear: FY 2004, Division: Canadian Division, Group: Research and Development
object[] valuesArray = { "FY 2004", "Canadian Division", "Research and Development" };

//The column names are FiscalYear, Division, Group
string[] columnNamesArray = { "FiscalYear", "Division", "Group" };

6. Use the ExcelTemplate.BindRow() method to bind the header data to the data markers in the template file (%%=Header.FiscalYear, %%=Header.Division, %%=Header.Group).

BindRowData() binds a single row of data to the template, but the data markers in the template do not need to be in a row.

XLT.BindRowData(valuesArray, columnNamesArray, "Header", dataProps);

7. Get the data for the Top 5 Expenses and All Expenses data sets.

In this case, we chose to parse CSV files that contained query results from the AdventureWorks2008 database. These calls are to a helper method GetCSVData that parses the CSV files and returns a DataTable with the values.

DataTable dtTop5 = GetCSVData(Page.MapPath("//data//Part1_Top5Expenses.csv"));
DataTable dtAll = GetCSVData(Page.MapPath("//data//Part1_AllExpenses.csv"));

8. Use ExcelTemplate.BindData() to bind the data for the Top 5 Expenses and All Expenses data sets.

Recall that the data source names (/Top 5 Expenses/, /All Expenses/) need to match the data marker names exactly.

XLT.BindData(dtTop5, "Top 5 Expenses", dataProps);
XLT.BindData(dtAll, "All Expenses", dataProps);

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

XLT.Process();

10. Call ExcelTemplate.Save() to save the output file.

ExcelTemplate has several output options: save to disk, save to a stream, stream the output file in a page's Response inline or as an attachment.

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

11. Run your code.

Here is an example of the output from the sample code:

Image Added

Note: The formatting has been applied to the values that replaced the data markers, including the data sets with multiple rows. Also note that the Top 5 Expenses and All Expenses tables have expanded to accomodate the new rows of data (i.e. All Expenses was pushed down when the Top 5 Expenses data was imported).

Final Code

Add code here

Downloads

using SoftArtisans.OfficeWriter.ExcelWriter;
...
ExcelTemplate XLT = new ExcelTemplate();

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

DataBindingProperties dataProps = XLT.CreateDataBindingProperties();

object[] valuesArray = { "FY 2004", "Canadian Division", "Research and Development" };
string[] columnNamesArray = { "FiscalYear", "Division", "Group" };

XLT.BindRowData(valuesArray, columnNamesArray, "Header", dataProps);

DataTable dtTop5 = GetCSVData(Page.MapPath("//data//Part1_Top5Expenses.csv"));
DataTable dtAll = GetCSVData(Page.MapPath("//data//Part1_AllExpenses.csv"));

XLT.BindData(dtTop5, "Top 5 Expenses", dataProps);
XLT.BindData(dtAll, "All Expenses", dataProps);

XLT.Process();

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

Downloads

Next Steps