Introduction

This part focuses on using ExcelApplication to create and populate a coversheet that includes user-supplied name, the current date, and a selected picturean image, hyperlinks, and formatted text. It also includes binding 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 user is provided with several logo options, one of which will be selected from the front endinserted into the sheet. The "Report For :" field will correspond with the supplied name, and finally, the "Date Executed :" will correspond with today's date.

Setup

1. Define a method to contain the ExcelApplication code to create a new worksheet and customize it. In the sample, there is an AddCoverSheet() method that holds the code for the ExcelApplicattion code in this part of the tutorial.

protected void AddCoverSheet()
{

}

2. You should have already completed Part 1 of this tutorial. To include the AddCoverSheet() method, just add a call in GenerateTemplate()

protected void GenerateTemplate()
{

	xla = new ExcelApplication();

	wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));

	for (int i = 0; i < selectedCountries.Count; i++)
	{
		wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
	}

	wb.Worksheets["SimpleTemplate"].Visibility = Worksheet.SheetVisibility.Hidden;

	wb.Worksheets[1].Select();

	/*********Part 2*************/
	AddCoverSheet();
	/**************************/

}

START HERE FOR REVIEW <<

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:

...