1

I am using a formula to create hyperlinks in excel.

Excel Formula : =HYPERLINK(CONCATENATE("https://loremipsum.com/#/Advert/",[@[Customer CID]],"/dolorsit"), "View")

This formula is evaluating as expected in excel but when I go fetch this through spreadsheet gear. The result is:-

"=HYPERLINK(CONCATENATE(\"https://loremipsum.com/#/Advert/\",#REF!,\"/dolorsit\"), \"View \")"

What I really want to fetch is the hyperlink. 

https://loremipsum.com/#/Advert/{ColumnValue}/dolorsit

I tried doing worksheet.Cells[i, j].Hyperlinks; but it is giving null reference error since the range has formula. And If I do worksheet.Cells[i, j].Formula it doesn't give me the Column reference. (Gives "#REF!" text only)

Does anyone have the solution to my problem ?

All I am trying to achieve is the hyperlink that is getting evaluated from that formula.

s7h
  • 51
  • 3
  • I don't know my way around Excel, the syntax it's omitting when you fetch it via C# - what does that mean? `[@[Customer CID]]` What is that the syntax for? Some sort of named cell reference? –  Jun 11 '18 at 10:40
  • Maybe refer to the following question, I'm not implying it's a duplicate at all but it appears as though you're trying to work with cells/a range with a name as opposed to explicitly referencing the cell indexes, so might be relevant: https://stackoverflow.com/q/33602560/1017882 –  Jun 11 '18 at 10:44
  • @JᴀʏMᴇᴇ 'Customer CID' is a column name in my worksheet and `[@[Customer CID]]` syntax fetches the value of that column for each row. – s7h Jun 11 '18 at 11:33

1 Answers1

0

You are running into a known limitation. SpreadsheetGear does not yet support "Excel Tables" (Excel’s Ribbon > Home > Styles > Format as Table...). These Table objects are dropped upon reading the file. This has the consequence of also dropping any formulas that use "structured references" (i.e., in your case the [@[Customer CID]] reference in your formula. Such references will be converted to #REF! errors.

We do have a feature request item to add support for Excel Tables (I work for SpreadsheetGear). You are welcome to contact us at support@spreadsheetgear.com so that I can add you to our request list for this feature, although I cannot provide any sort of timeline for when this might become available.

In the meantime, you will need to avoid using such "structured references" in your formulas and instead use normal cell references, or perhaps defined names if you prefer.

Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • Thanks, Tim. I am facing another issue while fetching the Text from a cell. Could you take a look. https://stackoverflow.com/questions/50818223/c-sharp-spreadsheetgear-name-as-text-if-formula-is-applied-to-cell – s7h Jun 12 '18 at 13:17