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

조회 수: 29 (최근 30일)
Fabio Masino
Fabio Masino 2018년 6월 15일
댓글: Prajwol Tamrakar 2022년 8월 23일
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

채택된 답변

Guillaume
Guillaume 2018년 6월 15일
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
Prajwol Tamrakar
Prajwol Tamrakar 2022년 8월 23일
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)

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2017b

Community Treasure Hunt

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

Start Hunting!

Translated by