.
Table of Contents | ||||
|---|---|---|---|---|
|
Intro
| Infonote |
|---|
This is Part 2 of a 3-part tutorial series for the Simple Expense Summary scenario. It is recommended that you complete Part 1 - Getting Started before starting this section. |
| Info | ||
|---|---|---|
| ||
There is a downloadable ExcelWriter_Basic_Tutorials.zip with completed templates and code. The completed example of the template is available under templates/part2_template.xlsx), page (Part2.aspx), and code behind (. The code for this part of the tutorial can be found in Part2.aspx.cs) are included in the SimpleExpenseSummary project available for download as part of the ExcelWriter Basics Tutorials. |
This part focuses on adding some Excel formulas to the template file from Part 1. Specifically, this covers combining data marker values with other text, adding a 'Total' row after imported data, and including formulas in imported data rows.
...
Right now, the header is set up just to display the values for FiscalYear, Division, and Group. Let's say we want to change the labels to "Expenses Summary - <Fiscal Year>", "Division: <Division>", "Group: <Group>".
...
Data markers must be the only content a cell, they cannot be combined with other text
...
.
...
To get around this, you need to build the text or formula with a reference to the cell the data marker occupies.
...
| Info |
|---|
For more information about how ExcelWriter imports data with the ExcelTemplate object, check out How ExcelWriter Inserts Rows: ExcelTemplate. BindData . That article also discusses how importing data with ExcelTemplate affects formulas. |
Let's say that we want to generate some short labels for the Top 5 Expenses to use with the descriptions. One possibility is to add the labels to the data source (i.e. through the SQL query or modifying the data structure directly), but another way is to use Excel formulas.
...
7. In cell D7, add a formula =UPPER(LEFT(B7, 4)).
Excel's LEFT(text, N) function returns the first N characters from the text, starting from the left. In this case, the first 4 characters from cell B7. Then the UPPER function converts the characters to uppercase.
...
When ExcelWriter imports the data, the first formula will get stretched to C7:C11. The second formula will first get updated to account for the new data inserted about above it (C16:C16) and then will get stretched to C16:C44.
...
You will see that the formulas have been stretched and updated as described above.
Final Code
| Csharp | 1 | 1 |
|---|
| Note |
For information on writing this code, see Part 1 - Getting Started . |
| 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);
|
| Vbnet | 1 | 1 |
Downloads
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