How to get hyperlink in a formula using EPPlus

Question

I need to get the exact link inside the formula(HYPERLINK) and save it to the database. here is my code to get the link,

answerList.Add(new SurveyCompetitorAnswer
{

    MainSurveyId = id,
    Answer = workSheet.Cells[AnswerRowRange].Text,
    DateCreated = DateTime.Now,
    Link = workSheet.Cells[AnswerRowRange].Formula,
    SurveyQuestionId = item.Id,
    SurveyCompetitorId = comp.Id
});

but it get the whole FORMULA text. example (HYPERLINK("https://www.owler.com/company/sproutsolutions","0.5"))

...

what I need is just https://www.owler.com/company/sproutsolutions.

I tried using Link = workSheet.Cells[AnswerRowRange].Hyperlink, but its purpose is only to SET links and not GET links.

EDIT: I tried using var link = workSheet.Cells[AnswerRowRange]; and Link = link.Hyperlink.AbsoluteUri, but it shows null reference exception

score 1 · Answer 1 · answered Apr 26 '19 at 02:56

This is what I did instead.

   var link = workSheet.Cells[AnswerRowRange].Formula;
   int index = link.IndexOf(",");
   if (index > 0)
   {
      link = link.Substring(0, index);
   }
   var onemore = link.Substring(11);
   var final = onemore.Substring(0,onemore.Length -1);

   answerList.Add(new SurveyCompetitorAnswer
   {
      MainSurveyId = id,
      Answer = workSheet.Cells[AnswerRowRange].Text,
      DateCreated = DateTime.Now,
      Link = final,
      SurveyQuestionId = item.Id,
      SurveyCompetitorId = comp.Id
   });

working fine now.

I_Don't_Code · Answer 2 · 2019-04-27T00:14:02.723

0

It looks like being able to take the formula HYPERLINK("https://www.owler.com/company/sproutsolutions","0.5") and extract the part ... within ("...", is what you want. So you need a way to read and extract a sub-string from a string (in this case the formula is the string, and the URL is the sub-string).

I'm not an expert in this by any means, but would one of these help?

Epplus read hyperlink with html fragment i

How to extract link url from Excel cell

Extracting a URL from hyperlinked text in Excel cell

*A note to future readers:
The author of the original question posted her own solution. Her method does work, but only when the the contents of the cell containing the hyperlink contains the formula HYPERLINK("URL","YourFriendlyNameGoesHere") and has no spaces before the "URL" in HYPERLINK("URL",....) like in the situation: HYPERLINK( "URL",....), which could result from spaces being accidentally typed into the cell (even just one space could be an issue).

This situation can be remedied by using the method .IndexOf("\"") to find the first quotation mark's " index position, instead of simply using .Substring(11), which assumes the position index 11 in HYPERLINK("URL",....) is the beginning of URL, which may not be the case when there's an extra space. Instead, use .Substring(...) with the index position of " + 1 in place of ... as the argument to .Substring(...).

edited Apr 27 '19 at 00:14

answered Apr 26 '19 at 01:34

I_Don't_Code

117
11

Thanks for the help. I tried using the second link `Link = workSheet.Cells[AnswerRowRange].Hyperlink.AbsoluteUri,` but it shows a null reference. – Chris May Apr 26 '19 at 02:04
@ChrisMay Correct, as you mentioned in your original post, ```.Hyperlink``` is used to set links, not get links. So ```.Hyperlink.AbsoluteUri``` wouldn't have to do with getting links. Do you know of a way to search a string? Because if you could search the string that is produced from the ```.Formula``` method, then you could extract just the hyperlink. Correct me if I'm wrong, but are you using C# language? – I_Don't_Code Apr 26 '19 at 02:18
@ChrisMay Here is a work-around: https://stackoverflow.com/questions/10709821/find-text-in-string-with-c-sharp Use the answer here to extract ```URL``` within ```HYPERLINK("URL",".........``` – I_Don't_Code Apr 26 '19 at 02:27
Yes, C#. I Tried using substring(11), it gets the start of the link but it gets the rest of the formula. Example `https://sprout.ph/about/","2015")`. – Chris May Apr 26 '19 at 02:28
As long as the ```URL``` doesn't contain a ```("``` then you should be fine. Otherwise this workaround may not work. I'm not certain, however. – I_Don't_Code Apr 26 '19 at 02:37
Further, this may be useful: https://github.com/spreadsheetlab/XLParser It parses/analyzes an excel formula and breaks it up into all of its individual parts. You can see with this demonstration of it: http://xlparser.perfectxl.nl/demo/ – I_Don't_Code Apr 26 '19 at 03:02

How to get hyperlink in a formula using EPPlus

2 Answers2