Validation (Type=WholeNumber- Operator=Between)

Aspose.Cells Product Family
1 

I’m using a similar code-fragment like below to define an validated CellArea:

…
Validations validations = worksheet.Validations;
validation = validations[validations.Add()];
validation.Type = ValidationType.WholeNumber;
validation.Operator = OperatorType.Between;
validation.Formula1 = “0”;
validation.Formula2 = “5”;
validation.ErrorMessage = “Error…”;
…

CellArea area;
area.StartRow = startrow;
area.EndRow = endrow;
area.StartColumn = startcol;
area.EndColumn = endcol;
validation.AreaList.Add(area);
…

The Validation is applied but it doesn’t work as expected: the only accepted
value in the validated area is 0. For any nonzero number I’m getting the defined ErrorMessage. 
(Expected behaviour: All values from [0…5] should be legal).

In the old Version (Aspose Excel 3.4.6) this way of using validations worked fine.

Thanks for any help.

Regards
Zijad

2
Hi Zijad,

Which version of Aspose.Cells you are using? Kindly use the latest version (4.3) (you may download the latest hotfix @: <A href="</A>).</P> <P>Following is my code which works fine. You may check the output file (attached):</P> <P> Workbook workbook = new Workbook();<BR> //Accessing the Validations collection of the worksheet<BR> Validations validations = workbook.Worksheets[0].Validations;<BR> //Creating a Validation object<BR> Validation validation = validations[validations.Add()];<BR> //Setting the validation type to whole number<BR> validation.Type = Aspose.Cells.ValidationType.WholeNumber;<BR> //Setting the operator for validation to Between<BR> validation.Operator = OperatorType.Between;<BR> //Setting the minimum value for the validation<BR> validation.Formula1 = "0";
//Setting the maximum value for the validation
validation.Formula2 = "5";
validation.ErrorMessage = "Error....";
//Applying the validation to a range of cells from A1 to B2 using the
//CellArea structure
CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;
//Adding the cell area to Validation
validation.AreaList.Add(area);
workbook.Save("d:\\test\\wholenum.xls");

Thank you.

3

The problem is now gone with that hotfix. Thanks Amjad.

Regards
Zijad

4

I'm currently using the latest version (4.3) and I just want to change an existing validation.

The existing validation must be between 0:00 and 23:59.

Worksheet sheetnext = myWorkbook.Worksheet[m_nextSheet]

Validations validations = sheetnext.Validations;

Validation validation = validations[0];

validation.ErrorMessage = "Test";

validation.Formula2 = "23:59";

myWorkBook.Save(_filename, FileFormatType.Default)

Result: --> formula2 is still 0:0:0 and errormessage is "Test"

5

Hi,

Thanks for pointing it out.

Yes, we found the problem you have mentioned, We can easily create time validation from the scratch but cannot manipulate / change the existing time validation. We will figure it out soon.

Thank you.

6

Hi,

Please try this fix.

7

THX, it's fixed.

greetz Koen