fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositTransactionWorkbookPopulator.java [64:181]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        setLayout(savingsTransactionSheet);
        officeSheetPopulator.populate(workbook, dateFormat);
        clientSheetPopulator.populate(workbook, dateFormat);
        extrasSheetPopulator.populate(workbook, dateFormat);
        populateSavingsTable(savingsTransactionSheet, dateFormat);
        setRules(savingsTransactionSheet, dateFormat);
        setDefaults(savingsTransactionSheet);
    }

    private void setDefaults(Sheet worksheet) {
        for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
            Row row = worksheet.getRow(rowNo);
            if (row == null) {
                row = worksheet.createRow(rowNo);
            }
            writeFormula(TransactionConstants.PRODUCT_COL, row,
                    "IF(ISERROR(VLOOKUP($C" + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",2,FALSE)),\"\",VLOOKUP($C"
                            + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",2,FALSE))");
            writeFormula(TransactionConstants.OPENING_BALANCE_COL, row,
                    "IF(ISERROR(VLOOKUP($C" + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",3,FALSE)),\"\",VLOOKUP($C"
                            + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",3,FALSE))");
        }
    }

    private void setRules(Sheet worksheet, String dateFormat) {
        CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.OFFICE_NAME_COL, TransactionConstants.OFFICE_NAME_COL);
        CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.CLIENT_NAME_COL, TransactionConstants.CLIENT_NAME_COL);
        CellRangeAddressList accountNumberRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.SAVINGS_ACCOUNT_NO_COL, TransactionConstants.SAVINGS_ACCOUNT_NO_COL);
        CellRangeAddressList transactionTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.TRANSACTION_TYPE_COL, TransactionConstants.TRANSACTION_TYPE_COL);
        CellRangeAddressList paymentTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.PAYMENT_TYPE_COL, TransactionConstants.PAYMENT_TYPE_COL);
        CellRangeAddressList transactionDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.TRANSACTION_DATE_COL, TransactionConstants.TRANSACTION_DATE_COL);

        DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);

        setNames(worksheet);

        DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office");
        DataValidationConstraint clientNameConstraint = validationHelper
                .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))");
        DataValidationConstraint accountNumberConstraint = validationHelper.createFormulaListConstraint(
                "INDIRECT(CONCATENATE(\"Account_\",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B1,\" \",\"_\"),\"(\",\"_\"),\")\",\"_\")))");
        DataValidationConstraint transactionTypeConstraint = validationHelper
                .createExplicitListConstraint(new String[] { "Withdrawal", "Deposit" });
        DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes");
        DataValidationConstraint transactionDateConstraint = validationHelper.createDateConstraint(
                DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($C1,$Q$2:$T$" + (savingsAccounts.size() + 1) + ",4,FALSE)",
                "=TODAY()", dateFormat);

        DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange);
        DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange);
        DataValidation accountNumberValidation = validationHelper.createValidation(accountNumberConstraint, accountNumberRange);
        DataValidation transactionTypeValidation = validationHelper.createValidation(transactionTypeConstraint, transactionTypeRange);
        DataValidation paymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, paymentTypeRange);
        DataValidation transactionDateValidation = validationHelper.createValidation(transactionDateConstraint, transactionDateRange);

        worksheet.addValidationData(officeValidation);
        worksheet.addValidationData(clientValidation);
        worksheet.addValidationData(accountNumberValidation);
        worksheet.addValidationData(transactionTypeValidation);
        worksheet.addValidationData(paymentTypeValidation);
        worksheet.addValidationData(transactionDateValidation);
    }

    private void setNames(Sheet worksheet) {
        Workbook savingsTransactionWorkbook = worksheet.getWorkbook();
        List<String> officeNames = officeSheetPopulator.getOfficeNames();

        // Office Names
        Name officeGroup = savingsTransactionWorkbook.createName();
        officeGroup.setNameName("Office");
        officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME + "!$B$2:$B$" + (officeNames.size() + 1));

        // Clients Named after Offices
        for (Integer i = 0; i < officeNames.size(); i++) {
            Integer[] officeNameToBeginEndIndexesOfClients = clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
            Name name = savingsTransactionWorkbook.createName();
            if (officeNameToBeginEndIndexesOfClients != null) {
                setSanitized(name, "Client_" + officeNames.get(i));
                name.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_SHEET_NAME + "!$B$" + officeNameToBeginEndIndexesOfClients[0]
                        + ":$B$" + officeNameToBeginEndIndexesOfClients[1]);
            }
        }

        // Counting clients with active savings and starting and end addresses
        // of cells for naming
        HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<>();
        ArrayList<String> clientsWithActiveSavings = new ArrayList<>();
        ArrayList<Long> clientIdsWithActiveSavings = new ArrayList<>();
        int startIndex = 1;
        int endIndex = 1;
        String clientName = "";
        Long clientId = null;
        for (int i = 0; i < savingsAccounts.size(); i++) {
            if (!clientName.equals(savingsAccounts.get(i).getClientName())) {
                endIndex = i + 1;
                clientNameToBeginEndIndexes.put(clientName, new Integer[] { startIndex, endIndex });
                startIndex = i + 2;
                clientName = savingsAccounts.get(i).getClientName();
                clientId = savingsAccounts.get(i).getClientId();
                clientsWithActiveSavings.add(clientName);
                clientIdsWithActiveSavings.add(clientId);
            }
            if (i == savingsAccounts.size() - 1) {
                endIndex = i + 2;
                clientNameToBeginEndIndexes.put(clientName, new Integer[] { startIndex, endIndex });
            }
        }

        // Account Number Named after Clients
        for (int j = 0; j < clientsWithActiveSavings.size(); j++) {
            Name name = savingsTransactionWorkbook.createName();
            setSanitized(name, "Account_" + clientsWithActiveSavings.get(j) + "_" + clientIdsWithActiveSavings.get(j) + "_");
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositTransactionWorkbookPopulator.java [65:182]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        setLayout(savingsTransactionSheet);
        officeSheetPopulator.populate(workbook, dateFormat);
        clientSheetPopulator.populate(workbook, dateFormat);
        extrasSheetPopulator.populate(workbook, dateFormat);
        populateSavingsTable(savingsTransactionSheet, dateFormat);
        setRules(savingsTransactionSheet, dateFormat);
        setDefaults(savingsTransactionSheet);
    }

    private void setDefaults(Sheet worksheet) {
        for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
            Row row = worksheet.getRow(rowNo);
            if (row == null) {
                row = worksheet.createRow(rowNo);
            }
            writeFormula(TransactionConstants.PRODUCT_COL, row,
                    "IF(ISERROR(VLOOKUP($C" + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",2,FALSE)),\"\",VLOOKUP($C"
                            + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",2,FALSE))");
            writeFormula(TransactionConstants.OPENING_BALANCE_COL, row,
                    "IF(ISERROR(VLOOKUP($C" + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",3,FALSE)),\"\",VLOOKUP($C"
                            + (rowNo + 1) + ",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",3,FALSE))");
        }
    }

    private void setRules(Sheet worksheet, String dateFormat) {
        CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.OFFICE_NAME_COL, TransactionConstants.OFFICE_NAME_COL);
        CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.CLIENT_NAME_COL, TransactionConstants.CLIENT_NAME_COL);
        CellRangeAddressList accountNumberRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.SAVINGS_ACCOUNT_NO_COL, TransactionConstants.SAVINGS_ACCOUNT_NO_COL);
        CellRangeAddressList transactionTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.TRANSACTION_TYPE_COL, TransactionConstants.TRANSACTION_TYPE_COL);
        CellRangeAddressList paymentTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.PAYMENT_TYPE_COL, TransactionConstants.PAYMENT_TYPE_COL);
        CellRangeAddressList transactionDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
                TransactionConstants.TRANSACTION_DATE_COL, TransactionConstants.TRANSACTION_DATE_COL);

        DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);

        setNames(worksheet);

        DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office");
        DataValidationConstraint clientNameConstraint = validationHelper
                .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))");
        DataValidationConstraint accountNumberConstraint = validationHelper.createFormulaListConstraint(
                "INDIRECT(CONCATENATE(\"Account_\",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B1,\" \",\"_\"),\"(\",\"_\"),\")\",\"_\")))");
        DataValidationConstraint transactionTypeConstraint = validationHelper
                .createExplicitListConstraint(new String[] { "Withdrawal", "Deposit" });
        DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes");
        DataValidationConstraint transactionDateConstraint = validationHelper.createDateConstraint(
                DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($C1,$Q$2:$T$" + (savingsAccounts.size() + 1) + ",4,FALSE)",
                "=TODAY()", dateFormat);

        DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange);
        DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange);
        DataValidation accountNumberValidation = validationHelper.createValidation(accountNumberConstraint, accountNumberRange);
        DataValidation transactionTypeValidation = validationHelper.createValidation(transactionTypeConstraint, transactionTypeRange);
        DataValidation paymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, paymentTypeRange);
        DataValidation transactionDateValidation = validationHelper.createValidation(transactionDateConstraint, transactionDateRange);

        worksheet.addValidationData(officeValidation);
        worksheet.addValidationData(clientValidation);
        worksheet.addValidationData(accountNumberValidation);
        worksheet.addValidationData(transactionTypeValidation);
        worksheet.addValidationData(paymentTypeValidation);
        worksheet.addValidationData(transactionDateValidation);
    }

    private void setNames(Sheet worksheet) {
        Workbook savingsTransactionWorkbook = worksheet.getWorkbook();
        List<String> officeNames = officeSheetPopulator.getOfficeNames();

        // Office Names
        Name officeGroup = savingsTransactionWorkbook.createName();
        officeGroup.setNameName("Office");
        officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME + "!$B$2:$B$" + (officeNames.size() + 1));

        // Clients Named after Offices
        for (Integer i = 0; i < officeNames.size(); i++) {
            Integer[] officeNameToBeginEndIndexesOfClients = clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
            Name name = savingsTransactionWorkbook.createName();
            if (officeNameToBeginEndIndexesOfClients != null) {
                setSanitized(name, "Client_" + officeNames.get(i));
                name.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_SHEET_NAME + "!$B$" + officeNameToBeginEndIndexesOfClients[0]
                        + ":$B$" + officeNameToBeginEndIndexesOfClients[1]);
            }
        }

        // Counting clients with active savings and starting and end addresses
        // of cells for naming
        HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<>();
        ArrayList<String> clientsWithActiveSavings = new ArrayList<>();
        ArrayList<Long> clientIdsWithActiveSavings = new ArrayList<>();
        int startIndex = 1;
        int endIndex = 1;
        String clientName = "";
        Long clientId = null;
        for (int i = 0; i < savingsAccounts.size(); i++) {
            if (!clientName.equals(savingsAccounts.get(i).getClientName())) {
                endIndex = i + 1;
                clientNameToBeginEndIndexes.put(clientName, new Integer[] { startIndex, endIndex });
                startIndex = i + 2;
                clientName = savingsAccounts.get(i).getClientName();
                clientId = savingsAccounts.get(i).getClientId();
                clientsWithActiveSavings.add(clientName);
                clientIdsWithActiveSavings.add(clientId);
            }
            if (i == savingsAccounts.size() - 1) {
                endIndex = i + 2;
                clientNameToBeginEndIndexes.put(clientName, new Integer[] { startIndex, endIndex });
            }
        }

        // Account Number Named after Clients
        for (int j = 0; j < clientsWithActiveSavings.size(); j++) {
            Name name = savingsTransactionWorkbook.createName();
            setSanitized(name, "Account_" + clientsWithActiveSavings.get(j) + "_" + clientIdsWithActiveSavings.get(j) + "_");
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -