Unable to get Worksheet.Validations to work properly

Aspose.Cells Product Family
1

Aspose team,



I’m attempting to setup decimal validation in a worksheet that’s generated at runtime. The validation checks that the data entered into the target CellArea is between 0 and 9999999. It doesn’t seem to be working; negative values can be entered without getting an error pop up.



The between validation can be setup directly in the open sheet, which seems to work just fine.



The code I am using (pasted below) is pulled almost directly out of the documentation (Aspose.Total for .NET|Documentation), under the “Perform Decimal Validation” section. An example program is also attached. It’s entirely possible I’m missing some property setting somewhere as this is my first attempt to use the Validations API.



Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

Cells wsCells = ws.Cells;



int row = 0, col = 0;



for (row = 0; row
{

for (col = 0; col < 10; col++)

{

wsCells[row, col].PutValue(Convert.ToDecimal(row + col));

}

}



int valId = ws.Validations.Add();

Validation v = ws.Validations[valId];



v.Type = ValidationType.Decimal;

v.Operator = OperatorType.Between;

v.Formula1 = “0”;

v.Formula2 = “9999999”;

v.ErrorMessage = “The number is not valid.”;

v.ErrorTitle = “Error Title”;

v.AlertStyle = ValidationAlertType.Stop;

v.ShowError = true;



CellArea cellArea = new CellArea();

cellArea.EndColumn = col;

cellArea.EndRow = row;

cellArea.StartColumn = 0;

cellArea.StartRow = 0;



v.AddArea(cellArea);



wb.Save(fileName, format);



One other issue I ran into: if validation is added to the sheet but a CellArea is not defined, the .Save() method throws a generic NullReferenceException. This is not a huge problem, however the reason for the exception is not very clear.



If at least 1 CellArea is required when adding a new validation, I think it might be better to throw a specific exception on .Save() or force the Validations.Add() method to require a CellArea object.



I am using Aspose.Cells v6.0.0.1.



Thanks for your help!

Jim

2

Hi,

Please check the code below, it is exactly as yours and the validation is working fine in my case. I have tested it with latest version Aspose.Cells for .NET v6.0.0.1

Please see the output.xls file generated by the code and tries to enter -1 or 10000000, it will display error message.

C#


Workbook workbook = new Workbook();


int validationIdx = workbook.Worksheets[0].Validations.Add();

Validation validation = workbook.Worksheets[0].Validations[validationIdx];


validation.Type = ValidationType.Decimal;


validation.Operator = OperatorType.Between;


validation.Formula1 = “0”;


validation.Formula2 = “9999999”;


validation.ErrorMessage = “Should be between 0 and 9999999”;


validation.ErrorTitle = “Error Title”;

validation.AlertStyle = ValidationAlertType.Stop;

validation.ShowError = true;


CellArea area;


area.StartColumn = 0;

area.StartRow = 0;


area.EndColumn = 0;

area.EndRow = 9;



validation.AreaList.Add(area);


workbook.Save(“f:\shak-data-rw\downloads\output.xls”, SaveFormat.Excel97To2003);



3

Hi Shakeel,



I appreciate the quick reply. I copied your code line for line and it works, in Xls, xlsb and xlsx formats.



The only real difference between the two is I’m using the validation.AddArea() method. You’re using validation.AreaList.Add().



Is there a bug in Validation.AddArea(), or am I just using the API improperly?



Thx!

Jim

4

Hi,

I have logged this issue as a bug in our database. We will let you know why two methods behave differently or if it is a bug, we will fix it.

This issue has been logged as CELLSNET-28924.

5

Hi,

We have fixed this issue. Please download: Aspose.Cells
for .NET v6.0.0.4

6

The issues you have found earlier (filed as 28924) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
7

I saw .net for cells 7.2.2.0 Validation.AddArea still has bugs.Thanks.

8
Hi,

cqcmdwym:
I saw .net for cells 7.2.2.0 Validation.AddArea still has bugs.Thanks.

Well, it might be an issue with older v7.2.2 regarding Validation.AddArea() that you are using. But, I tested it with our latest version/fix e.g: Aspose.Cells for .NET v7.3.4.3, it works absolutely fine with it. Please try our latest version/fix.

Sample code:

Workbook workbook = new Workbook();


int validationIdx = workbook.Worksheets[0].Validations.Add();

Validation validation = workbook.Worksheets[0].Validations[validationIdx];


validation.Type = ValidationType.Decimal;


validation.Operator = OperatorType.Between;


validation.Formula1 = "0";


validation.Formula2 = "9999999";


validation.ErrorMessage = "Should be between 0 and 9999999";


validation.ErrorTitle = "Error Title";

validation.AlertStyle = ValidationAlertType.Stop;

validation.ShowError = true;


CellArea area;


area.StartColumn = 0;

area.StartRow = 0;


area.EndColumn = 0;

area.EndRow = 9;



validation.AddArea(area);


workbook.Save("e:\\test2\\out.xls");

Thank you.
9

Hi Amjad Sahi,

Thanks for your quick reply.

10

Hi,

Thanks for your posting and using Aspose.Cells.

We are pleased to know that Validation.AddArea bug does not exist any more, so you can use it safely now.

Let us know if you have any other questions or you face any other issue, we will be glad to assist you further.

Also we recommend you to download and try the Offline Java Demos of Aspose.Cells for Java.

It contains simple runnable console examples which will help you to get familiar with Aspose.Cells for Java API (s) quickly and you will be able to use its existing features in your applications easily.