List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet()
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Reconcillation report in to Excel */// w w w. j a v a 2 s . com public void generateRTGSReconcillationExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Reconciliation Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Reconciliation Report on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 3); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 6; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Message Type"); } else if (j == 3) { cell.setCellValue("Transaction Type"); } else if (j == 4) { cell.setCellValue("Total Txn"); } else if (j == 5) { cell.setCellValue("Total Txn Amount(Rs.)"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 4) { long txnCount = 0; if (((ReportDTO) exportXLS.get(roww - 1)).getCount() != 0) { txnCount = ((ReportDTO) exportXLS.get(roww - 1)).getCount(); } cell.setCellValue(txnCount); } else if (j == 5) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Generate Counter Party Wise Reconcillation report in to Excel *///ww w . j a v a 2 s .c om public void generateReconcilliationReportCPwiseExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double outerGrandTotTxnAmt = 0.0; BigDecimal outerGrandTotTxnAmt = BigDecimal.ZERO; //Only If the list is not empty if (getReportMap().size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "CP Wise Reconciliation Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Counter Party wise Reconciliation Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, Map<String, List<ReportDTO>>> entry = (Map.Entry<String, Map<String, List<ReportDTO>>>) z .next(); // double grandTotTxnAmt = 0.0; BigDecimal grandTotTxnAmt = BigDecimal.ZERO; String cp = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Counter Party : " + cp); row = sheet.createRow(rowCount); rowCount += 1; Map map = entry.getValue(); if (map != null && map.size() > 0) { Set innerSet = map.entrySet(); for (short j = 0; j < 7; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Tran Type"); } else if (j == 3) { cell.setCellValue("Msg Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } } for (Iterator e = innerSet.iterator(); e.hasNext();) { Map.Entry<String, List<ReportDTO>> innerEntry = (Map.Entry<String, List<ReportDTO>>) e .next(); String msgType = innerEntry.getKey(); List exportXLS = new ArrayList(1); exportXLS.addAll(innerEntry.getValue()); if (exportXLS.size() > 0) { // double totTxnAmt = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; long sno = 0; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(msgType); for (int i = exportXLS.size(), roww = 1; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 7; j++) { cell = row.createCell(j); // Setting values in cell for each and // every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String type = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { type = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(type); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)) .getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount).setScale(2)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("Sub Total Amount (Msg Type : " + msgType + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(totTxnAmt.toString()); grandTotTxnAmt = grandTotTxnAmt.add(totTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount (Counter Partywise : " + cp + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(grandTotTxnAmt.setScale(2).toString()); outerGrandTotTxnAmt = outerGrandTotTxnAmt.add(grandTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount : "); cell = row.createCell((short) 6); cell.setCellValue(outerGrandTotTxnAmt.setScale(2).toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the Generate Counter Party Wise Reconcillation report in to Excel *///from w w w. j a va 2s . c o m public void generateReconcilliationReportBranchwiseExportToExcel(ServletOutputStream out) throws Exception { try { int rowCount = 0; // double outerGrandTotTxnAmt = 0.0; BigDecimal outerGrandTotTxnAmt = BigDecimal.ZERO; //Only If the list is not empty if (getReportMap().size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Br. Wise Reconciliation Report", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Wise Reconcillation Report from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, Map<String, List<ReportDTO>>> entry = (Map.Entry<String, Map<String, List<ReportDTO>>>) z .next(); // double grandTotTxnAmt = 0.0; BigDecimal grandTotTxnAmt = BigDecimal.ZERO; String branch = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch : " + branch); row = sheet.createRow(rowCount); rowCount += 1; Map map = entry.getValue(); if (map != null && map.size() > 0) { Set innerSet = map.entrySet(); for (short j = 0; j < 7; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Value Date"); } else if (j == 2) { cell.setCellValue("Tran Type"); } else if (j == 3) { cell.setCellValue("Msg Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Amount (Rs.)"); } } for (Iterator e = innerSet.iterator(); e.hasNext();) { Map.Entry<String, List<ReportDTO>> innerEntry = (Map.Entry<String, List<ReportDTO>>) e .next(); String msgType = innerEntry.getKey(); List exportXLS = new ArrayList(1); exportXLS.addAll(innerEntry.getValue()); if (exportXLS.size() > 0) { // double totTxnAmt = 0; BigDecimal totTxnAmt = BigDecimal.ZERO; long sno = 0; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(msgType); for (int i = exportXLS.size(), roww = 1; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 7; j++) { cell = row.createCell(j); // Setting values in cell for each and // every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); } cell.setCellValue(valueDate); } else if (j == 2) { String tranType = null; if (((ReportDTO) exportXLS.get(roww - 1)).getTranType() != null) { tranType = ((ReportDTO) exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 3) { String type = null; if (((ReportDTO) exportXLS.get(roww - 1)).getMsgType() != null) { type = ((ReportDTO) exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(type); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)) .getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { // double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("Sub Total Amount (Msg Type : " + msgType + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(totTxnAmt.setScale(2).toString()); grandTotTxnAmt = grandTotTxnAmt.add(totTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount (Branch : " + branch + " ) : "); cell = row.createCell((short) 6); cell.setCellValue(grandTotTxnAmt.setScale(2).toString()); outerGrandTotTxnAmt = outerGrandTotTxnAmt.add(grandTotTxnAmt); row = sheet.createRow(rowCount); rowCount += 1; } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 5); cell.setCellValue("TOTAL Amount : "); cell = row.createCell((short) 6); cell.setCellValue(outerGrandTotTxnAmt.toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method used to export the outward returned report in to Excel *//*from w w w . j a va2s. co m*/ public void generateOwReturnedExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double totTxnAmt = 0.0; BigDecimal totTxnAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getReportDTOs().iterator(); i.hasNext();) { ReportDTO dtoList = (ReportDTO) i.next(); exportXLS.add(dtoList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "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("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) 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("Value Date"); } else if (j == 2) { cell.setCellValue("Sender Address"); } else if (j == 3) { cell.setCellValue("Receiver Address"); } else if (j == 4) { cell.setCellValue("UTR No"); } else if (j == 5) { cell.setCellValue("Original UTR No"); } else if (j == 6) { cell.setCellValue("Info"); } else if (j == 7) { cell.setCellValue("Additional 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 valueDate = null; if (((ReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((ReportDTO) exportXLS.get(roww - 1)).getValueDate(); } valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); cell.setCellValue(valueDate); } else if (j == 2) { String sendAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 3) { String recAdd = null; if (((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 4) { String utrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 5) { String orgUtrNo = null; if (((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo() != null) { orgUtrNo = ((ReportDTO) exportXLS.get(roww - 1)).getOutUTRNo(); } cell.setCellValue(orgUtrNo); } else if (j == 6) { String i7495 = null; if (((ReportDTO) exportXLS.get(roww - 1)).getFieldI7495() != null) { i7495 = ((ReportDTO) exportXLS.get(roww - 1)).getFieldI7495(); } cell.setCellValue(i7495); } 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) { //double txnAmount = 0.0; String txnAmount = "0.00"; if (new BigDecimal(((ReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0.0) { txnAmount = ((ReportDTO) exportXLS.get(roww - 1)).getAmt(); } totTxnAmt = totTxnAmt.add(new BigDecimal(txnAmount)); cell.setCellValue(new BigDecimal(txnAmount).setScale(2).toString()); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 7); cell.setCellValue("Total Amount : "); cell = row.createCell((short) 8); cell.setCellValue(totTxnAmt.setScale(2).toString()); row = sheet.createRow(rowCount); rowCount += 1; book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.rtgs.reports.bean.InstaReportBean.java
License:Open Source License
/** * Method for Cretaing Excel for NEFT RTGS Net Settlement Report */// w ww . ja v a2 s . co m public void NEFTRTGS_NetSettlementExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; //To add serial number by priyak Map LMSaggregateMap = new HashMap(); int rowCount = 0; exportXLS = this.getNEFTRTGS_settlementList(); LMSaggregateMap = this.getNEFTRTGS_settlementMap(); rowCount = exportXLS.size(); 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-RTGS Net Settlement 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("Net Settlements received from RTGS"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Date :"); cell = row.createCell((short) 1); cell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(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; if (exportXLS.size() != 0) { rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 6; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("Msg Type"); } else if (j == 1) { cell.setCellValue("Ordering Institution"); } else if (j == 2) { cell.setCellValue("Code"); } else if (j == 3) { cell.setCellValue("Info"); } else if (j == 4) { cell.setCellValue("Additional Info"); } else if (j == 5) { cell.setCellValue("Amount"); } } else { // Setting values in cell for each and every row if (j == 0) { String msgType = ""; msgType = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getMsgType() + ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getMsgSubType(); cell.setCellValue(msgType); } else if (j == 1) { String orderingInstitution = ""; orderingInstitution = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)) .getOrderingInstitution(); cell.setCellValue((orderingInstitution == null) ? "" : orderingInstitution); } else if (j == 2) { String code = ""; code = this.getNEFTRTGS_NetSettleKeyword(); cell.setCellValue((code == null) ? "" : code); } else if (j == 3) { String info = ""; info = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getInfo(); cell.setCellValue((info == null) ? "" : info); } else if (j == 4) { String additionalInfo = ""; additionalInfo = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)) .getAdditionalInfo(); cell.setCellValue((additionalInfo == null) ? "" : additionalInfo); } else if (j == 5) { String amount = ""; amount = ((NEFT_RTGSNetSettlementDTO) exportXLS.get(roww - 1)).getAmount(); cell.setCellValue((amount == null) ? "0.00" : amount); } } cell.setCellStyle(caption_style); } } } row = sheet.createRow(rowCount); rowCount += 1; row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue("LMS NEFT Aggregate"); int rows = 0; short j; if (LMSaggregateMap.size() > 0) { Set<Map.Entry<String, String>> entrySet = LMSaggregateMap.entrySet(); row = sheet.createRow(rowCount); rowCount += 1; for (j = 0; j < 3; j++) { cell = row.createCell(j); if (rows == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Aggregate Amount\r\n(Total Credit-Total Debit)"); } } } for (Iterator<Map.Entry<String, String>> i = entrySet.iterator(); i.hasNext(); rows++) { Map.Entry<String, String> entry = i.next(); // String key = entry.getKey(); row = sheet.createRow(rowCount); rowCount += 1; for (j = 0; j < 3; j++) { cell = row.createCell(j); if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String batchTime = ""; batchTime = entry.getKey(); cell.setCellValue(batchTime); } else if (j == 2) { String totAmount = "0.00"; totAmount = entry.getValue(); //cell.setCellValue(FormatAmount.formatINRAmount(totAmount)); cell.setCellValue(new BigDecimal(totAmount).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.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
public static InputStream convertToGenericTollTagFormat(InputStream is, Long tollCompanyId, GenericDAO genericDAO, ImportMainSheetService importMainSheetService) throws Exception { String tollCompanyName = getTollCompanyName(genericDAO, tollCompanyId); LinkedHashMap<String, String> actualColumnListMap = getTollCompanySpecificMapping(tollCompanyName); List<LinkedList<Object>> tempData = importMainSheetService.importTollCompanySpecificTollTag(is, actualColumnListMap, tollCompanyId); System.out.println("Number of rows = " + tempData.size()); HSSFWorkbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(); CellStyle style = wb.createCellStyle(); createColumnHeaders(expectedColumnList, sheet, style); int rowIndex = 1; for (int i = 0; i < tempData.size(); i++) { System.out.println("Creating Row " + i + " with data = \n" + tempData.get(i)); int columnIndex = 0; LinkedList<Object> oneRow = tempData.get(i); Row row = sheet.createRow(rowIndex++); for (Object oneCellValue : oneRow) { if (columnIndex >= expectedColumnList.size()) { // For vendors where more than required columns are read from excel break; }// ww w .j a v a 2 s . c o m System.out.println("Creating Column @ " + columnIndex + " with value = " + oneCellValue); Cell cell = createExcelCell(sheet, row, columnIndex); formatCellValueForTollCompany(wb, cell, oneCellValue, tollCompanyName); columnIndex++; } } InputStream targetStream = createInputStream(wb); return targetStream; }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
public static ByteArrayOutputStream createTollUploadSuccessResponse() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFFont font = wb.createFont();//from w ww .j a va2 s. co m font.setColor(IndexedColors.GREEN.getIndex()); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); Cell cell = createExcelCell(sheet, row, 0, 256 * 100); cell.setCellStyle(cellStyle); cell.setCellValue("ALL tolls uploaded successfully"); return createOutputStream(wb); }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
public static ByteArrayOutputStream createTollUploadExceptionResponse(Exception e) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFFont font = wb.createFont();//from w w w. ja v a 2s.c o m font.setColor(Font.COLOR_RED); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); Cell cell = createExcelCell(sheet, row, 0, 256 * 100); cell.setCellStyle(cellStyle); cell.setCellValue("An error occurred while uploading!!!"); return createOutputStream(wb); }
From source file:com.pureinfo.dolphin.export.impl.ExcelExporterImpl.java
License:Open Source License
/** * @throws PureException/*from w ww . jav a 2 s . c o m*/ * @see com.pureinfo.dolphin.export.IExporter#export(OutputStream, * IExportGoods) */ public void export(OutputStream _os, IExportGoods _goods) throws PureException { try { //1. to create sheet HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, _goods.getName(), (short) 1); //2. to export headers if (_goods.hasHeader()) { HSSFCellStyle headerStyle = getHeaderStyle(workbook); exportHeaders(sheet, _goods.getHeaders(), headerStyle); } //3. to export data Object[] values; HSSFCellStyle dateStyle = getDateStyle(workbook); int nRowNum = 1; Iterator iter = _goods.iterator(); while (iter.hasNext()) { values = _goods.unpackGoods(iter.next()); exportRow(sheet, values, nRowNum++, dateStyle); } //4. to output to stream workbook.write(_os); } catch (IOException ex) { throw new PureException(PureException.UNKNOWN, "export " + _goods.getName() + " to excel", ex); } }
From source file:com.pureinfo.srm.project.model.compile.helper.CompileExcelExportHelper.java
License:Open Source License
public void export(OutputStream _os, IExportGoods _goods, int _year) throws PureException { try {/*from w w w . j av a2 s.c o m*/ // 1. to create sheet HSSFWorkbook workbook = new HSSFWorkbook(); style = workbook.createCellStyle(); style.setWrapText(true); HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, _goods.getName(), (short) 1); // change the print way to landscape sheet.getPrintSetup().setLandscape(true); // headLine is the true header of the page. // Workbook book = new CompileWorkBook(); // int index = book.addSSTString(HSSFHeader.font("", "Border") + // HSSFHeader.fontSize((short) 16) // + "\u6d59\u6c5f\u5927\u5b66" + _year // + // "\u5e74\u9ad8\u65b0\u529e\u65b0\u4e0a\u9879\u76ee\u8ba1\u5212\u9879\u76ee\u5355",true); HSSFHeader headLine = sheet.getHeader(); headLine.setCenter(HSSFHeader.font("", "Border") + HSSFHeader.fontSize((short) 16) + "" + _year + ""); // footer HSSFFooter footer = sheet.getFooter(); footer.setRight("." + HSSFFooter.page() + "."); footer.setLeft(this.dateFormat(new Date())); // 2. to export headers if (_goods.hasHeader()) { HSSFCellStyle headerStyle = getHeaderStyle(workbook); exportHeaders(sheet, _goods.getHeaders(), headerStyle); } // 3. to export data Object[] values; HSSFCellStyle dateStyle = getDateStyle(workbook); HSSFCellStyle doubleStyle = getDoubleStyle(workbook); int nRowNum = 1; Iterator iter = _goods.iterator(); while (iter.hasNext()) { values = _goods.unpackGoods(iter.next()); exportRow(sheet, values, nRowNum++, dateStyle, doubleStyle); } // 4. to output to stream workbook.write(_os); } catch (IOException ex) { throw new PureException(PureException.UNKNOWN, "export " + _goods.getName() + " to excel", ex); } }