Page History

...

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:

 wb.Worksheets.CreateWorksheet("Summary", 0);

2. Create a Worksheet object of the worksheet that was just created.

Worksheet ws = wb.Worksheets["Summary"];

3. Hide the gridlines in the summary worksheet using Worksheet.ShowGridlines. By default, this property is set to true.

ws.ShowGridlines = false;

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.

underlined.Font.Underline = Font.UnderlineStyle.Single;

6. Create another GlobalStyle using the Workbook.CreateStyle() method called 'boldAndFont12'.

GlobalStyle boldAndFont12 = wb.CreateStyle();

7. Use the Style.Font to set the style font to 'Bold' and 'Size to 12.

 
boldAndFont12.Font.Bold = true;
boldAndFont12.Font.Size = 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 :".

ws.Cells["A15"].Value = "Date Executed :";
ws.Cells["A16"].Value = "Report For :";

9. Create another GlobalStyle called "dateForm" and use the NumberFormat.DateFormat method to create the desired date format.

GlobalStyle dateForm = wb.CreateStyle();
dateForm.NumberFormat = wb.NumberFormat.DateFormat.MonthDayYear;

10.