Free Support Forum - aspose.com

Using List Validation and get the id of the selected drop down

Hi,


I have a requirement where in I will have a drop down list ( used list validation i.e I have used range and have set the range in to validation formula
validation.setFormula1("=MyRange"):wink:

But when I read thiscell instead of the range value I have to get the id. Basically I 
want to associate each value in the drop down to a key and value. For instance I have
4 drop downs, red, green, blue and orange. When I select red I have to get value 1 on 
green 56 on blue 100 something similar. 


Is this possible in current aspose implementation. 

Early reply is much appreciated
Thanks,

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your sample excel file with your dropdown list so that we could investigate this issue further and let you know the sample code to achieve your requirements.

Thanks for your cooperation.

Hi,

I am attaching the sample file.

Sheet 2 has colors and the numbers. I have named the range Blue and red for 1,2,3,4 and 11,22,33,44 respectively.

In Sheet 1 I have drop downs at A1:A10, so on sleected of Blue I need 1,2,3,4 drop downs in B1.
In case If I select red I need 11,22,33,44, drop downs.

Please revert back at the earliest.

Hi,

Thanks for using Aspose.Cells.

I have written the following sample code which creates your sample excel file from scratch using Aspose.Cells APIs. I have also attached the output xls file generated by the code for your reference.

Now if you click on cell A1, it will show you a dropdown showing “Blue, Red, Green, Yellow” and when you will click any of the item, it will update the cell B1 with its value like “1, 11, 111, 1111”.

Java
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
Worksheet worksheet2 = workbook.getWorksheets().add(“Sheet2”);

int idx = workbook.getWorksheets().getNames().add(“MyRange”);
Name nm = workbook.getWorksheets().getNames().get(idx);
nm.setRefersTo("=Sheet2!$B$1:$B$10");

idx = workbook.getWorksheets().getNames().add(“Blue”);
nm = workbook.getWorksheets().getNames().get(idx);
nm.setRefersTo("=Sheet2!$C$1:$C$10");

idx = workbook.getWorksheets().getNames().add(“Red”);
nm = workbook.getWorksheets().getNames().get(idx);
nm.setRefersTo("=Sheet2!$D$1:$D$10");

idx = workbook.getWorksheets().getNames().add(“Green”);
nm = workbook.getWorksheets().getNames().get(idx);
nm.setRefersTo("=Sheet2!$E$1:$E$10");

idx = workbook.getWorksheets().getNames().add(“Yellow”);
nm = workbook.getWorksheets().getNames().get(idx);
nm.setRefersTo("=Sheet2!$F$1:$F$10");

worksheet2.getCells().get(“B1”).putValue(“Blue”);
worksheet2.getCells().get(“B2”).putValue(“Red”);
worksheet2.getCells().get(“B3”).putValue(“Green”);
worksheet2.getCells().get(“B4”).putValue(“Yellow”);

worksheet2.getCells().get(“C1”).putValue(1);
worksheet2.getCells().get(“C2”).putValue(2);
worksheet2.getCells().get(“C3”).putValue(3);
worksheet2.getCells().get(“C4”).putValue(4);

worksheet2.getCells().get(“D1”).putValue(11);
worksheet2.getCells().get(“D2”).putValue(22);
worksheet2.getCells().get(“D3”).putValue(33);
worksheet2.getCells().get(“D4”).putValue(44);

worksheet2.getCells().get(“E1”).putValue(111);
worksheet2.getCells().get(“E2”).putValue(222);
worksheet2.getCells().get(“E3”).putValue(333);
worksheet2.getCells().get(“E4”).putValue(444);

worksheet2.getCells().get(“F1”).putValue(1111);
worksheet2.getCells().get(“F2”).putValue(2222);
worksheet2.getCells().get(“F3”).putValue(3333);
worksheet2.getCells().get(“F4”).putValue(4444);

CellArea area = CellArea.createCellArea(“A1”, “A5”);
idx = worksheet.getValidations().add(area);
Validation v = worksheet.getValidations().get(idx);
v.setErrorMessage(“Please select a color from the list”);
v.setErrorTitle(“Error”);
v.setFormula1("=MyRange");
v.setOperator(OperatorType.BETWEEN);
v.setType(ValidationType.LIST);

worksheet.getCells().get(“B1”).setFormula("=INDIRECT(A1)");
worksheet.getCells().get(“B2”).setFormula("=INDIRECT(A2)");
worksheet.getCells().get(“B3”).setFormula("=INDIRECT(A3)");
worksheet.getCells().get(“B4”).setFormula("=INDIRECT(A4)");
worksheet.getCells().get(“B5”).setFormula("=INDIRECT(A5)");

workbook.save(“output.xls”);