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

21 vues (au cours des 30 derniers jours)
Fabio Masino
Fabio Masino le 15 Juin 2018
Commenté : Prajwol Tamrakar le 23 Août 2022
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

Réponse acceptée

Guillaume
Guillaume le 15 Juin 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.
  1 commentaire
Prajwol Tamrakar
Prajwol Tamrakar le 23 Août 2022
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)

Connectez-vous pour commenter.

Plus de réponses (0)

Produits


Version

R2017b

Community Treasure Hunt

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

Start Hunting!

Translated by