List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getRowNum
@Override public int getRowNum()
From source file:org.hsh.bfr.db.imports.GeneralXLSImporter.java
License:Open Source License
public String doImport(final String filename, final JProgressBar progress, final boolean showResults) { Runnable runnable = new Runnable() { public void run() { try { if (progress != null) { progress.setVisible(true); progress.setStringPainted(true); progress.setString("Importiere Excel Datei..."); progress.setMinimum(0); }//from w ww. j a v a 2 s.co m InputStream is = null; System.out.println(filename); if (filename.startsWith("http://")) { URL url = new URL(filename); URLConnection uc = url.openConnection(); is = uc.getInputStream(); } else if (filename.startsWith("/org/hsh/bfr/db/res/")) { is = this.getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet; HSSFRow row; int numSuccess = 0; int numFailed = 0; String unusedFields = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); String tableName = sheet.getSheetName(); MyTable myT = DBKernel.myDBi.getTable(tableName); if (myT != null) { int numRows = sheet.getLastRowNum(); if (progress != null) { progress.setMaximum(numRows); progress.setValue(0); } row = sheet.getRow(0); String sql1 = ""; String sql2 = ""; String sql3 = ""; Vector<String> codeSql1 = new Vector<>(); Vector<String> codeSql2 = new Vector<>(); LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>(); int numCols = row.getLastCellNum(); String[] fieldNames = new String[numCols]; String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT); String[] ffieldTypes = new String[numCols]; MyTable[] myForeignTables = new MyTable[numCols]; String[] kzS = new String[numCols]; String[] dbFieldnames = new String[numCols]; int lfdCol = 0; Hashtable<String, String> dbFieldNames = new Hashtable<>(); for (int j = 0; j < numCols; j++) { String fieldName = row.getCell(j).getStringCellValue(); fieldNames[j] = fieldName; int ffe; String dbFieldName = getDBFieldName(fieldName, myT, takecareofID); if (dbFieldName != null) { String ft = getForeignTable(dbFieldName, myT); if (ft != null && ft.equals("DoubleKennzahlen")) { kzS[j] = getKZ(fieldName, dbFieldName); dbFieldnames[j] = dbFieldName; } else if (!dbFieldNames.containsKey(dbFieldName)) { dbFieldNames.put(dbFieldName, dbFieldName); sql1 += DBKernel.delimitL(dbFieldName) + ","; sql2 += "?,"; sql3 += DBKernel.delimitL(dbFieldName) + "=?,"; lfdCol++; } fieldTypes[j] = getType(dbFieldName, myT, takecareofID); } else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) { if (!foreignTables.containsKey(myT.getForeignFields()[ffe])) foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>()); ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false); foreignTables.get(myT.getForeignFields()[ffe]).add(j); myForeignTables[j] = myT.getForeignFields()[ffe]; } else if (DBKernel.showHierarchic(tableName) && fieldName.toLowerCase().endsWith("-code")) { codeSql1.add(DBKernel.delimitL("CodeSystem") + "," + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis")); codeSql2.add("'" + fieldName.substring(0, fieldName.length() - "-code".length()) + "',?,?"); } else if (!fieldName.equalsIgnoreCase("id")) { unusedFields += "," + fieldName; } } if (sql1.length() > 0 && sql2.length() > 0) { String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " (" + sql1.substring(0, sql1.length() - 1) + ") VALUES (" + sql2.substring(0, sql2.length() - 1) + ")"; PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); int idCol = lfdCol + 1; sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET " + sql3.substring(0, sql3.length() - 1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql); PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()]; boolean doCode[] = new boolean[codeSql1.size()]; int codesI; for (codesI = 0; codesI < codeSql1.size(); codesI++) { sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName)) + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI) + ")"; psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql); } LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>(); LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>(); for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) { Vector<Integer> vs = entry.getValue(); String ssql1 = "", ssql2 = "", ssql3 = ""; for (int ii = 0; ii < vs.size(); ii++) { ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]); ssql2 += ",?"; ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?"; } if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) { sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename()) + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1) + ")"; psForeign.put(entry.getKey(), DBKernel.getDBConnection() .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)); sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename()) + " SET " + ssql3.substring(1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; psForeignUpdate.put(entry.getKey(), DBKernel.getDBConnection().prepareStatement(sql)); } } LinkedHashMap<Object, String> hashBL = null; Iterator<Row> rows = sheet.rowIterator(); int lfd = 0; while (rows.hasNext()) { row = (HSSFRow) rows.next(); boolean setID = false; Integer lastID = null; ps.clearParameters(); psUpdate.clearParameters(); for (codesI = 0; codesI < codeSql1.size(); codesI++) { psCodes[codesI].clearParameters(); doCode[codesI] = false; } LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>(); for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate.entrySet()) { MyTable myT1 = entry.getKey(); psForeign.get(myT1).clearParameters(); psForeignUpdate.get(myT1).clearParameters(); lfdColsForeign.put(myT1, 0); } if (row.getRowNum() > 0) { lfdCol = 0; codesI = 0; Object[] kzVal = new Object[numCols]; for (int j = 0; j < numCols; j++) { if (fieldTypes[j] != null) { /* * if (fieldNames[j].equals( * "Bundesland")) { hashBL = * DBKernel * .myDBi.getHashMap("County"); * } else { hashBL = null; } */ lfdCol++; if (fieldTypes[j].startsWith("VARCHAR(") || fieldTypes[j].startsWith("CHAR(") || fieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL); else if (fieldTypes[j].equals("BOOLEAN")) manageBoolean(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("INTEGER")) manageInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("BIGINT")) manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DOUBLE")) { if (kzS[j] != null) { lfdCol--; //System.err.println(dbFieldnames[j] + "\t" + kzS[j]); if (DBKernel.kzIsString(kzS[j])) kzVal[j] = manageString(null, null, lfdCol, row.getCell(j)); else if (DBKernel.kzIsBoolean(kzS[j])) kzVal[j] = manageBoolean(null, null, lfdCol, row.getCell(j)); else kzVal[j] = manageDouble(null, null, lfdCol, row.getCell(j)); } else { manageDouble(ps, psUpdate, lfdCol, row.getCell(j)); } } else System.err .println("Wasn hier los? Undefinierter Feldtyp???? ->\t" + fieldNames[j]); } else if (myForeignTables[j] != null && ffieldTypes[j] != null) { lfdColsForeign.put(myForeignTables[j], lfdColsForeign.get(myForeignTables[j]) + 1); if (ffieldTypes[j].startsWith("VARCHAR(") || ffieldTypes[j].startsWith("CHAR(") || ffieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j), hashBL); else if (ffieldTypes[j].equals("BOOLEAN")) manageBoolean(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("INTEGER")) manageInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("BIGINT")) manageBigInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("DOUBLE")) { manageDouble(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); } else System.err.println(fieldNames[j] + " Feldtype????"); } else if (fieldNames[j].equals("ID")) { lastID = manageInteger(null, null, 0, row.getCell(j)); if (lastID != null) { if (DBKernel.hasID(tableName, lastID.intValue())) { psUpdate.setInt(idCol, lastID.intValue()); setID = true; } } } else if (DBKernel.showHierarchic(tableName) && fieldNames[j].toLowerCase().endsWith("-code")) { String code = manageString(psCodes[codesI], null, 1, row.getCell(j)); if (code != null && code.length() > 0) doCode[codesI] = true; codesI++; } else { //System.out.println(fieldNames[j]); } } try { if (setID) { psUpdate.execute(); } else { if (ps.executeUpdate() > 0) {// execute() lastID = DBKernel.getLastInsertedID(ps); } else { System.err.println("W"); } } numSuccess++; if (lastID != null) { for (int j = 0; j < numCols; j++) { if (dbFieldnames[j] != null && kzVal[j] != null) { DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j], lastID, null, kzS[j], kzVal[j]); } } for (codesI = 0; codesI < codeSql1.size(); codesI++) { if (doCode[codesI]) { psCodes[codesI].setInt(2, lastID); try { psCodes[codesI].execute(); numSuccess++; } catch (SQLException e1) { numFailed++; System.err.println(psCodes[codesI]); } } } for (Map.Entry<MyTable, PreparedStatement> entry : psForeign .entrySet()) { MyTable myT1 = entry.getKey(); MyTable[] foreignTs = myT.getForeignFields(); for (int ii = 0; ii < foreignTs.length; ii++) { if (foreignTs[ii] != null && foreignTs[ii].equals(myT1)) { if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT int lID = DBKernel .getLastInsertedID(psForeign.get(myT1)); // Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt DBKernel.sendRequest("UPDATE " + DBKernel.delimitL(tableName) + " SET " + DBKernel.delimitL(myT.getFieldNames()[ii]) + "=" + lID + " WHERE " + DBKernel.delimitL("ID") + "=" + lastID, false); } break; } } } /* * for (int j=0;j<numCols;j++) { * if (myForeignTables[j] != * null && ffieldTypes[j] != * null) { MyTable[] foreignTs = * myT.getForeignFields(); for * (int * ii=0;ii<foreignTs.length; * ii++) { if (foreignTs[ii] != * null && foreignTs[ii].equals( * myForeignTables[j])) { if * (psForeign * .get(myForeignTables * [j]).executeUpdate() > 0) { * // INSERT int lID = * DBKernel.getLastInsertedID * (psForeign * .get(myForeignTables[j])); * DBKernel * .sendRequest("UPDATE " + * DBKernel.delimitL(tableName) * + " SET " + * DBKernel.delimitL( * myT.getFieldNames()[ii]) + * "=" + lID + " WHERE " + * DBKernel.delimitL("ID") + "=" * + lastID, false); } break; } * } } } */ } } catch (Exception e1) { numFailed++; MyLogger.handleMessage(ps.toString()); MyLogger.handleException(e1); } } if (progress != null) { lfd++; progress.setValue(lfd); } } } myT.doMNs(); if (progress != null) { // Refreshen: MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable(); if (myDB.getActualTable() != null) { String actTablename = myDB.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myDB.setTable(myDB.getActualTable()); } } MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree(); if (myTR.getActualTable() != null) { String actTablename = myTR.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myTR.setTable(myTR.getActualTable()); } } } } else { System.err.println(tableName + " nicht in DB???"); } } if (progress != null) { progress.setVisible(false); } String log = numSuccess + " erfolgreiche Importe.\n"; log += numFailed + " fehlgeschlagene Importe.\n"; if (unusedFields.length() > 0) log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n"; if (showResults) { InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null); ib.setVisible(true); } else { System.out.println("GeneralXLSImporter (" + filename + "):\n" + log); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { MyLogger.handleException(e); } return ""; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * /*from w w w . ja v a 2s . c o m*/ * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
From source file:org.mifos.application.importexport.xls.XlsClientsImporter.java
License:Open Source License
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 {//from www .j ava2 s.co m 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)) { clientGlobalNum = null; // generate number } 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 { // monthly recurrence // make sure weekly details are empty if (recurEveryWeek != null || weeklyMeetingDay != null) { throw new RowException( getMessage(XlsMessageConstants.WEEKLY_MEETING_DETAILS_NOT_EMPTY)); } if (opt1Day == null) { // option 2 // 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 { // option 1 // 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); }
From source file:org.ofbiz.product.spreadsheetimport.ImportProductServices.java
License:Apache License
/** * This method is responsible to import spreadsheet data into "Product" and * "InventoryItem" entities into database. The method uses the * ImportProductHelper class to perform its operation. The method uses "Apache * POI" api for importing spreadsheet (xls files) data. * * Note : Create the spreadsheet directory in the ofbiz home folder and keep * your xls files in this folder only./*from w w w. j a va 2s . c o m*/ * * @param dctx the dispatch context * @param context the context * @return the result of the service execution */ public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx, Map<String, ? extends Object> context) { Delegator delegator = dctx.getDelegator(); Locale locale = (Locale) context.get("locale"); // System.getProperty("user.dir") returns the path upto ofbiz home // directory String path = System.getProperty("user.dir") + "/spreadsheet"; List<File> fileItems = FastList.newInstance(); if (UtilValidate.isNotEmpty(path)) { File importDir = new File(path); if (importDir.isDirectory() && importDir.canRead()) { File[] files = importDir.listFiles(); // loop for all the containing xls file in the spreadsheet // directory for (int i = 0; i < files.length; i++) { if (files[i].getName().toUpperCase().endsWith("XLS")) { fileItems.add(files[i]); } } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale)); } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale)); } if (fileItems.size() < 1) { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale) + path); } for (File item : fileItems) { // read all xls file and create workbook one by one. List<Map<String, Object>> products = FastList.newInstance(); List<Map<String, Object>> inventoryItems = FastList.newInstance(); POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(new FileInputStream(item)); wb = new HSSFWorkbook(fs); } catch (IOException e) { Debug.logError("Unable to read or create workbook from file", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotCreateWorkbookFromFile", locale)); } // get first sheet HSSFSheet sheet = wb.getSheetAt(0); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (row != null) { // read productId from first column "sheet column index // starts from 0" HSSFCell cell2 = row.getCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); String productId = cell2.getRichStringCellValue().toString(); // read QOH from ninth column HSSFCell cell5 = row.getCell(5); BigDecimal quantityOnHand = BigDecimal.ZERO; if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) quantityOnHand = new BigDecimal(cell5.getNumericCellValue()); // check productId if null then skip creating inventory item // too. boolean productExists = ImportProductHelper.checkProductExists(productId, delegator); if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) { products.add(ImportProductHelper.prepareProduct(productId)); if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0) inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand, delegator.getNextSeqId("InventoryItem"))); else inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO, delegator.getNextSeqId("InventoryItem"))); } int rowNum = row.getRowNum() + 1; if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) { Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module); } } } // create and store values in "Product" and "InventoryItem" entity // in database for (int j = 0; j < products.size(); j++) { GenericValue productGV = delegator.makeValue("Product", products.get(j)); GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j)); if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) { try { delegator.create(productGV); delegator.create(inventoryItemGV); } catch (GenericEntityException e) { Debug.logError("Cannot store product", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotStoreProduct", locale)); } } } int uploadedProducts = products.size() + 1; if (products.size() > 0) Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module); } return ServiceUtil.returnSuccess(); }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Takes each row of an Excel sheet and put it into DataImportProduct. * @param sheet the Excel sheet//from w ww.j a v a2 s . c om * @return a <code>Collection</code> of DataImportProduct entities * @throws RepositoryException if an error occurs */ protected Collection<? extends EntityInterface> createDataImportProducts(HSSFSheet sheet) throws RepositoryException { int sheetLastRowNumber = sheet.getLastRowNum(); List<DataImportProduct> products = FastList.newInstance(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (isNotEmpty(row)) { // row index starts at 0 here but is actually 1 in Excel int rowNum = row.getRowNum() + 1; // read productId from first column "sheet column index // starts from 0" String id = readStringCell(row, 0); if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("productId")) { Debug.logWarning("Row number " + rowNum + " not imported from Products tab: invalid ID value [" + id + "].", MODULE); continue; } DataImportProduct product = new DataImportProduct(); product.setProductId(id); product.setProductName(readStringCell(row, 1)); product.setInternalName(readStringCell(row, 1)); product.setProductTypeId(readStringCell(row, 2)); product.setDescription(readStringCell(row, 3)); product.setPrice(readBigDecimalCell(row, 4)); product.setPriceCurrencyUomId(readStringCell(row, 5)); product.setSupplierPartyId(readStringCell(row, 6)); product.setPurchasePrice(readBigDecimalCell(row, 7)); products.add(product); } } return products; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Take each row of an Excel sheet and put it into DataImportSupplier. * @param sheet the Excel sheet/*from w ww . j a v a 2 s . c om*/ * @return a <code>Collection</code> of DataImportSupplier entities * @throws RepositoryException if an error occurs */ protected Collection<? extends EntityInterface> createDataImportSuppliers(HSSFSheet sheet) throws RepositoryException { List<DataImportSupplier> suppliers = FastList.newInstance(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (isNotEmpty(row)) { // row index starts at 0 here but is actually 1 in Excel int rowNum = row.getRowNum() + 1; // read supplierId from first column "sheet column index // starts from 0" String id = readStringCell(row, 0); if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("supplierId")) { Debug.logWarning("Row number " + rowNum + " not imported from Suppliers tab: invalid ID value [" + id + "].", MODULE); continue; } DataImportSupplier supplier = new DataImportSupplier(); supplier.setSupplierId(id); supplier.setSupplierName(readStringCell(row, 1)); supplier.setAddress1(readStringCell(row, 2)); supplier.setAddress2(readStringCell(row, 3)); supplier.setCity(readStringCell(row, 4)); supplier.setStateProvinceGeoId(readStringCell(row, 5)); supplier.setPostalCode(readStringCell(row, 6)); supplier.setCountryGeoId(readStringCell(row, 7)); supplier.setPrimaryPhoneCountryCode(readStringCell(row, 8)); supplier.setPrimaryPhoneAreaCode(readStringCell(row, 9)); supplier.setPrimaryPhoneNumber(readStringCell(row, 10)); supplier.setNetPaymentDays(readLongCell(row, 11)); supplier.setIsIncorporated(readStringCell(row, 12)); supplier.setFederalTaxId(readStringCell(row, 13)); supplier.setRequires1099(readStringCell(row, 14)); suppliers.add(supplier); } } return suppliers; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Take each row of an Excel sheet and put it into DataImportCustomer. * @param sheet the Excel sheet//from w w w. ja v a 2s . co m * @return a <code>Collection</code> of DataImportCustomer entities * @throws RepositoryException if an error occurs */ protected Collection<? extends EntityInterface> createDataImportCustomers(HSSFSheet sheet) throws RepositoryException { List<DataImportCustomer> customers = FastList.newInstance(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (isNotEmpty(row)) { // row index starts at 0 here but is actually 1 in Excel int rowNum = row.getRowNum() + 1; // read customerId from first column "sheet column index // starts from 0" String id = readStringCell(row, 0); if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("customerId")) { Debug.logWarning("Row number " + rowNum + " not imported from Customers tab: invalid ID value [" + id + "].", MODULE); continue; } DataImportCustomer customer = new DataImportCustomer(); customer.setCustomerId(id); int rowCount = 1; // keep track of the row customer.setCompanyName(this.readStringCell(row, rowCount++)); customer.setFirstName(this.readStringCell(row, rowCount++)); customer.setLastName(this.readStringCell(row, rowCount++)); customer.setAttnName(this.readStringCell(row, rowCount++)); customer.setAddress1(this.readStringCell(row, rowCount++)); customer.setAddress2(this.readStringCell(row, rowCount++)); customer.setCity(this.readStringCell(row, rowCount++)); customer.setStateProvinceGeoId(this.readStringCell(row, rowCount++)); customer.setPostalCode(this.readStringCell(row, rowCount++)); customer.setPostalCodeExt(this.readStringCell(row, rowCount++)); customer.setCountryGeoId(this.readStringCell(row, rowCount++)); customer.setPrimaryPhoneCountryCode(this.readStringCell(row, rowCount++)); customer.setPrimaryPhoneAreaCode(this.readStringCell(row, rowCount++)); customer.setPrimaryPhoneNumber(this.readStringCell(row, rowCount++)); customer.setPrimaryPhoneExtension(this.readStringCell(row, rowCount++)); customer.setSecondaryPhoneCountryCode(this.readStringCell(row, rowCount++)); customer.setSecondaryPhoneAreaCode(this.readStringCell(row, rowCount++)); customer.setSecondaryPhoneNumber(this.readStringCell(row, rowCount++)); customer.setSecondaryPhoneExtension(this.readStringCell(row, rowCount++)); customer.setFaxCountryCode(this.readStringCell(row, rowCount++)); customer.setFaxAreaCode(this.readStringCell(row, rowCount++)); customer.setFaxNumber(this.readStringCell(row, rowCount++)); customer.setDidCountryCode(this.readStringCell(row, rowCount++)); customer.setDidAreaCode(this.readStringCell(row, rowCount++)); customer.setDidNumber(this.readStringCell(row, rowCount++)); customer.setDidExtension(this.readStringCell(row, rowCount++)); customer.setEmailAddress(this.readStringCell(row, rowCount++)); customer.setWebAddress(this.readStringCell(row, rowCount++)); customer.setDiscount(this.readBigDecimalCell(row, rowCount++)); customer.setPartyClassificationTypeId(this.readStringCell(row, rowCount++)); customer.setCreditCardNumber(this.readStringCell(row, rowCount++)); customer.setCreditCardExpDate(this.readStringCell(row, rowCount++)); customer.setOutstandingBalance(this.readBigDecimalCell(row, rowCount++)); customer.setCreditLimit(this.readBigDecimalCell(row, rowCount++)); customer.setCurrencyUomId(this.readStringCell(row, rowCount++)); customer.setDisableShipping(this.readStringCell(row, rowCount++)); customer.setNetPaymentDays(this.readLongCell(row, rowCount++)); customer.setShipToCompanyName(this.readStringCell(row, rowCount++)); customer.setShipToFirstName(this.readStringCell(row, rowCount++)); customer.setShipToLastName(this.readStringCell(row, rowCount++)); customer.setShipToAttnName(this.readStringCell(row, rowCount++)); customer.setShipToAddress1(this.readStringCell(row, rowCount++)); customer.setShipToAddress2(this.readStringCell(row, rowCount++)); customer.setShipToCity(this.readStringCell(row, rowCount++)); customer.setShipToStateProvinceGeoId(this.readStringCell(row, rowCount++)); customer.setShipToPostalCode(this.readStringCell(row, rowCount++)); customer.setShipToPostalCodeExt(this.readStringCell(row, rowCount++)); customer.setShipToStateProvGeoName(this.readStringCell(row, rowCount++)); customer.setShipToCountryGeoId(this.readStringCell(row, rowCount++)); customer.setNote(this.readStringCell(row, rowCount++)); customers.add(customer); } } return customers; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Take each row of an Excel sheet and put it into DataImportInventory. * @param sheet the Excel sheet/* w w w. j av a 2 s . c o m*/ * @return a <code>Collection</code> of DataImportInventory entities * @throws RepositoryException if an error occurs */ protected Collection<? extends EntityInterface> createDataImportInventory(HSSFSheet sheet) throws RepositoryException { List<DataImportInventory> inventory = FastList.newInstance(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (isNotEmpty(row)) { // row index starts at 0 here but is actually 1 in Excel int rowNum = row.getRowNum() + 1; // read itemId from first column "sheet column index // starts from 0" String id = readStringCell(row, 0); if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("itemId")) { Debug.logWarning("Row number " + rowNum + " not imported from Inventory tab: invalid ID value [" + id + "].", MODULE); continue; } DataImportInventory inventoryItem = new DataImportInventory(); inventoryItem.setItemId(id); inventoryItem.setProductId(this.readStringCell(row, 1)); inventoryItem.setFacilityId(this.readStringCell(row, 2)); inventoryItem.setAvailableToPromise(this.readBigDecimalCell(row, 3)); inventoryItem.setOnHand(this.readBigDecimalCell(row, 4)); inventoryItem.setMinimumStock(this.readBigDecimalCell(row, 5)); inventoryItem.setReorderQuantity(this.readBigDecimalCell(row, 6)); inventoryItem.setDaysToShip(this.readBigDecimalCell(row, 7)); inventoryItem.setInventoryValue(this.readBigDecimalCell(row, 8)); inventory.add(inventoryItem); } } return inventory; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Take each row of an Excel sheet and put it into DataImportGlAccount. * @param sheet the Excel sheet//from www . ja v a2 s. co m * @return a <code>Collection</code> of DataImportGlAccount entities * @throws RepositoryException if an error occurs */ protected Collection<? extends EntityInterface> createDataImportGlAccounts(HSSFSheet sheet) throws RepositoryException { List<DataImportGlAccount> glAccounts = FastList.newInstance(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (isNotEmpty(row)) { // row index starts at 0 here but is actually 1 in Excel int rowNum = row.getRowNum() + 1; // read glAccountrId from first column "sheet column index // starts from 0" String id = readStringCell(row, 0); if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("glAccountId")) { Debug.logWarning("Row number " + rowNum + " not imported from GL Accounts tab: invalid ID value [" + id + "].", MODULE); continue; } DataImportGlAccount glAccount = new DataImportGlAccount(); glAccount.setGlAccountId(id); glAccount.setParentGlAccountId(this.readStringCell(row, 1)); glAccount.setClassification(this.readStringCell(row, 2)); glAccount.setAccountName(this.readStringCell(row, 3)); glAccounts.add(glAccount); } } return glAccounts; }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
private static void walk(boolean[][] merged, HSSFDataFormat dataFormat, HSSFRow row, Handler handler) { if (row != null) { for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) { HSSFCell cell = row.getCell((short) cellNum); if (cell != null && !merged[row.getRowNum()][cellNum]) { short dataFormatId = cell.getCellStyle().getDataFormat(); if (dataFormatId > 0) { String format = dataFormat.getFormat(dataFormatId); final Matcher matcher = FORMAT_XPATH.matcher(format); if (matcher.find()) { // Found XPath expression String xpath = matcher.group(1); int separtorPosition = xpath.indexOf('|'); String sourceXPath = separtorPosition == -1 ? xpath : xpath.substring(0, separtorPosition); String targetXPath = separtorPosition == -1 ? null : xpath.substring(separtorPosition + 1); handler.cell(cell, sourceXPath, targetXPath); }//w w w . j a v a2 s. co m } } } } }