List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet()
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Future Dated Txns report in to Excel *///w w w . j av a 2 s . c o m public void neftFutureDatedTxnExportToExcel(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 Future Dated Txns Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); String statusName = ""; if (getReportDto().getStatus().equalsIgnoreCase("2000")) { statusName = "Active"; } else if (getReportDto().getStatus().equalsIgnoreCase("2050")) { statusName = "Cancelled"; } cell.setCellValue("NEFT Date wise Future Dated Txns Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()) + "with status " + statusName); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, List<ReportDTO>> entry = (Map.Entry<String, List<ReportDTO>>) z.next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); if (exportXLS.size() > 0) { String date = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Date : " + date); BigDecimal inwTotTxnAmt = BigDecimal.ZERO; BigDecimal owTotTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 11; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("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("Account Number"); } else if (j == 7) { cell.setCellValue("Beneficiary Details"); } else if (j == 8) { cell.setCellValue("Entry By"); } else if (j == 9) { cell.setCellValue("Cancelled By"); } else if (j == 10) { cell.setCellValue("Amount"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType(); } cell.setCellValue(msgType); } else if (j == 3) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String accNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getAccNo() != null) { accNo = ((ReportDTO) exportXLS.get(roww - 1)).getAccNo(); } cell.setCellValue(accNo); } else if (j == 7) { String beneName = null; if (((ReportDTO) exportXLS.get(roww - 1)).getBeneficiaryName() != null) { beneName = ((ReportDTO) exportXLS.get(roww - 1)).getBeneficiaryName(); } cell.setCellValue(beneName); } else if (j == 8) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getEntryBy() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getEntryBy(); } cell.setCellValue(status); } else if (j == 9) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getPassBy() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getPassBy(); } cell.setCellValue(status); } else if (j == 10) { String inwTxnAmount = "0.00"; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() .equalsIgnoreCase("inward")) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt .add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 8); cell.setCellValue("Total ( Date : " + date + " ) "); cell = row.createCell((short) 9); cell.setCellValue(inwTotTxnAmt.toString()); grandInwTotTxnAmt = grandInwTotTxnAmt.add(inwTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 9); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 10); cell.setCellValue(grandInwTotTxnAmt.toString()); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while exporting NEFT Future dated txns Report into Excel" + e.getMessage()); throw new Exception("Exception while exporting NEFT Future dated txns Report into Excel" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export NEFT Inward possible Return Payment Rejected by user Report * @author MohanaDevis//from w w w.j av a 2s .co m */ public void neftReturnPaymentRejectedReportExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "NEFT Return Payment Rejected", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("NEFT Inward Possible Return Payment Rejected By User Report between " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " and " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 10; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("UTR No"); } else if (j == 2) { cell.setCellValue("Transaction Type"); } else if (j == 3) { cell.setCellValue("Rejected By"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } else if (j == 7) { cell.setCellValue("Business date"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String userId = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUserId() != null) { userId = ((ReportDTO) exportXLS.get(roww - 1)).getUserId(); } cell.setCellValue(userId); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String amount = null; if (((ReportDTO) exportXLS.get(roww - 1)).getAmt() != null) { amount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(amount).setScale(2).toString()); } else if (j == 7) { String date = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { date = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(date); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error( "Exception while creating Excel sheet file for NEFT inward Possible Return Payment rejected Report" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the list in to Excel *//*from w ww. j a va 2 s . c o m*/ public void reconcillationExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); List consolList = new ArrayList(1); int rowCount = 0; long sno = 0; //Adding the items to a list ReconcileReportDTO dto = (ReconcileReportDTO) getNewReportDto(); for (Iterator i = dto.getReconcileReportDTOs().iterator(); i.hasNext();) { ReconcileReportDTO reconcileList = (ReconcileReportDTO) i.next(); exportXLS.add(reconcileList); } ConsolidatedReconcileReportDTO consolDto = dto.getConsolidatedReportDTO(); for (Iterator itr = consolDto.getConsolidatedReportDTOs().iterator(); itr.hasNext();) { ConsolidatedReconcileReportDTO conDto = (ConsolidatedReconcileReportDTO) itr.next(); consolList.add(conDto); } if (exportXLS.size() != 0) { 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 Reconciliation Reports", 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 Reconciliation Report"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(" "); cell = row.createCell((short) 1); cell.setCellValue(" "); cell = row.createCell((short) 2); cell.setCellValue("Reconciliation Report on"); cell = row.createCell((short) 3); cell.setCellValue( InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 9; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Gross Outward N06,N07(a)"); } else if (j == 3) { cell.setCellValue("Add rescheduled from previous batch(b)"); } else if (j == 4) { cell.setCellValue("Less rescheduled to next batch (c)"); } else if (j == 5) { cell.setCellValue("Less Rejected N03, N09 (d)"); } else if (j == 6) { cell.setCellValue("Net outward (e)"); } else if (j == 7) { cell.setCellValue("Inward N02 (f)"); } else if (j == 8) { cell.setCellValue("Aggregate for the batch (g)"); } } 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 (((ReconcileReportDTO) exportXLS.get(roww - 1)).getBatchTime() != null) { batchTime = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getBatchTime(); } else { batchTime = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getHeading(); } cell.setCellValue(batchTime); } else if (j == 2) { // double outAmt = 0; BigDecimal outAmt = BigDecimal.ZERO; outAmt = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getGrossOutwardAmount() .setScale(2); cell.setCellValue(outAmt.toString()); } else if (j == 3) { // double reshPreAmt = 0; BigDecimal reshPreAmt = BigDecimal.ZERO; reshPreAmt = ((ReconcileReportDTO) exportXLS.get(roww - 1)) .getRescheduledPrevBatchAmt().setScale(2); cell.setCellValue(reshPreAmt.toString()); } else if (j == 4) { // double reshNextAmt = 0; BigDecimal reshNextAmt = BigDecimal.ZERO; reshNextAmt = ((ReconcileReportDTO) exportXLS.get(roww - 1)) .getRescheduledNextBatchAmt().setScale(2); cell.setCellValue(reshNextAmt.toString()); } else if (j == 5) { // double rejectAmt = 0; BigDecimal rejectAmt = BigDecimal.ZERO; rejectAmt = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getRejectedAmt() .setScale(2); cell.setCellValue(rejectAmt.toString()); } else if (j == 6) { // double netOutamt = 0; BigDecimal netOutamt = BigDecimal.ZERO; netOutamt = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getNetOutwardAmt() .setScale(2); cell.setCellValue(netOutamt.toString()); } else if (j == 7) { // double netInamt = 0; BigDecimal netInamt = BigDecimal.ZERO; netInamt = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getNetInwardAmt() .setScale(2); cell.setCellValue(netInamt.toString()); } else if (j == 8) { // double aggAmt = 0; BigDecimal aggAmt = BigDecimal.ZERO; aggAmt = ((ReconcileReportDTO) exportXLS.get(roww - 1)).getAggregateAmt() .setScale(2); cell.setCellValue(aggAmt.toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellValue("Consolidated Report for the Day"); if (consolList.size() > 0) { for (int size = consolList.size(), rows = 0; rows <= size; rows++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 3; j++) { cell = row.createCell(j); if (rows == 0) { if (j == 1) { cell.setCellValue("RBI Account"); } else if (j == 2) { cell.setCellValue("NEFT Account"); } } else { if (j == 0) { String heading = null; heading = ((ConsolidatedReconcileReportDTO) consolList.get(rows - 1)) .getHeading(); cell.setCellValue(heading); } else if (j == 1) { // double rbiAcc = 0; String rbiAcc = "0.00"; rbiAcc = ((ConsolidatedReconcileReportDTO) consolList.get(rows - 1)) .getRBIAccountAmt(); cell.setCellValue(rbiAcc); } else if (j == 2) { // double neftAcc = 0; String neftAcc = "0.00"; neftAcc = ((ConsolidatedReconcileReportDTO) consolList.get(rows - 1)) .getNEFTAccountAmt(); cell.setCellValue(neftAcc); } } } } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the r41 inward report in to Excel *///from w w w . ja v a 2 s .c om public void generateR41InwardExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; BigDecimal totAmt = 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, "R41(CPN) 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); cell.setCellValue("Customer Payments Received - Grouped By Sender Address on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 5; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Sender Address"); } else if (j == 2) { cell.setCellValue("Value Date"); } else if (j == 3) { cell.setCellValue("No. of Txns"); } else if (j == 4) { cell.setCellValue("Total Txn Amount(Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } 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) { long txnCount = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { txnCount = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(txnCount); } else if (j == 4) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); totAmt = totAmt.add(new BigDecimal(txnAmount).setScale(2)); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 3); cell.setCellValue("Total : "); cell = row.createCell((short) 4); 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" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the r41 outward report in to Excel *//*ww w.ja v a 2s.c o m*/ public void generateR41OutwardExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; BigDecimal totAmt = 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, "R41(CPR) Submitted", 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("Customer Payments Submitted - Grouped By Receiver Address on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 5; 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("Receiver Address"); } else if (j == 3) { cell.setCellValue("No. of Txns"); } else if (j == 4) { cell.setCellValue("Total Txn Amount(Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 3) { long txnCount = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { txnCount = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(txnCount); } else if (j == 4) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); totAmt = totAmt.add(new BigDecimal(txnAmount).setScale(2)); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 3); cell.setCellValue("Total : "); cell = row.createCell((short) 4); 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" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the r42 inward report in to Excel */// ww w .j a va2 s. c o m public void generateR42InwardExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; BigDecimal totAmt = 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, "R42(IPN) 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); cell.setCellValue("Interbank Payments Received - Grouped By Sender Address on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 5; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Sender Address"); } else if (j == 2) { cell.setCellValue("Value Date"); } else if (j == 3) { cell.setCellValue("No. of Txns"); } else if (j == 4) { cell.setCellValue("Total Txn Amount(Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } 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) { long txnCount = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { txnCount = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(txnCount); } else if (j == 4) { // double txnAmount = 0.0; String txnAmount = "0.00"; // if (((ReportDTO)exportXLS // .get(roww - 1)).getAmount() != 0.0) { if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); totAmt = totAmt.add(new BigDecimal(txnAmount).setScale(2)); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 3); cell.setCellValue("Total : "); cell = row.createCell((short) 4); 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" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the r42 outward report in to Excel *//*from w w w .java 2 s. c om*/ public void generateR42OutwardExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; BigDecimal totAmt = 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, "R42(IPR) Submitted", 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("Interbank Payments Submitted - Grouped By Receiver Address on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 5; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Receiver Address"); } else if (j == 2) { cell.setCellValue("VALUE DATE"); } else if (j == 3) { cell.setCellValue("No. of Txns"); } else if (j == 4) { cell.setCellValue("Total Txn Amount(Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } 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) { long txnCount = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { txnCount = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(txnCount); } else if (j == 4) { // double txnAmount = 0.0; String txnAmount = "0.00"; if ((new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO)) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); totAmt = totAmt.add(new BigDecimal(txnAmount).setScale(2)); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 3); cell.setCellValue("Total : "); cell = row.createCell((short) 4); 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" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Graduated Payment report in to Excel *//* w w w . j a v a2 s . co m*/ public void generateGraduatedPaymentExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Graduated Payment 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("Graduated Payment Report on "); //Heading modified cell = row.createCell((short) 1); cell.setCellValue( InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 7); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 10; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("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("Balance (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 utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String debitCredit = null; if (((ReportDTO) exportXLS.get(roww - 1)).getDebitCredit() != null) { debitCredit = ((ReportDTO) exportXLS.get(roww - 1)).getDebitCredit(); } String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { if (debitCredit != null && debitCredit.equalsIgnoreCase("credit")) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } else if (j == 7) { String debitCredit = null; if (((ReportDTO) exportXLS.get(roww - 1)).getDebitCredit() != null) { debitCredit = ((ReportDTO) exportXLS.get(roww - 1)).getDebitCredit(); } String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { if (debitCredit != null && debitCredit.equalsIgnoreCase("debit")) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } else if (j == 8) { String balance = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getBalance()) .compareTo(BigDecimal.ZERO) != 0.0) { balance = ((ReportDTO) exportXLS.get(roww - 1)).getBalance(); } cell.setCellValue(new BigDecimal(balance).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export RTGS Inward possible Return Report * @author Eswaripriyak/*from w w w.j a va2s. c o m*/ */ public void generateInwardPossibleReturnReportExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "RTGS Possible Return 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("RTGS Inward possible Return Report on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); //Heading modified row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 10; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("UTR No"); } else if (j == 2) { cell.setCellValue("Transaction Type"); } else if (j == 3) { cell.setCellValue("Sender Address"); } else if (j == 4) { cell.setCellValue("Receiver Address"); } else if (j == 5) { cell.setCellValue("Amount (Rs.)"); } else if (j == 6) { cell.setCellValue("Business date"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String 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 amount = null; if (((ReportDTO) exportXLS.get(roww - 1)).getAmt() != null) { amount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(amount).setScale(2).toString()); } else if (j == 6) { String date = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { date = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(date); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file for RTGS inward Possible Return Report" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export RTGS Inward possible Return Payment Rejected by user Report * @author MohanaDevis/*from www .j av a 2s .com*/ */ public void returnPaymentRejectedReportExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "RTGS Return Payment Rejected", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("RTGS Inward Possible Return Payment Rejected By User Report between " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " and " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 10; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("UTR No"); } else if (j == 2) { cell.setCellValue("Transaction Type"); } else if (j == 3) { cell.setCellValue("Rejected By"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } else if (j == 7) { cell.setCellValue("Business date"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String userId = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUserId() != null) { userId = ((ReportDTO) exportXLS.get(roww - 1)).getUserId(); } cell.setCellValue(userId); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String amount = null; if (((ReportDTO) exportXLS.get(roww - 1)).getAmt() != null) { amount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(amount).setScale(2).toString()); } else if (j == 7) { String date = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { date = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(date); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error( "Exception while creating Excel sheet file for RTGS inward Possible Return Payment rejected Report" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }