Java tutorial
package com.sandstone_tech.lendfastselenium2.util; import java.io.File; import java.io.IOException; import java.util.List; import java.util.Map; import org.apache.commons.io.FileUtils; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.sandstone_tech.lendfastselenium2.model.CurrentAddress; import com.sandstone_tech.lendfastselenium2.model.EmploymentDetails; import com.sandstone_tech.lendfastselenium2.model.OriginationFormModel; import com.sandstone_tech.lendfastselenium2.model.PersonalDetailsModel; import com.sandstone_tech.lendfastselenium2.model.PreviousEmploymentDetails; import com.sandstone_tech.lendfastselenium2.model.PropertyContract; import com.sandstone_tech.lendfastselenium2.model.PropertyPurpose; import com.sandstone_tech.lendfastselenium2.model.PurchaseProperty; import com.sandstone_tech.lendfastselenium2.model.SignedDeclaration; import com.sandstone_tech.lendfastselenium2.model.TestDataModel; /** * * * @author andrewvillanueva * */ public abstract class PoiFileReader { /** * Extracts the content of the excel file to be converted as * {@link TestDataModel} object usable by the data provider * * @param fileName * @return */ protected Object[][] readExcelFileAsTDM(String fileName) { try { XSSFWorkbook wb = new XSSFWorkbook(FileUtils.openInputStream(new File(fileName))); // CONFIGURATION // XSSFSheet sheet = wb.getSheetAt(0); // DATA XSSFSheet dataSheet = wb.getSheetAt(1); if (dataSheet != null && dataSheet.getPhysicalNumberOfRows() > 1) { Object[][] testData = new Object[dataSheet.getPhysicalNumberOfRows() - 2][1]; for (int rowCount = 2; rowCount < dataSheet.getPhysicalNumberOfRows(); rowCount++) { XSSFRow row = dataSheet.getRow(rowCount); TestDataModel testDataModel = new TestDataModel(); testDataModel.setUsername(getStringValue(row.getCell(0))); testDataModel.setPassword(getStringValue(row.getCell(1))); testDataModel.setApplicationType(getStringValue(row.getCell(2))); testDataModel.setOrginationFormModel(this.getOrigination(row)); testDataModel.setPersonalDetailsModel(this.getPersonalDetail(row)); testDataModel.setSignedDeclaration(this.getSignedDeclaration(row)); testDataModel.setCurrentAddress(this.getCurrentAddress(row)); testDataModel.setEmploymentDetails(this.getEmploymentDetails(row)); testDataModel.setPreviousEmploymentDetails(this.getPreviousEmploymentDetails(row)); testDataModel.setPurchaseProperty( new PurchaseProperty(this.getPropertyContract(row), this.getPropertyPurpose(row))); testData[rowCount - 2][0] = testDataModel; } wb.close(); return testData; } wb.close(); } catch (IOException e) { e.printStackTrace(); } return null; } protected boolean isFileValid(File excelDataFile) { return excelDataFile.exists(); } private String getStringValue(XSSFCell cell) { if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: return cell.toString(); case XSSFCell.CELL_TYPE_NUMERIC: return this.formatDouble(cell.getNumericCellValue()); case XSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case XSSFCell.CELL_TYPE_BLANK: return ""; default: return ""; } } return ""; } private String formatDouble(double d) { if (d == (long) d) return String.format("%d", (long) d); else return String.format("%s", d); } protected List<String> readExcelFileAsList(String fileName) { // TODO : add implementations here... return null; } protected Map<String, Object> readExcelFileAsMap(String fileName) { // TODO : add implementations here... return null; } // EXTRACT DATA FROM EXCEL /** * * * @param row * @return */ private OriginationFormModel getOrigination(XSSFRow row) { OriginationFormModel origination = new OriginationFormModel(); origination.setOriginationChannel(getStringValue(row.getCell(3))); origination.setOriginationSearchBranch(getStringValue(row.getCell(4))); origination.setOriginationBranch(getStringValue(row.getCell(5))); origination.setOriginationSalesUsername(getStringValue(row.getCell(6))); origination.setOriginationSalesPerson(getStringValue(row.getCell(7))); return origination; } /** * * * @param row * @return */ private SignedDeclaration getSignedDeclaration(XSSFRow row) { SignedDeclaration signedDeclaration = new SignedDeclaration(); signedDeclaration.setPartySignedDeclaration(getStringValue(row.getCell(19))); signedDeclaration.setIdType(getStringValue(row.getCell(20))); signedDeclaration.setLicenseState(getStringValue(row.getCell(21))); signedDeclaration.setPartyIdNo(getStringValue(row.getCell(22))); return signedDeclaration; } /** * * * @param row * @return */ private PersonalDetailsModel getPersonalDetail(XSSFRow row) { PersonalDetailsModel personalDetails = new PersonalDetailsModel(); personalDetails.setIsExistingCustomer(getStringValue(row.getCell(8))); personalDetails.setCustomerTitle(getStringValue(row.getCell(9))); personalDetails.setFirstname(getStringValue(row.getCell(10))); personalDetails.setSurname(getStringValue(row.getCell(11))); personalDetails.setBirthday(getStringValue(row.getCell(12))); personalDetails.setMaritalStatus(getStringValue(row.getCell(13))); personalDetails.setSpouse(getStringValue(row.getCell(14))); personalDetails.setGender(getStringValue(row.getCell(15))); personalDetails.setPrefContactMethod(getStringValue(row.getCell(16))); personalDetails.setHomePhoneNo(getStringValue(row.getCell(17))); personalDetails.setHasDependent(getStringValue(row.getCell(18))); return personalDetails; } /** * * * @param row * @return */ private CurrentAddress getCurrentAddress(XSSFRow row) { CurrentAddress currentAddress = new CurrentAddress(); currentAddress.setResidentialStatus(getStringValue(row.getCell(23))); currentAddress.setResidencyStatus(getStringValue(row.getCell(24))); currentAddress.setNumber(getStringValue(row.getCell(25))); currentAddress.setStreetName(getStringValue(row.getCell(26))); currentAddress.setStreetType(getStringValue(row.getCell(27))); currentAddress.setStreetSuffix(getStringValue(row.getCell(28))); currentAddress.setSuburbName(getStringValue(row.getCell(29))); currentAddress.setState(getStringValue(row.getCell(30))); currentAddress.setPostcode(getStringValue(row.getCell(31))); currentAddress.setFullAddress(getStringValue(row.getCell(32))); currentAddress.setYears(getStringValue(row.getCell(33))); currentAddress.setMonths(getStringValue(row.getCell(34))); currentAddress.setSameAsResidential(getStringValue(row.getCell(35))); return currentAddress; } /** * * * @param row * @return */ private EmploymentDetails getEmploymentDetails(XSSFRow row) { EmploymentDetails employmentDetails = new EmploymentDetails(); employmentDetails.setOccupationCategory(getStringValue(row.getCell(36))); employmentDetails.setOccupationSubtype(getStringValue(row.getCell(37))); employmentDetails.setStatus(getStringValue(row.getCell(38))); employmentDetails.setEmployerType(getStringValue(row.getCell(39))); employmentDetails.setEmployerName(getStringValue(row.getCell(40))); employmentDetails.setSearchAddress(getStringValue(row.getCell(41))); employmentDetails.setAddress(getStringValue(row.getCell(42))); employmentDetails.setAddressVerify(getStringValue(row.getCell(43))); employmentDetails.setProbationaryPeriod(getStringValue(row.getCell(44))); employmentDetails.setIncomeMethod(getStringValue(row.getCell(45))); employmentDetails.setIncome(getStringValue(row.getCell(46))); employmentDetails.setIncomeStatus(getStringValue(row.getCell(47))); employmentDetails.setFrequency(getStringValue(row.getCell(48))); employmentDetails.setBonus(getStringValue(row.getCell(49))); employmentDetails.setCommisionBonusIncome(getStringValue(row.getCell(50))); employmentDetails.setYears(getStringValue(row.getCell(51))); employmentDetails.setMonths(getStringValue(row.getCell(52))); employmentDetails.setEmployeePhoneNo(getStringValue(row.getCell(53))); return employmentDetails; } /** * * * @param row * @return */ private PreviousEmploymentDetails getPreviousEmploymentDetails(XSSFRow row) { PreviousEmploymentDetails details = new PreviousEmploymentDetails(); details.setCategory(getStringValue(row.getCell(55))); details.setSubType(getStringValue(row.getCell(56))); details.setYears(getStringValue(row.getCell(57))); details.setMonths(getStringValue(row.getCell(58))); return details; } /** * Extracts {@link PropertyContract} data from excel * * @param row * @return */ private PropertyContract getPropertyContract(XSSFRow row) { PropertyContract contract = new PropertyContract(); contract.setForPreApproval(getStringValue(row.getCell(60))); contract.setForSecurity(getStringValue(row.getCell(61))); ; contract.setPropertyFound(getStringValue(row.getCell(62))); contract.setAddress(getStringValue(row.getCell(63))); contract.setAddressSelect(getStringValue(row.getCell(64))); contract.setAddressVerify(getStringValue(row.getCell(65))); contract.setEnteredContractSale(getStringValue(row.getCell(66))); contract.setHasCopyOfContract(getStringValue(row.getCell(67))); contract.setHasSignedByAll(getStringValue(row.getCell(68))); contract.setVendorName(getStringValue(row.getCell(69))); return contract; } /** * Extracts {@link PropertyPurpose} data from excel * * @param row * @return */ private PropertyPurpose getPropertyPurpose(XSSFRow row) { PropertyPurpose purpose = new PropertyPurpose(); purpose.setPurposeType(getStringValue(row.getCell(70))); purpose.setTypeANZ(getStringValue(row.getCell(71))); purpose.setPropertyType(getStringValue(row.getCell(72))); purpose.setZoning(getStringValue(row.getCell(73))); purpose.setQtyBedrooms(getStringValue(row.getCell(74))); purpose.setQtyBathrooms(getStringValue(row.getCell(75))); purpose.setPrice(getStringValue(row.getCell(76))); purpose.setForInvestment(getStringValue(row.getCell(77))); purpose.setPrincipalResidenceForAny(getStringValue(row.getCell(78))); purpose.setLandArea(getStringValue(row.getCell(79))); purpose.setMortgageFirst(getStringValue(row.getCell(80))); return purpose; } }