Examples with HSSFWorkbook org.apache.poi.hssf.usermodel.HSSFWorkbook used on opensource projects

Example 1 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project head by mifos.

the class XlsClientsImporter method parse.

public ParsedClientsDto parse(final InputStream input) {
    final List<ValueListElement> buisnessActivitiesList = customerDao.retrieveBusinessActivities();
    final List<ValueListElement> gendersList = customerDao.retrieveGenders();
    final List<ValueListElement> citizenshipsList = customerDao.retrieveCitizenship();
    final List<ValueListElement> ethinicitiesList = customerDao.retrieveEthnicity();
    final List<ValueListElement> educationLevelsList = customerDao.retrieveEducationLevels();
    final List<ValueListElement> handicappedList = customerDao.retrieveHandicapped();
    final List<ValueListElement> povertyStatusList = customerDao.retrievePoverty();
    final List<ValueListElement> maritalStatusList = customerDao.retrieveMaritalStatuses();
    final List<ValueListElement> salutationsList = customerDao.retrieveSalutations();
    final List<OfficeDto> allOfficess = officeDao.findAllOffices();
    final FieldConfig fieldConfig = FieldConfig.getInstance();
    final List<String> globalCustNums = new ArrayList<String>();
    final List<String> errorsList = new ArrayList<String>();
    final List<ImportedClientDetail> parsedClientDetails = new ArrayList<ImportedClientDetail>();
    try {
        final HSSFWorkbook workbook = new HSSFWorkbook(input);
        final HSSFSheet sheet = workbook.getSheetAt(0);
        /* test first data row */
        HSSFRow row = sheet.getRow(XlsImportConstants.FIRST_CLIENT_ROW.value());
        if (row == null) {
            errorsList.add(getMessage(XlsMessageConstants.NOT_ENOUGH_INPUT_ROW));
        }
        @SuppressWarnings("rawtypes") Iterator rowIterator = sheet.rowIterator();
        /* Skip first rows */
        if (errorsList.isEmpty()) {
            for (int i = 0; i < XlsImportConstants.SKIPPED_ROWS.value(); i++) {
                if (rowIterator.hasNext()) {
                    rowIterator.next();
                } else {
                    errorsList.add(getMessage(XlsMessageConstants.NOT_ENOUGH_INPUT_ROW));
                    break;
                }
            }
        }
        XlsImportConstants currentCell = XlsImportConstants.CLIENT_NUM_CELL;
        int friendlyRowNumber = 0;
        /* Parse client data */
        if (errorsList.isEmpty()) {
            while (rowIterator.hasNext()) {
                try {
                    row = (HSSFRow) rowIterator.next();
                    friendlyRowNumber = row.getRowNum() + 1;
                    /* Get data from sheet */
                    currentCell = XlsImportConstants.CLIENT_NUM_CELL;
                    String clientGlobalNum = getCellStringValue(row, currentCell);
                    if (StringUtils.isBlank(clientGlobalNum)) {
                        // generate number
                        clientGlobalNum = null;
                    } else {
                        // check for duplicates
                        validateGlobalCustNum(clientGlobalNum, globalCustNums);
                        globalCustNums.add(clientGlobalNum);
                    }
                    currentCell = XlsImportConstants.BRANCH_SHORT_NAME_CELL;
                    final String branchShortName = getCellStringValue(row, currentCell);
                    final Short branchOfficeId = getBranchId(branchShortName, allOfficess);
                    currentCell = XlsImportConstants.GROUP_GLOBAL_NUM_CELL;
                    final String groupGlobalNum = getCellStringValue(row, currentCell);
                    validateGroup(groupGlobalNum);
                    if (StringUtils.isBlank(groupGlobalNum) && branchOfficeId == null) {
                        String error = getRowError(friendlyRowNumber) + getMessage(XlsMessageConstants.OFFICE_AND_BRANCH);
                        errorsList.add(error);
                        continue;
                    }
                    currentCell = XlsImportConstants.SALUTATION_CELL;
                    final String salutation = getCellStringValue(row, currentCell);
                    final Integer salutationId = getValueElementId(salutation, salutationsList);
                    validateMandatoryField(salutationId);
                    currentCell = XlsImportConstants.FIRST_NAME_CELL;
                    final String clientFirstName = getCellStringValue(row, currentCell);
                    validateMandatoryField(clientFirstName);
                    currentCell = XlsImportConstants.MIDDLE_NAME_CELL;
                    final String clientMiddleName = getCellStringValue(row, currentCell);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.MIDDLE_NAME)) {
                        validateMandatoryField(clientMiddleName);
                    }
                    currentCell = XlsImportConstants.LAST_NAME_CELL;
                    final String clientLastName = getCellStringValue(row, currentCell);
                    validateMandatoryField(clientLastName);
                    currentCell = XlsImportConstants.SECOND_LAST_NAME_CELL;
                    final String clientSecondLastName = getCellStringValue(row, currentCell);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.SECOND_LAST_NAME)) {
                        validateMandatoryField(clientSecondLastName);
                    }
                    currentCell = XlsImportConstants.GOVERNMENT_ID_CELL;
                    String governmentId = getCellStringValue(row, currentCell);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.GOVERNMENT_ID)) {
                        validateMandatoryField(governmentId);
                    }
                    currentCell = XlsImportConstants.DATE_OF_BIRTH_CELL;
                    final Date dateOfBirth = getCellDateValue(row, currentCell);
                    validateMandatoryField(dateOfBirth);
                    validateAge(dateOfBirth);
                    currentCell = XlsImportConstants.GENDER_CELL;
                    final String gender = getCellStringValue(row, currentCell);
                    final Short genderId = intToShort(getValueElementId(gender, gendersList));
                    validateMandatoryField(genderId);
                    currentCell = XlsImportConstants.MARITAL_STATUS_CELL;
                    final String maritalStatus = getCellStringValue(row, currentCell);
                    final Integer maritalStatusId = getValueElementId(maritalStatus, maritalStatusList);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.MARITAL_STATUS)) {
                        validateMandatoryField(maritalStatusId);
                    }
                    currentCell = XlsImportConstants.NUMBER_OF_CHILDREN_CELL;
                    final Short numberOfChildren = intToShort(getCellIntegerValue(row, currentCell));
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.NUMBER_OF_CHILDREN)) {
                        validateMandatoryField(numberOfChildren);
                    }
                    currentCell = XlsImportConstants.CITIZENSHIP_CELL;
                    final String citizenship = getCellStringValue(row, currentCell);
                    final Integer citizenshipId = getValueElementId(citizenship, citizenshipsList);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.CITIZENSHIP)) {
                        validateMandatoryField(citizenshipId);
                    }
                    currentCell = XlsImportConstants.ETHINICITY_CELL;
                    final String ethinicity = getCellStringValue(row, currentCell);
                    final Integer ethinicityId = getValueElementId(ethinicity, ethinicitiesList);
                    if (fieldConfig.isFieldHidden(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.ETHNICITY)) {
                        validateMandatoryField(ethinicityId);
                    }
                    currentCell = XlsImportConstants.EDUCATION_LEVEL_CELL;
                    final String educationLevel = getCellStringValue(row, currentCell);
                    final Integer educationLevelId = getValueElementId(educationLevel, educationLevelsList);
                    if (fieldConfig.isFieldHidden(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.EDUCATION_LEVEL)) {
                        validateMandatoryField(educationLevelId);
                    }
                    currentCell = XlsImportConstants.ACTIVITIES_CELL;
                    final String activites = getCellStringValue(row, currentCell);
                    final Integer activityId = getValueElementId(activites, buisnessActivitiesList);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.BUSINESS_ACTIVITIES)) {
                        validateMandatoryField(activityId);
                    }
                    currentCell = XlsImportConstants.POVERTY_STATUS_CELL;
                    final String povertyStatus = getCellStringValue(row, currentCell);
                    final Short povertyStatusId = intToShort(getValueElementId(povertyStatus, povertyStatusList));
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.POVERTY_STATUS)) {
                        validateMandatoryField(povertyStatusId);
                    }
                    currentCell = XlsImportConstants.HANDICAPPED_CELL;
                    final String handicapped = getCellStringValue(row, currentCell);
                    final Integer handicappedId = getValueElementId(handicapped, handicappedList);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.HANDICAPPED)) {
                        validateMandatoryField(handicappedId);
                    }
                    currentCell = XlsImportConstants.SPOUSE_FATHER_RELATIONSHIP_CELL;
                    final String spouseOrFather = getCellStringValue(row, currentCell);
                    final Short spouseFatherNameType = getSpouseNameType(spouseOrFather);
                    final boolean familyMandatory = fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.FAMILY_DETAILS);
                    if (familyMandatory) {
                        validateMandatoryField(spouseFatherNameType);
                    }
                    currentCell = XlsImportConstants.SPOUSE_FIRST_NAME_CELL;
                    final String spouseFirstName = getCellStringValue(row, currentCell);
                    if (familyMandatory) {
                        validateMandatoryField(spouseFirstName);
                    }
                    currentCell = XlsImportConstants.SPOUSE_MIDDLE_NAME_CELL;
                    final String spouseMiddleName = getCellStringValue(row, currentCell);
                    currentCell = XlsImportConstants.SPOUSE_SECOND_LAST_NAME_CELL;
                    final String spouseSecondLastName = getCellStringValue(row, currentCell);
                    if (familyMandatory && fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.SPOUSE_FATHER_SECOND_LAST_NAME)) {
                        validateMandatoryField(spouseSecondLastName);
                    }
                    currentCell = XlsImportConstants.SPOUSE_LAST_NAME_CELL;
                    final String spouseLastName = getCellStringValue(row, currentCell);
                    if (familyMandatory) {
                        validateMandatoryField(spouseLastName);
                    }
                    currentCell = XlsImportConstants.ADDRESS_CELL;
                    final String address = getCellStringValue(row, currentCell);
                    if (fieldConfig.isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.ADDRESS1)) {
                        validateMandatoryField(address);
                    }
                    currentCell = XlsImportConstants.CITY_DISTRICT_CELL;
                    final String cityDistrict = getCellStringValue(row, currentCell);
                    currentCell = XlsImportConstants.STATE_CELL;
                    final String state = getCellStringValue(row, currentCell);
                    currentCell = XlsImportConstants.COUNTRY_CELL;
                    final String country = getCellStringValue(row, currentCell);
                    currentCell = XlsImportConstants.POSTAL_CODE_CELL;
                    final String postalCode = getCellStringValue(row, currentCell);
                    currentCell = XlsImportConstants.TELEPHONE_CELL;
                    final String telephone = getCellStringValue(row, currentCell);
                    currentCell = XlsImportConstants.RECRUITED_BY_CELL;
                    final String recruitedBy = getCellStringValue(row, currentCell);
                    validateMandatoryField(recruitedBy);
                    final Short formedBy = getOfficerId(recruitedBy);
                    currentCell = XlsImportConstants.STATUS_CELL;
                    final String status = getCellStringValue(row, currentCell);
                    final Short statusId = statusToShort(status);
                    currentCell = XlsImportConstants.LOAN_OFFICER_CELL;
                    final String loanOfficer = getCellStringValue(row, currentCell);
                    if (!StringUtils.isBlank(groupGlobalNum) && !StringUtils.isBlank(loanOfficer)) {
                        throw new CellException(getMessage(XlsMessageConstants.LOAN_OFFICER_FOR_GROUP_CLIENT));
                    }
                    currentCell = XlsImportConstants.ACTIVATION_DATE_CELL;
                    final LocalDate activationDate = DateUtils.getLocalDateFromDate(getCellDateValue(row, currentCell));
                    if (activationDate != null && !status.equals(getMessage(XlsMessageConstants.ACTIVE))) {
                        throw new CellException(getMessage(XlsMessageConstants.ACTIVE_STATUS_FOR_ACTIVATION_DATE));
                    }
                    /* Meeting data */
                    currentCell = XlsImportConstants.MEETING_FREQUENCY_CELL;
                    final Integer recurrenceType = getRecurrenceType(getCellStringValue(row, currentCell));
                    currentCell = XlsImportConstants.MEETING_RECUR_EVERY_WEEK_CELL;
                    final Integer recurEveryWeek = getCellIntegerValue(row, currentCell);
                    currentCell = XlsImportConstants.MEETING_ON_WEEK_CELL;
                    final Integer weeklyMeetingDay = getDayValue(getCellStringValue(row, currentCell));
                    currentCell = XlsImportConstants.MEETING_OPT1_DAY_CELL;
                    final Integer opt1Day = getCellIntegerValue(row, currentCell);
                    validateMonthDay(opt1Day);
                    currentCell = XlsImportConstants.MEETING_OPT1_EVERY_CELL;
                    final Integer opt1Every = getCellIntegerValue(row, currentCell);
                    validatePositive(opt1Every);
                    currentCell = XlsImportConstants.MEETING_OPT2_THE_CELL;
                    final Integer opt2The = getDayRankValue(getCellStringValue(row, currentCell));
                    currentCell = XlsImportConstants.MEETING_OPT2_DAY_CELL;
                    final Integer opt2Day = getDayValue(getCellStringValue(row, currentCell));
                    currentCell = XlsImportConstants.MEETING_OPT2_EVERY_CELL;
                    final Integer opt2Every = getCellIntegerValue(row, currentCell);
                    validatePositive(opt2Every);
                    currentCell = XlsImportConstants.MEETING_LOCATION_CELL;
                    final String meetingLocation = getCellStringValue(row, currentCell);
                    Integer recurrenceDayNumber = 0;
                    Integer recurrenceWeekOfMonth = 0;
                    Integer recurrenceDayOfWeek = 0;
                    Integer recurEvery = 0;
                    /*
                         * Validate meeting data
                         */
                    if (!StringUtils.isBlank(groupGlobalNum) && recurrenceType != null) {
                        // no meeting allowed for a group
                        throw new RowException(getMessage(XlsMessageConstants.MEETING_FOR_GROUP));
                    } else if (recurrenceType == null) {
                        // make sure everything is empty
                        if (recurEveryWeek != null || weeklyMeetingDay != null || opt1Day != null || opt1Every != null || opt2Day != null || opt2Every != null || opt2The != null || !StringUtils.isBlank(meetingLocation)) {
                            throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                        }
                    } else if (recurrenceType == RecurrenceType.WEEKLY.getValue().intValue()) {
                        // make sure weekly data is set
                        if (recurEveryWeek == null || weeklyMeetingDay == null) {
                            throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                        }
                        // make sure monthly details are empty
                        if (opt1Day != null || opt1Every != null || opt2Day != null || opt2Every != null || opt2The != null) {
                            throw new RowException(getMessage(XlsMessageConstants.MONTHLY_MEETING_DETAILS_NOT_EMPTY));
                        }
                        // set data
                        recurrenceDayOfWeek = weeklyMeetingDay;
                        recurEvery = recurEveryWeek;
                        // validate location
                        validateMandatoryField(meetingLocation);
                    } else {
                        // make sure weekly details are empty
                        if (recurEveryWeek != null || weeklyMeetingDay != null) {
                            throw new RowException(getMessage(XlsMessageConstants.WEEKLY_MEETING_DETAILS_NOT_EMPTY));
                        }
                        if (opt1Day == null) {
                            // make sure option 2 is set
                            if (opt2Day == null || opt2Every == null || opt2The == null) {
                                throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                            }
                            // make sure option 1 is empty
                            if (opt1Every != null) {
                                throw new RowException(getMessage(XlsMessageConstants.OPTIONS_EXCLUSIVE));
                            }
                            // set data
                            recurrenceWeekOfMonth = opt2The;
                            recurrenceDayOfWeek = opt2Day;
                            recurEvery = opt2Every;
                        } else {
                            // make sure option 1 is set
                            if (opt1Every == null) {
                                throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                            }
                            // make sure option 2 is empty
                            if (opt2Day != null || opt2Every != null || opt2The != null) {
                                throw new RowException(getMessage(XlsMessageConstants.OPTIONS_EXCLUSIVE));
                            }
                            // set data
                            recurrenceDayNumber = opt1Day;
                            recurEvery = opt1Every;
                        }
                        // validate location
                        validateMandatoryField(meetingLocation);
                    }
                    /*
                         * Create meeting data
                         */
                    MeetingDto meetingDto = null;
                    if (recurrenceType != null) {
                        final LocalDate meetingStartDate = new LocalDate();
                        final MeetingRecurrenceDto meetingRecurrenceDto = new MeetingRecurrenceDto(recurrenceDayNumber, recurrenceWeekOfMonth, recurrenceDayOfWeek);
                        final MeetingDetailsDto meetingDetailsDto = new MeetingDetailsDto(recurrenceType, null, recurEvery, meetingRecurrenceDto);
                        final MeetingTypeDto meetingTypeDto = new MeetingTypeDto(MeetingType.CUSTOMER_MEETING.getValue().intValue(), null, null);
                        meetingDto = new MeetingDto(meetingStartDate, meetingLocation, meetingTypeDto, meetingDetailsDto);
                    } else {
                    }
                    String clientName = buildName(clientFirstName, clientMiddleName, clientLastName, clientSecondLastName);
                    customerDao.validateClientForDuplicateNameOrGovtId(clientName, dateOfBirth, governmentId);
                    /* Create dto's */
                    /* address */
                    final Address addressObject = new Address(address, null, null, cityDistrict, state, country, postalCode, telephone);
                    final AddressDto addressDto = Address.toDto(addressObject);
                    /* Personal details */
                    final ClientNameDetailDto clientNameDetailDto = new ClientNameDetailDto(CLIENT_NAME_TYPE, salutationId, clientFirstName, clientMiddleName, clientLastName, clientSecondLastName);
                    final ClientPersonalDetailDto clientPersonalDetailDto = new ClientPersonalDetailDto(ethinicityId, citizenshipId, handicappedId, activityId, maritalStatusId, educationLevelId, numberOfChildren, genderId, povertyStatusId);
                    /* Spouse details */
                    ClientNameDetailDto spouseNameDetailDto = null;
                    if (spouseFatherNameType == null) {
                        spouseNameDetailDto = new ClientNameDetailDto();
                        spouseNameDetailDto.setFirstName("");
                        spouseNameDetailDto.setLastName("");
                    } else {
                        spouseNameDetailDto = new ClientNameDetailDto(spouseFatherNameType, null, spouseFirstName, spouseMiddleName, spouseLastName, spouseSecondLastName);
                    }
                    /* branch office */
                    Short officeId = 0;
                    Short loanOfficerId = null;
                    Short groupFlagValue = 1;
                    if (StringUtils.isBlank(groupGlobalNum)) {
                        if (statusId == CustomerStatus.CLIENT_ACTIVE.getValue() && meetingDto == null) {
                            String error = getRowError(friendlyRowNumber) + getMessage(XlsMessageConstants.NO_MEETING_ERROR);
                            errorsList.add(error);
                            continue;
                        }
                        groupFlagValue = 0;
                        officeId = branchOfficeId;
                        List<PersonnelBO> officers = legacyPersonnelDao.getActiveLoanOfficersUnderOffice(officeId);
                        if (officers.isEmpty()) {
                            String error = getCellError(friendlyRowNumber, XlsImportConstants.BRANCH_SHORT_NAME_CELL) + getMessage(XlsMessageConstants.NO_OFFICERS_ERROR, branchShortName);
                            errorsList.add(error);
                            continue;
                        }
                        loanOfficerId = null;
                        for (PersonnelBO officer : officers) {
                            if (officer.getDisplayName().equals(loanOfficer)) {
                                loanOfficerId = officer.getPersonnelId();
                                break;
                            }
                        }
                    } else {
                        validateGroupStatus(groupGlobalNum, statusId);
                    }
                    /* Not imported values */
                    final boolean trained = false;
                    final Date trainedDate = null;
                    final java.sql.Date mfiJoiningDate = null;
                    final String externalId = "";
                    final InputStream picture = null;
                    final List<ApplicableAccountFeeDto> feesToApply = null;
                    final List<ClientNameDetailDto> familyNames = null;
                    final List<ClientFamilyDetailDto> familyDetails = null;
                    final List<Short> selectedSavingsProducts = null;
                    /* Final dto */
                    final ClientCreationDetail clientCreationDetail = new ClientCreationDetail(selectedSavingsProducts, clientName, statusId, mfiJoiningDate, externalId, addressDto, formedBy, dateOfBirth, governmentId, trained, trainedDate, groupFlagValue, clientNameDetailDto, clientPersonalDetailDto, spouseNameDetailDto, picture, feesToApply, groupGlobalNum, familyNames, familyDetails, loanOfficerId, officeId, activationDate);
                    validateDuplicateCustomers(clientCreationDetail, parsedClientDetails);
                    final ImportedClientDetail importedClientDetail = new ImportedClientDetail(clientCreationDetail, clientGlobalNum, meetingDto);
                    parsedClientDetails.add(importedClientDetail);
                } catch (RowException ex) {
                    final String error = getRowError(friendlyRowNumber) + ex.getMessage();
                    errorsList.add(error);
                } catch (CustomerException ex) {
                    final String error = getRowError(friendlyRowNumber) + getMessage(XlsMessageConstants.DUPLICATE_CLIENT_ERROR);
                    errorsList.add(error);
                } catch (Exception ex) {
                    final String error = getCellError(friendlyRowNumber, currentCell) + ex.getMessage();
                    errorsList.add(error);
                }
            }
        }
    } catch (Exception ex) {
        errorsList.add(getMessage(XlsMessageConstants.ERROR_READING_DOCUMENT, ex.getMessage()));
    }
    return new ParsedClientsDto(errorsList, parsedClientDetails);
}

Example 2 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project head by mifos.

the class XlsLoansAccountImporter method parse.

/**
     * Parse input stream.
     * @param is input stream containing loan accounts' data
     * @return object containing successfully parsed rows and rows with errors
     */
public ParsedLoansDto parse(InputStream is) {
    //prepare objects: result, lists for rows
    ParsedLoansDto result = null;
    List<String> errors = new ArrayList<String>();
    //temporary list for new accounts numbers, currently not used
    List<String> newAccountsNumbers = new ArrayList<String>();
    List<ImportedLoanDetail> parsedLoanDetails = new ArrayList<ImportedLoanDetail>();
    // open spreadsheet
    try {
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        HSSFSheet sheet = workbook.getSheetAt(0);
        // check first row of data
        HSSFRow row = sheet.getRow(XlsLoansImportTemplateConstants.FIRST_ROW_WITH_DATA.getValue());
        if (row == null) {
            throw new XlsParsingException(getMessage(XlsMessageConstants.NOT_ENOUGH_INPUT_ROW, null));
        }
        Iterator<Row> iterator = sheet.rowIterator();
        // skip to rows with data
        while (iterator.hasNext() && (iterator.next().getRowNum() < XlsLoansImportTemplateConstants.FIRST_ROW_WITH_DATA.getValue() - 1)) ;
        // parse loan account's data
        while (iterator.hasNext()) {
            row = (HSSFRow) iterator.next();
            List<Object> params = new ArrayList<Object>();
            // setup the first cell
            XlsLoansImportTemplateConstants currentCell = XlsLoansImportTemplateConstants.ACCOUNT_NUMBER;
            try {
                // account number
                String accountNumber = getCellStringValue(row, currentCell);
                // TODO: rewrite this account number validation to more universal and extract method
                if (StringUtils.isBlank(accountNumber) && isEdit) {
                    //editing and account number is missing
                    throw new XlsParsingException(getCellError(XlsMessageConstants.MISSING_ACCOUNT_NUMBER, row, currentCell.getValue(), null));
                } else //TODO: validation if account for edition exists
                if (StringUtils.isBlank(accountNumber) && !isEdit) {
                    //not editing, adding with predefined account number and account number is not good
                    accountNumber = null;
                } else //account number is good for creating new account with predefined account number...
                if (!StringUtils.isBlank(accountNumber) && !isEdit) {
                    //...but it's duplicate
                    if (!validateAccountNumber(accountNumber, newAccountsNumbers)) {
                        params.clear();
                        params.add(accountNumber);
                        throw new XlsParsingException(getCellError(XlsMessageConstants.DUPLICATE_GLOBAL_NUM_ERROR, row, currentCell.getValue(), params));
                    }
                }
                //all good, account is either predefined from xls document or null and will be generated 
                //TODO: extract methods that can be shared between loans and savings
                // customer global id
                currentCell = XlsLoansImportTemplateConstants.CUSTOMER_GLOBAL_ID;
                String customerGlobalId = getCellStringValue(row, currentCell);
                if (customerGlobalId.isEmpty()) {
                    throw new XlsParsingException(getCellError(XlsMessageConstants.CUSTOMER_NOT_BLANK, row, currentCell.getValue(), params));
                }
                CustomerBO customerBO = null;
                customerBO = validateCustomerGlobalId(customerGlobalId);
                if (customerBO == null) {
                    params.clear();
                    params.add(customerGlobalId);
                    throw new XlsParsingException(getCellError(XlsMessageConstants.CUSTOMER_NOT_FOUND, row, currentCell.getValue(), params));
                }
                // product name
                currentCell = XlsLoansImportTemplateConstants.PRODUCT_NAME;
                String productName = getCellStringValue(row, currentCell);
                LoanOfferingBO loanOfferingBO = null;
                loanOfferingBO = validateProductName(productName, customerBO, row, currentCell.getValue());
                //TODO: add support for backdated payments
                LoanCreationLoanDetailsDto lcldd = loanAccountServiceFacade.retrieveLoanDetailsForLoanAccountCreation(customerBO.getCustomerId(), loanOfferingBO.getPrdOfferingId(), false);
                // status name
                currentCell = XlsLoansImportTemplateConstants.STATUS_NAME;
                String statusName = getCellStringValue(row, currentCell);
                XlsLoanSavingsAccountStatesConstants statusConstant = null;
                statusConstant = validateStatusName(statusName, customerBO, this.isEdit, row, currentCell.getValue());
                // status reason flag
                currentCell = XlsLoansImportTemplateConstants.CANCEL_FlAG_REASON;
                String cancelReason = getCellStringValue(row, currentCell);
                XlsLoanSavingsFlagsConstants flagConstant = null;
                flagConstant = validateStatusFlagReason(cancelReason, statusName, AccountTypes.LOAN_ACCOUNT, row, currentCell.getValue());
                // loan amount
                currentCell = XlsLoansImportTemplateConstants.LOAN_AMOUNT;
                BigDecimal loanAmount = getCellDecimalValue(row, currentCell);
                validateAmount(loanAmount, loanOfferingBO, customerBO, lcldd, row, currentCell.getValue());
                // Interest rate
                currentCell = XlsLoansImportTemplateConstants.INTEREST_RATE;
                BigDecimal interestRate = getCellDecimalValue(row, currentCell);
                validateInterestRate(interestRate, loanOfferingBO, customerBO, lcldd, row, currentCell.getValue());
                // number of installments
                currentCell = XlsLoansImportTemplateConstants.NO_OF_INSTALLMENTS;
                Integer numberOfInstallments = getCellIntegerValue(row, currentCell);
                validateNumberOfInstallments(numberOfInstallments, customerBO, loanOfferingBO, lcldd, row, currentCell.getValue());
                // disbursal date
                currentCell = XlsLoansImportTemplateConstants.DISBURLSAL_DATE;
                Date disbursalDate = getCellDateValue(row, currentCell);
                validateDisbursalDate(disbursalDate, customerBO, loanOfferingBO, currentCell.getValue(), row, statusName);
                // grace period
                currentCell = XlsLoansImportTemplateConstants.GRACE_PERIOD;
                Integer gracePeriod = getCellIntegerValue(row, currentCell);
                validateGracePeriod(gracePeriod, loanOfferingBO, customerBO, numberOfInstallments, row, currentCell.getValue());
                // source of founds
                currentCell = XlsLoansImportTemplateConstants.SOURCE_OF_FOUNDS;
                List<FundDto> funds = lcldd.getFundDtos();
                String sourceOfFund = getCellStringValue(row, currentCell);
                Integer sourceOfFundId = null;
                sourceOfFundId = validateSourceOfFund(sourceOfFund, funds, row, currentCell.getValue());
                // purpose
                List<ValueListElement> purposes = lcldd.getLoanPurposes();
                currentCell = XlsLoansImportTemplateConstants.PURPOSE;
                String loanPurpose = getCellStringValue(row, currentCell);
                Integer loanPurposeId = null;
                loanPurposeId = validateLoanPurposeId(loanPurpose, purposes, row, currentCell.getValue());
                // collateral type
                currentCell = XlsLoansImportTemplateConstants.COLLATERAL_TYPE;
                Integer collateralTypeId = null;
                String collateralType = getCellStringValue(row, currentCell);
                Map<String, String> collaterals = lcldd.getCollateralOptions();
                collateralTypeId = validateCollateralType(collateralType, collaterals, row, currentCell.getValue());
                // collateral notes
                currentCell = XlsLoansImportTemplateConstants.COLLATERAL_NOTES;
                String collateralNotes = getCellStringValue(row, currentCell);
                collateralNotes = StringUtils.isBlank(collateralNotes) ? null : collateralNotes;
                // external id
                currentCell = XlsLoansImportTemplateConstants.EXTERNAL_ID;
                String externalId = getCellStringValue(row, currentCell);
                externalId = StringUtils.isBlank(externalId) ? null : externalId;
                //...will be used for editing/adding loans with predefined account numbers
                if (accountNumber != null) {
                    newAccountsNumbers.add(accountNumber);
                }
                //create final objects
                //TODO handle backdated payments
                Short flagValue = flagConstant == null ? null : flagConstant.getFlag().getValue();
                ImportedLoanDetail detail = new ImportedLoanDetail(accountNumber, customerBO.getCustomerId(), loanOfferingBO.getPrdOfferingId(), statusConstant.getState().getValue(), flagValue, loanAmount, interestRate, numberOfInstallments, disbursalDate, gracePeriod, sourceOfFundId, loanPurposeId, collateralTypeId, collateralNotes, externalId);
                parsedLoanDetails.add(detail);
            } catch (XlsParsingException xex) {
                if (xex.isMultiple()) {
                    for (String msg : xex.getMessages()) {
                        errors.add(msg);
                    }
                } else {
                    errors.add(xex.getMessage());
                }
            } catch (Exception cex) {
                errors.add(cex.getMessage());
            }
        }
    } catch (Exception ex) {
        errors.add(ex.getMessage());
    }
    result = new ParsedLoansDto(errors, parsedLoanDetails);
    return result;
}

Example 3 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project OpenRefine by OpenRefine.

the class ExcelImporter method createParserUIInitializationData.

@Override
public JSONObject createParserUIInitializationData(ImportingJob job, List<JSONObject> fileRecords, String format) {
    JSONObject options = super.createParserUIInitializationData(job, fileRecords, format);
    JSONArray sheetRecords = new JSONArray();
    JSONUtilities.safePut(options, "sheetRecords", sheetRecords);
    try {
        if (fileRecords.size() > 0) {
            JSONObject firstFileRecord = fileRecords.get(0);
            File file = ImportingUtilities.getFile(job, firstFileRecord);
            InputStream is = new FileInputStream(file);
            if (!is.markSupported()) {
                is = new PushbackInputStream(is, 8);
            }
            try {
                Workbook wb = POIXMLDocument.hasOOXMLHeader(is) ? new XSSFWorkbook(is) : new HSSFWorkbook(new POIFSFileSystem(is));
                int sheetCount = wb.getNumberOfSheets();
                boolean hasData = false;
                for (int i = 0; i < sheetCount; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    int rows = sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
                    JSONObject sheetRecord = new JSONObject();
                    JSONUtilities.safePut(sheetRecord, "name", sheet.getSheetName());
                    JSONUtilities.safePut(sheetRecord, "rows", rows);
                    if (hasData) {
                        JSONUtilities.safePut(sheetRecord, "selected", false);
                    } else if (rows > 1) {
                        JSONUtilities.safePut(sheetRecord, "selected", true);
                        hasData = true;
                    }
                    JSONUtilities.append(sheetRecords, sheetRecord);
                }
            } finally {
                is.close();
            }
        }
    } catch (IOException e) {
        logger.error("Error generating parser UI initialization data for Excel file", e);
    } catch (IllegalArgumentException e) {
        logger.error("Error generating parser UI initialization data for Excel file (only Excel 97 & later supported)", e);
    } catch (POIXMLException e) {
        logger.error("Error generating parser UI initialization data for Excel file - invalid XML", e);
    }
    return options;
}

Example 4 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project adempiere by adempiere.

the class SMJReportViewer method createXlsReport.

//	dynInit
/**
			 * crea el reporte XLS - Create XLS report
			 */
private void createXlsReport() {
    SmjXlsReport xls = new SmjXlsReport();
    HSSFWorkbook book = xls.generate(data, generalTitle, clientName, clientNIT, periodName, currencyName, m_columns, city, logoId);
    fileXls = xls.tofile(book, generalTitle);
    revalidate();
}

Example 5 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project poi by apache.

the class TestOSGiBundle method testHSSF.

@Test
public void testHSSF() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("OSGi");
    s.createRow(0).createCell(0).setCellValue("With OSGi");
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);
    ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
    wb = new HSSFWorkbook(bais);
    assertEquals(1, wb.getNumberOfSheets());
    s = wb.getSheet("OSGi");
    assertEquals("With OSGi", s.getRow(0).getCell(0).toString());
}