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 RTGS Inward possible Return Payment Rejected by user Report * @author MohanaDevis//from w ww . java 2s .c o m */ public void returnPaymentRejectedReportExportToExcel(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, "RTGS Return Payment Rejected", 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("RTGS Inward Possible Return Payment Rejected By User Report between " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " and " + 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; 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("UTR No"); } else if (j == 2) { cell.setCellValue("Transaction Type"); } else if (j == 3) { cell.setCellValue("Rejected By"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } else if (j == 7) { cell.setCellValue("Business date"); } } 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 utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } 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 userId = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUserId() != null) { userId = ((ReportDTO) exportXLS.get(roww - 1)).getUserId(); } cell.setCellValue(userId); } 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 amount = null; if (((ReportDTO) exportXLS.get(roww - 1)).getAmt() != null) { amount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(amount).setScale(2).toString()); } else if (j == 7) { String date = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { date = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(date); } } 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 for RTGS inward Possible Return Payment rejected Report" + 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 Individual Txn Details report in to Excel *///ww w. ja va2 s .co m public void generateIndividualTXNDetailsExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double inwTotTxnAmt = 0; // double owTotTxnAmt = 0; BigDecimal inwTotTxnAmt = BigDecimal.ZERO; BigDecimal owTotTxnAmt = BigDecimal.ZERO; Set set = getReportMap().entrySet(); //Adding the items to a list for (Iterator i = set.iterator(); i.hasNext();) { Map.Entry entry = (Map.Entry) i.next(); List dtoList = (List) entry.getValue(); exportXLS.addAll(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, "Transaction Detailed 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("Individual Transaction Detailed Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + " with status " + getReportDto().getStatusValue()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 8); 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 < 11; 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("Host"); } else if (j == 3) { cell.setCellValue("Trans Type"); } else if (j == 4) { cell.setCellValue("Msg Type"); } else if (j == 5) { cell.setCellValue("UTR No"); } else if (j == 6) { cell.setCellValue("Sender Address"); } else if (j == 7) { cell.setCellValue("Receiver Address"); } else if (j == 8) { cell.setCellValue("Status"); } else if (j == 9) { cell.setCellValue("Inward Amount (Rs.)"); } else if (j == 10) { cell.setCellValue("Outward 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 host = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSource() != null) { host = ((ReportDTO) exportXLS.get(roww - 1)).getSource(); } cell.setCellValue(host); } 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) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 5) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 6) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 7) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 8) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } else if (j == 9) { // double inwTxnAmount = 0.00; String inwTxnAmount = "0.00"; // R43 is Debit Notification it should be in outward amount field - 20110713 if ((((ReportDTO) exportXLS.get(roww - 1)).getTranType().equalsIgnoreCase("inward")) && !(((ReportDTO) exportXLS.get(roww - 1)).getMsgType() .equalsIgnoreCase("R43"))) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt.add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } else if (j == 10) { // double owTxnAmount = 0.0; String owTxnAmount = "0.00"; // R43 is Debit Notification it should be in outward amount field - 20110713 if ((((ReportDTO) exportXLS.get(roww - 1)).getTranType() .equalsIgnoreCase("outward")) || (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() .equalsIgnoreCase("R43"))) { // if (((ReportDTO)exportXLS // .get(roww - 1)).getAmount() != 0.0) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { owTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } owTotTxnAmt = owTotTxnAmt.add(new BigDecimal(owTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(owTxnAmount).setScale(2).toString()); } } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 8); cell.setCellValue("Total : "); cell = row.createCell((short) 9); cell.setCellValue(inwTotTxnAmt.toString()); cell = row.createCell((short) 10); cell.setCellValue(owTotTxnAmt.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.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Future Dated Txns report in to Excel *//*from w w w. jav a 2s . co m*/ public void futureDatedTxnExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double grandInwTotTxnAmt = 0; // double grandOwTotTxnAmt = 0; BigDecimal grandInwTotTxnAmt = BigDecimal.ZERO; BigDecimal grandOwTotTxnAmt = 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, "Future Dated Txns 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); String statusName = ""; if (getReportDto().getStatus().equalsIgnoreCase("2000")) { statusName = "Active"; } else if (getReportDto().getStatus().equalsIgnoreCase("2050")) { statusName = "Cancelled"; } cell.setCellValue("RTGS Date wise Future Dated Txns Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + " with status " + statusName); 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; Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { String date = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Date : " + date); BigDecimal inwTotTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 11; 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("Msg Type"); } else if (j == 3) { cell.setCellValue("UTR Number"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { cell.setCellValue("Account No"); } else { cell.setCellValue("Info"); } } else if (j == 7) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { cell.setCellValue("Beneficiary Details"); } else { cell.setCellValue("Additional Info"); } } else if (j == 8) { cell.setCellValue("Entry By"); } else if (j == 9) { cell.setCellValue("Cancelled By"); } else if (j == 10) { cell.setCellValue("Amount"); } } 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)).getMsgSubType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType(); } 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) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { String a5561 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldA5561() != null) { a5561 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldA5561(); } cell.setCellValue(a5561); } else { 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) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { String n5561 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldN5561() != null) { n5561 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldN5561(); } cell.setCellValue(n5561); } else { 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) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getEntryBy() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getEntryBy(); } cell.setCellValue(status); } else if (j == 9) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getPassBy() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getPassBy(); } cell.setCellValue(status); } else if (j == 10) { String inwTxnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt.add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 9); cell.setCellValue("Total ( Date : " + date + " ) "); cell = row.createCell((short) 10); cell.setCellValue(inwTotTxnAmt.toString()); grandInwTotTxnAmt = grandInwTotTxnAmt.add(inwTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 9); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 10); cell.setCellValue(grandInwTotTxnAmt.toString()); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while exporting Future dtaed txns Report into Excel" + e.getMessage()); throw new Exception("Exception while exporting Future dtaed txns Report into Excel" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method to used to export the Exception Report */// w w w . j a v a 2 s . co m public void exceptionReportExportToExcel(ServletOutputStream out, String channel) throws Exception { try { int rowCount = 0; BigDecimal grandInwTotTxnAmt = 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, channel + " Exceptions 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); String statusName = ""; cell.setCellValue(channel + " Exceptions Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); 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, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { statusName = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Status : " + statusName); BigDecimal inwTotTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 11; 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("Msg Type"); } else if (j == 3) { cell.setCellValue("UTR Number"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Status"); } else if (j == 7) { cell.setCellValue("Amount"); } } 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)).getMsgSubType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType(); } 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 status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } else if (j == 7) { String inwTxnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt.add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total ( Status : " + statusName + " ) "); cell = row.createCell((short) 7); cell.setCellValue(inwTotTxnAmt.toString()); grandInwTotTxnAmt = grandInwTotTxnAmt.add(inwTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 7); cell.setCellValue(grandInwTotTxnAmt.toString()); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while exporting Exception Report into Excel" + e.getMessage()); throw new Exception("Exception while exporting Exception Report into Excel" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Branchwise Individual Details report in to Excel *//*from ww w . j a v a2s . co m*/ public void generateBrIndividualExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double grandInwTotTxnAmt = 0; // double grandOwTotTxnAmt = 0; BigDecimal grandInwTotTxnAmt = BigDecimal.ZERO; BigDecimal grandOwTotTxnAmt = 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, "Branchwise Individual 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 Detailed Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + "with status " + getReportDto().getStatusValue()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 8); 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, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { String branch = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch : " + branch); // double inwTotTxnAmt = 0; // double owTotTxnAmt = 0; BigDecimal inwTotTxnAmt = BigDecimal.ZERO; BigDecimal owTotTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 11; 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("Host"); } else if (j == 3) { cell.setCellValue("Msg Type"); } else if (j == 4) { cell.setCellValue("Tran Type"); } else if (j == 5) { cell.setCellValue("UTR No"); } else if (j == 6) { cell.setCellValue("Sender Address"); } else if (j == 7) { cell.setCellValue("Receiver Address"); } else if (j == 8) { cell.setCellValue("Status"); } else if (j == 9) { cell.setCellValue("Inward Amount (Rs.)"); } else if (j == 10) { cell.setCellValue("Outward 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 host = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSource() != null) { host = ((ReportDTO) exportXLS.get(roww - 1)).getSource(); } cell.setCellValue(host); } else if (j == 3) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 4) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 5) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 6) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 7) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 8) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } else if (j == 9) { // double inwTxnAmount = 0.00; String inwTxnAmount = "0.00"; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() .equalsIgnoreCase("inward")) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt .add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } else if (j == 10) { // double owTxnAmount = 0.0; String owTxnAmount = "0.00"; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() .equalsIgnoreCase("outward")) { // if (((ReportDTO)exportXLS // .get(roww - 1)).getAmount() != 0.0) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { owTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } owTotTxnAmt = owTotTxnAmt.add(new BigDecimal(owTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(owTxnAmount).setScale(2).toString()); } } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 8); cell.setCellValue("Total ( Branch : " + branch + " ) "); cell = row.createCell((short) 9); cell.setCellValue(inwTotTxnAmt.toString()); cell = row.createCell((short) 10); cell.setCellValue(owTotTxnAmt.toString()); grandInwTotTxnAmt = grandInwTotTxnAmt.add(inwTotTxnAmt); grandOwTotTxnAmt = grandOwTotTxnAmt.add(owTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 9); cell.setCellValue("Total Inward Amount : "); cell = row.createCell((short) 10); cell.setCellValue(grandInwTotTxnAmt.toString()); row = sheet.createRow(rowCount); rowCount += 1; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 9); cell.setCellValue("Total Outward Amount : "); cell = row.createCell((short) 10); cell.setCellValue(grandOwTotTxnAmt.toString()); 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 Branchwise Summary Details report in to Excel *///from w w w. j ava 2s. c om //Modified as like RTGS Br.summray Report public void generateBrSummaryExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double grandInwTotTxnAmt = 0; // double grandOwTotTxnAmt = 0; BigDecimal grandInwTotTxnAmt = BigDecimal.ZERO; BigDecimal grandOwTotTxnAmt = 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, "Branchwise Summary 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 Summary Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + "with status " + getReportDto().getStatusValue()); row = sheet.createRow(rowCount); rowCount += 1; if (new BigDecimal(getReportDto().getToAmount()).compareTo(BigDecimal.ZERO) > 0) { row = sheet.createRow(rowCount); cell = row.createCell((short) 0); cell.setCellValue("Amount Range from " + getReportDto().getFromAmount() + " to " + getReportDto().getToAmount()); rowCount += 1; } cell = row.createCell((short) 5); 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, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { String branch = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch : " + branch); // double inwTotTxnAmt = 0; // double owTotTxnAmt = 0; BigDecimal inwTotTxnAmt = BigDecimal.ZERO; BigDecimal owTotTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 8; 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("Host"); } else if (j == 3) { cell.setCellValue("Tran Type"); } else if (j == 4) { cell.setCellValue("Status"); } else if (j == 5) { cell.setCellValue("Count"); } else if (j == 6) { cell.setCellValue("Inward Amount (Rs.)"); } else if (j == 7) { cell.setCellValue("Outward 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 host = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSource() != null) { host = ((ReportDTO) exportXLS.get(roww - 1)).getSource(); } cell.setCellValue(host); } 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) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } else if (j == 5) { long count = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { count = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(count); } else if (j == 6) { // double inwTxnAmount = 0.00; String inwTxnAmount = "0.00"; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() .equalsIgnoreCase("inward")) { // if (((ReportDTO)exportXLS // .get(roww - 1)).getAmount() != 0.0) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt .add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } else if (j == 7) { // double owTxnAmount = 0.0; String owTxnAmount = "0.00"; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() .equalsIgnoreCase("outward")) { // if (((ReportDTO)exportXLS // .get(roww - 1)).getAmount() != 0.0) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { owTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } owTotTxnAmt = owTotTxnAmt.add(new BigDecimal(owTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(owTxnAmount).setScale(2).toString()); } } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("Total ( Branch : " + branch + " ) "); cell = row.createCell((short) 6); cell.setCellValue(inwTotTxnAmt.setScale(2).toString()); cell = row.createCell((short) 7); cell.setCellValue(owTotTxnAmt.setScale(2).toString()); grandInwTotTxnAmt = grandInwTotTxnAmt.add(inwTotTxnAmt); grandOwTotTxnAmt = grandOwTotTxnAmt.add(owTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Inward Amount : "); cell = row.createCell((short) 7); cell.setCellValue(grandInwTotTxnAmt.setScale(2).toString()); row = sheet.createRow(rowCount); rowCount += 1; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Outward Amount : "); cell = row.createCell((short) 7); cell.setCellValue(grandOwTotTxnAmt.setScale(2).toString()); 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 Bankwise report in to Excel *//*from w w w . ja v a 2s. c om*/ public void generateBankwiseExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double grandTotTxnAmt = 0; BigDecimal grandTotTxnAmt = 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; String title = ""; if (report != null && report.equalsIgnoreCase("inward")) { title = "Inward Bank Wise Report"; } else if (report != null && report.equalsIgnoreCase("outward")) { title = "Outward Bank Wise Report"; } book.setSheetName(0, title, 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 (report != null && report.equalsIgnoreCase("inward")) { cell.setCellValue("Branch Report - Inward - Bank Wise from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); } else if (report != null && report.equalsIgnoreCase("outward")) { cell.setCellValue("Branch Report - Outward - Bank Wise 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, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { String bank = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Bank : " + bank); // double totTxnAmt = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 7; 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("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("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) { // 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("Total ( Bank : " + bank + " ) "); 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 : "); cell = row.createCell((short) 6); cell.setCellValue(grandTotTxnAmt.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 Branch Inward Returned report in to Excel *///w ww .jav a2s .c om public void generateBrInwReturnedExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double grandTotTxnAmt = 0; BigDecimal grandTotTxnAmt = 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, "Branch Inward 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("Branch Inward Returned Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); 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, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { String date = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Date : " + date); // double totTxnAmt = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 8; 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("Msg Type"); } else if (j == 3) { cell.setCellValue("UTR Number"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Outward Utr No"); } else if (j == 7) { 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 owUtrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo() != null) { owUtrNo = ((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo(); } cell.setCellValue(owUtrNo); } else if (j == 7) { // double txnAmount = 0.0; String txnAmount = "0.00"; // if (((ReportDTO)exportXLS // .get(roww - 1)).getAmount() != 0.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).setScale(2)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total ( Date : " + date + " ) "); cell = row.createCell((short) 7); cell.setCellValue(totTxnAmt.toString()); grandTotTxnAmt = grandTotTxnAmt.add(totTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 7); cell.setCellValue(grandTotTxnAmt.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 unsuccessful payments report in to Excel *//*from w w w . jav a2 s. c om*/ public void unsuccessfulPaymentReportExportToExcel(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, "Unsuccessful Payments 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); String responeType = ""; if (getReportDto().getResponse().equalsIgnoreCase("R90")) { responeType = "PI"; } else { responeType = "SSN"; } cell.setCellValue("RTGS Unsuccessful Payments Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + " with Negative " + responeType + " Response"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); 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("Msg Type"); } else if (j == 3) { cell.setCellValue("UTR Number"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Response Type"); } else if (j == 7) { cell.setCellValue("Remarks"); } else if (j == 8) { cell.setCellValue("Amount)"); } } 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)).getMsgSubType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType(); } 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 resType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getResponseType() != null) { resType = ((ReportDTO) exportXLS.get(roww - 1)).getResponseType(); } cell.setCellValue(resType); } else if (j == 7) { String remarks = null; if (((ReportDTO) exportXLS.get(roww - 1)).getRemarks() != null) { remarks = ((ReportDTO) exportXLS.get(roww - 1)).getRemarks(); } cell.setCellValue(remarks); } else if (j == 8) { 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).setScale(2)); 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.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 for unsuccessful payment report" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file for unsuccessful payment report" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the branch subtype individual report in to Excel *///from w ww. j a va 2 s. c o m public void generateBrSubTypeIndividualExportToExcel(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. Subtype Individual 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); String tranType = ""; if (getReportDto().getTransactionType().equalsIgnoreCase("inward")) { tranType = "Inward"; } else { tranType = "Outward"; } if (getReportDto().getPaymentType().equalsIgnoreCase("R42")) { cell.setCellValue("Branch SubType Individual Report - " + tranType + " Interbank payment from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + "with status " + getReportDto().getStatusValue()); } else { cell.setCellValue("Branch SubType Individual Report - " + tranType + " Customer payment from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + "with status " + getReportDto().getStatusValue()); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 8); 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) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { cell.setCellValue("Account Number"); } else { cell.setCellValue("Info"); } } else if (j == 6) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { cell.setCellValue("Beneficiary Details"); } else { cell.setCellValue("Additional Info"); } } else if (j == 7) { cell.setCellValue("Amount (Rs.)"); } else if (j == 8) { cell.setCellValue("Status"); } } 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) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { String a5561 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldA5561() != null) { a5561 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldA5561(); } cell.setCellValue(a5561); } else { String i7495 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldI7495() != null) { i7495 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldI7495(); } cell.setCellValue(i7495); } } else if (j == 6) { if (getReportDto().getPaymentType().equalsIgnoreCase("R41")) { String n5561 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldN5561() != null) { n5561 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldN5561(); } cell.setCellValue(n5561); } else { String a7495 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldA7495() != null) { a7495 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldA7495(); } cell.setCellValue(a7495); } } else if (j == 7) { 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).setScale(2)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } else if (j == 8) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 7); cell.setCellValue(totTxnAmt.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); } }