List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator
@Override
public Iterator<Row> rowIterator()
From source file:com.jk.framework.util.ExcelUtil.java
License:Apache License
/** * Parses the file.//from ww w . j a v a 2s . c om * * @param selectedFile * the selected file * @param tableMeta * the table meta * @param headers * the headers * @return the array list * @throws ParserException * the parser exception */ // /////////////////////////////////////////////////////////////////////////////////////////////////////////////// public static ArrayList<Record> parseFile(final InputStream selectedFile, final TableMeta tableMeta, final String... headers) throws ParserException { final ArrayList<Record> list = new ArrayList<Record>(); try { final POIFSFileSystem fs = new POIFSFileSystem(selectedFile); final HSSFWorkbook wb = new HSSFWorkbook(fs); final HSSFSheet sheet = wb.getSheetAt(0); final Iterator rows = sheet.rowIterator(); boolean firstRow = true; Hashtable<String, Integer> customHeaders = null; while (rows.hasNext()) { if (firstRow) { if (headers.length != 0) { customHeaders = processHeaders(headers, (HSSFRow) rows.next()); firstRow = false; } else { rows.next();// to skip first row firstRow = false; continue; } } list.add(populateRow(tableMeta, (HSSFRow) rows.next(), customHeaders)); } } catch (final Exception e) { throw new ParserException(e); } return list; }
From source file:com.kcs.action.FrfImportAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {//from w w w.j ava 2 s. c o m InputStream input = new BufferedInputStream(new FileInputStream(this.toBeUploaded)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetfrf>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetfrf datasetfrf = new Datasetfrf(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetfrf.setOrgId(row.getCell(index++).toString()); datasetfrf.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetfrf.setLoanDepsitTrnTye(row.getCell(index++).toString()); datasetfrf.setCurrCode(row.getCell(index++).toString()); datasetfrf.setPaymentMethod(row.getCell(index++).toString()); datasetfrf.setBrOrBcFlg(row.getCell(index++).toString()); datasetfrf.setCommInLieuRate(row.getCell(index++).toString()); datasetfrf.setMinCommInLieu(row.getCell(index++).toString()); datasetfrf.setMaxCommInLieu(row.getCell(index++).toString()); datasetfrf.setOthFeeDesc(row.getCell(index++).toString()); datasetfrf.setEffectiveDate(convertDate(row.getCell(index++).toString())); datasetfrf.setEndDate(convertDate(row.getCell(index++).toString())); datasetfrf.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetfrf.setUpdBy(getCurrentLoginId()); datasetfrf.setUpdDate(DateUtil.getCurrentDateTime()); index++; index++; datasetfrf.setSysCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetfrf); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_FRF", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.FrwImportAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {/*from w w w . j ava2s . c om*/ InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded())); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetfrw>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetfrw datasetfrw = new Datasetfrw(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetfrw.setOrgId(row.getCell(index++).toString()); datasetfrw.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetfrw.setCurrCode(row.getCell(index++).toString()); datasetfrw.setCommInLieuRateForDepsit(row.getCell(index++).toString()); datasetfrw.setMinCommInLieuForDepsit(row.getCell(index++).toString()); datasetfrw.setMaxCommInLieuForDepsit(row.getCell(index++).toString()); datasetfrw.setInwTransfFeeForDepsit(row.getCell(index++).toString()); datasetfrw.setMinInwTransfFeeForDepsit(row.getCell(index++).toString()); datasetfrw.setMaxInwTransfFeeForDepsit(row.getCell(index++).toString()); datasetfrw.setOthFeeDescForDepsit(row.getCell(index++).toString()); datasetfrw.setCommInLieuRateForWithdw(row.getCell(index++).toString()); datasetfrw.setMinCommInLieuRateFWithdw(row.getCell(index++).toString()); datasetfrw.setMaxCommInLieuRateFWithdw(row.getCell(index++).toString()); datasetfrw.setWithdwFeeForOthTransf(row.getCell(index++).toString()); datasetfrw.setEffectiveDate(convertDate(row.getCell(index++).toString())); datasetfrw.setEndDate(convertDate(row.getCell(index++).toString())); datasetfrw.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetfrw.setUpdBy(getCurrentLoginId()); datasetfrw.setUpdDate(DateUtil.getCurrentDateTime()); index++; index++; datasetfrw.setSysCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetfrw); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_FRW", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.ImportExcelDsDipAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {/*from w w w .j av a 2 s .c om*/ InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded())); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetdip>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetdip datasetdip = new Datasetdip(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(3).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); logger.debug("dateFromJsp >>> " + dateFromJsp); logger.debug("dateFromExcel >>> " + dateFromExcel); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetdip.setOrgId(row.getCell(index++).toString()); datasetdip.setDataPvdrBrcNo(row.getCell(index++).toString()); datasetdip.setFiRptGrp(row.getCell(index++).toString()); datasetdip.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetdip.setItemType(row.getCell(index++).toString()); datasetdip.setItemDesc(row.getCell(index++).toString()); datasetdip.setCtryId(row.getCell(index++).toString()); datasetdip.setCurr(row.getCell(index++).toString()); datasetdip.setAmt(new BigDecimal(row.getCell(index++).toString())); datasetdip.setUpdDate(DateUtil.getCurrentDateTime()); datasetdip.setUpdBy(getCurrentLoginId()); index++; index++; datasetdip.setSysCode(row.getCell(index++).toString()); datasetdip.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetdip.setCustCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetdip); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_DIP", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.IrfImportAction.java
public void excelToList() throws Exception { String filePath = servletRequest.getSession().getServletContext().getRealPath("/"); try {/* w w w . j a va2s. co m*/ InputStream input = new BufferedInputStream(new FileInputStream(this.toBeUploaded)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); resultsFromExcel = new ArrayList<Datasetirf>(); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetirf datasetirf = new Datasetirf(); Date dateFromJsp = DateUtil.convertDateFromJsp(dataSetDate); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } datasetirf.setOrgId(row.getCell(0).toString()); datasetirf.setDataSetDate( DateUtil.parse(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX)); datasetirf.setArrgmentTye(row.getCell(2).toString()); datasetirf.setInvPartyTye(row.getCell(3).toString()); datasetirf.setCurrCode(row.getCell(4).toString()); datasetirf.setDepsitTerm(Long.valueOf(row.getCell(5).toString())); datasetirf.setDepsitTermUnt(row.getCell(6).toString()); datasetirf.setBalTierAmt(row.getCell(7).toString()); datasetirf .setInterestRate(NumberUtil.convertToBigDecimal(row.getCell(8).toString()).setScale(2)); datasetirf.setEffectiveDate( DateUtil.parse(row.getCell(9).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX)); if (!row.getCell(10).toString().equals("null") || row.getCell(10).toString().equals(null)) { datasetirf.setEndDate( DateUtil.parse(row.getCell(10).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX)); } else { datasetirf.setEndDate(null); } datasetirf.setSeq(Long.valueOf(row.getCell(11).toString())); datasetirf.setUpdBy(getCurrentLoginId()); datasetirf.setUpdDate(DateUtil.getCurrentDateTime()); datasetirf.setSysCode(row.getCell(14).toString()); resultsFromExcel.add(datasetirf); } checkColName++; } setResultsFromExcel(resultsFromExcel); session.put("EXCEL_TO_LIST", resultsFromExcel); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.OffImportAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {//from www.ja va 2 s . c o m InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded())); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetoff>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetoff datasetoff = new Datasetoff(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetoff.setOrgId(row.getCell(index++).toString()); datasetoff.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetoff.setArrgmentTye(row.getCell(index++).toString()); datasetoff.setCurrCode(row.getCell(index++).toString()); datasetoff.setMinimOpenAcctAmt(row.getCell(index++).toString()); datasetoff.setMinimBalForMaintenanceFee(row.getCell(index++).toString()); datasetoff.setMaintenanceFee(row.getCell(index++).toString()); datasetoff.setInactiveTerm(convertBigDecimal(row.getCell(index++).toString())); datasetoff.setInactiveTermUnt(row.getCell(index++).toString()); datasetoff.setInactiveFee(row.getCell(index++).toString()); datasetoff.setEffectiveDate(convertDate(row.getCell(index++).toString())); datasetoff.setEndDate(convertDate(row.getCell(index++).toString())); datasetoff.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetoff.setUpdBy(getCurrentLoginId()); datasetoff.setUpdDate(DateUtil.getCurrentDateTime()); index++; index++; datasetoff.setSysCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetoff); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_FRF", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.liferay.util.poi.XLSTextStripper.java
License:Open Source License
public XLSTextStripper(FileInputStream fis) { try {// w w w.j av a2 s .c o m StringBuffer sb = new StringBuffer(); HSSFWorkbook workbook = new HSSFWorkbook(fis); int numOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numOfSheets; i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { HSSFRow row = (HSSFRow) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); String cellStringValue = null; if (cell.getCellType() == 4) { boolean booleanValue = cell.getBooleanCellValue(); cellStringValue = Boolean.toString(booleanValue); } else if (cell.getCellType() == 0) { double doubleValue = cell.getNumericCellValue(); cellStringValue = Double.toString(doubleValue); } else if (cell.getCellType() == 1) { cellStringValue = cell.getStringCellValue(); } if (cellStringValue != null) { sb.append(cellStringValue); sb.append("\t"); } } sb.append("\n"); } } _text = sb.toString(); } catch (Exception e) { Logger.error(this, e.getMessage(), e); } }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * /* w w w . ja v a 2 s. c om*/ * excel * * @param inputstream : ? * @param pojoClass : (?) * @return */ public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) { Collection dist = new ArrayList<Object>(); try { // Field filed[] = pojoClass.getDeclaredFields(); // Annotation??,map Map<String, Method> fieldSetMap = new HashMap<String, Method>(); Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>(); // ? for (int i = 0; i < filed.length; i++) { Field f = filed[i]; // ?Annotation Excel excel = f.getAnnotation(Excel.class); // Annotationd? if (excel != null) { // AnnotationSetter String fieldname = f.getName(); String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); // method Method setMethod = pojoClass.getMethod(setMethodName, new Class[] { f.getType() }); // methodAnnotaion??key? // ??? ?? fieldSetMap.put(excel.exportName(), setMethod); if (excel.importConvert() == true) { // get/setXxxxConvert??? ?Entity? StringBuffer setConvertMethodName = new StringBuffer("convertSet"); setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); setConvertMethodName.append(fieldname.substring(1)); Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(), new Class[] { String.class }); fieldSetConvertMap.put(excel.exportName(), getConvertMethod); } } } // FileFileInputStream; // // HSSFWorkbook book = new HSSFWorkbook(inputstream); // // HSSFSheet sheet = book.getSheetAt(0); // // ? Iterator<Row> row = sheet.rowIterator(); // Row title = row.next(); // Iterator<Cell> cellTitle = title.cellIterator(); // map Map titlemap = new HashMap(); // int i = 0; // while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); titlemap.put(i, value); i = i + 1; } // ??DateFormat // SimpleDateFormat sf; while (row.hasNext()) { // Row rown = row.next(); // Iterator<Cell> cellbody = rown.cellIterator(); // Object tObject = pojoClass.newInstance(); int k = 0; // ?? while (cellbody.hasNext()) { Cell cell = cellbody.next(); // String titleString = (String) titlemap.get(k); // ?Annotation?set if (fieldSetMap.containsKey(titleString)) { Method setMethod = (Method) fieldSetMap.get(titleString); // setter? Type[] ts = setMethod.getGenericParameterTypes(); // ??? String xclass = ts[0].toString(); // ? if (Cell.CELL_TYPE_STRING == cell.getCellType() && fieldSetConvertMap.containsKey(titleString)) { // ???String? fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue()); } else { if (xclass.equals("class java.lang.String")) { // Cell??String? cell.setCellType(Cell.CELL_TYPE_STRING); setMethod.invoke(tObject, cell.getStringCellValue()); } else if (xclass.equals("class java.util.Date")) { // update-start--Author:Quainty Date:20130523 for??(?Excel?) Date cellDate = null; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { // ? cellDate = cell.getDateCellValue(); } else { // Cell.CELL_TYPE_STRING: ? yyyy-mm-dd hh:mm:ss ??(wait to do:?) cellDate = stringToDate(cell.getStringCellValue()); } setMethod.invoke(tObject, cellDate); //// -------------------------------------------------------------------------------------------- //String cellValue = cell.getStringCellValue(); //Date theDate = stringToDate(cellValue); //setMethod.invoke(tObject, theDate); //// -------------------------------------------------------------------------------------------- } else if (xclass.equals("class java.lang.Boolean")) { boolean valBool; if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { valBool = cell.getBooleanCellValue(); } else {// Cell.CELL_TYPE_STRING valBool = cell.getStringCellValue().equalsIgnoreCase("true") || (!cell.getStringCellValue().equals("0")); } setMethod.invoke(tObject, valBool); } else if (xclass.equals("class java.lang.Integer")) { Integer valInt; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valInt = (new Double(cell.getNumericCellValue())).intValue(); } else {// Cell.CELL_TYPE_STRING valInt = new Integer(cell.getStringCellValue()); } setMethod.invoke(tObject, valInt); } else if (xclass.equals("class java.lang.Long")) { Long valLong; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valLong = (new Double(cell.getNumericCellValue())).longValue(); } else {// Cell.CELL_TYPE_STRING valLong = new Long(cell.getStringCellValue()); } setMethod.invoke(tObject, valLong); } else if (xclass.equals("class java.math.BigDecimal")) { BigDecimal valDecimal; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valDecimal = new BigDecimal(cell.getNumericCellValue()); } else {// Cell.CELL_TYPE_STRING valDecimal = new BigDecimal(cell.getStringCellValue()); } setMethod.invoke(tObject, valDecimal); } } } // k = k + 1; } dist.add(tObject); } } catch (Exception e) { e.printStackTrace(); return null; } return dist; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {//from w w w. ja v a 2s. com FileInputStream myInput = new FileInputStream(filePath); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet mySheet1 = myWorkBook.getSheetAt(j); if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) { myWorkBook.removeSheetAt(j); hasTrue = true; break; } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); break; } } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void readTestSuitesFromXLS(List<TestSuite> excels, StringBuilder sb) throws FileNotFoundException, IOException, UnknownHostException, PhrescoException { Iterator<Row> rowIterator; FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next();//w w w . j a va 2 s . com } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(2))) && !getValue(next.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next); excels.add(createObject); } } }