List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet()
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * @param excel_name/*from w ww. jav a 2s. c o m*/ * ?Excel+?? * @param headList * ExcelHead? * @param fieldList * ExcelField? * @param dataList * Excel? * @throws Exception */ public static void createExcel(String excel_name, String[] headList, String[] fieldList, List<Map<String, Object>> dataList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.length; i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList[i]); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(objToString(dataMap.get(fieldList[i]))); } // =============================================================== } // ? FileOutputStream fOut = new FileOutputStream(excel_name); // Excel workbook.write(fOut); fOut.flush(); // ?? fOut.close(); //System.out.println("[" + excel_name + "]" + "?..."); }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * @param excel_name//from ww w . jav a 2 s.c o m * ?Excel+?? * @param headList * ExcelHead? * @param fieldList * ExcelField? * @param dataList * Excel? * @throws Exception */ public static void createExcel(String excel_name, List<String> headList, List<String> fieldList, List<Map<String, Object>> dataList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.size(); i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList.get(i)); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.size(); i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(objToString(dataMap.get(fieldList.get(i)))); } // =============================================================== } // ? FileOutputStream fOut = new FileOutputStream(excel_name); // Excel workbook.write(fOut); fOut.flush(); // ?? fOut.close(); //System.out.println("[" + excel_name + "]" + "?..."); }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * @param headList//from ww w. j a v a 2 s . c om * ExcelHead? * @param fieldList * ExcelField? * @param dataList * Excel? * @throws HSSFWorkbook */ public static HSSFWorkbook createExcel(List<String> headList, List<String> fieldList, List<Map<String, Object>> dataList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.size(); i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList.get(i)); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.size(); i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(objToString(dataMap.get(fieldList.get(i)))); } // =============================================================== } return workbook; }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * //from w ww . j a v a 2 s .c om * @param excel_name * ?Excel+?? * @param headList * ExcelHead * @param valueList * Excel? * @throws Exception */ public static void bulidExcel(String excel_name, String[] headList, List<String[]> valueList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.length; i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList[i]); } // =============================================================== for (int n = 0; n < valueList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); String[] valueArray = valueList.get(n); // =============================================================== for (int i = 0; i < valueArray.length; i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(valueArray[i]); } // =============================================================== } // ? FileOutputStream fOut = new FileOutputStream(excel_name); // Excel workbook.write(fOut); fOut.flush(); // ?? fOut.close(); //System.out.println("[" + excel_name + "]" + "?..."); }
From source file:com.mmj.app.common.file.ExcelUtils.java
License:Open Source License
/** * excelrow?IExcel.initHSSRow/*from w w w. ja va2s. c om*/ * * @param response * @param list * @param name * @param iExcel * @param headTitle * @return */ public static <T> HSSFWorkbook buildExcel(List<T> list, String name, IExcel<T> iExcel, String... headTitle) { if (list == null || list.size() == 0) { log.error("ExcelUtils buildExcel List<T>:list is null,name={}", name); return null; } if (iExcel == null || Argument.isEmptyArray(headTitle)) { return new HSSFWorkbook(); } String xlsName = name + "_" + DateViewTools.format(new Date(), "yyyy_MM_dd_HH_mm"); // HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, xlsName); for (int i = 0; i < headTitle.length; i++) { // ? sheet.setColumnWidth(i, 17 * 256); } HSSFRow rowTitle = sheet.createRow(0); HSSFCell cell = null; for (int i = 0; i < headTitle.length; i++) { // ? cell = rowTitle.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(headTitle[i]); } // row iExcel.initHSSRow(list, sheet); return workbook; }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the paymentreport in to Excel *//* w w w . j a v a2 s. co m*/ 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 ww w .jav a 2s .c om 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 a v a 2s.c o m*/ 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 w w w .j av a 2s . 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 *//*ww w. ja v a2 s. co 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); } }