.
...
| Info | ||
|---|---|---|
| ||
In the downloadable ExcelWriter_Basic_Tutorials.zip under SimpleExpenseSummary, there is a completed template file located in SimpleExpenseSummary/templates/part1_template.xlsx. |
...
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind
| Code Block |
|---|
using SoftArtisans.OfficeWriter.ExcelWriter;
|
2. In the method that will actually run the report, instantiate the ExcelTemplate object.
| Code Block |
|---|
ExcelTemplate XLT = new ExcelTemplate();
|
3. Open the template file with the ExcelTemplate.Open method.
| Code Block |
|---|
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.
| Code Block |
|---|
DataBindingProperties dataProps = XLT.CreateDataBindingProperties();
|
...
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.
| Code Block |
|---|
//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" };
|
...
BindRowData() binds a single row of data to the template, but the data markers in the template do not need to be in a single row.
| Code Block |
|---|
XLT.BindRowData(valuesArray, columnNamesArray, "Header", dataProps);
|
...
These calls are to a helper method GetCSVData that parses the CSV files and returns a DataTable with the values.
| Code Block |
|---|
DataTable dtTop5 = GetCSVData(Page.MapPath("//data//Part1_Top5Expenses.csv"));
DataTable dtAll = GetCSVData(Page.MapPath("//data//Part1_AllExpenses.csv"));
|
...
Recall that the data source names ([Top 5 Expenses], [All Expenses]) need to match the data marker names exactly.
| Code Block |
|---|
XLT.BindData(dtTop5, "Top 5 Expenses", dataProps);
XLT.BindData(dtAll, "All Expenses", dataProps);
|
9. Call ExcelTemplate.Process() to import the data into the file.
| Code Block |
|---|
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.
| Code Block |
|---|
XLT.Save(Page.Response, "Part1_Output.xlsx", false);
|
...
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 accommodate the new rows of data (i.e. All Expenses was pushed down when the Top 5 Expenses data was imported).
Final Code
| Code Block |
|---|
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);
|
...
You can download the code for the Basic ExcelWriter Tutorials as a Visual Studio solution, which includes the Simple Expense Summary.