IgnoreBlank validation fails

Aspose.Cells Product Family
1

Hi,


I am using Aspose.Cells to generate Excelsheet. I have a dropdown list which should throw an error if no selection is made or if a selection is deleted. I am using the following piece of code taken from your sample codes:

Workbook workbook = new Workbook();

// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];

// Add a new worksheet and access it.
int i = workbook.Worksheets.Add();

Worksheet worksheet2 = workbook.Worksheets[i];

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

// Name the range.
range.Name = “MyRange”;

// Fill different cells with data in the range.
range[0, 0].PutValue(“Blue”);
range[1, 0].PutValue(“Red”);
range[2, 0].PutValue(“Green”);
range[3, 0].PutValue(“Yellow”);

// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;

// Create a new validation to the validations list.
Validation 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”;
validation.IgnoreBlank = false;

// Set the error message.
validation.ErrorMessage = “Please select a color from the list”;

// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;

// Add the validation area.
validation.AreaList.Add(area);

workbook.Save(“C:\XLProj\validations.xls”);

Problem is, if you use the Delete key on the dropdown cell to remove the selected option, “IgnoreBlank” validation does not kick in. It, however, works fine if you do the same with the Backspace key. I have added the code as an attachment. Can you please suggest a fix for this?
2

Hi,


Thanks for providing us cod segment and some details.

I have tested your scenario/case a bit. I have performed the similar steps as per you sample code segment in MS Excel manually and found Aspose.Cells follows MS Excel standards which it should. This is behavior of Data Validation (List) of MS Excel. If you think MS Excel works other way, please create a sample file manually in MS Excel with your desired Data Validation set on some cells, save the file and provide it here with all the details, we will check on how to do it using Aspose.Cells APIs.

Thank you.
3

Hi Amjad,


I tested it with MS Excel and it is indeed Excel’s problem. Thanks for clarifying the issue.

Regards,
Ishita
4

Hi,


Good to know that it clarifies you a bit.

Feel free to contact us any time if you have further queries or issue.

Thanks,