ExcelDNA引发异常访问Range.Value2 [英] ExcelDNA throwing exception accessing Range.Value2

问题描述

我正在将excel插件(使用的shimloader)移植到exceldna，是的，我已经看到了其他SO(以及SO之外)的问题，但是没有任何问题可以解决我的问题，我希望有新的解决方案.

I am porting an excel addin (used shimloader) to exceldna, and yeah, I have seen the other SO (and off SO) questions but nothing resolves my question, and I'm hoping there are newer solutions.

代码很简单.

[ExcelFunction(Name="DoSomething")]
string DoSomething()
{
     var xl = ExcelDna.Application;    
     var callerCell = xl.Caller;
     var row = getRow(excelReference.RowFirst+1, callerCell.WorkSheet) ;
}

在GetRow()中:

In GetRow():

var row = (Range)worksheet.Rows[row];
var cell = (Range)bracketRow.Columns[4];

当我检查调试器时，我可以看到检索到的单元格是100％正确的，因为 cell.FormulaLocal 与excel行和列公式匹配.

When I check debugger, I can see the retrieved cell is 100% correct because cell.FormulaLocal matches the excel row and column formula.

FormulaLocal 中的值是"OtherSheet！A12" .

但是由于某种原因，每当我尝试 cell.Value2 时，它都会引发 COMException 并没有其他问题.这不是多线程应用程序，我不明白为什么会这样.

But for some reason, whenever I try cell.Value2, it throws a COMException and nothing else. This is not a multithreaded application and I can't understand why this is happening.

有什么想法吗?

当我将公式修改为该工作表引用成功时应该获得的值时，它不会抛出.

When I modify the formula to the value it should have gotten had the sheet reference been successful, it doesn't throw.

我通过在Excel函数中添加 IsMacroType = true 属性来解决此问题.但是现在 xl.Caller 返回 null ，argh

EDIT 2: I got around this by adding IsMacroType=true attribute to the excel function. But now xl.Caller returns null, argh

推荐答案

需要解决两个问题:如果单元格具有无效值，例如range.Value2抛出COMException.Excel中的#VALUE.如果单元格引用了同一工作簿中的另一个工作表，例如range.Value2抛出COMException."OtherSheet！A2"

Two issues needed solving: range.Value2 threw a COMException if the cell has an invalid value e.g. #VALUE in excel. range.Value2 threw a COMException if the cell referenced another worksheet in the same workbook e.g. "OtherSheet!A2"

为解决此问题，我将IsMacroType属性设置为true:

To solve this, I set the IsMacroType attribute to true:

[ExcelFunction(Name="DoSomething",IsMacroType=true)]
string DoSomething()
{
     var xl = ExcelDna.Application;    
     var callerCell = xl.Caller;
     var row = getRow(excelReference.RowFirst+1, callerCell.WorkSheet) ;
}

现在的问题是IsMacroType导致 xl.Caller 现在将返回null.

The problem now though is, IsMacroType causes xl.Caller will now return null.

我通过以下方法解决了这个问题:

I got around this by:

ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

            string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,reference);

            int index = sheetName.IndexOf(']', 0) + 1;
            int endIndex = sheetName.Length - index;
            sheetName = sheetName.Substring(index, endIndex);
            var worksheet = (Worksheet)xl.ActiveWorkbook.Sheets[sheetName];

这是我第一次参加Excel竞赛，启用IsMacroType是否有副作用?因为我看到@Govert表达了一些不确定行为的担忧...

This is my first rodeo to Excel world, is there any side effect to enabling IsMacroType? 'Cause I saw @Govert expressing some concerns of undefined behavior...

这篇关于ExcelDNA引发异常访问Range.Value2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！