...
Note: At the top 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.
Info | ||
---|---|---|
| ||
In the downloadable ExcelWriter_Basic_Tutorials.zip under SimpleExpenseSummary, there is a completed template file is located in SimpleExpenseSummary/templates/part1_template.xlsx. |
1. Start with a blank .xlsx file. Save the file as template.xlsx.
...
We're done adding the data markers, so next we'll hook the template up to some data before we do any formatting.
...
Adding an ExcelWriter Reference in Visual Studio
Info | ||
---|---|---|
| ||
Info | ||
In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll has already been added to the SimpleExpenseSummary project. |
Create a .NET project and add a reference to the ExcelWriter library.
- Open Visual Studio and create a .NET project.
- The sample code uses a web application.
- Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
- SoftArtisans.OfficeWriter.ExcelWriter.dll is located under Program Files > SoftArtisans > OfficeWriter > dotnet > bin
Writing the Code
Info | ||
---|---|---|
| ||
There is a sample web application page |
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);
|
Info |
---|
If you want to import a row of data as a vertical column in Excel, you need to use |
7. Get the data for the Top 5 Expenses and All Expenses data sets.In this case, we chose to parse CSV files that contain query results from the AdventureWorks2008 database to make running the sample code easier.
Info | ||
---|---|---|
| ||
In the sample project, we are parsing CSV files with query results, rather than querying a live database. The CSV files are available under the data directory. There is a copy of the CSV parser, |
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"));
|
If you are following in your own project and would like to parse the CSV files as well, you will need to:
- Add a reference to
GenericParsing.dll
- Include
GeneringParsing
at the top of your code. - Add the
GetCSVData
method that can be found in the sample code.
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.
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);
|
...
3. Add some borders to the cells in the Top Expenses and All Expenses tables. Then format the column headers as desired. Below is a screen shot of the final template:
4. Run the code with the updated template file. Here's a screenshot of the output with all the formatting applied:
...
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.
- OfficeWriter-Basics-csharpExcelWriter_Basic_Tutorials.zip