List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
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 w w w. j a v a 2s. co 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.asset.trails.service.impl.CauseCodeServiceImpl.java
@SuppressWarnings("unchecked") private boolean validateExcelCauseCodeContent(HSSFSheet sheet, HSSFCellStyle errorStyle, List<State> steps) { State state = State.findStateByLable(steps, STEP2_LABEL); if (state == null) { state = new State(); state.setDescription("Data validation"); state.setLabel(STEP2_LABEL);//from w w w. j av a 2 s.c om state.setStatus(EStatus.IN_PROGRESS); steps.add(state); } boolean error = false; if (colIndexes == null) { int lastCellNO = sheet.getRow(ROW_ALERT_TYPE).getLastCellNum(); HSSFCell cell = sheet.getRow(ROW_ALERT_TYPE).createCell(lastCellNO + 1); cell.setCellStyle(errorStyle); cell.setCellValue(new HSSFRichTextString(ERROR_UNKONW_TYPE)); error = true; } else { Iterator<Row> rowIter = sheet.rowIterator(); int rowCounter = -1; int totalRows = sheet.getLastRowNum(); int colStart = colIndexes.getColCauseCode(); int colEnd = colIndexes.getColInternalId(); while (rowIter.hasNext()) { HSSFRow row = (HSSFRow) rowIter.next(); rowCounter++; int progress = (int) ((float) rowCounter / totalRows * 100); state.setProgress(progress); if (rowCounter <= ROW_TABLE_HEAD) { continue; } StringBuffer errorMsg = new StringBuffer(); for (int col = colStart; col <= colEnd; col++) { HSSFCell cell = row.getCell(col); if (col == colIndexes.getColInternalId()) { if (!isCauseCodeExists(cell)) { buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID", ERROR_INTERNAL_ID_NOT_EXIST); } else { Long alertTypeId = getAlertTypeId(cell); if (alertTypeId != null && alertTypeId != this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode())) { buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID", ERROR_ALERT_TYPE_NOT_MATCH); } } } if (col == colIndexes.getColCauseCode()) { if (cell == null) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); continue; } HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId()); if (!isCauseCodeExists(causeCodeIdCell)) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); continue; } boolean pass = true; // if no change continue; String alertCauseNameInCell = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { alertCauseNameInCell = cell.getStringCellValue(); } else { pass = false; } if (alertCauseNameInCell == null || "".equals(alertCauseNameInCell.trim())) { pass = false; } if (!pass) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); continue; } if (alertCauseNameInCell.length() > 128) { alertCauseNameInCell = alertCauseNameInCell.substring(0, 128); } String alertCauseNameInDb = getAlertCauseName(causeCodeIdCell); // compare the cc name and cause code name under id. if // not same check it's availability. if same ignore. if (!strCompare(alertCauseNameInDb, alertCauseNameInCell)) { List<AlertCause> acList = null; try { acList = getEntityManager().createNamedQuery("findActiveAlertCauseByNameAndTypeId") .setParameter("alertCauseName", alertCauseNameInCell.trim().toUpperCase()) .setParameter("alertTypeId", this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode())) .getResultList(); if (acList.size() <= 0) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); } } catch (Exception e) { log.error(e.getMessage(), e); } } } if (col == colIndexes.getColTargetDate()) { if (!isDateFormat(cell)) {//CC Target Date is an optional field buildErrorMsg(errorMsg, colIndexes.getColTargetDate(), "CC target date", ERROR_BAD_DATE_FORMAT); } } if (col == colIndexes.getColOwner()) { if (!isOwnerExistsInBluePage(cell)) {//CC Owner is an optional field buildErrorMsg(errorMsg, colIndexes.getColOwner(), "CC owner", ERROR_UNKNOW_OWNER); } } } if (errorMsg.length() > 0) { HSSFCell msgCell = row.createCell(colIndexes.getColMessage()); msgCell.setCellStyle(errorStyle); msgCell.setCellValue(new HSSFRichTextString(errorMsg.toString())); error = true; } } } if (error) { state.setStatus(EStatus.FAILED); } else { if (state.getProgress() == 100 && state.getStatus().getPriority() < EStatus.FINISHED.getPriority()) { state.setStatus(EStatus.FINISHED); } } return error; }
From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java
private void saveCauseCode(HSSFWorkbook wb, String remoteUser, List<State> steps) { HSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIter = sheet.rowIterator(); State state = State.findStateByLable(steps, STEP3_LABEL); if (state == null) { state = new State(); state.setDescription("Persist changes"); state.setLabel(STEP3_LABEL);//from w ww . j a v a 2s . c om state.setStatus(EStatus.IN_PROGRESS); steps.add(state); } int rowCounter = -1; int totalRows = sheet.getLastRowNum(); while (rowIter.hasNext()) { HSSFRow row = (HSSFRow) rowIter.next(); rowCounter++; int progress = (int) ((float) rowCounter / totalRows * 100); state.setProgress(progress); if (progress == 100) { state.setStatus(EStatus.FINISHED); } if (rowCounter <= ROW_TABLE_HEAD) { continue; } HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId()); long causeCodeId = -1; if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { causeCodeId = Long.valueOf(causeCodeIdCell.getStringCellValue()); } else if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { causeCodeId = Math.round(causeCodeIdCell.getNumericCellValue()); } CauseCode causeCode = (CauseCode) getEntityManager().createNamedQuery("getCauseCodeById") .setParameter("id", causeCodeId).getSingleResult(); String causeCodeName = causeCode.getAlertCause().getName(); HSSFCell causeCodeCell = row.getCell(colIndexes.getColCauseCode()); String colCauseCode = null; if (causeCodeCell != null) { colCauseCode = causeCodeCell.getStringCellValue().trim(); } Date targetDate = causeCode.getTargetDate(); HSSFCell targetDateCell = row.getCell(colIndexes.getColTargetDate()); Date colTargetDate = null; if (targetDateCell != null) { if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(targetDateCell)) { colTargetDate = targetDateCell.getDateCellValue(); } else if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { colTargetDate = convertTextToDate(targetDateCell);//Convert Date Text to Date Object } } String owner = causeCode.getOwner(); HSSFCell ownerCell = row.getCell(colIndexes.getColOwner()); String colOwner = null; if (ownerCell != null) { colOwner = ownerCell.getStringCellValue().trim(); } //Assignee Comments Function Start if (colIndexes.getColAssigneeComments() != -1) { HSSFCell assigneeCommentsCell = row.getCell(colIndexes.getColAssigneeComments()); String assigneeComments = ""; if (assigneeCommentsCell != null && assigneeCommentsCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { assigneeComments = assigneeCommentsCell.getStringCellValue(); } if (assigneeComments != null && !"".equals(assigneeComments.trim())) { updateAssigneeComments(causeCode.getAlertId(), assigneeComments.trim(), colIndexes.getReportName().trim(), remoteUser); } } //Assignee Comments Function End boolean changed = false; if (!strCompare(causeCodeName, colCauseCode) || !dateCompare(targetDate, colTargetDate) || !strCompare(owner, colOwner)) { changed = true; } if (!changed) { continue; } CauseCodeHistory history = new CauseCodeHistory(); history.setCauseCode(causeCode); history.setAlertType(causeCode.getAlertType()); history.setAlertId(causeCode.getAlertId()); history.setAlertCause(causeCode.getAlertCause()); history.setTargetDate(causeCode.getTargetDate()); history.setOwner(causeCode.getOwner()); history.setRecordTime(causeCode.getRecordTime()); history.setRemoteUser(causeCode.getRemoteUser()); if (!strCompare(causeCodeName, colCauseCode)) { try { AlertCause alertCause = null; if ("UNDEFINED".equals(colCauseCode.trim().toUpperCase())) { alertCause = (AlertCause) getEntityManager() .createNamedQuery("findAlertCauseByNameWithoutShowInGui") .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult(); } else { alertCause = (AlertCause) getEntityManager().createNamedQuery("findAlertCauseByName") .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult(); } if (alertCause != null) { causeCode.setAlertCause(alertCause); } } catch (Exception e) { log.error(e.getMessage(), e); } } if (!dateCompare(targetDate, colTargetDate)) { causeCode.setTargetDate(colTargetDate); } if (!strCompare(owner, colOwner)) { causeCode.setOwner(colOwner); } causeCode.setRemoteUser(remoteUser); causeCode.setRecordTime(new Date()); try { getEntityManager().persist(history); getEntityManager().persist(causeCode); getEntityManager().flush(); } catch (Exception e) { log.error(e.getMessage(), e); } } }
From source file:com.ibm.ioes.actions.NewOrderAction.java
/** * @param templateStream /*from w w w .java2 s . c o m*/ * @method saveUploadedFileInfo * @purpose save uploaded file data in staging table in database * @param FormFile, * filepath, userName * @param excel_uploadPath, * uploadedFilePath * @return * @throws NpdException */ public int saveUploadedFileInfo(FormFile uploadedFile, int productID, String templateFilePath) throws IOESException { // AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() started"); int sheetCol, sheetRow; ArrayList<Object[][]> excelDataList = new ArrayList<Object[][]>(); int thisSaveCode = 0; int saveStatusCode = 0; try { String fileName = null; if (uploadedFile != null) { fileName = uploadedFile.getFileName(); } if (fileName != null) { HSSFWorkbook workbook = null; HSSFSheet sheet = null; HSSFRow rowInSheet = null; HSSFCell cellInSheet = null; workbook = new HSSFWorkbook(uploadedFile.getInputStream()); for (int count = 0; count < workbook.getNumberOfSheets() - 1; count++) { sheet = workbook.getSheetAt(count); sheetRow = sheet.getLastRowNum(); sheetCol = sheet.getRow(0).getLastCellNum(); Object excelData[][] = new Object[sheetRow][sheetCol]; for (int r = 1; r <= sheetRow; r++) { rowInSheet = sheet.getRow(r); int columIndex = 0; for (int c = 1; c < sheetCol + 1; c++) { if (rowInSheet != null) { cellInSheet = rowInSheet.getCell(c - 1); if (cellInSheet != null) { if (cellInSheet.getCellType() == 0) { excelData[r - 1][columIndex++] = Utility.convertWithOutE_WithOutDotZero( String.valueOf(cellInSheet.getNumericCellValue())); /*NumberFormat formatter = new DecimalFormat("0"); excelData[r - 1][columIndex++] = formatter .format(cellInSheet.getNumericCellValue());*/ } else { excelData[r - 1][columIndex++] = cellInSheet.toString().trim(); } } else { excelData[r - 1][columIndex++] = ""; } } else { excelData[r - 1][columIndex++] = ""; } } } excelDataList.add(excelData); } } //if (checkCode == 1) { NewOrderModel model = new NewOrderModel(); saveStatusCode = model.saveUploadedFileToTemporaryTable(excelDataList, productID, fileName); if (saveStatusCode > 0) { thisSaveCode = 1; } else { thisSaveCode = 0; } /*} else { thisSaveCode= 0; }*/ // AppConstants.IOES_LOGGER.info("Completed.."); return thisSaveCode; } catch (Exception ed) { ed.printStackTrace(); AppConstants.IOES_LOGGER.error("Error while getting saveUploadedFileInfo " + ed.getMessage()); throw new IOESException(ed); } finally { AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() completed"); } }
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 . jav a 2s .c o 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 w ww .j a v a 2s.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.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;/* w ww . j av a2s. co 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.ivstars.astrology.util.LocationProvider.java
License:Open Source License
public Location[] listLocations(String province) { HSSFSheet sheet = wb.getSheet(province); int begin = sheet.getFirstRowNum(); int end = sheet.getLastRowNum(); Location[] locs = new Location[end - begin + 1]; for (int i = begin; i <= end; i++) { locs[i - begin] = createLocation(sheet.getRow(i), province); }/*from ww w . j a v a2 s . c o m*/ return locs; }
From source file:com.kcs.action.ForwardContractAction.java
@Override @SuppressWarnings("empty-statement") public String export() throws Exception { list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate())); getLogger().debug("exportExcel : begin..."); setFileName("Forward contract Report.xls"); DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFCellStyle style = myWorkBook.createCellStyle(); HSSFCellStyle styleFont = myWorkBook.createCellStyle(); HSSFFont fontB = myWorkBook.createFont(); fontB.setBoldweight(Font.BOLDWEIGHT_BOLD); styleFont.setFont(fontB);//from w w w. j a v a 2s . c om list = sortSheet(list); List<String> listSheet = countSheet(list); for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) { List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet)); HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet)); List<String> currencyList = getCurrency(sheetObject); //------------------------- ROW 1 -----------------------------// Row FW1_row_0 = FW1.createRow(0); FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT"); CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER); HSSFCellStyle styleRow1 = myWorkBook.createCellStyle(); styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow1.setFont(fontB); FW1_row_0.getCell(0).setCellStyle(styleRow1); FW1_row_0.createCell(1) .setCellValue(" ? ?()"); FW1_row_0.getCell(1).setCellStyle(styleRow1); // FW1_row_0.getCell(8).setCellStyle(styleFont); //------------------------- ROW 2 -----------------------------// Row FW1_row_1 = FW1.createRow(1); FW1_row_1.createCell(0).setCellValue("FW"); // FW1_row_1.getCell(0).setCellStyle(styleBR); // FW1.addMergedRegion(new CellRangeAddress(1,1,7,8)); FW1.autoSizeColumn(0); FW1.autoSizeColumn(7); FW1.autoSizeColumn(8); Row FW1_row_2 = FW1.createRow(2); FW1_row_2.createCell(0).setCellValue("?"); //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT); HSSFCellStyle style2 = myWorkBook.createCellStyle(); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); FW1_row_2.getCell(0).setCellStyle(style2); Row FW1_row_3 = FW1.createRow(3); FW1_row_3.createCell(0).setCellValue("?"); HSSFCellStyle style3 = myWorkBook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_3.getCell(0).setCellStyle(style3); FW1.createRow(4).createCell(0).setCellValue("?"); FW1.createRow(5).createCell(0).setCellValue("? (129027)"); FW1.createRow(6).createCell(0).setCellValue( "???"); FW1.createRow(7).createCell(0).setCellValue(" ?"); FW1.createRow(8).createCell(0).setCellValue(" - ?? (129030)"); FW1.createRow(9).createCell(0) .setCellValue(" - ??? (129031)"); FW1.createRow(10).createCell(0).setCellValue(" ??"); FW1.createRow(11).createCell(0).setCellValue(" "); FW1.createRow(12).createCell(0).setCellValue(" - ?? (129034)"); FW1.createRow(13).createCell(0) .setCellValue(" - ??? (129035)"); FW1.createRow(14).createCell(0).setCellValue(" ???"); FW1.createRow(15).createCell(0).setCellValue(" - ?? (129037)"); FW1.createRow(16).createCell(0) .setCellValue(" - ??? (129038) "); FW1.createRow(17).createCell(0) .setCellValue(" ?"); FW1.createRow(18).createCell(0).setCellValue(" - ?? (129040)"); FW1.createRow(19).createCell(0) .setCellValue(" - ??? (129041) "); FW1.createRow(20).createCell(0) .setCellValue(" ??"); FW1.createRow(21).createCell(0).setCellValue(" - ?? (129043)"); FW1.createRow(22).createCell(0) .setCellValue(" - ??? (129044)"); FW1.createRow(23).createCell(0) .setCellValue(" ?"); FW1.createRow(24).createCell(0).setCellValue(" - ?? (129046)"); FW1.createRow(25).createCell(0) .setCellValue(" - ??? (129047) "); FW1.createRow(26).createCell(0).setCellValue("????"); FW1.createRow(27).createCell(0).setCellValue(" ??"); FW1.createRow(28).createCell(0).setCellValue(" - ?? (129050)"); FW1.createRow(29).createCell(0) .setCellValue(" - ??? (129051)"); FW1.createRow(30).createCell(0) .setCellValue(" "); FW1.createRow(31).createCell(0).setCellValue(" - ?? (129053)"); FW1.createRow(32).createCell(0) .setCellValue(" - ??? (129054)"); FW1.createRow(33).createCell(0).setCellValue(" ? "); FW1.createRow(34).createCell(0).setCellValue(" - ?? (129056) "); FW1.createRow(35).createCell(0) .setCellValue(" - ??? (129057) "); FW1.createRow(36).createCell(0).setCellValue(" "); FW1.createRow(37).createCell(0).setCellValue(" - ?? (129059) "); FW1.createRow(38).createCell(0) .setCellValue(" - ??? (129060) "); FW1.createRow(39).createCell(0).setCellValue("? (129061) "); FW1.createRow(40).createCell(0) .setCellValue("? (129062) "); // FW1.getRow(40).getCell(0).setCellStyle(styleBR); FW1.getRow(4).getCell(0).setCellStyle(styleFont); FW1.getRow(5).getCell(0).setCellStyle(styleFont); FW1.getRow(6).getCell(0).setCellStyle(styleFont); FW1.getRow(7).getCell(0).setCellStyle(styleFont); FW1.getRow(11).getCell(0).setCellStyle(styleFont); FW1.getRow(14).getCell(0).setCellStyle(styleFont); FW1.getRow(17).getCell(0).setCellStyle(styleFont); FW1.getRow(20).getCell(0).setCellStyle(styleFont); FW1.getRow(23).getCell(0).setCellStyle(styleFont); FW1.getRow(26).getCell(0).setCellStyle(styleFont); FW1.getRow(30).getCell(0).setCellStyle(styleFont); FW1.getRow(33).getCell(0).setCellStyle(styleFont); FW1.getRow(36).getCell(0).setCellStyle(styleFont); FW1.getRow(39).getCell(0).setCellStyle(styleFont); FW1.getRow(40).getCell(0).setCellStyle(styleFont); int cellCurrency = 1; for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); String currencyName = currencyList.get(iCurrencyList); FW1_row_2.createCell(cellCurrency).setCellValue(currencyName); FW1_row_3.createCell(cellCurrency).setCellValue(""); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style); FW1_row_3.getCell(cellCurrency).setCellStyle(style); List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName); for (ForwardContract objCCY : tmpGroupCCY) { FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + ""); if ("????" .equals(objCCY.getTRANS_TYPE())) { if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(8).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true)); FW1.getRow(8).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(9).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false)); FW1.getRow(9).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false)); } } else if ("????" .equals(objCCY.getTRANS_TYPE().trim())) { if ("New Forward".equals(objCCY.getLIST_ITEM())) { FW1.getRow(28).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true)); FW1.getRow(28).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(29).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false)); FW1.getRow(29).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false)); } } else if ("?".equals(objCCY.getTRANS_TYPE())) { } FW1.getRow(39).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false)); FW1.getRow(39).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false)); } // // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+""); CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency)); FW1_row_3.createCell(cellCurrency).setCellValue(""); HSSFCellStyle styleTop = style; styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN); FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop); cellCurrency++; } System.out.println("cellCurrency ++++ " + cellCurrency); int finishM = cellCurrency - 3; int noOfColumns = FW1.getRow(2).getLastCellNum(); int noOfRow = FW1.getLastRowNum(); // HSSFPalette palette = myWorkBook.getCustomPalette(); // HSSFColor hssfColor = null; // // // palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255); // hssfColor = palette.getColor(HSSFColor.WHITE.index); // // // // HSSFCellStyle styleBG = myWorkBook.createCellStyle(); // styleBG.setFillForegroundColor(hssfColor.getIndex()); // styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND); for (int i = 0; i < noOfRow; i++) { for (int j = 0; j < noOfColumns; j++) { FW1.autoSizeColumn(i); // row.getCell(j).setCellStyle(styleBG); } } for (int i = 4; i < noOfRow + 1; i++) { for (int j = 1; j < noOfColumns; j++) { HSSFCellStyle style6 = myWorkBook.createCellStyle(); try { if (j == 1) { style6.setBorderLeft(HSSFCellStyle.BORDER_THIN); style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } else { style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } if (i == noOfRow) { style6.setBorderBottom(HSSFCellStyle.BORDER_THIN); } FW1.getRow(i).getCell(j).setCellStyle(style6); } catch (Exception e) { FW1.getRow(i).createCell(j).setCellStyle(style6); } } } HSSFFont fontBold = myWorkBook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle(); borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN); borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER); borderRightCenterFontBold.setFont(fontBold); HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold; borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleR = myWorkBook.createCellStyle(); styleR.setBorderRight(HSSFCellStyle.BORDER_THIN); // Forward contract // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold); FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM)); FW1_row_0.createCell(finishM + 1) .setCellValue(" " + dateFormat.format(new Date()) + ""); HSSFCellStyle style4 = myWorkBook.createCellStyle(); style4.setBorderRight(HSSFCellStyle.BORDER_THIN); style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_0.getCell(finishM + 1).setCellStyle(style4); FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont); FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2)); FW1_row_1.createCell(finishM + 1).setCellValue( ":"); FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2)); FW1_row_1.getCell(finishM + 1).setCellStyle(style4); FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont); FW1_row_0.createCell(finishM + 2).setCellStyle(styleR); FW1_row_1.createCell(finishM + 2).setCellStyle(styleR); FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold); HSSFCellStyle borderBottom = myWorkBook.createCellStyle(); borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN); borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT); borderBottom.setFont(fontBold); FW1.getRow(40).getCell(0).setCellStyle(borderBottom); HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch(); /* Here is the thing: the line will go from top left in cell (0,0) to down left of cell (0,1) */ // int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3); HSSFSimpleShape shape = patriarch.createSimpleShape(anchor); shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); shape.setLineStyleColor(10, 10, 10); shape.setFillColor(90, 10, 200); shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT); shape.setLineStyle(HSSFShape.LINESTYLE_SOLID); // createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow); // FW1.createFreezePane(0,4); } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
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; }//w w w .j a v a 2s . 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; }