How to read hyperlink from Excel sheet

FLX TMS FlexCel for VCL & FMX
1

Hi,
I try to read a hyperlink from an Excel file e.g. in cell B7 with Flexcell and put it into a string for further processing with Delphi XE7.
Unfortunately I do not get it done and I have no good ideas left or examples found to get it working.
Hopefully someone has a working example.

Thanks and best regards,
Frans

2

Hi,

There are 2 issues here:
1)Hyperlinks can be of many types: They can be a link to a website, but they can also be a link to another file or to a cell in the same file. If you know that the hyperlinks are all to URLs, then you can just use the "Text" property, but if not you will have to use the hlink.LinkType property of THyperLink and process the values depending on the type of link.

2)Hyperlinks are not tied to a specific cell like B7, they are stored in a layer above the cells. This is what FlexCel returns. To get the link for cell B7 you would need to read all hyperlinks and put them into a Dictionary, then get the value from the dictionary.

Here is a sample code which reads all hyperlinks in the active sheet:


program Project4;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  VCL.FlexCel.Core,
  FlexCel.XlsAdapter;

var
  xls: TXlsFile;
  i: integer;
  hlink: THyperLink;
  hrange: TXlsCellRange;
begin
  xls := TXlsFile.Create('..\..\hlinks.xlsx');
  try
    for i := 1 to xls.HyperLinkCount do
    begin
      hrange := xls.GetHyperLinkCellRange(i);
      hlink := xls.GetHyperLink(i);
      WriteLn(TCellAddress.Create(hrange.Top, hrange.Left).CellRef + ':'
              + TCellAddress.Create(hrange.Bottom, hrange.Right).CellRef  + ' -> '
              + hlink.Text);
    end;
  finally
    xls.Free;
  end;
  ReadLn;
end.
3

Hi Adrian,
Thank you for your extensive answer.  I'am going to study and implement it.
With kind regards,
Frans