Custom Validation Formula is Cleared

Aspose.Cells Product Family
1

Hello,

I have a cell with some custom validation specified. When I populate the spreadsheet using the SmartTags, the formula for the custom validation is cleared. However, everything else in the validation remains the same. The error message, error title, most of the validation critera remain unchanged. Only the formula is cleared.

Another post on the forum indicates that custom validators cannot be setup through the api. The response from Aspose indicates you can set them up in a template, and Aspose will preserve the custom validator. This functionality is not working.

My custom formula for testing is "isNumber(d3)" where d3 is a cell in a row with SmartTags. It makes no difference if I have a SmartTag in the cell with validation, or if a SmartTag is simply in an adjacent cell.

Is this a known bug? If so, when is this scheduled to be fixed.

Thank you.

2

Which version of Aspose.Cells are you using? Have you tried latest v4.1.2? Could you please post your template file here? Thank you.

3

I am using v4.1.2. I have attached a template file that I am using. Cells B2 and C2 both have custom validation setup. After binding to a dataset, the validation is copied down to each row that has data. However, in both columns, the validation formula is cleared.

4

Hi,

We checked your template file. Actually, Aspose.Cells do not support to create / manage Custom validation. I think that's why the validation formula is omitted when data is copied down to the columns. We will figure it out and get back to you soon.

Thank you.

5

Hello,

That’s interesting. According to this thread, Custom Validation, Aspose.Cells should preserve the existing custom validation. We are attempting to do just what was suggested.

Please do let me know if/when this will be fixed or if there is a workaround.

Thank you.

6

Hi,

Well, Aspose.Cells do support to preserve those template workbooks having custom validations used in them and save and save as the excel files with the custom validation. But with Smart Markers it's a different scenario. There are a few limitations while using Smart markers in the template files and we are enhancing the smart markers these days with other important tasks. We will definitely figure it out ASAP.

Thanks for your patience.

7

Please try this fix.

8

I performed a quick test, and this looks like it works. Thanks for your help and the quick solution.

9

Hi Amjad,

Did anything changed since your reply?

Do you support creating/managing custom validations now?

Thanks,

Ofir

Amjad Sahi:

Hi,

We checked your template file. Actually, Aspose.Cells do not support to create / manage Custom validation. I think that's why the validation formula is omitted when data is copied down to the columns. We will figure it out and get back to you soon.

Thank you.

10

Hi Ofir,

Thanks for following up.

Yes, we did support custom validation some months ago. In the new versions of Aspose.Cells this funtionality is available. Please try the latest version Aspose.Cells for .NET (Latest Version)

Sample code:

Workbook workBook = new Workbook();
FileStream fileStream = null;
try
{

Worksheet workSheet = workBook.Worksheets[0];
Validations validations = workSheet.Validations;
//Creating a Validation object
Validation validation = validations[validations.Add()];
//Setting the custom validation type
validation.Type = Aspose.Cells.ValidationType.Custom;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorMessage = "This cell cannot be changed"; 
validation.Formula1 = "=A1";
validation.ShowError = true;
CellArea area = new CellArea();
area.StartRow =0;
area.EndRow = 0;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList.Add(area);
// Save the excel file.
workBook.Save(@"d:\\test\\Custom_Protection.xls", FileFormatType.Excel2003);

}

catch (Exception ex)

{

}

Thank you.