0

I am trying to get the text in a cell through spreadsheet gear library.

There is a formula applied to a column C1 =GETURL(I2) and it is evaluating text which is something like "https://loremipsum.com/2345/view"

I have another column C2 with a formula which outputs 'View' as Text

=HYPERLINK(CONCATENATE("https://loremipsum.com/",[@[Customer CID]],"/view"), "View")

Now, when i try to get the Text through C# code for cells in C2 column, I get "View" when I run the below statement and "#Name?" for cells in C1 column. 

worksheet.Cells[i, j].Text; //Outputs = "#NAME?"

I tried to use =TEXT(GETURL(I2), "") in excel but still it outputs #Name! I also tried doing worksheet.Cells[i, j].Value //Output = Name

Why is that the same statement is giving me different results even when both cells are having a formula applied to them. 

Function GETURL(cell As Range, Optional default_value As Variant)
  With cell.Range("A1")
    If .Hyperlinks.Count = 1 Then
      GETURL = .Hyperlinks(1).Address
    Else
      If Left$(Replace(Replace(Replace(.Formula, " ", ""), vbCr, ""), vbLf, ""), 11) = "=HYPERLINK(" Then
        Dim indexFirstArgument As Long: indexFirstArgument = InStr(.Formula, "(") + 1
        GETURL = Application.Evaluate(Mid$(.Formula, indexFirstArgument, InStrRev(.Formula, ",") - indexFirstArgument))
      Else
        GETURL = default_value
      End If
    End If
  End With
End Function
s7h
  • 51
  • 3

1 Answers1

0

As far as the SpreadsheetGear side of things go, note that SpreadsheetGear cannot execute VBA code (though it does preserve VBA in existing XLS or XLSM files). SpreadsheetGear does support adding your own custom functions, but you must implement this via the SpreadsheetGear.CustomFunctions API in your .NET application. If no custom function is available, SpreadsheetGear would return #NAME! errors whenever it encountered functions like GETURL, and I suspect this is what is occurring in your case.

To implement a Custom Function in SpreadsheetGear, you basically must sub-class the Function class and override its Evaluate(...) method, which is what gets called whenever SpreadsheetGear encounters your custom function in a formula. The doc has samples, but you can also find a fully-functioning ASP.NET sample here:

https://www.spreadsheetgear.com/support/samples/asp.net.sample.aspx?sample=customfunctions

IMPORTANT: SpreadsheetGear Custom Functions have a number of rules that you must abide by. I strongly recommend you review these rules, as laid out in the "Remarks" section of the documentation of the Function.Evaluate(...) method provided in the above link. One of these rules is that all access to cells must be done through the arguments provided to Evaluate(...) via the IArguments parameter.

Your GETURL function appears to access Range, its Hyperlinks collection and Formula, etc. These sorts of actions are not allowed in SpreadsheetGear's Custom Function API. All you would have access to is what is provided by IArguments, which in the case of passing in a range into your custom function would be those cells' underlying calculated values. In other words, your GETURL function as it is implemented above would not work ported over to a SpreadsheetGear Custom Function. You would need to update it in a way in which you don't access IRange / IRange.Hyperlinks / etc.

Tim Andersen
  • 3,014
  • 1
  • 15
  • 11