.
...
- formula stretching
- formula updating
- using formulas to concatenate strings and data marker values
| Table of Contents | ||
|---|---|---|
|
Intro
| Info |
|---|
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 |
|---|
The sample code template (part2_template.xlsx), page (Part2.aspx), and code behind (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.
Since the formulas are native Excel functionality, we will only be modifying the template file. There are no changes to the code from Part 1.
Combining Data Markers with Text
We start with the template file as it was at the end of Part 1:
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>".
| Info |
|---|
Data markers cannot be combined with other text because ExcelWriter does not have the ability to parse text and formulas for data markers. Data markers must be in their own cells. |
To get around this, you need to build the text or formula with a reference to the cell the data marker occupies.
1. Move the data markers for %%=Header.FiscalYear, %%=Header.Division, %%=Header.Group to a column off to the right, say Column N.
2. In A1, replace %%=Header.FiscalYear with a formula that builds the desired string with the formula ="Expenses Summary - " & N1.
3. In B2 and B3, replace %%=Header.Division and %%=Header.Group with similar formulas: ="Division: " & N2, ="Group: " & N3.
4. Hide column N so the extra data markers won't be visible.
5. Run the code.
In the output you will see the text combined with the values of the data markers:
Adding a Total Row
Using In-Row Formulas
Downloads
You can download the code for the Basic ExcelWriter Tutorials as a Visual Studio solution, which includes the Simple Expense Summary.
- OfficeWriter-Basics-csharp.zip