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

Question

My goal is to restrict the user to enter values ​​only in the range from 1 to 100 in the MS Excel cell.

I programmatically generate Excel files, but when I add the above checks, the Exception is selected as Exception from HRESULT: 0x800A03EC

The code I wrote looks like this:

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

There is a Microsoft.Office.Interop.Excel.ApplicationClass object in the above ExcelApp code

Any help really appreciated.

Answer 1

You need to remove the cell check item before adding another. Otherwise, you will see the check Exception selected 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 the cell validator does not exist (i.e. the first time), then deleting does not cause a problem; it is safe to leave it.

Change / Decision:

The problem in the code was an arr variable containing two elements 1 and 100. I assume that the argument XLFormatConditionOperator xlBetween in Validation.Add is misleading to us. To make it work for the XLDVType xlValidateList argument, the Formula1 argument must contain all 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); } } 

Answer 2

I don’t think you can pass an array of integers to .NET. You need to pass a line separated by commas, or a link to a line in a worksheet sheet. From docs :

xlValidateList - Formula 1 is required; Formula 2 is ignored. Formula 1 must contain either a comma-separated list of values ​​or a link to a sheet for this list.

For example:

 ExcelApp.get_Range(col1, col2).Cells.Validation.Add( XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlBetween, "1,100" ); 

Answer 3

what you want can be done with a macro that can be assigned when the cell value changes. to include a macro in dynamically created excel, visit http://www.eggheadcafe.com/articles/create_macro_at_runtime_in_dotnet.asp . I implemented the same sometimes backwards, and it is also easier.