How can I get Hyperlinks' addresses from an Excel file?

Hello, if I have an Excel file which contains Hyperlinks, how could I obtain by Matlab the link information (not the displayed text in the cell)?

Here my partial code:

file = 'Sample.xlsx';
sheet = 'Sheet1';

% load Excel data
[~,textData] = xlsread(file,sheet);

In this example code, textData doesn't contain the address information.

I hope the question is clear. Thanks in advance

Answer 1

[Guillaume]

Matlab does not have a function to retrieve the target of hyperlinks and the way excel stores hyperlink is a bit convoluted as the hyperlinks are not properties of the cell themselves, they're stored in a separate collection. The following function will retrieve all the hyperlinks in a spreadsheet:

function [links, locations] = xlsgethyperlinks(file, sheet)
   %retrieve all hyperlinks and their location in an excel worksheet
   %syntax:
   %    [links, locations] = xlsgethyperlinks(file);
   %    [links, locations] = xlsgethyperlinks(file, sheet);
   %with:
   %    file: full path of the excel workbook (scalar string / char vector)
   %    sheet: name or index of worksheet (scalar string / char vector / scalar positive integer). Default is 1
   %    links: column cell vector of hyperlink targets in the order returned by excel
   %    locations: column cell vector of char vectors containing the location of the hyperlinks in the sheet, in RC format
   %    Author: G. de Sercey
   %    BSD license
   if nargin < 2
       sheet = 1;
   end
   %todo: add input validation
   %The code starts excel, load the workbook, then iterates over all the hyperlinks in the worksheet storing the hyperlink address and the range address of the hyperlinks.
   %excel is closed automatically by the onCleanup when the function terminates (normally or due to errors).
   excel = actxserver('Excel.Application');
   cleanup = onCleanup(@() excel.Quit);
   workbook = excel.Workbooks.Open(file);
   worksheet = workbook.Worksheets.Item(sheet);
   hyperlinks = worksheet.Hyperlinks;
   links = cell(hyperlinks.Count, 1);
   locations = cell(hyperlinks.Count, 1);
   for hidx = 1:hyperlinks.Count
      hyperlink = hyperlinks.Item(hidx);
      links{hidx} = hyperlink.Address;
      locations{hidx} = hyperlink.Range.Address;
   end
end

Note that I've just written this on the fly just now. There may be bugs.

[Prajwol Tamrakar]

If you want to get the text associated with the hyperlinked cell, use the following in the last loop

for hidx = 1:hyperlinks.Count

hyperlink = hyperlinks.Item(hidx);

links{hidx} = hyperlink.Address;

locations{hidx} = hyperlink.Range.Address;

Names{hidx}=hyperlink.Name

end

And return "Names" using the following modification

function [links, locations, Names] = xlsgethyperlinks(file, sheet)