Free Support Forum - aspose.com

Validation (Type=WholeNumber- Operator=Between)

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

Hi Zijad,

Which version of Aspose.Cells you are using? Kindly use the latest version (4.3) (you may download the latest hotfix @: http://www.aspose.com/Community/Forums/thread/83325.aspx).

Following is my code which works fine. You may check the output file (attached):

Workbook workbook = new Workbook();
//Accessing the Validations collection of the worksheet
Validations validations = workbook.Worksheets[0].Validations;
//Creating a Validation object
Validation validation = validations[validations.Add()];
//Setting the validation type to whole number
validation.Type = Aspose.Cells.ValidationType.WholeNumber;
//Setting the operator for validation to Between
validation.Operator = OperatorType.Between;
//Setting the minimum value for the validation
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.

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

Regards
Zijad

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"

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.

Hi,

Please try this fix.

THX, it's fixed.

greetz Koen