Reading Cell contents

FLX TMS FlexCel Studio for .NET
1

Hi There

I am trying to read the cell content with a string of :-

=HYPERLINK("https://store.icrealtime.com/IPEG-B20F-IRW4","ICR | Store_IPEG-B20F-IRW4")

I want to read the actual string but Flexcel treats it as a Hyperlink although the cell is not in the Hyperlink Table.

Flexcel reads the cell contents as :-

ICR | Store_IPEG-B20F-IRW4

I have tried various workarounds without any success.

Many thanks in advance

Nigel

2

Hi,
=HYPERLINK is a formula, same as if you had "=A1 + A2" in the cell. If you read the cell, you will get a TFormula struct which has:

The url is one of the parameters of the formula, but not the formula result. I mean, in the spreadsheet below:

2025-06-22_15.15.48
2025-06-22_15.15.481102×518 7.38 KB

The formula result for the cell is "hello", not the hyperlink. You could also have more complex formulas, like

=HYPERLINK("https://tmssoftware.com","hello") & HYPERLINK("https://tmssoftware.com","bye")

And what would be the hyperlink there? (just for the record, if you click the cell in Excel it will go to https://tmssoftware.comhttps/tmssoftware.com ). You might even have a =if() there that only shows the hyperlink in some cases.

The solutions I see are:

  1. Quick and dirty: Look at the formula text, search for =Hyperlink and get the url after that. It will break in a lot of corner cases, but the common one will work. Also note that there must be a second parameter to =HYPERLINK for this to be a problem: To read a hyperlink like =HYPERLINK("https://test.com") you can get it from the formula result.
  2. If you want to do it more professionally, you can use Tokens (yes, we got you covered :slight_smile: )

Note that while the example uses GetFormulaTokens, you can use GetTokens if you already have the text you want to parse.
As mentioned in the link above, formulas are stored in RPN notation. So you don't have a TTokenFunction where you have a property .Arguments, but instead, in the stack, you have the Arguments and apply the Function to that.

To play with tokens, the simplest way will be to play with them: use the code in the example above to read a formula with the hyperlink and see the tokens it has.
For example, if you try the following code:

var xls = new XlsFile(1, true); var Tokens = xls.GetTokens(1,     "=HYPERLINK(\"https://store.icrealtime.com/IPEG-B20F-IRW4\",\"ICR | Store_IPEG-B20F-IRW4\")");

You'll see the following result.

2025-06-22_15.43.54
2025-06-22_15.43.541228×1172 210 KB

The important thing to notice is that the arguments go "in reverse", that is, the first argument is the more far away from the function token. The stack is:
Argument1 -> The one you want
Argument2
FunctionToken with name = "HYPERLINK", and 2 arguments.

But you can also have Hyperlink with 1 argument, in which case the argument most near the function would be the one you want. Basically, you have to scan Tokens for a HYPERLINK function, and when found at position i, look at Tokens[i - ArgumentCount]

3

Thanks for the detailed reply but can you explain how you can read the formula text for option 1 please ?

Also I cannot see how you can read the TTokenList from an individual cell ?

Many thanks

Nigel

4

Thanks for the detailed reply but can you explain how you can read the formula text for option 1 please ?

GetCellValue will return a TFormula object for cells with formulas. TFormula has a property for the formula text.

var Formula =   xls.GetCellValue(1,1) as TFormula;
if (Formula != null)
{
   FormulaText = Formula.Text;
  //Option 1: Parse(FormulaText);
  //Option 2: var Tokens = xls.GetTokens(xls.ActiveSheet, FormulaText);
   {...}
}

or, for option 2, if you aren't reading the value for anything else:

  var Tokens := xls.GetFormulaTokens(row, col);
  if (Tokens != null) {...}
5

Thanks Adrian

I got there in the end with a solution that works using Tokens. I will not embarrass myself by sharing the code but looping through the Tokens is not easy either :slight_smile:

All the best

Nigel

6

I got there in the end with a solution that works using Tokens.

Great to know!

no it's not :slight_smile: It takes a while to wrap the head around RPN...