List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getRowNum
@Override public int getRowNum()
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
/** * @return maximum 1-base index of column that were rendered, zero if none *//*from ww w.j a v a 2 s. co m*/ protected int processRow(CellRangeAddress[][] mergedRanges, HSSFRow row, Element tableRowElement) { final HSSFSheet sheet = row.getSheet(); final short maxColIx = row.getLastCellNum(); if (maxColIx <= 0) { return 0; } final List emptyCells = new ArrayList(maxColIx); if (this.isOutputRowNumbers()) { Element tableRowNumberCellElement = this.htmlDocumentFacade.createTableHeaderCell(); this.processRowNumber(row, tableRowNumberCellElement); emptyCells.add(tableRowNumberCellElement); } int maxRenderedColumn = 0; for (int colIx = 0; colIx < maxColIx; colIx++) { if (!this.isOutputHiddenColumns() && sheet.isColumnHidden(colIx)) { continue; } CellRangeAddress range = AbstractExcelUtils.getMergedRange(mergedRanges, row.getRowNum(), colIx); if ((range != null) && ((range.getFirstColumn() != colIx) || (range.getFirstRow() != row.getRowNum()))) { continue; } HSSFCell cell = row.getCell(colIx); int divWidthPx = 0; if (this.isUseDivsToSpan()) { divWidthPx = AbstractExcelConverter.getColumnWidth(sheet, colIx); boolean hasBreaks = false; for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++) { if (!this.isOutputHiddenColumns() && sheet.isColumnHidden(nextColumnIndex)) { continue; } if ((row.getCell(nextColumnIndex) != null) && !this.isTextEmpty(row.getCell(nextColumnIndex))) { hasBreaks = true; break; } divWidthPx += AbstractExcelConverter.getColumnWidth(sheet, nextColumnIndex); } if (!hasBreaks) { divWidthPx = Integer.MAX_VALUE; } } Element tableCellElement = this.htmlDocumentFacade.createTableCell(); if (range != null) { if (range.getFirstColumn() != range.getLastColumn()) { tableCellElement.setAttribute("colspan", String.valueOf((range.getLastColumn() - range.getFirstColumn()) + 1)); } if (range.getFirstRow() != range.getLastRow()) { tableCellElement.setAttribute("rowspan", String.valueOf((range.getLastRow() - range.getFirstRow()) + 1)); } } boolean emptyCell; if (cell != null) { emptyCell = this.processCell(cell, tableCellElement, AbstractExcelConverter.getColumnWidth(sheet, colIx), divWidthPx, row.getHeight() / 20f); } else { emptyCell = true; } if (emptyCell) { emptyCells.add(tableCellElement); } else { for (Iterator iterator = emptyCells.iterator(); iterator.hasNext();) { Element emptyCellElement = (Element) iterator.next(); tableRowElement.appendChild(emptyCellElement); } emptyCells.clear(); tableRowElement.appendChild(tableCellElement); maxRenderedColumn = colIx; } } return maxRenderedColumn + 1; }
From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java
License:Open Source License
public boolean 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); }//ww w .j ava 2 s .com 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("/de/bund/bfr/knime/openkrise/db/res/")) { is = this.getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } try (HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is))) { 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); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { MyLogger.handleException(e); } return true; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
private void addImageInfo(final HSSFRow row, final WorkbenchRow wbRow) { for (Integer c : cardImageCols) { HSSFCell imgCell = row.getCell(c); if (imgCell != null) { String imageSpec[] = imgCell.getRichStringCellValue().getString().split("\\t"); String imagePath = imageSpec[0]; String attachToTblName = imageSpec.length > 1 ? imageSpec[1] : null; if (imagePath != null) { try { wbRow.addImage(new File(imagePath), attachToTblName); } catch (IOException e) { //edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); //edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSImport.class, e); wbRow.addImagePath(imagePath, attachToTblName); UIRegistry.getStatusBar().setErrorMessage(e.getMessage()); StringBuilder errMsg = new StringBuilder(getResourceString("WB_IMG_IMPORT_ERROR")); errMsg.append(": "); errMsg.append(getResourceString("WB_ROW")); errMsg.append(" "); errMsg.append(row.getRowNum()); errMsg.append(", "); errMsg.append(imagePath); messages.add(errMsg.toString()); }// www.jav a2 s. co m } } } }
From source file:eu.squadd.timesheets.eolas.TimeTemplate.java
public String prepareTimesheet(String[] args) { String response = null;//from w w w . j a v a 2s .co m try { String[] ym = args[0].split("/"); month = Integer.parseInt(ym[0]); year = Integer.parseInt(ym[1]); Calendar cal = Calendar.getInstance(TimeZone.getDefault()); cal.set(Calendar.YEAR, year); cal.set(Calendar.MONTH, month - 1); int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH); monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH); String periodName = monthName + "-" + year; cal.set(Calendar.DATE, 1); String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime()); System.out.println("Month: " + periodName); System.out.println("Days in month: " + days); System.out.println("Month starts in: " + dayOfWeek); Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017; Map<String, String> holidays = this.extractHolidays(args); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template)); HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0); HSSFRow currentRow; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); sheet.getRow(4).getCell(1).setCellValue(periodName); int row = 7; int startRow = 0; int i = 1; while (i <= days) { currentRow = sheet.getRow(row); if (currentRow.getRowNum() > 47) break; String day = currentRow.getCell(0).getStringCellValue(); if (day.startsWith("Total")) { evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); row++; continue; } if (startRow == 0) { if (dayOfWeek.equals(day.substring(0, 3))) { startRow = currentRow.getRowNum(); System.out.println("Starting row found: " + startRow + 1); } else { row++; continue; } } cal.set(Calendar.DATE, i); String date = sdf.format(cal.getTime()); if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null && holidays.get(date) == null) { currentRow.getCell(1).setCellValue(date); currentRow.getCell(2).setCellValue(defaultHours); // regular hours //currentRow.getCell(3).setCellValue(defaultHours); // overtime hours currentRow.getCell(4).setCellValue(defaultHours); // total hours } i++; row++; } currentRow = sheet.getRow(46); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); currentRow = sheet.getRow(47); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); response = outFilePath.replace("#MONTH#", periodName); wb.write(new FileOutputStream(response)); } catch (IOException ex) { Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Timesheet created."); return response; }
From source file:file.open.util.parse.XlsParser.java
License:Open Source License
public String[] splitLine() throws Exception { if (m_iCurrentRow == m_iNbRows) { return null; }/* w w w . j a v a 2 s . c o m*/ HSSFRow row = m_sheet.getRow(m_iCurrentRow); if (row == null) { return null; } else { int cellIndex = 0; int noOfCells = row.getPhysicalNumberOfCells(); String[] values = new String[noOfCells]; short firstCellNum = row.getFirstCellNum(); short lastCellNum = row.getLastCellNum(); if (firstCellNum >= 0 && lastCellNum >= 0) { for (short iCurrent = firstCellNum; iCurrent < lastCellNum; iCurrent++) { HSSFCell cell = (HSSFCell) row.getCell((int) iCurrent); if (cell == null) { values[iCurrent] = ""; cellIndex++; continue; } else { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: double value = cell.getNumericCellValue(); if (HSSFDateUtil.isCellDateFormatted(cell)) { if (HSSFDateUtil.isValidExcelDate(value)) { Date date = HSSFDateUtil.getJavaDate(value); SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING); values[iCurrent] = dateFormat.format(date); } else { throw new Exception("Invalid Date value found at row number " + row.getRowNum() + " and column number " + cell.getNumericCellValue()); } } else { values[iCurrent] = value + ""; } break; case HSSFCell.CELL_TYPE_STRING: values[iCurrent] = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: values[iCurrent] = null; break; default: values[iCurrent] = null; } } } } m_iCurrentRow++; return values; } }
From source file:gatebass.utils.exel.POIExcelReader.java
/** * 41 This method is used to display the Excel content to command line. 42 * * * @param xlsPath/*from ww w .j a v a 2 s . c om*/ */ @SuppressWarnings("unchecked") public void displayFromExcel(String xlsPath) { // end_row = 2242; InputStream inputStream = null; try { inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; int dd = 0; try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); List<Individuals> individualses = new ArrayList<>(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // if (row.getRowNum() >= end_row) { // break; // } if (row.getRowNum() <= start_row) { continue; } dd = row.getRowNum(); // if (row.getRowNum() == 0 // || row.getRowNum() < 195 || row.getRowNum() > 250 // ) { // continue; // } Individuals individuals = null; // display row number in the console. // System.out.println("Row No.: " + row.getRowNum()); // once get a row its time to iterate through cells. Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // System.out.println("Cell No.: " + cell.getCellNum()); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellNum()) { case 0: individuals = new Individuals(); // individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString())); break; case 1: try { individuals.setCard_id(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setCard_id(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; // case 2: // if (!cell.getRichStringCellValue().getString().isEmpty()) { // } // break; case 3: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setFirst_name(cell.getRichStringCellValue().getString()); } break; case 4: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setLast_name(cell.getRichStringCellValue().getString()); } break; case 6: try { individuals.setNational_id(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setNational_id(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 10: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setFirst_name_ENG(cell.getRichStringCellValue().getString()); } break; case 16: try { individuals.setPostal_code(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setPostal_code(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 17: try { individuals.setId_number(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setId_number(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 18: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setBirth_day(databaseHelper.historyDao.getFirst("date", cell.getRichStringCellValue().getString().substring(2))); } break; case 19: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setFather_first_name(cell.getRichStringCellValue().getString()); } break; case 20: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setBirth_state(cell.getRichStringCellValue().getString()); } break; case 21: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setIssued(cell.getRichStringCellValue().getString()); } break; case 22: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setStreet_address(cell.getRichStringCellValue().getString()); } break; case 23: String ss = cell.getRichStringCellValue().getString(); individuals.setVeteran_status(BEDONE_KART); if (!ss.isEmpty()) { if (ss.contains("?")) { individuals.setVeteran_status(MOAF); } else if (ss.contains("")) { individuals.setVeteran_status(PAYAN_KHEDMAT); } } break; case 25: try { individuals.setMobile(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setMobile(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 26: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setAcademic_degree(cell.getRichStringCellValue().getString()); } break; case 27: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setField_of_study(cell.getRichStringCellValue().getString()); } break; case 28: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setReligion(cell.getRichStringCellValue().getString()); } break; case 34: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setHave_soe_pishine(true); } break; case 35: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setComments(cell.getRichStringCellValue().getString()); } break; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. // System.out.println("Numeric value: " + cell.getNumericCellValue()); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); // System.out.println("String value: " + richTextString.getString()); break; } default: { // types other than String and Numeric. // System.out.println("Type not supported."); break; } } } String split = FileSystems.getDefault().getSeparator(); individuals.setFilesPatch( "data" + split + "1394" + split + dd / 50 + split + individuals.getNational_id() + split); File imageFile = new File( "d://test//Images-Personal-Gatepass//" + individuals.getCard_id() + ".jpg"); if (imageFile.exists()) { individuals.setPicture_address(individuals.getNational_id() + "-pic"); copyImageFile(imageFile.getAbsolutePath(), server + individuals.getFilesPatch(), individuals.getPicture_address()); individuals.setPicture_address( individuals.getPicture_address() + getFileExtension(imageFile.getAbsolutePath())); } individualses.add(individuals); // databaseHelper.individualsDao.createOrUpdate(individuals, dd); } databaseHelper.individualsDao.insertList(individualses); } catch (Exception e) { e.printStackTrace(); } }
From source file:gatebass.utils.exel.POIExcelReader.java
@SuppressWarnings("unchecked") public void compnaiesFromExcel(String xlsPath) { InputStream inputStream = null; try {/*ww w . j av a 2 s .co m*/ inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); boolean check = false; while (rows.hasNext()) { check = false; HSSFRow row = (HSSFRow) rows.next(); // if (row.getRowNum() >= end_row) { // break; // } if (row.getRowNum() <= start_row) { continue; } Companies companies = new Companies(); // System.out.println("Row No.: " + row.getRowNum()); String companyName = row.getCell(2).getRichStringCellValue().getString(); if (!companyName.isEmpty()) { Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_fa", companyName); if (companiesTEMP == null) { check = true; companies.setCompany_fa(companyName); companies.setActive(true); } } else { continue; } try { String companyNameEn = row.getCell(9).getRichStringCellValue().getString(); if (!companyNameEn.isEmpty()) { Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_en", companyNameEn); if (companiesTEMP == null) { companies.setCompany_en(companyNameEn); } } } catch (Exception e) { } if (check) { // companieses.add(companies); Manage mm = databaseHelper.manageDao.getFirst("key", "company_folder_count"); int jj = Integer.parseInt(mm.getValue()); companies.setFolder_name("C" + jj); databaseHelper.companiesDao.createOrUpdate(companies); ++jj; mm.setValue(jj + ""); databaseHelper.manageDao.createOrUpdate(mm); } } // databaseHelper.companiesDao.insertList(companieses); } catch (Exception e) { Logger.getLogger(POIExcelReader.class.getName()).log(Level.SEVERE, e.getMessage(), e); } }
From source file:gatebass.utils.exel.POIExcelReader.java
@SuppressWarnings("unchecked") public void historyFromExcel(String xlsPath) { InputStream inputStream = null; try {/*from w w w .java 2s . c o m*/ inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); boolean check; List<History> historys = new ArrayList<>(); while (rows.hasNext()) { check = false; HSSFRow row = (HSSFRow) rows.next(); if (row.getRowNum() <= start_row) { continue; } History historyH = null; // System.out.println("Row No.: " + row.getRowNum()); String history = ""; try { history = row.getCell(7).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(18).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(24).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(29).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(30).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(31).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(32).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } check = false; historyH = null; history = ""; try { history = row.getCell(33).getRichStringCellValue().getString(); if (!history.isEmpty()) { history = history.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history); if (HistoryTEMP == null) { check = true; historyH = new History(history.substring(0, history.indexOf("/")), history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")), history.substring(history.lastIndexOf("/") + 1)); } } if (check) { historys.add(historyH); // databaseHelper.historyDao.createOrUpdate(historyH); } } catch (Exception e) { } } databaseHelper.historyDao.insertList(historys); } catch (Exception e) { e.printStackTrace(); } }
From source file:gatebass.utils.exel.POIExcelReader.java
@SuppressWarnings("unchecked") public void worksFromExcel(String xlsPath) { InputStream inputStream = null; try {/* w w w .ja va 2 s . c om*/ inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; List<WorkHistory> historys = new ArrayList<>(); try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); boolean check = false; while (rows.hasNext()) { check = false; HSSFRow row = (HSSFRow) rows.next(); // if (row.getRowNum() >= end_row) { // break; // } if (row.getRowNum() <= start_row) { continue; } WorkHistory workHistory = new WorkHistory(); // System.out.println("Row No.: " + row.getRowNum()); String row_value = row.getCell(2).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_fa", row_value); String ss1 = companiesTEMP.getCompany_fa(); if (ss1.equals(" ") || ss1.equals(" ") || ss1.equals(" ") || ss1.equals(" ") || ss1.equals(" ")) { workHistory.setGate_type(WorkHistory.EMPLOYER); } else { workHistory.setGate_type(WorkHistory.CONTRACTOR); } workHistory.setCompanies(companiesTEMP); } row_value = row.getCell(5).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { workHistory.setJobTitle(row_value); } try { row_value = row.getCell(7).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { row_value = row_value.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value); workHistory.setCardExpirationDateId(HistoryTEMP); } } catch (Exception e) { } try { row_value = row.getCell(11).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { workHistory.setJobTitleENG(row_value); } } catch (Exception e) { } try { row_value = row.getCell(24).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { row_value = row_value.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value); workHistory.setEmploymentDateId(HistoryTEMP); } } catch (Exception e) { } try { row_value = row.getCell(29).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { row_value = row_value.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value); workHistory.setCardIssuedDateId(HistoryTEMP); } } catch (Exception e) { } try { row_value = row.getCell(30).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { row_value = row_value.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value); workHistory.setCardIssuedDateId(HistoryTEMP); } } catch (Exception e) { } try { row_value = row.getCell(31).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { row_value = row_value.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value); workHistory.setCardIssuedDateId(HistoryTEMP); } } catch (Exception e) { } try { row_value = row.getCell(33).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { row_value = row_value.substring(2); History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value); workHistory.setCardDeliveryDate(HistoryTEMP); } } catch (Exception e) { } // System.out.println("Row No.: " + row.getRowNum() + " CardDelivery.: " + (workHistory.getCardDeliveryDate() == null ? "null" : workHistory.getCardDeliveryDate().getDate())); try { row_value = ((long) row.getCell(6).getNumericCellValue()) + ""; if (!row_value.isEmpty()) { Individuals individuals = databaseHelper.individualsDao.getFirst("national_id", row_value); workHistory.setIndividualsId(individuals); } } catch (Exception e) { } try { row_value = row.getCell(6).getRichStringCellValue().getString(); if (!row_value.isEmpty()) { Individuals individuals = databaseHelper.individualsDao.getFirst("national_id", row_value); workHistory.setIndividualsId(individuals); } } catch (Exception e) { } historys.add(workHistory); // databaseHelper.manageDao.createOrUpdate(mm); } databaseHelper.workHistoryDao.insertList(historys); } catch (Exception e) { Logger.getLogger(POIExcelReader.class.getName()).log(Level.SEVERE, e.getMessage(), e); } }
From source file:gatebass.utils.exel.POIExcelReader.java
@SuppressWarnings("unchecked") public void compnaiesFromExcel2(String xlsPath) { InputStream inputStream = null; try {//from w w w. j av a2 s . c o m inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); boolean check = false; while (rows.hasNext()) { check = false; HSSFRow row = (HSSFRow) rows.next(); // if (row.getRowNum() >= end_row) { // break; // } if (row.getRowNum() <= start_row) { continue; } Companies companies = new Companies(); // System.out.println("Row No.: " + row.getRowNum()); String companyName = row.getCell(2).getRichStringCellValue().getString(); if (!companyName.isEmpty()) { Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_fa", companyName); if (companiesTEMP == null) { check = true; companies.setCompany_fa(companyName); companies.setActive(true); } } else { continue; } try { String companyNameEn = row.getCell(9).getRichStringCellValue().getString(); if (!companyNameEn.isEmpty()) { // Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_en", companyNameEn); // if (companiesTEMP == null) { // companies.setCompany_en(companyNameEn); // } } } catch (Exception e) { } if (check) { // companieses.add(companies); Manage mm = databaseHelper.manageDao.getFirst("key", "company_folder_count"); int jj = Integer.parseInt(mm.getValue()); companies.setFolder_name("C" + jj); databaseHelper.companiesDao.createOrUpdate(companies); ++jj; mm.setValue(jj + ""); databaseHelper.manageDao.createOrUpdate(mm); } } // databaseHelper.companiesDao.insertList(companieses); } catch (Exception e) { Logger.getLogger(POIExcelReader.class.getName()).log(Level.SEVERE, e.getMessage(), e); } }