Sequential values from drop down list

Aspose.Cells Product Family
1

Hi all,

I recently came across an excel sheet which have a drop down list & based on selected value inside drop down list some formula have designed, those formula are in a difference sheet in same excel file.

So, what my question is, do this API have any functionality which gets values of all the drop down items & based on that value can get result of formula?

2

Hi Utsav,


Can you please share your spreadsheet you are talking about? Once we are sure of your requirement, we will provide you every assistance you may require.
Thank you for considering Aspose.
3

Hi Babar,

Here is the excel template file attached with this mail.

What I need to do is I have a list in first spread sheet, which I need to be changed using program, Aspose cell API, on selecting value from drop down list value inside second spread sheet will be calculated & changed accordingly.

So, the final result for me should be the number selected in drop down list & calculated value in second spread sheet. And both the result should be inside a new excel file(.xlsx) file.

Is that possible in Aspose cell API for Java?

Thanks,
Utsav.

4

Hi Utsav,


Thank you for providing us your sample file. We are now very clear of your requirement. For this feature we have logged a Ticket with ID CELLSJAVA-27233 in our tracking system.
We will keep you posted with updates on this.
5

Hi,

After closely looking into your template Excel file, I noticed there is a list data validation applied on A2 cell rather than a combo box.

Following is a sample code, how to create you desired template file, it has a formula in the second sheet that is based on the selected value from the data validation list in first sheet. When a user will select a value from the data validation list the relevant formula would be calculated accordingly. At run time, you may also select or enter a value from the data validation list and the can call Workbook.calculateFormula() method that would update the formula result in the second sheet.
I have also attached the generated file here.

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().getSheet(0);
Cells cells = sheet.getCells();
cells.getCell(“P2”).setValue(“Numbers:”);
cells.getCell(“P3”).setValue(2);
cells.getCell(“P4”).setValue(3);
cells.getCell(“P5”).setValue(4);
cells.getCell(“P6”).setValue(5);
cells.getCell(“P7”).setValue(6);
cells.getCell(“P8”).setValue(7);
cells.getCell(“P9”).setValue(8);
cells.getCell(“P10”).setValue(9);

Validations validations = sheet.getValidations();
int index = validations.add();
Validation validation = validations.get(index);
validation.setType(ValidationType.LIST);
validation.setInCellDropDown(true);
validation.setFormula1(“=Sheet1!P3:P10”);
validation.setShowError(true);
validation.setAlertType(ValidationAlertType.STOP);
validation.setErrorTitle(“Error”);
validation.setErrorMessage(“Please Enter the correct value from the list”);
CellArea area = new CellArea(1,0,1,0);
validation.addCellArea(area);



Worksheet formulasheet = workbook.getWorksheets().addSheet();
formulasheet.getCells().getCell(“A1”).setFormula(“=sheet1!A2^2”);

//Select/enter a value
cells.getCell(“A2”).setValue(5);
//Now Calculate the formulas.
workbook.calculateFormula();

int val = formulasheet.getCells().getCell(“A1”).getIntValue();
System.out.println(val);


workbook.save(“d:\files\outlistvalidation.xlsx”);


Hopefully, it will help you to accomplish you task.

Also please check the document on how to apply different validation types:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/data-filtering-and-validation.html

Thank you.