List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java
@Override public HSSFWorkbook printClaimRegister(List<PrintClaimRegisterPojo> listPojo) throws Exception { HSSFWorkbook workbook = null;/*from w w w.j a va 2 s . c o m*/ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( servletContext.getRealPath("templates") + File.separator + "print_claim_register.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); // title //HSSFRow row = sheet.getRow(1); //HSSFCell cell = row.getCell(1); //String title; //cell.setCellValue(title); // HSSFRow row; final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum() - 1; final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } // total style HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END]; short totalStyleHeight = sheet.getRow(rowTotal).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); int numQuantity = 0; for (PrintClaimRegisterPojo pcrp : listPojo) { numQuantity++; row = sheet.createRow(rowCurrent++); row.setHeight(styleHeight); cellCurrent = CELL_START; createCell(row, cellCurrent++, style, pcrp.getInvoiceNum()); createCell(row, cellCurrent++, style, pcrp.getPaymentNum()); createCell(row, cellCurrent++, style, dateFormat.format(pcrp.getPaymentDate())); createCell(row, cellCurrent++, style, pcrp.getTin()); createCell(row, cellCurrent++, style, pcrp.getName()); createCell(row, cellCurrent++, style, pcrp.getPaymentSum()); } List<Integer> totalCellIndexes = new ArrayList<>(); totalCellIndexes.add(CELL_START + 5); // for payment sum columns createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent); return workbook; }
From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java
@Override public HSSFWorkbook printPaymentManual(BankStatement bs) throws Exception { HSSFWorkbook workbook = null;// w ww . j a va 2 s . c om POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( servletContext.getRealPath("templates") + File.separator + "payment_manual.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow row = sheet.getRow(0); row.getCell(1).setCellValue(bs.getId()); final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum(); final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); row = sheet.createRow(3); createCell(row, 1, style, ""); createCell(row, 2, style, bs.getMfo()); createCell(row, 3, style, bs.getChet()); createCell(row, 4, style, bs.getPaymentNum()); createCell(row, 5, style, dateFormat.format(bs.getPaymentDate())); createCell(row, 6, style, ""); createCell(row, 7, style, bs.getTin()); return workbook; }
From source file:com.nkapps.billing.services.ReportPrintServiceImpl.java
@Override public HSSFWorkbook printClick(List<ReportClickListPojo> listPojos) throws Exception { HSSFWorkbook workbook = null;//from w w w. j a v a 2 s.co m POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream(servletContext.getRealPath("templates") + File.separator + "report_click.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); // title //HSSFRow row = sheet.getRow(1); //HSSFCell cell = row.getCell(1); //String title; //cell.setCellValue(title); // HSSFRow row; final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum() - 1; final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } // total style HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END]; short totalStyleHeight = sheet.getRow(rowTotal).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); int numQuantity = 0; for (ReportClickListPojo rclp : listPojos) { numQuantity++; row = sheet.createRow(rowCurrent++); row.setHeight(styleHeight); cellCurrent = CELL_START; createCell(row, cellCurrent++, style, numQuantity); createCell(row, cellCurrent++, style, dateFormat.format(rclp.getOperationDate())); createCell(row, cellCurrent++, style, dateFormat.format(rclp.getBsPaymentDate())); createCell(row, cellCurrent++, style, rclp.getBsPaymentNum()); createCell(row, cellCurrent++, style, rclp.getBsPaymentSum()); createCell(row, cellCurrent++, style, rclp.getClickPaymentSum()); createCell(row, cellCurrent++, style, rclp.getDiffSum()); } List<Integer> totalCellIndexes = new ArrayList<>(); totalCellIndexes.add(CELL_START + 4); // for totalCellIndexes.add(CELL_START + 5); // for totalCellIndexes.add(CELL_START + 6); // for createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent); return workbook; }
From source file:com.nkapps.billing.services.ReportPrintServiceImpl.java
@Override public HSSFWorkbook printSmst(List<ReportSmstListPojo> listPojos) throws Exception { HSSFWorkbook workbook = null;/* w w w . ja va 2 s . co m*/ POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream(servletContext.getRealPath("templates") + File.separator + "report_smst.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); // title //HSSFRow row = sheet.getRow(1); //HSSFCell cell = row.getCell(1); //String title; //cell.setCellValue(title); // HSSFRow row; final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum() - 1; final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } // total style HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END]; short totalStyleHeight = sheet.getRow(rowTotal).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); int numQuantity = 0; for (ReportSmstListPojo rslp : listPojos) { numQuantity++; row = sheet.createRow(rowCurrent++); row.setHeight(styleHeight); cellCurrent = CELL_START; createCell(row, cellCurrent++, style, numQuantity); createCell(row, cellCurrent++, style, dateFormat.format(rslp.getOperationDate())); createCell(row, cellCurrent++, style, rslp.getBsPaymentSum()); createCell(row, cellCurrent++, style, rslp.getSmstPaymentSum()); createCell(row, cellCurrent++, style, rslp.getDiffSum()); } List<Integer> totalCellIndexes = new ArrayList<>(); totalCellIndexes.add(CELL_START + 2); // for totalCellIndexes.add(CELL_START + 3); // for totalCellIndexes.add(CELL_START + 4); // for createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent); return workbook; }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun() throws FileNotFoundException, IOException { LOGGER.info(/*ww w .j a va2 s . c o m*/ "Upper Spec Limit (USL Shipping) executed at everyday on 8:00 am. Current time is : " + new Date()); String username = System.getProperty("user.name"); if (!"fg79cj".equals(username)) { username = "imperial"; } DateFormat dateFormat = new SimpleDateFormat("ddMMMyyyy"); Date date = new Date(); String todayDate = dateFormat.format(date); String reportName = "C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Sending to SBN Factory Report (" + todayDate + ").xls"; FileOutputStream fileOut = new FileOutputStream(reportName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("HIMS PROCESS EXCEED USL"); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(HSSFFont.COLOR_NORMAL); font.setBold(true); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); sheet.createFreezePane(0, 1); // Freeze 1st Row HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setRowStyle(style); HSSFCell cell1_0 = rowhead.createCell(0); cell1_0.setCellStyle(style); cell1_0.setCellValue("HARDWARE TYPE"); HSSFCell cell1_1 = rowhead.createCell(1); cell1_1.setCellStyle(style); cell1_1.setCellValue("HARDWARE ID"); HSSFCell cell1_2 = rowhead.createCell(2); cell1_2.setCellStyle(style); cell1_2.setCellValue("MATERIAL PASS NO"); HSSFCell cell1_3 = rowhead.createCell(3); cell1_3.setCellStyle(style); cell1_3.setCellValue("DURATION"); HSSFCell cell1_4 = rowhead.createCell(4); cell1_4.setCellStyle(style); cell1_4.setCellValue("CURRENT STATUS"); // WhUSLDAO whUslDAO = new WhUSLDAO(); // List<WhUSL> whUslList = whUslDAO.getWhUSLLog(); String materialPassNo = ""; String hardwareId = ""; String hardwareType = ""; String duration = ""; String status = ""; String text = ""; WhStatusLogDAO statusD = new WhStatusLogDAO(); List<WhStatusLog> whUslList = statusD.getTLReqToApproveAndApproveToMpCreatedList(); boolean checksize1 = false; boolean checksize2 = false; for (int i = 0; i < whUslList.size(); i++) { checksize1 = true; hardwareType = whUslList.get(i).getEquipmentType(); hardwareId = whUslList.get(i).getEquipmentId(); materialPassNo = whUslList.get(i).getMpNo(); String hourReqApp = whUslList.get(i).getRequestToApprove24(); String hourReqAppIfNull = whUslList.get(i).getRequestToApproveTemp24(); String hourAppMp = whUslList.get(i).getApproveToMPCreated24(); String hourAppMpIfNull = whUslList.get(i).getApproveToMPCreatedTemp24(); boolean flag = false; if (hourReqAppIfNull != null) { if (Integer.parseInt(hourReqAppIfNull) >= 24 && hourReqApp == null) { duration = whUslList.get(i).getRequestToApproveTemp(); status = "Pending Approval"; flag = true; } } if (hourAppMpIfNull != null) { if (Integer.parseInt(hourAppMpIfNull) >= 24 && hourAppMp == null && hourReqApp != null) { duration = whUslList.get(i).getApproveToMPCreatedTemp(); status = "Pending Material Pass Number"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } WhStatusLogDAO statusD2 = new WhStatusLogDAO(); List<WhStatusLog> whUslList2 = statusD2.getTLMpCreatedToFinalInventoryDateList(); for (int i = 0; i < whUslList2.size(); i++) { checksize2 = true; hardwareType = whUslList2.get(i).getEquipmentType(); hardwareId = whUslList2.get(i).getEquipmentId(); materialPassNo = whUslList2.get(i).getMpNo(); String hourMpTt = whUslList2.get(i).getMpCreatedToTtScan24(); String hourMpTtIfNull = whUslList2.get(i).getMpCreatedToTtScanTemp24(); String hourTtBs = whUslList2.get(i).getTtScanToBsScan24(); String hourTtBsIfNull = whUslList2.get(i).getTtScanToBsScanTemp24(); String hourBsShip = whUslList2.get(i).getBsScanToShip24(); String hourBsShipIfNull = whUslList2.get(i).getBsScanToShipTemp24(); String hourShipInv = whUslList2.get(i).getShipToInventory24(); String hourShipInvIfNull = whUslList2.get(i).getShipToInventoryTemp24(); boolean flag = false; if (hourMpTtIfNull != null) { if (Integer.parseInt(hourMpTtIfNull) >= 24 && hourMpTt == null) { duration = whUslList2.get(i).getMpCreatedToTtScanTemp(); status = "Pending Trip Ticket Scanning"; flag = true; } } if (hourTtBsIfNull != null) { if (Integer.parseInt(hourTtBsIfNull) >= 24 && hourTtBs == null && hourMpTt != null) { duration = whUslList2.get(i).getTtScanToBsScanTemp(); status = "Pending Barcode Sticker Scanning"; flag = true; } } if (hourBsShipIfNull != null) { if (Integer.parseInt(hourBsShipIfNull) >= 24 && hourBsShip == null && hourTtBs != null && hourMpTt != null) { duration = whUslList2.get(i).getBsScanToShipTemp(); status = "Pending Shipping Packing List"; flag = true; } } if (hourShipInvIfNull != null) { if (Integer.parseInt(hourShipInvIfNull) >= 24 && hourShipInv == null && hourBsShip != null && hourTtBs != null && hourMpTt != null) { duration = whUslList2.get(i).getShipToInventoryTemp(); status = "Pending Inventory in Seremban Factory"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } if (checksize1 == true || checksize2 == true) { workbook.write(fileOut); workbook.close(); //send email LOGGER.info("send email to person in charge"); EmailSender emailSender = new EmailSender(); com.onsemi.cdars.model.User user = new com.onsemi.cdars.model.User(); user.setFullname("All"); List<String> a = new ArrayList<String>(); String emailApprover = ""; String emaildistList1 = ""; String emaildistList2 = ""; String emaildistList3 = ""; String emaildistList4 = ""; emailApprover = "fg79cj@onsemi.com"; a.add(emailApprover); EmailConfigDAO econfD = new EmailConfigDAO(); int countDistList1 = econfD.getCountTask("Dist List 1"); if (countDistList1 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList1 = econfD.getEmailConfigByTask("Dist List 1"); emaildistList1 = distList1.getEmail(); a.add(emaildistList1); } econfD = new EmailConfigDAO(); int countDistList2 = econfD.getCountTask("Dist List 2"); if (countDistList2 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList2 = econfD.getEmailConfigByTask("Dist List 2"); emaildistList2 = distList2.getEmail(); a.add(emaildistList2); } econfD = new EmailConfigDAO(); int countDistList3 = econfD.getCountTask("Dist List 3"); if (countDistList3 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList3 = econfD.getEmailConfigByTask("Dist List 3"); emaildistList3 = distList3.getEmail(); a.add(emaildistList3); } econfD = new EmailConfigDAO(); int countDistList4 = econfD.getCountTask("Dist List 4"); if (countDistList4 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList4 = econfD.getEmailConfigByTask("Dist List 4"); emaildistList4 = distList4.getEmail(); a.add(emaildistList4); } String[] myArray = new String[a.size()]; String[] emailTo = a.toArray(myArray); // String[] to = {"fg79cj@onsemi.com"}; emailSender.htmlEmailWithAttachment(servletContext, user, //user name requestor emailTo, new File("C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Sending to SBN Factory Report (" + todayDate + ").xls"), "List of Hardware Exceed USL (24 hours) for Sending to SBN Factory", //subject "Report for Hardware Process from HIMS(Hadware Sending to SBN Factory) that exceed Upper Specs Limit (24 hours) has been made. <br />" + "Hence, attached is the report file for your view and perusal. <br /><br />" + "<br /><br /> " + "<style>table, th, td {border: 1px solid black;} </style>" + "<table style=\"width:100%\">" //tbl + "<tr>" + "<th>HARDWARE TYPE</th> " + "<th>HARDWARE ID</th> " + "<th>MATERIAL PASS NO.</th>" + "<th>DURATION</th>" + "<th>CURRENT STATUS</th>" + "</tr>" + table() + "</table>" + "<br />Thank you." //msg ); } // } }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun2() throws FileNotFoundException, IOException { LOGGER.info("Upper Spec Limit (USL Retrieval) executed at everyday on 8:00 am. Current time is : " + new Date()); String username = System.getProperty("user.name"); if (!"fg79cj".equals(username)) { username = "imperial"; }/*from w ww . ja v a2s .c o m*/ DateFormat dateFormat = new SimpleDateFormat("ddMMMyyyy"); Date date = new Date(); String todayDate = dateFormat.format(date); String reportName = "C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Retrieving from SBN Factory Report (" + todayDate + ").xls"; FileOutputStream fileOut = new FileOutputStream(reportName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("HIMS PROCESS EXCEED USL"); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(HSSFFont.COLOR_NORMAL); font.setBold(true); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); sheet.createFreezePane(0, 1); // Freeze 1st Row HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setRowStyle(style); HSSFCell cell1_0 = rowhead.createCell(0); cell1_0.setCellStyle(style); cell1_0.setCellValue("HARDWARE TYPE"); HSSFCell cell1_1 = rowhead.createCell(1); cell1_1.setCellStyle(style); cell1_1.setCellValue("HARDWARE ID"); HSSFCell cell1_2 = rowhead.createCell(2); cell1_2.setCellStyle(style); cell1_2.setCellValue("MATERIAL PASS NO"); HSSFCell cell1_3 = rowhead.createCell(3); cell1_3.setCellStyle(style); cell1_3.setCellValue("DURATION"); HSSFCell cell1_4 = rowhead.createCell(4); cell1_4.setCellStyle(style); cell1_4.setCellValue("CURRENT STATUS"); String materialPassNo = ""; String hardwareId = ""; String hardwareType = ""; String duration = ""; String status = ""; String text = ""; WhStatusLogDAO statusD = new WhStatusLogDAO(); List<WhStatusLog> whUslList = statusD.getTLRetrieveRequestToCloseList(); boolean checksize1 = false; for (int i = 0; i < whUslList.size(); i++) { checksize1 = true; hardwareType = whUslList.get(i).getEquipmentType(); hardwareId = whUslList.get(i).getEquipmentId(); materialPassNo = whUslList.get(i).getMpNo(); String hourReqVer = whUslList.get(i).getRequestToVerifiedDate24(); String hourReqVerIfNull = whUslList.get(i).getRequestToVerifiedDateTemp24(); String hourVerShip = whUslList.get(i).getVerifiedDatetoShipDate24(); String hourVerShipIfNull = whUslList.get(i).getVerifiedDatetoShipDateTemp24(); String hourShipBScan = whUslList.get(i).getShipDateToBsScan24(); String hourShipBScanIfNull = whUslList.get(i).getShipDateToBsScanTemp24(); String hourBScanTT = whUslList.get(i).getBsScanToTtScan24(); String hourBScanTTIfNull = whUslList.get(i).getBsScanToTtScanTemp24(); boolean flag = false; if (hourReqVerIfNull != null) { if (Integer.parseInt(hourReqVerIfNull) >= 24 && hourReqVer == null) { duration = whUslList.get(i).getRequestToVerifiedDateTemp(); status = "Pending Box Barcode Verification at SBN Factory"; flag = true; } } if (hourVerShipIfNull != null) { if (Integer.parseInt(hourVerShipIfNull) >= 24 && hourVerShip == null && hourReqVer != null) { duration = whUslList.get(i).getVerifiedDatetoShipDateTemp(); status = "Pending Shipping Packing List"; flag = true; } } if (hourShipBScanIfNull != null) { if (Integer.parseInt(hourShipBScanIfNull) >= 24 && hourShipBScan == null && hourVerShip != null) { duration = whUslList.get(i).getShipDateToBsScanTemp(); status = "Pending Box Barcode Verification at Rel Lab"; flag = true; } } if (hourBScanTTIfNull != null) { if (Integer.parseInt(hourBScanTTIfNull) >= 24 && hourBScanTT == null && hourShipBScan != null) { duration = whUslList.get(i).getBsScanToTtScanTemp(); status = "Pending Trip Ticket Verification at Rel Lab"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } if (checksize1 == true) { workbook.write(fileOut); workbook.close(); //send email LOGGER.info("send email to person in charge"); EmailSender emailSender = new EmailSender(); com.onsemi.cdars.model.User user = new com.onsemi.cdars.model.User(); user.setFullname("All"); List<String> a = new ArrayList<String>(); String emailApprover = ""; String emaildistList1 = ""; String emaildistList2 = ""; String emaildistList3 = ""; String emaildistList4 = ""; emailApprover = "fg79cj@onsemi.com"; a.add(emailApprover); EmailConfigDAO econfD = new EmailConfigDAO(); int countDistList1 = econfD.getCountTask("Dist List 1"); if (countDistList1 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList1 = econfD.getEmailConfigByTask("Dist List 1"); emaildistList1 = distList1.getEmail(); a.add(emaildistList1); } econfD = new EmailConfigDAO(); int countDistList2 = econfD.getCountTask("Dist List 2"); if (countDistList2 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList2 = econfD.getEmailConfigByTask("Dist List 2"); emaildistList2 = distList2.getEmail(); a.add(emaildistList2); } econfD = new EmailConfigDAO(); int countDistList3 = econfD.getCountTask("Dist List 3"); if (countDistList3 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList3 = econfD.getEmailConfigByTask("Dist List 3"); emaildistList3 = distList3.getEmail(); a.add(emaildistList3); } econfD = new EmailConfigDAO(); int countDistList4 = econfD.getCountTask("Dist List 4"); if (countDistList4 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList4 = econfD.getEmailConfigByTask("Dist List 4"); emaildistList4 = distList4.getEmail(); a.add(emaildistList4); } String[] myArray = new String[a.size()]; String[] emailTo = a.toArray(myArray); // String[] to = {"hmsrelon@gmail.com", "hmsrelontest@gmail.com"}; //9/11/16 // String[] to = {"fg79cj@onsemi.com"}; emailSender.htmlEmailWithAttachment(servletContext, user, //user name requestor emailTo, new File("C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Retrieving from SBN Factory Report (" + todayDate + ").xls"), "List of Hardware Exceed USL (24 hours) for Retrieval from SBN Factory", //subject "Report for Hardware Process from HIMS(Hadware Retrieval from SBN Factory) that exceed Upper Specs Limit (24 hours) has been made. <br />" + "Hence, attached is the report file for your view and perusal. <br /><br />" + "<br /><br /> " + "<style>table, th, td {border: 1px solid black;} </style>" + "<table style=\"width:100%\">" //tbl + "<tr>" + "<th>HARDWARE TYPE</th> " + "<th>HARDWARE ID</th> " + "<th>MATERIAL PASS NO.</th>" + "<th>DURATION</th>" + "<th>CURRENT STATUS</th>" + "</tr>" + table2() + "</table>" + "<br />Thank you." //msg ); } // } }
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 ww w. ja v a 2s .c om 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
public boolean deleteTestCasesFromXls(String filePath, String testSuiteName, String testCaseId) { boolean hasTrue = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); Iterator<Row> rowIterator; try {/*from ww w .j ava 2 s . c om*/ FileInputStream myInput = new FileInputStream(filePath); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(testSuiteName)) { rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) { tstCase = readTest(next); mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; HSSFRow row = mySheet.getRow(newNum); if (row != null) { mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); } hasTrue = true; break; } } } } if (hasTrue) { for (int j = 0; j < numberOfSheets; j++) { HSSFSheet myHSSFSheet = myWorkBook.getSheetAt(j); if (myHSSFSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHSSFSheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); TestCase createObject = readTest(next); if (StringUtils.isNotEmpty(createObject.getTestCaseId())) { testCases.add(createObject); } } float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; int totalTestCases = testCases.size(); for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } // if(tstCase.getStatus().equalsIgnoreCase("success")) { // totalPass = totalPass - 1; // } else if (tstCase.getStatus().equalsIgnoreCase("failure")) { // totalFail = totalFail - 1; // } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) { // totalNotApplicable = totalNotApplicable - 1; // } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) { // totalBlocked = totalBlocked - 1; // } HSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (StringUtils.isNotEmpty(tstCase.getTestCaseId()) && createObject.getName().equals(testSuiteName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, totalTestCases, "delete"); } } } } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void writeTestCaseToXLS(String testSuiteName, String[] cellValue, String status, int numCol, int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException { Iterator<Row> rowIterator; try {//from www. ja v a2 s .com FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet myHssfSheet = myWorkBook.getSheetAt(j); if (myHssfSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHssfSheet.rowIterator(); Row next; for (Cell cell : myHssfSheet.getRow((myHssfSheet.getLastRowNum()) - 1)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; if (cellno == 15) { break; } } float totalPass = 0; float totalFail = 0; float totalNotApp = 0; float totalBlocked = 0; float notExecuted = 0; float totalTestCases = 0; for (int i = 0; i <= 22; i++) { rowIterator.next(); } do { next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(1))) && !getValue(next.getCell(0)).equalsIgnoreCase("S.NO")) { String value = getValue(next.getCell(11)); if (StringUtils.isNotEmpty(value)) { if (value.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (value.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (value.equalsIgnoreCase("notApplicable")) { totalNotApp = totalNotApp + 1; } else if (value.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } else { notExecuted = notExecuted + 1; } } } while (rowIterator.hasNext()); //to update the status in the index page if (status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (status.equalsIgnoreCase("notApplicable")) { totalNotApp = totalNotApp + 1; } else if (status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } else { notExecuted = notExecuted + 1; } totalTestCases = totalPass + totalFail + totalNotApp + totalBlocked + notExecuted; HSSFSheet mySheetHssf = myWorkBook.getSheetAt(0); rowIterator = mySheetHssf.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (createObject.getName().equals(testSuiteName)) { addCalculationsToIndex(totalPass, totalFail, totalNotApp, totalBlocked, notExecuted, totalTestCases, next1); } } } Row r = null; if (myHssfSheet.getSheetName().equalsIgnoreCase("Index")) { r = myHssfSheet.createRow(next.getRowNum() - 1); } else { r = myHssfSheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); if (tryStyle[i] != null) { cell.setCellStyle(tryStyle[i]); } } FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } } } catch (PhrescoException e) { throw new PhrescoException(e); } catch (FileNotFoundException e) { throw new PhrescoException(e); } catch (IOException e) { throw new PhrescoException(e); } }
From source file:com.proem.exm.service.wholesaleGroupPurchase.customer.impl.CustomerInfoServiceImpl.java
@Override public Map<String, Object> getAllByExcel(String path) { Map returnMap = new HashMap(); String returnAnwer = ""; List<CustomerInfo> list = new ArrayList<CustomerInfo>(); try {/*from ww w .j av a 2 s . co m*/ HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(path)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null;// excel HSSFCell cell = null;// excel int totalRow = sheet.getLastRowNum(); for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); if (row != null) { CustomerInfo obj = new CustomerInfo(); HSSFCell obj_id = row.getCell(0); if (getValue(obj_id) != null) { obj.setId(getValue(obj_id)); } HSSFCell obj_code = row.getCell(1); obj.setCode(getValue(obj_code) == null ? "" : getValue(obj_code)); HSSFCell obj_name = row.getCell(2); obj.setName(getValue(obj_name) == null ? "" : getValue(obj_name)); HSSFCell obj_memory_code = row.getCell(3); obj.setMnemonicCode(getValue(obj_memory_code) == null ? "" : getValue(obj_memory_code)); HSSFCell defaultPrice = row.getCell(4); //obj.setDefaultPrice(getValue(defaultPrice) == null ? "" : getValue(defaultPrice)); obj.setDefaultPrice("WHOLESALE_PRICE"); HSSFCell discount = row.getCell(5); obj.setDiscount(getValue(discount) == null ? "" : getValue(discount)); HSSFCell ownedStores = row.getCell(6); obj.setOwnedStores(getValue(ownedStores) == null ? "" : getValue(ownedStores)); HSSFCell preferentialWay = row.getCell(7); obj.setPreferentialWay(getValue(preferentialWay) == null ? "" : getValue(preferentialWay)); HSSFCell settlementcycle = row.getCell(8); obj.setSettlementcycle(getValue(settlementcycle) == null ? "" : getValue(settlementcycle)); HSSFCell settlementdate = row.getCell(9); obj.setSettlementdate(getValue(settlementdate) == null ? "" : getValue(settlementdate)); HSSFCell settlementway = row.getCell(10); //obj.setSettlementway(getValue(settlementway)== null ? "" : getValue(settlementway)); obj.setSettlementway("?"); HSSFCell creditLimit = row.getCell(11); obj.setCreditLimit(getValue(creditLimit) == null ? "" : getValue(creditLimit)); HSSFCell saleman = row.getCell(12); obj.setSaleman(getValue(saleman) == null ? "" : getValue(saleman)); HSSFCell area = row.getCell(13); //obj.setArea(getValue(area) == null ? "" : getValue(area)); obj.setArea(""); HSSFCell type = row.getCell(14); obj.setType(getValue(type) == null ? "" : getValue(type)); HSSFCell linkman = row.getCell(15); obj.setLinkman(getValue(linkman) == null ? "" : getValue(linkman)); HSSFCell address = row.getCell(16); obj.setAddress(getValue(address) == null ? "" : getValue(address)); HSSFCell mail = row.getCell(17); obj.setMail(getValue(mail) == null ? "" : getValue(mail)); HSSFCell telephone = row.getCell(18); obj.setTelephone(getValue(telephone) == null ? "" : getValue(telephone)); HSSFCell bank = row.getCell(19); obj.setBank(getValue(bank) == null ? "" : getValue(bank)); HSSFCell mobilephone = row.getCell(20); obj.setMobilephone(getValue(mobilephone) == null ? "" : getValue(mobilephone)); HSSFCell taxregistration = row.getCell(21); obj.setTaxregistration(getValue(taxregistration) == null ? "" : getValue(taxregistration)); HSSFCell fax = row.getCell(22); obj.setFax(getValue(fax) == null ? "" : getValue(fax)); HSSFCell license = row.getCell(23); obj.setLicense(getValue(license) == null ? "" : getValue(license)); HSSFCell postcode = row.getCell(24); obj.setPostcode(getValue(postcode) == null ? "" : getValue(postcode)); HSSFCell account = row.getCell(25); obj.setAccount(getValue(account) == null ? "" : getValue(account)); HSSFCell frozen = row.getCell(26); obj.setFrozen(getValue(frozen) == null ? "" : getValue(frozen)); HSSFCell remark = row.getCell(27); obj.setRemark(getValue(remark) == null ? "" : getValue(remark)); list.add(obj); } } returnMap.put("returnAnwer", returnAnwer); returnMap.put("listSupply", list); return returnMap; } catch (Exception e) { e.printStackTrace(); } return null; }