How to access excel bookmarks using C#

Question

I have a very big excel file, that we have to import in database. Import is working perfect but it is taking like 35 second to import it. Client cannot wait to much. I have created bookmarks in it.

Here is the link to create bookmarks. http://www.youtube.com/watch?v=9n4g_l7h8jc

Now i want to read that bookmarks from C# code. As I said we have different sections like: 1. Distribution
2. Staff 3. Resources 4. Budget and so on...

I can access this heading/section directly from this code and I am getting row and column

_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
var externalDprDistribution = xlRange.Find("Distribution");
var colDist = externalDprDistribution.Column;
var rowDist = externalDprDistribution.Row;

but still i want to create bookmark in excel sheet and then i want to access bookmarks in c# code and will get row and column from there. So i need a code that can access created bookmarks in c#

Any help? Regards

You should mention what library you are using for reading from excel file. — alfoks, Dec 10 '12 at 11:49
@alfoks that would be http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._worksheet.aspx — user247702, Dec 10 '12 at 12:51
I have given reference of office dll and that is here Microsoft.Office.Interop.Excel; — Husnain Kazmi, Dec 10 '12 at 13:28
If you are only interested in the data, it may be quicker to access the xml directly rather than going through an interop. Excel workbooks from 2007 on (and maybe 2003) are simply zipped XML files. — Dale M, Dec 13 '12 at 05:00

score 0 · Answer 1 · answered Aug 01 '16 at 03:14

If yo extract the Excel file, you'll see that the bookmarks are hyperlinks.

<hyperlinks>
  <hyperlink ref="G18" location="Test_Mark1" display="Bookmark1"/>
  <hyperlink ref="G10" r:id="rId1"/>
</hyperlinks>

Recently for a project, I wrote below test codes to find bookmarks and update them, hope it helps

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO.Packaging;
using System.Xml;
using System.IO;


namespace OpenXMLTest
{
class Program
 {
static void Main(string[] args)
{
    string fileName = @"c:\temp\book1.xlsx";

    Console.WriteLine("Start reading bookmark of excel...");


    using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
    {
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, true))
        {
            WorkbookPart workbookPart = doc.WorkbookPart;

            //find bookmarks
            foreach (var workSheetPart in workbookPart.WorksheetParts)
            {
                var temp = workSheetPart.RootElement.Descendants<Hyperlinks>();
                IEnumerable<Hyperlink> hyperLinks = null;
                if (temp.Count() > 0)
                {
                    hyperLinks = temp.First().Cast<Hyperlink>();
                }

                var workSheet = workSheetPart.Worksheet;

                var cells = workSheet.Descendants<Cell>();

                //loop each cell, find bookmark
                foreach (var c in cells)
                {

                    if (hyperLinks != null && hyperLinks.Count() > 0)
                    {
                        var hyperLink = hyperLinks.SingleOrDefault(x => x.Reference.Value == c.CellReference.Value);
                        if (hyperLink != null)
                        {

                            if (!string.IsNullOrEmpty(hyperLink.Location))
                            {
                                Console.WriteLine("Bookmark.DisplayName : " + hyperLink.Display);
                                Console.WriteLine("Bookmark.Location : " + hyperLink.Location);


                                //update bookmark

                                hyperLink.Location = "BookMark_Test";
                                hyperLink.Display = "updated bookmark";

                                Console.WriteLine("Bookmark.DisplayName after updated : " + hyperLink.Display);
                                Console.WriteLine("Bookmark.Location after updated : " + hyperLink.Location);
                            }


                            //for normal hyperlinks
                            //var hr = workSheetPart.HyperlinkRelationships.SingleOrDefault(x => x.Id == hyperLink.Id);
                            //if (hr != null)
                            //{
                            //    Console.WriteLine(hr.Uri.ToString());
                            //}
                        }
                    }


                    workSheet.Save();
                }

                workbookPart.Workbook.Save();

            }



        }


    }

    Console.ReadKey();

}


}
}

How to access excel bookmarks using C#

1 Answers1