How to do validation for a cell using Aspose.Cells.Validation

Hi,

In a cell I need to check whether the entered value exist in database or not.

For that I used the below code,

Dim valFactor As Aspose.Cells.Validation

Dim area As Aspose.Cells.CellArea

valFactor = WorkSheet.Validations(WorkSheet.Validations.Add())

valFactor.Type = Aspose.Cells.ValidationType.List

valFactor.Operator = Aspose.Cells.OperatorType.None

'valFactor.InCellDropDown = True

valFactor.ShowError = True

valFactor.AlertStyle = Aspose.Cells.ValidationAlertType.Stop

valFactor.ErrorTitle = "Error"

valFactor.ErrorMessage = "Selected Meter Number doesnot exist"

valFactor.Formula1 = String.Join(",", str)

area = New Aspose.Cells.CellArea

'cell = GetCellFromEnum(Of CellPosition)(CellPosition.invoiceMeterColumn)

area.StartColumn = cell.Column

area.EndColumn = cell.Column

area.StartRow = cell.Row

area.EndRow = cell.Row

valFactor.AreaList.Add(area)

Am getting error in the commented line of code. The error is "Object reference not set to an instance of an object".

In CellPosition.invoiceMeterColumn am passing the value as 5.

Please guide me how to achieve this functionality?

Hi,

I am not sure about your issue, also you provided incomplete sample code in which you have used some variables, objects, procedures etc. for which I am not sure.

Please try our latest version/fix:
Aspose.Cells for Java (Latest Version)
Aspose.Cells for .NET (Latest Version)

If you still find the issue, kindly do create a sample console application (runnable) with template Excel file(s), you may use hard coded values and use DataTable objects dynamically (if you have used databases) etc., so that we could run your issue and find it (if possible).

please zip the application and post it here to show the issue, we will check your issue.

Moreover, please check the document for your reference on how to use validations using Aspose.Cells for .NET APIs:

Data Filtering
Data Validation

Thank you.

Please find the attached excel sheet.

In the excel sheet, If I enter some value in InvoiceMeter column and go to next tab, it should show an error message saying "Meter No doesnot exist" if the entered no not present in database.

The database values are taken in an datatable and giving to Validation.Formula.

Hi,

I have checked your file i.e Invoice_Sample.xls

Could you please create a sample validation manually using Ms-Excel inside it with your desired behavior and then attach it again?

We will look into your file using Aspose.Cells API and then let you know a sample code.

Hi,


As I understand you so far, you might be using some external source, e.g database/ data table etc. Well, you need to import your datatable/source to your template worksheet cells, then you should specify the range for your Fromula1 of data Validation.

Please see the sample code. I have used your template file. I created a new worksheet in it and added a range to fill it with some values for Formula1 (for Meter Number). Then, I applied the data Validation to the range i.e. D4:D8, I set reference to the range (of the second worksheet) as data Validation’s Formula1. I have also attached my output file here. When you input some value other than “0,1,2,3”, it will prompt you an Error message accordingly.

Sample code:

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

’ Add a new worksheet and access it.
Dim i As Integer = workbook.Worksheets.Add()
Dim worksheet2 As Worksheet = workbook.Worksheets(i)

’ Create a range in the second worksheet.
Dim range As Range = worksheet2.Cells.CreateRange(“E1”, “E4”)

’ Name the range.
range.Name = “MyRange”

’ Fill different cells with data in the range.
range(0, 0).PutValue(0)
range(1, 0).PutValue(1)
range(2, 0).PutValue(2)
range(3, 0).PutValue(3)

’ 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.
'validation.InCellDropDown = true;

’ 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:D8.
Dim area As CellArea
area.StartRow = 3
area.EndRow = 7
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\out_Invoice_Sample.xls”)

If you still have any confusion, kindly do create a runnable code same as mine to reproduce the issue, we will check it soon.

Thank you.

Dim valFactor As Aspose.Cells.Validation
Dim area As Aspose.Cells.CellArea

valFactor = WorkSheet.Validations(WorkSheet.Validations.Add())

valFactor.Operator = Aspose.Cells.OperatorType.None
valFactor.ShowError = True
valFactor.AlertStyle = Aspose.Cells.ValidationAlertType.Stop
valFactor.ErrorTitle = "Error"
valFactor.ErrorMessage = "Selected Meter Number doesnot exist"

Dim StrValue as String = "1,2,3,4,8,9,11,13,15,76,88,34,66"

valFactor.Formula1 = String.Join(",",StrValue)

area = New Aspose.Cells.CellArea

area.StartColumn = 3
area.EndColumn = 3

valFactor.AreaList.Add(area)

workbook.Save("D:\out_Invoice_Sample.xls")

Please find the attached code above.
* I dont want to show any dropdown in the Invoice Meter cell
* If I enter a value (say 50) in the "Invoice Meter" column in the excel, that value (50) should
be there in StrValue (which is shown in the sample code above).
* If the value 50 not there in StrValue then we need to display an error message.
* Also, in the 3rd column whichever row I enter the value, valiation should occur.There is no limitation for the row like til 5th row or til 10th row.
* I dont want to use any range I guess since am not displaying any value in the cell.
* After I enter the value in the Invoice Meter cell, only then validation should occur.

Hope am clear now. If not please specify where am confusing again.

Hi,


Please create a simple Excel file by manually created in MS Excel 2003 - 2010 (as Shakeel Faiz requested in his earlier post) containing your desired data Validation applied. Attach the Excel file here. We will provide you the relevant code on how to apply Data Validation as per your needs.

Thank you.

Am sorry i dono how to apply this validation using MS Excel since am new to excel itself.

So please get to know about my problem and provide me a solution please.

I hope I was clear in my previous post.

If the entered data doesnt match with the string array then throw an error message. This is the requirement.

Please provide me an solution.

Hi,

For your needs, you should apply list validation.

First you will insert all of your list values inside worksheet cells then in list validation, you will specify data source of your list validation as shown below in a screenshot.

As you can see in a screenshot, I have placed all of your values inside cells E7:E19 then I specified the data source of the list validation as =Sheet1!$E$7:$E$19

Screenshot:

Hi,


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.

Sample code:

’ 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.

Thank you so much for the detailed response. It helped me a lot.

I got the functionality what I needed

Thanks for you both

Hi,


Good to know that finally you got what you require for.

Thanks and have a good day!