Validation.setFormula1(" ") doesn’t set a dropdown list.
Sample code is as follows :
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
ValidationCollection validations = worksheet.getValidations();
CellArea area = new CellArea();
area.StartRow = 2;
area.EndRow = 3;
area.StartColumn = 0;
area.EndColumn = 1;
int index = validations.add(area);
Validation validation = validations.get(index);
validation.setType(ValidationType.LIST);
validation.setIgnoreBlank(true);
validation.setInCellDropDown(true);
validation.setShowError(true);
validation.setErrorMessage("You must select one of the value available in the drop-down list box");
validation.setShowInput(true);
validation.setInputMessage("Select");
String formula = "Value1,Value2,value3,Value4";
validation.setFormula1(formula);
validation.addArea(area);
workbook.save("D:\\DVModels\\download\\Sample.xlsx");
Note : The method still works when the file is being saved in ‘xls’ format but not using ‘xlsx’ format.Please find the samples generated.
Sample.zip (7.3 KB)
@dvector,
Thanks for your query.
We have tried to reproduce this issue using latest version Aspose.Cells for Java 18.12.9 but could not observe issue in output XLSX file as validation from dropdown list is working. You may please give a try to the following version and share your feedback.
Aspose.Cells for Java_v18.12.9.zip (6.4 MB)
Thanks for your reply. The version mentioned could resolve the above issue. But that couldn’t solve the exact problem we are trying to solve.
Sample code for the issue is as follows :
Workbook workbook = new Workbook("D:\\DVModels\\PRIMARY\\Students.xlsx");
ArrayList<String> dropDownData = null;
Worksheet worksheet = workbook.getWorksheets().get(0);
ValidationCollection validations = worksheet.getValidations();
Validation validation = validations.get(1);
System.out.println("validation " + (new ObjectMapper()).writeValueAsString(validation));
Object obj = validation.getListValue(7,2);
System.out.println(obj.getClass().getName());
System.out.println(validation.getValue1().getClass().getName());
if (obj.getClass().getName().equals("com.aspose.cells.ReferredArea")) {
dropDownData = getDropdownDataFromReferredArea((ReferredArea) obj,
workbook.getWorksheets().get(0).getWorkbook());
} else {
dropDownData = new ArrayList<>();
Object[] objArray = (Object[]) obj;
for (Object o : objArray) {
String val = o.toString().trim();
dropDownData.add(val);
}
}
for (String object : dropDownData) {
System.out.println("VALUE " + object);
}
Workbook wb2 = new Workbook();
Worksheet worksheet2 = wb2.getWorksheets().get(0);
ValidationCollection validations2 = worksheet2.getValidations();
CellArea area2 = new CellArea();
area2.StartRow = 6;
area2.EndRow = 6;
area2.StartColumn = 4;
area2.EndColumn = 4;
int index2 = validations2.add(area2);
Validation validation22 = validations2.get(index2);
validation22.setType(ValidationType.LIST);
validation22.setIgnoreBlank(true);
validation22.setInCellDropDown(true);
validation22.setShowError(true);
validation22.setErrorMessage("You must select one of the value available in the drop-down list box");
validation22.setShowInput(true);
validation.setInputMessage("Select");
validation22.addArea(area2);
StringBuilder formulaB = new StringBuilder();
for (String string : dropDownData) {
if (!string.equals("")) {
formulaB.append(string).append(",");
}
}
String formula = formulaB.toString();
formula =formula.substring(0, formula.length()-1);
validation22.setFormula1(formula);
System.out.println(formula.charAt(0));
System.out.println("val2 :: "+ (new ObjectMapper()).writeValueAsString(validation22));
wb2.save("D:\\DVModels\\download\\Sample2.xlsx");
The output file thus generated doesn’t have validation with a drop down as expected.
Please find the Sample input , output file and the error message displayed while opening the output file.
Sample.zip (29.6 KB)
@dvector,
Thank you for providing the sample code and sample files. I am afraid that it contains some function calls which are not resolved like getDropdownDataFromReferredArea and template file Students.xlsx is also missing. Please share a complete code where dropDownData list is hardcoded and all elements are filled along with the missing function and template file. Once we get a runnable code and template file, we will reproduce the issue here and provide our feedback.
Thanks for your reply. Sorry for missing the method getDropdownDataFromReferredArea in the above code.
The corrected sample code is as follows :
public class Sample {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook("D:\\DVModels\\PRIMARY\\Students.xlsx");
ArrayList<String> dropDownData = null;
Worksheet worksheet = workbook.getWorksheets().get(0);
ValidationCollection validations = worksheet.getValidations();
Validation validation = validations.get(1);
System.out.println("validation " + (new ObjectMapper()).writeValueAsString(validation));
Object obj = validation.getListValue(7, 2);
System.out.println(obj.getClass().getName());
System.out.println(validation.getValue1().getClass().getName());
if (obj.getClass().getName().equals("com.aspose.cells.ReferredArea")) {
dropDownData = getDropdownDataFromReferredArea((ReferredArea) obj,
workbook.getWorksheets().get(0).getWorkbook());
} else {
dropDownData = new ArrayList<>();
Object[] objArray = (Object[]) obj;
for (Object o : objArray) {
String val = o.toString().trim();
dropDownData.add(val);
}
}
for (String object : dropDownData) {
System.out.println("VALUE " + object);
}
Workbook wb2 = new Workbook();
Worksheet worksheet2 = wb2.getWorksheets().get(0);
ValidationCollection validations2 = worksheet2.getValidations();
CellArea area2 = new CellArea();
area2.StartRow = 6;
area2.EndRow = 6;
area2.StartColumn = 4;
area2.EndColumn = 4;
int index2 = validations2.add(area2);
Validation validation22 = validations2.get(index2);
validation22.setType(ValidationType.LIST);
validation22.setIgnoreBlank(true);
validation22.setInCellDropDown(true);
validation22.setShowError(true);
validation22.setErrorMessage("You must select one of the value available in the drop-down list box");
validation22.setShowInput(true);
validation.setInputMessage("Select");
validation22.addArea(area2);
StringBuilder formulaB = new StringBuilder();
for (String string : dropDownData) {
if (!string.equals("")) {
formulaB.append(string).append(",");
}
}
String formula = formulaB.toString();
formula = formula.substring(0, formula.length() - 1);
validation22.setFormula1(formula);
System.out.println(formula.charAt(0));
System.out.println("val2 :: " + (new ObjectMapper()).writeValueAsString(validation22));
wb2.save("D:\\DVModels\\download\\Sample2.xlsx");
}
private static ArrayList<String> getDropdownDataFromReferredArea(ReferredArea area, Workbook book)
throws Exception {
ArrayList<String> dropDownData = new ArrayList<>();
String sheetName = area.getSheetName();
Worksheet sheet = book.getWorksheets().get(sheetName);
Cells cells = sheet.getCells();
int startRow = area.getStartRow();
int endRow = area.getEndRow();
int startColumn = area.getStartColumn();
int endColumn = area.getEndColumn();
for (int row = startRow; row <= endRow; row++) {
for (int col = startColumn; col <= endColumn; col++) {
Cell cell = cells.get(row, col);
String value;
if (cell.getType() == CellValueType.IS_DATE_TIME) {
value = String.valueOf(cell.getDoubleValue());
} else {
value = cell.getDisplayStringValue();
}
dropDownData.add(value);
}
}
return dropDownData;
}
}
The zip file previously attached contains the input and the output generated. Incase, it is missing, please refer to the below zip file that conatins the required input.
Students.zip (29.6 KB)
@dvector,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42810 - Issue setting values to Validation
Hi,
The actual issue is identified. There is a limitation of 255 on the character length of the formula.As the value exceeds the limit, excel is discarding the validation as a whole.
@dvector,
Thank you for the feedback.
Good to know that your issue is identified already. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.