List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write
private void write(POIFSFileSystem fs) throws IOException
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export inwDetailreport to Excel *//*w w w. j av a 2 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 . j av 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 .ja v a 2 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 ww.j a v a 2 s .c om*/ public void outSummaryExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; int display = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); book.setSheetName(0, "Outward Summary Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); //Adding the items to a list OTDetailReportDTO outDto = (OTDetailReportDTO) getNeftRepDTO(); Set keySet = outDto.getOutwardMap().keySet(); row = sheet.createRow(rowCount); //Have done with Heading rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue("NEFT Outward Summary Report"); Iterator it = keySet.iterator(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Code :"); cell = row.createCell((short) 1); cell.setCellValue(getBranchIFSCCode(String.valueOf(getReportDto().getIfscId()))); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Date :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getValueDate()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Type :"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getReportType()); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Generated By:"); cell = row.createCell((short) 1); cell.setCellValue(neftRepDTO.reportRunBy); /*row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short)0); cell.setCellValue("Status:"); cell = row.createCell((short)1); cell.setCellValue(getReportDto().getStatusValue());*/ row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 1); cell.setCellValue("Summary Of the Transactions"); while (it.hasNext()) { List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = (List) outDto.getOutwardMap().get(key); for (Iterator itr = listRep.iterator(); itr.hasNext();) { SummaryInfoElement info = (SummaryInfoElement) itr.next(); exportXLS.add(info); } if (exportXLS.size() != 0) { for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 3; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 1 && display == 0) { cell.setCellValue("No of Transaction"); } else if (j == 2 && display == 0) { cell.setCellValue("Amount (Rs.)"); } } else { SummaryInfoElement summary = ((SummaryInfoElement) exportXLS.get(roww - 1)); if (j == 0) { String heading = null; if (summary != null) { heading = summary.getHeading(); } cell.setCellValue(heading); } else if (j == 1) { int count = 0; if (summary != null) { count = summary.getCount(); } cell.setCellValue(String.valueOf(count)); } else if (j == 2) { // double amount = 0; BigDecimal amount = BigDecimal.ZERO; if (summary != null) { amount = new BigDecimal(summary.getAmount()); } //cell.setCellValue(amount.toString()); //Have done amount format. cell.setCellValue(FormatAmount.formatINRAmount(amount.toString())); } } cell.setCellStyle(caption_style); } } display = 1; } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
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 *//* ww w . ja va2 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 *///from w w w . j ava 2 s .c o 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 {/*ww w . ja va 2 s .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); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export RTGS Inward possible Return Report * @author Eswaripriyak/*ww w .j a v a 2 s . co 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, "NEFT 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("NEFT Inward possible Return 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) 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(); date = InstaReportUtil.getDateInSpecificFormat(dateFormat, date); } 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 Report" + 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 to used to export the Neft Exception Report *//*from w w w . ja va 2s . c om*/ public void neftExceptionReportExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; BigDecimal grandInwTotTxnAmt = BigDecimal.ZERO; //Only If the list is not empty if (getReportMap().size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "NEFT Exceptions Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); String statusName = ""; cell.setCellValue("NEFT Exceptions Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); 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) { statusName = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Status : " + statusName); BigDecimal inwTotTxnAmt = BigDecimal.ZERO; long sno = 0; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 11; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Msg Type"); } else if (j == 3) { cell.setCellValue("UTR Number"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Status"); } else if (j == 7) { cell.setCellValue("Amount"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgSubType(); } cell.setCellValue(msgType); } else if (j == 3) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String status = null; if (((ReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((ReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } else if (j == 7) { String inwTxnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { inwTxnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } inwTotTxnAmt = inwTotTxnAmt.add(new BigDecimal(inwTxnAmount).setScale(2)); cell.setCellValue(new BigDecimal(inwTxnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total ( Status : " + statusName + " ) "); cell = row.createCell((short) 7); cell.setCellValue(inwTotTxnAmt.toString()); grandInwTotTxnAmt = grandInwTotTxnAmt.add(inwTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 7); cell.setCellValue(grandInwTotTxnAmt.toString()); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while exporting NEFT Exception Report into Excel" + e.getMessage()); throw new Exception("Exception while exporting NEFT Exception Report into Excel" + e); } }
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 */// ww w .ja v 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); } }