List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
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 *///from ww w . j a va 2 s. c o m 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 www . j a va2s . c om*/ 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 av a2 s . co m * @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 *//* ww w .j a va2 s . c o m*/ 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 . ja v a2s .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 va2 s. c o 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 *///from w w w .j av a2s .co m 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); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export inwSummaryreport to Excel * * This method completed modified as like RTGS Br.summary report by Eswaripriyak *//*from ww w .jav a 2 s . c o m*/ public void inwSummaryExportToExcel(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, "NEFT 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("NEFT 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; 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; 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("Transaction 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.toString()); cell = row.createCell((short) 7); 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) 6); cell.setCellValue("Total Inward Amount : "); cell = row.createCell((short) 7); cell.setCellValue(grandInwTotTxnAmt.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.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.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the outward returned report in to Excel *//* w w w . j ava2 s . co m*/ public void generateNEFTOwReturnedExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Outward Returned Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("NEFT Outward Returned Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 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("Batch Time"); } else if (j == 2) { cell.setCellValue("Value Date"); } else if (j == 3) { cell.setCellValue("Sender Address"); } else if (j == 4) { cell.setCellValue("Receiver Address"); } else if (j == 5) { cell.setCellValue("UTR No"); } else if (j == 6) { cell.setCellValue("Original UTR No"); } else if (j == 7) { cell.setCellValue("Info"); } else if (j == 8) { cell.setCellValue("Amount (Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String batchtime = null; if (((ReportDTO) exportXLS.get(roww - 1)).getBatchTime() != null) { batchtime = ((ReportDTO) exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batchtime); } else if (j == 2) { 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 == 3) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 4) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } 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 orgUtrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo() != null) { orgUtrNo = ((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo(); } cell.setCellValue(orgUtrNo); } else if (j == 7) { String a7495 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldA7495() != null) { a7495 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldA7495(); } cell.setCellValue(a7495); } else if (j == 8) { String txnAmount = "0.00"; if (((ReportDTO) exportXLS.get(roww - 1)).getAmt() != null) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } BigDecimal dec = new BigDecimal(txnAmount); dec.setScale(2); totTxnAmt = totTxnAmt.add(dec); cell.setCellValue(txnAmount); } } 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" + 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
public void inwTxnsDetailExportToExcel(ServletOutputStream out) throws Exception { try {/*from ww w. j ava2s .c o m*/ 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 = getInwardTxns().iterator(); i.hasNext();) { TransactionInfo info = (TransactionInfo) i.next(); exportXLS.add(info); } //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; //added newly on 29-Jan-2010 for printing whole in one page // HSSFPrintSetup ps = sheet.getPrintSetup(); // sheet.setAutobreaks(true); // ps.setFitHeight((short)1); // ps.setFitWidth((short)1); //Ends here book.setSheetName(0, "Inward Txns -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(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("NEFT Inward Txns Report"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("NEFT Inward Transactions - Detailed - from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue( "Status: " + reportDto.getStatusValue() + " Batch Time: " + reportDto.getBatchTime()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch: " + reportDto.getBranchCode()); 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("Batch Time"); } else if (j == 2) { cell.setCellValue("Transaction Ref.No"); } else if (j == 3) { cell.setCellValue("Amount(Rs) "); } else if (j == 4) { cell.setCellValue("Benificiary IFSC"); } else if (j == 5) { cell.setCellValue("Benificiary A/c Name"); } else if (j == 6) { cell.setCellValue("Benificiary A/c Type"); } else if (j == 7) { cell.setCellValue("Benificiary A/c No"); } else if (j == 8) { cell.setCellValue("Sender IFSC"); } 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 { // 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 batchTime = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime() != null) { batchTime = ((TransactionInfo) exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batchTime); } else if (j == 2) { String refNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo() != null) { refNo = ((TransactionInfo) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(refNo); } else if (j == 3) { BigDecimal amt = BigDecimal.ZERO; amt = ((TransactionInfo) exportXLS.get(roww - 1)).getAmount(); cell.setCellValue(amt.setScale(2).toString()); totAmt = totAmt.add(amt); } else if (j == 4) { String benIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc() != null) { benIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccIfsc(); } cell.setCellValue(benIfsc); } else if (j == 5) { String benAccName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccName() != null) { benAccName = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccName(); } cell.setCellValue(benAccName); } else if (j == 6) { String benAccType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccType() != null) { benAccType = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccType(); } cell.setCellValue(benAccType); } else if (j == 7) { String benAccNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo() != null) { benAccNo = ((TransactionInfo) exportXLS.get(roww - 1)).getBeneficiaryInfo() .getAccNo(); } cell.setCellValue(benAccNo); } else if (j == 8) { String senderIfsc = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc() != null) { senderIfsc = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccIfsc(); } cell.setCellValue(senderIfsc); } else if (j == 9) { String accName = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName() != null) { accName = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccName(); } cell.setCellValue(accName); } else if (j == 10) { String accType = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType() != null) { accType = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccType(); } cell.setCellValue(accType); } else if (j == 11) { String accNo = null; if (((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo() .getAccNo() != null) { accNo = ((TransactionInfo) exportXLS.get(roww - 1)).getSenderInfo().getAccNo(); } cell.setCellValue(accNo); } 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); } } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 2); cell.setCellValue("Total"); cell = row.createCell((short) 3); cell.setCellValue(totAmt.toString()); sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file for Inward txns" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file for Inward txns" + e); } }