我正在将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
发布评论