Post
Ask Question

How to Realize Bookmark Function in Excel

Bookmark Brief Introduction

 
In different environments, bookmarks has different definitions. When reading a book, a bookmark is a piece of paper that is inserted in a book to record which page we have read to. When browsing websites, we can add some interesting and useful webpages as bookmarks to conveniently and quickly return to another time. In MS Office, a bookmark is a function to help users go to a specified location quickly and easily. This article focuses on how to realize a bookmark function in MS Excel.
 
Preparation
 
In my example, I prepare two workbooks.
 
Workbook 1
 
This workbook is a sales report. It includes many worksheets. From worksheet 2 to 6, it shows vendor sales reports, while the latter worksheets show a commission report.
Vendor sales information includes two parts, Parts and Orders. Orders information is related to Parts. For example, the fourth worksheet shows orders information about PartNo K01-07. Commission report includes information in year 2010 and 2011.
 
Workbook 2
 
This workbook just has only one worksheet which saves information about staff.
 
Bookmark List
 
What I will do is to create a bookmark in the first worksheet of Workbook 1. There are five main bookmark titles, vendor sales report, 2010 commission report, 2011 commission report, Staff information and e-iceblue (a website). Also, I set some other vice bookmark titles under the main bookmarks.
 
The effect will be as in the following image:
 
bookmark list.png
 
partinfo.png

Step
 
1. Write sub-methods for calling them in the main method.
 

DrawInternalHyperlink

 
If we want to move to other worksheets once clicking relevant bookmark title, we need to draw hyperlink for titles. Select bookmark range and add hyperlink in worksheet. Then, set type. Because the bookmark points to internal worksheet, so set type as workbook. Finally, set hyperlink text and jump to where after clicking it.
  1. static private void DrawInternalHyperlink(Worksheet sheetSource, Worksheet sheetDest, int rowIndex, string text)  
  2. {  
  3.     CellRange range = sheetSource.Range[rowIndex, 2];  
  4.     HyperLink hyperlink = sheetSource.HyperLinks.Add(range);  
  5.     hyperlink.Type = HyperLinkType.Workbook;  
  6.     hyperlink.Address = text;  
  7.     hyperlink.SubAddress = sheetDest.Range["A1"].RangeGlobalAddress;  
  8. } 

DrawExternalHyperlink

 
The fourth bookmark title points to Workbook 2, so set external hyperlink. Also, select range, add hyperlink and set hyperlink type as file. After setting text, add a file path.
  1. static private void DrawExternalHyperlink(Worksheet sheetSource, int rowIndex, string text)  
  2. {  
  3.     CellRange range = sheetSource.Range[rowIndex, 2];  
  4.     HyperLink hyperlink = sheetSource.HyperLinks.Add(range);  
  5.     hyperlink.Type = HyperLinkType.File;  
  6.     hyperlink.TextToDisplay = text;  
  7.     hyperlink.Address = @"C:\Users\lenovo\Desktop\bookmark\bookmark\StaffInfo.xlsx";  
  8. } 

DrawUrlHyperlink

 
The fifth bookmark title moves us to a website, so draw a URL hyperlink. Select range, add a hyperlink and set hyperlink type as URL. Add text and website address.
  1. static private void DrawUrlHyperlink(Worksheet sheetSource, int rowIndex, string url)  
  2. {  
  3.     CellRange range = sheetSource.Range[rowIndex, 2];  
  4.     HyperLink hyperlink = sheetSource.HyperLinks.Add(range);  
  5.     hyperlink.Type = HyperLinkType.Url;  
  6.     hyperlink.TextToDisplay = "5   E-iceblue";  
  7.     hyperlink.Address = url;  
  8. } 

SetFormat

 
This method is used to set bookmark list format, including font style, background color and indentation.
  1. static private void SetFormat(Worksheet sheet, int rowIndex,float fontSize,int indent)  
  2. {  
  3.     sheet.Range[rowIndex, 2].Style.Font.Size = fontSize;  
  4.     sheet.Range[rowIndex, 2].Style.Font.IsBold = true;  
  5.     sheet.Range[rowIndex, 2].Style.IndentLevel = indent;  
  6.     sheet.Range[rowIndex, 2].Style.Font.Color = Color.AliceBlue;  
  7.     sheet.Range[rowIndex, 2].Style.Font.FontName = "Calibri";  
  8.     sheet.Range[rowIndex, 2].Style.Color = Color.LightSkyBlue;  
  9. } 
2. Load the file from the computer and set the source sheet. Then, define rowindex initial value, which means that the bookmark list will be input from which row.
  1. Workbook workbook = new Workbook();  
  2. workbook.LoadFromFile(@"..\..\bookmark list.xlsx", ExcelVersion.Version2010);  
  3. Worksheet sheetSource = workbook.Worksheets[0];  
  4. int rowIndex = 2; 
3. Draw bookmark list 1. Bookmark list 1 includes one main bookmark, two sub-bookmarks and two third-bookmarks. The main bookmark is linked to worksheet 2. The two sub-bookmarks are linked to worksheet 2 and 4 respectively. The two third-bookmarks are linked to worksheet 3 and 5. All the bookmark titles are worksheet name.
  1. DrawInternalHyperlink(sheetSource, workbook.Worksheets[1], rowIndex++, String.Format("1   {0}", workbook.Worksheets[1].Name));  
  2. SetFormat(sheetSource, 2, 13, 0);  
  3.   
  4. DrawInternalHyperlink(sheetSource, workbook.Worksheets[2], rowIndex++, String.Format("1.1  {0}", workbook.Worksheets[2].Name));  
  5. SetFormat(sheetSource, 3, 12, 4);  
  6.   
  7. DrawInternalHyperlink(sheetSource, workbook.Worksheets[3], rowIndex++, String.Format("1.1.1  {0}", workbook.Worksheets[3].Name));  
  8. SetFormat(sheetSource, 4, 11, 8);  
  9.   
  10. DrawInternalHyperlink(sheetSource, workbook.Worksheets[4], rowIndex++, String.Format("1.2   {0}", workbook.Worksheets[4].Name));  
  11. SetFormat(sheetSource, 5, 12, 4);  
  12.   
  13. DrawInternalHyperlink(sheetSource, workbook.Worksheets[5], rowIndex++, String.Format("1.2.1  {0}", workbook.Worksheets[5].Name));  
  14. SetFormat(sheetSource, 6, 11, 8); 
4. Draw bookmark lists 2 and 3. Bookmark list 2 and 3 also point to worksheets in Workbook 1. Therefore, draw it as the above step.
  1. DrawInternalHyperlink(sheetSource, workbook.Worksheets[6], rowIndex++, String.Format("2   {0}", workbook.Worksheets[6].Name));  
  2. SetFormat(sheetSource, 7, 13,0);  
  3.   
  4. DrawInternalHyperlink(sheetSource, workbook.Worksheets[7], rowIndex++, String.Format("2.1   {0}", workbook.Worksheets[7].Name));  
  5. SetFormat(sheetSource, 8, 12,4);  
  6.   
  7. DrawInternalHyperlink(sheetSource, workbook.Worksheets[8], rowIndex++, String.Format("2.2   {0}", workbook.Worksheets[8].Name));  
  8. SetFormat(sheetSource, 9, 12,4);  
  9.   
  10. DrawInternalHyperlink(sheetSource, workbook.Worksheets[9], rowIndex++, String.Format("3   {0}", workbook.Worksheets[9].Name));  
  11. SetFormat(sheetSource, 10, 13,0);  
  12.   
  13. DrawInternalHyperlink(sheetSource, workbook.Worksheets[10], rowIndex++, String.Format("3.1   {0}", workbook.Worksheets[10].Name));  
  14. SetFormat(sheetSource, 11, 12,2);  
  15. DrawInternalHyperlink(sheetSource, workbook.Worksheets[11], rowIndex++, String.Format("3.2   {0}", workbook.Worksheets[11].Name));  
  16. SetFormat(sheetSource, 12, 12,2); 
5. Draw bookmark list 4. Bookmark list 4 is linked to Workbook 2. Set its location, title, and format.
  1. DrawExternalHyperlink(sheetSource, rowIndex++, "4   StaffInfo.xlsx");  
  2. SetFormat(sheetSource, 13, 13,0); 
6. Draw bookmark list 5. Bookmark list 5 is linked to a website. Set its location and add site address. Then set the format.
  1. DrawUrlHyperlink(sheetSource, rowIndex++, "http://www.e-iceblue.com");  
  2. etFormat(sheetSource, 14, 13,0); 
7. Set column width and row height.
  1. sheetSource.AllocatedRange.AutoFitColumns();  
  2. heetSource.AllocatedRange.RowHeight = 18; 
8. Save and launch file
  1. workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2010);  
  2. ystem.Diagnostics.Process.Start(@"..\..\result.xlsx"); 

Conclusion

 
In this article, I show a method about how to realize bookmark function in MS Excel. The bookmark can point to worksheet in one workbook, in other workbook and website. After clicking bookmark title, we can get the relevant contents about this title shows.
 
Note: Spire.XLS is used in this method to help me realize this function.
 
*Workbook 1, workbook 2 and the whole project will be packed. You can download to get details on coding.


Similar Articles