Create custom validation for Excel 2003

Aspose.Cells Product Family
1

Hi,


I want to produce files with cutom data validation using Aspose Cells v.17.1.0.
I am testing this for both xls and xlsx files and when used for xls the resutling data validation appears as custom but has no formula stored, so something is not working.

Is it so that Excel 2003 did not support custom data validations?
Or am I doing something wrong in the code snippet below? (in cell A1 I want to require a text that is longer than 2 characters but shorter than 10. And if the user chooses to enter a formula that does not evaluate and gives an error code I want to accept that as well because I will ignore such cells anyway)

<pre style=“background-color: rgb(255, 255, 255); font-family: “Courier New”;”>Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
int col1 = 0;
int col2 = 0;
int row1 = 0;
int row2 = 0;

String formula1 = “IF(ISERROR(A1),TRUE,AND(LEN(A1)<10,LEN(A1)>2))”;

ValidationCollection validations = ws.getValidations();

CellArea area = new CellArea();
area.StartColumn = col1;
area.StartRow = row1;
area.EndColumn = col2;
area.EndRow = row2;

Validation validation = validations.get(validations.add(area));

validation.setType(ValidationType.CUSTOM);
validation.setInCellDropDown(true);
validation.setIgnoreBlank(true);

validation.setFormula1(formula1);
validation.setErrorTitle(“errorTitle”);
validation.setErrorMessage(“errorMessage”);

<span style=“background-color: rgb(255, 255, 255); font-family: “Courier New”;”>wb.save(<span style=“font-family: “Courier New”; color: rgb(0, 128, 0); font-weight: bold;”>“asposeValidation.xls”<span style=“background-color: rgb(255, 255, 255); font-family: “Courier New”;”>);

Best regards
Claes
2

File samples: xlsx works fine, xls version has custom validation without formula.

3

Hi,


Thanks for using Aspose.Cells.

We were able to observe this issue and logged it in our database for a fix.

Is it so that Excel 2003 did not support custom data validations?

Excel 2003 supports it. Please see this screenshot.


This issue has been logged as

  • CELLSJAVA-42177 - Formula in custom validation gets missing when workbook is saved in xls format

4
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSJAVA-42177 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

For a moment (before we provide you a fix), you can change the formula as the following:

String formula1 = "=IF(ISERROR(A1),TRUE,AND(LEN(A1)<10,LEN(A1)>2))";

5

Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix: Aspose.Cells for Java (Latest Version) and let us know your feedback.

6

Hi,


Both the workaround and fix works perfect.

Thanks!
Claes
7

Hi,


Thanks for your feedback.

Good to know that your issue is sorted out by the new fix/version, we have closed it now. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.

Thank you.
8

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.