Introduction
This part focuses on using ExcelApplication to create and populate a coversheet that includes user-supplied name, the current date, and a selected picture. It also includes data binding with ExcelTemplate.
Because this part of the tutorial demonstrates further capabilities of ExcelApplication, the code behind will be modified.
Writing the Code
Writing the code for the cover sheet comes in two parts. the first part uses ExcelApplication
to create a coversheet; and the second part uses ExcelTemplate
to bind the appropriate data to the data markers on the cover sheet.
Creating the coversheet with ExcelApplication
Scoping the coversheet
We want to create a cover sheet that looks like the following Excel worksheet:
Ultimately, the cells below the Table of Contents will be populated with hyperlinks to country sheets in the workbook. The logo will be selected from the front end. The "Report For :" field will correspond with the supplied name, and finally, the "Date Executed :" will correspond with today's date.
Executing the coversheet
1. Following the line {{wb.Worksheets0.Visibility = Worksheet.SheetVisibility.Hidden; }}, use the Worksheets.CreateWorksheet method to create a new worksheet called "Summary" at the beginning of the workbook:
2. Create a Worksheet object of the worksheet that was just created.
3. Hide the gridlines in the summary worksheet using Worksheet.ShowGridlines. By default, this property is set to true
.
4. Create a GlobalStyle using the Workbook.CreateStyle() method. ExcelWriter uses three Styles: CellStyle. GlobalStyle
, and NamedStyle. The GlobalStyles
type is a Style
that can be applied to cells and areas throughout the workbook.
newcode}}GlobalStyle underlined = wb.CreateStyle();{{newcode
5. Set the underlined GlobalStyle
to Font.UnderlineStyle to Single
. ExcelApplication allows for various underline types.
6. Create another GlobalStyle
using the Workbook.CreateStyle()
method called 'boldAndFont12
'.
7. Use the Style.Font to set the style font to 'Bold
' and 'Size
to 12.
8. Use the Cell.Value method to set the value of cell A15 to the string "Date Executed :". Cell.Value
must be a string.
Use the Cell.Value
method again to set the value of cell A16 to the string "Report For :".
9. Create another GlobalStyle
called "dateForm" and use the NumberFormat.DateFormat method to create the desired date format.
10.