Table of Contents

Setting up the Template

About templates and data markers

An ExcelWriter template is an Excel file that contains ExcelWriter data markers. A datamarker is a cell value beginning with %%= that specifies a database colum, variable, or array to insert into the spreadsheet column. Data markers are added to a worksheet in Excel and then bound to data sources in code. ExcelWriter populates the data markers with values from the data sources when the code is executed.

ExcelWriter templates do not necessarily need to be in the Excel template file format (XLT, XLTX, XLTM), although these formats are supported by ExcelWriter.

Datamarker syntax

The basic syntax for a datamarker is %%=DataSourceName.ColumnName, where DataSourceName is the name of the data source and ColumnName is the name of the column of data in the data source. You need to follow these rules when naming data markers:

Adding data markers to the the template

The final template will look something like this:

In the sample code, the completed template file is located in SimpleExpenseSummary/templates/part1_template.xlsx

1. Start with a blank xlsx file. Save the file as template.xlsx.

2. Add some text placeholders for data markers and table headers. In the header of the worksheet, we will display the fiscal year, the company division and group. Below will be 2 tables: one to show the top 5 expenses and another to show all the expenses.

In the screen shot, Fiscal Year, Division, and Group will replaced by data markers. Top Expenses, All Expenses, Description, and Expenses are going to become table headers. The data markers for that data will go in the rows below.

3. Replace Fiscal Year, Division, and Group with data markers. These values are going to be a single row of a data set called "Header". The column names will be "FiscalYear", "Division" and "Group".

4. Next add data markers for Top Expenses and All Expenses:

We're done adding the data markers, so next we'll add some styles and formatting to the data markers.

Formatting cells with data markers

Data markers take the formatting and style properties of the cell that they are in. This means if a data marker is bold, then the value that replaces the data marker will be bold as well.

5. In the screen shot we have made the %%=Header.FiscalYear cell font size 18, %%=Header.Division is bold, and %%=Header.Group is italic.

When importing multiple rows of data, ExcelWriter will insert a new row in the worksheet for each row of data, starting from the row with the data markers. Each of the new rows will take on the styles and formatting of the cells that contain the data markers.

6. Since 'Expenses' will be currency values, add a currency number formatting to the cells containing the Expenses data markers. This number formatting will be repeated for row of data that is inserted.

7. Add some borders to the cells in the Top Expenses and All Expenses tables. Then format the table headers as desired. Below is a screen shot of the final image:

We're done creating the template. Now it's time to write the code.

Writing the Code

In the sample code, the a sample web application page Part1.aspx and code behind Part1.aspx.cs are available in the SimpleExpenseSummary directory.

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.

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

If you want to import a row of data as a vertical column in Excel, you need to use ExcelTemplate.BindColumnData and the data marker syntax %%=$DataSourceName.ColumnName, with a $ to denote that the data should be imported as a column instead of a row.

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:

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.

Next Steps

Continue on to Part 2: Working with Formulas