Validation class not emulating Excel

Aspose.Cells Product Family
1

I am using this file valid.xlsx.zip (20.6 KB)
;

The validation in cell A1 has 3 values John, Jane, Joe

But when I am using this code:

Worksheet sheet = book.getWorksheets().get("Sheet1");
    Cells cells  = sheet.getCells();
    Cell a1 = cells.get("a1");

    if(a1.getValidationValue()) {
        System.out.println("Value "+a1.getDisplayStringValue()+" is valid");
    } else {
        System.out.println("Value "+a1.getDisplayStringValue()+" is NOT valid");
    }

    a1.setValue("Jane");

    if(a1.getValidationValue()) {
        System.out.println("Value "+a1.getDisplayStringValue()+" is valid");
    } else {
        System.out.println("Value "+a1.getDisplayStringValue()+" is NOT valid");
    }

    a1.setValue("Joe");

    if(a1.getValidationValue()) {
        System.out.println("Value "+a1.getDisplayStringValue()+" is valid");
    } else {
        System.out.println("Value "+a1.getDisplayStringValue()+" is NOT valid");
    }

The output I get is

Value John is valid
Value Jane is NOT valid
Value Joe is NOT valid

This output is completely unexpected. Can you please look into this?

2

@dvector,

Thanks for the template file and sample code.

After an initial test, I am able to observe the issue as you mentioned by using your sample code with your template file. I found that Cell.getValidationValue returns wrong value for the inserted value. I have logged a ticket with an id “CELLSJAVA-42822” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

3

Thanks. Can you tell me if there is any temporary workaround for this?

4

@dvector,

Please spare us a little time to evaluate your issue thoroughly. We hope we can figure it out in the next 3- 5 days or so. If it takes more time, we might provide a workaround for it.

5

@dvector,

After further investigation and evaluation of your issue, we found in your template file, the specified values in the list are “John”," Jane"," Joe". Please notice the leading spaces before second and third value. So, currently, as a workaround you may input the cell value as " Jane"," Joe" instead of “Jane”,“Joe” when evaluating the validations. We will investigate it further, if we found MS Excel always ignores the leading spaces and tailing spaces while checking the constraints, we will do in the same way and provide a fix for it soon.

6

@dvector,

Please try our latest version/fix: Aspose.Cells for Java v19.1.5 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells for Java_v19.1.5.zip (6.4 MB)

7

Thanks. Can you please point me to the changelog for version 19? I am using v18.12.9

8

@dvector,

We are scheduled to release our next official release, i.e, Aspose.Cells for Java v19.2 in the third/fourth week of February 2019, it will include all the fixes including yours. So, you may check the release notes of the published version to see change log and other details at that time.

Once we publish the next official release (in Downloads section and maven repos.), you will be notified in this thread too.

9

The issues you have found earlier (filed as CELLSJAVA-42822) have been fixed in Aspose.Cells for Java 19.2. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi