.
...
The final template will look something like this:
| Info |
|---|
In the sample code, the completed template file is located in SimpleExpenseSummary/templates/part1_template.xlsx |
1. Start with a blank .xlsx file. Save the file as template.xlsx.
2. Add some text placeholders for data markers and table headers. 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 |
...
...
sample code, the completed template file is located in SimpleExpenseSummary/templates/part1_template.xlsx |
1. Start with a blank .xlsx file. Save the file as template.xlsx.
2. Add some data markers for Replace Fiscal Year, Division, and Group with data markers. These values will be a single row of a data set called "Header". The column names will be "FiscalYear", "Division" and "Group".
43. Next add data markers for Top Expenses and All Expenses, which will be table header columns:
- The data source name for Top Expenses will be "Top 5 Expenses" with column names "Description" and "Expenses".
- The data source name for All Expenses will be "All Expenses" with the same column names.
- Since the data source names have spaces, the data markers need to be in brackets.
...
We're done adding the data markers, so next we'll add some styles and formatting to the data markers.
Formatting cells with data markers
| Info |
|---|
Data markers take the formatting and style properties of the cell that they are in. This means if a data marker is bold, then the value that replaces the data marker will be bold as well. |
5. In the screen shot below we have made the %%=Header.FiscalYear cell font size 18, %%=Header.Division is bold, and %%=Header.Group is italic.
| Info |
|---|
When importing multiple rows of data, ExcelWriter will insert a new row in the worksheet for each row of data, starting from the row with the data markers. Each of the new rows will take on the styles and formatting of the cells that contain the data markers. For more details on this behavior see How ExcelWriter Inserts Rows. |
6. Since the 'Expenses' data will be currency values, add a currency number formatting to the cells containing the Expenses data markers. This number formatting will be repeated for each row of data that is inserted.
7. 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:
We're done creating the template. Now it's time to write the code.
hook the template up to some data before we do any formatting.
Writing the Code
| Info |
|---|
In the sample code, the a sample web application page |
...
Here is an example of the output from the sample code:
Formatting cells with data markers
| Info |
|---|
Data markers take the formatting and style properties of the cell that they are in. This means if a data marker is bold, then the value that replaces the data marker will be bold as well. |
1. In the screen shot below we have made the %%=Header.FiscalYear cell font size 18, %%=Header.Division is bold, and %%=Header.Group is italic.
| Info |
|---|
When importing multiple rows of data, ExcelWriter will insert a new row in the worksheet for each row of data, starting from the row with the data markers. Each of the new rows will take on the styles and formatting of the cells that contain the data markers. For more details on this behavior see How ExcelWriter Inserts Rows. |
2. Since the 'Expenses' data will be currency values, add a currency number formatting to the cells containing the Expenses data markers. This number formatting will be repeated for each row of data that is inserted.
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:
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).
...