none
Code to check whether hyperlink text is meaningful or not in a excel using open xml library RRS feed

  • Question

  • Code to check whether hyperlink text is meaningful or not in a excel using open xml library
    Wednesday, August 10, 2016 8:21 AM

Answers

  • Do you mean get the displayed text of the hyperlinks?
    E.g. 


    You could refer to the following code. It lists all the cell text which have hyperlinks.

        public static List<string> ListHyperlinks(string fileName)
            {
                List<string> items = new List<string>();
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
                {
    
                    WorkbookPart wbPart = document.WorkbookPart;
                    foreach(WorksheetPart wsPart in wbPart.WorksheetParts)
                    {
                        foreach(Hyperlink hyperLink in wsPart.Worksheet.Descendants<Hyperlink>())
                        {
                            // get the cell reference if there is a hyperlink
    
                            string value = null;
                            foreach (Cell cell in wsPart.Worksheet.Descendants<Cell>())
                            {
                                // get the cell text                   
                                if (cell.CellReference.Value  == hyperLink.Reference.Value )
                                {
                                    if (cell.DataType != null)
                                    {
                                        switch (cell.DataType.Value)
                                        {
                                            case CellValues.SharedString:
    
                                                SharedStringTablePart ssPart = wbPart.SharedStringTablePart;
                                                SharedStringTable ssTable = ssPart.SharedStringTable;
                                            
                                                SharedStringItem ssValue = (SharedStringItem)ssTable.ChildElements.GetItem(Convert.ToInt32(cell.CellValue.Text));
                                              
                                                items.Add(ssValue.InnerText.ToString());
                                                break;
                                            case CellValues.Boolean:
                                                switch (value)
                                                {
                                                    case "0":
                                                        value = "FALSE";
                                                        break;
                                                    default:
                                                        value = "TRUE";
                                                        break;
                                                }
                                                break;
                                        }
                                    }
                                }
                            }
                       
                            //foreach (HyperlinkRelationship relation in wsPart.HyperlinkRelationships)
                            //{
                            //    items.Add(relation.Uri.AbsoluteUri);
                            //}
                        }
                        
                    }        
                    return items;
                }
              
            }


    Thursday, August 11, 2016 8:34 AM
    Moderator

All replies

  • Hi,

    What kinds of hyperlinks do you want to check if valid? How do you determine whether its text meaningful or not?

     

    The following lists all the hyperlinks in the excel.

    using System;
    using System.Collections.Generic;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    namespace ListHyperlinks
    {
        class Program
        {
           
            static void Main(string[] args)
            {
                string fileName = (@"C:\Users\Administrator\Desktop\threads.xlsx");
                var items = ListHyperlinks(fileName);
                string line = string.Join("\n", items.ToArray());
                Console.WriteLine(line);
                Console.ReadKey();
               
            }
            public static List<string> ListHyperlinks(string fileName)
            {
                List<string> items = new List<string>();
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
                {
    
                    WorkbookPart wbPart = document.WorkbookPart;
                    foreach(WorksheetPart wsPart in wbPart.WorksheetParts)
                    {
                        foreach(Hyperlink hyperLink in wsPart.Worksheet.Descendants<Hyperlink>())
                        {
                            foreach (HyperlinkRelationship relation in wsPart.HyperlinkRelationships)
                            {
                                items.Add(relation.Uri.AbsoluteUri);
                            }
                        }
                        
                    }        
                    return items;
                }
              
            }
        }
    }
    

    Thursday, August 11, 2016 5:10 AM
    Moderator
  • I want to check whether the text which is written on the hyperlink is valid or not means is it related to the hyperlink or not. For example Download should have a link which is to download something. Can i get a code which give me the text field of the hyperlink.
    Thursday, August 11, 2016 6:20 AM
  • Do you mean get the displayed text of the hyperlinks?
    E.g. 


    You could refer to the following code. It lists all the cell text which have hyperlinks.

        public static List<string> ListHyperlinks(string fileName)
            {
                List<string> items = new List<string>();
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
                {
    
                    WorkbookPart wbPart = document.WorkbookPart;
                    foreach(WorksheetPart wsPart in wbPart.WorksheetParts)
                    {
                        foreach(Hyperlink hyperLink in wsPart.Worksheet.Descendants<Hyperlink>())
                        {
                            // get the cell reference if there is a hyperlink
    
                            string value = null;
                            foreach (Cell cell in wsPart.Worksheet.Descendants<Cell>())
                            {
                                // get the cell text                   
                                if (cell.CellReference.Value  == hyperLink.Reference.Value )
                                {
                                    if (cell.DataType != null)
                                    {
                                        switch (cell.DataType.Value)
                                        {
                                            case CellValues.SharedString:
    
                                                SharedStringTablePart ssPart = wbPart.SharedStringTablePart;
                                                SharedStringTable ssTable = ssPart.SharedStringTable;
                                            
                                                SharedStringItem ssValue = (SharedStringItem)ssTable.ChildElements.GetItem(Convert.ToInt32(cell.CellValue.Text));
                                              
                                                items.Add(ssValue.InnerText.ToString());
                                                break;
                                            case CellValues.Boolean:
                                                switch (value)
                                                {
                                                    case "0":
                                                        value = "FALSE";
                                                        break;
                                                    default:
                                                        value = "TRUE";
                                                        break;
                                                }
                                                break;
                                        }
                                    }
                                }
                            }
                       
                            //foreach (HyperlinkRelationship relation in wsPart.HyperlinkRelationships)
                            //{
                            //    items.Add(relation.Uri.AbsoluteUri);
                            //}
                        }
                        
                    }        
                    return items;
                }
              
            }


    Thursday, August 11, 2016 8:34 AM
    Moderator