import com.grapecity.documents.excel.*;

public class SmartDependentList {

    public static void main(String[] args) throws Exception {
        //Step 1 - Workbook Initialization
        Workbook workbook = new Workbook();
        workbook.open("E:\\download\\smartdependentlist\\CustomerOrderHistory.xlsx");

        //Step 2 - Get the Worksheet
        IWorksheet worksheet;
        worksheet = workbook.getWorksheets().get(0);

        //Step 3 - Get the unique list of customer names (for master dropdown)
        IRange rngUniqueCustomerNames;
        rngUniqueCustomerNames = worksheet.getRange("T3"); //dummy range to get unique list of customer names
        rngUniqueCustomerNames.setFormula2("=UNIQUE($B$2:$B$2156)");

        //Step 4 - Create the master dropdown
        IValidation customerNameList = worksheet.getRange("L3").getValidation();
        customerNameList.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$T$3#", null);

        //Step 5 - Fetch the list of unique OrderIDs(for dependent dropdown)
        workbook.getNames().add("CustomerName", "=$L$3");
        workbook.getNames().add("Unique_Cus_Order_combo", "=UNIQUE(data!$A$2:$B$2156)");

        IRange rngUniqueOrderIds;
        rngUniqueOrderIds = worksheet.getRange("V2"); //dummy rnage to get unique list of customer names
        rngUniqueOrderIds.setFormula2("=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1)");

        //Step 6 - Populate the dependent dropdown
        IValidation orderIdList = worksheet.getRange("L6").getValidation();
        orderIdList.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$v$2#", null);

        //Step 7 - Set the default values to dropdown and save the workbook
        worksheet.getRange("L3").setValue("Paul Henriot");
        worksheet.getRange("L6").setValue(10248);

        workbook.save("E:\\download\\smartdependentlist\\CustomerOrderHistoryReport.xlsx");

    }
}