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