List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write
private void write(POIFSFileSystem fs) throws IOException
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the branch Inward Rejected report in to Excel *//*from w w w . j a va2s.co m*/ public void generateBrInwRejectedExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double totTxnAmt = 0.0; BigDecimal totTxnAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Br. Inward Rejected Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); if (getReportDto().getPaymentType().equalsIgnoreCase("R42")) { cell.setCellValue("Branch Inward Rejected Report - Inward Interbank payment from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); } else { cell.setCellValue("Branch Inward Rejected Report - Inward Customer payment from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 7); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 10; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Sub Msg Type"); } else if (j == 3) { cell.setCellValue("UTR No"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Rejected By"); } else if (j == 7) { cell.setCellValue("Rejection Approved By"); } else if (j == 8) { cell.setCellValue("Remarks"); } else if (j == 9) { cell.setCellValue("Amount (Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String entryBy = null; if (((ReportDTO) exportXLS.get(roww - 1)).getEntryBy() != null) { entryBy = ((ReportDTO) exportXLS.get(roww - 1)).getEntryBy(); } cell.setCellValue(entryBy); } else if (j == 7) { String passBy = null; if (((ReportDTO) exportXLS.get(roww - 1)).getPassBy() != null) { passBy = ((ReportDTO) exportXLS.get(roww - 1)).getPassBy(); } cell.setCellValue(passBy); } else if (j == 8) { String remarks = null; if (((ReportDTO) exportXLS.get(roww - 1)).getRemarks() != null) { remarks = ((ReportDTO) exportXLS.get(roww - 1)).getRemarks(); } cell.setCellValue(remarks); } else if (j == 9) { // double txnAmount = 0.0; String txnAmount = "0"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 8); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 9); cell.setCellValue(totTxnAmt.setScale(2).toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Reconcillation report in to Excel *//*from ww w. java 2s .com*/ public void generateRTGSReconcillationExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Reconciliation Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Reconciliation Report on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 6; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Message Type"); } else if (j == 3) { cell.setCellValue("Transaction Type"); } else if (j == 4) { cell.setCellValue("Total Txn"); } else if (j == 5) { cell.setCellValue("Total Txn Amount(Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 4) { long txnCount = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { txnCount = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(txnCount); } else if (j == 5) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Generate Counter Party Wise Reconcillation report in to Excel *///from w w w . j a v a2s .com public void generateReconcilliationReportCPwiseExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double outerGrandTotTxnAmt = 0.0; BigDecimal outerGrandTotTxnAmt = BigDecimal.ZERO; //Only If the list is not empty if (getReportMap().size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "CP Wise Reconciliation Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Counter Party wise Reconciliation Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, Map<String, List<ReportDTO>>> entry = (Map.Entry<String, Map<String, List<ReportDTO>>>) z .next(); // double grandTotTxnAmt = 0.0; BigDecimal grandTotTxnAmt = BigDecimal.ZERO; String cp = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Counter Party : " + cp); row = sheet.createRow(rowCount); rowCount += 1; Map map = entry.getValue(); if (map != null && map.size() > 0) { Set innerSet = map.entrySet(); for (short j = 0; j < 7; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Tran Type"); } else if (j == 3) { cell.setCellValue("Msg Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } } for (Iterator e = innerSet.iterator(); e.hasNext();) { Map.Entry<String, List<ReportDTO>> innerEntry = (Map.Entry<String, List<ReportDTO>>) e .next(); String msgType = innerEntry.getKey(); List exportXLS = new ArrayList(1); exportXLS.addAll(innerEntry.getValue()); if (exportXLS.size() > 0) { // double totTxnAmt = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; long sno = 0; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(msgType); for (int i = exportXLS.size(), roww = 1; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 7; j++) { cell = row.createCell(j); // Setting values in cell for each and // every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String type = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { type = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(type); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)) .getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount).setScale(2)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("Sub Total Amount (Msg Type : " + msgType + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(totTxnAmt.toString()); grandTotTxnAmt = grandTotTxnAmt.add(totTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount (Counter Partywise : " + cp + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(grandTotTxnAmt.setScale(2).toString()); outerGrandTotTxnAmt = outerGrandTotTxnAmt.add(grandTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount : "); cell = row.createCell((short) 6); cell.setCellValue(outerGrandTotTxnAmt.setScale(2).toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Generate Counter Party Wise Reconcillation report in to Excel *///from www. ja v a 2s . c o m public void generateReconcilliationReportBranchwiseExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double outerGrandTotTxnAmt = 0.0; BigDecimal outerGrandTotTxnAmt = BigDecimal.ZERO; //Only If the list is not empty if (getReportMap().size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Br. Wise Reconciliation Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Wise Reconcillation Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, Map<String, List<ReportDTO>>> entry = (Map.Entry<String, Map<String, List<ReportDTO>>>) z .next(); // double grandTotTxnAmt = 0.0; BigDecimal grandTotTxnAmt = BigDecimal.ZERO; String branch = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch : " + branch); row = sheet.createRow(rowCount); rowCount += 1; Map map = entry.getValue(); if (map != null && map.size() > 0) { Set innerSet = map.entrySet(); for (short j = 0; j < 7; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Tran Type"); } else if (j == 3) { cell.setCellValue("Msg Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } } for (Iterator e = innerSet.iterator(); e.hasNext();) { Map.Entry<String, List<ReportDTO>> innerEntry = (Map.Entry<String, List<ReportDTO>>) e .next(); String msgType = innerEntry.getKey(); List exportXLS = new ArrayList(1); exportXLS.addAll(innerEntry.getValue()); if (exportXLS.size() > 0) { // double totTxnAmt = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; long sno = 0; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(msgType); for (int i = exportXLS.size(), roww = 1; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 7; j++) { cell = row.createCell(j); // Setting values in cell for each and // every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String type = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { type = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(type); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)) .getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("Sub Total Amount (Msg Type : " + msgType + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(totTxnAmt.setScale(2).toString()); grandTotTxnAmt = grandTotTxnAmt.add(totTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount (Branch : " + branch + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(grandTotTxnAmt.setScale(2).toString()); outerGrandTotTxnAmt = outerGrandTotTxnAmt.add(grandTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount : "); cell = row.createCell((short) 6); cell.setCellValue(outerGrandTotTxnAmt.toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the outward returned report in to Excel *///w w w. j ava 2 s . c o m public void generateOwReturnedExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double totTxnAmt = 0.0; BigDecimal totTxnAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Outward Returned Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Outward Returned Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 9; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Sender Address"); } else if (j == 3) { cell.setCellValue("Receiver Address"); } else if (j == 4) { cell.setCellValue("UTR No"); } else if (j == 5) { cell.setCellValue("Original UTR No"); } else if (j == 6) { cell.setCellValue("Info"); } else if (j == 7) { cell.setCellValue("Additional Info"); } else if (j == 8) { cell.setCellValue("Amount (Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); cell.setCellValue(valueDate); } else if (j == 2) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 3) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 4) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 5) { String orgUtrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo() != null) { orgUtrNo = ((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo(); } cell.setCellValue(orgUtrNo); } else if (j == 6) { String i7495 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldI7495() != null) { i7495 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldI7495(); } cell.setCellValue(i7495); } else if (j == 7) { String a7495 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldA7495() != null) { a7495 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldA7495(); } cell.setCellValue(a7495); } else if (j == 8) { //double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 7); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 8); cell.setCellValue(totTxnAmt.setScale(2).toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method for Cretaing Excel for NEFT RTGS Net Settlement Report *//*w w w . j a v a2s .c om*/ public void NEFTRTGS_NetSettlementExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; //To add serial number by priyak Map LMSaggregateMap = new HashMap(); int rowCount = 0; exportXLS = this.getNEFTRTGS_settlementList(); LMSaggregateMap = this.getNEFTRTGS_settlementMap(); rowCount = exportXLS.size(); HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "NEFT-RTGS Net Settlement Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Net Settlements received from RTGS"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Date :"); cell = row.createCell((short) 1); cell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; if (exportXLS.size() != 0) { rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 6; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("Msg Type"); } else if (j == 1) { cell.setCellValue("Ordering Institution"); } else if (j == 2) { cell.setCellValue("Code"); } else if (j == 3) { cell.setCellValue("Info"); } else if (j == 4) { cell.setCellValue("Additional Info"); } else if (j == 5) { cell.setCellValue("Amount"); } } else { // Setting values in cell for each and every row if (j == 0) { String msgType = ""; msgType = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getMsgType() + ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getMsgSubType(); cell.setCellValue(msgType); } else if (j == 1) { String orderingInstitution = ""; orderingInstitution = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)) .getOrderingInstitution(); cell.setCellValue((orderingInstitution == null) ? "" : orderingInstitution); } else if (j == 2) { String code = ""; code = this.getNEFTRTGS_NetSettleKeyword(); cell.setCellValue((code == null) ? "" : code); } else if (j == 3) { String info = ""; info = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getInfo(); cell.setCellValue((info == null) ? "" : info); } else if (j == 4) { String additionalInfo = ""; additionalInfo = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)) .getAdditionalInfo(); cell.setCellValue((additionalInfo == null) ? "" : additionalInfo); } else if (j == 5) { String amount = ""; amount = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getAmount(); cell.setCellValue((amount == null) ? "0.00" : amount); } } cell.setCellStyle(caption_style); } } } row = sheet.createRow(rowCount); rowCount += 1; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("LMS NEFT Aggregate"); int rows = 0; short j; if (LMSaggregateMap.size() > 0) { Set<Map.Entry<String, String>> entrySet = LMSaggregateMap.entrySet(); row = sheet.createRow(rowCount); rowCount += 1; for (j = 0; j < 3; j++) { cell = row.createCell(j); if (rows == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Aggregate Amount\r\n(Total Credit-Total Debit)"); } } } for (Iterator<Map.Entry<String, String>> i = entrySet.iterator(); i.hasNext(); rows++) { Map.Entry<String, String> entry = i.next(); // String key = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; for (j = 0; j < 3; j++) { cell = row.createCell(j); if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String batchTime = ""; batchTime = entry.getKey(); cell.setCellValue(batchTime); } else if (j == 2) { String totAmount = "0.00"; totAmount = entry.getValue(); //cell.setCellValue(FormatAmount.formatINRAmount(totAmount)); cell.setCellValue(new BigDecimal(totAmount).setScale(2).toString()); } } } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun() throws FileNotFoundException, IOException { LOGGER.info(/*w w w .j av a 2 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 w w . j a v a2 s .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.openitech.util.HSSFWrapper.java
License:Apache License
public static final void openWorkbook(HSSFWorkbook workbook) throws IllegalAccessException, IllegalArgumentException, IllegalArgumentException, InvocationTargetException, IOException { String filename = System.getProperty("java.io.tmpdir") + System.getProperty("file.separator") + Calendar.getInstance().getTimeInMillis() + "_export.xls"; java.io.File file = new java.io.File(filename); if (!System.getProperty("os.name").equals("Linux")) { file.deleteOnExit();//from w w w . j av a 2 s. c om } java.io.FileOutputStream out = new java.io.FileOutputStream(file); workbook.write(out); out.close(); if (file.exists()) { if (Desktop.isFileOpenSupported() && (excel.length() == 0)) { Desktop.open(file); } else { Runtime.getRuntime().exec(new String[] { excel, file.getAbsolutePath() }); } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {//from w ww . j ava 2 s .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; }