Introduction

This part focuses on using ExcelApplication to create and a coversheet that an image, hyperlinks, and formatted text. It also includes binding data with ExcelTemplate.

Writing the Code

Writing the code for the cover sheet comes in two parts. the first part uses ExcelApplication to create a template 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

We want to create a cover sheet that looks like the following Excel worksheet:

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.

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

Adding the cover sheet

1. In AddCoverSheet, create a new worksheet called "Summary" at the beginning of the workbook with Worksheets.CreateWorksheet:

2. Select the "Summary" worksheet as the worksheet that will be active when the workbook opens with Worksheets.Select().

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

Inserting an image

1. The image will be inserted in cell A1. Adjust the height of row 1 to accommodate the image by retrieving the RowProperties object of row 1:

Note: The row height is set in units of 1/72 of an inch.

2. In the example, the user can select an image to insert into the file. The path of that image is dynamically retrieved and stored in variable imagePath.

3. Create an Anchor in cell A1 with 0 offset

>>>REVIEW HERE

13. Inserting a picture into a worksheet requires creating an Anchor object using the Worksheet.CreateAnchor() method.

14. We then want to create a Picture object using Pictures.CreatePicture method where the string describing the name of the image is pulled from the utility method RBImage.SelectedItem.Value.

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 :" and value of cell A18 to "Table of Contents".

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

10. Apply the styles to the various cells.

11. Insert the data markers for the WebFormData (Textbox inputted name, and today's date) as strings. Data markers must have the format %%=DataSource.ColumnName

12. Iterate through the country sheet names using a for loop, excluding the "Summary" and hidden "SimpleTemplate" sheets.

Cells below the "Table of Contents" cell are given a Hyperlink formula to another sheet in the workbook. When hyperlinking to another page in a workbook, the formula must follow "=HYPERLINK(\"#DestinationSheet!DesitinationCell\", \"Text\")" and use the Cell.Formula method.

Binding the coversheet data with ExcelTemplate

1. Following the lines in the code of Part 1,

string name = TextBox1.Text;

2. Create an string array for the header values and a string array for the column names.

ExcelTemplate can be bound to numerous types of .NET data structures: single variables, arrays (1-D, jagged, multi-dimensional), DataSet, DataTable, IDataReader etc. The source of the data can come from anywhere.

Some of the aforementioned structures have built in column names, such as the DataTable. When working with arrays, which don't have built in column names, you have to define the column names in a separate string array.

3. Use the ExcelTemplate.BindRowData method to bind the web form data to the data markers in the template file (%%=WebFormData.Name, %%=WebFormData.Date) with blank DataBindingProperties.

BindRowData() binds a single row of data to the template, but the data markers in the template do not need to be in a single row.

4. Now run your code.

Here is an example of what the form will look like.

Notice that the countries in the Table of Contents are hyperlinked to the corresponding sheets in the workbook.

Final Code

Downloads

You can download the code for the Extended Sales Summary here.

• ExtendedSalesSummary_CSharp.zip