How to add number validation in MS Excel cell using C#

Question

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

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

code I've written is as below:

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);

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

Any help is really appreciated.

Answer 1

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.

Edit/Solution:

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);
    }
}