List Data Validation bugs

Aspose.Cells Product Family
1

Hi there,

Version: 2.5.4.12 (aspose-cells-2.5.4.12.jar)

I am having problem in list data validation. here are the snipped of the code:

info_sheet = workbook.getWorksheets().getSheet(INFO);
defaults_sheet = workbook.worksheets().getSheet(DEFAULTS);

yes_no_range = info_sheet.cells.createNamedRange(“YesNoRange”, 13, 1, 14, 1);
yes_no_range.getCell(0, 0).setValue(“Y”);
yes_no_range.getCell(1, 0).setValue(“N”);

defaults_sheet_validations = defaults_sheet.validations();
yes_no_validation = defaults_sheet_validations.get(defaults_sheet_validations.add());
yes_no_validation.setType(ValidationType.LIST);
yes_no_validation.setInCellDropDown = (true);
yes_no_validation.setFormula1("=YesNoRange");
yes_no_validation.setShowError(true);
yes_no_validation.setAlertType(ValidationAlertType::STOP);
yes_no_validation.setErrorTitle(“Error”);
yes_no_validation.setErrorMessage(“Please enter Y for Yes or N for No”);

area = CellArea.new(46, 8, 47, 11)
yes_no_validation.addCellArea(area)

from the snipped code above, i used excel to check and it shows the “=(((#REF!)))” in DataValidation :: Settings :: Source.

However, the workaround is by:



info_sheet = workbook.getWorksheets().getSheet(INFO);
defaults_sheet = workbook.worksheets().getSheet(DEFAULTS);



yes_no_range = info_sheet.cells.createNamedRange(“YesNoRange”, 13, 1, 14, 1);

yes_no_range.getCell(0, 0).setValue(“Y”);

yes_no_range.getCell(1, 0).setValue(“N”);
workbook.save("/tmp/mytest.xls"); // save to temp 1st and reopen

workbook = new Workbook();
workbook.open("/tmp/mytest.xls");
defaults_sheet = test_workbook.worksheets.sheet(DEFAULTS);


defaults_sheet_validations = defaults_sheet.validations();

yes_no_validation = defaults_sheet_validations.get(defaults_sheet_validations.add());

yes_no_validation.setType(ValidationType.LIST);

yes_no_validation.setInCellDropDown = (true);

yes_no_validation.setFormula1("=YesNoRange");

yes_no_validation.setShowError(true);

yes_no_validation.setAlertType(ValidationAlertType::STOP);

yes_no_validation.setErrorTitle(“Error”);

yes_no_validation.setErrorMessage(“Please enter Y for Yes or N for No”);



area = CellArea.new(46, 8, 47, 11)

yes_no_validation.addCellArea(area)




It seems like the NamedRange is not properly available in the Object Model until after a save/load cycle. Please help.

2

Hi,

Thanks for your reporting.

I have tested your code with the latest version Aspose.Cells for Java v2.5.4.13 and there seems to be a problem. I have logged this issue in our database. We will further investigate and fix it and update you asap.

This issue has been logged as CELLSJAVA-29221.

3

Hi,

Development team could reproduce the issue. Following is the executable code derived from your code. It is working fine.

You should download the latest version: Aspose.Cells for Java v2.5.4.13

Java



Workbook wb = new Workbook();


Worksheets wss = wb.getWorksheets();


Worksheet sheet;


sheet = wss.getSheet(0);


NamedRange yes_no_range = sheet.getCells().createNamedRange(“YesNoRange”, 13, 1, 14, 1);


yes_no_range.getCell(0, 0).setValue(“Y”);


yes_no_range.getCell(1, 0).setValue(“N”);


Validations defaults_sheet_validations = sheet.getValidations();


Validation yes_no_validation = defaults_sheet_validations.get(defaults_sheet_validations.add());


yes_no_validation.setType(ValidationType.LIST);


yes_no_validation.setInCellDropDown(true);


yes_no_validation.setFormula1(“=YesNoRange”);


yes_no_validation.setShowError(true);


yes_no_validation.setAlertType(ValidationAlertType.STOP);


yes_no_validation.setErrorTitle(“Error”);


yes_no_validation.setErrorMessage(“Please enter Y for Yes or N for No”);


CellArea area = new CellArea(46, 8, 47, 11);


yes_no_validation.addCellArea(area);


wb.save(“res.xls”, FileFormatType.EXCEL97TO2003);



Also attached is the resultant excel file. I think it works fine for the validation in ms excel.

So please provide your test code to reproduce the issue.
4

Hi,

For your help and further elaboration. Please see the screenshot below.

Screenshot:

5

Cool and I’ll try it soon. At the moment, I have changed the approach of the name range. Previously, the name range is create dynamically, now I have changed to statically which defined in excel itself. since it is much less code to do. But thanks for the fix and i’ll try it if i do need to make it dynamically in the future.