向 Excel 工作表中添加公式会导致 HRESULT:0x800A03EC [英] Adding a formula to Excel worksheet results in HRESULT: 0x800A03EC
问题描述
我在网上搜索了合适的解决方案,但找不到任何有用的东西...
I've searched for an appropriate solution online but couldn't find anything helpful...
在 Excel 工作表中,我需要从数据库表中分配一些值,然后在每个值旁边添加一个公式(取决于同一工作簿中的另一个 Excel 工作表).添加数据效果很好,但添加公式会导致错误.
In an Excel worksheet I need to assign some values from a database table and then add a formula to next to each value (depending on another Excel worksheet in the same workbook). Adding the data works perfectly but adding the formula results in an error.
我正在获取数据并将其添加到工作表中,如下所示:
I'm getting the data and adding it to the sheet like this:
using (SqlConnection conn = new SqlConnection("MyConnectionString"))
using (SqlCommand comm = new SqlCommand("SELECT DISTINCT [MyField] FROM [MyTable]", conn)
{
conn.Open();
using (SqlDataReader reader = comm.ExecuteReader())
{
myStringList.Add("MyField");
if (reader.HasRows)
while (reader.Read())
myStringList.Add(reader.GetString(reader.GetOrdinal("MyField")));
}
}
workbook.Worksheets.Add(After: workbook.Worksheets[workbook.Sheets.Count]);
for (int counter = 1; counter <= myStringList.Count(); counter++)
((Excel.Worksheet)workbook.ActiveSheet).Cells[counter, 1] = myStringList[counter-1];
到目前为止一切顺利.现在我开始解决我的问题.我需要为 B2
、B3
、... 列中的每个使用过的单元格添加一个公式.困难在于我想用 for
循环来做它,因为公式取决于 A
列.
So far so good. Now I get to my problem. I need to add a formula to the cells B2
, B3
, ... for every used cell in column A
. The difficulty is that I want to do it with a for
loop because the formula depends on the column A
.
for (int counter = 2; counter <= myStringList.Count(); counter++)
((Excel.Worksheet)workbook.ActiveSheet).Range["B" + counter].Formula
= $"=VLOOKUP(A{counter};MyOtherWorksheet!$B$2:$B${numberOfRows};1;FALSE)";
numberOfRows
是 MyOtherWorksheet
中 B
列的行数(它在调试器中返回正确的数字,所以这不是问题).
numberOfRows
is the number of rows in column B
in MyOtherWorksheet
(it returns the correct number in debugger, so that's not the problem).
但是当我像这样分配公式时,我收到以下异常但没有任何有用的消息:
But when I assign the formula like this, I'm getting the following exception without any helpful message:
结果:0x800A03EC
HRESULT: 0x800A03EC
我尝试将 .Range["B" + counter]
更改为 .Cells[counter, 2]
甚至尝试使用 .FormulaR1C1
而不是 .Formula
但我得到了同样的例外.
I tried changing .Range["B" + counter]
to .Cells[counter, 2]
and even tried using .FormulaR1C1
instead of .Formula
but I got the same exception.
我错过了什么?
推荐答案
我发现了问题.我不得不将 .Formula
更改为 .FormulaLocal
.
I've found the problem. I had to change .Formula
to .FormulaLocal
.
MSDN description for .FormulaLocal
:
返回或设置对象的公式,使用用户语言中的 A1 样式引用.读/写变体.
Returns or sets the formula for the object, using A1-style references in the language of the user. Read/write Variant.
MSDN description for .Formula
:
返回或设置一个 Variant 值,该值以 A1 样式表示法和宏语言表示对象的公式.
Returns or sets a Variant value that represents the object's formula in A1-style notation and in the macro language.
这篇关于向 Excel 工作表中添加公式会导致 HRESULT:0x800A03EC的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!