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?
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?
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.
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().getSheet(0);
Cells cells = sheet.getCells();
Validations validations = sheet.getValidations();
int index = validations.add();
Validation validation = validations.get(index);
validation.setErrorMessage(“Please Enter the correct value from the list”);
CellArea area = new CellArea(1,0,1,0);
Worksheet formulasheet = workbook.getWorksheets().addSheet();
//Select/enter a value
//Now Calculate the formulas.
int val = formulasheet.getCells().getCell(“A1”).getIntValue();
Hopefully, it will help you to accomplish you task.
Also please check the document on how to apply different validation types: