在 C# 函数中设置 Excel 单元格的公式
我已经为 Excel 创建了一个自动化插件，作为 C# 类库实现，其中包含 UDF 包装器。（我不使用 VSTO）
我想在 UDF 中执行的操作是：
1> 获取单元格地址输入公式的位置；
2>将该单元格的公式设置为我自己的公式之一；
我已经完成了第1点>如下所示：
Microsoft.Office.Interop.Excel.Application excelApp = (Microsoft.Office.Interop.Excel.Application)Marshal.GetActiveObject("Excel.Application");
Microsoft.Office.Interop.Excel.Range target = (Microsoft.Office.Interop.Excel.Range)excelApp.get_Caller(System.Type.Missing);
string cellAddress = target.get_Address(Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value);
//Perform some operations based on address
//Assign a formula to my cell (This does not work !)
target.Formula ="=SUM(2,2)"
这应该根据 此处。
我做错了什么？还有其他方法可以设置单元格的公式吗？
谢谢，
I have created an Automation Add-in for Excel,implemented as a C# class library, which contains a UDF wrapper.(I do not use VSTO)
What I would like to do in my UDF, is to:
1>get the cell address where the formula was entered;
2>set the formula of that cell to one of my own;
I have already done point 1> as follows:
Microsoft.Office.Interop.Excel.Application excelApp = (Microsoft.Office.Interop.Excel.Application)Marshal.GetActiveObject("Excel.Application");
Microsoft.Office.Interop.Excel.Range target = (Microsoft.Office.Interop.Excel.Range)excelApp.get_Caller(System.Type.Missing);
string cellAddress = target.get_Address(Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value);
//Perform some operations based on address
//Assign a formula to my cell (This does not work !)
target.Formula ="=SUM(2,2)"
This should work according to the MSDN documentation from here.
What am I doing wrong ? Is there any other way I can set the formula of the cell?
Thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
UDF 只能将值返回到其调用单元格 - 不允许覆盖其自己的公式（或更改任何其他单元格的内容）。您需要创建一个命令来执行此操作。
A UDF can only return a value to its calling cell - it is not allowed to overwrite its own formula (or alter the contents of any other cell). You would need to create a command to do that.
'=SUM(2,2)' 与 '2+2' 相同；
如果你想添加单元格，例如'A1'和'A4'，你应该使用这种格式'=SUM(A1,A4)'
或者你想添加从'A1'到'A4'的单元格，你应该使用' =SUM(A1:A4)'。
或整列“B”、“=SUM(B:B)”。
'=SUM(2,2)' is same to '2+2';
if you want add the cells, for example 'A1' and 'A4', you should use this format '=SUM(A1,A4)'
or you want add the cell from 'A1' to 'A4', you shou use '=SUM(A1:A4)'.
or whole column 'B', '=SUM(B:B)'.
尝试这个目标 .set_Value(RangeValueDataType: Type.Missing, value: "=SUM(2,2)");
try this target .set_Value(RangeValueDataType: Type.Missing, value: "=SUM(2,2)");