List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setFontHeightInPoints
public void setFontHeightInPoints(short height)
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the paymentreport in to Excel *///from ww w. ja v a2s . c o m public void paymentExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double totAmt = 0; BigDecimal totAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getDetailReportDTOs().iterator(); i.hasNext();) { NEFTDetailsReportDTO indentList = (NEFTDetailsReportDTO) i.next(); exportXLS.add(indentList); } //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; if (report.equalsIgnoreCase("submitted")) { book.setSheetName(0, "Payments Submitted", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); } else { book.setSheetName(0, "Payments Received", 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.equalsIgnoreCase("submitted")) { cell.setCellValue("Payment Submitted Report " + reportDto.getPaymentType() + " From " + reportDto.getValueDate() + " to " + reportDto.getToDate() + " with status " + reportDto.getStatusValue()); } else { cell.setCellValue("Payment Received Report " + reportDto.getPaymentType() + " From " + reportDto.getValueDate() + " to " + reportDto.getToDate() + " with status " + reportDto.getStatusValue()); } 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 NUMBER"); } else if (j == 5) { cell.setCellValue("ACCOUNT NUMBER"); } else if (j == 6) { cell.setCellValue("BENIFICIARY DETAILS"); } 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 (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(valueDate); } else if (j == 2) { String sendAdd = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 3) { String recAdd = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 4) { String utrNo = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 5) { String accNo = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6021() != null) { accNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6021(); } else if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6061() != null) { accNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6061(); } cell.setCellValue(accNo); } else if (j == 6) { String beniDetails = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField5565() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField5565(); if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6081() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6081() + "-" + beniDetails; } } else if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6091() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6091(); } cell.setCellValue(beniDetails); } else if (j == 7) { String amt = null; if (new BigDecimal(((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0) { // totAmt += ((NEFTDetailsReportDTO)exportXLS // .get(roww - 1)).getAmount(); totAmt = totAmt.add(new BigDecimal( ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()).setScale(2)); amt = String.valueOf(((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()); } cell.setCellValue(new BigDecimal(amt).setScale(2).toString()); } else if (j == 8) { //To add status column in the excel sheet. String status = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 6); cell.setCellValue("TOTAL AMOUNT"); cell.setCellStyle(caption_style); cell = row.createCell((short) 7); cell.setCellValue(String.valueOf(totAmt)); 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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the graduated paymentreport in to Excel *//*w w w .j a v a 2s. c o m*/ public void graduatedPaymentExportToExcel(ServletOutputStream out) throws Exception { try { List<ReportDTO> exportXLS = new ArrayList<ReportDTO>(1); long sno = 0; int rowCount = 0; //double totAmt = 0; //Adding the items to a list for (Iterator i = getGraduadtedPayments().iterator(); i.hasNext();) { ReportDTO dto = (ReportDTO) i.next(); exportXLS.add(dto); } //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, "Graduated Payment", 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(""); cell = row.createCell((short) 1); cell.setCellValue("Datewise Graduated Payment Report"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 10); 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 < 13; 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("Msg Type"); } else if (j == 3) { cell.setCellValue("Tran Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Credit Amount(Rs)"); } else if (j == 7) { cell.setCellValue("Debit Amount(Rs)"); } else if (j == 8) { cell.setCellValue("Batch Time"); } else if (j == 9) { cell.setCellValue("Rescheduled Date"); } else if (j == 10) { cell.setCellValue("Rescheduled Batch Time"); } else if (j == 11) { cell.setCellValue("Rejected Date"); } else if (j == 12) { cell.setCellValue("Rejected Batch Time"); } } 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 ((exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = (exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String msgType = null; if ((exportXLS.get(roww - 1)).getMsgType() != null) { msgType = (exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String tranType = null; if ((exportXLS.get(roww - 1)).getTranType() != null) { tranType = (exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 4) { String sendAdd = null; if ((exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = (exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if ((exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = (exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { //Modified by priyak to maintain uniformity String crdDeb = null; String amount = null; if ((exportXLS.get(roww - 1)).getDebitCredit() != null) { crdDeb = (exportXLS.get(roww - 1)).getDebitCredit(); if (crdDeb.equals("Credit")) { amount = (exportXLS.get(roww - 1)).getAmt(); } else { amount = "0.00"; } } cell.setCellValue(amount); } else if (j == 7) { String crdDeb = null; String amount = null; if ((exportXLS.get(roww - 1)).getDebitCredit() != null) { crdDeb = (exportXLS.get(roww - 1)).getDebitCredit(); if (crdDeb.equals("Debit")) { amount = (exportXLS.get(roww - 1)).getAmt(); } else { amount = "0.00"; } } cell.setCellValue(amount); } else if (j == 8) { String batchTime = null; if ((exportXLS.get(roww - 1)).getBatchTime() != null) { batchTime = (exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batchTime); } else if (j == 9) { String reshDate = null; if ((exportXLS.get(roww - 1)).getReshDate() != null) { reshDate = (exportXLS.get(roww - 1)).getReshDate(); reshDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, reshDate); } cell.setCellValue(reshDate); } else if (j == 10) { String reshBatchTime = null; if ((exportXLS.get(roww - 1)).getReshBatchTime() != null) { reshBatchTime = (exportXLS.get(roww - 1)).getReshBatchTime(); } cell.setCellValue(reshBatchTime); } else if (j == 11) { String rejDate = null; if ((exportXLS.get(roww - 1)).getRejDate() != null) { rejDate = (exportXLS.get(roww - 1)).getRejDate(); rejDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, rejDate); } cell.setCellValue(rejDate); } else if (j == 12) { String rejBatchTime = null; if ((exportXLS.get(roww - 1)).getRejBatchTime() != null) { rejBatchTime = (exportXLS.get(roww - 1)).getRejBatchTime(); } cell.setCellValue(rejBatchTime); } } 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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Br inward returned report in to Excel */// www .jav a 2 s . co m public void returnedInwardExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; // double totAmt = 0; BigDecimal totAmt = BigDecimal.ZERO; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getReportMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Inward Returned", 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(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Report - Inward Returned from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(roww); roww += 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); roww += 1; while (it.hasNext()) { List exportXLS = new ArrayList(1); // double subTotal = 0; BigDecimal subTotal = BigDecimal.ZERO; String date = (String) it.next(); List listRep = (List) reportMap.get(date); for (Iterator itr = listRep.iterator(); itr.hasNext();) { ReportDTO repDTO = (ReportDTO) itr.next(); exportXLS.add(repDTO); } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("DATE :" + date + " BATCH TIME :" + reportDto.getBatchTime()); //Only If the DTO is not empty if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 8; j++) { cell = row.createCell(j); // for header if (count == 0) { // for header 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 { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String valueDate = null; if (date != null) { valueDate = date; } valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(count - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(count - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String utrNo = null; if (((ReportDTO) exportXLS.get(count - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(count - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(count - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(count - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(count - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(count - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String outUtr = null; if (((ReportDTO) exportXLS.get(count - 1)).getOutUTRNo() != null) { outUtr = ((ReportDTO) exportXLS.get(count - 1)).getOutUTRNo(); } cell.setCellValue(outUtr); } else if (j == 7) { String amt = null; // if (((ReportDTO)exportXLS // .get(count-1)).getAmount() != 0) { if (new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0) { // totAmt += ((ReportDTO)exportXLS // .get(count-1)).getAmount(); // subTotal += ((ReportDTO)exportXLS // .get(count-1)).getAmount(); // amt = String.valueOf(((ReportDTO)exportXLS // .get(count-1)).getAmount()); totAmt = totAmt.add( new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt())); subTotal = subTotal.add( new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt())); amt = String.valueOf(((ReportDTO) exportXLS.get(count - 1)).getAmt()); } cell.setCellValue(new BigDecimal(amt).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 6); cell.setCellValue("Sub Total(Date :" + date + ")"); cell = row.createCell((short) 7); //cell.setCellValue(String.valueOf(subTotal)); cell.setCellValue(subTotal.setScale(2).toString()); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount"); cell = row.createCell((short) 7); // cell.setCellValue(String.valueOf(totAmt)); cell.setCellValue(totAmt.setScale(2).toString()); sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Batchwise Reconcilition report in to Excel *//*w w w .ja va2 s . c om*/ public void batchwiseReconcillationExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getReconcillationMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Batchwise Reconciliation", 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(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batchwise - Reconciliation Report "); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Date"); cell = row.createCell((short) 1); cell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); /* row = sheet.createRow(roww); //Commented by priyak roww += 1; cell = row.createCell((short)0); cell.setCellValue("Transaction Type"); cell = row.createCell((short)1); cell.setCellValue(getReportDto().getTransactionType());*/ row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 9); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); roww += 1; while (it.hasNext()) { String type = null; List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = reconcillationMap.get(key); if (listRep.size() > 0) { for (Iterator itr = listRep.iterator(); itr.hasNext();) { if (key.equals("N04")) { type = "As Per N04"; NEFTN04DetailsDTO n04DTO = (NEFTN04DetailsDTO) itr.next(); exportXLS.add(n04DTO); } else { type = "As Per LMS"; BatchwiseReconcillationDTO n04DTO = (BatchwiseReconcillationDTO) itr.next(); exportXLS.add(n04DTO); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue(type); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellValue("Outward Transactions"); cell = row.createCell((short) 3); cell.setCellValue(""); cell = row.createCell((short) 4); cell.setCellValue(""); cell = row.createCell((short) 5); cell.setCellValue(""); cell = row.createCell((short) 6); cell.setCellValue(""); cell = row.createCell((short) 7); cell.setCellValue(""); cell = row.createCell((short) 8); cell.setCellValue("Inward Transactions"); if (type.equalsIgnoreCase("As Per N04")) { if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); // for header if (count == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Total amount Sent"); } else if (j == 4) { cell.setCellValue("Total no.of txns Accepted"); } else if (j == 5) { cell.setCellValue("Total amount Accepted"); } else if (j == 6) { cell.setCellValue("Total no.of txns Rejected"); } else if (j == 7) { cell.setCellValue("Total amount Rejected"); } else if (j == 8) { cell.setCellValue("Total no.of txns Received"); } else if (j == 9) { cell.setCellValue("Total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns Returned"); } else if (j == 11) { cell.setCellValue("Total amount Returned"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String batchTime = null; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField3535() != null) { batchTime = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField3535(); } cell.setCellValue(batchTime); } else if (j == 2) { String noSent = null; noSent = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)).getField5175(); cell.setCellValue(noSent); } else if (j == 3) { String amtSent = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4105() != null) { amtSent = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4105(); } //cell.setCellValue(amtSent); cell.setCellValue(new BigDecimal(amtSent).setScale(2).toString()); } else if (j == 4) { String noAccept = null; noAccept = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5180(); cell.setCellValue(noAccept); } else if (j == 5) { String amtAccept = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4110() != null) { amtAccept = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4110(); if (amtAccept.indexOf(",") != -1) { amtAccept = amtAccept.replace(",", "."); } } //cell.setCellValue(amtAccept); cell.setCellValue(new BigDecimal(amtAccept).setScale(2).toString()); } else if (j == 6) { String noReject = null; noReject = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5185(); cell.setCellValue(noReject); } else if (j == 7) { String amtReject = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4115() != null) { amtReject = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4115(); } //cell.setCellValue(amtReject); cell.setCellValue(new BigDecimal(amtReject).setScale(2).toString()); } else if (j == 8) { String noReceive = null; noReceive = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5267(); cell.setCellValue(noReceive); } else if (j == 9) { String amtReceive = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4410() != null) { amtReceive = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4410(); } //cell.setCellValue(amtReceive); cell.setCellValue(new BigDecimal(amtReceive).setScale(2).toString()); } else if (j == 10) { String noReturn = null; noReturn = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5047(); cell.setCellValue(noReturn); } else if (j == 11) { String amtReturn = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4460() != null) { amtReturn = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4460(); } //cell.setCellValue(amtReturn); cell.setCellValue(new BigDecimal(amtReturn).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } } } else { if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); // for header if (count == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Outward total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Outward Total Amount"); } else if (j == 4) { cell.setCellValue("Total no.of txns Settled,Rescheduled"); } else if (j == 5) { cell.setCellValue("Total amount Settled,Rescheduled"); } else if (j == 6) { cell.setCellValue("Total no.of txns Unsuccessful"); } else if (j == 7) { cell.setCellValue("Total Amount Unsuccessful"); } else if (j == 8) { cell.setCellValue("Inward total no.of Txns Received"); } else if (j == 9) { cell.setCellValue("Inward total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns returned"); } else if (j == 11) { cell.setCellValue("Total amount returned"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String batchTime = null; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getBatchTime() != null) { batchTime = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getBatchTime(); } cell.setCellValue(batchTime); } else if (j == 2) { long noSent = 0; noSent = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAccepted() + ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnRejected(); cell.setCellValue(noSent); } else if (j == 3) { // double owTxnSentAmt = 0; BigDecimal owTxnSentAmt = BigDecimal.ZERO; BigDecimal owTxnSentAcceptedAmt = new BigDecimal( (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtAccepted())); BigDecimal owTxnSentRejctedAmt = new BigDecimal( (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtRejected())); owTxnSentAmt = owTxnSentAcceptedAmt.add(owTxnSentRejctedAmt); //cell.setCellValue(String.valueOf(owTxnSentAmt)); cell.setCellValue(owTxnSentAmt.setScale(2).toString()); } else if (j == 4) { long noAccept = 0; noAccept = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAccepted(); //cell.setCellValue(noAccept); cell.setCellValue(new BigDecimal(noAccept).setScale(2).toString()); } else if (j == 5) { String amtAccept = "0.00"; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtAccepted() != null) { amtAccept = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtAccepted(); } //cell.setCellValue(amtAccept); cell.setCellValue(new BigDecimal(amtAccept).setScale(2).toString()); } else if (j == 6) { long noReject = 0; noReject = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnRejected(); cell.setCellValue(noReject); } else if (j == 7) { String amtReject = null; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtRejected() != null) { amtReject = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtRejected(); } //cell.setCellValue(amtReject); cell.setCellValue(new BigDecimal(amtReject).setScale(2).toString()); } else if (j == 8) { long noReceive = 0; noReceive = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnReceived() + ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnReturned(); cell.setCellValue(noReceive); } else if (j == 9) { String amtReceive = "0.00"; String amtReturn = "0.00"; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReceived() != null) { amtReceive = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReceived(); } if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned() != null) { amtReturn = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned(); } BigDecimal totInw = new BigDecimal(amtReceive) .add(new BigDecimal(amtReturn)); //cell.setCellValue(amtReceive); cell.setCellValue(totInw.setScale(2).toString()); } else if (j == 10) { long noReturn = 0; noReturn = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnReturned(); cell.setCellValue(noReturn); } else if (j == 11) { String amtReturn = "0.00"; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned() != null) { amtReturn = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned(); } //cell.setCellValue(amtReturn); cell.setCellValue(new BigDecimal(amtReturn).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } } } } else { if (key.equals("N04")) { row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("As Per N04"); row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Total amount Sent"); } else if (j == 4) { cell.setCellValue("Total no.of txns Accepted"); } else if (j == 5) { cell.setCellValue("Total amount Accepted"); } else if (j == 6) { cell.setCellValue("Total no.of txns Rejected"); } else if (j == 7) { cell.setCellValue("Total amount Rejected"); } else if (j == 8) { cell.setCellValue("Total no.of txns Received"); } else if (j == 9) { cell.setCellValue("Total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns Returned"); } else if (j == 11) { cell.setCellValue("Total amount Returned"); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("No Records Found"); } else { row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("As Per LMS"); row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Outward total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Outward Total Amount"); } else if (j == 4) { cell.setCellValue("Total no.of txns Settled,Rescheduled"); } else if (j == 5) { cell.setCellValue("Total amount Settled,Rescheduled"); } else if (j == 6) { cell.setCellValue("Total no.of txns Unsuccessful"); } else if (j == 7) { cell.setCellValue("Total Amount Unsuccessful"); } else if (j == 8) { cell.setCellValue("Inward total no.of Txns Received"); } else if (j == 9) { cell.setCellValue("Inward total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns returned"); } else if (j == 11) { cell.setCellValue("Total amount returned"); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("No Records Found"); } } } 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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Batchwise aggregate report in to Excel *//*from ww w. j a v a 2 s .c o m*/ public void batchwiseAggregateExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; BigDecimal grandCredTotAmt = BigDecimal.ZERO; BigDecimal grandDebTotAmt = BigDecimal.ZERO; BigDecimal grandAggTotAmt = BigDecimal.ZERO; // double grandCredTotAmt = 0; // double grandDebTotAmt = 0; // double grandAggTotAmt = 0; long grandTotCredit = 0; long grandTotDebit = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getAggregateMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Batchwise Aggregate Detailed", 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(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batchwise Aggregate Detailed Report for " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch"); cell = row.createCell((short) 1); String brName = getBranchName(String.valueOf(getReportDto().getIfscId())); cell.setCellValue(brName); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(roww); roww += 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); roww += 1; while (it.hasNext()) { List exportXLS = new ArrayList(1); // double credTotAmt = 0; // double debTotAmt = 0; // double aggTotAmt = 0; BigDecimal credTotAmt = BigDecimal.ZERO; BigDecimal debTotAmt = BigDecimal.ZERO; BigDecimal aggTotAmt = BigDecimal.ZERO; long totCredit = 0; long totDebit = 0; String batchTime = (String) it.next(); List listRep = (List) aggregateMap.get(batchTime); for (Iterator itr = listRep.iterator(); itr.hasNext();) { BatchwiseAggregateDTO aggDTO = (BatchwiseAggregateDTO) itr.next(); exportXLS.add(aggDTO); } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch :"); cell = row.createCell((short) 1); cell.setCellValue(batchTime); //Only If the DTO is not empty if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 7; j++) { cell = row.createCell(j); // for header if (count == 0) { // for header if (j == 0) { cell.setCellValue("S.NO"); } else if (j == 1) { cell.setCellValue("BRANCH IFSC CODE"); } else if (j == 2) { cell.setCellValue("NO OF CREDITS"); } else if (j == 3) { cell.setCellValue("CREDIT AMOUNT(Rs)"); } else if (j == 4) { cell.setCellValue("NO OF DEBITS"); } else if (j == 5) { cell.setCellValue("DEBIT AMOUNT(Rs)"); } else if (j == 6) { cell.setCellValue("AGGREGATE AMOUNT (CREDIT-DEBIT)(Rs)"); } } else { 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(count); } else if (j == 1) { String ifsc = null; if (((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getIfsc() != null) { ifsc = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getIfsc(); } cell.setCellValue(ifsc); } else if (j == 2) { long noCredit = 0; noCredit = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getNoOfCredits(); totCredit += noCredit; grandTotCredit += noCredit; cell.setCellValue(String.valueOf(noCredit)); } else if (j == 3) { String credAmt = null; if (((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount() != null) { credTotAmt = credTotAmt.add( new BigDecimal(((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount()).setScale(2)); credAmt = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount(); grandCredTotAmt = grandCredTotAmt.add( new BigDecimal(((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount()).setScale(2)); } cell.setCellValue(new BigDecimal(credAmt).setScale(2).toString()); } else if (j == 4) { long noDebit = 0; noDebit = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getNoOfDebits(); totDebit += noDebit; grandTotDebit += noDebit; cell.setCellValue(noDebit); } else if (j == 5) { String debitAmt = null; if (((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getDebitAmount() != null) { debTotAmt = debTotAmt.add(new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getDebitAmount()) .setScale(2)); // grandDebTotAmt += Double.valueOf(((BatchwiseAggregateDTO)exportXLS // .get(count-1)).getDebitAmount()); grandDebTotAmt = grandDebTotAmt.add(new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getDebitAmount()) .setScale(2)); debitAmt = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getDebitAmount(); } cell.setCellValue(new BigDecimal(debitAmt).setScale(2).toString()); } else if (j == 6) { // double aggAmt = 0; // // double credit = Double.valueOf(((BatchwiseAggregateDTO)exportXLS.get(count-1)).getCreditAmount()); // double debit = Double.valueOf(((BatchwiseAggregateDTO)exportXLS.get(count-1)).getDebitAmount()); BigDecimal aggAmt = BigDecimal.ZERO; BigDecimal credit = new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getCreditAmount()); BigDecimal debit = new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getDebitAmount()); aggAmt = credit.subtract(debit).setScale(2); aggTotAmt = aggTotAmt.add(aggAmt).setScale(2); grandAggTotAmt = grandAggTotAmt.add(aggAmt).setScale(2); cell.setCellValue(String.valueOf(aggAmt)); } cell.setCellStyle(caption_style); } } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("TOTAL"); cell = row.createCell((short) 2); cell.setCellValue(totCredit); cell = row.createCell((short) 3); cell.setCellValue(String.valueOf(credTotAmt)); cell = row.createCell((short) 4); cell.setCellValue(totDebit); cell = row.createCell((short) 5); cell.setCellValue(String.valueOf(debTotAmt)); cell = row.createCell((short) 6); cell.setCellValue(String.valueOf(aggTotAmt)); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue(""); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("GRAND TOTAL"); cell = row.createCell((short) 2); cell.setCellValue(grandTotCredit); cell = row.createCell((short) 3); cell.setCellValue(String.valueOf(grandCredTotAmt)); cell = row.createCell((short) 4); cell.setCellValue(grandTotDebit); cell = row.createCell((short) 5); cell.setCellValue(String.valueOf(grandDebTotAmt)); cell = row.createCell((short) 6); cell.setCellValue(String.valueOf(grandAggTotAmt)); 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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method for Exporting the NEFT branchwise aggregate Report * @parameter ServletOutputStream//from w w w. j a v a2s. com * @return void */ public void batchwiseAggregateSummaryExportToExcel(ServletOutputStream out) throws Exception { int roww = 0; BigDecimal grandAggTotAmt = BigDecimal.ZERO; String batchTime = ""; BatchwiseAggregateDTO aggDTO = null; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getAggregateMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Batchwise Aggregate Summary", 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(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batchwise Aggregate Summary Report for " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch"); cell = row.createCell((short) 1); String brName = getBranchName(String.valueOf(getReportDto().getIfscId())); cell.setCellValue(brName); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(roww); roww += 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); roww += 1; for (short j = 0; j < 6; j++) { cell = row.createCell(j); // for header if (j == 0) { cell.setCellValue("Batch Time"); } else if (j == 1) { cell.setCellValue("No. of credits"); } else if (j == 2) { cell.setCellValue("Credit Amount (Rs.)"); } else if (j == 3) { cell.setCellValue("No. of debits"); } else if (j == 4) { cell.setCellValue("debit Amount (Rs.)"); } else if (j == 5) { cell.setCellValue("Aggregate Amount (Credit-Debit)(Rs.)"); } } while (it.hasNext()) { batchTime = (String) it.next(); aggDTO = (BatchwiseAggregateDTO) aggregateMap.get(batchTime); row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 6; j++) { cell = row.createCell(j); // for header if (j == 0) { cell.setCellValue(aggDTO.getBatchTime()); } else if (j == 1) { cell.setCellValue(aggDTO.getNoOfCredits()); } else if (j == 2) { cell.setCellValue(new BigDecimal(aggDTO.getCreditAmount()).setScale(2).toString()); } else if (j == 3) { cell.setCellValue(aggDTO.getNoOfDebits()); } else if (j == 4) { cell.setCellValue(new BigDecimal(aggDTO.getDebitAmount()).setScale(2).toString()); } else if (j == 5) { grandAggTotAmt = new BigDecimal(aggDTO.getCreditAmount()) .subtract(new BigDecimal(aggDTO.getDebitAmount())); cell.setCellValue(grandAggTotAmt.setScale(2).toString()); } } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export inwDetailreport to Excel *//*from w w w. j av a 2s. com*/ public void inwDetailExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); book.setSheetName(0, "Inward Detail Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("NEFT Inward Summary Report"); //Adding the items to a list ITDetailReportDTO inwDto = (ITDetailReportDTO) getNeftRepDTO(); Set keySet = inwDto.getReceivedTransactionInfo().keySet(); Iterator it = keySet.iterator(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Code :"); cell = row.createCell((short) 1); cell.setCellValue(getBranchIFSCCode(String.valueOf(getReportDto().getIfscId()))); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Date :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getValueDate()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Type :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getReportType()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Inward Type :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getInwardType()); while (it.hasNext()) { List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = (List) inwDto.getReceivedTransactionInfo().get(key); for (Iterator itr = listRep.iterator(); itr.hasNext();) { TransactionInfo info = (TransactionInfo) itr.next(); exportXLS.add(info); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(key); if (exportXLS.size() != 0) { for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 13; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Benificiary IFSC"); } else if (j == 3) { cell.setCellValue("Sender IFSC"); } else if (j == 4) { cell.setCellValue("Transaction Ref.No"); } else if (j == 5) { cell.setCellValue("Amount(Rs)"); } else if (j == 6) { cell.setCellValue("Benificiary A/c Name"); } else if (j == 7) { cell.setCellValue("Benificiary A/c Type"); } else if (j == 8) { cell.setCellValue("Benificiary A/c No"); } else if (j == 9) { cell.setCellValue("Sender A/c Name"); } else if (j == 10) { cell.setCellValue("Sender A/c Type"); } else if (j == 11) { cell.setCellValue("Sender A/c No"); } else if (j == 12) { cell.setCellValue("Transaction Status"); } } else { if (j == 0) { cell.setCellValue(roww); } else if (j == 1) { String batch = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime() != null) { batch = ((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batch); } else if (j == 2) { String beneIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc() != null) { beneIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc(); } cell.setCellValue(beneIfsc); } else if (j == 3) { String sendIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc() != null) { sendIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc(); } cell.setCellValue(sendIfsc); } else if (j == 4) { String transRef = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo() != null) { transRef = ((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(transRef); } else if (j == 5) { // double amt = 0; // amt = ((TransactionInfo)exportXLS.get(roww-1)).getAmount(); BigDecimal amt = BigDecimal.ZERO; amt = ((TransactionInfo) exportXLS.get(roww - 1)).getAmount(); //cell.setCellValue(FormatAmount.formatINRAmount(amt)); cell.setCellValue(amt.setScale(2).toString()); } else if (j == 6) { String bencAcName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccName() != null) { bencAcName = ((TransactionInfo) exportXLS.get(roww - 1)) .getBeneficiaryInfo().getAccName(); } cell.setCellValue(bencAcName); } else if (j == 7) { String bencAcType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccType() != null) { bencAcType = ((TransactionInfo) exportXLS.get(roww - 1)) .getBeneficiaryInfo().getAccType(); } cell.setCellValue(bencAcType); } else if (j == 8) { String bencAcNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo() != null) { bencAcNo = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo(); } cell.setCellValue(bencAcNo); } else if (j == 9) { String sendAcName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName() != null) { sendAcName = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName(); } cell.setCellValue(sendAcName); } else if (j == 10) { String sendAcType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType() != null) { sendAcType = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType(); } cell.setCellValue(sendAcType); } else if (j == 11) { String sendAcNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo() != null) { sendAcNo = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo(); } cell.setCellValue(sendAcNo); } else if (j == 12) { String status = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getStatusShortDesc() != null) { status = ((TransactionInfo) exportXLS.get(roww - 1)).getStatusShortDesc(); } cell.setCellValue(status); } cell.setCellStyle(caption_style); } } } } else { row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("No records found"); } } 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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export outTxnDetailreport to Excel *//* ww w . j a v a2 s. c o m*/ public void outTxnDetailExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; BigDecimal totAmt = BigDecimal.ZERO; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); book.setSheetName(0, "Outward Txn Detail Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); //Adding the items to a list OTDetailReportDTO outDto = (OTDetailReportDTO) getNeftRepDTO(); Set keySet = outDto.getOutwardMap().keySet(); Iterator it = keySet.iterator(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Code :"); cell = row.createCell((short) 1); cell.setCellValue(getBranchIFSCCode(String.valueOf(getReportDto().getIfscId()))); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Date :"); cell = row.createCell((short) 1); cell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Type :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getReportType()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Generated by :"); cell = row.createCell((short) 1); cell.setCellValue(neftRepDTO.reportRunBy); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Outward Txn Detailed Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 13); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; while (it.hasNext()) { List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = (List) outDto.getOutwardMap().get(key); for (Iterator itr = listRep.iterator(); itr.hasNext();) { TransactionInfo info = (TransactionInfo) itr.next(); exportXLS.add(info); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(key); if (exportXLS.size() != 0) { for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 16; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Value Date"); } else if (j == 3) { cell.setCellValue("Transaction Ref.No"); } else if (j == 4) { cell.setCellValue("Amount(Rs)"); } else if (j == 5) { cell.setCellValue("Sender IFSC"); } else if (j == 6) { cell.setCellValue("Sender A/c Type"); } else if (j == 7) { cell.setCellValue("Sender A/c No"); } else if (j == 8) { cell.setCellValue("Sender A/c Name"); } else if (j == 9) { cell.setCellValue("Benificiary IFSC"); } else if (j == 10) { cell.setCellValue("Benificiary A/c Type"); } else if (j == 11) { cell.setCellValue("Benificiary A/c No"); } else if (j == 12) { cell.setCellValue("Benificiary A/c Name"); } else if (j == 13) { cell.setCellValue("Rescheduled Date"); } else if (j == 14) { cell.setCellValue("Rescheduled Time"); } else if (j == 15) { cell.setCellValue("Message Status"); } } else { if (j == 0) { cell.setCellValue(roww); } else if (j == 1) { String batch = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime() != null) { batch = ((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batch); } else if (j == 2) { String valueDate = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getValueDate() != null) { Date date = (Date) ((TransactionInfo) exportXLS.get(roww - 1)) .getValueDate(); valueDate = InstaReportUtil.formatDate(date); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, InstaReportUtil.formatDateString(valueDate)); } cell.setCellValue(valueDate); } else if (j == 3) { String transRef = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo() != null) { transRef = ((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(transRef); } else if (j == 4) { // double amt = 0; BigDecimal amt = BigDecimal.ZERO; amt = ((TransactionInfo) exportXLS.get(roww - 1)).getAmount(); cell.setCellValue(FormatAmount.formatINRAmount(amt)); totAmt = totAmt.add(amt); } else if (j == 5) { String sendIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc() != null) { sendIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc(); } cell.setCellValue(sendIfsc); } else if (j == 6) { String sendAcType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType() != null) { sendAcType = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType(); } cell.setCellValue(sendAcType); } else if (j == 7) { String sendAcNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo() != null) { sendAcNo = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo(); } cell.setCellValue(sendAcNo); } else if (j == 8) { String sendAcName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName() != null) { sendAcName = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName(); } cell.setCellValue(sendAcName); } else if (j == 9) { String beneIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc() != null) { beneIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc(); } cell.setCellValue(beneIfsc); } else if (j == 10) { String bencAcType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccType() != null) { bencAcType = ((TransactionInfo) exportXLS.get(roww - 1)) .getBeneficiaryInfo().getAccType(); } cell.setCellValue(bencAcType); } else if (j == 11) { String bencAcNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo() != null) { bencAcNo = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo(); } cell.setCellValue(bencAcNo); } else if (j == 12) { String bencAcName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccName() != null) { bencAcName = ((TransactionInfo) exportXLS.get(roww - 1)) .getBeneficiaryInfo().getAccName(); } cell.setCellValue(bencAcName); } else if (j == 13) { String reschDate = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getRescheduleDate() != null) { Date date = (Date) ((TransactionInfo) exportXLS.get(roww - 1)) .getRescheduleDate(); reschDate = InstaReportUtil.formatDate(date); reschDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, InstaReportUtil.formatDateString(reschDate)); } cell.setCellValue(reschDate); } else if (j == 14) { String reschBatch = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getRescheduleBatch() != null) { reschBatch = ((TransactionInfo) exportXLS.get(roww - 1)) .getRescheduleBatch(); } cell.setCellValue(reschBatch); } else if (j == 15) { String status = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getCurrentStatus() != null) { status = ((TransactionInfo) exportXLS.get(roww - 1)).getCurrentStatus(); } cell.setCellValue(status); } cell.setCellStyle(caption_style); } } } } else { row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("No records found"); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); cell.setCellValue("Total"); cell = row.createCell((short) 4); cell.setCellValue(totAmt.setScale(2).toString()); sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export outDetailreport to Excel *//*from w w w.j a v a 2s .co m*/ public void outDetailExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); book.setSheetName(0, "Outward Detail Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); //Adding the items to a list OTDetailReportDTO outDto = (OTDetailReportDTO) getNeftRepDTO(); Set keySet = outDto.getOutwardMap().keySet(); Iterator it = keySet.iterator(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Code :"); cell = row.createCell((short) 1); cell.setCellValue(getBranchIFSCCode(String.valueOf(getReportDto().getIfscId()))); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Date :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getValueDate()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Type :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getReportType()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Generated by :"); cell = row.createCell((short) 1); cell.setCellValue(neftRepDTO.reportRunBy); while (it.hasNext()) { List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = (List) outDto.getOutwardMap().get(key); for (Iterator itr = listRep.iterator(); itr.hasNext();) { TransactionInfo info = (TransactionInfo) itr.next(); exportXLS.add(info); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(key); if (exportXLS.size() != 0) { for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 14; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Value Date"); } else if (j == 3) { cell.setCellValue("Sender IFSC"); } else if (j == 4) { cell.setCellValue("Benificiary IFSC"); } else if (j == 5) { cell.setCellValue("Transaction Ref.No"); } else if (j == 6) { cell.setCellValue("Amount(Rs)"); } else if (j == 7) { cell.setCellValue("Sender A/c Type"); } else if (j == 8) { cell.setCellValue("Sender A/c No"); } else if (j == 9) { cell.setCellValue("Sender A/c Name"); } else if (j == 10) { cell.setCellValue("Benificiary A/c Type"); } else if (j == 11) { cell.setCellValue("Benificiary A/c No"); } else if (j == 12) { cell.setCellValue("Benificiary A/c Name"); } else if (j == 13) { cell.setCellValue("Transaction Status"); } } else { if (j == 0) { cell.setCellValue(roww); } else if (j == 1) { String batch = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime() != null) { batch = ((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batch); } else if (j == 2) { String valueDate = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getValueDate() != null) { Date date = (Date) ((TransactionInfo) exportXLS.get(roww - 1)) .getValueDate(); valueDate = InstaReportUtil.formatDate(date); } cell.setCellValue(valueDate); } else if (j == 3) { String sendIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc() != null) { sendIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc(); } cell.setCellValue(sendIfsc); } else if (j == 4) { String beneIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc() != null) { beneIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc(); } cell.setCellValue(beneIfsc); } else if (j == 5) { String transRef = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo() != null) { transRef = ((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(transRef); } else if (j == 6) { // double amt = 0; BigDecimal amt = BigDecimal.ZERO; amt = ((TransactionInfo) exportXLS.get(roww - 1)).getAmount(); //cell.setCellValue(FormatAmount.formatINRAmount(amt)); cell.setCellValue(amt.setScale(2).toString()); } else if (j == 7) { String sendAcType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType() != null) { sendAcType = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType(); } cell.setCellValue(sendAcType); } else if (j == 8) { String sendAcNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo() != null) { sendAcNo = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo(); } cell.setCellValue(sendAcNo); } else if (j == 9) { String sendAcName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName() != null) { sendAcName = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName(); } cell.setCellValue(sendAcName); } else if (j == 10) { String bencAcType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccType() != null) { bencAcType = ((TransactionInfo) exportXLS.get(roww - 1)) .getBeneficiaryInfo().getAccType(); } cell.setCellValue(bencAcType); } else if (j == 11) { String bencAcNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo() != null) { bencAcNo = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo(); } cell.setCellValue(bencAcNo); } else if (j == 12) { String bencAcName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccName() != null) { bencAcName = ((TransactionInfo) exportXLS.get(roww - 1)) .getBeneficiaryInfo().getAccName(); } cell.setCellValue(bencAcName); } else if (j == 13) { String status = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getCurrentStatus() != null) { status = ((TransactionInfo) exportXLS.get(roww - 1)).getCurrentStatus(); } cell.setCellValue(status); } cell.setCellStyle(caption_style); } } } } else { row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("No records found"); } } 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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export outSummaryreport to Excel *//*ww w .j av a2 s . c om*/ public void outSummaryExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; int display = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); book.setSheetName(0, "Outward Summary Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); //Adding the items to a list OTDetailReportDTO outDto = (OTDetailReportDTO) getNeftRepDTO(); Set keySet = outDto.getOutwardMap().keySet(); row = sheet.createRow(rowCount); //Have done with Heading rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue("NEFT Outward Summary Report"); Iterator it = keySet.iterator(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Code :"); cell = row.createCell((short) 1); cell.setCellValue(getBranchIFSCCode(String.valueOf(getReportDto().getIfscId()))); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Date :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getValueDate()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Type :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getReportType()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Generated By:"); cell = row.createCell((short) 1); cell.setCellValue(neftRepDTO.reportRunBy); /*row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short)0); cell.setCellValue("Status:"); cell = row.createCell((short)1); cell.setCellValue(getReportDto().getStatusValue());*/ row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 1); cell.setCellValue("Summary Of the Transactions"); while (it.hasNext()) { List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = (List) outDto.getOutwardMap().get(key); for (Iterator itr = listRep.iterator(); itr.hasNext();) { SummaryInfoElement info = (SummaryInfoElement) itr.next(); exportXLS.add(info); } if (exportXLS.size() != 0) { for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 3; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 1 && display == 0) { cell.setCellValue("No of Transaction"); } else if (j == 2 && display == 0) { cell.setCellValue("Amount (Rs.)"); } } else { SummaryInfoElement summary = ((SummaryInfoElement) exportXLS.get(roww - 1)); if (j == 0) { String heading = null; if (summary != null) { heading = summary.getHeading(); } cell.setCellValue(heading); } else if (j == 1) { int count = 0; if (summary != null) { count = summary.getCount(); } cell.setCellValue(String.valueOf(count)); } else if (j == 2) { // double amount = 0; BigDecimal amount = BigDecimal.ZERO; if (summary != null) { amount = new BigDecimal(summary.getAmount()); } //cell.setCellValue(amount.toString()); //Have done amount format. cell.setCellValue(FormatAmount.formatINRAmount(amount.toString())); } } cell.setCellStyle(caption_style); } } display = 1; } } 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); } }