...

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:

ws.GetRowProperties(0).Height = 90;

...

Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);

Writing

...

Text

1. Set the Cell.Value of cell A15 A3 to the string "Date Executed :". In cell A16A4, set Cell.Value to "Report For:". In cell A18A6, set the Cell.Value to "Table of Contents".

ws.Cells["A15A3"].Value = "Date Executed :";
ws.Cells["A16A4"].Value = "Report For :";
ws.Cells["A18A6"].Value = "Table of Contents";

2. Insert the data markers for the "Date Executed" and "Report For" data, which will be imported using ExcelTemplate.

ws.Cells["C15C3"].Value = "%%=WebFormData.Date";
ws.Cells["C16C4"].Value = "%%=WebFormData.Name";

3. Create a GlobalStyle using the Workbook.CreateStyle() method. Next we will add hyperlinks to each of the worksheets in the workbook. To do this, we will use Excel's native HYPERLINK formula to point to the other worksheets. The format for the HYPERLINK formula will be:

=HYPERLINK("#DestinationSheet!DestinationCell", "Display Text")

This will be set to the Cell.Formula property.

Iterate through the country sheet names using a for loop, excluding the "Summary" and hidden "SimpleTemplate" sheets, which are the first two worksheets in the workbook.

for (int i = 2; i < wb.Worksheets.Count; i++)
{
    string sheetName = wb.Worksheets[i].Name.ToString();
    ws.Cells[16 + i, 1].Formula = "=HYPERLINK(\"#"+sheetName+"!A1\", \""+sheetName+"\")";
}

Adding Styles to the Text

For more about using styles in ExcelWriter, see Effective Use of Styles.

newcode}}GlobalStyle underlined = wb1. Create a GlobalStyle using the Workbook.CreateStyle();{{newcode

4. Set the Font.UnderlineStyle of underlined to{{UnderlineStyle.Single}}.

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

5. Create another GlobalStyle called boldAndFont12.

...

method. This style will be applied to the labels on the summary sheet.

GlobalStyle labels = wb.CreateStyle();

62. Set the Font.Bold to true and Font.Size to 12.

boldAndFont12
labels.Font.Bold = true;
boldAndFont12labels.Font.Size = 12;

7. Create another GlobalStyle called dateForm and use NumberFormat.DateFormat to specify the desired NumberFormat.DateFormat3. Apply the labels style to the label cells with Cell.ApplyStyle(Style).

GlobalStyle
dateForm = wb.CreateStyle(ws.Cells["A3"].ApplyStyle(labels);
dateForm.NumberFormat = wb.NumberFormat.DateFormat.MonthDayYear;

8. Apply the underlined style to A15 and A16. Apply boldAndFont12 to A18 and apply dateForm to B15.

ws.Cells["A4"].ApplyStyle(labels);
ws.Cells["A6"].ApplyStyle(labels);

4. Set the Font.UnderlineStyle of cell A6 to be UnderlineStyle.Single.

ws.Cells["A15A6"].Style.Font.Underline = underlined;
ws.Cells["A16"].Style = underlined;
ws.Cells["A18"].Style = boldAndFont12;
ws.Cells["B15"].Style = dateForm;

9. Next we will add hyperlinks to each of the worksheets in the workbook. To do this, we will use Excel's native HYPERLINK formula to point to the other worksheets. The format for the HYPERLINK formula will be:

=HYPERLINK(\"#DestinationSheet!DesitinationCell\", \"Text\")

This will be set to the Cell.Formula property.

Iterate through the country sheet names using a for loop, excluding the "Summary" and hidden "SimpleTemplate" sheets, which are the first two worksheets in the workbook.

Font.UnderlineStyle.Single;

10. The final code for AddCoverSheet() should be:

protected void AddCoverSheet()
{
            Worksheet ws = wb.Worksheets.CreateWorksheet("Summary", 0);
            ws.Select();
            ws.ShowGridlines = false;

            /*******Inserting the Image********/


            ws.GetRowProperties(0).Height = 90; 

            string imagePath = RBImage.SelectedItem.Value;

            Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);
            Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);

            /*******Writing values*************/

            ws.Cells["A3"].Value = "Date Executed :";
            ws.Cells["A4"].Value = "Report For :";
            ws.Cells["A6"].Value = "Table of Contents";

            ws.Cells["C3"].Value = "%%=WebFormData.Date";
            ws.Cells["C4"].Value = "%%=WebFormData.Name";

            for (int i = 2; i < wb.Worksheets.Count; i++)
            {
                string sheetName = wb.Worksheets[i].Name.ToString();
                ws.Cells[167 + i, 1].Formula = "=HYPERLINK(\"#" + sheetName +
                    "!A1\", \"" + sheetName + "\")";
            }

            /******Adding Styles to Text*******/

            GlobalStyle labels = wb.CreateStyle();
            labels.Font.Bold = true;
            labels.Font.Size = 12;

            ws.Cells["A3"].ApplyStyle(labels);
            ws.Cells["A4"].ApplyStyle(labels);
            ws.Cells["A6"].ApplyStyle(labels);

            ws.Cells["A6"].Style.Font.Underline = Font.UnderlineStyle.Single;

}

>>REVIEW HERE

Binding the coversheet data with ExcelTemplate

...