Aspose.Cells Hyperlinks and ranges

Aspose.Cells Product Family
1

Good day!

I’ve got a problem with Aspose.Cells sheet.Hyperlinks collection (version: 8.7.1.0).
I need a sorter(!!) when I try to find valid hyperlink! Every day I process some xls-documents with bad hyperlinks info.

For example:
sheet.Hyperlinks.Add(1, 1, 10, 10, “test1”);
sheet.Hyperlinks.Add(2, 2, 10, 10, “test2”);
sheet.Hyperlinks.Add(3, 3, 1, 1, “test3”);

What is right way to get hyperlink for cell (2,2)? What is right hyperlink value for it?
And for cell (2,3) and (3,3)?

Next test:
sheet.Hyperlinks.Add(1, 1, 4, 4, “test1”);
sheet.Hyperlinks.Add(3, 3, 4, 4, “test2”);

What is right values for this cells: (3,3), (3,4), (4,3), (4,4)?
What should I do if the cell (3,3) is the part of merged range (“A1:E4”)!!!

P.P.S.
This VBA function always return valid result:

Function GetAddress(myRange As Range)
If myRange.Hyperlinks.Count > 0 Then
GetAddress = myRange.Hyperlinks(myRange.Hyperlinks.Count).Address
Else
GetAddress = “”
End If
End Function

But I need a right way for Aspose.Cells.
Please, I need help!

2

Hi Stephan,


Thank you for contacting Aspose support.

Please note, from Aspose.Cells perspective, the hyperlinks are maintained as a separate entity and can be accessed/manipulated using the HyperlinkCollection & Hyperlink classes. This is because a hyperlink can span over multiple cells as well as specified for a merged cell. Moreover, the order of the hyperlinks in the HyperlinkCollection class is not associated with the cell reference (where hyperlink reside) but the order in which the hyperlinks have been added manually or via code. For instance, if you add the hyperlink to cell B1 first and then in cell A1, the order of these hyperlinks will be same when accessed from HyperlinkCollection class. Regarding your main concerns, you can access the cell area, Address and display text of a hyperlink using the Hyperlink.Area, Hyperlink.Address & Hyperlink.TextToDisplay properties respectively, which are also valid for hyperlinks spanning over multiple and merged cells, however, Aspose.Cells APIs do not provide any mechanism to sort the hyperlink collection based on the cell references nor access the hyperlink via a cell reference.

C#

var book = new Workbook(dir + “sample.xlsx”);
var sheet = book.Worksheets[0];
var links = sheet.Hyperlinks;
foreach (Hyperlink link in links)
{
System.Console.WriteLine(link.Area + " " + link.Address + " " + link.TextToDisplay);
}

Hope this helps.
3

And what should I do for my terms?

I need to know the valid way to get hyperlink for one specified cell. How I sad, HyperlinkCollection has some intersected ranges. And cell may be part of merged range.
4

Hi Stepan,

Thank you for writing back.

Please spare me some time to discuss the matter with the product team to find a suitable solution for your scenario. I will shortly get back with updates in this regard.

5

Hi again,


Please check the following method that returns the object of the Hyperlink based on cell reference (row, column pair). Please note, as per behaviour of Excel application, if more than one hyperlinks are part of the intersected cell ranges, the returned object would be of the last displayed hyperlink.

C#

public static Hyperlink GetLink(Worksheet sheet, int row, int column)
{
ArrayList list = sheet.Cells.MergedCells;
{
foreach (CellArea ca in list)
{
if (row >= ca.StartRow
&& row <= ca.EndRow
&& column >= ca.StartColumn
&& column <= ca.EndColumn)
{
row = ca.StartRow;
column = ca.EndColumn;
break;
}
}
}
HyperlinkCollection links = sheet.Hyperlinks;
for (int i = links.Count - 1; i >= 0; i–)
{
CellArea ca = links[i].Area;
if (row >= ca.StartRow
&& row <= ca.EndRow
&& column >= ca.StartColumn
&& column <= ca.EndColumn)
{

return links[i];
}
}
return null;
}