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

Introduction

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

Prototype

@Override
public HSSFFont createFont() 

Source Link

Document

create a new Font and add it to the workbook's font table

Usage

From source file:com.smi.travel.controller.excel.checking.airticket.TicketFareSummary.java

public void genTicketFareSummaryByAgent(HSSFWorkbook wb, List ticketSumByAgent) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    TicketFareSummaryByAgentStaff dataheader = new TicketFareSummaryByAgentStaff();

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC26 = wb.createCellStyle();
    styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC26.setDataFormat(currency.getFormat("#,##0"));
    styleC26.setAlignment(styleC22.ALIGN_CENTER);

    HSSFCellStyle styleC27 = wb.createCellStyle();
    styleC27.setAlignment(styleC27.ALIGN_RIGHT);
    styleC27.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC28 = wb.createCellStyle();
    styleC28.setAlignment(styleC28.ALIGN_CENTER);
    styleC28.setDataFormat(currency.getFormat("#,##0"));

    HSSFCellStyle styleC29 = wb.createCellStyle();
    styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    HSSFCellStyle styleC30 = wb.createCellStyle();
    styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC30.setAlignment(styleC22.ALIGN_CENTER);

    if (!ticketSumByAgent.isEmpty()) {
        dataheader = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(0);

        // set Header Report (Row 1)
        HSSFCellStyle styleC1 = wb.createCellStyle();
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("List Ticket Summary By Agent");
        styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
        cell1.setCellStyle(styleC1);//from  w  w w  . j a v  a  2  s .  co m
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:G1"));

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Print By : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getPrintby());
        cell22.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
        HSSFCell cell23 = row2.createCell(4);
        cell23.setCellValue("Airline Code : ");
        cell23.setCellStyle(styleC21);
        HSSFCell cell24 = row2.createCell(5);
        cell24.setCellValue(dataheader.getAirline());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("Department : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeaddepartment());
        cell32.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
        HSSFCell cell33 = row3.createCell(4);
        cell33.setCellValue("Ticket Type : ");
        cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(5);
        cell34.setCellValue(dataheader.getHeadtickettype());
        cell34.setCellStyle(styleC22);

        // Row 4
        HSSFRow row4 = sheet.createRow(3);
        HSSFCell cell41 = row4.createCell(0);
        cell41.setCellValue("Term Pay : ");
        cell41.setCellStyle(styleC21);
        HSSFCell cell42 = row4.createCell(1);
        cell42.setCellValue(dataheader.getTermpay());
        cell42.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));
        HSSFCell cell43 = row4.createCell(4);
        cell43.setCellValue("Ticket Buy : ");
        cell43.setCellStyle(styleC21);
        HSSFCell cell44 = row4.createCell(5);
        cell44.setCellValue(dataheader.getHeadticketbuy());
        cell44.setCellStyle(styleC22);

        // Row 5
        HSSFRow row5 = sheet.createRow(4);
        HSSFCell cell51 = row5.createCell(0);
        cell51.setCellValue("Airline : ");
        cell51.setCellStyle(styleC21);
        HSSFCell cell52 = row5.createCell(1);
        cell52.setCellValue(dataheader.getHeaderairline());
        cell52.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));
        HSSFCell cell53 = row5.createCell(4);
        cell53.setCellValue("Sale Staff : ");
        cell53.setCellStyle(styleC21);
        HSSFCell cell54 = row5.createCell(5);
        cell54.setCellValue(dataheader.getHeadsale());
        cell54.setCellStyle(styleC22);

        // Row 6
        HSSFRow row6 = sheet.createRow(5);
        HSSFCell cell61 = row6.createCell(0);
        cell61.setCellValue("Issue Date : ");
        cell61.setCellStyle(styleC21);
        sheet.addMergedRegion(CellRangeAddress.valueOf("A6:D6"));
        HSSFCell cell62 = row6.createCell(4);
        if (!"".equalsIgnoreCase(dataheader.getIssuefrom())) {
            cell62.setCellValue(dataheader.getIssuefrom());
            cell62.setCellStyle(styleC22);
        }
        HSSFCell cell63 = row6.createCell(5);
        if (!"".equalsIgnoreCase(dataheader.getIssueto())) {
            cell63.setCellValue("to  " + dataheader.getIssueto());
            cell63.setCellStyle(styleC22);
        }

        // Row 7
        HSSFRow row7 = sheet.createRow(6);
        HSSFCell cell71 = row7.createCell(0);
        cell71.setCellValue("Invoice Date : ");
        cell71.setCellStyle(styleC21);
        sheet.addMergedRegion(CellRangeAddress.valueOf("A7:D7"));
        HSSFCell cell72 = row7.createCell(4);
        if (!"".equalsIgnoreCase(dataheader.getInvdatefrom())) {
            cell72.setCellValue(dataheader.getInvdatefrom());
            cell72.setCellStyle(styleC22);
        }
        HSSFCell cell73 = row7.createCell(5);
        if (!"".equalsIgnoreCase(dataheader.getInvdateto())) {
            cell73.setCellValue("to  " + dataheader.getInvdateto());
            cell73.setCellStyle(styleC22);
        }

        // Row 8
        HSSFRow row8 = sheet.createRow(7);
        HSSFCell cell81 = row8.createCell(0);
        cell81.setCellValue("Print on : ");
        cell81.setCellStyle(styleC21);
        sheet.addMergedRegion(CellRangeAddress.valueOf("A8:D8"));
        HSSFCell cell82 = row8.createCell(4);
        cell82.setCellValue(dataheader.getPrinton());
        cell82.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("E8:F8"));
    }

    // Header Table style
    HSSFCellStyle styleC3 = wb.createCellStyle();
    styleC3.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3.setAlignment(styleC3.ALIGN_CENTER);
    styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    // Detail of Table
    String temp = "";
    int count = 9;
    int ktemp = 12;
    //Total Outbound
    int totalPaxOut = 0;
    BigDecimal totalInvAmountOut = new BigDecimal("0.00");
    BigDecimal totalTiccomOut = new BigDecimal("0.00");
    BigDecimal totalSalePriceOut = new BigDecimal("0.00");
    BigDecimal totalAgentCommOut = new BigDecimal("0.00");
    BigDecimal totalProfitOut = new BigDecimal("0.00");
    BigDecimal totalCostOut = new BigDecimal("0.00");
    //Total Wendy
    int totalPaxWendy = 0;
    BigDecimal totalInvAmountWendy = new BigDecimal("0.00");
    BigDecimal totalTiccomWendy = new BigDecimal("0.00");
    BigDecimal totalSalePriceWendy = new BigDecimal("0.00");
    BigDecimal totalAgentCommWendy = new BigDecimal("0.00");
    BigDecimal totalProfitWendy = new BigDecimal("0.00");
    BigDecimal totalCostWendy = new BigDecimal("0.00");
    for (int i = 0; i < ticketSumByAgent.size(); i++) {
        TicketFareSummaryByAgentStaff data = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(i);
        if (!temp.equalsIgnoreCase(data.getAgentname())) {
            if (!"".equalsIgnoreCase(temp)) {
                HSSFRow row = sheet.createRow(count + i);
                String totalPax = "SUM(C" + ktemp + ":C" + (count + i) + ")";
                String totalInvAmount = "SUM(D" + ktemp + ":D" + (count + i) + ")";
                String totalSalePrice = "SUM(E" + ktemp + ":E" + (count + i) + ")";
                String totalCost = "SUM(F" + ktemp + ":F" + (count + i) + ")";
                String totalTiccom = "SUM(G" + ktemp + ":G" + (count + i) + ")";
                String totalAgentComm = "SUM(H" + ktemp + ":H" + (count + i) + ")";
                String totalProfit = "SUM(I" + ktemp + ":I" + (count + i) + ")";
                // Set align Text
                HSSFCell cellTotal0 = row.createCell(0);
                cellTotal0.setCellStyle(styleC29);
                HSSFCell cellTotal = row.createCell(1);
                cellTotal.setCellValue("Total");
                cellTotal.setCellStyle(styleC30);
                HSSFCell cellTotal02 = row.createCell(2);
                cellTotal02.setCellFormula(totalPax);
                cellTotal02.setCellStyle(styleC26);
                HSSFCell cellTotal03 = row.createCell(3);
                cellTotal03.setCellFormula(totalInvAmount);
                cellTotal03.setCellStyle(styleC25);
                HSSFCell cellTotal04 = row.createCell(4);
                cellTotal04.setCellFormula(totalSalePrice);
                cellTotal04.setCellStyle(styleC25);
                HSSFCell cellTotal05 = row.createCell(5);
                cellTotal05.setCellFormula(totalCost);
                cellTotal05.setCellStyle(styleC25);
                HSSFCell cellTotal06 = row.createCell(6);
                cellTotal06.setCellFormula(totalTiccom);
                cellTotal06.setCellStyle(styleC25);
                HSSFCell cellTotal07 = row.createCell(7);
                cellTotal07.setCellFormula(totalAgentComm);
                cellTotal07.setCellStyle(styleC25);
                HSSFCell cellTotal08 = row.createCell(8);
                cellTotal08.setCellFormula(totalProfit);
                cellTotal08.setCellStyle(styleC25);
                count = count + 2;
                ktemp = count + 3 + i;
            }

            int counts = count + i;
            int countss = count + 1 + i;

            // Row Agent Name
            HSSFRow row008 = sheet.createRow(counts);
            HSSFCell cell0081 = row008.createCell(1);
            cell0081.setCellValue("Agent name ");
            cell0081.setCellStyle(styleC23);
            HSSFCell cell0082 = row008.createCell(2);
            cell0082.setCellValue(data.getAgentname());
            cell0082.setCellStyle(styleC22);
            temp = data.getAgentname();

            // Header Table
            HSSFRow row09 = sheet.createRow(countss);
            HSSFCell cell091 = row09.createCell(0);
            cell091.setCellValue("Staff");
            cell091.setCellStyle(styleC3);
            sheet.autoSizeColumn(0);
            HSSFCell cell092 = row09.createCell(1);
            cell092.setCellValue("Department");
            cell092.setCellStyle(styleC3);
            sheet.autoSizeColumn(1);
            HSSFCell cell093 = row09.createCell(2);
            cell093.setCellValue("Pax");
            sheet.autoSizeColumn(2);
            cell093.setCellStyle(styleC3);
            HSSFCell cell094 = row09.createCell(3);
            cell094.setCellValue("Invoice Amount");
            cell094.setCellStyle(styleC3);
            sheet.autoSizeColumn(3);
            HSSFCell cell095 = row09.createCell(4);
            cell095.setCellValue("Sales Price");
            cell095.setCellStyle(styleC3);
            sheet.autoSizeColumn(4);
            HSSFCell cell096 = row09.createCell(5);
            cell096.setCellValue("Cost");
            cell096.setCellStyle(styleC3);
            sheet.autoSizeColumn(5);
            HSSFCell cell097 = row09.createCell(6);
            cell097.setCellValue("Ticket Comm");
            cell097.setCellStyle(styleC3);
            sheet.autoSizeColumn(6);
            HSSFCell cell098 = row09.createCell(7);
            cell098.setCellValue("Agent Comm");
            cell098.setCellStyle(styleC3);
            sheet.autoSizeColumn(7);
            HSSFCell cell099 = row09.createCell(8);
            cell099.setCellValue("Profit");
            cell099.setCellStyle(styleC3);
            sheet.autoSizeColumn(8);
            count = count + 2;
        }
        //set data 
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell celldata01 = row.createCell(0);
        celldata01.setCellValue(data.getOwner());
        celldata01.setCellStyle(styleC29);
        HSSFCell celldata02 = row.createCell(1);
        celldata02.setCellValue(data.getDepartment());
        celldata02.setCellStyle(styleC29);
        HSSFCell celldata03 = row.createCell(2);
        celldata03.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                : new BigDecimal(data.getPax()).doubleValue());
        celldata03.setCellStyle(styleC26);
        HSSFCell celldata04 = row.createCell(3);
        celldata04.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInvamount())) ? 0
                : new BigDecimal(data.getInvamount()).doubleValue());
        celldata04.setCellStyle(styleC25);
        HSSFCell celldata05 = row.createCell(4);
        celldata05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSaleprice())) ? 0
                : new BigDecimal(data.getSaleprice()).doubleValue());
        celldata05.setCellStyle(styleC25);
        HSSFCell celldata06 = row.createCell(5);
        celldata06.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCost())) ? 0
                : new BigDecimal(data.getCost()).doubleValue());
        celldata06.setCellStyle(styleC25);
        HSSFCell celldata07 = row.createCell(6);
        celldata07.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTiccom())) ? 0
                : new BigDecimal(data.getTiccom()).doubleValue());
        celldata07.setCellStyle(styleC25);
        HSSFCell celldata08 = row.createCell(7);
        celldata08.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAgentcom())) ? 0
                : new BigDecimal(data.getAgentcom()).doubleValue());
        celldata08.setCellStyle(styleC25);
        HSSFCell celldata09 = row.createCell(8);
        celldata09.setCellValue("".equalsIgnoreCase(String.valueOf(data.getProfit())) ? 0
                : new BigDecimal(data.getProfit()).doubleValue());
        celldata09.setCellStyle(styleC25);

        if ("outbound".equalsIgnoreCase(data.getDepartment())) {
            TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(i);
            int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0);
            BigDecimal invamount = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00");
            BigDecimal ticcom = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00");
            BigDecimal saleprice = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00");
            BigDecimal agentcomm = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00");
            BigDecimal profit = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00");
            BigDecimal cost = new BigDecimal(!"".equalsIgnoreCase(sum.getCost()) ? sum.getCost() : "0.00");
            totalPaxOut = totalPaxOut + pax;
            totalInvAmountOut = totalInvAmountOut.add(invamount);
            totalTiccomOut = totalTiccomOut.add(ticcom);
            totalSalePriceOut = totalSalePriceOut.add(saleprice);
            totalAgentCommOut = totalAgentCommOut.add(agentcomm);
            totalProfitOut = totalProfitOut.add(profit);
            totalCostOut = totalCostOut.add(cost);
        }
        if ("wendy".equalsIgnoreCase(data.getDepartment())) {
            TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(i);
            int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0);
            BigDecimal invamount = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00");
            BigDecimal ticcom = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00");
            BigDecimal saleprice = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00");
            BigDecimal agentcomm = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00");
            BigDecimal profit = new BigDecimal(
                    !"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00");
            BigDecimal cost = new BigDecimal(!"".equalsIgnoreCase(sum.getCost()) ? sum.getCost() : "0.00");
            totalPaxWendy = totalPaxWendy + pax;
            totalInvAmountWendy = totalInvAmountWendy.add(invamount);
            totalTiccomWendy = totalTiccomWendy.add(ticcom);
            totalSalePriceWendy = totalSalePriceWendy.add(saleprice);
            totalAgentCommWendy = totalAgentCommWendy.add(agentcomm);
            totalProfitWendy = totalProfitWendy.add(profit);
            totalCostWendy = totalCostWendy.add(cost);
        }

        // set total last row
        if (i == (ticketSumByAgent.size() - 1)) {
            HSSFRow rows = sheet.createRow(count + 1 + i);

            String totalPax = "SUM(C" + ktemp + ":C" + (count + i + 1) + ")";
            String totalInvAmount = "SUM(D" + ktemp + ":D" + (count + i + 1) + ")";
            String totalSalePrice = "SUM(E" + ktemp + ":E" + (count + i + 1) + ")";
            String totalCost = "SUM(F" + ktemp + ":F" + (count + i + 1) + ")";
            String totalTiccom = "SUM(G" + ktemp + ":G" + (count + i + 1) + ")";
            String totalAgentComm = "SUM(H" + ktemp + ":H" + (count + i + 1) + ")";
            String totalProfit = "SUM(I" + ktemp + ":I" + (count + i + 1) + ")";

            HSSFCell celldatas0 = rows.createCell(0);
            celldatas0.setCellStyle(styleC29);
            HSSFCell celldatas01 = rows.createCell(1);
            celldatas01.setCellValue("Total");
            celldatas01.setCellStyle(styleC30);
            HSSFCell celldatas02 = rows.createCell(2);
            celldatas02.setCellFormula(totalPax);
            celldatas02.setCellStyle(styleC26);
            HSSFCell celldatas03 = rows.createCell(3);
            celldatas03.setCellFormula(totalInvAmount);
            celldatas03.setCellStyle(styleC25);
            HSSFCell celldatas04 = rows.createCell(4);
            celldatas04.setCellFormula(totalSalePrice);
            celldatas04.setCellStyle(styleC25);
            HSSFCell celldatas05 = rows.createCell(5);
            celldatas05.setCellFormula(totalCost);
            celldatas05.setCellStyle(styleC25);
            HSSFCell celldatas06 = rows.createCell(6);
            celldatas06.setCellFormula(totalTiccom);
            celldatas06.setCellStyle(styleC25);
            HSSFCell celldatas07 = rows.createCell(7);
            celldatas07.setCellFormula(totalAgentComm);
            celldatas07.setCellStyle(styleC25);
            HSSFCell celldatas08 = rows.createCell(8);
            celldatas08.setCellFormula(totalProfit);
            celldatas08.setCellStyle(styleC25);
            count = count + 3;

            rows = sheet.createRow(count + i);
            HSSFCell celldataOut01 = rows.createCell(1);
            celldataOut01.setCellValue("Summary Outbound");
            celldataOut01.setCellStyle(styleC22);
            HSSFCell celldataOut02 = rows.createCell(2);
            celldataOut02.setCellValue("".equalsIgnoreCase(String.valueOf(totalPaxOut)) ? 0
                    : new BigDecimal(totalPaxOut).doubleValue());
            celldataOut02.setCellStyle(styleC28);
            HSSFCell celldataOut03 = rows.createCell(3);
            celldataOut03.setCellValue("".equalsIgnoreCase(String.valueOf(totalInvAmountOut)) ? 0
                    : totalInvAmountOut.doubleValue());
            celldataOut03.setCellStyle(styleC27);
            HSSFCell celldataOut04 = rows.createCell(4);
            celldataOut04.setCellValue("".equalsIgnoreCase(String.valueOf(totalSalePriceOut)) ? 0
                    : totalSalePriceOut.doubleValue());
            celldataOut04.setCellStyle(styleC27);
            HSSFCell celldataOut05 = rows.createCell(5);
            celldataOut05.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(totalCostOut)) ? 0 : totalCostOut.doubleValue());
            celldataOut05.setCellStyle(styleC27);
            HSSFCell celldataOut06 = rows.createCell(6);
            celldataOut06.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(totalTiccomOut)) ? 0 : totalTiccomOut.doubleValue());
            celldataOut06.setCellStyle(styleC27);
            HSSFCell celldataOut07 = rows.createCell(7);
            celldataOut07.setCellValue("".equalsIgnoreCase(String.valueOf(totalAgentCommOut)) ? 0
                    : totalAgentCommOut.doubleValue());
            celldataOut07.setCellStyle(styleC27);
            HSSFCell celldataOut08 = rows.createCell(8);
            celldataOut08.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(totalProfitOut)) ? 0 : totalProfitOut.doubleValue());
            celldataOut08.setCellStyle(styleC27);

            rows = sheet.createRow(count + 1 + i);
            HSSFCell celldataWen01 = rows.createCell(1);
            celldataWen01.setCellValue("Summary Wendy");
            celldataWen01.setCellStyle(styleC22);
            HSSFCell celldataWen02 = rows.createCell(2);
            celldataWen02.setCellValue("".equalsIgnoreCase(String.valueOf(totalPaxWendy)) ? 0
                    : new BigDecimal(totalPaxWendy).doubleValue());
            celldataWen02.setCellStyle(styleC28);
            HSSFCell celldataWen03 = rows.createCell(3);
            celldataWen03.setCellValue("".equalsIgnoreCase(String.valueOf(totalInvAmountWendy)) ? 0
                    : totalInvAmountWendy.doubleValue());
            celldataWen03.setCellStyle(styleC27);
            HSSFCell celldataWen04 = rows.createCell(4);
            celldataWen04.setCellValue("".equalsIgnoreCase(String.valueOf(totalSalePriceWendy)) ? 0
                    : totalSalePriceWendy.doubleValue());
            celldataWen04.setCellStyle(styleC27);
            HSSFCell celldataWen05 = rows.createCell(5);
            celldataWen05.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(totalCostWendy)) ? 0 : totalCostWendy.doubleValue());
            celldataWen05.setCellStyle(styleC27);
            HSSFCell celldataWen06 = rows.createCell(6);
            celldataWen06.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(totalTiccomWendy)) ? 0 : totalTiccomWendy.doubleValue());
            celldataWen06.setCellStyle(styleC27);
            HSSFCell celldataWen07 = rows.createCell(7);
            celldataWen07.setCellValue("".equalsIgnoreCase(String.valueOf(totalAgentCommWendy)) ? 0
                    : totalAgentCommWendy.doubleValue());
            celldataWen07.setCellStyle(styleC27);
            HSSFCell celldataWen08 = rows.createCell(8);
            celldataWen08.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(totalProfitWendy)) ? 0 : totalProfitWendy.doubleValue());
            celldataWen08.setCellStyle(styleC27);

            rows = sheet.createRow(count + 2 + i);
            String totalPaxWI = "SUM(C" + (count + i + 1) + ":C" + (count + i + 2) + ")";
            String totalInvAmountWI = "SUM(D" + (count + i + 1) + ":D" + (count + i + 2) + ")";
            String totalSalePriceWI = "SUM(E" + (count + i + 1) + ":E" + (count + i + 2) + ")";
            String totalCostWI = "SUM(F" + (count + i + 1) + ":F" + (count + i + 2) + ")";
            String totalTiccomWI = "SUM(G" + (count + i + 1) + ":G" + (count + i + 2) + ")";
            String totalAgentCommWI = "SUM(H" + (count + i + 1) + ":H" + (count + i + 2) + ")";
            String totalProfitWI = "SUM(I" + (count + i + 1) + ":I" + (count + i + 2) + ")";

            HSSFCell celldataWI01 = rows.createCell(1);
            celldataWI01.setCellValue("Total");
            celldataWI01.setCellStyle(styleC22);
            HSSFCell celldataWI02 = rows.createCell(2);
            celldataWI02.setCellFormula(totalPaxWI);
            celldataWI02.setCellStyle(styleC28);
            HSSFCell celldataWI03 = rows.createCell(3);
            celldataWI03.setCellFormula(totalInvAmountWI);
            celldataWI03.setCellStyle(styleC27);
            HSSFCell celldataWI04 = rows.createCell(4);
            celldataWI04.setCellFormula(totalSalePriceWI);
            celldataWI04.setCellStyle(styleC27);
            HSSFCell celldataWI05 = rows.createCell(5);
            celldataWI05.setCellFormula(totalCostWI);
            celldataWI05.setCellStyle(styleC27);
            HSSFCell celldataWI06 = rows.createCell(6);
            celldataWI06.setCellFormula(totalTiccomWI);
            celldataWI06.setCellStyle(styleC27);
            HSSFCell celldataWI07 = rows.createCell(7);
            celldataWI07.setCellFormula(totalAgentCommWI);
            celldataWI07.setCellStyle(styleC27);
            HSSFCell celldataWI08 = rows.createCell(8);
            celldataWI08.setCellFormula(totalProfitWI);
            celldataWI08.setCellStyle(styleC27);

        }
    }
    for (int j = 0; j < 8; j++) {
        sheet.autoSizeColumn(j);
    }
}

---

From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java

private void getOverdueSummary(HSSFWorkbook wb, List refundTicket) {
    String sheetName = "overdueSummary";// name of sheet
    HSSFSheet sheet1 = wb.createSheet(sheetName);
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleAlignRightBorderAllHeaderTable = wb.createCellStyle();
    styleAlignRightBorderAllHeaderTable.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleAlignRightBorderAllHeaderTable.setAlignment(styleAlignRightBorderAllHeaderTable.ALIGN_CENTER);
    styleAlignRightBorderAllHeaderTable.setBorderTop(styleAlignRightBorderAllHeaderTable.BORDER_THIN);
    styleAlignRightBorderAllHeaderTable.setBorderBottom(styleAlignRightBorderAllHeaderTable.BORDER_THIN);
    styleAlignRightBorderAllHeaderTable.setBorderRight(styleAlignRightBorderAllHeaderTable.BORDER_THIN);
    styleAlignRightBorderAllHeaderTable.setBorderLeft(styleAlignRightBorderAllHeaderTable.BORDER_THIN);
    HSSFCellStyle styleAlignRightBorderAllNumber = wb.createCellStyle();
    styleAlignRightBorderAllNumber.setAlignment(styleAlignRightBorderAllNumber.ALIGN_RIGHT);
    styleAlignRightBorderAllNumber.setDataFormat(currency.getFormat("#,##0.00"));
    styleAlignRightBorderAllNumber.setBorderTop(styleAlignRightBorderAllNumber.BORDER_THIN);
    styleAlignRightBorderAllNumber.setBorderBottom(styleAlignRightBorderAllNumber.BORDER_THIN);
    styleAlignRightBorderAllNumber.setBorderRight(styleAlignRightBorderAllNumber.BORDER_THIN);
    HSSFCellStyle styleAlignRightBorderAll = wb.createCellStyle();
    styleAlignRightBorderAll.setAlignment(styleAlignRightBorderAll.ALIGN_LEFT);
    styleAlignRightBorderAll.setBorderTop(styleAlignRightBorderAll.BORDER_THIN);
    styleAlignRightBorderAll.setBorderBottom(styleAlignRightBorderAll.BORDER_THIN);
    styleAlignRightBorderAll.setBorderRight(styleAlignRightBorderAll.BORDER_THIN);
    styleAlignRightBorderAll.setBorderLeft(styleAlignRightBorderAll.BORDER_THIN);
    HSSFCellStyle styleNumber = wb.createCellStyle();
    styleNumber.setAlignment(styleNumber.ALIGN_RIGHT);
    styleNumber.setDataFormat(currency.getFormat("#,##0.00"));
    HSSFCellStyle styleNumberBorderRight = wb.createCellStyle();
    styleNumberBorderRight.setAlignment(styleNumberBorderRight.ALIGN_RIGHT);
    styleNumberBorderRight.setDataFormat(currency.getFormat("#,##0.00"));
    styleNumberBorderRight.setBorderRight(styleNumberBorderRight.BORDER_THIN);
    HSSFCellStyle styleBorderBottom = wb.createCellStyle(); //use
    styleBorderBottom.setBorderBottom(styleBorderBottom.BORDER_THIN);
    styleAlignRightBorderAllNumber.setBorderLeft(styleAlignRightBorderAllNumber.BORDER_THIN);
    HSSFCellStyle styleAlignRightBorderBottomRight = wb.createCellStyle();//use
    styleAlignRightBorderBottomRight.setAlignment(styleAlignRightBorderBottomRight.ALIGN_LEFT);
    styleAlignRightBorderBottomRight.setBorderBottom(styleAlignRightBorderBottomRight.BORDER_THIN);
    styleAlignRightBorderBottomRight.setBorderRight(styleAlignRightBorderBottomRight.BORDER_THIN);
    HSSFCellStyle styleAlignRightBorderAllColor = wb.createCellStyle();
    styleAlignRightBorderAllColor.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleAlignRightBorderAllColor.setAlignment(styleAlignRightBorderAllColor.ALIGN_LEFT);
    styleAlignRightBorderAllColor.setBorderTop(styleAlignRightBorderAllColor.BORDER_THIN);
    styleAlignRightBorderAllColor.setBorderBottom(styleAlignRightBorderAllColor.BORDER_THIN);
    styleAlignRightBorderAllColor.setBorderRight(styleAlignRightBorderAllColor.BORDER_THIN);
    styleAlignRightBorderAllColor.setBorderLeft(styleAlignRightBorderAllColor.BORDER_THIN);
    styleAlignRightBorderAllColor.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());

    HSSFCellStyle styleAlignRightBorderAllDetailTable = wb.createCellStyle();
    styleAlignRightBorderAllDetailTable.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont()));
    styleAlignRightBorderAllDetailTable.setAlignment(styleAlignRightBorderAllDetailTable.ALIGN_LEFT);
    styleAlignRightBorderAllDetailTable.setBorderTop(styleAlignRightBorderAllDetailTable.BORDER_THIN);
    styleAlignRightBorderAllDetailTable.setBorderBottom(styleAlignRightBorderAllDetailTable.BORDER_THIN);
    styleAlignRightBorderAllDetailTable.setBorderRight(styleAlignRightBorderAllDetailTable.BORDER_THIN);
    styleAlignRightBorderAllDetailTable.setBorderLeft(styleAlignRightBorderAllDetailTable.BORDER_THIN);

    HSSFCellStyle total = wb.createCellStyle();
    total.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont()));
    total.setAlignment(total.ALIGN_CENTER);
    total.setBorderTop(total.BORDER_THIN);
    total.setBorderBottom(total.BORDER_THIN);
    total.setBorderRight(total.BORDER_THIN);
    total.setBorderLeft(total.BORDER_THIN);

    // set Header Report (Row 1)
    HSSFCellStyle styleC11 = wb.createCellStyle();
    HSSFRow row01 = sheet1.createRow(0);
    HSSFCell cell01 = row01.createCell(0);
    cell01.setCellValue("Overdue Summary");
    styleC11.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cell01.setCellStyle(styleC11);/*w w w  .j  a v  a2s  . co m*/
    sheet1.addMergedRegion(CellRangeAddress.valueOf("A1:G1"));

    List<OverdueSummartExcel> listOver = new ArrayList<OverdueSummartExcel>();
    if (refundTicket != null && refundTicket.size() != 0) {
        listOver = refundTicket;
    } else {
        listOver = null;
    }
    OverdueSummartExcel over = new OverdueSummartExcel();
    if ((refundTicket != null) && (refundTicket.size() != 0)) {
        over = (OverdueSummartExcel) refundTicket.get(0);
    }
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Row 2
    HSSFRow row02 = sheet1.createRow(1);
    HSSFCell cell021 = row02.createCell(0);
    cell021.setCellValue("Client : ");
    cell021.setCellStyle(styleC21);
    HSSFCell cell022 = row02.createCell(1);
    cell022.setCellValue(over.getClientname_page());
    cell022.setCellStyle(styleC22);
    sheet1.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
    HSSFCell cell023 = row02.createCell(4);
    cell023.setCellValue("Agent : ");
    cell023.setCellStyle(styleC21);
    HSSFCell cell024 = row02.createCell(5);
    cell024.setCellValue(over.getStaffname_page());
    cell024.setCellStyle(styleC22);

    // Row 3
    HSSFRow row03 = sheet1.createRow(2);
    HSSFCell cell031 = row03.createCell(0);
    cell031.setCellValue("Date : ");
    cell031.setCellStyle(styleC21);
    HSSFCell cell032 = row03.createCell(1);
    cell032.setCellValue(over.getFrom_page());
    cell032.setCellStyle(styleC22);
    sheet1.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
    HSSFCell cell033 = row03.createCell(4);
    cell033.setCellValue("Vat Type : ");
    cell033.setCellStyle(styleC21);
    HSSFCell cell034 = row03.createCell(5);
    cell034.setCellValue(over.getVattype_page());
    cell034.setCellStyle(styleC22);

    // Row 4
    HSSFRow row04 = sheet1.createRow(3);
    HSSFCell cell041 = row04.createCell(0);
    cell041.setCellValue("Department : ");
    cell041.setCellStyle(styleC21);
    HSSFCell cell042 = row04.createCell(1);
    cell042.setCellValue(over.getDepart_page());
    cell042.setCellStyle(styleC22);
    sheet1.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));
    HSSFCell cell043 = row04.createCell(4);
    cell043.setCellValue("View : ");
    cell043.setCellStyle(styleC21);
    HSSFCell cell044 = row04.createCell(5);
    cell044.setCellValue(over.getView_page());
    cell044.setCellStyle(styleC22);

    // Row 5
    HSSFRow row05 = sheet1.createRow(4);
    HSSFCell cell051 = row05.createCell(0);
    cell051.setCellValue("Group : ");
    cell051.setCellStyle(styleC21);
    sheet1.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));
    HSSFCell cell052 = row05.createCell(1);
    cell052.setCellValue(over.getGroup_page());
    cell052.setCellStyle(styleC22);
    HSSFCell cell053 = row05.createCell(4);
    cell053.setCellValue("Print By : ");
    cell053.setCellStyle(styleC21);
    HSSFCell cell054 = row05.createCell(5);
    cell054.setCellValue(over.getPrintby_page());
    cell054.setCellStyle(styleC22);

    // Row 3
    HSSFRow row06 = sheet1.createRow(5);
    HSSFCell cell061 = row06.createCell(0);
    cell061.setCellValue("Print Date : ");
    cell061.setCellStyle(styleC21);
    HSSFCell cell062 = row06.createCell(1);
    cell062.setCellValue(over.getPrintdate_page());
    cell062.setCellStyle(styleC22);
    sheet1.addMergedRegion(CellRangeAddress.valueOf("B6:D6"));

    // Header Table
    HSSFRow row6 = sheet1.createRow(8);
    HSSFCell cell61 = row6.createCell(0);
    cell61.setCellValue("Invoice No.");
    cell61.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(0);
    HSSFCell cell62 = row6.createCell(1);
    cell62.setCellValue("Date");
    cell62.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(1);
    HSSFCell cell63 = row6.createCell(2);
    cell63.setCellValue("Detail");
    sheet1.autoSizeColumn(2);
    cell63.setCellStyle(styleAlignRightBorderAllHeaderTable);
    HSSFCell cell64 = row6.createCell(3);
    cell64.setCellValue("Baht");
    cell64.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(3);
    HSSFCell cell65 = row6.createCell(4);
    cell65.setCellValue("JPY");
    cell65.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(4);
    HSSFCell cell66 = row6.createCell(5);
    cell66.setCellValue("USD");
    cell66.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(5);
    HSSFCell cell67 = row6.createCell(6);
    cell67.setCellValue("Rec No");
    cell67.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(6);
    HSSFCell cell68 = row6.createCell(7);
    cell68.setCellValue("Rec Amt");
    cell68.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(7);
    HSSFCell cell69 = row6.createCell(8);
    cell69.setCellValue("Department");
    cell69.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(8);
    HSSFCell cell70 = row6.createCell(9);
    cell70.setCellValue("Credit");
    cell70.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(9);
    HSSFCell cell71 = row6.createCell(10);
    cell71.setCellValue("Ref No");
    cell71.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(10);
    HSSFCell cell72 = row6.createCell(11);
    cell72.setCellValue("Due Date");
    cell72.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(11);
    HSSFCell cell73 = row6.createCell(12);
    cell73.setCellValue("Overdue Status");
    cell73.setCellStyle(styleAlignRightBorderAllHeaderTable);
    sheet1.autoSizeColumn(12);

    int start = 11;
    int end = 0;
    int num = 0;
    int count = 9;
    String temp = "";
    //        String sumThbAll = "";
    //        String sumJpyAll = "";
    //        String sumUsdAll = "";
    //        String sumRecAmtAll = "";
    BigDecimal bahtTotalAll = new BigDecimal(BigInteger.ZERO);
    BigDecimal jpyTotalAll = new BigDecimal(BigInteger.ZERO);
    BigDecimal usdTotalAll = new BigDecimal(BigInteger.ZERO);
    BigDecimal recamtTotalAll = new BigDecimal(BigInteger.ZERO);

    if (listOver != null && listOver.size() != 0) {
        for (int r = 0; r < listOver.size(); r++) {
            if (r != 0) {
                if ("Agent".equals(listOver.get(r).getGroup())) {
                    bahtTotalAll = bahtTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getBath())));
                    jpyTotalAll = jpyTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getJpy())));
                    usdTotalAll = usdTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getUsd())));
                    recamtTotalAll = recamtTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getRecamt())));

                    if (temp.equals(listOver.get(r).getInvto())) { // equal type   
                        if (r != (listOver.size() - 1)) { // check not last row
                            HSSFRow row = sheet1.createRow(r + count);
                            createCell(row, listOver, r, styleAlignRightBorderAllNumber,
                                    styleAlignRightBorderAll);
                            sheet1.autoSizeColumn(13);
                        } else {
                            HSSFRow row = sheet1.createRow(r + count);
                            createCell(row, listOver, r, styleAlignRightBorderAllNumber,
                                    styleAlignRightBorderAll);
                            sheet1.autoSizeColumn(13);
                        }
                    } else { // not equal type
                             // Start New Row (Group)
                        String totalthb = "SUM(D" + start + ":D" + (r + count) + ")";
                        String totaljpy = "SUM(E" + start + ":E" + (r + count) + ")";
                        String totalusd = "SUM(F" + start + ":F" + (r + count) + ")";
                        String totalrecamt = "SUM(H" + start + ":H" + (r + count) + ")";
                        //                                sumThbAll += ",D"+(count+r+1);
                        //                                sumJpyAll += ",E"+(count+r+1);
                        //                                sumUsdAll += ",F"+(count+r+1);
                        //                                sumRecAmtAll += ",H"+(count+r+1);
                        start = count + r + 3;
                        HSSFRow row00 = sheet1.createRow(r + count);
                        HSSFCell cell00 = row00.createCell(0);
                        cell00.setCellValue("");
                        cell00.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell001 = row00.createCell(1);
                        cell001.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell002 = row00.createCell(2);
                        cell002.setCellStyle(styleAlignRightBorderAllDetailTable);
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("A" + (count + r + 1) + ":C" + (count + r + 1) + ""));
                        HSSFCell cell003 = row00.createCell(3);
                        cell003.setCellFormula(totalthb);
                        cell003.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell004 = row00.createCell(4);
                        cell004.setCellFormula(totaljpy);
                        cell004.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell005 = row00.createCell(5);
                        cell005.setCellFormula(totalusd);
                        cell005.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell006 = row00.createCell(6);
                        cell006.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell007 = row00.createCell(7);
                        cell007.setCellFormula(totalrecamt);
                        cell007.setCellStyle(styleAlignRightBorderAllNumber);
                        for (int k = 8; k < 13; k++) {
                            HSSFCell cell008 = row00.createCell(k);
                            cell008.setCellStyle(styleAlignRightBorderAllNumber);
                        }
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("I" + (count + r + 1) + ":M" + (count + r + 1) + ""));
                        HSSFRow row0 = sheet1.createRow(r + count + 1);
                        HSSFCell cell = row0.createCell(0);
                        cell.setCellValue(listOver.get(r).getInvto());
                        cell.setCellStyle(styleAlignRightBorderAllDetailTable);
                        String add = "A" + (r + count + 2) + ":M" + (r + count + 2) + "";
                        sheet1.addMergedRegion(CellRangeAddress.valueOf(add));
                        row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor);
                        HSSFRow row122 = sheet1.createRow(r + count + 2);
                        createCell(row122, listOver, r, styleAlignRightBorderAllNumber,
                                styleAlignRightBorderAll);
                        sheet1.autoSizeColumn(13);
                        count = count + 2;
                    }
                    temp = listOver.get(r).getInvto();
                    if (r == (listOver.size() - 1)) {
                        String totalthb = "SUM(D" + start + ":D" + (count + r + 1) + ")";
                        String totaljpy = "SUM(E" + start + ":E" + (count + r + 1) + ")";
                        String totalusd = "SUM(F" + start + ":F" + (count + r + 1) + ")";
                        String totalrecamt = "SUM(H" + start + ":H" + (count + r + 1) + ")";
                        //                            sumThbAll += ",D"+(count+r+2);
                        //                            sumJpyAll += ",E"+(count+r+2);
                        //                            sumUsdAll += ",F"+(count+r+2);
                        //                            sumRecAmtAll += ",H"+(count+r+2);
                        HSSFRow row00 = sheet1.createRow(count + r + 1);
                        HSSFCell cell00 = row00.createCell(0);
                        cell00.setCellValue("");
                        cell00.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell001 = row00.createCell(1);
                        cell001.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell002 = row00.createCell(2);
                        cell002.setCellStyle(styleAlignRightBorderAllDetailTable);
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("A" + (count + r + 2) + ":C" + (count + r + 2) + ""));
                        HSSFCell cell003 = row00.createCell(3);
                        cell003.setCellFormula(totalthb);
                        cell003.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell004 = row00.createCell(4);
                        cell004.setCellFormula(totaljpy);
                        cell004.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell005 = row00.createCell(5);
                        cell005.setCellFormula(totalusd);
                        cell005.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell006 = row00.createCell(6);
                        cell006.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell007 = row00.createCell(7);
                        cell007.setCellFormula(totalrecamt);
                        cell007.setCellStyle(styleAlignRightBorderAllNumber);
                        for (int k = 8; k < 13; k++) {
                            HSSFCell cell008 = row00.createCell(k);
                            cell008.setCellStyle(styleAlignRightBorderAllNumber);
                        }
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("I" + (count + r + 2) + ":M" + (count + r + 2) + ""));

                        HSSFRow rowTotalAll = sheet1.createRow(count + r + 2);
                        HSSFCell cellTotal00 = rowTotalAll.createCell(0);
                        cellTotal00.setCellValue("Total");
                        cellTotal00.setCellStyle(total);
                        HSSFCell cellTotal001 = rowTotalAll.createCell(1);
                        cellTotal001.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cellTotal002 = rowTotalAll.createCell(2);
                        cellTotal002.setCellStyle(styleAlignRightBorderAllDetailTable);
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("A" + (count + r + 3) + ":C" + (count + r + 3) + ""));
                        HSSFCell cellTotal003 = rowTotalAll.createCell(3);
                        cellTotal003.setCellValue(bahtTotalAll.doubleValue());
                        cellTotal003.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal004 = rowTotalAll.createCell(4);
                        cellTotal004.setCellValue(jpyTotalAll.doubleValue());
                        cellTotal004.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal005 = rowTotalAll.createCell(5);
                        cellTotal005.setCellValue(usdTotalAll.doubleValue());
                        cellTotal005.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal006 = rowTotalAll.createCell(6);
                        cellTotal006.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal007 = rowTotalAll.createCell(7);
                        cellTotal007.setCellValue(recamtTotalAll.doubleValue());
                        cellTotal007.setCellStyle(styleAlignRightBorderAllNumber);
                        for (int k = 8; k < 13; k++) {
                            HSSFCell cellTotal008 = rowTotalAll.createCell(k);
                            cellTotal008.setCellStyle(styleAlignRightBorderAllNumber);
                        }
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("I" + (count + r + 3) + ":M" + (count + r + 3) + ""));
                    }
                } else if ("Owner".equals(listOver.get(r).getGroup())) {
                    bahtTotalAll = bahtTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getBath())));
                    jpyTotalAll = jpyTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getJpy())));
                    usdTotalAll = usdTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getUsd())));
                    recamtTotalAll = recamtTotalAll.add(
                            "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0)
                                    : (new BigDecimal(listOver.get(r).getRecamt())));

                    if (temp.equals(listOver.get(r).getOwnername())) { // equal type   
                        if (r != (listOver.size() - 1)) { // check not last row
                            HSSFRow row = sheet1.createRow(r + count);
                            createCell(row, listOver, r, styleAlignRightBorderAllNumber,
                                    styleAlignRightBorderAll);
                            sheet1.autoSizeColumn(13);
                        } else {
                            HSSFRow row = sheet1.createRow(r + count);
                            createCell(row, listOver, r, styleAlignRightBorderAllNumber,
                                    styleAlignRightBorderAll);
                            sheet1.autoSizeColumn(13);
                        }
                    } else { // not equal type
                             // Start New Row (Group)
                        String totalthb = "SUM(D" + start + ":D" + (r + count) + ")";
                        String totaljpy = "SUM(E" + start + ":E" + (r + count) + ")";
                        String totalusd = "SUM(F" + start + ":F" + (r + count) + ")";
                        String totalrecamt = "SUM(H" + start + ":H" + (r + count) + ")";
                        //                            sumThbAll += ",D"+(count+r+1);
                        //                            sumJpyAll += ",E"+(count+r+1);
                        //                            sumUsdAll += ",F"+(count+r+1);
                        //                            sumRecAmtAll += ",H"+(count+r+1);
                        start = count + r + 3;
                        HSSFRow row00 = sheet1.createRow(r + count);
                        HSSFCell cell00 = row00.createCell(0);
                        cell00.setCellValue("");
                        cell00.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell001 = row00.createCell(1);
                        cell001.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell002 = row00.createCell(2);
                        cell002.setCellStyle(styleAlignRightBorderAllDetailTable);
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("A" + (count + r + 1) + ":C" + (count + r + 1) + ""));
                        HSSFCell cell003 = row00.createCell(3);
                        cell003.setCellFormula(totalthb);
                        cell003.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell004 = row00.createCell(4);
                        cell004.setCellFormula(totaljpy);
                        cell004.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell005 = row00.createCell(5);
                        cell005.setCellFormula(totalusd);
                        cell005.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell006 = row00.createCell(6);
                        cell006.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell007 = row00.createCell(7);
                        cell007.setCellFormula(totalrecamt);
                        cell007.setCellStyle(styleAlignRightBorderAllNumber);
                        for (int k = 8; k < 13; k++) {
                            HSSFCell cell008 = row00.createCell(k);
                            cell008.setCellStyle(styleAlignRightBorderAllNumber);
                        }
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("I" + (count + r + 1) + ":M" + (count + r + 1) + ""));
                        HSSFRow row0 = sheet1.createRow(r + count + 1);
                        HSSFCell cell = row0.createCell(0);
                        cell.setCellValue(listOver.get(r).getOwnername());
                        cell.setCellStyle(styleAlignRightBorderAllDetailTable);
                        String add = "A" + (r + count + 2) + ":M" + (r + count + 2) + "";
                        sheet1.addMergedRegion(CellRangeAddress.valueOf(add));
                        row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor);
                        HSSFRow row122 = sheet1.createRow(r + count + 2);
                        createCell(row122, listOver, r, styleAlignRightBorderAllNumber,
                                styleAlignRightBorderAll);
                        sheet1.autoSizeColumn(13);
                        count = count + 2;
                    }
                    temp = listOver.get(r).getOwnername();
                    if (r == (listOver.size() - 1)) {
                        String totalthb = "SUM(D" + start + ":D" + (count + r + 1) + ")";
                        String totaljpy = "SUM(E" + start + ":E" + (count + r + 1) + ")";
                        String totalusd = "SUM(F" + start + ":F" + (count + r + 1) + ")";
                        String totalrecamt = "SUM(H" + start + ":H" + (count + r + 1) + ")";
                        //                            sumThbAll += ",D"+(count+r+2);
                        //                            sumJpyAll += ",E"+(count+r+2);
                        //                            sumUsdAll += ",F"+(count+r+2);
                        //                            sumRecAmtAll += ",H"+(count+r+2);
                        HSSFRow row00 = sheet1.createRow(count + r + 1);
                        HSSFCell cell00 = row00.createCell(0);
                        cell00.setCellValue("");
                        cell00.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell001 = row00.createCell(1);
                        cell001.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cell002 = row00.createCell(2);
                        cell002.setCellStyle(styleAlignRightBorderAllDetailTable);
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("A" + (count + r + 2) + ":C" + (count + r + 2) + ""));
                        HSSFCell cell003 = row00.createCell(3);
                        cell003.setCellFormula(totalthb);
                        cell003.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell004 = row00.createCell(4);
                        cell004.setCellFormula(totaljpy);
                        cell004.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell005 = row00.createCell(5);
                        cell005.setCellFormula(totalusd);
                        cell005.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell006 = row00.createCell(6);
                        cell006.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cell007 = row00.createCell(7);
                        cell007.setCellFormula(totalrecamt);
                        cell007.setCellStyle(styleAlignRightBorderAllNumber);
                        for (int k = 8; k < 13; k++) {
                            HSSFCell cell008 = row00.createCell(k);
                            cell008.setCellStyle(styleAlignRightBorderAllNumber);
                        }
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("I" + (count + r + 2) + ":M" + (count + r + 2) + ""));

                        HSSFRow rowTotalAll = sheet1.createRow(count + r + 2);
                        HSSFCell cellTotal00 = rowTotalAll.createCell(0);
                        cellTotal00.setCellValue("Total");
                        cellTotal00.setCellStyle(total);
                        HSSFCell cellTotal001 = rowTotalAll.createCell(1);
                        cellTotal001.setCellStyle(styleAlignRightBorderAllDetailTable);
                        HSSFCell cellTotal002 = rowTotalAll.createCell(2);
                        cellTotal002.setCellStyle(styleAlignRightBorderAllDetailTable);
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("A" + (count + r + 3) + ":C" + (count + r + 3) + ""));
                        HSSFCell cellTotal003 = rowTotalAll.createCell(3);
                        cellTotal003.setCellValue(bahtTotalAll.doubleValue());
                        cellTotal003.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal004 = rowTotalAll.createCell(4);
                        cellTotal004.setCellValue(jpyTotalAll.doubleValue());
                        cellTotal004.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal005 = rowTotalAll.createCell(5);
                        cellTotal005.setCellValue(usdTotalAll.doubleValue());
                        cellTotal005.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal006 = rowTotalAll.createCell(6);
                        cellTotal006.setCellStyle(styleAlignRightBorderAllNumber);
                        HSSFCell cellTotal007 = rowTotalAll.createCell(7);
                        cellTotal007.setCellValue(recamtTotalAll.doubleValue());
                        cellTotal007.setCellStyle(styleAlignRightBorderAllNumber);
                        for (int k = 8; k < 13; k++) {
                            HSSFCell cellTotal008 = rowTotalAll.createCell(k);
                            cellTotal008.setCellStyle(styleAlignRightBorderAllNumber);
                        }
                        sheet1.addMergedRegion(
                                CellRangeAddress.valueOf("I" + (count + r + 3) + ":M" + (count + r + 3) + ""));
                    }
                }
            } else {
                bahtTotalAll = bahtTotalAll
                        .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0)
                                : (new BigDecimal(listOver.get(r).getBath())));
                jpyTotalAll = jpyTotalAll
                        .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0)
                                : (new BigDecimal(listOver.get(r).getJpy())));
                usdTotalAll = usdTotalAll
                        .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0)
                                : (new BigDecimal(listOver.get(r).getUsd())));
                recamtTotalAll = recamtTotalAll.add(
                        "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0)
                                : (new BigDecimal(listOver.get(r).getRecamt())));

                HSSFRow row0 = sheet1.createRow(count + r);
                if ("Owner".equals(listOver.get(r).getGroup())) {
                    HSSFCell cell = row0.createCell(0);
                    cell.setCellValue(listOver.get(r).getOwnername());
                    cell.setCellStyle(styleAlignRightBorderAllDetailTable);
                } else if ("Agent".equals(listOver.get(r).getGroup())) {
                    HSSFCell cell = row0.createCell(0);
                    cell.setCellValue(listOver.get(r).getInvto());
                    cell.setCellStyle(styleAlignRightBorderAllDetailTable);
                }
                String add = "A" + (count + r + 1) + ":M" + (count + r + 1) + "";
                sheet1.addMergedRegion(CellRangeAddress.valueOf(add));
                row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor);
                HSSFRow row = sheet1.createRow(count + r + 1);
                createCell(row, listOver, num, styleAlignRightBorderAllNumber, styleAlignRightBorderAll);
                sheet1.autoSizeColumn(13);
                count = count + 1;
            }
        }
        for (int i = 0; i < 30; i++) {
            sheet1.autoSizeColumn(i);
        }
    }
}

---

From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java

private void getStockInvoiceSummary(HSSFWorkbook wb, List stockInvoiceSummary) {
    String sheetStockInvoiceSummary = "StockInvoiceSummary";// name of sheet

    UtilityExcelFunction excelFunction = new UtilityExcelFunction();

    HSSFSheet sheet = wb.createSheet(sheetStockInvoiceSummary);

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC26 = wb.createCellStyle();
    styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC26.setDataFormat(currency.getFormat("#,##0"));
    styleC26.setAlignment(styleC22.ALIGN_CENTER);

    HSSFCellStyle styleC27 = wb.createCellStyle();
    styleC27.setAlignment(styleC27.ALIGN_RIGHT);
    styleC27.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC28 = wb.createCellStyle();
    styleC28.setAlignment(styleC28.ALIGN_CENTER);
    styleC28.setDataFormat(currency.getFormat("#,##0"));

    HSSFCellStyle styleC29 = wb.createCellStyle();
    styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC29.setVerticalAlignment(styleC29.VERTICAL_CENTER);

    HSSFCellStyle styleC30 = wb.createCellStyle();
    styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC30.setAlignment(styleC30.ALIGN_CENTER);
    styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER);

    HSSFCellStyle styleC31 = wb.createCellStyle();
    styleC31.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC31.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC32 = wb.createCellStyle();
    styleC32.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC32.setAlignment(styleC32.ALIGN_CENTER);
    styleC32.setVerticalAlignment(styleC32.VERTICAL_CENTER);
    styleC32.setWrapText(true);//from  w w  w .  java  2s  .com

    HSSFCellStyle styleC33 = wb.createCellStyle();
    styleC33.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC33.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC33.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC33.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC33.setAlignment(styleC33.ALIGN_LEFT);
    styleC33.setVerticalAlignment(styleC33.VERTICAL_CENTER);
    styleC33.setWrapText(true);

    StockInvoiceSummaryView dataheader = (StockInvoiceSummaryView) stockInvoiceSummary.get(0);

    // set Header Report (Row 1)
    HSSFCellStyle styleC1 = wb.createCellStyle();
    HSSFRow row1 = sheet.createRow(0);
    HSSFCell cell1 = row1.createCell(0);
    cell1.setCellValue("Stock Invoice Summary");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cell1.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(0);
    cell21.setCellValue("Product : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(1);
    cell22.setCellValue(dataheader.getProductHeader());
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
    HSSFCell cell23 = row2.createCell(4);
    cell23.setCellValue("Inv To : ");
    cell23.setCellStyle(styleC21);
    HSSFCell cell24 = row2.createCell(5);
    cell24.setCellValue(dataheader.getInvtoHeader());
    cell24.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F2:I2"));

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(0);
    cell31.setCellValue("Effective Date : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(1);
    cell32.setCellValue(dataheader.getEffectivedateHeader());
    cell32.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
    HSSFCell cell33 = row3.createCell(4);
    cell33.setCellValue("Invoice Date : ");
    cell33.setCellStyle(styleC21);
    HSSFCell cell34 = row3.createCell(5);
    cell34.setCellValue(dataheader.getInvoicedateHeader());
    cell34.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F3:I3"));

    // Row 4
    HSSFRow row4 = sheet.createRow(3);
    HSSFCell cell41 = row4.createCell(0);
    cell41.setCellValue("Add Date : ");
    cell41.setCellStyle(styleC21);
    HSSFCell cell42 = row4.createCell(1);
    cell42.setCellValue(dataheader.getAdddateHeader());
    cell42.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));

    // Header Table
    HSSFCellStyle styleC3 = wb.createCellStyle();
    styleC3.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3.setAlignment(styleC3.ALIGN_CENTER);
    styleC3.setVerticalAlignment(styleC3.VERTICAL_CENTER);
    styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN);

    HSSFCellStyle styletop = wb.createCellStyle();
    styletop.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styletop.setAlignment(styletop.ALIGN_CENTER);
    styletop.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styletop.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFCellStyle stylebottom = wb.createCellStyle();
    stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylebottom.setAlignment(styletop.ALIGN_CENTER);
    stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont()));
    stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFRow row5 = sheet.createRow(5);
    HSSFCell cell51 = row5.createCell(0);
    cell51.setCellValue("Item No");
    cell51.setCellStyle(styletop);
    HSSFCell cell62 = row5.createCell(1);
    cell62.setCellValue("Item type");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row5.createCell(2);
    cell63.setCellValue("Ref No");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row5.createCell(3);
    cell64.setCellValue("Owner");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row5.createCell(4);
    cell65.setCellValue("Inv No");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row5.createCell(5);
    cell66.setCellValue("Inv Name");
    cell66.setCellStyle(styletop);
    sheet.autoSizeColumn(5);
    HSSFCell cell67 = row5.createCell(6);
    cell67.setCellValue("Inv Date");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row5.createCell(7);
    cell68.setCellValue("Cost");
    cell68.setCellStyle(styletop);
    HSSFCell cell69 = row5.createCell(8);
    cell69.setCellValue("Sale Price");
    cell69.setCellStyle(styletop);
    HSSFCell cell610 = row5.createCell(9);
    cell610.setCellValue("Profit");
    cell610.setCellStyle(styletop);
    HSSFCell cell611 = row5.createCell(10);
    cell611.setCellValue("Stock No");
    cell611.setCellStyle(styletop);

    //Detail of Table
    int count = 6;
    boolean isMerge = false;
    int hMerge = 7;
    int countMerge = 0;

    for (int i = 0; i < stockInvoiceSummary.size(); i++) {
        StockInvoiceSummaryView data = (StockInvoiceSummaryView) stockInvoiceSummary.get(i);
        StockInvoiceSummaryView dataTemp = new StockInvoiceSummaryView();
        if (i != stockInvoiceSummary.size() - 1) {
            dataTemp = (StockInvoiceSummaryView) stockInvoiceSummary.get(i + 1);

        } else {
            dataTemp = null;
        }

        HSSFRow row = sheet.createRow(count + i);
        String id = (data.getId() != null ? data.getId() : "");
        String idTemp = (dataTemp != null ? dataTemp.getId() : "");
        countMerge++;

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(
                !"".equalsIgnoreCase(data.getItemno()) && data.getItemno() != null ? data.getItemno() : "");
        celldata0.setCellStyle(styleC30);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(
                !"".equalsIgnoreCase(data.getItemtype()) && data.getItemtype() != null ? data.getItemtype()
                        : "");
        celldata1.setCellStyle(styleC30);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(
                !"".equalsIgnoreCase(data.getRefno()) && data.getRefno() != null ? data.getRefno() : "");
        celldata2.setCellStyle(styleC30);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(
                !"".equalsIgnoreCase(data.getOwner()) && data.getOwner() != null ? data.getOwner() : "");
        celldata3.setCellStyle(styleC30);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(
                !"".equalsIgnoreCase(data.getInvno()) && data.getInvno() != null ? data.getInvno() : "");
        celldata4.setCellStyle(styleC33);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(
                !"".equalsIgnoreCase(data.getInvname()) && data.getInvname() != null ? data.getInvname() : "");
        celldata5.setCellStyle(styleC29);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(
                !"".equalsIgnoreCase(data.getInvdate()) && data.getInvdate() != null ? data.getInvdate() : "");
        celldata6.setCellStyle(styleC33);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue(!"".equalsIgnoreCase(data.getCost()) && data.getCost() != null
                ? new BigDecimal(data.getCost()).doubleValue()
                : 0);
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue(!"".equalsIgnoreCase(data.getSaleprice()) && data.getSaleprice() != null
                ? new BigDecimal(data.getSaleprice()).doubleValue()
                : 0);
        celldata8.setCellStyle(styleC25);

        HSSFCell celldata9 = row.createCell(9);
        celldata9.setCellValue(!"".equalsIgnoreCase(data.getProfit()) && data.getProfit() != null
                ? new BigDecimal(data.getProfit()).doubleValue()
                : 0);
        celldata9.setCellStyle(styleC25);

        HSSFCell celldata10 = row.createCell(10);
        celldata10.setCellValue(
                !"".equalsIgnoreCase(data.getStockno()) && data.getStockno() != null ? data.getStockno() : "");
        celldata10.setCellStyle(styleC29);

        //            HSSFCell celldata11 = row.createCell(11);
        //            celldata11.setCellValue(!"".equalsIgnoreCase(data.getId()) && data.getId() != null ? data.getId() : "");
        //            celldata11.setCellStyle(styleC29);

        if (!id.equalsIgnoreCase(idTemp) && (!"".equalsIgnoreCase(id) || !"".equalsIgnoreCase(idTemp))) {
            if (countMerge > 1) {
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("H" + (hMerge) + ":H" + (hMerge + (countMerge - 1))));
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("I" + (hMerge) + ":I" + (hMerge + (countMerge - 1))));
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("J" + (hMerge) + ":J" + (hMerge + (countMerge - 1))));
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("K" + (hMerge) + ":K" + (hMerge + (countMerge - 1))));
            }

            hMerge = 7 + i + 1;
            countMerge = 0;
        }

        //            HSSFCell celldata11 = row.createCell(11);
        //            celldata11.setCellValue(!"".equalsIgnoreCase(data.getId()) && data.getId() != null ? data.getId() : "");
        //            celldata11.setCellStyle(styleC29);

    }

    for (int j = 0; j < 11; j++) {
        sheet.autoSizeColumn(j);
    }

    //        sheet.setColumnWidth(0, 256*15);
    //        sheet.setColumnWidth(1, 256*15);
    //        sheet.setColumnWidth(2, 256*15);
    sheet.setColumnWidth(6, 256 * 10);
    sheet.setColumnWidth(7, 256 * 15);
    sheet.setColumnWidth(8, 256 * 15);
    sheet.setColumnWidth(9, 256 * 15);
    sheet.setColumnWidth(10, 256 * 60);
    //        sheet.setColumnWidth(11, 256*15);
    //        sheet.setColumnWidth(12, 256*15);
    //        sheet.setColumnWidth(13, 256*15);
    //        sheet.setColumnWidth(14, 256*15);
    //        sheet.setColumnWidth(15, 256*15);
    //        sheet.setColumnWidth(16, 256*15);
    //        sheet.setColumnWidth(17, 256*15);
    //        sheet.setColumnWidth(18, 256*15);
    //        sheet.setColumnWidth(19, 256*15);

}

---

From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java

private void getStockNonInvoiceSummary(HSSFWorkbook wb, List stockNonInvoiceSummary) {
    String sheetStockNonInvoiceSummary = "StockNonInvoiceSummary";// name of sheet

    UtilityExcelFunction excelFunction = new UtilityExcelFunction();

    HSSFSheet sheet = wb.createSheet(sheetStockNonInvoiceSummary);

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC26 = wb.createCellStyle();
    styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC26.setDataFormat(currency.getFormat("#,##0"));
    styleC26.setAlignment(styleC26.ALIGN_CENTER);

    HSSFCellStyle styleC27 = wb.createCellStyle();
    styleC27.setAlignment(styleC27.ALIGN_RIGHT);
    styleC27.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC28 = wb.createCellStyle();
    styleC28.setAlignment(styleC28.ALIGN_CENTER);
    styleC28.setDataFormat(currency.getFormat("#,##0"));

    HSSFCellStyle styleC29 = wb.createCellStyle();
    styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC29.setVerticalAlignment(styleC29.VERTICAL_CENTER);

    HSSFCellStyle styleC30 = wb.createCellStyle();
    styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC30.setAlignment(styleC30.ALIGN_CENTER);
    styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER);

    HSSFCellStyle styleC31 = wb.createCellStyle();
    styleC31.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC31.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC32 = wb.createCellStyle();
    styleC32.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC32.setAlignment(styleC32.ALIGN_CENTER);
    styleC32.setVerticalAlignment(styleC32.VERTICAL_CENTER);
    styleC32.setWrapText(true);//from w ww  .  ja v a  2s  .com

    StockNonInvoiceSummaryView dataheader = (StockNonInvoiceSummaryView) stockNonInvoiceSummary.get(0);

    // set Header Report (Row 1)
    HSSFCellStyle styleC1 = wb.createCellStyle();
    HSSFRow row1 = sheet.createRow(0);
    HSSFCell cell1 = row1.createCell(0);
    cell1.setCellValue("Stock Non Invoice Summary");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cell1.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(0);
    cell21.setCellValue("Product : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(1);
    cell22.setCellValue(dataheader.getProductHeader());
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
    HSSFCell cell23 = row2.createCell(4);
    cell23.setCellValue("Invoice Sup : ");
    cell23.setCellStyle(styleC21);
    HSSFCell cell24 = row2.createCell(5);
    cell24.setCellValue(dataheader.getInvoicesupHeader());
    cell24.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F2:H2"));

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(0);
    cell31.setCellValue("Effective Date : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(1);
    cell32.setCellValue(dataheader.getEffectivedateHeader());
    cell32.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
    HSSFCell cell33 = row3.createCell(4);
    cell33.setCellValue("Pay Date : ");
    cell33.setCellStyle(styleC21);
    HSSFCell cell34 = row3.createCell(5);
    cell34.setCellValue(dataheader.getPaydateHeader());
    cell34.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F3:H3"));

    // Row 4
    HSSFRow row4 = sheet.createRow(3);
    HSSFCell cell41 = row4.createCell(0);
    cell41.setCellValue("Add Date : ");
    cell41.setCellStyle(styleC21);
    HSSFCell cell42 = row4.createCell(1);
    cell42.setCellValue(dataheader.getAdddateHeader());
    cell42.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));

    // Header Table
    HSSFCellStyle styleC3 = wb.createCellStyle();
    styleC3.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3.setAlignment(styleC3.ALIGN_CENTER);
    styleC3.setVerticalAlignment(styleC3.VERTICAL_CENTER);
    styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN);

    HSSFCellStyle styletop = wb.createCellStyle();
    styletop.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styletop.setAlignment(styletop.ALIGN_CENTER);
    styletop.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styletop.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFCellStyle stylebottom = wb.createCellStyle();
    stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylebottom.setAlignment(styletop.ALIGN_CENTER);
    stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont()));
    stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFRow row5 = sheet.createRow(5);
    HSSFCell cell51 = row5.createCell(0);
    cell51.setCellValue("Item No");
    cell51.setCellStyle(styletop);
    HSSFCell cell62 = row5.createCell(1);
    cell62.setCellValue("Item Type");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row5.createCell(2);
    cell63.setCellValue("Ref No");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row5.createCell(3);
    cell64.setCellValue("Owner");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row5.createCell(4);
    cell65.setCellValue("Pay No");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row5.createCell(5);
    cell66.setCellValue("Pay Date");
    cell66.setCellStyle(styletop);
    HSSFCell cell67 = row5.createCell(6);
    cell67.setCellValue("Invoice Sup");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row5.createCell(7);
    cell68.setCellValue("Cost");
    cell68.setCellStyle(styletop);
    HSSFCell cell69 = row5.createCell(8);
    cell69.setCellValue("Stock No");
    cell69.setCellStyle(styletop);

    //Detail of Table
    int count = 6;
    for (int i = 0; i < stockNonInvoiceSummary.size(); i++) {
        StockNonInvoiceSummaryView data = (StockNonInvoiceSummaryView) stockNonInvoiceSummary.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(
                !"".equalsIgnoreCase(data.getItemno()) && data.getItemno() != null ? data.getItemno() : "");
        celldata0.setCellStyle(styleC29);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(
                !"".equalsIgnoreCase(data.getItemtype()) && data.getItemtype() != null ? data.getItemtype()
                        : "");
        celldata1.setCellStyle(styleC30);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(
                !"".equalsIgnoreCase(data.getRefno()) && data.getRefno() != null ? data.getRefno() : "");
        celldata2.setCellStyle(styleC30);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(
                !"".equalsIgnoreCase(data.getOwner()) && data.getOwner() != null ? data.getOwner() : "");
        celldata3.setCellStyle(styleC29);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(
                !"".equalsIgnoreCase(data.getPayno()) && data.getPayno() != null ? data.getPayno() : "");
        celldata4.setCellStyle(styleC32);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(
                !"".equalsIgnoreCase(data.getPaydate()) && data.getPaydate() != null ? data.getPaydate() : "");
        celldata5.setCellStyle(styleC30);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(!"".equalsIgnoreCase(data.getInvoicesup()) && data.getInvoicesup() != null
                ? data.getInvoicesup()
                : "");
        celldata6.setCellStyle(styleC29);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue(!"".equalsIgnoreCase(data.getCost()) && data.getCost() != null
                ? new BigDecimal(data.getCost()).doubleValue()
                : 0);
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue(
                !"".equalsIgnoreCase(data.getStockno()) && data.getStockno() != null ? data.getStockno() : "");
        celldata8.setCellStyle(styleC29);

        HSSFCell celldata9 = row.createCell(9);
        celldata9
                .setCellValue(!"".equalsIgnoreCase(data.getPaymentstockno()) && data.getPaymentstockno() != null
                        ? data.getPaymentstockno()
                        : "");
        celldata9.setCellStyle(styleC29);

    }

    for (int j = 0; j < 9; j++) {
        sheet.autoSizeColumn(j);
    }

    //        sheet.setColumnWidth(0, 256*15);
    //        sheet.setColumnWidth(1, 256*15);
    //        sheet.setColumnWidth(2, 256*15);
    //        sheet.setColumnWidth(3, 256*15);
    //        sheet.setColumnWidth(4, 256*25);
    //        sheet.setColumnWidth(5, 256*25);
    //        sheet.setColumnWidth(6, 256*15);
    //        sheet.setColumnWidth(10, 256*15);
    //        sheet.setColumnWidth(11, 256*15);
    //        sheet.setColumnWidth(12, 256*15);
    //        sheet.setColumnWidth(13, 256*15);
    //        sheet.setColumnWidth(14, 256*15);
    //        sheet.setColumnWidth(15, 256*15);
    //        sheet.setColumnWidth(16, 256*15);
    //        sheet.setColumnWidth(17, 256*15);
    //        sheet.setColumnWidth(18, 256*15);
    //        sheet.setColumnWidth(19, 256*15);

}

---

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportTravoxReport(List<MainMigrateModel> list) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFSheet sheet = wb.createSheet("TravoxReport");

    HSSFRow row2 = sheet.createRow(0);/*  ww w  .j  a  v a  2 s . c  om*/
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("GJ");
    cell20.setCellStyle(styleC3Center);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("PAY NO");
    cell21.setCellStyle(styleC3Center);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("NAME");
    cell22.setCellStyle(styleC3Center);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("AP CODE");
    cell23.setCellStyle(styleC3Center);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("REFDOC");
    cell24.setCellStyle(styleC3Center);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("SYSTEM_DATE");
    cell25.setCellStyle(styleC3Center);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("DUE DATE");
    cell26.setCellStyle(styleC3Center);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("INVOICE NUM");
    cell27.setCellStyle(styleC3Center);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("MAIN DESCRIPTION");
    cell28.setCellStyle(styleC3Center);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("CODE");
    cell29.setCellStyle(styleC3Center);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("TYPE PRODUCT");
    cell30.setCellStyle(styleC3Center);
    HSSFCell cell31 = row2.createCell(11);
    cell31.setCellValue("DESCRIPTION");
    cell31.setCellStyle(styleC3Center);
    HSSFCell cell32 = row2.createCell(12);
    cell32.setCellValue("TOTAL AMOUNT");
    cell32.setCellStyle(styleC3Center);
    HSSFCell cell33 = row2.createCell(13);
    cell33.setCellValue("TOTAL VAT");
    cell33.setCellStyle(styleC3Center);
    HSSFCell cell34 = row2.createCell(14);
    cell34.setCellValue("CUR");
    cell34.setCellStyle(styleC3Center);
    HSSFCell cell35 = row2.createCell(15);
    cell35.setCellValue("AMOUNT");
    cell35.setCellStyle(styleC3Center);
    HSSFCell cell36 = row2.createCell(16);
    cell36.setCellValue("DEPARTMENT");
    cell36.setCellStyle(styleC3Center);
    HSSFCell cell37 = row2.createCell(17);
    cell37.setCellValue("ACC NO");
    cell37.setCellStyle(styleC3Center);
    HSSFCell cell38 = row2.createCell(18);
    cell38.setCellValue("EXPENSE DATE");
    cell38.setCellStyle(styleC3Center);
    HSSFCell cell39 = row2.createCell(19);
    cell39.setCellValue("VOUCHER NO");
    cell39.setCellStyle(styleC3Center);
    HSSFCell cell40 = row2.createCell(20);
    cell40.setCellValue("VOUCHER AMOUNT");
    cell40.setCellStyle(styleC3Center);

    if (list != null) {
        int count = 1;
        for (int i = 0; i < list.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) list.get(i);
            HSSFRow row = sheet.createRow(count + i);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getGj());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getPayno());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getName());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(data.getApcode());
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(data.getRefdoc());
            cell3.setCellStyle(styleC24);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getSystemdate());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getDuedate());
            cell5.setCellStyle(styleC24);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getInvoicenum());
            cell6.setCellStyle(styleC24);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getMaindescription());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getCode());
            cell8.setCellStyle(styleC24);
            HSSFCell cell9 = row.createCell(10);
            cell9.setCellValue(data.getTypeproduct());
            cell9.setCellStyle(styleC24);
            HSSFCell cell11 = row.createCell(11);
            cell11.setCellValue(data.getDescription());
            cell11.setCellStyle(styleC24);
            HSSFCell cell12 = row.createCell(12);
            cell12.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalamount()))
                    ? (new BigDecimal(data.getTotalamount())).doubleValue()
                    : 0);
            cell12.setCellStyle(styleC25);
            HSSFCell cell013 = row.createCell(13);
            cell013.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalvat()))
                    ? (new BigDecimal(data.getTotalvat())).doubleValue()
                    : 0);
            cell013.setCellStyle(styleC25);
            HSSFCell cell14 = row.createCell(14);
            cell14.setCellValue(data.getCur());
            cell14.setCellStyle(styleC23);
            HSSFCell cell15 = row.createCell(15);
            cell15.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getAmount()))
                    ? (new BigDecimal(data.getAmount())).doubleValue()
                    : 0);
            cell15.setCellStyle(styleC25);
            HSSFCell cell16 = row.createCell(16);
            cell16.setCellValue(data.getDepartment());
            cell16.setCellStyle(styleC24);
            HSSFCell cell17 = row.createCell(17);
            cell17.setCellValue(data.getAccno());
            cell17.setCellStyle(styleC24);
            HSSFCell cell18 = row.createCell(18);
            cell18.setCellValue(data.getExpensedate());
            cell18.setCellStyle(styleC24);
            HSSFCell cell19 = row.createCell(19);
            cell19.setCellValue(data.getVoucherno());
            cell19.setCellStyle(styleC24);
            HSSFCell cell020 = row.createCell(20);
            cell020.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getVoucheramount()))
                    ? (new BigDecimal(data.getVoucheramount())).doubleValue()
                    : 0);
            cell020.setCellStyle(styleC25);
        }
    }
    for (int x = 0; x < 21; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(2, 256 * 30);
    sheet.setColumnWidth(8, 256 * 30);
    sheet.setColumnWidth(11, 256 * 30);
    exportFileExcel("TravoxReport", wb);
}

---

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportInvoiceReport(List<MainMigrateModel> listInv) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFSheet sheet = wb.createSheet("Invoice");

    HSSFRow row2 = sheet.createRow(0);/*from  w  w  w.j a v  a 2 s.  com*/
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("INV NO");
    cell21.setCellStyle(styleC3Center);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("NAME");
    cell22.setCellStyle(styleC3Center);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("INV DATE");
    cell23.setCellStyle(styleC3Center);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("GRAND TOTAL");
    cell24.setCellStyle(styleC3Center);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("GRAND TOTAL GROSS");
    cell25.setCellStyle(styleC3Center);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("GRAND TOTAL VAT");
    cell26.setCellStyle(styleC3Center);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("CUR");
    cell27.setCellStyle(styleC3Center);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("DEPARTMENT");
    cell28.setCellStyle(styleC3Center);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("ACC NO");
    cell29.setCellStyle(styleC3Center);

    if (listInv != null) {
        int count = 1;
        for (int i = 0; i < listInv.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listInv.get(i);
            HSSFRow row = sheet.createRow(count + i);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getId());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getInvno());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getName());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(data.getInvdate());
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            //                cell3.setCellValue(data.getGrandtotal());
            cell3.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotal()))
                    ? (new BigDecimal(data.getGrandtotal())).doubleValue()
                    : 0);
            cell3.setCellStyle(styleC25);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalgross()))
                    ? (new BigDecimal(data.getGrandtotalgross())).doubleValue()
                    : 0);
            cell4.setCellStyle(styleC25);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalvat()))
                    ? (new BigDecimal(data.getGrandtotalvat())).doubleValue()
                    : 0);
            cell5.setCellStyle(styleC25);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getCur());
            cell6.setCellStyle(styleC23);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getDepartment());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getAccno());
            cell8.setCellStyle(styleC24);
        }
    }
    for (int x = 0; x < 10; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(2, 256 * 30);
    exportFileExcel("Invoice", wb);
}

---

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportDeptorInvoiceReport(List<MainMigrateModel> listInv) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    //        styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFSheet sheet = wb.createSheet("DeptorInvoice");

    HSSFRow row2 = sheet.createRow(0);//  ww w  .ja v  a2 s  .  co m
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("INV NO");
    cell20.setCellStyle(styleC3Center);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("DATE");
    cell21.setCellStyle(styleC3Center);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("NAME");
    cell22.setCellStyle(styleC3Center);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("DETAIL");
    cell23.setCellStyle(styleC3Center);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("INV AMOUNT");
    cell24.setCellStyle(styleC3Center);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("RECEIVE NO");
    cell25.setCellStyle(styleC3Center);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("RECEIVE AMOUNT");
    cell26.setCellStyle(styleC3Center);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("REMAIN AMOUNT");
    cell27.setCellStyle(styleC3Center);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("CODE");
    cell28.setCellStyle(styleC3Center);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("DEPARTMENT");
    cell29.setCellStyle(styleC3Center);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("GRAND TOTAL VATAMT");
    cell30.setCellStyle(styleC3Center);

    if (listInv != null) {
        int count = 1;
        for (int i = 0; i < listInv.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listInv.get(i);
            HSSFRow row = sheet.createRow(count + i);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getInvoiceno());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getInvoicedate());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getInvoicename());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(data.getInvoicedetail());
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(data.getInvoiceamount());
            cell3.setCellStyle(styleC25);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getReceiveno());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getReceiveamount());
            cell5.setCellStyle(styleC25);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getRemainamount());
            cell6.setCellStyle(styleC25);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getCode());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getDepartment());
            cell8.setCellStyle(styleC24);
            HSSFCell cell9 = row.createCell(10);
            cell9.setCellValue(data.getGrandtotal());
            cell9.setCellStyle(styleC25);
        }
    }
    for (int x = 0; x < 11; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(2, 256 * 30);
    exportFileExcel("DeptorInvoice", wb);
}

---

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportAPReport(List<MainMigrateModel> listAP) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    String datetemp = "";
    if (listAP != null) {
        HSSFSheet sheet = wb.createSheet(listAP.get(0).getPaydate().substring(3, 10).replaceAll("-", ""));
        int count = 1;
        for (int i = 0; i < listAP.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listAP.get(i);
            if (!"".equalsIgnoreCase(datetemp)
                    && !datetemp.equalsIgnoreCase(data.getPaydate().substring(3, 10))) {
                sheet = wb.createSheet(data.getPaydate().substring(3, 10).replaceAll("-", ""));
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("PAYID");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("PAY NO");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("AP CODE");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("NAME");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("PAY DATE");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("DEPARTMENT");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("VAT TYPE");
                cell26.setCellStyle(styleC3Center);
                HSSFCell cell27 = row2.createCell(7);
                cell27.setCellValue("TAX NO");
                cell27.setCellStyle(styleC3Center);
                HSSFCell cell28 = row2.createCell(8);
                cell28.setCellValue("BRANCH");
                cell28.setCellStyle(styleC3Center);
                HSSFCell cell29 = row2.createCell(9);
                cell29.setCellValue("BRANCH NO");
                cell29.setCellStyle(styleC3Center);
                count = 1;/*from  w ww .  j  ava  2s . c  o  m*/
                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 15);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 25);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
                sheet.setColumnWidth(7, 256 * 15);
                sheet.setColumnWidth(8, 256 * 15);
                sheet.setColumnWidth(9, 256 * 15);
            } else if ("".equalsIgnoreCase(datetemp)) {
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("PAYID");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("PAY NO");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("AP CODE");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("NAME");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("PAY DATE");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("DEPARTMENT");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("VAT TYPE");
                cell26.setCellStyle(styleC3Center);
                HSSFCell cell27 = row2.createCell(7);
                cell27.setCellValue("TAX NO");
                cell27.setCellStyle(styleC3Center);
                HSSFCell cell28 = row2.createCell(8);
                cell28.setCellValue("BRANCH");
                cell28.setCellStyle(styleC3Center);
                HSSFCell cell29 = row2.createCell(9);
                cell29.setCellValue("BRANCH NO");
                cell29.setCellStyle(styleC3Center);

                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 15);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 25);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
                sheet.setColumnWidth(7, 256 * 15);
                sheet.setColumnWidth(8, 256 * 15);
                sheet.setColumnWidth(9, 256 * 15);

            }

            HSSFRow row = sheet.createRow(count);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getPayid());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getPayno());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getApCode());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(String.valueOf(data.getApname()));
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(String.valueOf(data.getPaydate()));
            cell3.setCellStyle(styleC24);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getDepartment());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getVattype());
            cell5.setCellStyle(styleC24);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getTaxno());
            cell6.setCellStyle(styleC24);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getBranch());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getBranchno());
            cell8.setCellStyle(styleC24);
            datetemp = data.getPaydate().substring(3, 10);
            count++;
        }
    }
    exportFileExcel("APReport", wb);
}

---

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportARReport(List<MainMigrateModel> listAR) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    String datetemp = "";
    if (listAR != null) {
        //            HSSFSheet sheet = wb.createSheet(listAR.get(0).getInvdate().substring(3,10).replaceAll("-", ""));
        HSSFSheet sheet = wb.createSheet("ARReport");
        int count = 1;
        for (int i = 0; i < listAR.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listAR.get(i);
            if (!"".equalsIgnoreCase(datetemp)
                    && !datetemp.equalsIgnoreCase(data.getInvdate().substring(3, 10))) {
                sheet = wb.createSheet(data.getInvdate().substring(3, 10).replaceAll("-", ""));
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("CODE");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("INV NAME");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("INV NO");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("INV DATE");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("TAX NO");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("BRANCH");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("BRANCH NO");
                cell26.setCellStyle(styleC3Center);

                count = 1;/*from  w  w  w.  j a v a 2s .  co  m*/
                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 25);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 15);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
            } else if ("".equalsIgnoreCase(datetemp)) {
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("CODE");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("INV NAME");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("INV NO");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("INV DATE");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("TAX NO");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("BRANCH");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("BRANCH NO");
                cell26.setCellStyle(styleC3Center);

                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 25);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 15);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
            }

            HSSFRow row = sheet.createRow(count);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getCode());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getInvname());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getInvno());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(String.valueOf(data.getInvdate()));
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(data.getTaxno());
            cell3.setCellStyle(styleC24);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getBranch());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getBranchno());
            cell5.setCellStyle(styleC24);

            //                    datetemp = data.getInvdate().substring(3,10);
            count++;
        }
    }
    exportFileExcel("ARReport", wb);
}

---

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportTaxinvoiceReport(List reptax) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("TaxInvoice");

    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    HSSFRow row1 = sheet.createRow(0);/*  w ww .jav a 2  s .com*/
    HSSFCell cellStart = row1.createCell(0);
    cellStart.setCellValue("Tax Invoice Report");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cellStart.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN);

    HSSFRow row2 = sheet.createRow(2);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(0);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("TAX ID");
    cell21.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(1);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("TAX NO");
    cell22.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(2);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("TAX DATE");
    cell23.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(3);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("CODE AP");
    cell24.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(4);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("DESCRIPTION");
    cell25.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(5);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("GROSS AMOUNT");
    cell26.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(6);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("VAT AMOUNT");
    cell27.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(7);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("AMOUNT");
    cell28.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(8);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("FLAG TYPE");
    cell29.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(9);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("INVOICE TYPE");
    cell30.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(10);
    HSSFCell cell31 = row2.createCell(11);
    cell31.setCellValue("TAX NO 1");
    cell31.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(11);
    HSSFCell cell32 = row2.createCell(12);
    cell32.setCellValue("BRANCH");
    cell32.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(12);
    HSSFCell cell33 = row2.createCell(13);
    cell33.setCellValue("BRANCH NO");
    cell33.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(13);

    int count = 3;

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    for (int i = 0; i < reptax.size(); i++) {
        ReportTaxInvoice data = (ReportTaxInvoice) reptax.get(i);
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(data.getId());
        cell0.setCellStyle(styleC23);
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(data.getTaxid());
        cell1.setCellStyle(styleC23);
        HSSFCell cell13 = row.createCell(2);
        cell13.setCellValue(data.getTaxno());
        cell13.setCellStyle(styleC23);
        HSSFCell cell2 = row.createCell(3);
        cell2.setCellValue(String.valueOf(data.getTaxdate()));
        cell2.setCellStyle(styleC23);
        HSSFCell cell3 = row.createCell(4);
        cell3.setCellValue(data.getCodeap());
        cell3.setCellStyle(styleC24);
        HSSFCell cell4 = row.createCell(5);
        cell4.setCellValue(data.getDescription().trim());
        cell4.setCellStyle(styleC24);
        HSSFCell cell5 = row.createCell(6);
        cell5.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getGrossamount()))
                ? (data.getGrossamount()).doubleValue()
                : 0);
        cell5.setCellStyle(styleC25);
        HSSFCell cell6 = row.createCell(7);
        cell6.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getVatamount()))
                ? (data.getVatamount()).doubleValue()
                : 0);
        cell6.setCellStyle(styleC25);
        HSSFCell cell7 = row.createCell(8);
        cell7.setCellValue(
                !"null".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (data.getAmount()).doubleValue()
                        : 0);
        cell7.setCellStyle(styleC25);
        HSSFCell cell8 = row.createCell(9);
        cell8.setCellValue(data.getFlagtype());
        cell8.setCellStyle(styleC23);
        HSSFCell cell9 = row.createCell(10);
        cell9.setCellValue(data.getInvoicetype());
        cell9.setCellStyle(styleC24);
        HSSFCell cell10 = row.createCell(11);
        cell10.setCellValue(data.getTaxno1());
        cell10.setCellStyle(styleC24);
        HSSFCell cell11 = row.createCell(12);
        cell11.setCellValue(data.getBranch());
        cell11.setCellStyle(styleC24);
        HSSFCell cell12 = row.createCell(13);
        cell12.setCellValue(data.getBranchno());
        cell12.setCellStyle(styleC23);

    }
    for (int j = 0; j < 15; j++) {
        sheet.autoSizeColumn(j);
    }
    sheet.setColumnWidth(5, 256 * 40);//27
    exportFileExcel("TaxInvoiceReport", wb);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »