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.
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.