You will always need to fill a range of cells in a worksheet (in the workbook) if you need to apply the Data Validation, if you do not fill data range in the cells, you cannot apply the data validation in MS Excel. For your need I have define the range in the array (first I created a string array then I converted to int array accordingly). Now I filled a second worksheet (adding a new worksheet to the book) with the array values to the cells. Now I made this second worksheet hidden (as per your need, so no other user could see the worksheet). After this, I applied the data validation to D column in your template file i.e. D4: D65536 as per your needs. Also, when you click on any cell in the range i.e. D4: D65536, no drop down will be available as per your requirement. The output file is attached.
’ Create a workbook object.
Dim workbook As New Workbook(“e:\test2\Invoice_Sample.xls”)
’ Get the first worksheet.
Dim worksheet1 As Worksheet = workbook.Worksheets(0)
’ Cell Values.
Dim values As String = “1,2,3,4,8,9,11,13,15,76,88,34,66”
'split the values into array.
Dim arr() As String = values.Split(","c)
’ Add a new worksheet and access it.
Dim i As Integer = workbook.Worksheets.Add()
Dim worksheet2 As Worksheet = workbook.Worksheets(i)
'Convert string array to int array
'define an int array;
Dim arr2(arr.Length - 1) As Integer
For index As Integer = 0 To arr.Length - 1
arr2(index) = Convert.ToInt32(arr(index))
Next index
'import the array.
worksheet2.Cells.ImportArray(arr2, 0, 0, False)
’ Create a range in the second worksheet.
Dim range As Range = worksheet2.Cells.CreateRange(“A1”, CellsHelper.CellIndexToName(0,worksheet2.Cells.MaxDataColumn).ToString())
’ Name the range.
range.Name = “MyRange”
'Hide the second worksheet .
worksheet2.IsVisible = False
’ Get the validations collection.
Dim validations As ValidationCollection = worksheet1.Validations
’ Create a new validation to the validations list.
Dim validation As Validation = validations(validations.Add())
’ Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List
’ Set the operator.
validation.Operator = OperatorType.None
’ Set the in cell drop down disabled.
validation.InCellDropDown = False
’ Set the formula1.
validation.Formula1 = “=MyRange”
’ Enable it to show error.
validation.ShowError = True
’ Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop
’ Set the error title.
validation.ErrorTitle = “Error”
’ Set the error message.
validation.ErrorMessage = “Selected Meter Number does not exist”
’ Specify the validation area D4:D65536.
Dim area As CellArea
area.StartRow = 3
area.EndRow = 65535
area.StartColumn = 3
area.EndColumn = 3
’ Add the validation area.
validation.AreaList.Add(area)
worksheet1.Cells(“D4”).PutValue(3)
’ Save the excel file.
workbook.Save(“e:\test2\Myoutput_Invoice_Sample.xls”)
Hopefully, this time it will suit your needs, also, I think you may amend/update my sample code accordingly for your further needs.
Thank you.