...
Csharp |
---|
|
ExcelTemplate XLT = new ExcelTemplate();
|
3. Open the template file from earlier with the ExcelTemplate.Open() method.
Csharp |
---|
|
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.
Csharp |
---|
|
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.
Csharp |
---|
|
//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.
Csharp |
---|
|
XLT.BindRowData(valuesArray, columnNamesArray, "Header", dataProps);
|
Info |
---|
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.
Csharp |
---|
|
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.
Csharp |
---|
|
XLT.BindData(dtTop5, "Top 5 Expenses", dataProps);
XLT.BindData(dtAll, "All Expenses", dataProps);
|
9. Call ExcelTemplate.Process() to import the data into the file.
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.
Csharp |
---|
|
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
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