C# Excel 依赖下拉列表与验证和间接函数

Question

以下是使用C#动态创建依赖下拉列表的通用尝试。当从pick1选择值“A”时，pick2应该显示来自SecondaryRangeA的值。

这段代码几乎可以正常工作，但它显示的不是SecondaryRangeA，而是字面值“A”。

pick2.Validation.Add(XlDVType.xlValidateList, 
                     XlDVAlertStyle.xlValidAlertStop, 
                     XlFormatConditionOperator.xlBetween, 
                     "=INDIRECT(\"A5\")");

当我打开导出的Excel并修改数据验证时，它会显示公式。

=INDIRECT("A5")

如果我在Excel中手动修改公式以排除引号，它的表现符合预期。

=INDIRECT(A5)

当我将代码修改为以下内容时，会出现异常。有任何想法吗？

pick2.Validation.Add(XlDVType.xlValidateList, 
                     XlDVAlertStyle.xlValidAlertStop, 
                     XlFormatConditionOperator.xlBetween, 
                     "=INDIRECT(A5)");

异常：

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  Message=Exception from HRESULT: 0x800A03EC
Source=""


StackTrace:
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Validation.Add(XlDVType Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)
   at TestExcelValidation.Program.Main(String[] args) in C:\TFS\ExcelInterop\TestExcelValidation\Program.cs:line 44
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

完整示例：

using System.Collections.Generic;
using System.IO;
using System.Linq;
using Microsoft.Office.Interop.Excel;

namespace TestExcelValidation
{
    class Program
    {
        static void Main(string[] args)
        {
            string temporaryPath = Path.GetTempPath();
            string temporaryFile = Path.GetTempFileName();
            Application appl = new Application();
            appl.Visible = true;
            Workbook workbook = appl.Workbooks.Open(temporaryFile, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Worksheet worksheet = (Worksheet)workbook.Worksheets.Add();



            List<string> primaryList = new List<string>();
            primaryList.Add("A");
            primaryList.Add("B");

            List<string> secondaryListA = new List<string>();
            secondaryListA.Add("A1");
            secondaryListA.Add("A2");
            secondaryListA.Add("A3");

            List<string> secondaryListB = new List<string>();
            secondaryListB.Add("B1");
            secondaryListB.Add("B2");
            secondaryListB.Add("B3");

            Range primaryRange = AddToExcelNamedRange(worksheet, primaryList, 'A', 1, "PrimaryRange");
            Range secondaryRangeA = AddToExcelNamedRange(worksheet, secondaryListA, 'B', 1, "A");
            Range secondaryRangeB = AddToExcelNamedRange(worksheet, secondaryListB, 'C', 1, "B");

            Range pick1 = worksheet.Range["A5"];
            pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange");
            Range pick2 = worksheet.Range["A6"];
            pick2.Validation.Delete();
            pick2.NumberFormat = "Text";
            pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(\"A5\")");
            pick2.Validation.InCellDropdown = true;
            pick2.Validation.IgnoreBlank = true;
        }

        private static Range AddToExcelNamedRange(Worksheet worksheet, List<string> primaryList, char col, int row, string rangeName)
        {
            Range range = worksheet.Range[col.ToString() + row.ToString(), col.ToString() + primaryList.Count().ToString()];
            range.Name = rangeName;
            foreach (string item in primaryList)
            {
                worksheet.Cells[row, col - 64] = item;
                row++;
            }
            return range;
        }
    }
}

- Joshua Lowry

Answer 1

我有一个解决方法，但是我想知道为什么这个不起作用。我相信我以后还会遇到这种情况。

以下是英文答案，只有上帝知道其他两个人在说什么。

问题

使用C#（或VBA）在Excel中通过验证添加级联下拉列表时会导致COMException 0x800A03EC。

原因

它不起作用的原因是源实际上是空的。

让我向您展示我是如何解决这个问题的。我在Excel中注入了一个宏并运行它：

Range pick1 = worksheet.Range["A5"];
pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange");
Range pick2 = worksheet.Range["A6"];

StringBuilder sb = new StringBuilder();
sb.Append("Sub InsertCascadingDropDown()" + Environment.NewLine);
sb.Append("    Range(\"A6\").Select" + Environment.NewLine);
sb.Append("    With Selection.Validation" + Environment.NewLine);
sb.Append("        .Delete" + Environment.NewLine);
sb.Append("        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:=\"=INDIRECT(A5)\"" + Environment.NewLine);
sb.Append("        .IgnoreBlank = True" + Environment.NewLine);
sb.Append("        .InCellDropdown = True" + Environment.NewLine);
sb.Append("        .ShowInput = True" + Environment.NewLine);
sb.Append("        .ShowError = True" + Environment.NewLine);
sb.Append("    End With" + Environment.NewLine);
sb.Append("End Sub" + Environment.NewLine);

//You need to add a COM reference to Microsoft Visual Basic for Applications Extensibility for this to work
var xlmodule = workbook.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); 

xlmodule.CodeModule.AddFromString(sb.ToString()); 
appl.Run("InsertCascadingDropDown");

当宏执行添加验证的代码行时，会出现运行时错误“1004”。

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(A5)"

这让我相信 selection 对象没有被定义（或者更确切地说，它是空的，这取决于你如何解释单词 Selection）。

解决方案

我尝试了一些操作，并在发现以下内容时手动添加验证来停止代码控制：

该源目前评估为错误。

这是一个明显的证据，选择对象不为空，实际上是A5下拉列表的选择/值为空！

添加级联下拉列表需要其父级具有值！

所以你只需要这样做：

pick1.Value2 = "A";  //< set the parent to have a value
pick2.Validation.Delete();  //<- this is not really needed, unless you run this again
pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(A5)");

- Jeremy Thompson

Answer 2

至少在Excel 2007和2010中，=Indirect(A5)（不带引号）在单元格中使用时会评估为#REF。我认为从C#传递此代码的方式与异常有关（因为它将评估为错误）。此外，手动创建验证时使用相同的函数也会在Excel中显示评估为错误的消息。编辑：尽管在更多的研究中，我在某些方面被纠正了。

Indirect()期望从A1或R1C1格式中获得字符串值作为输入，而不是实际的单元格引用。除非目标范围是单元格引用，例如A5是A1。

根据MSDN，只有当文件处于打开且在内存中时，Indirect()才会进行计算MSDN 151323。打开工作簿并更改验证列表并正确评估它并不意味着在运行C#代码时不存在错误。

- Kevin

Answer 3

如果例如A5中的值为B，那么=INDIRECT(A5)与=INDIRECT("B")相同，这与=B相同，但不是有效的公式或单元格引用。 =INDIRECT("A5")与=A5相同。

- Slai