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)