Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write.

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:com.mycompany.mavenproject1.MainExecutor.java

public static void main(String[] args) throws Exception {
    FileInputStream fio = new FileInputStream(new File(pathToInput));

    //Read// www. ja v  a2 s  .  c  om
    HSSFWorkbook inputExcel = new HSSFWorkbook(fio);

    // Write 
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample sheet");

    LinkedHashSet<String> players = getAllPlayerNames(inputExcel);
    System.out.println(players.size());
    Iterator<String> playersIterator = players.iterator();

    Map<String, Object[]> data = getNewWorkbookData(inputExcel, playersIterator);
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        System.out.println("com.mycompany.mavenproject1.MainExecutor.main");
        System.out.println(rownum);
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(new File(pathToModel1));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.nkapps.billing.controllers.BankStatementController.java

@RequestMapping(value = "/print-excel", method = RequestMethod.POST)
public void printExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = null;

    String fileName;//from  w w w .jav a 2 s  . co  m
    if ("paymentManual".equals(request.getParameter("type"))) {
        String bankStatementId = request.getParameter("bankStatementId");
        BankStatement bs = bankStatementService.getBankStatementForPaymentManual(bankStatementId);
        workbook = bankStatementPrintService.printPaymentManual(bs);

        fileName = "PAYMENT_MANUAL_"
                + new SimpleDateFormat("dd.MM.yyyy HH-mm-ss").format(Calendar.getInstance().getTime());

    } else {
        SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
        Date periodStart = sdf.parse(request.getParameter("periodStart"));
        Date periodEnd = sdf.parse(request.getParameter("periodEnd"));

        switch (request.getParameter("type")) {
        case "claim": {
            List<PrintClaimPojo> listPojo = bankStatementService.getPrintClaimList(periodStart, periodEnd);
            workbook = bankStatementPrintService.printClaim(listPojo);

            break;
        }
        case "register": {
            List<PrintRegisterPojo> listPojo = bankStatementService.getPrintRegisterList(periodStart,
                    periodEnd);
            workbook = bankStatementPrintService.printRegister(listPojo);

            break;
        }
        case "claimRegister": {
            List<PrintClaimRegisterPojo> listPojo = bankStatementService.getPrintClaimRegisterList(periodStart,
                    periodEnd);
            workbook = bankStatementPrintService.printClaimRegister(listPojo);

            break;
        }
        }
        fileName = new SimpleDateFormat("dd.MM.yyyy HH-mm-ss").format(Calendar.getInstance().getTime());

    }

    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xls\"");
    response.addHeader("Cache-Control", "max-age=1, must-revalidate");
    response.addHeader("Pragma", "no-cache");

    OutputStream stream = response.getOutputStream();
    workbook.write(stream);

}

From source file:com.nkapps.billing.controllers.ReportController.java

@RequestMapping(value = "/print-click", method = RequestMethod.POST)
public void printClick(HttpServletRequest request, HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = null;

    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
    Date periodStart = sdf.parse(request.getParameter("periodStart"));
    Date periodEnd = sdf.parse(request.getParameter("periodEnd"));

    List<ReportClickListPojo> listPojo = reportService.getPrintClickList(periodStart, periodEnd);
    workbook = reportPrintService.printClick(listPojo);

    String fileName = new SimpleDateFormat("dd.MM.yyyy HH-mm-ss").format(Calendar.getInstance().getTime());
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xls\"");
    response.addHeader("Cache-Control", "max-age=1, must-revalidate");
    response.addHeader("Pragma", "no-cache");

    OutputStream stream = response.getOutputStream();
    workbook.write(stream);

}

From source file:com.nkapps.billing.controllers.ReportController.java

@RequestMapping(value = "/print-smst", method = RequestMethod.POST)
public void printSmst(HttpServletRequest request, HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = null;

    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
    Date periodStart = sdf.parse(request.getParameter("periodStart"));
    Date periodEnd = sdf.parse(request.getParameter("periodEnd"));

    List<ReportSmstListPojo> listPojo = reportService.getPrintSmstList(periodStart, periodEnd);
    workbook = reportPrintService.printSmst(listPojo);

    String fileName = new SimpleDateFormat("dd.MM.yyyy HH-mm-ss").format(Calendar.getInstance().getTime());
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xls\"");
    response.addHeader("Cache-Control", "max-age=1, must-revalidate");
    response.addHeader("Pragma", "no-cache");

    OutputStream stream = response.getOutputStream();
    workbook.write(stream);

}

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  . ja v a 2 s  .c o  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  . j  a va2  s. com*/
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
 *//* w ww.  j a va  2  s  .  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 ava 2  s.  co  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
 *//* w w  w. j  av a2  s .  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);
    }
}

From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java

License:Open Source License

/**
 * Method for Exporting the NEFT branchwise aggregate Report
 * @parameter ServletOutputStream/*ww w  .ja  va  2  s  . c o m*/
 * @return void
 */
public void batchwiseAggregateSummaryExportToExcel(ServletOutputStream out) throws Exception {

    int roww = 0;
    BigDecimal grandAggTotAmt = BigDecimal.ZERO;
    String batchTime = "";
    BatchwiseAggregateDTO aggDTO = null;
    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 Summary", 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 Summary 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) 3);
    String dateForm = currentReportPrintTime.substring(0, 11);
    String time = currentReportPrintTime.substring(11);
    cell.setCellValue(
            "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time);
    roww += 1;
    for (short j = 0; j < 6; j++) {

        cell = row.createCell(j);

        // for header
        if (j == 0) {
            cell.setCellValue("Batch Time");
        } else if (j == 1) {
            cell.setCellValue("No. of credits");
        } else if (j == 2) {
            cell.setCellValue("Credit Amount (Rs.)");
        } else if (j == 3) {
            cell.setCellValue("No. of debits");
        } else if (j == 4) {
            cell.setCellValue("debit Amount (Rs.)");
        } else if (j == 5) {
            cell.setCellValue("Aggregate Amount (Credit-Debit)(Rs.)");
        }
    }
    while (it.hasNext()) {

        batchTime = (String) it.next();
        aggDTO = (BatchwiseAggregateDTO) aggregateMap.get(batchTime);

        row = sheet.createRow(roww);
        roww += 1;
        for (short j = 0; j < 6; j++) {

            cell = row.createCell(j);
            // for header
            if (j == 0) {
                cell.setCellValue(aggDTO.getBatchTime());
            } else if (j == 1) {
                cell.setCellValue(aggDTO.getNoOfCredits());
            } else if (j == 2) {
                cell.setCellValue(new BigDecimal(aggDTO.getCreditAmount()).setScale(2).toString());
            } else if (j == 3) {
                cell.setCellValue(aggDTO.getNoOfDebits());
            } else if (j == 4) {
                cell.setCellValue(new BigDecimal(aggDTO.getDebitAmount()).setScale(2).toString());
            } else if (j == 5) {
                grandAggTotAmt = new BigDecimal(aggDTO.getCreditAmount())
                        .subtract(new BigDecimal(aggDTO.getDebitAmount()));
                cell.setCellValue(grandAggTotAmt.setScale(2).toString());
            }
        }
    }
    sheet = book.createSheet();

    book.write(out);
    out.flush();
    out.close();
}