MATLAB Answers

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

21 views (last 30 days)
Fabio Masino
Fabio Masino on 15 Jun 2018
Answered: Guillaume on 15 Jun 2018
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

  0 Comments

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 15 Jun 2018
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.

  0 Comments

Sign in to comment.

More Answers (0)

Products


Release

R2017b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!