List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getFirstRowNum
@Override public int getFirstRowNum()
From source file:javaexecelprocess.JavaExecelProcess.java
public long[] getFieldsLen() { // Integer dat = new Integer(7); int iCols = getColumns(); long[] fieldsLen = new long[iCols]; for (int i = 0; i < iCols; i++) { fieldsLen[i] = -1;/*from w w w . ja v a 2 s . c o m*/ } HSSFSheet activeSheet = wb.getSheetAt(0); int iFirstRow = activeSheet.getFirstRowNum(); int iLastRow = activeSheet.getLastRowNum(); for (int i = iFirstRow + 1; i <= iLastRow; i++) { HSSFRow row = activeSheet.getRow(i); int iFirstCol = row.getFirstCellNum(); int iLastCol = row.getLastCellNum(); for (int j = iFirstCol; j < iLastCol; j++) { HSSFCell cell = row.getCell(j); int cellType = cell.getCellType(); if (HSSFCell.CELL_TYPE_STRING == cellType) { long tmpLen = cell.getStringCellValue().length(); if (fieldsLen[j - iFirstCol] < tmpLen) { fieldsLen[j - iFirstCol] = tmpLen; } } else if (HSSFCell.CELL_TYPE_NUMERIC == cellType) { fieldsLen[j - iFirstCol] = -1; } else { } } } return fieldsLen; }
From source file:javaexecelprocess.JavaExecelProcess.java
public boolean isDBFormat() { boolean ret = true; HSSFSheet activeSheet = wb.getSheetAt(0); int iFirstRow = activeSheet.getFirstRowNum(); int iLastRow = activeSheet.getLastRowNum(); List<Integer> fieldsType = getFieldsType(); if (null == fieldsType) { ret = false;/*from www . jav a 2s. c o m*/ return ret; } for (int i = iFirstRow + 1; i <= iLastRow; i++) { HSSFRow row = activeSheet.getRow(i); int iFirstCol = row.getFirstCellNum(); int iLastCol = row.getLastCellNum(); for (int j = iFirstCol; j < iLastCol; j++) { HSSFCell cell = row.getCell(j); // String cessStr = cell.toString(); int cellType = cell.getCellType(); // if(HSSFCell.CELL_TYPE_BLANK == cellType // || HSSFCell.CELL_TYPE_ERROR == cellType){ Integer colType = fieldsType.get(j); if (colType.intValue() != cellType) { ret = false; break; } } if (false == ret) { break; } } return ret; }
From source file:javaexecelprocess.JavaExecelProcess.java
private List<Integer> getFieldsType() { List<Integer> fieldsType = null; HSSFSheet activeSheet = wb.getSheetAt(0); int iFirstRow = activeSheet.getFirstRowNum(); int iLastRow = activeSheet.getLastRowNum(); HSSFRow row = activeSheet.getRow(iFirstRow + 1); int iFirstCol = row.getFirstCellNum(); int iLastCol = row.getLastCellNum(); int iCols = row.getPhysicalNumberOfCells(); if (0 != iCols) { fieldsType = new ArrayList<>(); }/* w w w .java 2 s . co m*/ for (int j = iFirstCol; j < iLastCol; j++) { HSSFCell cell = row.getCell(j); int cellType = cell.getCellType(); fieldsType.add(cellType); } return fieldsType; // throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. }
From source file:javaexecelprocess.JavaExecelProcess.java
public void insertDataFromExcel() { String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'h111', 'h222');"; // String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES ('2', 'h111', 'h222');"; String sqlHead = ""; sqlHead += "INSERT INTO `" + fields.get(0) + "` (`id`,"; int i = 0;/*from ww w. j a va 2s . c o m*/ for (i = 0; i < fields.size() - 2; i++) { sqlHead += " `" + fields.get(i + 1) + "`,"; } sqlHead += " `" + fields.get(i + 1) + "`) VALUES (NULL,"; PreparedStatement pst = null; HSSFSheet activeSheet = wb.getSheetAt(0); int iFirstRow = activeSheet.getFirstRowNum(); int iLastRow = activeSheet.getLastRowNum(); for (i = iFirstRow + 1; i <= iLastRow; i++) { String sql = sqlHead; HSSFRow row = activeSheet.getRow(i); int iFirstCol = row.getFirstCellNum(); int iLastCol = row.getLastCellNum(); int j = 0; for (j = iFirstCol; j < iLastCol - 1; j++) { HSSFCell cell = row.getCell(j); String cessStr = cell.toString(); sql += " '" + cessStr + "',"; } HSSFCell cell = row.getCell(j); String cessStr = cell.toString(); sql += " '" + cessStr + "');"; try { pst = mysqlConn.prepareStatement(sql); pst.execute(); } catch (SQLException ex) { Logger.getLogger(JavaExecelProcess.class.getName()).log(Level.SEVERE, null, ex); System.out.println("insert data exception."); } } // sql += "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'f1111', 'f2222');"; }
From source file:mongodbutils.Filehandler.java
public boolean processFile(String filePath, MongodbConnection mc, String strdbName, String strCollName) throws IOException { this.mc = mc; FileInputStream fileIn = null; try {/*from w w w .ja v a 2s. c o m*/ fileIn = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Object objReturn = null; //Read in first row as field names Row rowH = sheet.getRow(sheet.getFirstRowNum()); String fields[] = new String[sheet.getRow(0).getLastCellNum()]; for (Cell cell : rowH) { objReturn = null; objReturn = getCellValue(cell); fields[cell.getColumnIndex()] = objReturn.toString(); } //loop thru all cells with values int rowcount = 0; for (Row row : sheet) { if (row.getRowNum() == 0) { continue; //skip first row } JSONObject obj = new JSONObject(); for (Cell cell : row) { if (fields.length < cell.getColumnIndex()) { continue; //only export column if we have header set } objReturn = null; objReturn = getCellValue(cell); if (!objReturn.toString().equals("")) { if (objReturn instanceof Double) { obj.put(fields[cell.getColumnIndex()], objReturn); } else if (objReturn instanceof String) { if (objReturn.toString().contains("$date")) { JSONParser parser = new JSONParser(); try { obj.put(fields[cell.getColumnIndex()], parser.parse(objReturn.toString())); } catch (ParseException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } } else { obj.put(fields[cell.getColumnIndex()], objReturn); } } } } rowcount += 1; mc.insertJSON(strdbName, strCollName, obj.toJSONString()); } return true; } catch (FileNotFoundException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception e) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, e); } finally { if (fileIn != null) { fileIn.close(); } } return false; }
From source file:net.intelliant.util.UtilImport.java
License:Open Source License
public static List<String> readExcelFirstRow(String excelFilePath, boolean isFirstRowHeader, int sheetIndex) throws FileNotFoundException, IOException { List<String> columnIndices = new ArrayList<String>(); File file = new File(excelFilePath); if (file != null && file.canRead()) { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); // HSSFSheet sheet = wb.getSheet(wb.getActiveSheetIndex()); HSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet != null) { HSSFRow firstRow = sheet.getRow(sheet.getFirstRowNum()); if (firstRow != null) { firstRow.getPhysicalNumberOfCells(); Iterator<?> cells = firstRow.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (isFirstRowHeader) { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { columnIndices.add(cell.toString()); } else { columnIndices.add("N/A - " + cell.getCellNum()); }/*from w ww. j av a 2s .c om*/ } else { columnIndices.add(String.valueOf(cell.getCellNum())); } } } } } return columnIndices; }
From source file:npv.importer.XlsImporter.java
private Double[] parseFile() throws IOException { InputStream inputStream = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(inputStream); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); System.out.println("Testing.First row num=" + sheet.getFirstRowNum()); findTag(sheet, tag);/*from w w w. j a v a 2 s . c o m*/ //reading an array of Ri values after '#Ri' tag HSSFRow row = sheet.getRow(rPosition[0]); ArrayList<Double> cellValues = new ArrayList<Double>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= rPosition[1] + 1) { cellValues.add(cell.getNumericCellValue()); } } rValues = new Double[cellValues.size()]; rValues = cellValues.toArray(new Double[rValues.length]); System.out.println("Values from sheet:"); for (int i = 0; i < rValues.length; i++) { System.out.println(rValues[i]); } return this.rValues; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
@Transactional public String upload() { try {//from w w w.ja v a 2s. com insertQuery = persistenceService.getSession().createSQLQuery(insertsql); final Bankaccount ba = (Bankaccount) persistenceService.find("from Bankaccount ba where id=?", Long.valueOf(accountId)); accNo = ba.getAccountnumber(); final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(bankStatmentInXls)); final HSSFWorkbook wb = new HSSFWorkbook(fs); wb.getNumberOfSheets(); final HSSFSheet sheet = wb.getSheetAt(0); sheet.getFirstRowNum(); // Validating selected bankaccount and BankStatements bankaccount final HSSFRow row = sheet.getRow(ACCOUNTNUMBER_ROW_INDEX); if (row == null) { bank_account_not_match_msg = bank_account_not_match_msg.replace("#name", bankStatmentInXlsFileName); throw new ValidationException( Arrays.asList(new ValidationError(bank_account_not_match_msg, bank_account_not_match_msg))); } String strValue2 = getStrValue(row.getCell(0)); strValue2 = strValue2.substring(strValue2.indexOf(':') + 1, strValue2.indexOf('-')).trim(); if (!strValue2.equals(accNo.trim())) { bank_account_not_match_msg = bank_account_not_match_msg.replace("#name", bankStatmentInXlsFileName); throw new ValidationException( Arrays.asList(new ValidationError(bank_account_not_match_msg, bank_account_not_match_msg))); } AutoReconcileBean ab = null; HSSFRow detailRow = null; String dateStr = null; rowIndex = STARTOF_DETAIL_ROW_INDEX; count = 0; do { try { ab = new AutoReconcileBean(); if (rowIndex == STARTOF_DETAIL_ROW_INDEX) { detailRow = sheet.getRow(rowIndex); if (rowIndex >= 9290) if (LOGGER.isDebugEnabled()) LOGGER.debug(String.valueOf(detailRow.getRowNum())); dateStr = getStrValue(detailRow.getCell(TXNDT_INDEX)); if (alreadyUploaded(dateStr)) { file_already_uploaded = file_already_uploaded.replace("#name", bankStatmentInXlsFileName); throw new ValidationException(Arrays .asList(new ValidationError(file_already_uploaded, file_already_uploaded))); } ab.setTxDateStr(dateStr); } ab.setTxDateStr(dateStr); ab.setInstrumentNo(getStrValue(detailRow.getCell(CHEQUENO_INDEX))); // if(strValue!=null) // ab.setInstrumentNo(strValue.replaceFirst(".0", "")); ab.setDebit(getNumericValue(detailRow.getCell(DEBIT_INDEX))); ab.setCredit(getNumericValue(detailRow.getCell(CREDIT_INDEX))); ab.setBalance(getNumericValue(detailRow.getCell(BALANCE_INDEX))); String strValue = getStrValue(detailRow.getCell(NARRATION_INDEX)); if (strValue != null) { if (strValue.length() > 125) strValue = strValue.substring(0, 125); // strValue=strValue.replaceFirst(".0", ""); ab.setNarration(strValue); } ab.setType(getStrValue(detailRow.getCell(TYPE_INDEX))); ab.setCSLno(getStrValue(detailRow.getCell(CSLNO_INDEX))); // if(ab.getType()==null) // ab.setType("CLG"); if (LOGGER.isInfoEnabled()) LOGGER.info(detailRow.getRowNum() + " " + ab.toString()); insert(ab); if (count % 20 == 0) persistenceService.getSession().flush(); } catch (ValidationException ve) { throw ve; } catch (final NumberFormatException e) { if (!isFailed) failureMessage += detailRow.getRowNum() + 1; else failureMessage += " , " + detailRow.getRowNum() + 1; isFailed = true; throw new ValidationException( Arrays.asList(new ValidationError(failureMessage, failureMessage))); } rowIndex++; count++; detailRow = sheet.getRow(rowIndex); if (detailRow != null) dateStr = getStrValue(detailRow.getCell(TXNDT_INDEX)); else dateStr = null; // ab.setTxDateStr(detailRow.getRowNum()+"-->" + dateStr); } while (dateStr != null && !dateStr.isEmpty()); if (isFailed) throw new ValidationException(Arrays.asList(new ValidationError(failureMessage, failureMessage))); else { final FileStoreMapper fileStore = fileStoreService.store(getBankStatmentInXls(), bankStatmentInXlsFileName, "application/vnd.ms-excel", FinancialConstants.MODULE_NAME_APPCONFIG, false); persistenceService.persist(fileStore); String fileStoreId = fileStore.getFileStoreId(); DocumentUpload upload = new DocumentUpload(); upload.setFileStore(fileStore); upload.setObjectId(accountId.longValue()); upload.setObjectType(FinancialConstants.BANK_STATEMET_OBJECT); upload.setUploadedDate(new Date()); documentUploadRepository.save(upload); message = successMessage.replace("#", "" + count); } } catch (final FileNotFoundException e) { throw new ValidationException( Arrays.asList(new ValidationError("File cannot be uploaded", "File cannot be uploaded"))); } catch (final IOException e) { throw new ValidationException(Arrays .asList(new ValidationError("Unable to read uploaded file", "Unable to read uploaded file"))); } catch (final ValidationException ve) { throw ve; } catch (final NullPointerException npe) { throw new ValidationException( Arrays.asList(new ValidationError(bankStatementFormat, bankStatementFormat))); } catch (final Exception e) { throw new ValidationException( Arrays.asList(new ValidationError(bankStatementFormat, bankStatementFormat))); } return "upload"; }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xls file content. * //from w ww . ja v a2 s .c om * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } final StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } HSSFWorkbook wb; try { wb = new HSSFWorkbook(is); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { final HSSFCell cell = row.getCell((short) k); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case HSSFCell.CELL_TYPE_FORMULA: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getCellFormula().toString()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_BOOLEAN: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getBooleanCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_ERROR: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getErrorCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_STRING: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getStringCellValue().toString()).append(" "); return null; } }); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.gageot.excel.core.RowMapperSheetExtractor.java
License:Apache License
@Override public List<T> extractData(HSSFSheet sheet) throws IOException { List<T> rows = Lists.newArrayList(); int firstRowIndex = sheet.getFirstRowNum(); int lastRowIndex = sheet.getLastRowNum(); for (int i = firstRowIndex; i <= lastRowIndex; i++) { T row = rowMapper.mapRow(sheet.getRow(i), i); if (null != row) { rows.add(row);/* www . ja v a2 s. co m*/ } } return rows; }