...

1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind

using SoftArtisans.OfficeWriter.ExcelWriter;

2. In the method that is going to actually run the report, instantiate the ExcelTemplate object.

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.

...

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.BindRowData 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);

...

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:

...

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

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

You can download the code for the Basic ExcelWriter Tutorials as a Visual Studio solution, which includes the Simple Expense Summary.

...