...
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind
Csharpcode | ||
---|---|---|
1 | 1 | using SoftArtisans.OfficeWriter.ExcelWriter; |
Vbnet | 1 | 1 |
2. In the method that is going to actually run the report, instantiate the ExcelTemplate object.
Csharpcode | ||
---|---|---|
2 | 2 | ExcelTemplate XLT = new ExcelTemplate(); |
Vbnet | 2 | 2 |
3. Open the template file from earlier with the ExcelTemplate.Open method.
Csharpcode | ||
---|---|---|
3 | 3 | XLT.Open(Page.MapPath("//templates//part1_template.xlsx")); |
Vbnet | 3 | 3 |
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.
Csharpcode | ||
---|---|---|
4 | 4 | DataBindingProperties dataProps = XLT.CreateDataBindingProperties(); |
Vbnet | 4 | 4 |
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.
Csharpcode | ||
---|---|---|
5 | 5 | //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" }; |
Vbnet | 5 | 5 |
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.
Csharpcode | ||
---|---|---|
6 | 6 | XLT.BindRowData(valuesArray, columnNamesArray, "Header", dataProps); |
Vbnet | 6 | 6 |
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 |
...
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.
Csharpcode | ||
---|---|---|
7 | 7 |
DataTable dtTop5 = GetCSVData(Page.MapPath("//data//Part1_Top5Expenses.csv"));
DataTable dtAll = GetCSVData(Page.MapPath("//data//Part1_AllExpenses.csv"));
|
Vbnet | 7 | 7 |
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.
Csharpcode | ||
---|---|---|
8 | 8 | XLT.BindData(dtTop5, "Top 5 Expenses", dataProps); XLT.BindData(dtAll, "All Expenses", dataProps); |
Vbnet | 8 | 8 |
9. Call ExcelTemplate.Process() to import the data into the file.
Csharpcode | ||
---|---|---|
9 | 9 | XLT.Process(); |
Vbnet | 9 | 9 |
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.
Csharpcode | ||
---|---|---|
10 | 10 | XLT.Save(Page.Response, "Part1_Output.xlsx", false); |
Vbnet | 10 | 10 |
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
Csharpcode | ||
---|---|---|
11 | 11 | 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); |
Vbnet | 11 | 11 |
Downloads
You can download the code for the Basic ExcelWriter Tutorials as a Visual Studio solution, which includes the Simple Expense Summary.
...