I’m developing a method to create an XLSX file that contains data validation on entire columns.
In particular, I’m trying to add a dropdown to the cells to restrict user choice to a list of values.
This is my code:
cells.get("B1").setValue("Source");
Style cellB1 = cells.get("B1").getStyle();
cellB1.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.DOUBLE, Color.getBlack());
cells.get("B1").setStyle(cellB1);
CellArea area = CellArea.createCellArea("B2", "B1048576");
List<Source> sources = sourceService.findAll();
Range range = sheet.getCells().createRange(1, 30, sources.size(), 4);
range.setName("SourceRange");
for(int i = 0; i< sources.size(); i++) {
range.get(i, 0).setValue(sources.get(i).getName());
}
ValidationCollection validations = sheet.getValidations();
int index = validations.add(area);
Validation validation = validations.get(index);
validation.setType(ValidationType.LIST);
validation.setOperator(OperatorType.BETWEEN);
validation.setInCellDropDown(true);
validation.setFormula1("=SourceRange");
validation.setShowError(true);
validation.setAlertStyle(ValidationAlertType.WARNING);
validation.setErrorTitle("An invalid value was entered");
validation.setErrorMessage("Select a value from the list");
I have several issues:
Clicking on the dropdown values, the cell remains empty or is filled by a wrong value (not the one that has been clicked)
Writing a custom value, no error message appear.
Could I delete the values in the cell used to create the range? I tried to, but the dropdown is filled only by empty values.
Could you please add/implement your desired list data validation to a column in MS Excel manually and save the file to provide us. We will check and help you on how to do it via Aspose.Cells API.
In the attachment, is the example file. The DEVELOPED column is the one created through the code I added in my previous message, and the RIGHT column is how I would like it to be.
The DEVELOPED column is not working well (clicking on the dropdown values, sometimes the cell is filled, sometimes it remains empty)
I checked your scenario/case using your sample file. Since your source range involves more than 1 column (i.e., “=Sheet1!$AF$2:$AI$11”), you cannot create list data validation properly, so you are getting this issue. This is MS Excel’s behavior that your named range should involve single column or single row. Even, you may try to accomplish the task (as per your code segment) in MS Excel manually and you will notice, it won’t work and you cannot create list data validation based on such a named range (involving multiple columns). In short, either you should have a data range with single column (e.g., “=Sheet1!$AF$2:$AF$11”) or you should directly specify the cells range ("=$AF$2:$AI$11") instead of named range. See the sample code segments of both ways, you may choose any one. I have tested both methods are working ok:
1)
.....
CellArea area = CellArea.createCellArea("B2", "B1048576");
Range range = sheet.getCells().createRange(1, 31,10,1);//=Sheet1!$AF$2:$AF$11
range.setName("SourceRange");
ValidationCollection validations = sheet.getValidations();
int index = validations.add(area);
Validation validation = validations.get(index);
validation.setType(ValidationType.LIST);
validation.setOperator(OperatorType.BETWEEN);
validation.setInCellDropDown(true);
validation.setFormula1("=SourceRange");
......
2)
.....
CellArea area = CellArea.createCellArea("B2", "B1048576");
ValidationCollection validations = sheet.getValidations();
int index = validations.add(area);
Validation validation = validations.get(index);
validation.setType(ValidationType.LIST);
validation.setOperator(OperatorType.BETWEEN);
validation.setInCellDropDown(true);
validation.setFormula1("=$AF$2:$AF$11");
......