Issues with drop down validations for xls file

Aspose.Cells Product Family
1

Hi,


We have noticed that in recent releases of Aspose Cells there are issues with drop down validations in the xls format (no issue with xlsx)

I will work on creating a code example where you can reproduce this but I thought I would log it directly.

From version 8.5.2 we have issues creating files with multiple areas using drop down validations.
After a quick search in the forums I would suggest this might be caused by the fix for CELLSJAVA 41427 (Too many validation cells corrupts file).

In our case we are only creating 6 different cellregions for drop down validations, but only one of them works in the resulting file (the others have silently disappeared). If we switch to using xlsx it works fine. I will get back asap with sample code if you need it.

BTW, I noticed that the method ValidationColection.add() is deprecated but cannot find notes on what to use instead. Should it be the add method with a cellArea as argument? (FYI we have tested the issue with both approaches)

Best regards
Claes
2
Hi Claes,

spygordon:
We have noticed that in recent releases of Aspose Cells there are issues with drop down validations in the xls format (no issue with xlsx)

I will work on creating a code example where you can reproduce this but I thought I would log it directly.

From version 8.5.2 we have issues creating files with multiple areas using drop down validations.
After a quick search in the forums I would suggest this might be caused by the fix for CELLSJAVA 41427 (Too many validation cells corrupts file).

In our case we are only creating 6 different cellregions for drop down validations, but only one of them works in the resulting file (the others have silently disappeared). If we switch to using xlsx it works fine. I will get back asap with sample code if you need it.

It would be of great help in isolating the problem cause and consequently provide a fix if you can share an executable sample application along with the sample spreadsheet. We will be looking forward to it.

spygordon:
BTW, I noticed that the method ValidationColection.add() is deprecated but cannot find notes on what to use instead. Should it be the add method with a cellArea as argument? (FYI we have tested the issue with both approaches)

Yes, the ValidationColection.add method that does not accept any parameter has been depreciated. You have to use the new overload versions of the aforesaid method that could either accept an instance of CellArea or Validation. I will also check and provide the release reference where the mentioned method was marked obsolete.
3

Hi again,


Here’s some sample code (the validation for options2 does not come out in xls, but it does in xlsx):

Workbook w = new Workbook();
Worksheet sheet = w.getWorksheets().get(0);

String[][] options = new String[][]{
        {“a”, “b”, “c”},
        {“d”, “e”, “f”}
};

//write the options
Cells cells = sheet.getCells();
int row = 0;
for (;row < 3; row++) {
    for (int col = 0; col < 2; col++) {
        cells.get(row, col).putValue(options[col][row]);
    }
}
//create named ranges for the options
cells.createRange(“A1”, “A3”).setName(“options1”);
cells.createRange(“B1”, “B3”).setName(“options2”);

ValidationCollection validations = sheet.getValidations();

CellArea area = new CellArea();//area for option list 1
area.StartColumn = 0;
area.StartRow = row+1;
area.EndColumn = 0;
area.EndRow = row+10;

//create the 1st drop down validation
Validation list = validations.get(validations.add(area));
list.setType(ValidationType.LIST);
list.setInCellDropDown(true);
list.setIgnoreBlank(true);
list.setFormula1("=options1");

area = new CellArea();  //area for option list 2
area.StartColumn = 1;
area.StartRow = row+1;
area.EndColumn = 1;
area.EndRow = row+10;
                         
//create the 2nd drop down validation
list = validations.get(validations.add(area));
list.setType(ValidationType.LIST);
list.setInCellDropDown(true);
list.setIgnoreBlank(true);
list.setFormula1("=options2");

w.save(“aspose.xls”); //does not work, but w.save(“aspose.xlsx”) works
4

Hi Claes,

Thank you for sharing the code.

We have evaluated the presented scenario while using the latest revision of Aspose.Cells for Java 8.6.0, and we are able to notice that said problem. In resultant XLS, the list validation is added for the region A5:A14 whereas in the resultant XLSX both regions (A5:A14 & B5:B14) has the list validation added. We have logged this incident in our bug tracking system as CELLSJAVA-41488 for further investigation & correction. Please allow us some time for proper analysis and get back to you with updates in this regard.

5

Hi,

Thanks for using Aspose.Cells.

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

6

Hi,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.6.0.2 and let us know your feedback.

7

Hi,


It works fine now.

Thank you
Claes
8

Hi,


Thanks for your feedback.

Good to know that it figures out your issue now. Feel free to write back if you have further comments or questions, we will be happy to assist you soon.

Thank you.
9

The issues you have found earlier (filed as CELLSJAVA-41488) have been fixed in

Aspose.Cells for Java (Download | Maven)

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