在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];

到目前为止，一切都很好.现在我解决了我的问题.我需要为 A 列中每个使用的单元格添加一个公式到单元格 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" +计数器] 更改为 .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.

.FormulaLocal 的MSDN描述:

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.

.Formula 的MSDN描述:

MSDN description for .Formula:

以A1样式表示法和宏语言返回或设置一个Variant值，该值代表对象的公式.

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屋！