fineract-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From av...@apache.org
Subject [09/19] fineract git commit: all modules populate & import
Date Wed, 13 Dec 2017 09:03:22 GMT
http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java
new file mode 100644
index 0000000..27248c6
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java
@@ -0,0 +1,235 @@
+/**
+ * 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.centers;
+
+import org.apache.fineract.infrastructure.bulkimport.constants.CenterConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.GroupSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.PersonnelSheetPopulator;
+import org.apache.fineract.organisation.office.data.OfficeData;
+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 CentersWorkbookPopulator extends AbstractWorkbookPopulator {
+
+
+	private OfficeSheetPopulator officeSheetPopulator;
+	private PersonnelSheetPopulator personnelSheetPopulator;
+	private GroupSheetPopulator groupSheetPopulator;
+
+	public CentersWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator,
+			PersonnelSheetPopulator personnelSheetPopulator,GroupSheetPopulator groupSheetPopulator) {
+		this.officeSheetPopulator = officeSheetPopulator;
+		this.personnelSheetPopulator = personnelSheetPopulator;
+		this.groupSheetPopulator=groupSheetPopulator;
+	}
+
+	@Override
+	public void populate(Workbook workbook,String dateFormat) {
+		Sheet centerSheet = workbook.createSheet(TemplatePopulateImportConstants.CENTER_SHEET_NAME);
+		personnelSheetPopulator.populate(workbook,dateFormat);
+		officeSheetPopulator.populate(workbook,dateFormat);
+		groupSheetPopulator.populate(workbook,dateFormat);
+		setLayout(centerSheet);
+		setLookupTable(centerSheet,dateFormat);
+		setRules(centerSheet,dateFormat);
+	}
+	
+
+	private void setLayout(Sheet worksheet) {
+		Row rowHeader = worksheet.createRow(0);
+		rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT);
+		worksheet.setColumnWidth(CenterConstants.CENTER_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.SUBMITTED_ON_DATE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.MEETING_START_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.IS_REPEATING_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.INTERVAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.REPEATS_ON_DAY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.STATUS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.CENTER_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.FAILURE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.GROUP_NAMES_STARTING_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.LOOKUP_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.LOOKUP_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.LOOKUP_REPEAT_NORMAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.LOOKUP_REPEAT_MONTHLY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+		worksheet.setColumnWidth(CenterConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+
+		writeString(CenterConstants.CENTER_NAME_COL, rowHeader, "Center Name*");
+		writeString(CenterConstants.OFFICE_NAME_COL, rowHeader, "Office Name*");
+		writeString(CenterConstants.STAFF_NAME_COL, rowHeader, "Staff Name*");
+		writeString(CenterConstants.EXTERNAL_ID_COL, rowHeader, "External ID");
+		writeString(CenterConstants.ACTIVE_COL, rowHeader, "Active*");
+		writeString(CenterConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date*");
+		writeString(CenterConstants.SUBMITTED_ON_DATE_COL,rowHeader,"Submitted On Date");
+		writeString(CenterConstants.MEETING_START_DATE_COL, rowHeader, "Meeting Start Date* (On or After)");
+		writeString(CenterConstants.IS_REPEATING_COL, rowHeader, "Repeat*");
+		writeString(CenterConstants.FREQUENCY_COL, rowHeader, "Frequency*");
+		writeString(CenterConstants.INTERVAL_COL, rowHeader, "Interval*");
+		writeString(CenterConstants.REPEATS_ON_DAY_COL, rowHeader, "Repeats On*");
+		writeString(CenterConstants.GROUP_NAMES_STARTING_COL,rowHeader,"Group Names* (Enter in consecutive cells horizontally)");
+		writeString(CenterConstants.LOOKUP_OFFICE_NAME_COL, rowHeader, "Office Name");
+		writeString(CenterConstants.LOOKUP_OFFICE_OPENING_DATE_COL, rowHeader, "Opening Date");
+		writeString(CenterConstants.LOOKUP_REPEAT_NORMAL_COL, rowHeader, "Repeat Normal Range");
+		writeString(CenterConstants.LOOKUP_REPEAT_MONTHLY_COL, rowHeader, "Repeat Monthly Range");
+		writeString(CenterConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, rowHeader, "If Repeat Weekly Range");
+	}
+	private void setLookupTable(Sheet centerSheet,String dateFormat) {
+		setOfficeDateLookupTable(centerSheet, officeSheetPopulator.getOffices(), CenterConstants.LOOKUP_OFFICE_NAME_COL,CenterConstants.LOOKUP_OFFICE_OPENING_DATE_COL,dateFormat);
+    	int rowIndex;
+    	for(rowIndex = 1; rowIndex <= 11; rowIndex++) {
+    		Row row = centerSheet.getRow(rowIndex);
+    		if(row == null)
+    			row = centerSheet.createRow(rowIndex);
+    		writeInt(CenterConstants.LOOKUP_REPEAT_MONTHLY_COL, row, rowIndex);
+    	}
+    	for(rowIndex = 1; rowIndex <= 3; rowIndex++) 
+    		writeInt(CenterConstants.LOOKUP_REPEAT_NORMAL_COL, centerSheet.getRow(rowIndex), rowIndex);
+
+    	String[] days = new String[]{
+    			TemplatePopulateImportConstants.MONDAY,
+				TemplatePopulateImportConstants.TUESDAY,
+				TemplatePopulateImportConstants.WEDNESDAY,
+				TemplatePopulateImportConstants.THURSDAY,
+				TemplatePopulateImportConstants.FRIDAY,
+				TemplatePopulateImportConstants.SATURDAY,
+				TemplatePopulateImportConstants.SUNDAY};
+
+    	for(rowIndex = 1; rowIndex <= 7; rowIndex++)
+    		writeString(CenterConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, centerSheet.getRow(rowIndex), days[rowIndex-1]);
+		
+	}
+	private void setRules(Sheet worksheet,String dateFormat) {
+    	CellRangeAddressList officeNameRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.OFFICE_NAME_COL,CenterConstants. OFFICE_NAME_COL);
+    	CellRangeAddressList staffNameRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.STAFF_NAME_COL,CenterConstants. STAFF_NAME_COL);
+    	CellRangeAddressList activationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),CenterConstants. ACTIVATION_DATE_COL,CenterConstants. ACTIVATION_DATE_COL);
+    	CellRangeAddressList activeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.ACTIVE_COL, CenterConstants.ACTIVE_COL);
+		CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),CenterConstants. SUBMITTED_ON_DATE_COL,CenterConstants.SUBMITTED_ON_DATE_COL);
+    	CellRangeAddressList meetingStartDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.MEETING_START_DATE_COL,CenterConstants. MEETING_START_DATE_COL);
+    	CellRangeAddressList isRepeatRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),CenterConstants. IS_REPEATING_COL,CenterConstants. IS_REPEATING_COL);
+    	CellRangeAddressList repeatsRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.FREQUENCY_COL, CenterConstants.FREQUENCY_COL);
+    	CellRangeAddressList repeatsEveryRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.INTERVAL_COL,CenterConstants. INTERVAL_COL);
+    	CellRangeAddressList repeatsOnRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.REPEATS_ON_DAY_COL,CenterConstants. REPEATS_ON_DAY_COL);
+    	
+    	
+    	DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet)worksheet);
+    	List<OfficeData> offices = officeSheetPopulator.getOffices();
+    	setNames(worksheet, offices);
+    	
+
+    	DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office");
+    	DataValidationConstraint staffNameConstraint = validationHelper.
+				createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$B1))");
+    	DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint
+				(DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($B1,$IR$2:$IS" + (offices.size() + 1)+",2,FALSE)",
+						"=TODAY()", dateFormat);
+    	DataValidationConstraint booleanConstraint = validationHelper.createExplicitListConstraint(new String[]{"True", "False"});
+		DataValidationConstraint submittedOnDateConstraint =
+				validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
+						"=$F1", null,dateFormat);
+    	DataValidationConstraint meetingStartDateConstraint =
+				validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,
+						"=$F1", "=TODAY()", dateFormat);
+    	DataValidationConstraint repeatsConstraint =
+				validationHelper.createExplicitListConstraint(new String[]{
+						TemplatePopulateImportConstants.FREQUENCY_DAILY,
+						TemplatePopulateImportConstants.FREQUENCY_WEEKLY,
+						TemplatePopulateImportConstants.FREQUENCY_MONTHLY,
+						TemplatePopulateImportConstants.FREQUENCY_YEARLY});
+    	DataValidationConstraint repeatsEveryConstraint = validationHelper.createFormulaListConstraint("INDIRECT($J1)");
+    	DataValidationConstraint repeatsOnConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE($J1,\"_DAYS\"))");
+
+
+    	DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange);
+    	DataValidation staffValidation = validationHelper.createValidation(staffNameConstraint, staffNameRange);
+    	DataValidation activationDateValidation = validationHelper.createValidation(activationDateConstraint, activationDateRange);
+    	DataValidation activeValidation = validationHelper.createValidation(booleanConstraint, activeRange);
+    	DataValidation submittedOnValidation=validationHelper.createValidation(submittedOnDateConstraint,submittedDateRange);
+    	DataValidation meetingStartDateValidation = validationHelper.createValidation(meetingStartDateConstraint, meetingStartDateRange);
+    	DataValidation isRepeatValidation = validationHelper.createValidation(booleanConstraint, isRepeatRange);
+    	DataValidation repeatsValidation = validationHelper.createValidation(repeatsConstraint, repeatsRange);
+    	DataValidation repeatsEveryValidation = validationHelper.createValidation(repeatsEveryConstraint, repeatsEveryRange);
+    	DataValidation repeatsOnValidation = validationHelper.createValidation(repeatsOnConstraint, repeatsOnRange);
+    	
+
+    	worksheet.addValidationData(activeValidation);
+        worksheet.addValidationData(officeValidation);
+        worksheet.addValidationData(staffValidation);
+        worksheet.addValidationData(activationDateValidation);
+        worksheet.addValidationData(submittedOnValidation);
+        worksheet.addValidationData(meetingStartDateValidation);
+        worksheet.addValidationData(isRepeatValidation);
+        worksheet.addValidationData(repeatsValidation);
+        worksheet.addValidationData(repeatsEveryValidation);
+        worksheet.addValidationData(repeatsOnValidation);
+	}
+	
+	private void setNames(Sheet worksheet, List<OfficeData> offices) {
+    	Workbook centerWorkbook = worksheet.getWorkbook();
+    	Name officeCenter = centerWorkbook.createName();
+    	officeCenter.setNameName("Office");
+    	officeCenter.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME +"!$B$2:$B$" + (offices.size() + 1));
+    	
+    	
+    	//Repeat constraint names
+    	Name repeatsDaily = centerWorkbook.createName();
+    	repeatsDaily.setNameName("Daily");
+    	repeatsDaily.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IT$2:$IT$4");
+    	Name repeatsWeekly = centerWorkbook.createName();
+    	repeatsWeekly.setNameName("Weekly");
+    	repeatsWeekly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IT$2:$IT$4");
+    	Name repeatYearly = centerWorkbook.createName();
+    	repeatYearly.setNameName("Yearly");
+    	repeatYearly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IT$2:$IT$4");
+    	Name repeatsMonthly = centerWorkbook.createName();
+    	repeatsMonthly.setNameName("Monthly");
+    	repeatsMonthly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IU$2:$IU$12");
+    	Name repeatsOnWeekly = centerWorkbook.createName();
+    	repeatsOnWeekly.setNameName("Weekly_Days");
+    	repeatsOnWeekly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IV$2:$IV$8");
+    	
+    	
+    	//Staff Names for each office
+    	for(Integer i = 0; i < offices.size(); i++) {
+    		Integer[] officeNameToBeginEndIndexesOfStaff = personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i);
+    		Name loanOfficerName = centerWorkbook.createName();
+    		 if(officeNameToBeginEndIndexesOfStaff != null) {
+    	        loanOfficerName.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_"));
+    	        loanOfficerName.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + officeNameToBeginEndIndexesOfStaff[1]);
+    		 }
+    	}
+		
+	}
+	
+	
+}
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java
new file mode 100644
index 0000000..f65be13
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java
@@ -0,0 +1,236 @@
+/**
+ * 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.chartofaccounts;
+
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.accounting.glaccount.domain.GLAccountType;
+import org.apache.fineract.accounting.glaccount.domain.GLAccountUsage;
+import org.apache.fineract.infrastructure.bulkimport.constants.ChartOfAcountsConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+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.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator {
+    private List<GLAccountData> glAccounts;
+    private Map<String,List<String>> accountTypeToAccountNameAndTag;
+    private Map<Integer,Integer[]>accountTypeToBeginEndIndexesofAccountNames;
+    private List<String> accountTypesNoDuplicatesList;
+
+
+    public ChartOfAccountsWorkbook(List<GLAccountData> glAccounts) {
+        this.glAccounts = glAccounts;
+    }
+
+    @Override
+    public void populate(Workbook workbook,String dateFormat) {
+        Sheet chartOfAccountsSheet=workbook.createSheet(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME);
+        setLayout(chartOfAccountsSheet);
+        setAccountTypeToAccountNameAndTag();
+        setLookupTable(chartOfAccountsSheet);
+        setRules(chartOfAccountsSheet);
+        setDefaults(chartOfAccountsSheet);
+    }
+
+    private void setAccountTypeToAccountNameAndTag() {
+        accountTypeToAccountNameAndTag=new HashMap<>();
+        for (GLAccountData glAccount: glAccounts) {
+            addToaccountTypeToAccountNameMap(glAccount.getType().getValue(),glAccount.getName()+
+                    "-"+glAccount.getId()+"-"+glAccount.getTagId().getName()+"-"+glAccount.getTagId().getId());
+        }
+    }
+
+    private void addToaccountTypeToAccountNameMap(String key, String value) {
+        List<String> values=accountTypeToAccountNameAndTag.get(key);
+        if (values==null){
+            values=new ArrayList<String>();
+        }
+        if (!values.contains(value)){
+            values.add(value);
+            accountTypeToAccountNameAndTag.put(key,values);
+        }
+    }
+
+    private void setRules(Sheet chartOfAccountsSheet) {
+        CellRangeAddressList accountTypeRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                ChartOfAcountsConstants. ACCOUNT_TYPE_COL,ChartOfAcountsConstants.ACCOUNT_TYPE_COL);
+        CellRangeAddressList accountUsageRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                ChartOfAcountsConstants.ACCOUNT_USAGE_COL,ChartOfAcountsConstants.ACCOUNT_USAGE_COL);
+        CellRangeAddressList manualEntriesAllowedRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL,ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL);
+        CellRangeAddressList parentRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                ChartOfAcountsConstants.PARENT_COL,ChartOfAcountsConstants.PARENT_COL);
+        CellRangeAddressList tagRange = new  CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
+                ChartOfAcountsConstants.TAG_COL,ChartOfAcountsConstants.TAG_COL);
+
+        DataValidationHelper validationHelper=new HSSFDataValidationHelper((HSSFSheet) chartOfAccountsSheet);
+        setNames(chartOfAccountsSheet, accountTypesNoDuplicatesList);
+
+        DataValidationConstraint accountTypeConstraint=validationHelper.
+                createExplicitListConstraint(new  String[]{
+                        GLAccountType.ASSET.toString(),
+                        GLAccountType.LIABILITY.toString(),
+                        GLAccountType.EQUITY.toString(),
+                        GLAccountType.INCOME.toString(),
+                        GLAccountType.EXPENSE.toString()});
+        DataValidationConstraint accountUsageConstraint=validationHelper.
+                createExplicitListConstraint(new String[]{
+                        GLAccountUsage.DETAIL.toString(),
+                        GLAccountUsage.HEADER.toString()});
+        DataValidationConstraint booleanConstraint=validationHelper.
+                createExplicitListConstraint(new String[]{"True","False"});
+        DataValidationConstraint parentConstraint=validationHelper.
+                createFormulaListConstraint("INDIRECT(CONCATENATE(\"AccountName_\",$A1))");
+        DataValidationConstraint tagConstraint=validationHelper.
+                createFormulaListConstraint("INDIRECT(CONCATENATE(\"Tags_\",$A1))");
+
+        DataValidation accountTypeValidation=validationHelper.createValidation(accountTypeConstraint,accountTypeRange);
+        DataValidation accountUsageValidation=validationHelper.createValidation(accountUsageConstraint,accountUsageRange);
+        DataValidation manualEntriesValidation=validationHelper.createValidation(booleanConstraint,manualEntriesAllowedRange);
+        DataValidation parentValidation=validationHelper.createValidation(parentConstraint,parentRange);
+        DataValidation tagValidation=validationHelper.createValidation(tagConstraint,tagRange);
+
+        chartOfAccountsSheet.addValidationData(accountTypeValidation);
+        chartOfAccountsSheet.addValidationData(accountUsageValidation);
+        chartOfAccountsSheet.addValidationData(manualEntriesValidation);
+        chartOfAccountsSheet.addValidationData(parentValidation);
+        chartOfAccountsSheet.addValidationData(tagValidation);
+    }
+
+    private void setNames(Sheet chartOfAccountsSheet,List<String> accountTypesNoDuplicatesList) {
+        Workbook chartOfAccountsWorkbook=chartOfAccountsSheet.getWorkbook();
+        for (Integer i=0;i<accountTypesNoDuplicatesList.size();i++){
+            Name tags=chartOfAccountsWorkbook.createName();
+            Integer [] tagValueBeginEndIndexes=accountTypeToBeginEndIndexesofAccountNames.get(i);
+            if(accountTypeToBeginEndIndexesofAccountNames!=null){
+                tags.setNameName("Tags_"+accountTypesNoDuplicatesList.get(i));
+                tags.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME+
+                        "!$S$"+tagValueBeginEndIndexes[0]+":$S$"+tagValueBeginEndIndexes[1]);
+            }
+            Name accountNames=chartOfAccountsWorkbook.createName();
+            Integer [] accountNamesBeginEndIndexes=accountTypeToBeginEndIndexesofAccountNames.get(i);
+            if (accountNamesBeginEndIndexes!=null){
+                accountNames.setNameName("AccountName_"+accountTypesNoDuplicatesList.get(i));
+                accountNames.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME+
+                        "!$Q$"+accountNamesBeginEndIndexes[0]+":$Q$"+accountNamesBeginEndIndexes[1]);
+            }
+        }
+    }
+    private void setDefaults(Sheet worksheet){
+        try {
+            for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
+                Row row = worksheet.getRow(rowNo);
+                if (row == null)
+                    row = worksheet.createRow(rowNo);
+                writeFormula(ChartOfAcountsConstants.PARENT_ID_COL, row,
+                        "IF(ISERROR(VLOOKUP($E"+(rowNo+1)+",$Q$2:$R$"+(glAccounts.size()+1)+",2,FALSE))," +
+                                "\"\",(VLOOKUP($E"+(rowNo+1)+",$Q$2:$R$"+(glAccounts.size()+1)+",2,FALSE)))");
+                writeFormula(ChartOfAcountsConstants.TAG_ID_COL,row,
+                        "IF(ISERROR(VLOOKUP($H"+(rowNo+1)+",$S$2:$T$"+(glAccounts.size()+1)+",2,FALSE))," +
+                                "\"\",(VLOOKUP($H"+(rowNo+1)+",$S$2:$T$"+(glAccounts.size()+1)+",2,FALSE)))");
+            }
+        } catch (Exception e) {
+            e.printStackTrace();
+        }
+    }
+
+    private void setLookupTable(Sheet chartOfAccountsSheet) {
+        accountTypesNoDuplicatesList =new ArrayList<>();
+        for (int i = 0; i <glAccounts.size() ; i++) {
+            if (!accountTypesNoDuplicatesList.contains(glAccounts.get(i).getType().getValue())) {
+                accountTypesNoDuplicatesList.add(glAccounts.get(i).getType().getValue());
+            }
+        }
+        int rowIndex=1,startIndex=1,accountTypeIndex=0;
+        accountTypeToBeginEndIndexesofAccountNames= new HashMap<Integer,Integer[]>();
+        for (String accountType: accountTypesNoDuplicatesList) {
+             startIndex=rowIndex+1;
+             Row row =chartOfAccountsSheet.createRow(rowIndex);
+             writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL,row,accountType);
+             List<String> accountNamesandTags =accountTypeToAccountNameAndTag.get(accountType);
+             if (!accountNamesandTags.isEmpty()){
+                 for (String accountNameandTag:accountNamesandTags) {
+                     if (chartOfAccountsSheet.getRow(rowIndex)!=null){
+                         String accountNameAndTagAr[]=accountNameandTag.split("-");
+                         writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,row,accountNameAndTagAr[0]);
+                         writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,row,accountNameAndTagAr[1]);
+                         writeString(ChartOfAcountsConstants.LOOKUP_TAG_COL,row,accountNameAndTagAr[2]);
+                         writeString(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,row,accountNameAndTagAr[3]);
+                         rowIndex++;
+                     }else{
+                         row =chartOfAccountsSheet.createRow(rowIndex);
+                         String accountNameAndTagAr[]=accountNameandTag.split("-");
+                         writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,row,accountNameAndTagAr[0]);
+                         writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,row,accountNameAndTagAr[1]);
+                         writeString(ChartOfAcountsConstants.LOOKUP_TAG_COL,row,accountNameAndTagAr[2]);
+                         writeString(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,row,accountNameAndTagAr[3]);
+                         rowIndex++;
+                     }
+                 }
+                 accountTypeToBeginEndIndexesofAccountNames.put(accountTypeIndex++,new Integer[]{startIndex,rowIndex});
+             }else {
+                 accountTypeIndex++;
+             }
+        }
+    }
+
+    private void setLayout(Sheet chartOfAccountsSheet) {
+        Row rowHeader=chartOfAccountsSheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.ACCOUNT_TYPE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.ACCOUNT_NAME_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.ACCOUNT_USAGE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.PARENT_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.PARENT_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.GL_CODE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.TAG_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.TAG_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.DESCRIPTION_COL,TemplatePopulateImportConstants.EXTRALARGE_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_TAG_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        
+        writeString(ChartOfAcountsConstants.ACCOUNT_TYPE_COL,rowHeader,"Account Type*");
+        writeString(ChartOfAcountsConstants.GL_CODE_COL,rowHeader,"GL Code *");
+        writeString(ChartOfAcountsConstants.ACCOUNT_USAGE_COL,rowHeader,"Account Usage *");
+        writeString(ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL,rowHeader,"Manual entries allowed *");
+        writeString(ChartOfAcountsConstants.PARENT_COL,rowHeader,"Parent");
+        writeString(ChartOfAcountsConstants.PARENT_ID_COL,rowHeader,"Parent Id");
+        writeString(ChartOfAcountsConstants.ACCOUNT_NAME_COL,rowHeader,"Account Name");
+        writeString(ChartOfAcountsConstants.TAG_COL,rowHeader,"Tag *");
+        writeString(ChartOfAcountsConstants.TAG_ID_COL,rowHeader,"Tag Id");
+        writeString(ChartOfAcountsConstants.DESCRIPTION_COL,rowHeader,"Description *");
+        writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL,rowHeader,"Lookup Account type");
+        writeString(ChartOfAcountsConstants.LOOKUP_TAG_COL,rowHeader,"Lookup Tag");
+        writeString(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,rowHeader,"Lookup Tag Id");
+        writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,rowHeader,"Lookup Account name *");
+        writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,rowHeader,"Lookup Account Id");
+
+    }
+}

http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java
new file mode 100644
index 0000000..9907985
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java
@@ -0,0 +1,405 @@
+/**
+ * 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.client;
+
+import org.apache.fineract.infrastructure.bulkimport.constants.ClientEntityConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.PersonnelSheetPopulator;
+import org.apache.fineract.infrastructure.codes.data.CodeValueData;
+import org.apache.fineract.organisation.office.data.OfficeData;
+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 ClientEntityWorkbookPopulator extends AbstractWorkbookPopulator {
+
+    private OfficeSheetPopulator officeSheetPopulator;
+    private PersonnelSheetPopulator personnelSheetPopulator;
+    private List<CodeValueData> clientTypeCodeValues;
+    private List<CodeValueData> constitutionCodeValues;
+    private List<CodeValueData> clientClassificationCodeValues;
+    private List<CodeValueData> addressTypesCodeValues;
+    private List<CodeValueData> stateProvinceCodeValues;
+    private List<CodeValueData> countryCodeValues;
+    private List<CodeValueData> mainBusinesslineCodeValues;
+
+
+    public ClientEntityWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator,
+            PersonnelSheetPopulator personnelSheetPopulator,List<CodeValueData>clientTypeCodeValues,
+            List<CodeValueData>constitutionCodeValues,List<CodeValueData>mainBusinessline ,
+            List<CodeValueData>clientClassification,List<CodeValueData>addressTypesCodeValues,
+            List<CodeValueData>stateProvinceCodeValues,List<CodeValueData>countryCodeValues ) {
+        this.officeSheetPopulator = officeSheetPopulator;
+        this.personnelSheetPopulator = personnelSheetPopulator;
+        this.clientTypeCodeValues=clientTypeCodeValues;
+        this.constitutionCodeValues=constitutionCodeValues;
+        this.clientClassificationCodeValues=clientClassification;
+        this.addressTypesCodeValues=addressTypesCodeValues;
+        this.stateProvinceCodeValues=stateProvinceCodeValues;
+        this.countryCodeValues=countryCodeValues;
+        this.mainBusinesslineCodeValues=mainBusinessline;
+    }
+
+
+    @Override
+    public void populate(Workbook workbook,String dateFormat) {
+        Sheet clientSheet = workbook.createSheet(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME);
+        personnelSheetPopulator.populate(workbook,dateFormat);
+        officeSheetPopulator.populate(workbook,dateFormat);
+        setLayout(clientSheet);
+        setOfficeDateLookupTable(clientSheet, officeSheetPopulator.getOffices(),
+                ClientEntityConstants.RELATIONAL_OFFICE_NAME_COL, ClientEntityConstants.RELATIONAL_OFFICE_OPENING_DATE_COL,dateFormat);
+        setClientDataLookupTable(clientSheet);
+        setRules(clientSheet,dateFormat);
+    }
+
+    private void setClientDataLookupTable(Sheet clientSheet) {
+        int rowIndex=0;
+        for (CodeValueData clientTypeCodeValue:clientTypeCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_CLIENT_TYPES,row,clientTypeCodeValue.getName()+"-"+clientTypeCodeValue.getId());
+        }
+        rowIndex=0;
+        for (CodeValueData clientClassificationCodeValue:clientClassificationCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_CLIENT_CLASSIFICATION,row,
+                    clientClassificationCodeValue.getName()+"-"+clientClassificationCodeValue.getId());
+        }
+        rowIndex=0;
+        for (CodeValueData constitutionCodeValue:constitutionCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_CONSTITUTION_COL,row,
+                    constitutionCodeValue.getName()+"-"+constitutionCodeValue.getId());
+        }
+        rowIndex=0;
+        for (CodeValueData mainBusinessCodeValue:mainBusinesslineCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_MAIN_BUSINESS_LINE,row,
+                    mainBusinessCodeValue.getName()+"-"+mainBusinessCodeValue.getId());
+        }
+        rowIndex=0;
+        for (CodeValueData addressTypeCodeValue:addressTypesCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_ADDRESS_TYPE,row,
+                    addressTypeCodeValue.getName()+"-"+addressTypeCodeValue.getId());
+        }
+        rowIndex=0;
+        for (CodeValueData stateCodeValue:stateProvinceCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_STATE_PROVINCE,row,
+                    stateCodeValue.getName()+"-"+stateCodeValue.getId());
+        }
+        rowIndex=0;
+        for (CodeValueData countryCodeValue: countryCodeValues) {
+            Row row =clientSheet.getRow(++rowIndex);
+            if(row==null)
+                row=clientSheet.createRow(rowIndex);
+            writeString(ClientEntityConstants.LOOKUP_COUNTRY,row,
+                    countryCodeValue.getName()+"-"+countryCodeValue.getId());
+        }
+
+    }
+
+    private void setLayout(Sheet worksheet) {
+        Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX);
+        rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT);
+        worksheet.setColumnWidth(ClientEntityConstants.NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        writeString(ClientEntityConstants.NAME_COL, rowHeader, "Name*");
+        worksheet.setColumnWidth(ClientEntityConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.INCOPORATION_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.INCOPORATION_VALID_TILL_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.MOBILE_NO_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.CLIENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.CLIENT_CLASSIFICATION_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.INCOPORATION_NUMBER_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.MAIN_BUSINESS_LINE,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.CONSTITUTION_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.REMARKS_COL,TemplatePopulateImportConstants.LARGE_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.SUBMITTED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_ENABLED,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.STREET_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_LINE_1_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_LINE_2_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_LINE_3_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.CITY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.STATE_PROVINCE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.COUNTRY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.POSTAL_CODE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.IS_ACTIVE_ADDRESS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.WARNING_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+
+        worksheet.setColumnWidth(ClientEntityConstants.RELATIONAL_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_CONSTITUTION_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_CLIENT_TYPES,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_CLIENT_CLASSIFICATION,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_ADDRESS_TYPE,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_STATE_PROVINCE,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_COUNTRY,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+        worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_MAIN_BUSINESS_LINE,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+        writeString(ClientEntityConstants.NAME_COL,rowHeader,"Name");
+        writeString(ClientEntityConstants.OFFICE_NAME_COL, rowHeader, "Office Name*");
+        writeString(ClientEntityConstants.STAFF_NAME_COL, rowHeader, "Staff Name");
+        writeString(ClientEntityConstants.INCOPORATION_DATE_COL,rowHeader,"Incorporation Date");
+        writeString(ClientEntityConstants.INCOPORATION_VALID_TILL_COL,rowHeader,"Incorporation Validity Till Date");
+        writeString(ClientEntityConstants.MOBILE_NO_COL, rowHeader, "Mobile number");
+        writeString(ClientEntityConstants.CLIENT_TYPE_COL, rowHeader, "Client Type ");
+        writeString(ClientEntityConstants.CLIENT_CLASSIFICATION_COL, rowHeader, "Client Classification ");
+        writeString(ClientEntityConstants.INCOPORATION_NUMBER_COL,rowHeader,"Incorporation Number");
+        writeString(ClientEntityConstants.MAIN_BUSINESS_LINE,rowHeader,"Main Business Line");
+        writeString(ClientEntityConstants.CONSTITUTION_COL,rowHeader,"Constitution");
+        writeString(ClientEntityConstants.REMARKS_COL,rowHeader,"Remarks");
+        writeString(ClientEntityConstants.EXTERNAL_ID_COL, rowHeader, "External ID ");
+        writeString(ClientEntityConstants.SUBMITTED_ON_COL,rowHeader,"Submitted On Date");
+        writeString(ClientEntityConstants.ACTIVE_COL, rowHeader, "Active*");
+        writeString(ClientEntityConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date ");
+        writeString(ClientEntityConstants.ADDRESS_ENABLED,rowHeader,"Address Enabled ");
+        writeString(ClientEntityConstants.ADDRESS_TYPE_COL, rowHeader, "Address Type ");
+        writeString(ClientEntityConstants.STREET_COL, rowHeader, "Street  ");
+        writeString(ClientEntityConstants.ADDRESS_LINE_1_COL, rowHeader, "Address Line 1");
+        writeString(ClientEntityConstants.ADDRESS_LINE_2_COL, rowHeader, "Address Line 2");
+        writeString(ClientEntityConstants.ADDRESS_LINE_3_COL, rowHeader, "Address Line 3");
+        writeString(ClientEntityConstants.CITY_COL, rowHeader, "City");
+        writeString(ClientEntityConstants.STATE_PROVINCE_COL, rowHeader, "State/ Province");
+        writeString(ClientEntityConstants.COUNTRY_COL, rowHeader, "Country");
+        writeString(ClientEntityConstants.POSTAL_CODE_COL, rowHeader, "Postal Code");
+        writeString(ClientEntityConstants.IS_ACTIVE_ADDRESS_COL, rowHeader, "Is active Address ? ");
+        writeString(ClientEntityConstants.WARNING_COL, rowHeader, "All * marked fields are compulsory.");
+
+        writeString(ClientEntityConstants.RELATIONAL_OFFICE_NAME_COL, rowHeader, "Lookup office Name  ");
+        writeString(ClientEntityConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, rowHeader, "Lookup Office Opened Date ");
+        writeString(ClientEntityConstants.LOOKUP_CONSTITUTION_COL, rowHeader, "Lookup Constitution ");
+        writeString(ClientEntityConstants.LOOKUP_CLIENT_TYPES, rowHeader, "Lookup Client Types ");
+        writeString(ClientEntityConstants.LOOKUP_CLIENT_CLASSIFICATION, rowHeader, "Lookup Client Classification ");
+        writeString(ClientEntityConstants.LOOKUP_ADDRESS_TYPE, rowHeader, "Lookup AddressType ");
+        writeString(ClientEntityConstants.LOOKUP_STATE_PROVINCE, rowHeader, "Lookup State/Province ");
+        writeString(ClientEntityConstants.LOOKUP_COUNTRY, rowHeader, "Lookup Country ");
+        writeString(ClientEntityConstants.LOOKUP_MAIN_BUSINESS_LINE,rowHeader,"Lookup Business Line");
+
+
+    }
+
+    private void setRules(Sheet worksheet,String dateFormat) {
+        CellRangeAddressList officeNameRange = new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.OFFICE_NAME_COL,
+                ClientEntityConstants.OFFICE_NAME_COL);
+        CellRangeAddressList staffNameRange = new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.STAFF_NAME_COL, ClientEntityConstants.STAFF_NAME_COL);
+        CellRangeAddressList submittedOnDateRange = new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.SUBMITTED_ON_COL,ClientEntityConstants. SUBMITTED_ON_COL);
+        CellRangeAddressList dateRange = new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. ACTIVATION_DATE_COL,ClientEntityConstants. ACTIVATION_DATE_COL);
+        CellRangeAddressList activeRange = new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. ACTIVE_COL,ClientEntityConstants. ACTIVE_COL);
+        CellRangeAddressList clientTypeRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. CLIENT_TYPE_COL,ClientEntityConstants. CLIENT_TYPE_COL);
+        CellRangeAddressList constitutionRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.CONSTITUTION_COL,ClientEntityConstants. CONSTITUTION_COL);
+        CellRangeAddressList mainBusinessLineRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. MAIN_BUSINESS_LINE,ClientEntityConstants. MAIN_BUSINESS_LINE);
+        CellRangeAddressList clientClassificationRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.CLIENT_CLASSIFICATION_COL,
+                ClientEntityConstants.CLIENT_CLASSIFICATION_COL);
+        CellRangeAddressList enabledAddressRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.ADDRESS_ENABLED, ClientEntityConstants.ADDRESS_ENABLED);
+        CellRangeAddressList addressTypeRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.ADDRESS_TYPE_COL,ClientEntityConstants. ADDRESS_TYPE_COL);
+        CellRangeAddressList stateProvinceRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.STATE_PROVINCE_COL,ClientEntityConstants. STATE_PROVINCE_COL);
+        CellRangeAddressList countryRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. COUNTRY_COL,ClientEntityConstants. COUNTRY_COL);
+        CellRangeAddressList activeAddressRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. IS_ACTIVE_ADDRESS_COL,ClientEntityConstants. IS_ACTIVE_ADDRESS_COL);
+        CellRangeAddressList incorporateDateRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.INCOPORATION_DATE_COL,ClientEntityConstants.INCOPORATION_DATE_COL);
+        CellRangeAddressList incorporateDateTillRange=new CellRangeAddressList(1,
+                SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.INCOPORATION_VALID_TILL_COL,
+                ClientEntityConstants.INCOPORATION_VALID_TILL_COL);
+
+
+        DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);
+
+        List<OfficeData> offices = officeSheetPopulator.getOffices();
+        setNames(worksheet, offices);
+
+        DataValidationConstraint officeNameConstraint =
+                validationHelper.createFormulaListConstraint("Office");
+        DataValidationConstraint staffNameConstraint =
+                validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$B1))");
+        DataValidationConstraint submittedOnDateConstraint =
+                validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
+                        "=$O1" ,null, dateFormat);
+        DataValidationConstraint activationDateConstraint =
+                validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,
+                        "=VLOOKUP($B1,$AJ$2:$AK" + (offices.size() + 1) + ",2,FALSE)", "=TODAY()", dateFormat);
+        DataValidationConstraint activeConstraint =
+                validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+        DataValidationConstraint clientTypesConstraint =
+                validationHelper.createFormulaListConstraint("ClientTypes");
+        DataValidationConstraint constitutionConstraint =
+                validationHelper.createFormulaListConstraint("Constitution");
+        DataValidationConstraint mainBusinessLineConstraint =
+                validationHelper.createFormulaListConstraint("MainBusinessLine");
+        DataValidationConstraint clientClassificationConstraint =
+                validationHelper.createFormulaListConstraint("ClientClassification");
+        DataValidationConstraint enabledAddressConstraint =
+                validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+        DataValidationConstraint addressTypeConstraint =
+                validationHelper.createFormulaListConstraint("AddressType");
+        DataValidationConstraint stateProvinceConstraint =
+                validationHelper.createFormulaListConstraint("StateProvince");
+        DataValidationConstraint countryConstraint =
+                validationHelper.createFormulaListConstraint("Country");
+        DataValidationConstraint activeAddressConstraint =
+                validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+        DataValidationConstraint incorpDateConstraint=
+                validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
+                        "=TODAY()",null,dateFormat);
+        DataValidationConstraint incorpDateTillConstraint=
+                validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,
+                        "=TODAY()",null,dateFormat);
+
+
+        DataValidation officeValidation =
+                validationHelper.createValidation(officeNameConstraint, officeNameRange);
+        DataValidation staffValidation =
+                validationHelper.createValidation(staffNameConstraint, staffNameRange);
+        DataValidation submittedOnDateValidation =
+                validationHelper.createValidation(submittedOnDateConstraint, submittedOnDateRange);
+        DataValidation activationDateValidation =
+                validationHelper.createValidation(activationDateConstraint, dateRange);
+        DataValidation activeValidation =
+                validationHelper.createValidation(activeConstraint, activeRange);
+        DataValidation clientTypeValidation =
+                validationHelper.createValidation(clientTypesConstraint, clientTypeRange);
+        DataValidation constitutionValidation =
+                validationHelper.createValidation(constitutionConstraint,constitutionRange);
+        DataValidation mainBusinessLineValidation =
+                validationHelper.createValidation(mainBusinessLineConstraint,mainBusinessLineRange);
+        DataValidation clientClassificationValidation =
+                validationHelper.createValidation(clientClassificationConstraint, clientClassificationRange);
+        DataValidation enabledAddressValidation=
+                validationHelper.createValidation(enabledAddressConstraint,enabledAddressRange);
+        DataValidation addressTypeValidation =
+                validationHelper.createValidation(addressTypeConstraint, addressTypeRange);
+        DataValidation stateProvinceValidation =
+                validationHelper.createValidation(stateProvinceConstraint, stateProvinceRange);
+        DataValidation countryValidation =
+                validationHelper.createValidation(countryConstraint, countryRange);
+        DataValidation activeAddressValidation =
+                validationHelper.createValidation(activeAddressConstraint,activeAddressRange);
+        DataValidation incorporateDateValidation=validationHelper.createValidation(incorpDateConstraint,incorporateDateRange);
+        DataValidation incorporateDateTillValidation=validationHelper.createValidation(incorpDateTillConstraint,incorporateDateTillRange);
+
+        worksheet.addValidationData(activeValidation);
+        worksheet.addValidationData(officeValidation);
+        worksheet.addValidationData(staffValidation);
+        worksheet.addValidationData(activationDateValidation);
+        worksheet.addValidationData(submittedOnDateValidation);
+        worksheet.addValidationData(clientTypeValidation);
+        worksheet.addValidationData(constitutionValidation);
+        worksheet.addValidationData(mainBusinessLineValidation);
+        worksheet.addValidationData(clientClassificationValidation);
+        worksheet.addValidationData(enabledAddressValidation);
+        worksheet.addValidationData(addressTypeValidation);
+        worksheet.addValidationData(stateProvinceValidation);
+        worksheet.addValidationData(countryValidation);
+        worksheet.addValidationData(activeAddressValidation);
+        worksheet.addValidationData(incorporateDateValidation);
+        worksheet.addValidationData(incorporateDateTillValidation);
+    }
+
+    private void setNames(Sheet worksheet, List<OfficeData> offices) {
+        Workbook clientWorkbook = worksheet.getWorkbook();
+        Name officeGroup = clientWorkbook.createName();
+        officeGroup.setNameName("Office");
+        officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1));
+
+        Name clientTypeGroup = clientWorkbook.createName();
+        clientTypeGroup.setNameName("ClientTypes");
+        clientTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AN$2:$AN$" +
+                (clientTypeCodeValues.size() + 1));
+
+        Name constitutionGroup = clientWorkbook.createName();
+        constitutionGroup.setNameName("Constitution");
+        constitutionGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AL$2:$AL$" +
+                (constitutionCodeValues.size() + 1));
+
+        Name mainBusinessLineGroup = clientWorkbook.createName();
+        mainBusinessLineGroup.setNameName("MainBusinessLine");
+        mainBusinessLineGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AR$2:$AR$" +
+                (mainBusinesslineCodeValues.size() + 1));
+
+        Name clientClassficationGroup = clientWorkbook.createName();
+        clientClassficationGroup.setNameName("ClientClassification");
+        clientClassficationGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AM$2:$AM$" +
+                (clientClassificationCodeValues.size() + 1));
+
+        Name addressTypeGroup = clientWorkbook.createName();
+        addressTypeGroup.setNameName("AddressType");
+        addressTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+
+                "!$AO$2:$AO$" + (addressTypesCodeValues.size() + 1));
+
+        Name stateProvinceGroup = clientWorkbook.createName();
+        stateProvinceGroup.setNameName("StateProvince");
+        stateProvinceGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+
+                "!$AP$2:$AP$" + (stateProvinceCodeValues.size() + 1));
+
+        Name countryGroup = clientWorkbook.createName();
+        countryGroup.setNameName("Country");
+        countryGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AQ$2:$AQ$" +
+                (countryCodeValues.size() + 1));
+
+        for (Integer i = 0; i < offices.size(); i++) {
+            Integer[] officeNameToBeginEndIndexesOfStaff =
+                    personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i);
+            if (officeNameToBeginEndIndexesOfStaff != null) {
+                Name name = clientWorkbook.createName();
+                name.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_"));
+                name.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" +
+                        officeNameToBeginEndIndexesOfStaff[0] + ":$B$"
+                        + officeNameToBeginEndIndexesOfStaff[1]);
+            }
+        }
+    }
+
+}

http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java
new file mode 100644
index 0000000..69d1119
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java
@@ -0,0 +1,370 @@
+/**
+ * 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.client;
+
+import org.apache.fineract.infrastructure.bulkimport.constants.ClientPersonConstants;
+import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator;
+import org.apache.fineract.infrastructure.bulkimport.populator.PersonnelSheetPopulator;
+import org.apache.fineract.infrastructure.codes.data.CodeValueData;
+import org.apache.fineract.organisation.office.data.OfficeData;
+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 ClientPersonWorkbookPopulator extends AbstractWorkbookPopulator {
+
+  private OfficeSheetPopulator officeSheetPopulator;
+  private PersonnelSheetPopulator personnelSheetPopulator;
+  private List<CodeValueData>clientTypeCodeValues;
+  private List<CodeValueData>genderCodeValues;
+  private List<CodeValueData>clientClassificationCodeValues;
+  private List<CodeValueData>addressTypesCodeValues;
+  private List<CodeValueData>stateProvinceCodeValues;
+  private List<CodeValueData>countryCodeValues;
+
+
+  public ClientPersonWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator,
+      PersonnelSheetPopulator personnelSheetPopulator,List<CodeValueData>clientTypeCodeValues,
+          List<CodeValueData>genderCodeValues, List<CodeValueData>clientClassification,List<CodeValueData>addressTypesCodeValues,
+          List<CodeValueData>stateProvinceCodeValues,List<CodeValueData>countryCodeValues ) {
+    this.officeSheetPopulator = officeSheetPopulator;
+    this.personnelSheetPopulator = personnelSheetPopulator;
+    this.clientTypeCodeValues=clientTypeCodeValues;
+    this.genderCodeValues=genderCodeValues;
+    this.clientClassificationCodeValues=clientClassification;
+    this.addressTypesCodeValues=addressTypesCodeValues;
+    this.stateProvinceCodeValues=stateProvinceCodeValues;
+    this.countryCodeValues=countryCodeValues;
+  }
+
+
+  @Override
+  public void populate(Workbook workbook,String dateFormat) {
+    Sheet clientSheet = workbook.createSheet(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME);
+    personnelSheetPopulator.populate(workbook,dateFormat);
+    officeSheetPopulator.populate(workbook,dateFormat);
+    setLayout(clientSheet);
+    setOfficeDateLookupTable(clientSheet, officeSheetPopulator.getOffices(),
+            ClientPersonConstants.RELATIONAL_OFFICE_NAME_COL, ClientPersonConstants.RELATIONAL_OFFICE_OPENING_DATE_COL,dateFormat);
+    setClientDataLookupTable(clientSheet);
+    setRules(clientSheet,dateFormat);
+  }
+
+  private void setClientDataLookupTable(Sheet clientSheet) {
+    int rowIndex=0;
+    for (CodeValueData clientTypeCodeValue:clientTypeCodeValues) {
+      Row row =clientSheet.getRow(++rowIndex);
+      if(row==null)
+        row=clientSheet.createRow(rowIndex);
+      writeString(ClientPersonConstants.LOOKUP_CLIENT_TYPES_COL,row,clientTypeCodeValue.getName()+
+              "-"+clientTypeCodeValue.getId());
+    }
+    rowIndex=0;
+    for (CodeValueData clientClassificationCodeValue:clientClassificationCodeValues) {
+      Row row =clientSheet.getRow(++rowIndex);
+      if(row==null)
+        row=clientSheet.createRow(rowIndex);
+      writeString(ClientPersonConstants.LOOKUP_CLIENT_CLASSIFICATION_COL,row,
+              clientClassificationCodeValue.getName()+"-"+clientClassificationCodeValue.getId());
+    }
+    rowIndex=0;
+    for (CodeValueData genderCodeValue:genderCodeValues) {
+      Row row =clientSheet.getRow(++rowIndex);
+      if(row==null)
+        row=clientSheet.createRow(rowIndex);
+      writeString(ClientPersonConstants.LOOKUP_GENDER_COL,row,genderCodeValue.getName()+"-"+genderCodeValue.getId());
+    }
+    rowIndex=0;
+    for (CodeValueData addressTypeCodeValue:addressTypesCodeValues) {
+      Row row =clientSheet.getRow(++rowIndex);
+      if(row==null)
+        row=clientSheet.createRow(rowIndex);
+      writeString(ClientPersonConstants.LOOKUP_ADDRESS_TYPE_COL,row,
+              addressTypeCodeValue.getName()+"-"+addressTypeCodeValue.getId());
+    }
+    rowIndex=0;
+    for (CodeValueData stateCodeValue:stateProvinceCodeValues) {
+      Row row =clientSheet.getRow(++rowIndex);
+      if(row==null)
+        row=clientSheet.createRow(rowIndex);
+      writeString(ClientPersonConstants.LOOKUP_STATE_PROVINCE_COL,row,stateCodeValue.getName()+"-"+stateCodeValue.getId());
+    }
+    rowIndex=0;
+    for (CodeValueData countryCodeValue: countryCodeValues) {
+      Row row =clientSheet.getRow(++rowIndex);
+      if(row==null)
+        row=clientSheet.createRow(rowIndex);
+      writeString(ClientPersonConstants.LOOKUP_COUNTRY_COL,row,countryCodeValue.getName()+"-"+countryCodeValue.getId());
+    }
+
+  }
+
+  private void setLayout(Sheet worksheet) {
+    Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX);
+    rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT);
+    worksheet.setColumnWidth(ClientPersonConstants.FIRST_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LAST_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.MIDDLE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    writeString(ClientPersonConstants.FIRST_NAME_COL, rowHeader, "First Name*");
+    writeString(ClientPersonConstants.LAST_NAME_COL, rowHeader, "Last Name*");
+    writeString(ClientPersonConstants.MIDDLE_NAME_COL, rowHeader, "Middle Name");
+    worksheet.setColumnWidth(ClientPersonConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.SUBMITTED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.MOBILE_NO_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.DOB_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.CLIENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.GENDER_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.CLIENT_CLASSIFICATION_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.IS_STAFF_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_ENABLED_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.STREET_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_LINE_1_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_LINE_2_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_LINE_3_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.CITY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.STATE_PROVINCE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.COUNTRY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.POSTAL_CODE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.IS_ACTIVE_ADDRESS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.WARNING_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+
+    worksheet.setColumnWidth(ClientPersonConstants.RELATIONAL_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_GENDER_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_CLIENT_TYPES_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_CLIENT_CLASSIFICATION_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_ADDRESS_TYPE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_STATE_PROVINCE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_COUNTRY_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE);
+    writeString(ClientPersonConstants.OFFICE_NAME_COL, rowHeader, "Office Name*");
+    writeString(ClientPersonConstants.STAFF_NAME_COL, rowHeader, "Staff Name");
+    writeString(ClientPersonConstants.EXTERNAL_ID_COL, rowHeader, "External ID ");
+    writeString(ClientPersonConstants.SUBMITTED_ON_COL,rowHeader,"Submitted On Date");
+    writeString(ClientPersonConstants.ACTIVATION_DATE_COL, rowHeader, "Activation date");
+    writeString(ClientPersonConstants.ACTIVE_COL, rowHeader, "Active*");
+    writeString(ClientPersonConstants.MOBILE_NO_COL, rowHeader, "Mobile number");
+    writeString(ClientPersonConstants.DOB_COL, rowHeader, "Date of Birth ");
+    writeString(ClientPersonConstants.CLIENT_TYPE_COL, rowHeader, "Client Type ");
+    writeString(ClientPersonConstants.IS_STAFF_COL, rowHeader, "Is a staff memeber ");
+    writeString(ClientPersonConstants.GENDER_COL, rowHeader, "Gender ");
+    writeString(ClientPersonConstants.ADDRESS_ENABLED_COL,rowHeader,"Address Enabled *");
+    writeString(ClientPersonConstants.CLIENT_CLASSIFICATION_COL, rowHeader, "Client Classification ");
+    writeString(ClientPersonConstants.ADDRESS_TYPE_COL, rowHeader, "Address Type ");
+    writeString(ClientPersonConstants.STREET_COL, rowHeader, "Street  ");
+    writeString(ClientPersonConstants.ADDRESS_LINE_1_COL, rowHeader, "Address Line 1");
+    writeString(ClientPersonConstants.ADDRESS_LINE_2_COL, rowHeader, "Address Line 2");
+    writeString(ClientPersonConstants.ADDRESS_LINE_3_COL, rowHeader, "Address Line 3 ");
+    writeString(ClientPersonConstants.CITY_COL, rowHeader, "City ");
+    writeString(ClientPersonConstants.STATE_PROVINCE_COL, rowHeader, "State/ Province ");
+    writeString(ClientPersonConstants.COUNTRY_COL, rowHeader, "Country ");
+    writeString(ClientPersonConstants.POSTAL_CODE_COL, rowHeader, "Postal Code ");
+    writeString(ClientPersonConstants.IS_ACTIVE_ADDRESS_COL, rowHeader, "Is active Address ? ");
+    writeString(ClientPersonConstants.WARNING_COL, rowHeader, "All * marked fields are compulsory.");
+
+    writeString(ClientPersonConstants.RELATIONAL_OFFICE_NAME_COL, rowHeader, "Lookup office Name  ");
+    writeString(ClientPersonConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, rowHeader, "Lookup Office Opened Date ");
+    writeString(ClientPersonConstants.LOOKUP_GENDER_COL, rowHeader, "Lookup Gender ");
+    writeString(ClientPersonConstants.LOOKUP_CLIENT_TYPES_COL, rowHeader, "Lookup Client Types ");
+    writeString(ClientPersonConstants.LOOKUP_CLIENT_CLASSIFICATION_COL, rowHeader, "Lookup Client Classification ");
+    writeString(ClientPersonConstants.LOOKUP_ADDRESS_TYPE_COL, rowHeader, "Lookup AddressType ");
+    writeString(ClientPersonConstants.LOOKUP_STATE_PROVINCE_COL, rowHeader, "Lookup State/Province ");
+    writeString(ClientPersonConstants.LOOKUP_COUNTRY_COL, rowHeader, "Lookup Country ");
+
+
+  }
+
+  private void setRules(Sheet worksheet,String dateformat) {
+    CellRangeAddressList officeNameRange = new CellRangeAddressList(1,
+        SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.OFFICE_NAME_COL, ClientPersonConstants.OFFICE_NAME_COL);
+    CellRangeAddressList staffNameRange = new CellRangeAddressList(1,
+        SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. STAFF_NAME_COL,ClientPersonConstants. STAFF_NAME_COL);
+    CellRangeAddressList submittedOnDateRange = new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. SUBMITTED_ON_COL, ClientPersonConstants.SUBMITTED_ON_COL);
+    CellRangeAddressList activationDateRange = new CellRangeAddressList(1,
+        SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.ACTIVATION_DATE_COL, ClientPersonConstants.ACTIVATION_DATE_COL);
+    CellRangeAddressList activeRange = new CellRangeAddressList(1,
+        SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.ACTIVE_COL,ClientPersonConstants. ACTIVE_COL);
+    CellRangeAddressList clientTypeRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. CLIENT_TYPE_COL,ClientPersonConstants. CLIENT_TYPE_COL);
+    CellRangeAddressList dobRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. DOB_COL,ClientPersonConstants. DOB_COL);
+    CellRangeAddressList isStaffRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. IS_STAFF_COL,ClientPersonConstants. IS_STAFF_COL);
+    CellRangeAddressList genderRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.GENDER_COL,ClientPersonConstants. GENDER_COL);
+    CellRangeAddressList clientClassificationRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.CLIENT_CLASSIFICATION_COL, ClientPersonConstants.CLIENT_CLASSIFICATION_COL);
+    CellRangeAddressList enabledAddressRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.ADDRESS_ENABLED_COL, ClientPersonConstants.ADDRESS_ENABLED_COL);
+    CellRangeAddressList addressTypeRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. ADDRESS_TYPE_COL, ClientPersonConstants.ADDRESS_TYPE_COL);
+    CellRangeAddressList stateProvinceRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. STATE_PROVINCE_COL, ClientPersonConstants.STATE_PROVINCE_COL);
+    CellRangeAddressList countryRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.COUNTRY_COL, ClientPersonConstants.COUNTRY_COL);
+    CellRangeAddressList activeAddressRange=new CellRangeAddressList(1,
+            SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. IS_ACTIVE_ADDRESS_COL,ClientPersonConstants. IS_ACTIVE_ADDRESS_COL);
+
+
+    DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);
+
+    List<OfficeData> offices = officeSheetPopulator.getOffices();
+    setNames(worksheet, offices);
+
+    DataValidationConstraint officeNameConstraint =
+        validationHelper.createFormulaListConstraint("Office");
+    DataValidationConstraint staffNameConstraint =
+        validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$D1))");
+    DataValidationConstraint submittedOnDateConstraint =
+            validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
+                     "=$I1" ,null,dateformat);
+    DataValidationConstraint activationDateConstraint =
+        validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,
+            "=VLOOKUP($D1,$AJ$2:$AK" + (offices.size() + 1) + ",2,FALSE)", "=TODAY()", dateformat);
+    DataValidationConstraint dobDateConstraint =
+            validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
+                    "=TODAY()",null, dateformat);
+    DataValidationConstraint activeConstraint =
+        validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+    DataValidationConstraint clientTypesConstraint =
+            validationHelper.createFormulaListConstraint("ClientTypes");
+    DataValidationConstraint isStaffConstraint =
+            validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+    DataValidationConstraint genderConstraint =
+            validationHelper.createFormulaListConstraint("Gender");
+    DataValidationConstraint clientClassificationConstraint =
+            validationHelper.createFormulaListConstraint("ClientClassification");
+    DataValidationConstraint enabledAddressConstraint =
+            validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+    DataValidationConstraint addressTypeConstraint =
+            validationHelper.createFormulaListConstraint("AddressType");
+    DataValidationConstraint stateProvinceConstraint =
+            validationHelper.createFormulaListConstraint("StateProvince");
+    DataValidationConstraint countryConstraint =
+            validationHelper.createFormulaListConstraint("Country");
+    DataValidationConstraint activeAddressConstraint =
+            validationHelper.createExplicitListConstraint(new String[] {"True", "False"});
+
+    DataValidation officeValidation =
+        validationHelper.createValidation(officeNameConstraint, officeNameRange);
+    DataValidation staffValidation =
+        validationHelper.createValidation(staffNameConstraint, staffNameRange);
+    DataValidation submittedOnDateValidation =
+            validationHelper.createValidation(submittedOnDateConstraint, submittedOnDateRange);
+    DataValidation activationDateValidation =
+        validationHelper.createValidation(activationDateConstraint, activationDateRange);
+    DataValidation dobDateValidation =
+            validationHelper.createValidation(dobDateConstraint, dobRange);
+    DataValidation activeValidation =
+        validationHelper.createValidation(activeConstraint, activeRange);
+    DataValidation clientTypeValidation =
+            validationHelper.createValidation(clientTypesConstraint, clientTypeRange);
+    DataValidation isStaffValidation =
+            validationHelper.createValidation(isStaffConstraint, isStaffRange);
+    DataValidation genderValidation =
+            validationHelper.createValidation(genderConstraint, genderRange);
+    DataValidation clientClassificationValidation =
+            validationHelper.createValidation(clientClassificationConstraint, clientClassificationRange);
+    DataValidation enabledAddressValidation=
+            validationHelper.createValidation(enabledAddressConstraint,enabledAddressRange);
+    DataValidation addressTypeValidation =
+            validationHelper.createValidation(addressTypeConstraint, addressTypeRange);
+    DataValidation stateProvinceValidation =
+            validationHelper.createValidation(stateProvinceConstraint, stateProvinceRange);
+    DataValidation countryValidation =
+            validationHelper.createValidation(countryConstraint, countryRange);
+    DataValidation activeAddressValidation =
+            validationHelper.createValidation(activeAddressConstraint,activeAddressRange);
+
+    worksheet.addValidationData(activeValidation);
+    worksheet.addValidationData(officeValidation);
+    worksheet.addValidationData(staffValidation);
+    worksheet.addValidationData(activationDateValidation);
+    worksheet.addValidationData(submittedOnDateValidation);
+    worksheet.addValidationData(dobDateValidation);
+    worksheet.addValidationData(clientTypeValidation);
+    worksheet.addValidationData(isStaffValidation);
+    worksheet.addValidationData(genderValidation);
+    worksheet.addValidationData(clientClassificationValidation);
+    worksheet.addValidationData(enabledAddressValidation);
+    worksheet.addValidationData(addressTypeValidation);
+    worksheet.addValidationData(stateProvinceValidation);
+    worksheet.addValidationData(countryValidation);
+    worksheet.addValidationData(activeAddressValidation);
+  }
+
+  private void setNames(Sheet worksheet, List<OfficeData> offices) {
+    Workbook clientWorkbook = worksheet.getWorkbook();
+    Name officeGroup = clientWorkbook.createName();
+    officeGroup.setNameName("Office");
+    officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1));
+
+    Name clientTypeGroup = clientWorkbook.createName();
+    clientTypeGroup.setNameName("ClientTypes");
+    clientTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AN$2:$AN$" +
+            (clientTypeCodeValues.size() + 1));
+
+    Name genderGroup = clientWorkbook.createName();
+    genderGroup.setNameName("Gender");
+    genderGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AL$2:$AL$" + (genderCodeValues.size() + 1));
+
+    Name clientClassficationGroup = clientWorkbook.createName();
+    clientClassficationGroup.setNameName("ClientClassification");
+    clientClassficationGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AM$2:$AM$" +
+            (clientClassificationCodeValues.size() + 1));
+
+    Name addressTypeGroup = clientWorkbook.createName();
+    addressTypeGroup.setNameName("AddressType");
+    addressTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AO$2:$AO$" +
+            (addressTypesCodeValues.size() + 1));
+
+    Name stateProvinceGroup = clientWorkbook.createName();
+    stateProvinceGroup.setNameName("StateProvince");
+    stateProvinceGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AP$2:$AP$" +
+            (stateProvinceCodeValues.size() + 1));
+
+    Name countryGroup = clientWorkbook.createName();
+    countryGroup.setNameName("Country");
+    countryGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AQ$2:$AQ$" +
+            (countryCodeValues.size() + 1));
+    
+    for (Integer i = 0; i < offices.size(); i++) {
+      Integer[] officeNameToBeginEndIndexesOfStaff =
+          personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i);
+      if (officeNameToBeginEndIndexesOfStaff != null) {
+        Name name = clientWorkbook.createName();
+        name.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_"));
+        name.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" +
+                officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + officeNameToBeginEndIndexesOfStaff[1]);
+      }
+    }
+  }
+
+}
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java
new file mode 100644
index 0000000..9a81574
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java
@@ -0,0 +1,52 @@
+/**
+ * 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.comparator;
+
+import org.apache.fineract.portfolio.loanaccount.data.LoanAccountData;
+
+import java.util.Comparator;
+
+/**
+ * Sorting the loan values based on loan status giving priority to active loans
+ * */
+
+public class LoanComparatorByStatusActive implements Comparator<LoanAccountData> {
+
+    @Override
+    public int compare(LoanAccountData  o1, LoanAccountData o2) {
+
+        boolean isData1StatusActive = o1.getStatusStringValue().equals("Active");
+        boolean isData2StatusActive = o2.getStatusStringValue().equals("Active");
+
+        // if both status active, these have the same rank
+        if (isData1StatusActive && isData2StatusActive){
+            return 0;
+        }
+
+        if (isData1StatusActive){
+            return -1;
+        }
+
+        if (isData2StatusActive){
+            return 1;
+        }
+        // if no status active, these have the same rank
+        return 0;
+    }
+}
\ No newline at end of file


Mime
View raw message