List Data Validation bugs

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.

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.

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.

Hi,

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

Screenshot:

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.