如何使用C＃加号验证在MS Excel单元格 [英] How to add number validation in MS Excel cell using C#

问题描述

我的目标是限制用户仅1至100 MS Excel单元格的范围之间的输入值。

My objective is to restrict user to enter values only between the range of 1 to 100 in MS Excel cell.

我编程生成Excel文件，但是当我上面添加验证抛出异常为异常来自HRESULT：我已经写0x800A03EC

I am programmatically generating Excel files, but when I add above validation Exception is thrown as Exception from HRESULT: 0x800A03EC

代码如下：

int[] arr = {1,100};
ExcelApp.get_Range(col1, col2).Cells.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, arr, Type.Missing);

在上面的代码 ExcelApp 是一个对象 Microsoft.Office.Interop.Excel.ApplicationClass

In above code ExcelApp is an object of Microsoft.Office.Interop.Excel.ApplicationClass

任何帮助真的赞赏。

推荐答案

您需要添加其他之前删除单元格验证。否则，你将看到验证抛出异常的异常来自HRESULT：0x800A03EC

You need to delete the cell validator before adding another one. Otherwise you will see the validation Exception is thrown as Exception from HRESULT: 0x800A03EC

ExcelApp.get_Range("A1").Cells.Validation.Delete();

ExcelApp.get_Range("A1").Cells.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, delimitedString1To100, Type.Missing);

如果没有单元格验证存在（即第一次），然后删除不会导致问题，其安全在离开

If no cell validator exists (ie first time) then Deleting doesn't cause a problem, its safe to leave in.

在代码的问题是变量改编包含两个项目1安培; 100.我猜在 XLFormatConditionOperator 参数 xlBetween 在 Validation.Add 的参数误导我们。为了使一个参数工作的 XLDVType xlValidateList 的一级方程式参数需要包含所有有效的值1,2 ，3 ... 100：

The problem in the code was the variable arr contained two items 1 & 100. I'm guessing the XLFormatConditionOperator argument xlBetween in Validation.Add's parameter mislead us. To make it work for a argument XLDVType of xlValidateList the Formula1 argument needs to contain all the valid values 1,2,3...100:

var val = new Random();
var delimitedString1To100 = string.Join(",", (int[])Enumerable.Range(1, 100).ToArray());
for (int i = 1; i < 11; i++)
{
    using (var rnCells = xlApp.Range["A" + i.ToString()].WithComCleanup())
    {
        rnCells.Resource.Value2 = val.Next(100);
        rnCells.Resource.Cells.Validation.Delete();
        rnCells.Resource.Cells.Validation.Add(
            Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
            Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,
            Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, delimitedString1To100, Type.Missing);
    }
}

这篇关于如何使用C＃加号验证在MS Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！