问题描述
我正在尝试通过电子表格齿轮库在单元格中获取文本.
I am trying to get the text in a cell through spreadsheet gear library.
有一个应用于列C1 =GETURL(I2)的公式，它正在评估文本，类似于" https ://loremipsum.com/2345/view "
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"
我还有另一列C2，其中的公式将视图"输出为文本
I have another column C2 with a formula which outputs 'View' as Text
=HYPERLINK(CONCATENATE("https://loremipsum.com/",[@[Customer CID]],"/view"), "View")
现在，当我尝试通过C#代码获取C2列中单元格的文本时，运行以下语句和"#Name?"时，我将获得查看".用于C1列中的单元格.
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?"
我尝试在excel中使用=TEXT(GETURL(I2), "")，但仍输出#Name！我也尝试做worksheet.Cells[i, j].Value //Output = 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
推荐答案
就SpreadsheetGear而言，请注意SpreadsheetGear无法执行VBA代码(尽管它确实将VBA保留在现有XLS或XLSM文件中). SpreadsheetGear确实支持添加您自己的自定义函数，但是您必须通过SpreadsheetGear来实现. CustomFunctions API.如果没有自定义函数，则SpreadsheetGear将返回#NAME！每当遇到诸如GETURL之类的功能时都会出错，我怀疑这就是您的情况.
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.
要在SpreadsheetGear中实现自定义功能，基本上必须将 Function 类，并覆盖其评估(...)方法，只要SpreadsheetGear在公式中遇到您的自定义函数，就会调用该方法.该文档提供了示例，但您也可以在此处找到功能全面的ASP.NET示例:
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
重要:SpreadsheetGear自定义函数具有许多必须遵守的规则.我强烈建议，您仔细阅读以上链接中提供的Function.Evaluate(...)方法文档的备注"部分所述的规则.这些规则之一是，对单元格的所有访问都必须通过通过 IArgument 参数.
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.
您的GETURL函数似乎可以访问Range，其超链接集合和公式等.SpreadsheetGear的Custom Function API中不允许进行此类操作.您将只能访问 IArguments ，如果将范围传递到自定义函数中，则将是这些单元格的基础计算值.换句话说，上面实现的GETURL函数将无法移植到SpreadsheetGear自定义函数.您可能需要以不访问IRange/IRange.Hyperlinks/etc的方式进行更新.
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.
这篇关于C#SpreadSheetGear:#NAME?如果公式应用于单元格，则为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！