.
...
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.
...
| 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);
|
Downloads
You can download the code for the Basic ExcelWriter Tutorials as a Visual Studio solution, which includes the Simple Expense Summary.
...