Add hyperlink to a worksheet

FLX TMS FlexCel for VCL & FMX
1

I want to add a worksheet to my workbook that contains links to the other worksheets in the workbook.

I
have not managed to figure out how to add the hyperlink to a specific
cell in the worksheet. Here is the c# code that I have currently:

var
hyperLink = new THyperLink(THyperLinkType.CurrentWorkbook,
inputFile.FileCode, "Link to another worksheet", inputFile.FileCode +
"#A1", null);

var cellRange = new TXlsCellRange("A1:A1");

excelFile.AddHyperLink(cellRange, hyperLink);
excelFile.SetCellValue(1, 4, hyperlink, 4);

Currently this is outputting, in cell D1, "FlexCel.Core.THyperLink"

What I want to link to is cell A1 on the other worksheets. The purpose of this is to have a master worksheet with links to the other worksheets.


2

Hi,

In SetCellValue, just write something different, like:

excelFile.SetCellValue(1, 4, "Link to another worksheet");

Whatever string you set in SetCellValue, is what you will get in the text. Cells can't really have hyperlinks, they have strings, numbers, etc. AddHyperlink adds a layer over the text, but what is in the cell is what will be displayed.

To get the exact syntax for an Hyperlink (and for many other things), remember that you can use APIMate (start menu->tms flexcel->tools). Create a file with a link in Excel, save it to a file, and open the file with APIMate. This is for example what I get to make an hyperlink from cell A1 (sheet1) to B4 (sheet2):

    //Set the cell values
    TFlxFormat fmt;
    fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Hyperlink, 0), true);
    xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
    xls.SetCellValue(1, 1, "Go to Sheet2");

    //Hyperlinks
    THyperLink Link;
    Link = new THyperLink(THyperLinkType.CurrentWorkbook, "", "Go to Sheet2", "", "Sheet2!B4");
    xls.AddHyperLink(new TXlsCellRange(1, 1, 1, 1), Link);


Note 1:
To get the string Sheet2!B4 if you have column and row numbers, remember that you can use TCellAddress class to do the conversions. So the "real" code (as opposed to the APIMate generated code) would be:

TCellAddress addr = new TCellAddress("Sheet2", 4, 2, false, false);
var Link = new THyperLink(THyperLinkType.CurrentWorkbook, "", "Go to Sheet2", "", addr.CellRef);

You can use TCellAddress to convert both strings like Sheet1!A1 to a sheet name, a column number and a row number, or to convert a sheet name, a column number and a row number into a string.

Note 2: Sorry for the delay answering, I didn't got the email notifying me of this post. It looks like the notification system for the posts in the forums is having issues.
3

That worked! Thanks a million for getting back to me on this.

Also, thanks for the extra part on getting the text of the cell using addr.CellRef. I had not considered doing this but it will certainly save me a few lines of code and it's a much neater approach.

4

You can ignore my last comment on getting the text of the cell using addr.CellRef. I realise now that this is not what you were saying.