List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setFontHeightInPoints
public void setFontHeightInPoints(short height)
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 w w w. j a v a 2 s. c o 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 .ja v a 2 s .c o m //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.j av a2 s .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.ja v a 2s.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 a2s . c o m*/ 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 ww w . ja v a 2s .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); } }
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 ww w . j av a2 s .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 w w w. j a v a2 s. 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 ww .j a v a2 s. co m*/ 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 */// www .ja v a2 s. c om 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); } }