Example usage for org.apache.poi.xssf.usermodel XSSFRow getCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFRow getCell.

Prototype

@Override
public XSSFCell getCell(int cellnum)

Source Link

Document

Returns the cell at the given (0 based) index, with the org.apache.poi.ss.usermodel.Row.MissingCellPolicy from the parent Workbook.

Usage

From source file:eu.alpinweiss.filegen.command.steps.impl.ReadInputParametersStepImpl.java

License:Apache License

private String readLineSeparator(XSSFSheet sheet) {
    XSSFRow row = sheet.getRow(1);
    Cell cell = row.getCell(1);
    return cell.getStringCellValue();
}

From source file:eu.riscoss.server.EntityManager.java

License:Apache License

private void readImportFile(RiscossDB db) throws Exception {

    //Load importing config xml file
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    FileInputStream f = new FileInputStream("resources/importation_config.xml");
    Document doc = builder.parse(f);
    Element element = doc.getDocumentElement();

    //Get relationships config info
    NodeList importNodes = element.getElementsByTagName("relationships");
    Element e = (Element) importNodes.item(0);
    String sheet = e.getElementsByTagName("sheet_name").item(0).getTextContent();

    List<Integer> entitiesColumns = new ArrayList<>();
    NodeList ent = e.getElementsByTagName("layer");
    while (ent != null) {
        Element el = (Element) ent.item(0);
        if (el != null) {
            entitiesColumns.add(Integer.valueOf(el.getAttribute("entity_column")) - 1);
            ent = el.getElementsByTagName("layer");
        } else//from  w w w .j  a  va  2s  .c om
            break;
    }

    List<ConfigItem> config = new ArrayList<>();

    //Get imported entitites config info
    NodeList cf = element.getElementsByTagName("entities");
    Element impEnt = (Element) cf.item(0);
    NodeList configNodes = impEnt.getElementsByTagName("imported_entity");

    for (int i = 0; i < configNodes.getLength(); ++i) {
        ConfigItem conf = new ConfigItem();
        Element entity = (Element) configNodes.item(i);
        conf.sheet = entity.getElementsByTagName("sheet_name").item(0).getTextContent();
        conf.nameColumn = Integer.parseInt(entity.getElementsByTagName("name_column").item(0).getTextContent())
                - 1;

        List<Pair<String, Pair<Integer, Integer>>> definedIdItem = new ArrayList<>();
        List<Pair<String, Integer>> definedValueItem = new ArrayList<>();

        NodeList p = entity.getElementsByTagName("custom_information");
        Element ee = (Element) p.item(0);
        NodeList prop = ee.getElementsByTagName("custom_field");

        for (int j = 0; j < prop.getLength(); ++j) {
            Element b = (Element) prop.item(j);

            if (b.getElementsByTagName("id").item(0) != null) {
                String id = b.getElementsByTagName("id").item(0).getTextContent();
                int column = Integer.parseInt(b.getElementsByTagName("value_column").item(0).getTextContent())
                        - 1;
                definedValueItem.add(new Pair<>(id, column));
            } else {
                int column = Integer.parseInt(b.getElementsByTagName("id_column").item(0).getTextContent()) - 1;
                int val = Integer.parseInt(b.getElementsByTagName("value").item(0).getTextContent());
                String prefix = b.getElementsByTagName("prefix").item(0).getTextContent();
                definedIdItem.add(new Pair<>(prefix, new Pair<>(column, val)));
            }
        }
        conf.definedIdItem = definedIdItem;
        conf.definedValueItem = definedValueItem;

        config.add(conf);
    }

    File xlsx = new File("resources/entities_info.xlsx");
    FileInputStream fis = new FileInputStream(xlsx);
    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet ws = wb.getSheet(sheet);

    Map<String, Pair<String, String>> list = new HashMap<>();

    Map<Integer, List<String>> entities = new HashMap<>();

    List<Pair<String, String>> relationships = new ArrayList<>();

    boolean read = true;
    int i = 6;
    while (read) {
        //For every valid row in xlsx file
        XSSFRow row = ws.getRow(i);
        if (row == null || row.getCell(0).toString().equals(""))
            read = false;
        else {
            //For every custom entity defined in i row
            for (int j = 0; j < config.size(); ++j) {
                String parent = row.getCell(config.get(j).nameColumn).toString();

                if (!parent.equals("")) {

                    int x = 0;
                    while (x < entitiesColumns.size()) {
                        if (entitiesColumns.get(x) == config.get(j).nameColumn)
                            break;
                        else
                            ++x;
                    }

                    if (x > 0)
                        relationships
                                .add(new Pair<>(row.getCell(entitiesColumns.get(x - 1)).toString(), parent));

                    if (entities.containsKey(config.get(j).nameColumn))
                        entities.get(config.get(j).nameColumn).add(parent);
                    else {
                        List<String> n = new ArrayList<>();
                        n.add(parent);
                        entities.put(config.get(j).nameColumn, n);
                    }

                    String prefix = "";

                    //For every custom info defined for j entity in i row
                    for (int k = 0; k < config.get(j).definedIdItem.size(); ++k) {
                        prefix = config.get(j).definedIdItem.get(k).getLeft();
                        if (!row.getCell(config.get(j).definedIdItem.get(k).getRight().getLeft()).toString()
                                .equals("")) {
                            checkNewInfo(parent,
                                    prefix + row
                                            .getCell(config.get(j).definedIdItem.get(k).getRight().getLeft())
                                            .toString(),
                                    config.get(j).definedIdItem.get(k).getRight().getRight().toString(), list,
                                    db);
                        }
                    }
                    for (int k = 0; k < config.get(j).definedValueItem.size(); ++k) {
                        if (!row.getCell(config.get(j).definedValueItem.get(k).getRight()).toString()
                                .equals("")) {
                            String value = row.getCell(config.get(j).definedValueItem.get(k).getRight())
                                    .toString();
                            checkNewInfo(parent, config.get(j).definedValueItem.get(k).getLeft(), value, list,
                                    db);
                        }
                    }
                }
            }
            ++i;
        }
    }

    Map<String, String> en = importEntities(entitiesColumns, entities, relationships, db);

    //Delete old imported data for entities
    Set<String> all = new HashSet<>();
    for (List<String> s : entities.values())
        all.addAll(s);
    for (String target : all) {
        for (String id : db.listRiskData(target)) {
            JsonObject o = (JsonObject) new JsonParser().parse(db.readRiskData(target, id));
            if (o.get("type").toString().equals("\"imported\"")) {
                JsonObject delete = new JsonObject();
                delete.addProperty("id", id);
                delete.addProperty("target", target);
                JsonArray array = new JsonArray();
                array.add(delete);
                db.storeRiskData(delete.toString());
            }
        }
    }

    for (String child : list.keySet()) {
        if (!list.get(child).getLeft().equals("") && !list.get(child).getRight().equals(""))
            storeRDR(child.split("@")[0], en.get(child.split("@")[0]), list.get(child).getLeft(),
                    list.get(child).getRight(), db);
    }

}

From source file:FormatConvert.exceloperation.Excel2csv.java

public static void copySheets2CSV(XSSFSheet sheet, String csvfile) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap = null;

    try {// w  ww.j a va 2s  . c  o  m
        FileWriter fw = new FileWriter(csvfile);

        String str = "";
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow srcRow = sheet.getRow(i);
            if (srcRow != null) {
                System.out.println(srcRow.getLastCellNum());
                System.out.println(srcRow.getFirstCellNum());
                //                    System.out.println(srcRow.getCell(srcRow.getLastCellNum()).toString());
                for (int j = srcRow.getFirstCellNum(); j < srcRow.getLastCellNum(); j++) {

                    if (srcRow.getCell(j) != null && j != srcRow.getLastCellNum() - 1) {
                        srcRow.getCell(j).setCellType(1);

                        str = str + srcRow.getCell(j).getReference() + ",";
                    } else if (srcRow.getCell(j) != null) {
                        srcRow.getCell(j).setCellType(1);

                        str = str + srcRow.getCell(j).getStringCellValue() + "\r\n";
                    }
                    //
                }
                fw.append(str);
            }
            str = "";
        }

        fw.flush();
        fw.close();
    } catch (IOException ex) {

    } //Util.copyPictures(newSheet,sheet) ;
}

From source file:Import.ImportCapital.java

@Override
public int getSheetRowCount(XSSFSheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    lastRowNum++;/*from w ww.j  a v a2 s .co  m*/
    for (int i = START_ROW_CAPITAL; i < lastRowNum; i++) {
        XSSFRow row = sheet.getRow(i);
        XSSFCell cell1 = row.getCell(1);
        XSSFCell cell2 = row.getCell(2);
        XSSFCell cell3 = row.getCell(3);
        XSSFCell cell4 = row.getCell(4);

        if ((cell1 == null || getStringCellValueNoSetError(cell1) == null)
                && (cell2 == null || getStringCellValueNoSetError(cell2) == null)
                && (cell3 == null || getStringCellValueNoSetError(cell3) == null)
                && (cell4 == null || getStringCellValueNoSetError(cell4) == null)) {
            return i - START_ROW_CAPITAL;
        }
    }
    return lastRowNum - START_ROW_CAPITAL;
}

From source file:Import.ImportCapital.java

@Override
protected void threadProcess(XSSFSheet sheet, int index, int lastRowNum) {
    validationError = new StringBuffer();

    XSSFRow row = sheet.getRow(index);

    int rowCount = index + 1;
    // A/*from   w ww .  j a v  a2s  .  c  om*/
    //XSSFCell cell0 = row.getCell(0);

    // B                         
    XSSFCell cell1 = row.getCell(1);
    String depaertmentCode = this.getStringCellValueSetError(cell1, 1);

    Integer departmentId;
    Integer businessUnitId;
    if (!isBlankOrNull(depaertmentCode)) {
        if (TestGUI.IsEnableLog()) {
            logln("? " + rowCount);
        }
        departmentId = cacheDao.findDepartIdmentByFullCodeCache(depaertmentCode);
        businessUnitId = departmentId;
    } else {
        departmentId = 0;
        businessUnitId = 0;
        //            validationError.append("  ").append(mapStringValidation.get(1)).append(" [ ").append(depaertmentCode).append(" ] ");
        //            if(index == START_ROW_CAPITAL){
        //                validationErrorList.add(new StringBuffer(""));
        //                logln("");
        //            }
        //            logln("? " + (index-5) + " ?");
        //            return;
    }

    //? C
    XSSFCell cell2 = row.getCell(2);
    String fundCode = this.getStringCellValueSetError(cell2, 2);
    FiFund fundDao = null;
    if (!isBlankOrNull(fundCode)) {
        fundDao = cacheDao.findFundByFundCode(fundCode, departmentId);
        if (fundDao == null || isNullOrZero(fundDao.getFundId())) {
            validationError.append(" [ ").append(mapStringValidation.get(2)).append(fundCode)
                    .append("  ").append(depaertmentCode).append(" ] ");
        }
    }

    // D
    XSSFCell cell3 = row.getCell(3);
    String capitalCode = this.getStringCellValueSetError(cell3, 3);

    // E
    XSSFCell cell4 = row.getCell(4);
    String capitalName = this.getStringCellValueSetError(cell4, 4);

    //? F
    XSSFCell cell5 = row.getCell(5);
    String interestType = this.getStringCellValueNoSetError(cell5);
    interestType = (!isBlankOrNull(interestType) && INTERREST_STRING.equals(interestType)) ? STRING_C
            : STRING_I;

    // G
    XSSFCell cell6 = row.getCell(6);
    String capitalDesc = this.getStringCellValueNoSetError(cell6);

    // H
    XSSFCell cell7 = row.getCell(7);
    Date setupDate = this.getDateCellValueNoSetError(cell7);

    // I
    XSSFCell cell8 = row.getCell(8);
    String note = this.getStringCellValueNoSetError(cell8);

    //? J            
    XSSFCell cell9 = row.getCell(9);
    String accountCode = this.getStringCellValueSetError(cell9, 9);
    Integer accountId = null;
    if (!isBlankOrNull(accountCode)) {
        accountId = cacheDao.getAccountIdByFullCodeCache(accountCode);
        if (isNullOrZero(accountId)) {
            validationError.append(" [ ").append(mapStringValidation.get(9)).append(accountCode)
                    .append(" ?").append(" ] ");
        }
    }

    //? K
    XSSFCell cell10 = row.getCell(10);
    String sourceCode = this.getStringCellValueSetError(cell10, 10);
    Integer sourceId = null;
    if (!isBlankOrNull(sourceCode)) {
        sourceId = cacheDao.getSourceIdByFullCodeCache(sourceCode);
        if (isNullOrZero(sourceId)) {
            validationError.append(" [ ").append(mapStringValidation.get(10)).append(sourceCode)
                    .append(" ?").append(" ] ");
        }
    }

    //? L
    XSSFCell cell11 = row.getCell(11);
    String planCode = this.getStringCellValueSetError(cell11, 11);
    Integer planId = null;
    if (!isBlankOrNull(planCode)) {
        planId = cacheDao.findPlanIdmentByFullCodeCache(planCode);
        if (isNullOrZero(planId)) {
            validationError.append(" [ ").append(mapStringValidation.get(11)).append(planCode)
                    .append(" ?").append(" ] ");
        }
    }

    // M
    XSSFCell cell12 = row.getCell(12);
    BigDecimal balance = this.getBigDecimalCellValueNoSetError(cell12);

    //? N
    XSSFCell cell13 = row.getCell(13);
    String firstName = this.getStringCellValueNoSetError(cell13);

    //? O
    XSSFCell cell14 = row.getCell(14);
    String address = this.getStringCellValueNoSetError(cell14);

    if (validationError.length() > 0) {
        // error ? row ? commit
        this.commit = false;
        if (TestGUI.IsEnableLogError()) {
            if (!TestGUI.IsEnableLog()) {
                logln("? " + rowCount);
            }
            log.append("   Error : ");
            log.append(validationError.toString());
            log.append("\n");
        }

        validationError.insert(0, " ").insert(0, rowCount).insert(0, "ROW ");
        validationErrorList.add(validationError);

    } else {
        //?
        if (this.commit) {

            FiCapital capital = createFiCapital(departmentId, fundDao, capitalCode, capitalName, interestType,
                    capitalDesc, setupDate, note, accountId, sourceId, planId, balance);

            // spec  @N  person ? capitalFounder
            if (!isBlankOrNull(firstName)) {
                ShPerson person = createShPerson(firstName, address, businessUnitId);

                createFiCapitalFounder(capital.getCapitalId(), person.getPersonId());
            }
        }
        if (TestGUI.IsEnableLog()) {
            log.append("      ");
            log.append("\n");
        }

    }
}

From source file:Import.ImportDonation.java

@Override
protected void threadProcess(XSSFSheet sheet, int index, int lastRowNum) {
    validationError = new StringBuffer();
    validationWarn = new StringBuffer();
    //        List<Object> itemList = new ArrayList<>();
    XSSFRow row = sheet.getRow(index);

    this.rowCount = index + 1;

    XSSFCell cell0 = row.getCell(0);
    logln("? " + rowCount);
    try {/*www  .j  a  va2  s.c o m*/
        ReceiveType receiveType = ReceiveType.valueOf(getStringCellValueNoSetError(cell0));
        mode = receiveType.getType();
        if (isBlankOrNull(mode)) {
            throw new NullPointerException();
        }
    } catch (NullPointerException iEx) {
        validationError.append(
                "  [] ()");
        setError();
        return;
    } catch (IllegalArgumentException iEx) {
        validationError.append(
                " []  ()");
        setError();
        return;
    }

    XSSFCell cell86 = row.getCell(86);
    DEPARTMENT_CODE = getStringFormatNoSetError(cell86, "##");
    try {
        if (DEPARTMENT_CODE.length() > 2) {
            validationError.append(
                    " ? 2 ? ");
            throw new NullPointerException();
        }
    } catch (NullPointerException nullEx) {
        setError(86);
        //            setError();
        //            return;
    }

    ShPerson person = addPerson(row);

    addPersonDoc(row, person);
    addAddress(row, person);
    switch (mode) {
    case STRING_D: {
        AddDoanteMember(row, person);
        break;
    }
    case STRING_C:
        ;
    case STRING_B: {
        AcDonateMember donateMember = AddDoanteMember(row, person);
        AddDoanteMemberAccount(row, person, donateMember);
        break;
    }
    case STRING_M: {
        ShMember member = AddMember(row, person);
        AddMemberCard(row, person, member);
        break;
    }
    default:
        ;
    }
    this.setError();
}

From source file:Import.ImportDonation.java

@Override
protected int getSheetRowCount(XSSFSheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    lastRowNum++;//from   ww  w  . ja v a 2  s  . co  m
    int count = 0;
    for (int i = START_ROW_DONAION; i < lastRowNum; i++) {
        XSSFRow row = sheet.getRow(i);
        XSSFCell cell0 = row.getCell(0);
        XSSFCell cell3 = row.getCell(3);
        if ((cell0 == null || getStringCellValueNoSetError(cell0) == null)
                && (cell3 == null || getStringCellValueNoSetError(cell3) == null)) {
            return count;
        }
        count++;
    }
    return lastRowNum - START_ROW_DONAION;
}

From source file:Import.ImportDonation.java

private ShPerson addPerson(XSSFRow row) {
    ShPerson person = new ShPerson();

    //        Integer departmentId = getDepartmentId(DEPARTMENT_CODE) ;
    Integer departmentId = getDepartmentIdCache(DEPARTMENT_CODE);
    if (departmentId == null || departmentId == 0) {
        //            setErrorValue(86, DEPARTMENT_CODE);
    }//from   w ww .  ja  v a2s.  com
    person.setBusinessUnitId(departmentId);

    XSSFCell cell2 = row.getCell(2);
    String titleName = getStringCellValueNoSetError(cell2);// 
    person.setTitleName(titleName);

    person.setPersonType(STRING_P);
    if (!isBlankOrNull(titleName)) {
        ShTitle title = cacheDao.findTitleByTitleNameCache(titleName);
        try {
            person.setTitleId(title.getTitleId());
        } catch (NullPointerException nullEx) {
        }
    }

    XSSFCell cell3 = row.getCell(3);
    String firstNameThai = getStringCellValueSetError(cell3, 3);//  ()
    person.setFirstName(firstNameThai);

    XSSFCell cell4 = row.getCell(4);
    String middleNameThai = getStringCellValueNoSetError(cell4); // ?
    person.setMiddleName(middleNameThai);

    XSSFCell cell5 = row.getCell(5);
    String lastNameThai = getStringCellValueNoSetError(cell5);// ?
    person.setLastName(lastNameThai);

    XSSFCell cell6 = row.getCell(6);
    String firstNameEng = getStringCellValueNoSetError(cell6); //  (?)
    person.setFirstNameEng(firstNameEng);

    XSSFCell cell7 = row.getCell(7);
    String middleNameEng = getStringCellValueNoSetError(cell7); // ? (?)
    person.setMiddleNameEng(middleNameEng);

    XSSFCell cell8 = row.getCell(8);
    String lastNameEng = getStringCellValueNoSetError(cell8); // ? (?)
    person.setLastNameEng(lastNameEng);

    XSSFCell cell9 = row.getCell(9);
    String nickName = getStringCellValueNoSetError(cell9); // 
    person.setNickName(nickName);

    XSSFCell cell10 = row.getCell(10);
    String Phone = getStringCellValueNoSetError(cell10); // 
    person.setPhone(Phone);

    XSSFCell cell11 = row.getCell(11);
    String fax = getStringCellValueNoSetError(cell11); // ??
    person.setFax(fax);

    XSSFCell cell16 = row.getCell(16);
    String sex = getStringCellValueNoSetError(cell16); // 
    try {
        SexType sexType = SexType.valueOf(sex);
        person.setSex(sexType.getDescription());
    } catch (NullPointerException e) {
        person.setSex(STRING_N);
    }

    XSSFCell cell17 = row.getCell(17);
    Date birthDate = getDateCellValueIsNotEmptySetError(cell17, 17); // ?
    person.setBirthDate(birthDate);

    person.setResidentAddressSource(STRING_N);

    person.setShipAddressSource(STRING_N);
    person.setIsShipRecipientAuto(STRING_N);
    String shipRecipient = getShipRecipient(titleName, firstNameThai, middleNameThai, lastNameThai);
    person.setShipRecipient(shipRecipient);

    XSSFCell cell48 = row.getCell(48);
    String addressName = getStringFormatNoSetError(cell48, "##"); // // @AW

    XSSFCell cell49 = row.getCell(49);
    String addressLocation = getStringFormatNoSetError(cell49, "##"); // / @AX

    XSSFCell cell50 = row.getCell(50);
    String addressNo = getStringFormatNoSetError(cell50, "##"); //  @AY

    XSSFCell cell51 = row.getCell(51);
    String addressMoo = getStringFormatNoSetError(cell51, "##"); //  @AZ

    XSSFCell cell53 = row.getCell(53);
    String addressStreet = getStringCellValueNoSetError(cell53); // 

    XSSFCell cell54 = row.getCell(54);
    String addressSoi = getStringFormatNoSetError(cell54, "##"); // ?/

    String address1 = getShipAddress(addressName, addressLocation, addressNo, addressMoo, addressStreet,
            addressSoi);

    person.setShipAddressLine1(address1);

    XSSFCell cell55 = row.getCell(55);
    String province = getStringCellValueNoSetError(cell55); // 

    XSSFCell cell56 = row.getCell(56);
    String amphoe = getStringCellValueNoSetError(cell56); // 

    XSSFCell cell57 = row.getCell(57);
    String tumbon = getStringCellValueNoSetError(cell57); // 

    String address2 = getShipAddress(tumbon, amphoe, province, null, null, null);
    person.setShipAddressLine2(address2);

    XSSFCell cell58 = row.getCell(58);
    String postCode = getStringFormatNoSetError(cell58, "##"); // 

    XSSFCell cell59 = row.getCell(59);
    String country = getStringCellValueNoSetError(cell59); // 

    String address3 = getShipAddress(postCode, country, null, null, null, null);
    person.setShipAddressLine3(address3);

    person.setIsCancel(STRING_N);
    person.setIsLockUpdate(STRING_N);
    person.setCreateTime(NOW);
    person.setCreateProg(CREATE_PROG);
    person.setCreateUserId(CREATE_USER_ID);

    person.setLastUpdTime(NOW);
    person.setLastUpdProg(CREATE_PROG);
    person.setLastUpdUserId(CREATE_USER_ID);

    person.setLastUpdVersion(1);
    cacheDao.addEntity(person);
    return person;
}

From source file:Import.ImportDonation.java

private ShAddress addAddress(XSSFRow row, ShPerson person) {
    if (validatAddAddress(row))
        return null;
    ShAddress address = new ShAddress();

    XSSFCell cell48 = row.getCell(48);
    String addressName1 = getStringFormatNoSetError(cell48, "##"); // // 

    XSSFCell cell49 = row.getCell(49);/*  ww  w.j  a  v a2s.co  m*/
    String addressLocation1 = getStringFormatNoSetError(cell49, "##"); // /

    XSSFCell cell50 = row.getCell(50);
    String addressNo1 = getStringFormatNoSetError(cell50, "##"); // 

    XSSFCell cell51 = row.getCell(51);
    String moo1 = getStringFormatNoSetError(cell51, "##"); // 

    XSSFCell cell53 = row.getCell(53);
    String street1 = getStringFormatNoSetError(cell53, "##"); // 

    XSSFCell cell54 = row.getCell(54);
    String soi1 = getStringFormatNoSetError(cell54, "##"); // /

    XSSFCell cell58 = row.getCell(58);
    String postCode1 = getStringFormatNoSetError(cell58, "##"); // 

    XSSFCell cell60 = row.getCell(60);
    String phone1 = getStringFormatNoSetError(cell60, "##"); // 

    //ADDRESS_ID   ID Running
    //BUSINESS_UNIT_ID   SH_PERSON.BUSINESS_UNIT_ID
    address.setBusinessUnitId(person.getBusinessUnitId());
    //PERSON_ID   SH_PERSON.PERSON_ID
    address.setPersonId(person.getPersonId());
    //ADDRESS_TYPE   R
    address.setAddressType(STRING_R);
    //ADDRESS_NAME   @AW
    address.setAddressName(addressName1);
    //ADDRESS_LOCATION   @AX
    address.setAddressLocation(addressLocation1);
    //ADDRESS_NO   @AY
    address.setAddressNo(addressNo1);
    //ADDRESS_MOO   @AZ
    address.setAddressMoo(moo1);
    //ADDRESS_AREA_ID   "? V_SH_REGION.REGION_ID
    //V_SH_REGION.FULL_NAME =@BF||' '||@BE||' '||@BD"

    XSSFCell cell55 = row.getCell(55);
    String province = getStringCellValueNoSetError(cell55); // 

    XSSFCell cell56 = row.getCell(56);
    String amphoe = getStringCellValueNoSetError(cell56); // 

    XSSFCell cell57 = row.getCell(57);
    String tumbon = getStringCellValueNoSetError(cell57); // 

    String regionFullName = getShipAddress(tumbon, amphoe, province, null, null, null);

    if (!isBlankOrNull(regionFullName)) {
        VShRegion region = cacheDao.findRegionByFullNameCache(regionFullName);
        try {
            address.setAddressAreaId(region.getRegionId());
        } catch (NullPointerException nullEx) {
            validationWarn.append(" [ ").append(mapStringValidation.get(575655))
                    .append(regionFullName).append(" ?").append(" ] ");
        }
    }
    //ADDRESS_AREA_NAME   @BF||' '||@BE||' '||@BD
    address.setAddressAreaName(regionFullName);
    //ADDRESS_STREET   @BB
    address.setAddressStreet(street1);
    //ADDRESS_SOI   @BC
    address.setAddressSoi(soi1);
    //ADDRESS_POSTAL   @BG
    address.setAddressPostal(postCode1);
    //ADDRESS_PHONE   @BI
    address.setAddressPhone(phone1);
    //IS_CANCEL   N
    address.setIsCancel(STRING_N);
    //IS_LOCK_UPDATE   N
    address.setIsLockUpdate(STRING_N);
    //CREATE_TIME    Convert
    address.setCreateTime(NOW);
    //CREATE_USER_ID   CONVERT
    address.setCreateUserId(CREATE_USER_ID);
    //CREATE_PROG    Excel 
    address.setCreateProg(CREATE_PROG);

    address.setLastUpdTime(NOW);
    address.setLastUpdUserId(CREATE_USER_ID);
    address.setLastUpdProg(CREATE_PROG);
    address.setLastUpdVersion(1);

    cacheDao.addEntity(address);
    return address;
}

From source file:Import.ImportDonation.java

private AcDonateMember AddDoanteMember(XSSFRow row, ShPerson person) {
    AcDonateMember donateMember = new AcDonateMember();

    donateMember.setBusinessUnitId(person.getBusinessUnitId());

    donateMember.setDepartmentId(person.getBusinessUnitId());

    XSSFCell cell63 = row.getCell(63);
    Date oldDonateDate = getDateCellValueIsNotEmptySetError(cell63, 63);
    if (oldDonateDate == null) {
        oldDonateDate = new Date();
    }//from  ww w  .  j  a  v  a  2 s .c o  m
    String budgetYear = dateToBudgetYear(oldDonateDate);
    try {
        subBudgetYear = budgetYear.substring(2);
    } catch (NullPointerException nullEx) {
        validationError.append("  ").append(" [")
                .append(budgetYear).append(" ] ");
    }

    donateMember.setBudgetYear(budgetYear);

    //        XSSFCell cell68 = row.getCell(68);
    //        Date actorData = getDateCellValueIsNotEmptySetError(cell68);
    donateMember.setActorDate(oldDonateDate);

    donateMember.setPersonId(person.getPersonId());
    Integer personId = donateMember.getPersonId();
    if (donateMember.getPersonId() == null || donateMember.getPersonId() == 0) {
        personId = donateMember.getPersonId();
    }
    XSSFCell cell66 = row.getCell(66);
    String purPose = getStringCellValueNoSetError(cell66);
    try {
        donateMember.setPurpose(purPose.substring(0, 999));
    } catch (Exception e) {
        donateMember.setPurpose(purPose);
    }
    donateMember.setReceiveType(mode);

    XSSFCell cell62 = row.getCell(62);
    BigDecimal oldAmount = getBigDecimalCellValueNoSetError(cell62);
    donateMember.setOldAmount(oldAmount);

    // 
    switch (mode) {
    case STRING_C: {
        XSSFCell cell69 = row.getCell(69);
        String bankName = getStringCellValueSetError(cell69, 69);
        if (!isBlankOrNull(bankName)) {
            ShBank bank = cacheDao.findShBankByBankNameCache(bankName);
            try {
                donateMember.setCreditCardOwner(bank.getBankId());
            } catch (NullPointerException nullEx) {
            }
        }

        XSSFCell cell70 = row.getCell(70);
        String creditCardType = getStringCellValueSetError(cell70, 70);
        if (!isBlankOrNull(creditCardType)) {
            AcCreditCardType CreditCardType = cacheDao.findAcCreditCardTypeByNameCache(creditCardType);
            try {
                donateMember.setCreditCardType(CreditCardType.getCreditCardTypeId());
            } catch (NullPointerException nullEx) {
                setErrorValueInDB(70, creditCardType);
            }
        }

        XSSFCell cell71 = row.getCell(71);
        String creditCardNo = getStringCellValueSetError(cell71, 71);

        if (!isBlankOrNull(creditCardNo)) {
            //return null  lenght
            String creditCardNoCheck = ImportUtils.replaceCreditCardNo(creditCardNo, 16);
            if (creditCardNoCheck == null) {
                setErrorCustom(
                        "[ " + mapStringValidation.get(71) + " " + creditCardNo + " ]");
            } else {
                creditCardNo = creditCardNoCheck;
            }
        }

        donateMember.setCreditCardNo(creditCardNo);

        XSSFCell cell72 = row.getCell(72);
        String creditCardName = getStringCellValueNoSetError(cell72);

        donateMember.setCreditCardName(creditCardName);
        XSSFCell cell73 = row.getCell(73);
        final String creditCardExpYear = getStringCellValueNoSetError(cell73);

        //
        if (creditCardExpYear != null && !"".equals(creditCardExpYear)) {
            try {

                Integer creditCardExpYearInteger = Integer.valueOf(creditCardExpYear);
                Calendar calendar = Calendar.getInstance();
                int year = calendar.get(Calendar.YEAR);
                // 2000  2050  +- ? 10
                if ((creditCardExpYearInteger > 2050 || creditCardExpYearInteger < 2000)
                        || (creditCardExpYearInteger < (year + 10) && creditCardExpYearInteger > (year - 10))) {
                    setErrorCustom(73,
                            creditCardExpYear + " 2000  2050");
                }
            } catch (Exception e) {
                setErrorCustom(73, " " + creditCardExpYear);
            }
        }

        donateMember.setCreditCardExpYear(ImportUtils.subString(creditCardExpYear, 4, true));
        XSSFCell cell74 = row.getCell(74);
        final String creditCardExpMonth = getStringCellValueNoSetError(cell74);

        //
        if (creditCardExpMonth != null && !"".equals(creditCardExpMonth)) {
            try {
                Integer creditCardExpMonthInteger = Integer.valueOf(creditCardExpMonth);
                // 1  12
                if (creditCardExpMonthInteger > 12 || creditCardExpMonthInteger < 1) {
                    setErrorCustom(74,
                            creditCardExpMonth + " 1  12");
                }
            } catch (Exception e) {
                setErrorCustom(73, " " + creditCardExpMonth);
            }
        }

        donateMember.setCreditCardExpMonth(ImportUtils.subString(creditCardExpMonth, 2, true));
        XSSFCell cell79 = row.getCell(79);
        BigDecimal amount = getBigDecimalCellValueSetError(cell79, 79);
        donateMember.setAmount(amount);

        XSSFCell cell80 = row.getCell(80);
        Integer offDate = getIntegerCellValueSetError(cell80, 80);
        donateMember.setOffDate(offDate);
        break;
    }
    case STRING_B: {
        XSSFCell cell75 = row.getCell(75);
        String bankName = getStringCellValueSetError(cell75, 75);
        Integer bankId = null;
        if (!isBlankOrNull(bankName)) {
            ShBank bankDao = cacheDao.findShBankByBankNameCache(bankName);
            try {
                bankId = bankDao.getBankId();
                donateMember.setBankId(bankId);
            } catch (NullPointerException nullEx) {
                validationError.append(" [ ").append(mapStringValidation.get(75))
                        .append(bankName).append(" ?").append(" ] ");
            }
        }
        XSSFCell cell76 = row.getCell(76);
        String bankBranchName = getStringCellValueNoSetError(cell76);
        if (!isBlankOrNull(bankBranchName)) {
            ShBankBranch bankBranch = cacheDao.findShBankBranchByBankBranchNameAndBankIdCache(bankBranchName,
                    bankId);
            try {
                donateMember.setBankBranchId(bankBranch.getBankBranchId());
            } catch (NullPointerException nullEx) {
            }
        }

        XSSFCell cell77 = row.getCell(77);
        String bankAccount = getStringCellValueSetError(cell77, 77);
        if (!isBlankOrNull(bankAccount)) {
            //return null  lenght
            String bankAccountCheck = ImportUtils.replaceDash(bankAccount, 10);
            if (bankAccountCheck == null) {
                setErrorCustom(
                        "[ " + mapStringValidation.get(77) + " " + bankAccount + " ]");
            } else {
                bankAccount = bankAccountCheck;
            }
        }
        donateMember.setBankAccount(bankAccount);
        XSSFCell cell78 = row.getCell(78);
        String bankAccountName = getStringCellValueSetError(cell78, 78);
        donateMember.setBankAccountName(bankAccountName);
        XSSFCell cell79 = row.getCell(79);
        BigDecimal amount = getBigDecimalCellValueSetError(cell79, 79);
        donateMember.setAmount(amount);

        XSSFCell cell80 = row.getCell(80);
        Integer offDate = getIntegerCellValueSetError(cell80, 80);
        donateMember.setOffDate(offDate);
        break;
    }
    case STRING_D: {
        donateMember.setAmount(oldAmount);
        break;
    }
    }

    donateMember.setOldDonateDate(oldDonateDate);
    donateMember.setIsCancel(STRING_N);
    donateMember.setIsLockUpdate(STRING_N);
    XSSFCell cell64 = row.getCell(64);
    String bookNo = getStringFormatNoSetError(cell64, "##");

    XSSFCell cell65 = row.getCell(65);
    String receipt = getStringFormatNoSetError(cell65, "##");

    //        XSSFCell cell66 = row.getCell(66);
    //        String objective = getStringCellValueNoSetError(cell66); // 
    XSSFCell cell61 = row.getCell(61);
    String donateName = getStringCellValueNoSetError(cell61); // 

    String addition = getAddition(bookNo, receipt, dateToshortDate(oldDonateDate), purPose, donateName);
    donateMember.setAddition(addition);

    donateMember.setCreateTime(NOW);
    donateMember.setCreateUserId(CREATE_USER_ID);
    donateMember.setCreateProg(CREATE_PROG);

    donateMember.setLastUpdTime(NOW);
    donateMember.setLastUpdUserId(CREATE_USER_ID);
    donateMember.setLastUpdProg(CREATE_PROG);
    donateMember.setLastUpdVersion(1);

    //DONATE_MEMBER_NO   Gen ????
    try {
        String donateMemberNo = getFormDonatorNo(person.getBusinessUnitId(), budgetYear.substring(2));
        donateMember.setDonateMemberNo(donateMemberNo);
    } catch (NullPointerException e) {
        validationError.append(" [Gen DonateMemberNo ] ");
    }
    cacheDao.addEntity(donateMember);
    this.addDonateMemberStatus(donateMember);
    //        logln(" donateMemberId " + donateMember.getDonateMemberId()+ "\n");
    return donateMember;
}