Page History

...

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);

...

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

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

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. Apply the styles to the various cells.

ws.Cells["A15"].Style = underlined;
ws.Cells["A16"].Style = underlined;
ws.Cells["A18"].Style = boldAndFont12;
ws.Cells["B15"].Style = dateForm;

11. 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.

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+"\")";
}

12. Finally, we want to insert a picture into the cover sheet. This requires creating an Anchor object using the Worksheet.CreateAnchor() method.

Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);

13. 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.

string image = RBImage.SelectedItem.Value;
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@image), anc);

14. We want to set the dimensions of the Picture object using Picture.Height and Picture.Width.

logo.Height = 195;
logo.Width = 290;

15. Finally, we want select the first sheet in the workbook (the "Summary" sheet) to be first viewed when the document is opened.

wb.Worksheets[0].Select();

Binding the coversheet data with ExcelTemplate