List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTable.java
License:Open Source License
public List<String> columnNames() { List<String> columnNames = new ArrayList<String>(); HSSFRow headerRow = sheet.getRow(startingRowIndex + 1); for (int cellnum = 0; cellnum < headerRow.getLastCellNum(); cellnum++) { columnNames.add(headerRow.getCell(cellnum).getRichStringCellValue().getString()); }/* w w w . j a v a 2 s .com*/ return columnNames; }
From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTable.java
License:Open Source License
/** * Gets cell values from row// www . ja v a2s . com * * @param relativRowIndex * to the description and cell headers * @return cell values for the given row */ public List<Object> getRow(int relativRowIndex) { List<Object> result = new ArrayList<Object>(); HSSFRow row = sheet.getRow(startingRowIndex + 2 + relativRowIndex); for (int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) { HSSFCell cell = row.getCell(cellnum); try { int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_NUMERIC: { result.add(cell.getNumericCellValue()); continue; } case HSSFCell.CELL_TYPE_FORMULA: { result.add(cell.getNumericCellValue()); continue; } case HSSFCell.CELL_TYPE_STRING: { result.add(cell.getRichStringCellValue().getString()); continue; } default: { throw new IllegalStateException("can't handle value"); } } } catch (Exception e) { throw new ExcelTableCellReadException(row.getRowNum() + 1, cellnum, cell, e); } } return result; }
From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTables.java
License:Open Source License
public ExcelExampleTable getTable(String tableDescription) { HSSFSheet sheet = workbook.getSheetAt(0); int rowIndex = -1; int cellnum = -1; HSSFCell cell = null;//from www. ja v a 2s . c o m try { for (rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); if (null == row) { continue; } for (cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) { cell = row.getCell(cellnum); if ((HSSFCell.CELL_TYPE_STRING == cell.getCellType()) && tableDescription.equals(cell.getRichStringCellValue().getString())) { return new ExcelExampleTable(sheet, rowIndex, cellnum); } } } } catch (Exception e) { throw new IllegalStateException("Problem with cell (row=" + rowIndex + ",cellnum=" + cellnum + ",val=" + cell + ") getting table [" + tableDescription + "] from [" + xlsFilePath + "]", e); } throw new IllegalArgumentException("Can't find [" + tableDescription + "] in [" + xlsFilePath + "]"); }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
protected void cleanupCells(HSSFSheet resultSheet) { for (int i = resultSheet.getFirstRowNum(); i <= resultSheet.getLastRowNum(); i++) { HSSFRow row = resultSheet.getRow(i); if (row != null) { for (int j = 0; j < row.getLastCellNum(); j++) { HSSFCell cell = row.getCell(j); if (cell != null) { row.removeCell(cell); }// w w w .jav a 2 s .c o m } } } }
From source file:com.huateng.bo.impl.risk.T40201BOTarget.java
License:Open Source License
public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception { HSSFWorkbook workbook = null;/*from w w w .j a v a 2 s. com*/ HSSFSheet sheet = null; HSSFRow row = null; // ? String returnMsg = ""; // ?? int fileNameIndex = 0; // ?? String fileName = null; // ?? String saCardNo = null; // ?? // String saLimitAmt = null; // ? // String saAction = null; // String saBrhId = operator.getOprBrhId(); // ? String saOprId = operator.getOprId(); // String saInitTime = CommonFunction.getCurrentDateTime(); TblCtlCardInf tblCtlCardInf = null; FileInputStream fileInputStream = null; for (File file : fileList) { fileInputStream = new FileInputStream(file); workbook = new HSSFWorkbook(fileInputStream); sheet = workbook.getSheetAt(0); fileName = fileNameList.get(fileNameIndex); for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING) returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + "" + (i + 1) + "???<br>"; if (!"".equals(returnMsg)) return returnMsg; saCardNo = row.getCell(0).getStringCellValue(); // ?? if (saCardNo.getBytes().length > 19) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; // saLimitAmt = row.getCell(1).getStringCellValue(); // saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt); // ?? /*if(!CommonFunction.isAllDigit(saLimitAmt)) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>"; if(saLimitAmt.getBytes().length > 12) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/ // saAction = row.getCell(2).getStringCellValue(); // ? /*if(!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction))) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/ // ??????? tblCtlCardInf = new TblCtlCardInf(); tblCtlCardInf.setId(saCardNo); // tblCtlCardInf.setSaLimitAmt(saLimitAmt); // tblCtlCardInf.setSaAction(saAction); tblCtlCardInf.setSaInitZoneNo(saBrhId); tblCtlCardInf.setSaInitOprId(saOprId); tblCtlCardInf.setSaInitTime(saInitTime); tblCtlCardInfDAO.saveOrUpdate(tblCtlCardInf); } fileInputStream.close(); fileNameIndex++; } return Constants.SUCCESS_CODE; }
From source file:com.huateng.bo.impl.risk.T40202BOTarget.java
License:Open Source License
@SuppressWarnings("unchecked") public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception { HSSFWorkbook workbook = null;/*from ww w .j a v a 2 s. c o m*/ HSSFSheet sheet = null; HSSFRow row = null; // ? String returnMsg = ""; // ?? int fileNameIndex = 0; // ?? String fileName = null; String sql = null; // List<Object[]> dataList = null; // ?? String saMerNo = null; // ?? String saMerChName = null; // ?? String saMerEnName = null; // ? String saZoneNo = null; // ?? // String saLimitAmt = null; // ? // String saAction = null; // String saBrhId = operator.getOprBrhId(); // ? String saOprId = operator.getOprId(); // String saInitTime = CommonFunction.getCurrentDateTime(); TblCtlMchtInf tblCtlMchtInf = null; FileInputStream fileInputStream = null; for (File file : fileList) { fileInputStream = new FileInputStream(file); workbook = new HSSFWorkbook(fileInputStream); sheet = workbook.getSheetAt(0); fileName = fileNameList.get(fileNameIndex); for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING) returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + "" + (i + 1) + "???<br>"; if (!"".equals(returnMsg)) return returnMsg; saMerNo = row.getCell(0).getStringCellValue(); // ? if (saMerNo.getBytes().length > 15) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; sql = "select mcht_nm,eng_name,bank_no from TBL_MCHT_BASE_INF where MCHT_NO = '" + saMerNo + "'"; dataList = CommonFunction.getCommQueryDAO().findBySQLQuery(sql); if (dataList.size() == 0) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "??<br>"; // ?? saMerChName = dataList.get(0)[0].toString(); // ?? saMerEnName = dataList.get(0)[1].toString(); saZoneNo = dataList.get(0)[2].toString(); // saLimitAmt = row.getCell(1).getStringCellValue(); // saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt); // ?? /*if(!CommonFunction.isAllDigit(saLimitAmt)) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>"; if(saLimitAmt.getBytes().length > 12) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/ // saAction = row.getCell(2).getStringCellValue(); // ? /*if(!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction))) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/ // ?????? tblCtlMchtInf = new TblCtlMchtInf(); tblCtlMchtInf.setId(saMerNo); tblCtlMchtInf.setSaMerChName(saMerChName); tblCtlMchtInf.setSaMerEnName(saMerEnName); tblCtlMchtInf.setSaZoneNo(saZoneNo); // tblCtlMchtInf.setSaLimitAmt(saLimitAmt); // tblCtlMchtInf.setSaAction(saAction); tblCtlMchtInf.setSaInitZoneNo(saBrhId); tblCtlMchtInf.setSaInitOprId(saOprId); tblCtlMchtInf.setSaInitTime(saInitTime); tblCtlMchtInfDAO.saveOrUpdate(tblCtlMchtInf); } fileInputStream.close(); fileNameIndex++; } return Constants.SUCCESS_CODE; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
@SuppressWarnings("deprecation") public String getErrorExcel(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;//from w ww . j a va 2 s . co m HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; BillingTriggerValidation validateDto = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.errors") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getErrorLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("ERROR LOG"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); System.out.println(dtoObj.getErrorLogValue()); if (dtoObj.getErrorLogValue() == null) { wc.setCellValue("No Errors"); } else { wc.setCellValue(dtoObj.getErrorLogValue().toString()); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getResultExcel(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;//from ww w . ja v a2 s .com HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.success") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getResultLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("RESULT LOG" + "_" + "ORDERNO"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); System.out.println(dtoObj.getResultLogValue()); if (dtoObj.getResultLogValue() == null) { wc.setCellValue("SUCCESS"); } else { if (dtoObj.getOrderNo() != null) wc.setCellValue(dtoObj.getResultLogValue().toString() + "_(" + dtoObj.getOrderNo().toString() + ")"); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getResultErrorMixLog(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;//from ww w . j a va2 s . c o m HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.success") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getResultErrorMixLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); HSSFCellStyle styleErr = wb.createCellStyle(); HSSFCellStyle styleSuccess = wb.createCellStyle(); HSSFFont fontSuccess = wb.createFont(); HSSFFont fontErr = wb.createFont(); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("RESULT LOG" + "_" + "ORDERNO"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); if ((dtoObj.getResultLogValue() == null || dtoObj.getResultLogValue().length() == 0) && dtoObj.getErrorLogValue() != null) { fontErr.setColor(HSSFColor.RED.index); styleErr.setFont(fontErr); wc.setCellStyle(styleErr); wc.setCellValue(dtoObj.getErrorLogValue().toString()); } else { fontSuccess.setColor(HSSFColor.BLACK.index); styleSuccess.setFont(fontSuccess); wc.setCellStyle(styleSuccess); wc.setCellValue(dtoObj.getResultLogValue().toString()); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + " Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java
License:Open Source License
public String extractText(String filepath) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(filepath)); String resultText = ""; HSSFWorkbook wb = new HSSFWorkbook(input); if (wb == null) { return resultText; }/*from ww w .jav a2s. c o m*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); for (int k = 0; k < cNum; k++) { try { if ((cell = row.getCell((short) k)) != null) { /* * if(HSSFDateUtil.isCellDateFormatted(cell) == * true) { resultText += * cell.getDateCellValue().toString() + " "; } else */ if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { resultText += " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { resultText += cell.getRichStringCellValue().toString() + " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); resultText += d.toString() + " "; } /* * else if(cell.getCellType() == * HSSFCell.CELL_TYPE_FORMULA){ resultText += * cell.getCellFormula() + " "; } */ } } catch (Exception ex) { } } resultText += "\n"; } } if (input != null) { input.close(); } return resultText; }