List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow
@Override public HSSFRow createRow(int rownum)
From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java
License:Open Source License
private void createLargestFreeSizeTab() { HSSFSheet sheet = wb.createSheet(); // declare a row object reference HSSFRow row = null;/*w w w . ja v a 2 s.c o m*/ HSSFRow description = null; // declare a cell object reference HSSFCell cell = null; //set the sheet name in Unicode wb.setSheetName(11, "Largest free cell size in heap"); row = sheet.createRow(0); //r.setHeight((short)500); cell = row.createCell(0); cell.setCellStyle(styles.get("header")); cell.setCellValue(new HSSFRichTextString("Largest free cell size in heap")); description = sheet.createRow(1); //creates an empty row. row = sheet.createRow(2); row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(styles.get("header1")); cell.setCellValue(new HSSFRichTextString("Threads")); addCycleIntervals(row); cell = row.createCell((int) row.getLastCellNum()); cell.setCellStyle(styles.get("header2")); cell.setCellValue(new HSSFRichTextString("Delta")); int rowNo = 4; for (String heap : heapThreads) { row = sheet.createRow(rowNo); cell = row.createCell(0); cell.setCellStyle(styles.get("cell_normal")); cell.setCellValue(new HSSFRichTextString(heap)); ArrayList<ThreadData> heapValues = heapData.get(heap.toLowerCase()); if (heapValues != null) { int j = 1; for (int i = 0; i < heapValues.size(); i++) { if (constants.contains(i + 1)) continue; cell = row.createCell(j++); cell.setCellStyle(styles.get("cell_number")); ThreadData thData = heapValues.get(i); if (thData.getStatus() == 0) { //cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellValue(new HSSFRichTextString(NOT_AVAILABLE)); } else cell.setCellValue(thData.getLargestFreeCellSize()); } } cell = row.createCell(logData.getNumberOfCycles() + 1 - constants.size()); cell.setCellStyle(styles.get("cell_number")); if (deltaData.get(heap.toLowerCase()) != null) { long largestFreeCellDelta = deltaData.get(heap.toLowerCase()).getLargestFreeCellSize(); cell.setCellValue(largestFreeCellDelta); } else cell.setCellValue(0); rowNo++; } for (int i = 0; i <= logData.getNumberOfCycles(); i++) { sheet.autoSizeColumn((short) i); } cell = description.createCell(0); cell.setCellValue(new HSSFRichTextString( "Specifies the largest free cell size in heap for each thread in bytes, for each cycle in seconds")); }
From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java
License:Open Source License
/** * This method creates the sheet for Global Data. * // w ww. j a v a 2s .c om * */ private void createGlobalChunksSheet() { HSSFSheet sheet = wb.createSheet(); // declare a row object reference HSSFRow row = null; HSSFRow description = null; // declare a cell object reference HSSFCell cell = null; //set the sheet name in Unicode wb.setSheetName(2, "Global Data"); row = sheet.createRow(0); //r.setHeight((short)500); cell = row.createCell(0); cell.setCellStyle(styles.get("header")); cell.setCellValue(new HSSFRichTextString("Global Data")); description = sheet.createRow(1); //creates an empty row. row = sheet.createRow(2); row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(styles.get("header1")); cell.setCellValue(new HSSFRichTextString("Chunk Names")); addCycleIntervals(row); cell = row.createCell((int) row.getLastCellNum()); cell.setCellStyle(styles.get("header2")); cell.setCellValue(new HSSFRichTextString("Delta")); int rowNo = 4; for (String heap : glodChunks) { row = sheet.createRow(rowNo); cell = row.createCell(0); cell.setCellStyle(styles.get("cell_normal")); cell.setCellValue(new HSSFRichTextString(heap)); ArrayList<GlobalDataChunks> heapValues = glodData.get(heap); int j = 1; for (int i = 0; i < heapValues.size(); i++) { if (constants.contains(i + 1)) continue; cell = row.createCell(j++); cell.setCellStyle(styles.get("cell_number")); GlobalDataChunks thData = heapValues.get(i); if (thData.getAttrib() == 0) { //cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellValue(new HSSFRichTextString(NOT_AVAILABLE)); } else cell.setCellValue(thData.getSize()); } cell = row.createCell(logData.getNumberOfCycles() + 1 - constants.size()); cell.setCellStyle(styles.get("cell_number")); if (glodDeltaData.get(heap) != null) { long largestFreeCellDelta = Long.parseLong(glodDeltaData.get(heap)); cell.setCellValue(largestFreeCellDelta); } rowNo++; } for (int i = 0; i <= logData.getNumberOfCycles(); i++) { sheet.autoSizeColumn((short) i); } cell = description.createCell(0); cell.setCellValue(new HSSFRichTextString( "Specifies the chunk sizes in bytes that caontains global data, for each cycle in seconds")); }
From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java
License:Open Source License
private void createNonHeapChunkSheet() { //create a new sheet HSSFSheet sheet = wb.createSheet(); // declare a row object reference HSSFRow row = null;//from ww w . j ava 2 s . c o m HSSFRow description = null; // declare a cell object reference HSSFCell cell = null; //set the sheet name in Unicode wb.setSheetName(3, "Non-heap chunks"); row = sheet.createRow(0); //r.setHeight((short)500); cell = row.createCell(0); cell.setCellStyle(styles.get("header")); cell.setCellValue(new HSSFRichTextString("Non-Heap Chunk Size")); description = sheet.createRow(1); //creates an empty row row = sheet.createRow(2); row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(styles.get("header2")); cell.setCellValue(new HSSFRichTextString("Chunk Names")); addCycleIntervals(row); cell = row.createCell((int) row.getLastCellNum()); cell.setCellStyle(styles.get("header2")); cell.setCellValue(new HSSFRichTextString("Delta")); int rowNo = 4; for (String heap : nonHeapChunks) { row = sheet.createRow(rowNo); cell = row.createCell(0); cell.setCellStyle(styles.get("cell_normal")); cell.setCellValue(new HSSFRichTextString(heap)); ArrayList<ChunksData> chunkValues = chunkData.get(heap); int j = 1; for (int i = 0; i < chunkValues.size(); i++) { if (constants.contains(i + 1)) continue; cell = row.createCell(j++); cell.setCellStyle(styles.get("cell_number")); ChunksData chData = chunkValues.get(i); if (chData.getAttrib() == 0) { //cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellValue(new HSSFRichTextString(NOT_AVAILABLE)); } else cell.setCellValue(chData.getSize()); } cell = row.createCell(logData.getNumberOfCycles() + 1 - constants.size()); cell.setCellStyle(styles.get("cell_number")); if (chunkDeltaData.get(heap) != null) { long largestFreeCellDelta = Long.parseLong(chunkDeltaData.get(heap)); cell.setCellValue(largestFreeCellDelta); } rowNo++; } for (int i = 0; i <= logData.getNumberOfCycles(); i++) { sheet.autoSizeColumn((short) i); } cell = description.createCell(0); cell.setCellValue( new HSSFRichTextString("Specifies the non heap chunk sizes in bytes, for each cycle in seconds")); }
From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java
License:Open Source License
/** * This method creates sheet with Window Group events. * *//*ww w . jav a 2s . co m*/ private void createWindowGroupSheet() { //create a new sheet HSSFSheet sheet = wb.createSheet(); // declare a row object reference HSSFRow row = null; HSSFRow description = null; // declare a cell object reference HSSFCell cell = null; //set the sheet name in Unicode wb.setSheetName(12, "Window Groups"); row = sheet.createRow(0); //r.setHeight((short)500); cell = row.createCell(0); cell.setCellStyle(styles.get("header")); cell.setCellValue(new HSSFRichTextString("Window Group Events")); description = sheet.createRow(1); //creates an empty row row = sheet.createRow(2); row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(styles.get("header2")); cell.setCellValue(new HSSFRichTextString("Window Group Names")); addCycleIntervals(row); SWMTLogReaderUtils utils = new SWMTLogReaderUtils(); ArrayList<String> wndg_names = utils.getWindowGroupNames(logData); int rowNo = 4; if (wndg_names == null) return; else { for (String name : wndg_names) { row = sheet.createRow(rowNo++); cell = row.createCell(0); cell.setCellStyle(styles.get("cell_normal")); cell.setCellValue(new HSSFRichTextString(name)); ArrayList<WindowGroupEventData> events_list = utils.getAllWindowGroupEvents(name, logData); int j = 1; for (int i = 0; i < events_list.size(); i++) { if (constants.contains(i + 1)) continue; cell = row.createCell(j++); cell.setCellStyle(styles.get("cell_normal")); WindowGroupEventData eventSet = events_list.get(i); if (eventSet == null) { cell.setCellValue(new HSSFRichTextString(NOT_AVAILABLE)); } else { StringBuffer tmp = new StringBuffer(); if (eventSet.getEvent_0_count() > 0) tmp.append(" NoEvent(" + eventSet.getEvent_0_count() + ")"); if (eventSet.getEvent_1_count() > 0) tmp.append(" NameChanged(" + eventSet.getEvent_1_count() + ")"); if (eventSet.getEvent_2_count() > 0) tmp.append(" FocusGained(" + eventSet.getEvent_2_count() + ")"); if (eventSet.getEvent_3_count() > 0) tmp.append(" FocusLost(" + eventSet.getEvent_3_count() + ")"); cell.setCellValue(new HSSFRichTextString(tmp.toString())); } } } for (int i = 0; i <= logData.getNumberOfCycles(); i++) sheet.autoSizeColumn((short) i); cell = description.createCell(0); cell.setCellValue(new HSSFRichTextString( "Specifies the type and number of events for each window group, in each cycle.")); } }
From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java
License:Open Source License
private void createDataInOverView(HSSFSheet sheet, int rowNo) { SWMTLogReaderUtils utils = new SWMTLogReaderUtils(); ArrayList<String> allThreads = utils.getAllThreadNames(logData); Collections.sort(allThreads); long[] maxHeapSizes = new long[allThreads.size()]; long[] heapSizes = new long[allThreads.size()]; long[] freeCells = new long[allThreads.size()]; long[] allocCells = new long[allThreads.size()]; long[] freeSpace = new long[allThreads.size()]; long[] allocSpace = new long[allThreads.size()]; long[] slackSpace = new long[allThreads.size()]; long[] totalFiles = new long[allThreads.size()]; long[] totalHandles = new long[allThreads.size()]; long[] totalStacks = new long[allThreads.size()]; int i = 0;/*from w w w . j a va 2 s.c o m*/ for (String thName : allThreads) { if (thName.startsWith("MemSpy") && thName.endsWith("::MemSpy")) continue; HSSFRow row = sheet.createRow(rowNo); HSSFCell cell = row.createCell(0); cell.setCellStyle(styles.get("cell_normal")); cell.setCellValue(new HSSFRichTextString(thName)); cell = row.createCell(1); cell.setCellStyle(styles.get("cell_normal")); if (logData.getNumberOfCycles() == 1) { cell.setCellValue(new HSSFRichTextString("Alive")); } else { int status = utils.getThreadStatusFromAllCycles(thName, logData); if (status == 0) cell.setCellValue(new HSSFRichTextString("Dead")); else if (status == 1 || status == 2) cell.setCellValue(new HSSFRichTextString("Alive")); else cell.setCellValue(new HSSFRichTextString(NOT_AVAILABLE)); } ThreadData threadDelta = deltaData.get(thName.toLowerCase()); cell = row.createCell(2); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getMaxHeapSize()); maxHeapSizes[i] = threadDelta.getMaxHeapSize(); /*ArrayList<String> heapThreads = utils.getAllHeapThreads(logData); long delta = 0; long lastValue = 0; ArrayList<ThreadData> heapData = utils.getHeapDataFromAllCycles(thName, logData); if(utils.getHeapStatusFromAllCycles(thName, logData) == 0) { //Display zeros for all heap fields maxHeapSize = 0; delta = 0; } else{ lastValue = heapData.get(logData.size()-1).getHeapChunkSize(); maxHeapSize = heapData.get(logData.size()-1).getMaxHeapSize(); long firstValue = 0; for(int i=heapData.size()-2; i>=0; i--) { ThreadData data = heapData.get(i); if(data.getStatus() != CycleData.Deleted){ firstValue = data.getHeapChunkSize(); } else break; } if(firstValue != -1) delta = lastValue - firstValue; } */ //cell.setCellValue(maxHeapSize); cell = row.createCell(3); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getHeapChunkSize()); heapSizes[i] = threadDelta.getHeapChunkSize(); cell = row.createCell(4); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getHeapAllocatedSpace()); allocSpace[i] = threadDelta.getHeapAllocatedSpace(); cell = row.createCell(5); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getHeapFreeSpace()); freeSpace[i] = threadDelta.getHeapFreeSpace(); cell = row.createCell(6); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getAllocatedCells()); allocCells[i] = threadDelta.getAllocatedCells(); cell = row.createCell(7); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getFreeCells()); freeCells[i] = threadDelta.getFreeCells(); cell = row.createCell(8); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getFreeSlackSize()); slackSpace[i] = threadDelta.getFreeSlackSize(); cell = row.createCell(9); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getStackSize()); totalStacks[i] = threadDelta.getStackSize(); cell = row.createCell(10); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getOpenFiles()); totalFiles[i] = threadDelta.getOpenFiles(); cell = row.createCell(11); cell.setCellStyle(styles.get("cell_number")); cell.setCellValue(threadDelta.getPsHandles()); totalHandles[i] = threadDelta.getPsHandles(); i++; rowNo++; } if (allThreads.size() > 0) { HSSFRow totalRow = sheet.createRow(rowNo); long totalMaxHeapSize = utils.calculateAndGetTotal(maxHeapSizes); long totalStackSize = utils.calculateAndGetTotal(totalStacks); totalHeapSizeChange = utils.calculateAndGetTotal(heapSizes); totalFreeCellChange = utils.calculateAndGetTotal(freeCells); totalAllocCellChange = utils.calculateAndGetTotal(allocCells); totalFreeSpaceChange = utils.calculateAndGetTotal(freeSpace); totalAllocSpaceChange = utils.calculateAndGetTotal(allocSpace); totalSlackSpaceChange = utils.calculateAndGetTotal(slackSpace); totalFilesChange = utils.calculateAndGetTotal(totalFiles); totalHandlesChange = utils.calculateAndGetTotal(totalHandles); HSSFCell cell = totalRow.createCell(2); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalMaxHeapSize); cell = totalRow.createCell(3); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalHeapSizeChange); cell = totalRow.createCell(4); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalAllocSpaceChange); cell = totalRow.createCell(5); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalFreeSpaceChange); cell = totalRow.createCell(6); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalAllocCellChange); cell = totalRow.createCell(7); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalFreeCellChange); cell = totalRow.createCell(8); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalSlackSpaceChange); cell = totalRow.createCell(9); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalStackSize); cell = totalRow.createCell(10); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalFilesChange); cell = totalRow.createCell(11); cell.setCellStyle(styles.get("cell_blue_font_white")); cell.setCellValue(totalHandlesChange); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the paymentreport in to Excel */// ww w.j av a2 s .c om public void paymentExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double totAmt = 0; BigDecimal totAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getDetailReportDTOs().iterator(); i.hasNext();) { NEFTDetailsReportDTO indentList = (NEFTDetailsReportDTO) i.next(); exportXLS.add(indentList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; if (report.equalsIgnoreCase("submitted")) { book.setSheetName(0, "Payments Submitted", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); } else { book.setSheetName(0, "Payments Received", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); } caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); if (report.equalsIgnoreCase("submitted")) { cell.setCellValue("Payment Submitted Report " + reportDto.getPaymentType() + " From " + reportDto.getValueDate() + " to " + reportDto.getToDate() + " with status " + reportDto.getStatusValue()); } else { cell.setCellValue("Payment Received Report " + reportDto.getPaymentType() + " From " + reportDto.getValueDate() + " to " + reportDto.getToDate() + " with status " + reportDto.getStatusValue()); } for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 9; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.NO"); } else if (j == 1) { cell.setCellValue("VALUE DATE"); } else if (j == 2) { cell.setCellValue("SENDER ADDRESS"); } else if (j == 3) { cell.setCellValue("RECEIVER ADDRESS"); } else if (j == 4) { cell.setCellValue("UTR NUMBER"); } else if (j == 5) { cell.setCellValue("ACCOUNT NUMBER"); } else if (j == 6) { cell.setCellValue("BENIFICIARY DETAILS"); } else if (j == 7) { cell.setCellValue("AMOUNT(Rs)"); } else if (j == 8) { cell.setCellValue("STATUS"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(valueDate); } else if (j == 2) { String sendAdd = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 3) { String recAdd = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 4) { String utrNo = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 5) { String accNo = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6021() != null) { accNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6021(); } else if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6061() != null) { accNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6061(); } cell.setCellValue(accNo); } else if (j == 6) { String beniDetails = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField5565() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField5565(); if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6081() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6081() + "-" + beniDetails; } } else if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6091() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6091(); } cell.setCellValue(beniDetails); } else if (j == 7) { String amt = null; if (new BigDecimal(((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0) { // totAmt += ((NEFTDetailsReportDTO)exportXLS // .get(roww - 1)).getAmount(); totAmt = totAmt.add(new BigDecimal( ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()).setScale(2)); amt = String.valueOf(((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()); } cell.setCellValue(new BigDecimal(amt).setScale(2).toString()); } else if (j == 8) { //To add status column in the excel sheet. String status = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 6); cell.setCellValue("TOTAL AMOUNT"); cell.setCellStyle(caption_style); cell = row.createCell((short) 7); cell.setCellValue(String.valueOf(totAmt)); cell.setCellStyle(caption_style); sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the graduated paymentreport in to Excel *///from w w w . ja va 2s . c o m public void graduatedPaymentExportToExcel(ServletOutputStream out) throws Exception { try { List<ReportDTO> exportXLS = new ArrayList<ReportDTO>(1); long sno = 0; int rowCount = 0; //double totAmt = 0; //Adding the items to a list for (Iterator i = getGraduadtedPayments().iterator(); i.hasNext();) { ReportDTO dto = (ReportDTO) i.next(); exportXLS.add(dto); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Graduated Payment", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue("Datewise Graduated Payment Report"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 10); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 13; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("UTR No"); } else if (j == 2) { cell.setCellValue("Msg Type"); } else if (j == 3) { cell.setCellValue("Tran Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Credit Amount(Rs)"); } else if (j == 7) { cell.setCellValue("Debit Amount(Rs)"); } else if (j == 8) { cell.setCellValue("Batch Time"); } else if (j == 9) { cell.setCellValue("Rescheduled Date"); } else if (j == 10) { cell.setCellValue("Rescheduled Batch Time"); } else if (j == 11) { cell.setCellValue("Rejected Date"); } else if (j == 12) { cell.setCellValue("Rejected Batch Time"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String utrNo = null; if ((exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = (exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String msgType = null; if ((exportXLS.get(roww - 1)).getMsgType() != null) { msgType = (exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String tranType = null; if ((exportXLS.get(roww - 1)).getTranType() != null) { tranType = (exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 4) { String sendAdd = null; if ((exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = (exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if ((exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = (exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { //Modified by priyak to maintain uniformity String crdDeb = null; String amount = null; if ((exportXLS.get(roww - 1)).getDebitCredit() != null) { crdDeb = (exportXLS.get(roww - 1)).getDebitCredit(); if (crdDeb.equals("Credit")) { amount = (exportXLS.get(roww - 1)).getAmt(); } else { amount = "0.00"; } } cell.setCellValue(amount); } else if (j == 7) { String crdDeb = null; String amount = null; if ((exportXLS.get(roww - 1)).getDebitCredit() != null) { crdDeb = (exportXLS.get(roww - 1)).getDebitCredit(); if (crdDeb.equals("Debit")) { amount = (exportXLS.get(roww - 1)).getAmt(); } else { amount = "0.00"; } } cell.setCellValue(amount); } else if (j == 8) { String batchTime = null; if ((exportXLS.get(roww - 1)).getBatchTime() != null) { batchTime = (exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batchTime); } else if (j == 9) { String reshDate = null; if ((exportXLS.get(roww - 1)).getReshDate() != null) { reshDate = (exportXLS.get(roww - 1)).getReshDate(); reshDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, reshDate); } cell.setCellValue(reshDate); } else if (j == 10) { String reshBatchTime = null; if ((exportXLS.get(roww - 1)).getReshBatchTime() != null) { reshBatchTime = (exportXLS.get(roww - 1)).getReshBatchTime(); } cell.setCellValue(reshBatchTime); } else if (j == 11) { String rejDate = null; if ((exportXLS.get(roww - 1)).getRejDate() != null) { rejDate = (exportXLS.get(roww - 1)).getRejDate(); rejDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, rejDate); } cell.setCellValue(rejDate); } else if (j == 12) { String rejBatchTime = null; if ((exportXLS.get(roww - 1)).getRejBatchTime() != null) { rejBatchTime = (exportXLS.get(roww - 1)).getRejBatchTime(); } cell.setCellValue(rejBatchTime); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Br inward returned report in to Excel *///from ww w .j ava2 s. c om public void returnedInwardExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; // double totAmt = 0; BigDecimal totAmt = BigDecimal.ZERO; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getReportMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Inward Returned", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Report - Inward Returned from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 5); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); roww += 1; while (it.hasNext()) { List exportXLS = new ArrayList(1); // double subTotal = 0; BigDecimal subTotal = BigDecimal.ZERO; String date = (String) it.next(); List listRep = (List) reportMap.get(date); for (Iterator itr = listRep.iterator(); itr.hasNext();) { ReportDTO repDTO = (ReportDTO) itr.next(); exportXLS.add(repDTO); } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("DATE :" + date + " BATCH TIME :" + reportDto.getBatchTime()); //Only If the DTO is not empty if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 8; j++) { cell = row.createCell(j); // for header if (count == 0) { // for header if (j == 0) { cell.setCellValue("S.NO"); } else if (j == 1) { cell.setCellValue("VALUE DATE"); } else if (j == 2) { cell.setCellValue("MSG TYPE"); } else if (j == 3) { cell.setCellValue("UTR NUMBER"); } else if (j == 4) { cell.setCellValue("SENDER ADDRESS"); } else if (j == 5) { cell.setCellValue("RECEIVER ADDRESS"); } else if (j == 6) { cell.setCellValue("OUTWARD UTR NO"); } else if (j == 7) { cell.setCellValue("AMOUNT(Rs)"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String valueDate = null; if (date != null) { valueDate = date; } valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(count - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(count - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String utrNo = null; if (((ReportDTO) exportXLS.get(count - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(count - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(count - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(count - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(count - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(count - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String outUtr = null; if (((ReportDTO) exportXLS.get(count - 1)).getOutUTRNo() != null) { outUtr = ((ReportDTO) exportXLS.get(count - 1)).getOutUTRNo(); } cell.setCellValue(outUtr); } else if (j == 7) { String amt = null; // if (((ReportDTO)exportXLS // .get(count-1)).getAmount() != 0) { if (new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0) { // totAmt += ((ReportDTO)exportXLS // .get(count-1)).getAmount(); // subTotal += ((ReportDTO)exportXLS // .get(count-1)).getAmount(); // amt = String.valueOf(((ReportDTO)exportXLS // .get(count-1)).getAmount()); totAmt = totAmt.add( new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt())); subTotal = subTotal.add( new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt())); amt = String.valueOf(((ReportDTO) exportXLS.get(count - 1)).getAmt()); } cell.setCellValue(new BigDecimal(amt).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 6); cell.setCellValue("Sub Total(Date :" + date + ")"); cell = row.createCell((short) 7); //cell.setCellValue(String.valueOf(subTotal)); cell.setCellValue(subTotal.setScale(2).toString()); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount"); cell = row.createCell((short) 7); // cell.setCellValue(String.valueOf(totAmt)); cell.setCellValue(totAmt.setScale(2).toString()); sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Batchwise Reconcilition report in to Excel *//*from ww w .jav a2s .c o m*/ public void batchwiseReconcillationExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getReconcillationMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Batchwise Reconciliation", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batchwise - Reconciliation Report "); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Report Date"); cell = row.createCell((short) 1); cell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); /* row = sheet.createRow(roww); //Commented by priyak roww += 1; cell = row.createCell((short)0); cell.setCellValue("Transaction Type"); cell = row.createCell((short)1); cell.setCellValue(getReportDto().getTransactionType());*/ row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 9); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); roww += 1; while (it.hasNext()) { String type = null; List exportXLS = new ArrayList(1); String key = (String) it.next(); List listRep = reconcillationMap.get(key); if (listRep.size() > 0) { for (Iterator itr = listRep.iterator(); itr.hasNext();) { if (key.equals("N04")) { type = "As Per N04"; NEFTN04DetailsDTO n04DTO = (NEFTN04DetailsDTO) itr.next(); exportXLS.add(n04DTO); } else { type = "As Per LMS"; BatchwiseReconcillationDTO n04DTO = (BatchwiseReconcillationDTO) itr.next(); exportXLS.add(n04DTO); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue(type); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellValue("Outward Transactions"); cell = row.createCell((short) 3); cell.setCellValue(""); cell = row.createCell((short) 4); cell.setCellValue(""); cell = row.createCell((short) 5); cell.setCellValue(""); cell = row.createCell((short) 6); cell.setCellValue(""); cell = row.createCell((short) 7); cell.setCellValue(""); cell = row.createCell((short) 8); cell.setCellValue("Inward Transactions"); if (type.equalsIgnoreCase("As Per N04")) { if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); // for header if (count == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Total amount Sent"); } else if (j == 4) { cell.setCellValue("Total no.of txns Accepted"); } else if (j == 5) { cell.setCellValue("Total amount Accepted"); } else if (j == 6) { cell.setCellValue("Total no.of txns Rejected"); } else if (j == 7) { cell.setCellValue("Total amount Rejected"); } else if (j == 8) { cell.setCellValue("Total no.of txns Received"); } else if (j == 9) { cell.setCellValue("Total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns Returned"); } else if (j == 11) { cell.setCellValue("Total amount Returned"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String batchTime = null; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField3535() != null) { batchTime = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField3535(); } cell.setCellValue(batchTime); } else if (j == 2) { String noSent = null; noSent = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)).getField5175(); cell.setCellValue(noSent); } else if (j == 3) { String amtSent = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4105() != null) { amtSent = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4105(); } //cell.setCellValue(amtSent); cell.setCellValue(new BigDecimal(amtSent).setScale(2).toString()); } else if (j == 4) { String noAccept = null; noAccept = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5180(); cell.setCellValue(noAccept); } else if (j == 5) { String amtAccept = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4110() != null) { amtAccept = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4110(); if (amtAccept.indexOf(",") != -1) { amtAccept = amtAccept.replace(",", "."); } } //cell.setCellValue(amtAccept); cell.setCellValue(new BigDecimal(amtAccept).setScale(2).toString()); } else if (j == 6) { String noReject = null; noReject = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5185(); cell.setCellValue(noReject); } else if (j == 7) { String amtReject = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4115() != null) { amtReject = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4115(); } //cell.setCellValue(amtReject); cell.setCellValue(new BigDecimal(amtReject).setScale(2).toString()); } else if (j == 8) { String noReceive = null; noReceive = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5267(); cell.setCellValue(noReceive); } else if (j == 9) { String amtReceive = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4410() != null) { amtReceive = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4410(); } //cell.setCellValue(amtReceive); cell.setCellValue(new BigDecimal(amtReceive).setScale(2).toString()); } else if (j == 10) { String noReturn = null; noReturn = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField5047(); cell.setCellValue(noReturn); } else if (j == 11) { String amtReturn = "0.00"; if (((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4460() != null) { amtReturn = ((NEFTN04DetailsDTO) exportXLS.get(count - 1)) .getField4460(); } //cell.setCellValue(amtReturn); cell.setCellValue(new BigDecimal(amtReturn).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } } } else { if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); // for header if (count == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Outward total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Outward Total Amount"); } else if (j == 4) { cell.setCellValue("Total no.of txns Settled,Rescheduled"); } else if (j == 5) { cell.setCellValue("Total amount Settled,Rescheduled"); } else if (j == 6) { cell.setCellValue("Total no.of txns Unsuccessful"); } else if (j == 7) { cell.setCellValue("Total Amount Unsuccessful"); } else if (j == 8) { cell.setCellValue("Inward total no.of Txns Received"); } else if (j == 9) { cell.setCellValue("Inward total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns returned"); } else if (j == 11) { cell.setCellValue("Total amount returned"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String batchTime = null; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getBatchTime() != null) { batchTime = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getBatchTime(); } cell.setCellValue(batchTime); } else if (j == 2) { long noSent = 0; noSent = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAccepted() + ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnRejected(); cell.setCellValue(noSent); } else if (j == 3) { // double owTxnSentAmt = 0; BigDecimal owTxnSentAmt = BigDecimal.ZERO; BigDecimal owTxnSentAcceptedAmt = new BigDecimal( (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtAccepted())); BigDecimal owTxnSentRejctedAmt = new BigDecimal( (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtRejected())); owTxnSentAmt = owTxnSentAcceptedAmt.add(owTxnSentRejctedAmt); //cell.setCellValue(String.valueOf(owTxnSentAmt)); cell.setCellValue(owTxnSentAmt.setScale(2).toString()); } else if (j == 4) { long noAccept = 0; noAccept = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAccepted(); //cell.setCellValue(noAccept); cell.setCellValue(new BigDecimal(noAccept).setScale(2).toString()); } else if (j == 5) { String amtAccept = "0.00"; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtAccepted() != null) { amtAccept = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtAccepted(); } //cell.setCellValue(amtAccept); cell.setCellValue(new BigDecimal(amtAccept).setScale(2).toString()); } else if (j == 6) { long noReject = 0; noReject = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnRejected(); cell.setCellValue(noReject); } else if (j == 7) { String amtReject = null; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtRejected() != null) { amtReject = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getOwTxnAmtRejected(); } //cell.setCellValue(amtReject); cell.setCellValue(new BigDecimal(amtReject).setScale(2).toString()); } else if (j == 8) { long noReceive = 0; noReceive = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnReceived() + ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnReturned(); cell.setCellValue(noReceive); } else if (j == 9) { String amtReceive = "0.00"; String amtReturn = "0.00"; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReceived() != null) { amtReceive = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReceived(); } if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned() != null) { amtReturn = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned(); } BigDecimal totInw = new BigDecimal(amtReceive) .add(new BigDecimal(amtReturn)); //cell.setCellValue(amtReceive); cell.setCellValue(totInw.setScale(2).toString()); } else if (j == 10) { long noReturn = 0; noReturn = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnReturned(); cell.setCellValue(noReturn); } else if (j == 11) { String amtReturn = "0.00"; if (((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned() != null) { amtReturn = ((BatchwiseReconcillationDTO) exportXLS.get(count - 1)) .getIwTxnAmtReturned(); } //cell.setCellValue(amtReturn); cell.setCellValue(new BigDecimal(amtReturn).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } } } } else { if (key.equals("N04")) { row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("As Per N04"); row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Total amount Sent"); } else if (j == 4) { cell.setCellValue("Total no.of txns Accepted"); } else if (j == 5) { cell.setCellValue("Total amount Accepted"); } else if (j == 6) { cell.setCellValue("Total no.of txns Rejected"); } else if (j == 7) { cell.setCellValue("Total amount Rejected"); } else if (j == 8) { cell.setCellValue("Total no.of txns Received"); } else if (j == 9) { cell.setCellValue("Total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns Returned"); } else if (j == 11) { cell.setCellValue("Total amount Returned"); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("No Records Found"); } else { row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("As Per LMS"); row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 12; j++) { cell = row.createCell(j); if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("Batch Time"); } else if (j == 2) { cell.setCellValue("Outward total no.of txns Sent"); } else if (j == 3) { cell.setCellValue("Outward Total Amount"); } else if (j == 4) { cell.setCellValue("Total no.of txns Settled,Rescheduled"); } else if (j == 5) { cell.setCellValue("Total amount Settled,Rescheduled"); } else if (j == 6) { cell.setCellValue("Total no.of txns Unsuccessful"); } else if (j == 7) { cell.setCellValue("Total Amount Unsuccessful"); } else if (j == 8) { cell.setCellValue("Inward total no.of Txns Received"); } else if (j == 9) { cell.setCellValue("Inward total amount Received"); } else if (j == 10) { cell.setCellValue("Total no.of txns returned"); } else if (j == 11) { cell.setCellValue("Total amount returned"); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("No Records Found"); } } } sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the Batchwise aggregate report in to Excel *//*from ww w . j a va 2s. c o m*/ public void batchwiseAggregateExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; BigDecimal grandCredTotAmt = BigDecimal.ZERO; BigDecimal grandDebTotAmt = BigDecimal.ZERO; BigDecimal grandAggTotAmt = BigDecimal.ZERO; // double grandCredTotAmt = 0; // double grandDebTotAmt = 0; // double grandAggTotAmt = 0; long grandTotCredit = 0; long grandTotDebit = 0; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getAggregateMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Batchwise Aggregate Detailed", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batchwise Aggregate Detailed Report for " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate())); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch"); cell = row.createCell((short) 1); String brName = getBranchName(String.valueOf(getReportDto().getIfscId())); cell.setCellValue(brName); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch Time"); cell = row.createCell((short) 1); cell.setCellValue(getReportDto().getBatchTime()); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 4); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); roww += 1; while (it.hasNext()) { List exportXLS = new ArrayList(1); // double credTotAmt = 0; // double debTotAmt = 0; // double aggTotAmt = 0; BigDecimal credTotAmt = BigDecimal.ZERO; BigDecimal debTotAmt = BigDecimal.ZERO; BigDecimal aggTotAmt = BigDecimal.ZERO; long totCredit = 0; long totDebit = 0; String batchTime = (String) it.next(); List listRep = (List) aggregateMap.get(batchTime); for (Iterator itr = listRep.iterator(); itr.hasNext();) { BatchwiseAggregateDTO aggDTO = (BatchwiseAggregateDTO) itr.next(); exportXLS.add(aggDTO); } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Batch :"); cell = row.createCell((short) 1); cell.setCellValue(batchTime); //Only If the DTO is not empty if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 7; j++) { cell = row.createCell(j); // for header if (count == 0) { // for header if (j == 0) { cell.setCellValue("S.NO"); } else if (j == 1) { cell.setCellValue("BRANCH IFSC CODE"); } else if (j == 2) { cell.setCellValue("NO OF CREDITS"); } else if (j == 3) { cell.setCellValue("CREDIT AMOUNT(Rs)"); } else if (j == 4) { cell.setCellValue("NO OF DEBITS"); } else if (j == 5) { cell.setCellValue("DEBIT AMOUNT(Rs)"); } else if (j == 6) { cell.setCellValue("AGGREGATE AMOUNT (CREDIT-DEBIT)(Rs)"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { // String no = null; // // sno += 1; // no = String.valueOf(sno); cell.setCellValue(count); } else if (j == 1) { String ifsc = null; if (((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getIfsc() != null) { ifsc = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getIfsc(); } cell.setCellValue(ifsc); } else if (j == 2) { long noCredit = 0; noCredit = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getNoOfCredits(); totCredit += noCredit; grandTotCredit += noCredit; cell.setCellValue(String.valueOf(noCredit)); } else if (j == 3) { String credAmt = null; if (((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount() != null) { credTotAmt = credTotAmt.add( new BigDecimal(((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount()).setScale(2)); credAmt = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount(); grandCredTotAmt = grandCredTotAmt.add( new BigDecimal(((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getCreditAmount()).setScale(2)); } cell.setCellValue(new BigDecimal(credAmt).setScale(2).toString()); } else if (j == 4) { long noDebit = 0; noDebit = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getNoOfDebits(); totDebit += noDebit; grandTotDebit += noDebit; cell.setCellValue(noDebit); } else if (j == 5) { String debitAmt = null; if (((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getDebitAmount() != null) { debTotAmt = debTotAmt.add(new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getDebitAmount()) .setScale(2)); // grandDebTotAmt += Double.valueOf(((BatchwiseAggregateDTO)exportXLS // .get(count-1)).getDebitAmount()); grandDebTotAmt = grandDebTotAmt.add(new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getDebitAmount()) .setScale(2)); debitAmt = ((BatchwiseAggregateDTO) exportXLS.get(count - 1)) .getDebitAmount(); } cell.setCellValue(new BigDecimal(debitAmt).setScale(2).toString()); } else if (j == 6) { // double aggAmt = 0; // // double credit = Double.valueOf(((BatchwiseAggregateDTO)exportXLS.get(count-1)).getCreditAmount()); // double debit = Double.valueOf(((BatchwiseAggregateDTO)exportXLS.get(count-1)).getDebitAmount()); BigDecimal aggAmt = BigDecimal.ZERO; BigDecimal credit = new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getCreditAmount()); BigDecimal debit = new BigDecimal( ((BatchwiseAggregateDTO) exportXLS.get(count - 1)).getDebitAmount()); aggAmt = credit.subtract(debit).setScale(2); aggTotAmt = aggTotAmt.add(aggAmt).setScale(2); grandAggTotAmt = grandAggTotAmt.add(aggAmt).setScale(2); cell.setCellValue(String.valueOf(aggAmt)); } cell.setCellStyle(caption_style); } } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("TOTAL"); cell = row.createCell((short) 2); cell.setCellValue(totCredit); cell = row.createCell((short) 3); cell.setCellValue(String.valueOf(credTotAmt)); cell = row.createCell((short) 4); cell.setCellValue(totDebit); cell = row.createCell((short) 5); cell.setCellValue(String.valueOf(debTotAmt)); cell = row.createCell((short) 6); cell.setCellValue(String.valueOf(aggTotAmt)); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue(""); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("GRAND TOTAL"); cell = row.createCell((short) 2); cell.setCellValue(grandTotCredit); cell = row.createCell((short) 3); cell.setCellValue(String.valueOf(grandCredTotAmt)); cell = row.createCell((short) 4); cell.setCellValue(grandTotDebit); cell = row.createCell((short) 5); cell.setCellValue(String.valueOf(grandDebTotAmt)); cell = row.createCell((short) 6); cell.setCellValue(String.valueOf(grandAggTotAmt)); sheet = book.createSheet(); book.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }