fineract-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From av...@apache.org
Subject [06/19] fineract git commit: all modules populate & import
Date Wed, 13 Dec 2017 09:03:19 GMT
http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositTransactionWorkbookPopulator.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositTransactionWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositTransactionWorkbookPopulator.java
new file mode 100644
index 0000000..d462b5f
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositTransactionWorkbookPopulator.java
@@ -0,0 +1,242 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.populator.recurringdeposit;
+
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TransactionConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.ClientSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.ExtrasSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator;
+import org.apache.fineract.portfolio.savings.data.SavingsAccountData;
+import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+
+public class RecurringDepositTransactionWorkbookPopulator extends AbstractWorkbookPopulator {
+
+    private OfficeSheetPopulator officeSheetPopulator;
+    private ClientSheetPopulator clientSheetPopulator;
+    private ExtrasSheetPopulator extrasSheetPopulator;
+
+    private List<SavingsAccountData>savingsAccounts;
+
+    public RecurringDepositTransactionWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator,
+            ClientSheetPopulator clientSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator,
+            List<SavingsAccountData> savingsAccounts) {
+        this.officeSheetPopulator = officeSheetPopulator;
+        this.clientSheetPopulator = clientSheetPopulator;
+        this.extrasSheetPopulator = extrasSheetPopulator;
+        this.savingsAccounts=savingsAccounts;
+    }
+
+    @Override
+    public void populate(Workbook workbook,String dateFormat) {
+        Sheet savingsTransactionSheet = workbook.createSheet(TemplatePopulateImportConstants.SAVINGS_TRANSACTION_SHEET_NAME);
+        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) {
+                name.setNameName("Client_" + officeNames.get(i).trim().replaceAll("[ )(]", "_"));
+                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, 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();
+            name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_") + "_" + clientIdsWithActiveSavings.get(j) + "_");
+            name.setRefersToFormula(TemplatePopulateImportConstants.SAVINGS_TRANSACTION_SHEET_NAME+"!$Q$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0] + ":$Q$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]);
+        }
+
+        //Payment Type Name
+        Name paymentTypeGroup = savingsTransactionWorkbook.createName();
+        paymentTypeGroup.setNameName("PaymentTypes");
+        paymentTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1));
+    }
+
+    private void populateSavingsTable(Sheet savingsTransactionSheet,String dateFormat) {
+        Workbook workbook = savingsTransactionSheet.getWorkbook();
+        CellStyle dateCellStyle = workbook.createCellStyle();
+        short df = workbook.createDataFormat().getFormat(dateFormat);
+        dateCellStyle.setDataFormat(df);
+        int rowIndex = 1;
+        Row row;
+        Collections.sort(savingsAccounts, SavingsAccountData.ClientNameComparator);
+            for(SavingsAccountData savingsAccount : savingsAccounts) {
+                row = savingsTransactionSheet.createRow(rowIndex++);
+                writeString(TransactionConstants.LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName()  + "(" + savingsAccount.getClientId() + ")");
+                writeLong(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
+                writeString(TransactionConstants.LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
+                if(savingsAccount.getMinRequiredOpeningBalance() != null)
+                    writeBigDecimal(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
+                writeDate(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, row,"" +
+                        savingsAccount.getTimeline().getActivatedOnDate().getDayOfMonth() + "/"
+                        + savingsAccount.getTimeline().getActivatedOnDate().getMonthOfYear() + "/"
+                        + savingsAccount.getTimeline().getActivatedOnDate().getYear() , dateCellStyle,dateFormat);
+            }
+    }
+
+    private void setLayout(Sheet worksheet) {
+        Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX);
+        rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT);
+        worksheet.setColumnWidth(TransactionConstants.OFFICE_NAME_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.CLIENT_NAME_COL, 5000);
+        worksheet.setColumnWidth(TransactionConstants.SAVINGS_ACCOUNT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.PRODUCT_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.OPENING_BALANCE_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.TRANSACTION_TYPE_COL, 3300);
+        worksheet.setColumnWidth(TransactionConstants.AMOUNT_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.TRANSACTION_DATE_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.PAYMENT_TYPE_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.ACCOUNT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.CHECK_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.RECEIPT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.ROUTING_CODE_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.BANK_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_CLIENT_NAME_COL, 5000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_PRODUCT_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, 3700);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, 3500);
+        writeString(TransactionConstants.OFFICE_NAME_COL, rowHeader, "Office Name*");
+        writeString(TransactionConstants.CLIENT_NAME_COL, rowHeader, "Client Name*");
+        writeString(TransactionConstants.SAVINGS_ACCOUNT_NO_COL, rowHeader, "Account No.*");
+        writeString(TransactionConstants.PRODUCT_COL, rowHeader, "Product Name");
+        writeString(TransactionConstants.OPENING_BALANCE_COL, rowHeader, "Opening Balance");
+        writeString(TransactionConstants.TRANSACTION_TYPE_COL, rowHeader, "Transaction Type*");
+        writeString(TransactionConstants.AMOUNT_COL, rowHeader, "Amount*");
+        writeString(TransactionConstants.TRANSACTION_DATE_COL, rowHeader, "Date*");
+        writeString(TransactionConstants.PAYMENT_TYPE_COL, rowHeader, "Type*");
+        writeString(TransactionConstants.ACCOUNT_NO_COL, rowHeader, "Account No");
+        writeString(TransactionConstants.CHECK_NO_COL, rowHeader, "Check No");
+        writeString(TransactionConstants.RECEIPT_NO_COL, rowHeader, "Receipt No");
+        writeString(TransactionConstants.ROUTING_CODE_COL, rowHeader, "Routing Code");
+        writeString(TransactionConstants.BANK_NO_COL, rowHeader, "Bank No");
+        writeString(TransactionConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client");
+        writeString(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account");
+        writeString(TransactionConstants.LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product");
+        writeString(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, rowHeader, "Lookup Opening Balance");
+        writeString(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, rowHeader, "Lookup Savings Activation Date");
+        }
+    }
+

http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositWorkbookPopulator.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositWorkbookPopulator.java
new file mode 100644
index 0000000..a034eec
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/recurringdeposit/RecurringDepositWorkbookPopulator.java
@@ -0,0 +1,404 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.populator.recurringdeposit;
+
+import org.apache.fineract.infrastructure.bulkimport.constants.RecurringDepositConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.*;
+import org.apache.fineract.portfolio.savings.data.RecurringDepositProductData;
+import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+import java.util.List;
+
+public class RecurringDepositWorkbookPopulator extends AbstractWorkbookPopulator {
+
+    private OfficeSheetPopulator officeSheetPopulator;
+    private ClientSheetPopulator clientSheetPopulator;
+    private PersonnelSheetPopulator personnelSheetPopulator;
+    private RecurringDepositProductSheetPopulator productSheetPopulator;
+
+
+    public RecurringDepositWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator,
+            ClientSheetPopulator clientSheetPopulator, PersonnelSheetPopulator personnelSheetPopulator,
+            RecurringDepositProductSheetPopulator recurringDepositProductSheetPopulator) {
+
+        this.officeSheetPopulator = officeSheetPopulator;
+        this.clientSheetPopulator = clientSheetPopulator;
+        this.personnelSheetPopulator = personnelSheetPopulator;
+        this.productSheetPopulator = recurringDepositProductSheetPopulator;
+    }
+
+    @Override
+    public void populate(Workbook workbook,String dateFormat) {
+        Sheet recurringDepositSheet = workbook.createSheet(TemplatePopulateImportConstants.RECURRING_DEPOSIT_SHEET_NAME);
+        officeSheetPopulator.populate(workbook,dateFormat);
+        clientSheetPopulator.populate(workbook,dateFormat);
+        personnelSheetPopulator.populate(workbook,dateFormat);
+        productSheetPopulator.populate(workbook,dateFormat);
+        setRules(recurringDepositSheet,dateFormat);
+        setDefaults(recurringDepositSheet,dateFormat);
+        setClientAndGroupDateLookupTable(recurringDepositSheet, clientSheetPopulator.getClients(), null,
+                RecurringDepositConstants.LOOKUP_CLIENT_NAME_COL,  RecurringDepositConstants.LOOKUP_ACTIVATION_DATE_COL,!TemplatePopulateImportConstants.CONTAINS_CLIENT_EXTERNAL_ID,dateFormat);
+        setLayout(recurringDepositSheet);
+
+    }
+
+    private void setLayout(Sheet worksheet) {
+        Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX);
+        rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT);
+        worksheet.setColumnWidth(RecurringDepositConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.CLIENT_NAME_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.PRODUCT_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.FIELD_OFFICER_NAME_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.SUBMITTED_ON_DATE_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.APPROVED_DATE_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.ACTIVATION_DATE_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.INTEREST_POSTING_PERIOD_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.INTEREST_CALCULATION_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.LOCKIN_PERIOD_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.RECURRING_DEPOSIT_AMOUNT_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.DEPOSIT_PERIOD_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.DEPOSIT_FREQUENCY_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.DEPOSIT_FREQUENCY_TYPE_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.DEPOSIT_START_DATE_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.IS_MANDATORY_DEPOSIT_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.ALLOW_WITHDRAWAL_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.ADJUST_ADVANCE_PAYMENTS_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.FREQ_SAME_AS_GROUP_CENTER_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.EXTERNAL_ID_COL,  TemplatePopulateImportConstants.SMALL_COL_SIZE);
+
+        worksheet.setColumnWidth(RecurringDepositConstants.CHARGE_ID_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.CHARGE_AMOUNT_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.CHARGE_DUE_DATE_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.CHARGE_ID_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.CHARGE_AMOUNT_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.CHARGE_DUE_DATE_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+
+        worksheet.setColumnWidth(RecurringDepositConstants.LOOKUP_CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(RecurringDepositConstants.LOOKUP_ACTIVATION_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+
+        writeString(RecurringDepositConstants.OFFICE_NAME_COL, rowHeader, "Office Name*");
+        writeString(RecurringDepositConstants.CLIENT_NAME_COL, rowHeader, "Client Name*");
+        writeString(RecurringDepositConstants.PRODUCT_COL, rowHeader, "Product*");
+        writeString(RecurringDepositConstants.FIELD_OFFICER_NAME_COL, rowHeader, "Field Officer*");
+        writeString(RecurringDepositConstants.SUBMITTED_ON_DATE_COL, rowHeader, "Submitted On*");
+        writeString(RecurringDepositConstants.APPROVED_DATE_COL, rowHeader, "Approved On*");
+        writeString(RecurringDepositConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date*");
+        writeString(RecurringDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, rowHeader, "Interest Compounding Period*");
+        writeString(RecurringDepositConstants.INTEREST_POSTING_PERIOD_COL, rowHeader, "Interest Posting Period*");
+        writeString(RecurringDepositConstants.INTEREST_CALCULATION_COL, rowHeader, "Interest Calculated*");
+        writeString(RecurringDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, rowHeader, "# Days in Year*");
+        writeString(RecurringDepositConstants.LOCKIN_PERIOD_COL, rowHeader, "Locked In For");
+        writeString(RecurringDepositConstants.RECURRING_DEPOSIT_AMOUNT_COL, rowHeader, "Recurring Deposit Amount");
+        writeString(RecurringDepositConstants.DEPOSIT_PERIOD_COL, rowHeader, "Deposit Period");
+        writeString(RecurringDepositConstants.DEPOSIT_FREQUENCY_COL, rowHeader, "Deposit Frequency");
+        writeString(RecurringDepositConstants.DEPOSIT_START_DATE_COL, rowHeader, "Deposit Start Date");
+        writeString(RecurringDepositConstants.IS_MANDATORY_DEPOSIT_COL, rowHeader, "Is Mandatory Deposit?");
+        writeString(RecurringDepositConstants.ALLOW_WITHDRAWAL_COL, rowHeader, "Allow Withdrawal?");
+        writeString(RecurringDepositConstants.ADJUST_ADVANCE_PAYMENTS_COL, rowHeader, "Adjust Advance Payments Toward Future Installments ");
+        writeString(RecurringDepositConstants.FREQ_SAME_AS_GROUP_CENTER_COL, rowHeader, "Deposit Frequency Same as Group/Center meeting");
+        writeString(RecurringDepositConstants.EXTERNAL_ID_COL, rowHeader, "External Id");
+
+        writeString(RecurringDepositConstants.CHARGE_ID_1,rowHeader,"Charge Id");
+        writeString(RecurringDepositConstants.CHARGE_AMOUNT_1, rowHeader, "Charged Amount");
+        writeString(RecurringDepositConstants.CHARGE_DUE_DATE_1, rowHeader, "Charged On Date");
+        writeString(RecurringDepositConstants.CHARGE_ID_2,rowHeader,"Charge Id");
+        writeString(RecurringDepositConstants.CHARGE_AMOUNT_2, rowHeader, "Charged Amount");
+        writeString(RecurringDepositConstants.CHARGE_DUE_DATE_2, rowHeader, "Charged On Date");
+
+        writeString(RecurringDepositConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Client Name");
+        writeString(RecurringDepositConstants.LOOKUP_ACTIVATION_DATE_COL, rowHeader, "Client Activation Date");
+    }
+
+    private void setDefaults(Sheet worksheet,String dateFormat) {
+        Workbook workbook = worksheet.getWorkbook();
+        CellStyle dateCellStyle = workbook.createCellStyle();
+        short df = workbook.createDataFormat().getFormat(dateFormat);
+        dateCellStyle.setDataFormat(df);
+            for (Integer rowNo = 1; rowNo < 1000; rowNo++) {
+                Row row = worksheet.createRow(rowNo);
+                writeFormula(RecurringDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Compouding_\",$C"
+                        + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Compouding_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.INTEREST_POSTING_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Posting_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Posting_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.INTEREST_CALCULATION_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Calculation_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Calculation_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Days_In_Year_\",$C"
+                        + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"Days_In_Year_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.LOCKIN_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Lockin_Period_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Lockin_Period_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Lockin_Frequency_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Lockin_Frequency_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.RECURRING_DEPOSIT_AMOUNT_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Deposit_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Deposit_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Term_Type_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Term_Type_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.IS_MANDATORY_DEPOSIT_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Mandatory_Deposit_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Mandatory_Deposit_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.ALLOW_WITHDRAWAL_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Allow_Withdrawal_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Allow_Withdrawal_\",$C" + (rowNo + 1) + ")))");
+                writeFormula(RecurringDepositConstants.ADJUST_ADVANCE_PAYMENTS_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Adjust_Advance_\",$C" + (rowNo + 1)
+                        + "))),\"\",INDIRECT(CONCATENATE(\"Adjust_Advance_\",$C" + (rowNo + 1) + ")))");
+            }
+    }
+
+    private void setRules(Sheet worksheet,String dateFormat) {
+        CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.OFFICE_NAME_COL, RecurringDepositConstants.OFFICE_NAME_COL);
+        CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.CLIENT_NAME_COL, RecurringDepositConstants.CLIENT_NAME_COL);
+        CellRangeAddressList productNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.PRODUCT_COL, RecurringDepositConstants.PRODUCT_COL);
+        CellRangeAddressList fieldOfficerRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.FIELD_OFFICER_NAME_COL, RecurringDepositConstants.FIELD_OFFICER_NAME_COL);
+        CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.SUBMITTED_ON_DATE_COL, RecurringDepositConstants.SUBMITTED_ON_DATE_COL);
+        CellRangeAddressList approvedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.APPROVED_DATE_COL, RecurringDepositConstants.APPROVED_DATE_COL);
+        CellRangeAddressList activationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.ACTIVATION_DATE_COL, RecurringDepositConstants.ACTIVATION_DATE_COL);
+        CellRangeAddressList interestCompudingPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, RecurringDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL);
+        CellRangeAddressList interestPostingPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.INTEREST_POSTING_PERIOD_COL, RecurringDepositConstants.INTEREST_POSTING_PERIOD_COL);
+        CellRangeAddressList interestCalculationRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.INTEREST_CALCULATION_COL, RecurringDepositConstants.INTEREST_CALCULATION_COL);
+        CellRangeAddressList interestCalculationDaysInYearRange = new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), RecurringDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL,
+                RecurringDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL);
+        CellRangeAddressList lockinPeriodFrequencyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL, RecurringDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL);
+        CellRangeAddressList depositAmountRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.RECURRING_DEPOSIT_AMOUNT_COL,RecurringDepositConstants. RECURRING_DEPOSIT_AMOUNT_COL);
+        CellRangeAddressList depositPeriodTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL, RecurringDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL);
+        CellRangeAddressList depositFrequencyTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.DEPOSIT_FREQUENCY_TYPE_COL, RecurringDepositConstants.DEPOSIT_FREQUENCY_TYPE_COL);
+        CellRangeAddressList isMandatoryDepositRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants. IS_MANDATORY_DEPOSIT_COL, RecurringDepositConstants.IS_MANDATORY_DEPOSIT_COL);
+        CellRangeAddressList allowWithdrawalRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.ALLOW_WITHDRAWAL_COL, RecurringDepositConstants.ALLOW_WITHDRAWAL_COL);
+        CellRangeAddressList adjustAdvancePaymentRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants. ADJUST_ADVANCE_PAYMENTS_COL, RecurringDepositConstants.ADJUST_ADVANCE_PAYMENTS_COL);
+        CellRangeAddressList sameFreqAsGroupRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.FREQ_SAME_AS_GROUP_CENTER_COL, RecurringDepositConstants.FREQ_SAME_AS_GROUP_CENTER_COL);
+        CellRangeAddressList depositStartDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                RecurringDepositConstants.DEPOSIT_START_DATE_COL, RecurringDepositConstants.DEPOSIT_START_DATE_COL);
+
+        DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);
+
+        setNames(worksheet);
+
+        DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office");
+        DataValidationConstraint clientNameConstraint = validationHelper
+                .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))");
+        DataValidationConstraint productNameConstraint = validationHelper.createFormulaListConstraint("Products");
+        DataValidationConstraint fieldOfficerNameConstraint = validationHelper
+                .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$A1))");
+        DataValidationConstraint submittedDateConstraint = validationHelper.createDateConstraint(
+                DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($B1,$AF$2:$AG$"
+                        + (clientSheetPopulator.getClientsSize() + 1) + ",2,FALSE)", "=TODAY()",
+                dateFormat);
+        DataValidationConstraint approvalDateConstraint = validationHelper.createDateConstraint(
+                DataValidationConstraint.OperatorType.BETWEEN, "=$E1", "=TODAY()", dateFormat);
+        DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint(
+                DataValidationConstraint.OperatorType.BETWEEN, "=$F1", "=TODAY()", dateFormat);
+        DataValidationConstraint interestCompudingPeriodConstraint = validationHelper.createExplicitListConstraint(new String[] {
+               TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_DAILY ,
+                TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_MONTHLY,
+                TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_QUARTERLY,
+                TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_SEMI_ANNUALLY,
+                TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_ANNUALLY});
+        DataValidationConstraint interestPostingPeriodConstraint = validationHelper.createExplicitListConstraint(new String[] {
+               TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_MONTHLY ,
+                TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_QUARTERLY,
+                TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_BIANUALLY,
+                TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_ANNUALLY });
+        DataValidationConstraint interestCalculationConstraint = validationHelper.createExplicitListConstraint(new String[] {
+                TemplatePopulateImportConstants.INTEREST_CAL_DAILY_BALANCE,
+                TemplatePopulateImportConstants.INTEREST_CAL_AVG_BALANCE});
+
+        DataValidationConstraint interestCalculationDaysInYearConstraint = validationHelper.createExplicitListConstraint(new String[] {
+                TemplatePopulateImportConstants.INTEREST_CAL_DAYS_IN_YEAR_360,
+                TemplatePopulateImportConstants.INTEREST_CAL_DAYS_IN_YEAR_365});
+        DataValidationConstraint frequency = validationHelper.createExplicitListConstraint(new String[] {
+                TemplatePopulateImportConstants.FREQUENCY_DAYS,
+                TemplatePopulateImportConstants.FREQUENCY_WEEKS,
+                TemplatePopulateImportConstants.FREQUENCY_MONTHS,
+                TemplatePopulateImportConstants.FREQUENCY_YEARS});
+        DataValidationConstraint depositConstraint = validationHelper.createDecimalConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "=INDIRECT(CONCATENATE(\"Min_Deposit_\",$C1))", null);
+        DataValidationConstraint booleanConstraint = validationHelper.createExplicitListConstraint(new String[] {
+                "True", "False" });
+        DataValidationConstraint depositStartDateConstraint = validationHelper.createDateConstraint(
+                DataValidationConstraint.OperatorType.BETWEEN, "=$G1", "=TODAY()", "dd/mm/yy");
+
+        DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange);
+        DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange);
+        DataValidation productNameValidation = validationHelper.createValidation(productNameConstraint, productNameRange);
+        DataValidation fieldOfficerValidation = validationHelper.createValidation(fieldOfficerNameConstraint, fieldOfficerRange);
+        DataValidation interestCompudingPeriodValidation = validationHelper.createValidation(interestCompudingPeriodConstraint,
+                interestCompudingPeriodRange);
+        DataValidation interestPostingPeriodValidation = validationHelper.createValidation(interestPostingPeriodConstraint,
+                interestPostingPeriodRange);
+        DataValidation interestCalculationValidation = validationHelper.createValidation(interestCalculationConstraint,
+                interestCalculationRange);
+        DataValidation interestCalculationDaysInYearValidation = validationHelper.createValidation(
+                interestCalculationDaysInYearConstraint, interestCalculationDaysInYearRange);
+        DataValidation lockinPeriodFrequencyValidation = validationHelper.createValidation(frequency,
+                lockinPeriodFrequencyRange);
+        DataValidation depositPeriodTypeValidation = validationHelper.createValidation(frequency,
+                depositPeriodTypeRange);
+        DataValidation depositFrequencyTypeValidation = validationHelper.createValidation(frequency,
+                depositFrequencyTypeRange);
+        DataValidation submittedDateValidation = validationHelper.createValidation(submittedDateConstraint, submittedDateRange);
+        DataValidation approvalDateValidation = validationHelper.createValidation(approvalDateConstraint, approvedDateRange);
+        DataValidation activationDateValidation = validationHelper.createValidation(activationDateConstraint, activationDateRange);
+        DataValidation  depositAmountValidation = validationHelper.createValidation(depositConstraint, depositAmountRange);
+        DataValidation isMandatoryDepositValidation = validationHelper.createValidation(
+                booleanConstraint, isMandatoryDepositRange);
+        DataValidation allowWithdrawalValidation = validationHelper.createValidation(
+                booleanConstraint, allowWithdrawalRange);
+        DataValidation adjustAdvancePaymentValidation = validationHelper.createValidation(
+                booleanConstraint, adjustAdvancePaymentRange);
+        DataValidation sameFreqAsGroupValidation = validationHelper.createValidation(
+                booleanConstraint, sameFreqAsGroupRange);
+        DataValidation depositStartDateValidation = validationHelper.createValidation(
+                depositStartDateConstraint, depositStartDateRange);
+
+        worksheet.addValidationData(officeValidation);
+        worksheet.addValidationData(clientValidation);
+        worksheet.addValidationData(productNameValidation);
+        worksheet.addValidationData(fieldOfficerValidation);
+        worksheet.addValidationData(submittedDateValidation);
+        worksheet.addValidationData(approvalDateValidation);
+        worksheet.addValidationData(activationDateValidation);
+        worksheet.addValidationData(interestCompudingPeriodValidation);
+        worksheet.addValidationData(interestPostingPeriodValidation);
+        worksheet.addValidationData(interestCalculationValidation);
+        worksheet.addValidationData(interestCalculationDaysInYearValidation);
+        worksheet.addValidationData(lockinPeriodFrequencyValidation);
+        worksheet.addValidationData(depositPeriodTypeValidation);
+        worksheet.addValidationData(depositAmountValidation);
+        worksheet.addValidationData(depositFrequencyTypeValidation);
+        worksheet.addValidationData(isMandatoryDepositValidation);
+        worksheet.addValidationData(allowWithdrawalValidation);
+        worksheet.addValidationData(adjustAdvancePaymentValidation);
+        worksheet.addValidationData(sameFreqAsGroupValidation);
+        worksheet.addValidationData(depositStartDateValidation);
+    }
+
+    private void setNames(Sheet worksheet) {
+        Workbook savingsWorkbook = worksheet.getWorkbook();
+        List<String> officeNames = officeSheetPopulator.getOfficeNames();
+        List<RecurringDepositProductData> products = productSheetPopulator.getProducts();
+
+        // Office Names
+        Name officeGroup = savingsWorkbook.createName();
+        officeGroup.setNameName("Office");
+        officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (officeNames.size() + 1));
+
+        // Client and Loan Officer Names for each office
+        for (Integer i = 0; i < officeNames.size(); i++) {
+            Integer[] officeNameToBeginEndIndexesOfClients = clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
+            Integer[] officeNameToBeginEndIndexesOfStaff = personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i);
+            Name clientName = savingsWorkbook.createName();
+            Name fieldOfficerName = savingsWorkbook.createName();
+            if (officeNameToBeginEndIndexesOfStaff != null) {
+                fieldOfficerName.setNameName("Staff_" + officeNames.get(i).trim().replaceAll("[ )(]", "_"));
+                fieldOfficerName.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfStaff[0] + ":$B$"
+                        + officeNameToBeginEndIndexesOfStaff[1]);
+            }
+            if (officeNameToBeginEndIndexesOfClients != null) {
+                clientName.setNameName("Client_" + officeNames.get(i).trim().replaceAll("[ )(]", "_"));
+                clientName.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfClients[0] + ":$B$"
+                        + officeNameToBeginEndIndexesOfClients[1]);
+            }
+        }
+
+        // Product Name
+        Name productGroup = savingsWorkbook.createName();
+        productGroup.setNameName("Products");
+        productGroup.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$B$2:$B$" + (productSheetPopulator.getProductsSize() + 1));
+
+        // Default Interest Rate, Interest Compounding Period, Interest Posting
+        // Period, Interest Calculation, Interest Calculation Days In Year,
+        // Minimum Deposit, Lockin Period, Lockin Period Frequency
+        // Names for each product
+        for (Integer i = 0; i < products.size(); i++) {
+            Name interestCompoundingPeriodName = savingsWorkbook.createName();
+            Name interestPostingPeriodName = savingsWorkbook.createName();
+            Name interestCalculationName = savingsWorkbook.createName();
+            Name daysInYearName = savingsWorkbook.createName();
+            Name lockinPeriodName = savingsWorkbook.createName();
+            Name lockinPeriodFrequencyName = savingsWorkbook.createName();
+            Name depositName = savingsWorkbook.createName();
+            Name minDepositName = savingsWorkbook.createName();
+            Name maxDepositName = savingsWorkbook.createName();
+            Name minDepositTermTypeName = savingsWorkbook.createName();
+            Name allowWithdrawalName = savingsWorkbook.createName();
+            Name mandatoryDepositName = savingsWorkbook.createName();
+            Name adjustAdvancePaymentsName = savingsWorkbook.createName();
+
+            RecurringDepositProductData product = products.get(i);
+            String productName = product.getName().replaceAll("[ ]", "_");
+
+            interestCompoundingPeriodName.setNameName("Interest_Compouding_" + productName);
+            interestPostingPeriodName.setNameName("Interest_Posting_" + productName);
+            interestCalculationName.setNameName("Interest_Calculation_" + productName);
+            daysInYearName.setNameName("Days_In_Year_" + productName);
+            minDepositName.setNameName("Min_Deposit_" + productName);
+            maxDepositName.setNameName("Max_Deposit_" + productName);
+            depositName.setNameName("Deposit_" + productName);
+            allowWithdrawalName.setNameName("Allow_Withdrawal_" + productName);
+            mandatoryDepositName.setNameName("Mandatory_Deposit_" + productName);
+            adjustAdvancePaymentsName.setNameName("Adjust_Advance_" + productName);
+            interestCompoundingPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$E$" + (i + 2));
+            interestPostingPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$F$" + (i + 2));
+            interestCalculationName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$G$" + (i + 2));
+            daysInYearName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$H$" + (i + 2));
+            depositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$N$" + (i + 2));
+            minDepositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$L$" + (i + 2));
+            maxDepositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$M$" + (i + 2));
+            allowWithdrawalName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$Y$" + (i + 2));
+            mandatoryDepositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$X$" + (i + 2));
+            adjustAdvancePaymentsName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$Z$" + (i + 2));
+
+            if(product.getMinDepositTermType() != null) {
+                minDepositTermTypeName.setNameName("Term_Type_" + productName);
+                minDepositTermTypeName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$P$" + (i + 2));
+            }
+            if (product.getLockinPeriodFrequency() != null) {
+                lockinPeriodName.setNameName("Lockin_Period_" + productName);
+                lockinPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$I$" + (i + 2));
+            }
+            if (product.getLockinPeriodFrequencyType() != null) {
+                lockinPeriodFrequencyName.setNameName("Lockin_Frequency_" + productName);
+                lockinPeriodFrequencyName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$J$" + (i + 2));
+            }
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsTransactionsWorkbookPopulator.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsTransactionsWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsTransactionsWorkbookPopulator.java
new file mode 100644
index 0000000..2573b71
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsTransactionsWorkbookPopulator.java
@@ -0,0 +1,240 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.populator.savings;
+
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TransactionConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.ClientSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.ExtrasSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator;
+import org.apache.fineract.portfolio.savings.data.SavingsAccountData;
+import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.joda.time.format.DateTimeFormat;
+import org.joda.time.format.DateTimeFormatter;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+
+public class SavingsTransactionsWorkbookPopulator extends AbstractWorkbookPopulator {
+    private OfficeSheetPopulator officeSheetPopulator;
+    private ClientSheetPopulator clientSheetPopulator;
+    private ExtrasSheetPopulator extrasSheetPopulator;
+
+    private List<SavingsAccountData>savingsAccounts;
+
+    public SavingsTransactionsWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator,
+            ClientSheetPopulator clientSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator,
+            List<SavingsAccountData> savingsAccounts) {
+        this.officeSheetPopulator = officeSheetPopulator;
+        this.clientSheetPopulator = clientSheetPopulator;
+        this.extrasSheetPopulator = extrasSheetPopulator;
+        this.savingsAccounts=savingsAccounts;
+    }
+
+    @Override
+    public void populate(Workbook workbook,String dateFormat) {
+        Sheet savingsTransactionSheet = workbook.createSheet(TemplatePopulateImportConstants.SAVINGS_TRANSACTION_SHEET_NAME);
+        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) {
+                name.setNameName("Client_" + officeNames.get(i).trim().replaceAll("[ )(]", "_"));
+                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, 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();
+            name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_") + "_" + clientIdsWithActiveSavings.get(j) + "_");
+            name.setRefersToFormula(TemplatePopulateImportConstants.SAVINGS_TRANSACTION_SHEET_NAME+"!$Q$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0] + ":$Q$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]);
+        }
+
+        //Payment Type Name
+        Name paymentTypeGroup = savingsTransactionWorkbook.createName();
+        paymentTypeGroup.setNameName("PaymentTypes");
+        paymentTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1));
+    }
+
+    private void populateSavingsTable(Sheet savingsTransactionSheet,String dateFormat) {
+        Workbook workbook = savingsTransactionSheet.getWorkbook();
+        CellStyle dateCellStyle = workbook.createCellStyle();
+        short df = workbook.createDataFormat().getFormat(dateFormat);
+        dateCellStyle.setDataFormat(df);
+        int rowIndex = 1;
+        Row row;
+        Collections.sort(savingsAccounts, SavingsAccountData.ClientNameComparator);
+        for(SavingsAccountData savingsAccount : savingsAccounts) {
+            row = savingsTransactionSheet.createRow(rowIndex++);
+            writeString(TransactionConstants.LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName()  + "(" + savingsAccount.getClientId() + ")");
+            writeLong(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
+            writeString(TransactionConstants.LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
+            if(savingsAccount.getMinRequiredOpeningBalance() != null)
+                writeBigDecimal(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
+            writeDate(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, row,"" +
+                    savingsAccount.getTimeline().getActivatedOnDate().getDayOfMonth() + "/"
+                    + savingsAccount.getTimeline().getActivatedOnDate().getMonthOfYear() + "/"
+                    + savingsAccount.getTimeline().getActivatedOnDate().getYear() , dateCellStyle,dateFormat);
+        }
+    }
+
+    private void setLayout(Sheet worksheet) {
+        Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX);
+        rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT);
+        worksheet.setColumnWidth(TransactionConstants.OFFICE_NAME_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.CLIENT_NAME_COL, 5000);
+        worksheet.setColumnWidth(TransactionConstants.SAVINGS_ACCOUNT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.PRODUCT_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.OPENING_BALANCE_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.TRANSACTION_TYPE_COL, 3300);
+        worksheet.setColumnWidth(TransactionConstants.AMOUNT_COL, 4000);
+        worksheet.setColumnWidth(TransactionConstants.TRANSACTION_DATE_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.PAYMENT_TYPE_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.ACCOUNT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.CHECK_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.RECEIPT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.ROUTING_CODE_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.BANK_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_CLIENT_NAME_COL, 5000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_PRODUCT_COL, 3000);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, 3700);
+        worksheet.setColumnWidth(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, 3500);
+        writeString(TransactionConstants.OFFICE_NAME_COL, rowHeader, "Office Name*");
+        writeString(TransactionConstants.CLIENT_NAME_COL, rowHeader, "Client Name*");
+        writeString(TransactionConstants.SAVINGS_ACCOUNT_NO_COL, rowHeader, "Account No.*");
+        writeString(TransactionConstants.PRODUCT_COL, rowHeader, "Product Name");
+        writeString(TransactionConstants.OPENING_BALANCE_COL, rowHeader, "Opening Balance");
+        writeString(TransactionConstants.TRANSACTION_TYPE_COL, rowHeader, "Transaction Type*");
+        writeString(TransactionConstants.AMOUNT_COL, rowHeader, "Amount*");
+        writeString(TransactionConstants.TRANSACTION_DATE_COL, rowHeader, "Date*");
+        writeString(TransactionConstants.PAYMENT_TYPE_COL, rowHeader, "Type*");
+        writeString(TransactionConstants.ACCOUNT_NO_COL, rowHeader, "Account No");
+        writeString(TransactionConstants.CHECK_NO_COL, rowHeader, "Check No");
+        writeString(TransactionConstants.RECEIPT_NO_COL, rowHeader, "Receipt No");
+        writeString(TransactionConstants.ROUTING_CODE_COL, rowHeader, "Routing Code");
+        writeString(TransactionConstants.BANK_NO_COL, rowHeader, "Bank No");
+        writeString(TransactionConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client");
+        writeString(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account");
+        writeString(TransactionConstants.LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product");
+        writeString(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, rowHeader, "Lookup Opening Balance");
+        writeString(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, rowHeader, "Lookup Savings Activation Date");
+    }
+}


Mime
View raw message