.
| Table of Contents | ||
|---|---|---|
|
...
| 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.
...
You will see that the first 4 characters from the description have been set to uppercase in column D.
Adding a Total Row
If an aggregate formula, such as TOTAL or SUM, references a data marker row, ExcelWriter will stretch the formula to include all the rows that have been inserted. In this section, we'll cover how to add a total row.
9. Insert a new row below Top Expenses and All Expenses. Add labels for the total row and format as desired.
10. In cell C8, add the formula =SUM(C7:C7). Add a similar formula to C13: =SUM(C12:C12). Don't forget to format C8 and C13 to use currency formatting.
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 it (C16:C16) and then will get stretched to C16:C44.
11. Run the report.
You will see that the formulas have been stretched and updated as described above.
Downloads
You can download the code for the Basic ExcelWriter Tutorials as a Visual Studio solution, which includes the Simple Expense Summary.
...