Example usage for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn

Introduction

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

Prototype

@Override
public void autoSizeColumn(int column) 

Source Link

Document

Adjusts the column width to fit the contents.

This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.

Usage

From source file:com.sammyun.ExcelView.java

License:Open Source License

/**
 * ?Excel//  www.j  a  va  2  s.  c  om
 * 
 * @param model ?
 * @param workbook workbook
 * @param request request
 * @param response response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "M" + "o" + "S" + "ho" + "o" + "p"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

---

From source file:com.save.reports.ExportDataGridToExcel.java

public void workSheet() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample sheet");

    int rownum = 1;
    for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) {
        Row row = sheet.createRow(rownum);
        int cellcount = 0;
        if ((rownum - 1) == 0) {
            Row rowHeader = sheet.createRow(rownum - 1);
            for (Object propertyId : getDataGrid().getContainerDataSource().getContainerPropertyIds()) {
                Cell cell = rowHeader.createCell(cellcount);
                cell.setCellValue(propertyId.toString().toUpperCase());
                sheet.autoSizeColumn(cellcount);
                cellcount++;//from  ww w.jav a2  s.c o  m
            }
        }

        Item item = getDataGrid().getContainerDataSource().getItem(itemId);
        int cellnum = 0;
        for (Object propertyId : item.getItemPropertyIds()) {
            Cell cell = row.createCell(cellnum);
            if (propertyId.equals("employee")) {
                cell.setCellValue(item.getItemProperty(propertyId).getValue().toString().toUpperCase());
            } else {
                cell.setCellValue((item.getItemProperty(propertyId).getValue() == null) ? " "
                        : item.getItemProperty(propertyId).getValue().toString());
            }

            sheet.autoSizeColumn(cellnum);
            cellnum++;
        }
        rownum++;
    }

    try {
        Date date = new Date();
        file = File.createTempFile("file-" + date.getTime(), ".xls");
        FileOutputStream fos = new FileOutputStream(file.getAbsolutePath());
        workbook.write(fos);
        fos.flush();
        fos.close();
    } catch (FileNotFoundException ex) {
        ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
        Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
        Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
    }

}

---

From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java

public void getChangeARReport(HSSFWorkbook wb, List changeARReport) {
    List<ARNirvana> listAR = changeARReport;
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    // set Header Report (Row 1)
    HSSFCellStyle styleC1 = wb.createCellStyle();
    HSSFRow row1 = sheet.createRow(0);//from ww w. j a v a  2  s .  c  om
    HSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue("CHANGE AR REPORT");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cell1.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B1:I1"));

    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleNumber = wb.createCellStyle();
    styleNumber.setAlignment(styleC21.ALIGN_RIGHT);
    styleNumber.setDataFormat(currency.getFormat("#,##0.00"));
    //        styleNumber.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0"));

    ARNirvana arTemp = new ARNirvana();
    if (!listAR.isEmpty()) {
        arTemp = (ARNirvana) listAR.get(0);
    }
    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("Print on : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(2);
    Date date = new Date();
    SimpleDateFormat sm = new SimpleDateFormat("dd/MM/yyyy");
    String strDate = sm.format(date);
    cell22.setCellValue(strDate);
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("C2:E2"));

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(1);
    cell31.setCellValue("Report Of : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(2);
    cell32.setCellValue(arTemp.getPrintofdatePage());
    cell32.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("C3:D3"));

    // Row 4
    HSSFRow row4 = sheet.createRow(3);
    HSSFCell cell41 = row4.createCell(1);
    cell41.setCellValue("Department : ");
    cell41.setCellStyle(styleC21);
    HSSFCell cell42 = row4.createCell(2);
    cell42.setCellValue(arTemp.getDepartmentPage());
    cell42.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("C4:E4"));

    // Header Table
    HSSFCellStyle styleHeader = wb.createCellStyle();
    styleHeader.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleHeader.setAlignment(styleHeader.ALIGN_CENTER);
    styleHeader.setBorderTop(styleHeader.BORDER_THIN);
    styleHeader.setBorderLeft(styleHeader.BORDER_THIN);
    styleHeader.setBorderBottom(styleHeader.BORDER_THIN);
    styleHeader.setBorderRight(styleHeader.BORDER_THIN);
    HSSFCellStyle styleDetailTable = wb.createCellStyle();
    styleDetailTable.setAlignment(styleDetailTable.ALIGN_LEFT);
    styleDetailTable.setBorderLeft(styleDetailTable.BORDER_THIN);
    styleDetailTable.setBorderRight(styleDetailTable.BORDER_THIN);
    HSSFCellStyle styleDetailTableNumber = wb.createCellStyle();
    styleDetailTableNumber.setAlignment(styleDetailTableNumber.ALIGN_RIGHT);
    styleDetailTableNumber.setBorderLeft(styleDetailTableNumber.BORDER_THIN);
    styleDetailTableNumber.setBorderRight(styleDetailTableNumber.BORDER_THIN);
    styleDetailTableNumber.setDataFormat(currency.getFormat("#,##0.00"));
    HSSFCellStyle styleDetailTableBorderBottom = wb.createCellStyle();
    styleDetailTableBorderBottom.setBorderTop(styleDetailTableBorderBottom.BORDER_THIN);

    HSSFRow row6 = sheet.createRow(8);
    HSSFCell cell61 = row6.createCell(1);
    cell61.setCellValue("No.");
    cell61.setCellStyle(styleHeader);
    sheet.autoSizeColumn(1);
    HSSFCell cell62 = row6.createCell(2);
    cell62.setCellValue("Inv No");
    cell62.setCellStyle(styleHeader);
    sheet.autoSizeColumn(2);
    HSSFCell cell63 = row6.createCell(3);
    cell63.setCellValue("AR Code");
    cell63.setCellStyle(styleHeader);
    sheet.autoSizeColumn(3);
    HSSFCell cell64 = row6.createCell(4);
    cell64.setCellValue("Inv To");
    cell64.setCellStyle(styleHeader);
    sheet.autoSizeColumn(4);
    HSSFCell cell65 = row6.createCell(5);
    cell65.setCellValue("Acc Code");
    cell65.setCellStyle(styleHeader);
    sheet.autoSizeColumn(5);
    HSSFCell cell66 = row6.createCell(6);
    cell66.setCellValue("Gross");
    cell66.setCellStyle(styleHeader);
    sheet.autoSizeColumn(6);
    HSSFCell cell67 = row6.createCell(7);
    cell67.setCellValue("Amount");
    cell67.setCellStyle(styleHeader);
    sheet.autoSizeColumn(7);
    HSSFCell cell68 = row6.createCell(8);
    cell68.setCellValue("Cur");
    cell68.setCellStyle(styleHeader);
    sheet.autoSizeColumn(8);

    int count = 9 + listAR.size();
    int start = 11;
    int end = 0;
    int num = 0;
    for (int r = 9; r < count; r++) {
        if (num <= (listAR.size() - 1)) {
            HSSFRow row = sheet.createRow(r);
            HSSFCell cell5 = row.createCell(1);
            cell5.setCellValue((num + 1));
            cell5.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(1);
            HSSFCell cell6 = row.createCell(2);
            cell6.setCellValue(listAR.get(num).getIntreference());
            cell6.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(2);
            HSSFCell cell7 = row.createCell(3);
            if (listAR.get(num).getCustomerid() != null) {
                cell7.setCellValue(listAR.get(num).getCustomerid());
                sheet.autoSizeColumn(3);
            } else {
                cell7.setCellValue("");
                sheet.autoSizeColumn(3);
            }
            cell7.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(3);
            HSSFCell cell8 = row.createCell(4);
            if (listAR.get(num).getCustomername() != null) {
                cell8.setCellValue(listAR.get(num).getCustomername());
                sheet.autoSizeColumn(4);
            } else {
                cell8.setCellValue("");
                sheet.autoSizeColumn(4);
            }
            cell8.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(4);
            HSSFCell cell9 = row.createCell(5);
            if (listAR.get(num).getSalesaccount1() != null) {
                cell9.setCellValue(listAR.get(num).getSalesaccount1());
                sheet.autoSizeColumn(5);
            } else {
                cell9.setCellValue("");
                sheet.autoSizeColumn(5);
            }
            cell9.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(5);
            HSSFCell cell10 = row.createCell(6);
            if (listAR.get(num).getSalesamt() != null) {
                cell10.setCellValue(listAR.get(num).getSalesamt().doubleValue());
                sheet.autoSizeColumn(6);
            } else {
                cell10.setCellValue(0.00);
                sheet.autoSizeColumn(6);
            }
            cell10.setCellStyle(styleDetailTableNumber);
            sheet.autoSizeColumn(6);
            HSSFCell cell11 = row.createCell(7);
            if (listAR.get(num).getAramt() != null) {
                cell11.setCellValue(listAR.get(num).getAramt().doubleValue());
                sheet.autoSizeColumn(7);
            } else {
                cell11.setCellValue(0.00);
                sheet.autoSizeColumn(7);
            }
            cell11.setCellStyle(styleDetailTableNumber);
            HSSFCell cell12 = row.createCell(8);
            cell12.setCellValue(listAR.get(num).getCurrencyid());
            cell12.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(8);
            num++;
        }
    }

    HSSFRow row = sheet.createRow(count);
    row.createCell(1).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(2).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(3).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(4).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(5).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(6).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(7).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(8).setCellStyle(styleDetailTableBorderBottom);

}

---

From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java

public void genCollectionReport(HSSFWorkbook wb, List collectionNirvanaList) {
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    CollectionNirvana dataheader = new CollectionNirvana();
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    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(styleC22.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"));
    styleC25.setWrapText(true);/*ww w  .  j a  v  a  2  s .  co  m*/
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    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);
    styleC26.setWrapText(true);
    styleC26.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

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

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

    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.setWrapText(true);
    styleC29.setVerticalAlignment(HSSFCellStyle.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.setWrapText(true);
    styleC30.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    if (!collectionNirvanaList.isEmpty()) {
        dataheader = (CollectionNirvana) collectionNirvanaList.get(0);

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

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Print On : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getSystemdate());
        cell22.setCellStyle(styleC22);
        //            sheet.addMergedRegion(CellRangeAddress.valueOf("B2:C2"));
        HSSFCell cell23 = row2.createCell(2);
        cell23.setCellValue("By : " + dataheader.getUser());
        cell23.setCellStyle(styleC22);
        //            HSSFCell cell24 = row2.createCell(3);
        //            cell24.setCellValue(dataheader.getUser());
        //            cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("Report of : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        if (!"".equalsIgnoreCase(dataheader.getFrom())) {
            cell32.setCellValue(dataheader.getFrom());
            cell32.setCellStyle(styleC22);
            //            sheet.addMergedRegion(CellRangeAddress.valueOf("B3:C3"));
        }
        //            HSSFCell cell33 = row3.createCell(2);
        //            cell33.setCellValue("To : ");
        //            cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(2);
        if (!"".equalsIgnoreCase(dataheader.getTo())) {
            cell34.setCellValue("To : " + dataheader.getTo());
            cell34.setCellStyle(styleC22);
        }

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

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

        HSSFRow row6 = sheet.createRow(5);
        HSSFCell cell61 = row6.createCell(0);
        cell61.setCellValue("No");
        cell61.setCellStyle(styleC3);
        sheet.autoSizeColumn(0);
        HSSFCell cell62 = row6.createCell(1);
        cell62.setCellValue("Receipt No");
        cell62.setCellStyle(styleC3);
        sheet.autoSizeColumn(1);
        HSSFCell cell63 = row6.createCell(2);
        cell63.setCellValue("Inv No");
        sheet.autoSizeColumn(2);
        cell63.setCellStyle(styleC3);
        HSSFCell cell64 = row6.createCell(3);
        cell64.setCellValue("Ar Code");
        cell64.setCellStyle(styleC3);
        sheet.autoSizeColumn(3);
        HSSFCell cell65 = row6.createCell(4);
        cell65.setCellValue("Inv To");
        cell65.setCellStyle(styleC3);
        sheet.autoSizeColumn(4);
        HSSFCell cell66 = row6.createCell(5);
        cell66.setCellValue("Acc Code");
        cell66.setCellStyle(styleC3);
        sheet.autoSizeColumn(5);
        HSSFCell cell67 = row6.createCell(6);
        cell67.setCellValue("Inv Amount");
        cell67.setCellStyle(styleC3);
        sheet.autoSizeColumn(6);
        HSSFCell cell68 = row6.createCell(7);
        cell68.setCellValue("Sum Inv");
        cell68.setCellStyle(styleC3);
        sheet.autoSizeColumn(7);
        HSSFCell cell69 = row6.createCell(8);
        cell69.setCellValue("Diff");
        cell69.setCellStyle(styleC3);
        sheet.autoSizeColumn(8);
        HSSFCell cell70 = row6.createCell(9);
        cell70.setCellValue("Sum Rec");
        cell70.setCellStyle(styleC3);
        sheet.autoSizeColumn(9);
        HSSFCell cell71 = row6.createCell(10);
        cell71.setCellValue("Cur");
        cell71.setCellStyle(styleC3);
        sheet.autoSizeColumn(10);
        HSSFCell cell72 = row6.createCell(11);
        cell72.setCellValue("Withholding Tax");
        cell72.setCellStyle(styleC3);
        sheet.autoSizeColumn(11);
        HSSFCell cell73 = row6.createCell(12);
        cell73.setCellValue("Pay By");
        cell73.setCellStyle(styleC3);
        sheet.autoSizeColumn(12);
        HSSFCell cell74 = row6.createCell(13);
        cell74.setCellValue("Status");
        cell74.setCellStyle(styleC3);
        sheet.autoSizeColumn(13);
    }
    //Detail of Table
    int count = 6;
    int no = 1;
    for (int i = 0; i < collectionNirvanaList.size(); i++) {
        CollectionNirvana data = (CollectionNirvana) collectionNirvanaList.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(String.valueOf(no));
        celldata0.setCellStyle(styleC30);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(String.valueOf(data.getRecno()));
        celldata1.setCellStyle(styleC29);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(String.valueOf(data.getInvno()));
        celldata2.setCellStyle(styleC29);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(String.valueOf(data.getArcode()));
        celldata3.setCellStyle(styleC29);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(String.valueOf(data.getInvto()));
        celldata4.setCellStyle(styleC29);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(String.valueOf(data.getAcccode()));
        celldata5.setCellStyle(styleC29);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(String.valueOf(data.getInvoiceamount()));
        celldata6.setCellStyle(styleC25);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInvamount())) ? 0
                : (data.getInvamount()).doubleValue());
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue(
                "".equalsIgnoreCase(String.valueOf(data.getDiff())) ? 0 : (data.getDiff()).doubleValue());
        celldata8.setCellStyle(styleC25);

        HSSFCell celldata9 = row.createCell(9);
        celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getRecamount())) ? 0
                : (data.getRecamount()).doubleValue());
        celldata9.setCellStyle(styleC25);

        HSSFCell celldata10 = row.createCell(10);
        celldata10.setCellValue(String.valueOf(data.getCur()));
        celldata10.setCellStyle(styleC30);

        HSSFCell celldata11 = row.createCell(11);
        celldata11.setCellValue(
                "".equalsIgnoreCase(String.valueOf(data.getWithtax())) ? 0 : (data.getWithtax()).doubleValue());
        celldata11.setCellStyle(styleC25);

        HSSFCell celldata12 = row.createCell(12);
        celldata12.setCellValue(String.valueOf(data.getPayby()));
        celldata12.setCellStyle(styleC29);

        HSSFCell celldata13 = row.createCell(13);
        celldata13.setCellValue(String.valueOf(data.getCollectionStatus()));
        celldata13.setCellStyle(styleC30);

        if (i == (collectionNirvanaList.size() - 1)) {
            row = sheet.createRow(count + i + 3);
            HSSFCellStyle styleSum = wb.createCellStyle();
            styleSum.setAlignment(styleSum.ALIGN_RIGHT);
            styleSum.setDataFormat(currency.getFormat("#,##0.00"));

            HSSFCell cellTotal00 = row.createCell(1);
            cellTotal00.setCellValue("Cash : ");
            cellTotal00.setCellStyle(styleSum);
            HSSFCell cellTotal01 = row.createCell(2);
            cellTotal01.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(data.getCash())) ? 0 : (data.getCash()).doubleValue());
            cellTotal01.setCellStyle(styleSum);
            HSSFCell cellTotal000 = row.createCell(11);
            cellTotal000.setCellValue("Total Amount : ");
            cellTotal000.setCellStyle(styleSum);
            HSSFCell cellTotal001 = row.createCell(12);
            cellTotal001.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamount())) ? 0
                    : (data.getTotalamount()).doubleValue());
            cellTotal001.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 4);
            HSSFCell cellTotal02 = row.createCell(1);
            cellTotal02.setCellValue("Chq : ");
            cellTotal02.setCellStyle(styleSum);
            HSSFCell cellTotal03 = row.createCell(2);
            cellTotal03.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(data.getChq())) ? 0 : (data.getChq()).doubleValue());
            cellTotal03.setCellStyle(styleSum);
            HSSFCell cellTotal002 = row.createCell(11);
            cellTotal002.setCellValue("Total Amount (Wait) : ");
            cellTotal002.setCellStyle(styleSum);
            HSSFCell cellTotal003 = row.createCell(12);
            cellTotal003.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountwait())) ? 0
                    : (data.getTotalamountwait()).doubleValue());
            cellTotal003.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 5);
            HSSFCell cellTotal04 = row.createCell(1);
            cellTotal04.setCellValue("Credit Card : ");
            cellTotal04.setCellStyle(styleSum);
            HSSFCell cellTotal05 = row.createCell(2);
            cellTotal05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCredit())) ? 0
                    : (data.getCredit()).doubleValue());
            cellTotal05.setCellStyle(styleSum);
            HSSFCell cellTotal004 = row.createCell(11);
            cellTotal004.setCellValue("Total Amount (Void) : ");
            cellTotal004.setCellStyle(styleSum);
            HSSFCell cellTotal005 = row.createCell(12);
            cellTotal005.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountvoid())) ? 0
                    : (data.getTotalamountvoid()).doubleValue());
            cellTotal005.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 6);
            HSSFCell cellTotal06 = row.createCell(1);
            cellTotal06.setCellValue("Bank Transfer : ");
            cellTotal06.setCellStyle(styleSum);
            HSSFCell cellTotal07 = row.createCell(2);
            cellTotal07.setCellValue("".equalsIgnoreCase(String.valueOf(data.getBanktransfer())) ? 0
                    : (data.getBanktransfer()).doubleValue());
            cellTotal07.setCellStyle(styleSum);
            HSSFCell cellTotal006 = row.createCell(11);
            cellTotal006.setCellValue("Total Amount (Invoice) : ");
            cellTotal006.setCellStyle(styleSum);
            HSSFCell cellTotal007 = row.createCell(12);
            cellTotal007.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountinvoice())) ? 0
                    : (data.getTotalamountinvoice()).doubleValue());
            cellTotal007.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 8);
            HSSFCell cellTotal08 = row.createCell(1);
            cellTotal08.setCellValue("W/T : ");
            cellTotal08.setCellStyle(styleSum);
            HSSFCell cellTotal09 = row.createCell(2);
            cellTotal09.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(data.getWt())) ? 0 : (data.getWt()).doubleValue());
            cellTotal09.setCellStyle(styleSum);
            HSSFCell cellTotal008 = row.createCell(11);
            cellTotal008.setCellValue("Total Amount (Diff) : ");
            cellTotal008.setCellStyle(styleSum);
            HSSFCell cellTotal009 = row.createCell(12);
            cellTotal009.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountdiff())) ? 0
                    : (data.getTotalamountdiff()).doubleValue());
            cellTotal009.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 9);
            HSSFCell cellTotal10 = row.createCell(1);
            cellTotal10.setCellValue("Cash (---) : ");
            cellTotal10.setCellStyle(styleSum);
            HSSFCell cellTotal11 = row.createCell(2);
            cellTotal11.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCashminus())) ? 0
                    : (data.getCashminus()).doubleValue());
            cellTotal11.setCellStyle(styleSum);
        }

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

---

From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java

private void getApReport(HSSFWorkbook wb, List ApNirvana) {
    UtilityFunction util = new UtilityFunction();
    SimpleDateFormat df = new SimpleDateFormat();
    df.applyPattern("dd/MM/yyyy");
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    APNirvana dataheader = new APNirvana();

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

    if (ApNirvana != null) {
        dataheader = (APNirvana) ApNirvana.get(0);
    }/* w  w  w .  j  a v a  2  s  .  c o m*/

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

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

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(0);
    cell31.setCellValue("Report of : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(1);
    cell32.setCellValue(dataheader.getDatefrom());
    cell32.setCellStyle(styleC22);
    if (!"".equalsIgnoreCase(dataheader.getDateto())) {
        HSSFCell cell33 = row3.createCell(2);
        cell33.setCellValue("To");
        cell33.setCellStyle(styleC20);
        HSSFCell cell34 = row3.createCell(3);
        cell34.setCellValue(dataheader.getDateto());
        cell34.setCellStyle(styleC22);
    }
    //        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));

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

    // Row 5
    HSSFRow row5 = sheet.createRow(4);
    HSSFCell cell51 = row5.createCell(0);
    cell51.setCellValue("Page : ");
    cell51.setCellStyle(styleC21);
    HSSFCell cell52 = row5.createCell(5);
    cell52.setCellValue("");
    cell52.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));

    // 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 row6 = sheet.createRow(8);
    HSSFCell cell61 = row6.createCell(0);
    cell61.setCellValue("No.");
    cell61.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(0);
    HSSFCell cell62 = row6.createCell(1);
    cell62.setCellValue("Ref InvNo");
    cell62.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(1);
    HSSFCell cell63 = row6.createCell(2);
    cell63.setCellValue("Invoice Date");
    sheet.autoSizeColumn(2);
    cell63.setCellStyle(styleC3Center);
    HSSFCell cell64 = row6.createCell(3);
    cell64.setCellValue("Pay No.");
    cell64.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(3);
    HSSFCell cell65 = row6.createCell(4);
    cell65.setCellValue("Ap Code");
    cell65.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(4);
    HSSFCell cell66 = row6.createCell(5);
    cell66.setCellValue("Invoice Sup");
    cell66.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(5);

    HSSFCellStyle styleC3Right = wb.createCellStyle();
    styleC3Right.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Right.setAlignment(styleC3Right.ALIGN_RIGHT);
    styleC3Right.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3Right.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3Right.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3Right.setBorderTop(HSSFCellStyle.BORDER_THIN);
    HSSFCell cell67 = row6.createCell(6);
    cell67.setCellValue("Acc Code");
    cell67.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(6);
    HSSFCell cell68 = row6.createCell(7);
    cell68.setCellValue("Cur");
    cell68.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(7);
    HSSFCell cell69 = row6.createCell(8);
    cell69.setCellValue("Gross");
    cell69.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(8);
    HSSFCell cell70 = row6.createCell(9);
    cell70.setCellValue("Amount");
    cell70.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(9);

    //Detail of Table
    int count = 9;
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC22.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 < ApNirvana.size(); i++) {
        APNirvana data = (APNirvana) ApNirvana.get(i);
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(i + 1);
        cell0.setCellStyle(styleC23);
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(data.getRefinvoiceno());
        cell1.setCellStyle(styleC24);
        HSSFCell cell2 = row.createCell(2);
        cell2.setCellValue(
                data.getTransdate() == null ? "" : util.ConvertString(df.format(data.getTransdate())));
        cell2.setCellStyle(styleC24);
        HSSFCell cell3 = row.createCell(3);
        cell3.setCellValue(data.getPayno());
        cell3.setCellStyle(styleC24);
        HSSFCell cell4 = row.createCell(4);
        cell4.setCellValue(data.getVendorid());
        cell4.setCellStyle(styleC24);
        HSSFCell cell5 = row.createCell(5);
        cell5.setCellValue(data.getVendorname());
        cell5.setCellStyle(styleC24);
        HSSFCell cell6 = row.createCell(6);
        cell6.setCellValue(data.getPuraccount1());
        cell6.setCellStyle(styleC24);
        HSSFCell cell7 = row.createCell(7);
        cell7.setCellValue(data.getCurrencyid());
        cell7.setCellStyle(styleC23);
        HSSFCell cell8 = row.createCell(8);
        cell8.setCellValue((data.getVatamt() != null) ? data.getVatamt().doubleValue()
                : new BigDecimal("0").doubleValue());
        cell8.setCellStyle(styleC25);
        HSSFCell cell9 = row.createCell(9);
        cell9.setCellValue((data.getBasevatamt() != null) ? data.getBasevatamt().doubleValue()
                : new BigDecimal("0").doubleValue());
        cell9.setCellStyle(styleC25);

        if (i == (ApNirvana.size() - 1)) {
            row = sheet.createRow(count + i + 1);
            for (int k = 0; k < 7; k++) {
                HSSFCellStyle styleSum = wb.createCellStyle();
                styleSum.setAlignment(styleC24.ALIGN_RIGHT);
                styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN);
                styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                HSSFCell cellSum = row.createCell(k);
                if (k == 0) {
                    styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                }
                cellSum.setCellStyle(styleSum);
            }
            HSSFCellStyle styleSum = wb.createCellStyle();
            styleSum.setAlignment(styleSum.ALIGN_RIGHT);
            styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            styleSum.setDataFormat(currency.getFormat("#,##0.00"));

            String sumGross = "SUM(I" + 10 + ":I" + (count + i + 1) + ")";
            String sumAmount = "SUM(J" + 10 + ":J" + (count + i + 1) + ")";

            HSSFCell cell5Sum = row.createCell(7);
            cell5Sum.setCellValue("Total");
            cell5Sum.setCellStyle(styleSum);
            HSSFCell cell6Sum = row.createCell(8);
            cell6Sum.setCellFormula(sumGross);
            cell6Sum.setCellStyle(styleSum);
            HSSFCell cell7Sum = row.createCell(9);
            cell7Sum.setCellFormula(sumAmount);
            cell7Sum.setCellStyle(styleSum);

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

---

From source file:com.smi.travel.controller.excel.booking.BookingSummary.java

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

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    styleC21.setDataFormat(currency.getFormat("#,##0"));
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC22.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"));
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setWrapText(true);//w w  w  . j a  va  2  s .  c o m
    styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER);
    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);
    styleC26.setWrapText(true);
    styleC26.setVerticalAlignment(styleC26.VERTICAL_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.setWrapText(true);
    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.setWrapText(true);
    styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER);
    if (!listBooking.isEmpty()) {
        dataheader = (BookingNonInvoiceView) listBooking.get(0);

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

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Owner : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getHeaderowner());
        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.getHeaderinvoicesup());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("Booking Date : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeaderbookingdate());
        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.getHeaderpaydate());
        cell34.setCellStyle(styleC22);

    }

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

    // Detail of Table
    String temp = "";
    int count = 4;
    int ktemp = 7;

    for (int i = 0; i < listBooking.size(); i++) {
        BookingNonInvoiceView data = (BookingNonInvoiceView) listBooking.get(i);
        if (!temp.equalsIgnoreCase(data.getInvoicesup())) {
            if (!"".equalsIgnoreCase(temp)) {
                count = count + 2;
                ktemp = count + 3 + i;
            }

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

            // Row Inv Sup
            HSSFRow row000 = sheet.createRow(counts);
            HSSFCell cell001 = row000.createCell(0);
            cell001.setCellValue("Invoice Sup ");
            cell001.setCellStyle(styleC3);
            HSSFCell cell002 = row000.createCell(1);
            cell002.setCellValue(data.getInvoicesup().replaceAll("\r\n", " "));
            cell002.setCellStyle(styleC29);
            HSSFCell cell003 = row000.createCell(2);
            cell003.setCellStyle(styleC29);
            HSSFCell cell004 = row000.createCell(3);
            cell004.setCellStyle(styleC29);

            sheet.addMergedRegion(CellRangeAddress.valueOf("B" + (counts + 1) + ":D" + (counts + 1)));

            temp = data.getInvoicesup();

            // Header Table
            HSSFRow row09 = sheet.createRow(countss);
            HSSFCell cell091 = row09.createCell(0);
            cell091.setCellValue("Ref No");
            cell091.setCellStyle(styleC3);
            sheet.autoSizeColumn(0);
            HSSFCell cell092 = row09.createCell(1);
            cell092.setCellValue("Booking Date");
            cell092.setCellStyle(styleC3);
            sheet.autoSizeColumn(1);
            HSSFCell cell093 = row09.createCell(2);
            cell093.setCellValue("Owner");
            sheet.autoSizeColumn(2);
            cell093.setCellStyle(styleC3);
            HSSFCell cell094 = row09.createCell(3);
            cell094.setCellValue("Pay No");
            cell094.setCellStyle(styleC3);
            sheet.autoSizeColumn(3);
            HSSFCell cell095 = row09.createCell(4);
            cell095.setCellValue("Pay Date");
            cell095.setCellStyle(styleC3);
            sheet.autoSizeColumn(4);
            //                HSSFCell cell096 = row09.createCell(5);
            //                cell096.setCellValue("Invoice Sup"); 
            //                cell096.setCellStyle(styleC3);
            //                sheet.autoSizeColumn(5);
            HSSFCell cell097 = row09.createCell(5);
            cell097.setCellValue("Description");
            cell097.setCellStyle(styleC3);
            sheet.autoSizeColumn(5);
            HSSFCell cell098 = row09.createCell(6);
            cell098.setCellValue("Pay Amount");
            cell098.setCellStyle(styleC3);
            sheet.autoSizeColumn(6);
            HSSFCell cell099 = row09.createCell(7);
            cell099.setCellValue("Currency");
            cell099.setCellStyle(styleC3);
            sheet.autoSizeColumn(7);
            HSSFCell cell100 = row09.createCell(8);
            cell100.setCellValue("Price From Billable");
            cell100.setCellStyle(styleC3);
            sheet.autoSizeColumn(8);
            HSSFCell cell101 = row09.createCell(9);
            cell101.setCellValue("Currency");
            cell101.setCellStyle(styleC3);
            sheet.autoSizeColumn(9);
            count = count + 2;
        }

        //set data 
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell celldata01 = row.createCell(0);
        celldata01.setCellValue(data.getRefno());
        celldata01.setCellStyle(styleC29);
        HSSFCell celldata02 = row.createCell(1);
        celldata02.setCellValue(data.getBookdate());
        celldata02.setCellStyle(styleC29);
        HSSFCell celldata03 = row.createCell(2);
        celldata03.setCellValue(data.getOwner());
        celldata03.setCellStyle(styleC29);
        HSSFCell celldata04 = row.createCell(3);
        celldata04.setCellValue(data.getPayno());
        celldata04.setCellStyle(styleC29);
        HSSFCell celldata05 = row.createCell(4);
        celldata05.setCellValue(data.getPaydate());
        celldata05.setCellStyle(styleC29);
        //            HSSFCell celldata06 = row.createCell(5);
        //            celldata06.setCellValue(data.getInvoicesup());
        //            celldata06.setCellStyle(styleC29);
        HSSFCell celldata07 = row.createCell(5);
        celldata07.setCellValue(data.getDescription());
        celldata07.setCellStyle(styleC29);
        HSSFCell celldata08 = row.createCell(6);
        celldata08.setCellValue(data.getPayamount().trim());
        celldata08.setCellStyle(styleC25);
        HSSFCell celldata09 = row.createCell(7);
        celldata09.setCellValue(data.getCurrency());
        celldata09.setCellStyle(styleC30);
        HSSFCell celldata10 = row.createCell(8);
        celldata10.setCellValue(data.getSale().trim());
        celldata10.setCellStyle(styleC25);
        HSSFCell celldata11 = row.createCell(9);
        celldata11.setCellValue(data.getSalecurrency());
        celldata11.setCellStyle(styleC30);
    }

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

---

From source file:com.smi.travel.controller.excel.booking.BookingSummary.java

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

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    styleC21.setDataFormat(currency.getFormat("#,##0"));
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC22.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"));
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER);

    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);
    styleC26.setVerticalAlignment(styleC26.VERTICAL_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.setWrapText(true);//  w  w w  . ja  va2s  .c  o  m
    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.setWrapText(true);
    styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER);

    if (!listBooking.isEmpty()) {
        dataheader = (BookingInvoiceView) listBooking.get(0);

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

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Owner : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getHeaderowner());
        cell22.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B2:C2"));
        HSSFCell cell23 = row2.createCell(3);
        cell23.setCellValue("Invoice To : ");
        cell23.setCellStyle(styleC21);
        HSSFCell cell24 = row2.createCell(4);
        cell24.setCellValue(dataheader.getHeaderinvto());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("Booking Date : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeaderbookingdate());
        cell32.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:C3"));
        HSSFCell cell33 = row3.createCell(3);
        cell33.setCellValue("Invoice Date : ");
        cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(4);
        cell34.setCellValue(dataheader.getHeaderinvdate());
        cell34.setCellStyle(styleC22);

    }

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

    // Header Table
    HSSFRow row09 = sheet.createRow(4);
    HSSFCell cell091 = row09.createCell(0);
    cell091.setCellValue("Ref No");
    cell091.setCellStyle(styleC3);
    sheet.autoSizeColumn(0);
    HSSFCell cell092 = row09.createCell(1);
    cell092.setCellValue("Booking Date");
    cell092.setCellStyle(styleC3);
    sheet.autoSizeColumn(1);
    HSSFCell cell093 = row09.createCell(2);
    cell093.setCellValue("Owner");
    sheet.autoSizeColumn(2);
    cell093.setCellStyle(styleC3);
    HSSFCell cell094 = row09.createCell(3);
    cell094.setCellValue("Description");
    cell094.setCellStyle(styleC3);
    sheet.autoSizeColumn(3);
    HSSFCell cell095 = row09.createCell(4);
    cell095.setCellValue("Inv No");
    cell095.setCellStyle(styleC3);
    sheet.autoSizeColumn(4);
    HSSFCell cell096 = row09.createCell(5);
    cell096.setCellValue("Inv Date");
    cell096.setCellStyle(styleC3);
    sheet.autoSizeColumn(5);
    HSSFCell cell097 = row09.createCell(6);
    cell097.setCellValue("Inv To");
    cell097.setCellStyle(styleC3);
    sheet.autoSizeColumn(6);
    HSSFCell cell098 = row09.createCell(7);
    cell098.setCellValue("Cost From Billable");
    cell098.setCellStyle(styleC3);
    sheet.autoSizeColumn(7);
    HSSFCell cell099 = row09.createCell(8);
    cell099.setCellValue("Currency");
    cell099.setCellStyle(styleC3);
    sheet.autoSizeColumn(8);

    // Detail of Table
    int count = 5;
    for (int i = 0; i < listBooking.size(); i++) {
        BookingInvoiceView data = (BookingInvoiceView) listBooking.get(i);
        //set data 
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell celldata01 = row.createCell(0);
        celldata01.setCellValue(data.getRefno());
        celldata01.setCellStyle(styleC29);
        HSSFCell celldata02 = row.createCell(1);
        celldata02.setCellValue(data.getBookdate());
        celldata02.setCellStyle(styleC30);
        HSSFCell celldata03 = row.createCell(2);
        celldata03.setCellValue(data.getOwner());
        celldata03.setCellStyle(styleC29);
        HSSFCell celldata04 = row.createCell(3);
        celldata04.setCellValue(data.getDescription());
        celldata04.setCellStyle(styleC29);
        HSSFCell celldata05 = row.createCell(4);
        celldata05.setCellValue(data.getInvno());
        celldata05.setCellStyle(styleC29);
        HSSFCell celldata06 = row.createCell(5);
        celldata06.setCellValue(data.getInvdate());
        celldata06.setCellStyle(styleC29);
        HSSFCell celldata07 = row.createCell(6);
        celldata07.setCellValue(data.getInvto());
        celldata07.setCellStyle(styleC29);
        HSSFCell celldata08 = row.createCell(7);
        celldata08.setCellValue(data.getCost().trim());
        celldata08.setCellStyle(styleC25);
        HSSFCell celldata09 = row.createCell(8);
        celldata09.setCellValue(data.getCurrency());
        celldata09.setCellStyle(styleC30);

    }
    for (int j = 0; j < 10; j++) {
        sheet.autoSizeColumn(j);
    }
    sheet.setColumnWidth(4, 256 * 15);
    sheet.setColumnWidth(5, 256 * 15);
}

---

From source file:com.smi.travel.controller.excel.booking.OutboundHotelSummary.java

private void genOutboundHotelSummaryReport(HSSFWorkbook wb, List list) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    OutboundHotelSummaryView dataheader = new OutboundHotelSummaryView();

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    styleC21.setDataFormat(currency.getFormat("#,##0.00"));
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    if (!list.isEmpty()) {
        dataheader = (OutboundHotelSummaryView) list.get(0);

        // set Header Report (Row 1)
        HSSFCellStyle styleC1 = wb.createCellStyle();
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("Hotel Summary");
        styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
        cell1.setCellStyle(styleC1);/*from  w w w  .  j av  a 2 s .c o m*/
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Country : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getHeadcountry());
        cell22.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
        HSSFCell cell23 = row2.createCell(4);
        cell23.setCellValue("Pay By : ");
        cell23.setCellStyle(styleC21);
        HSSFCell cell24 = row2.createCell(5);
        cell24.setCellValue(dataheader.getHeadpayby());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("City : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeadcity());
        cell32.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
        HSSFCell cell33 = row3.createCell(4);
        cell33.setCellValue("Bank Transfer : ");
        cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(5);
        cell34.setCellValue(dataheader.getHeadbanktransfer());
        cell34.setCellStyle(styleC22);

        // Row 4
        HSSFRow row4 = sheet.createRow(3);
        HSSFCell cell41 = row4.createCell(0);
        cell41.setCellValue("Hotel : ");
        cell41.setCellStyle(styleC21);
        HSSFCell cell42 = row4.createCell(1);
        cell42.setCellValue(dataheader.getHeadhotel());
        cell42.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));
        HSSFCell cell43 = row4.createCell(4);
        cell43.setCellValue("Status : ");
        cell43.setCellStyle(styleC21);
        HSSFCell cell44 = row4.createCell(5);
        cell44.setCellValue(dataheader.getHeadstatus());
        cell44.setCellStyle(styleC22);

        // Row 5
        HSSFRow row5 = sheet.createRow(4);
        HSSFCell cell51 = row5.createCell(0);
        cell51.setCellValue("Date : ");
        cell51.setCellStyle(styleC21);
        HSSFCell cell52 = row5.createCell(1);
        cell52.setCellValue(dataheader.getHeaddate());
        cell52.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));

    }

    // 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);
    //        
    //        for(int i = 0 ; i < 20 ; i++){
    //            HSSFRow row4 = sheet.createRow(4);
    //            HSSFCell cell = row4.createCell(i);
    //            cell.setCellStyle(stylebottom);
    //        }

    HSSFRow row6 = sheet.createRow(6);
    HSSFCell cell61 = row6.createCell(0);
    cell61.setCellValue("SALE DATE");
    cell61.setCellStyle(styletop);
    HSSFCell cell62 = row6.createCell(1);
    cell62.setCellValue("RECORD NO");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row6.createCell(2);
    cell63.setCellValue("REF NO");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row6.createCell(3);
    cell64.setCellValue("HOTEL NAME");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row6.createCell(4);
    cell65.setCellValue("CUSTOMER NAME");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row6.createCell(5);
    cell66.setCellValue("PERIOD");
    cell66.setCellStyle(styletop);
    sheet.autoSizeColumn(5);
    HSSFCell cell67 = row6.createCell(6);
    cell67.setCellValue("PAX");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row6.createCell(7);
    cell68.setCellValue("NETT");
    cell68.setCellStyle(styletop);
    HSSFCell cell69 = row6.createCell(8);
    cell69.setCellValue("SELLING");
    cell69.setCellStyle(styletop);
    HSSFCell cell610 = row6.createCell(9);
    cell610.setCellValue("TOTAL");
    cell610.setCellStyle(styletop);
    HSSFCell cell611 = row6.createCell(10);
    cell611.setCellValue("SALE");
    cell611.setCellStyle(styletop);
    HSSFCell cell612 = row6.createCell(11);
    cell612.setCellValue("PROFIT");
    cell612.setCellStyle(styletop);
    HSSFCell cell613 = row6.createCell(12);
    cell613.setCellValue("BANK");
    cell613.setCellStyle(styletop);
    HSSFCell cell614 = row6.createCell(13);
    cell614.setCellValue("DATE");
    cell614.setCellStyle(styletop);
    HSSFCell cell615 = row6.createCell(14);
    cell615.setCellValue("SUPPLIER");
    cell615.setCellStyle(styletop);
    HSSFCell cell616 = row6.createCell(15);
    cell616.setCellValue("STATUS");
    cell616.setCellStyle(styletop);
    HSSFCell cell617 = row6.createCell(16);
    cell617.setCellValue("REMARK");
    cell617.setCellStyle(styletop);
    HSSFCell cell618 = row6.createCell(17);
    cell618.setCellValue("SALLER");
    cell618.setCellStyle(styletop);

    sheet.addMergedRegion(CellRangeAddress.valueOf("A7:A8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("B7:B8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("C7:C8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("D7:D8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("E7:E8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("F7:F8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("G7:G8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("O7:O8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("P7:P8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("Q7:Q8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("R7:R8"));

    HSSFRow row7 = sheet.createRow(7);
    HSSFCell cell71 = row7.createCell(0);
    cell71.setCellStyle(stylebottom);
    HSSFCell cell72 = row7.createCell(1);
    cell72.setCellStyle(stylebottom);
    HSSFCell cell73 = row7.createCell(2);
    cell73.setCellStyle(stylebottom);
    HSSFCell cell74 = row7.createCell(3);
    cell74.setCellStyle(stylebottom);
    HSSFCell cell75 = row7.createCell(4);
    cell75.setCellStyle(stylebottom);
    HSSFCell cell76 = row7.createCell(5);
    cell76.setCellStyle(stylebottom);
    //        HSSFCell cell77 = row7.createCell(6);
    //        cell77.setCellValue("OF PAX");
    //        cell77.setCellStyle(stylebottom);
    HSSFCell cell78 = row7.createCell(7);
    cell78.setCellValue("ROOM/NIGHT");
    cell78.setCellStyle(stylebottom);
    HSSFCell cell79 = row7.createCell(8);
    cell79.setCellValue("ROOM/NIGHT");
    cell79.setCellStyle(stylebottom);
    HSSFCell cell710 = row7.createCell(9);
    cell710.setCellValue("NETT");
    cell710.setCellStyle(stylebottom);
    HSSFCell cell711 = row7.createCell(10);
    cell711.setCellValue("TOTAL");
    cell711.setCellStyle(stylebottom);
    HSSFCell cell712 = row7.createCell(11);
    cell712.setCellValue("TOTAL");
    cell712.setCellStyle(stylebottom);
    HSSFCell cell713 = row7.createCell(12);
    cell713.setCellValue("TRFS");
    cell713.setCellStyle(stylebottom);
    HSSFCell cell714 = row7.createCell(13);
    cell714.setCellValue("TRFS");
    cell714.setCellStyle(stylebottom);
    HSSFCell cell715 = row7.createCell(14);
    cell715.setCellStyle(stylebottom);
    HSSFCell cell716 = row7.createCell(15);
    cell716.setCellStyle(stylebottom);
    HSSFCell cell717 = row7.createCell(16);
    cell717.setCellStyle(stylebottom);
    HSSFCell cell718 = row7.createCell(17);
    cell718.setCellStyle(stylebottom);

    //Detail of Table
    int count = 8;
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC23.setDataFormat(currency.getFormat("#,##0"));

    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    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"));

    for (int i = 0; i < list.size(); i++) {
        OutboundHotelSummaryView data = (OutboundHotelSummaryView) list.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(data.getHoteldate());
        celldata0.setCellStyle(styleC23);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(data.getRecordno());
        celldata1.setCellStyle(styleC24);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(data.getRefno());
        celldata2.setCellStyle(styleC24);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(data.getHotel());
        celldata3.setCellStyle(styleC24);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(data.getLeader());
        celldata4.setCellStyle(styleC24);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(data.getPeriod());
        celldata5.setCellStyle(styleC23);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                : new BigDecimal(data.getPax()).doubleValue());
        celldata6.setCellStyle(styleC23);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNet())) ? 0
                : new BigDecimal(data.getNet()).doubleValue());
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata14 = row.createCell(8);
        celldata14.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSale())) ? 0
                : new BigDecimal(data.getSale()).doubleValue());
        celldata14.setCellStyle(styleC25);

        //set data 
        HSSFCell celldata8 = row.createCell(9);
        celldata8.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotelnet())) ? 0
                : new BigDecimal(data.getTotelnet()).doubleValue());
        celldata8.setCellStyle(styleC25);

        HSSFCell celldata9 = row.createCell(10);
        celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalsell())) ? 0
                : new BigDecimal(data.getTotalsell()).doubleValue());
        celldata9.setCellStyle(styleC25);

        HSSFCell celldata10 = row.createCell(11);
        celldata10.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalprofit())) ? 0
                : new BigDecimal(data.getTotalprofit()).doubleValue());
        celldata10.setCellStyle(styleC25);

        HSSFCell celldata11 = row.createCell(12);
        celldata11.setCellValue(data.getBank());
        celldata11.setCellStyle(styleC24);

        HSSFCell celldata12 = row.createCell(13);
        celldata12.setCellValue(data.getTransferdate());
        celldata12.setCellStyle(styleC24);

        HSSFCell celldata15 = row.createCell(14);
        celldata15.setCellValue(data.getSupplier());
        celldata15.setCellStyle(styleC24);

        HSSFCell celldata13 = row.createCell(15);
        celldata13.setCellValue(data.getStatus());
        celldata13.setCellStyle(styleC23);

        HSSFCell celldata16 = row.createCell(16);
        celldata16.setCellValue(data.getRemark());
        celldata16.setCellStyle(styleC24);

        HSSFCell celldata17 = row.createCell(17);
        celldata17.setCellValue(data.getSeller());
        celldata17.setCellStyle(styleC24);

    }

    for (int j = 0; j < 21; 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);
    sheet.setColumnWidth(20, 256 * 15);

}

---

From source file:com.smi.travel.controller.excel.booking.OutboundPackageSummary.java

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

    OutboundPackageSummaryView dataheader = new OutboundPackageSummaryView();

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    styleC21.setDataFormat(currency.getFormat("#,##0.00"));
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    if (!opslist.isEmpty()) {
        dataheader = (OutboundPackageSummaryView) opslist.get(0);

        // set Header Report (Row 1)
        HSSFCellStyle styleC1 = wb.createCellStyle();
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("Package Summary");
        styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
        cell1.setCellStyle(styleC1);/*  w  w w  .j  av a 2  s . c o m*/
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("City : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getHeadercity());
        cell22.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
        HSSFCell cell23 = row2.createCell(4);
        cell23.setCellValue("Pay By : ");
        cell23.setCellStyle(styleC21);
        HSSFCell cell24 = row2.createCell(5);
        cell24.setCellValue(dataheader.getHeaderpayby());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("Package : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeaderpackage());
        cell32.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
        HSSFCell cell33 = row3.createCell(4);
        cell33.setCellValue("Bank Transfer : ");
        cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(5);
        cell34.setCellValue(dataheader.getHeaderbank());
        cell34.setCellStyle(styleC22);

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

    }
    // 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);
    //        
    //        for(int i = 0 ; i < 20 ; i++){
    //            HSSFRow row4 = sheet.createRow(4);
    //            HSSFCell cell = row4.createCell(i);
    //            cell.setCellStyle(stylebottom);
    //        }

    HSSFRow row5 = sheet.createRow(5);
    HSSFCell cell61 = row5.createCell(0);
    cell61.setCellValue("SALE DATE");
    cell61.setCellStyle(styletop);
    HSSFCell cell62 = row5.createCell(1);
    cell62.setCellValue("RECORD NO");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row5.createCell(2);
    cell63.setCellValue("REF NO");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row5.createCell(3);
    cell64.setCellValue("TOUR CODE");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row5.createCell(4);
    cell65.setCellValue("TOUR NAME");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row5.createCell(5);
    cell66.setCellValue("CUSTOMER NAME");
    cell66.setCellStyle(styletop);
    sheet.autoSizeColumn(5);
    HSSFCell cell67 = row5.createCell(6);
    cell67.setCellValue("PERIOD");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row5.createCell(7);
    cell68.setCellValue("PAX");
    cell68.setCellStyle(styletop);
    HSSFCell cell068 = row5.createCell(8);
    cell068.setCellValue("");
    cell068.setCellStyle(styletop);
    HSSFCell cell069 = row5.createCell(9);
    cell069.setCellValue("");
    cell069.setCellStyle(styletop);
    HSSFCell cell70 = row5.createCell(10);
    cell70.setCellValue("TOTAL");
    cell70.setCellStyle(styletop);
    HSSFCell cell71 = row5.createCell(11);
    cell71.setCellValue("TOTAL");
    cell71.setCellStyle(styletop);
    HSSFCell cell72 = row5.createCell(12);
    cell72.setCellValue("TOTAL");
    cell72.setCellStyle(styletop);
    HSSFCell cell73 = row5.createCell(13);
    cell73.setCellValue("BANK");
    cell73.setCellStyle(styletop);
    HSSFCell cell74 = row5.createCell(14);
    cell74.setCellValue("DATE");
    cell74.setCellStyle(styletop);
    HSSFCell cell75 = row5.createCell(15);
    cell75.setCellValue("STATUS");
    cell75.setCellStyle(styletop);
    HSSFCell cell76 = row5.createCell(16);
    cell76.setCellValue("REMARK");
    cell76.setCellStyle(styletop);
    HSSFCell cell77 = row5.createCell(17);
    cell77.setCellValue("SELLER");
    cell77.setCellStyle(styletop);

    sheet.addMergedRegion(CellRangeAddress.valueOf("A6:A7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("B6:B7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("C6:C7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("D6:D7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("E6:E7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("F6:F7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("G6:G7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("H6:J6"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("P6:P7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("R6:R7"));

    HSSFRow row6 = sheet.createRow(6);
    HSSFCell cell78 = row6.createCell(0);
    cell78.setCellStyle(stylebottom);
    HSSFCell cell79 = row6.createCell(1);
    cell79.setCellStyle(stylebottom);
    HSSFCell cell80 = row6.createCell(2);
    cell80.setCellValue("");
    cell80.setCellStyle(stylebottom);
    HSSFCell cell81 = row6.createCell(3);
    cell81.setCellValue("");
    cell81.setCellStyle(stylebottom);
    HSSFCell cell82 = row6.createCell(4);
    cell82.setCellValue("");
    cell82.setCellStyle(stylebottom);
    HSSFCell cell83 = row6.createCell(5);
    cell83.setCellValue("");
    cell83.setCellStyle(stylebottom);
    HSSFCell cell84 = row6.createCell(6);
    cell84.setCellValue("");
    cell84.setCellStyle(stylebottom);
    HSSFCell cell85 = row6.createCell(7);
    cell85.setCellValue("AD");
    cell85.setCellStyle(styleC3);
    HSSFCell cell86 = row6.createCell(8);
    cell86.setCellValue("CH");
    cell86.setCellStyle(styleC3);
    HSSFCell cell87 = row6.createCell(9);
    cell87.setCellValue("IN");
    cell87.setCellStyle(styleC3);
    HSSFCell cell91 = row6.createCell(10);
    cell91.setCellValue("NETT");
    cell91.setCellStyle(stylebottom);
    HSSFCell cell92 = row6.createCell(11);
    cell92.setCellValue("SALE");
    cell92.setCellStyle(stylebottom);
    HSSFCell cell93 = row6.createCell(12);
    cell93.setCellValue("PROFIT");
    cell93.setCellStyle(stylebottom);
    HSSFCell cell94 = row6.createCell(13);
    cell94.setCellValue("TRSF");
    cell94.setCellStyle(stylebottom);
    HSSFCell cell95 = row6.createCell(14);
    cell95.setCellValue("TRSF");
    cell95.setCellStyle(stylebottom);
    HSSFCell cell96 = row6.createCell(15);
    cell96.setCellValue("");
    cell96.setCellStyle(stylebottom);
    HSSFCell cell97 = row6.createCell(16);
    cell97.setCellValue("SUPPLIER");
    cell97.setCellStyle(stylebottom);
    HSSFCell cell98 = row6.createCell(17);
    cell98.setCellValue("");
    cell98.setCellStyle(stylebottom);

    //Detail of Table
    int count = 7;
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC23.setDataFormat(currency.getFormat("#,##0"));

    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    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.setAlignment(styleC26.ALIGN_CENTER);
    styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    for (int i = 0; i < opslist.size(); i++) {
        OutboundPackageSummaryView data = (OutboundPackageSummaryView) opslist.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(data.getDepartdate());
        celldata0.setCellStyle(styleC23);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(data.getRecondno());
        celldata1.setCellStyle(styleC24);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(data.getRefno());
        celldata2.setCellStyle(styleC24);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(data.getPackagecode());
        celldata3.setCellStyle(styleC24);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(data.getPackagename());
        celldata4.setCellStyle(styleC24);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(data.getLeader());
        celldata5.setCellStyle(styleC24);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(data.getPeriod());
        celldata6.setCellStyle(styleC26);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaxadult())) ? 0
                : new BigDecimal(data.getPaxadult()).doubleValue());
        celldata7.setCellStyle(styleC23);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaxchild())) ? 0
                : new BigDecimal(data.getPaxchild()).doubleValue());
        celldata8.setCellStyle(styleC23);

        //set data 
        HSSFCell celldata9 = row.createCell(9);
        celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaxinfant())) ? 0
                : new BigDecimal(data.getPaxinfant()).doubleValue());
        celldata9.setCellStyle(styleC23);

        HSSFCell celldata10 = row.createCell(10);
        celldata10.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNet())) ? 0
                : new BigDecimal(data.getNet()).doubleValue());
        celldata10.setCellStyle(styleC25);

        HSSFCell celldata11 = row.createCell(11);
        celldata11.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSell())) ? 0
                : new BigDecimal(data.getSell()).doubleValue());
        celldata11.setCellStyle(styleC25);

        HSSFCell celldata12 = row.createCell(12);
        celldata12.setCellValue("".equalsIgnoreCase(String.valueOf(data.getProfit())) ? 0
                : new BigDecimal(data.getProfit()).doubleValue());
        celldata12.setCellStyle(styleC25);

        HSSFCell celldata13 = row.createCell(13);
        celldata13.setCellValue(data.getBanktransfer());
        celldata13.setCellStyle(styleC23);

        HSSFCell celldata14 = row.createCell(14);
        celldata14.setCellValue(data.getTransferdate());
        celldata14.setCellStyle(styleC23);

        HSSFCell celldata15 = row.createCell(15);
        celldata15.setCellValue(data.getStatusname());
        celldata15.setCellStyle(styleC23);

        HSSFCell celldata16 = row.createCell(16);
        celldata16.setCellValue(data.getRemark());
        celldata16.setCellStyle(styleC24);

        HSSFCell celldata17 = row.createCell(17);
        celldata17.setCellValue(data.getSeller());
        celldata17.setCellStyle(styleC24);

    }

    for (int j = 0; j < 21; 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);
    //        sheet.setColumnWidth(20, 256*15);

}

---

From source file:com.smi.travel.controller.excel.booking.OutboundProductSummary.java

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

    PaymentProfitLossView dataheader = new PaymentProfitLossView();

    HSSFDataFormat currency = wb.createDataFormat();

    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    styleC21.setDataFormat(currency.getFormat("#,##0"));
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);

    HSSFCellStyle styleGroup = wb.createCellStyle();
    styleGroup.setAlignment(styleGroup.ALIGN_CENTER);
    styleGroup.setFont(excelFunction.getHeaderTable(wb.createFont()));

    HSSFCellStyle styleGroupdata = wb.createCellStyle();
    styleGroupdata.setAlignment(styleGroupdata.ALIGN_LEFT);
    //        styleGroupdata.setFont(excelFunction.getHeaderTable(wb.createFont()));

    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"));
    styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER);

    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);
    styleC26.setVerticalAlignment(styleC26.VERTICAL_CENTER);
    //        styleC26.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    //        styleC26.setFillPattern(styleC26.SOLID_FOREGROUND);

    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.setWrapText(true);//from ww w. j ava  2 s  . c o m
    styleC29.setVerticalAlignment(styleC29.VERTICAL_CENTER);
    //        styleC29.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    //        styleC29.setFillPattern(styleC29.SOLID_FOREGROUND);

    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);
    styleC30.setWrapText(true);
    styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER);
    //        styleC30.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    //        styleC30.setFillPattern(styleC30.SOLID_FOREGROUND);

    HSSFCellStyle styleTotal = wb.createCellStyle();
    styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleTotal.setWrapText(true);
    styleTotal.setVerticalAlignment(styleTotal.VERTICAL_CENTER);
    styleTotal.setAlignment(styleTotal.ALIGN_CENTER);
    styleTotal.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont()));
    styleTotal.setDataFormat(currency.getFormat("#,##0"));
    //        styleTotal.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    //        styleTotal.setFillPattern(styleTotal.SOLID_FOREGROUND);

    HSSFCellStyle styleTotalCurrency = wb.createCellStyle();
    styleTotalCurrency.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleTotalCurrency.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleTotalCurrency.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleTotalCurrency.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleTotalCurrency.setWrapText(true);
    styleTotalCurrency.setVerticalAlignment(styleTotalCurrency.VERTICAL_CENTER);
    styleTotalCurrency.setAlignment(styleTotalCurrency.ALIGN_RIGHT);
    styleTotalCurrency.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont()));
    styleTotalCurrency.setDataFormat(currency.getFormat("#,##0.00"));
    //        styleTotalCurrency.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    //        styleTotalCurrency.setFillPattern(styleTotalCurrency.SOLID_FOREGROUND);

    if (!listPayment.isEmpty()) {
        dataheader = (PaymentProfitLossView) listPayment.get(0);
        // set Header Report (Row 1)
        HSSFCellStyle styleC1 = wb.createCellStyle();
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("Profit & Loss Summary");
        styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
        cell1.setCellStyle(styleC1);
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:G1"));

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Owner : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getHeaderowner());
        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.getHeaderinvsup());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("City : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeadercity());
        cell32.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
        HSSFCell cell33 = row3.createCell(4);
        cell33.setCellValue("Group : ");
        cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(5);
        cell34.setCellValue(dataheader.getHeadergroup());
        cell34.setCellStyle(styleC22);

        // Row 4
        HSSFRow row4 = sheet.createRow(3);
        HSSFCell cell41 = row4.createCell(0);
        cell41.setCellValue("Product Type : ");
        cell41.setCellStyle(styleC21);
        HSSFCell cell42 = row4.createCell(1);
        cell42.setCellValue(dataheader.getHeaderproducttype());
        cell42.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));
        HSSFCell cell43 = row4.createCell(4);
        cell43.setCellValue("Pay Date : ");
        cell43.setCellStyle(styleC21);
        HSSFCell cell44 = row4.createCell(5);
        cell44.setCellValue(dataheader.getHeaderpaydate());
        cell44.setCellStyle(styleC22);

        // Row 5
        HSSFRow row5 = sheet.createRow(4);
        HSSFCell cell51 = row5.createCell(0);
        cell51.setCellValue("Departure Date : ");
        cell51.setCellStyle(styleC21);
        HSSFCell cell52 = row5.createCell(1);
        cell52.setCellValue(dataheader.getHeaderdepartdate());
        cell52.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));
        HSSFCell cell53 = row5.createCell(4);
        cell53.setCellValue("Invoice Date : ");
        cell53.setCellStyle(styleC21);
        HSSFCell cell54 = row5.createCell(5);
        cell54.setCellValue(dataheader.getHeaderinvdate());
        cell54.setCellStyle(styleC22);
    }
    // Header Table
    HSSFCellStyle styleC3 = wb.createCellStyle();
    styleC3.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3.setAlignment(styleC3.ALIGN_CENTER);
    //        styleC3.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    //        styleC3.setFillPattern(styleC3.SOLID_FOREGROUND);
    // Detail of Table
    String temp = "group";
    int count = 6;
    int ktemp = 9;
    String temprefno = "";
    //Total
    BigDecimal totalpax = new BigDecimal("0.00");
    BigDecimal totalsale = new BigDecimal("0.00");
    BigDecimal totalcost = new BigDecimal("0.00");
    BigDecimal totalprofit = new BigDecimal("0.00");

    BigDecimal totalpaxAll = new BigDecimal("0.00");
    BigDecimal totalsaleAll = new BigDecimal("0.00");
    BigDecimal totalcostAll = new BigDecimal("0.00");
    BigDecimal totalprofitAll = new BigDecimal("0.00");

    for (int i = 0; i < listPayment.size(); i++) {
        PaymentProfitLossView data = (PaymentProfitLossView) listPayment.get(i);
        String groupby = data.getHeadergroup();
        String groupbytemp = "";

        if ("OWNER".equalsIgnoreCase(groupby)) {
            groupbytemp = data.getOwner();
        } else if ("PRODUCT TYPE".equalsIgnoreCase(groupby)) {
            groupbytemp = data.getProducttype();
        } else if ("CITY".equalsIgnoreCase(groupby)) {
            groupbytemp = data.getCity();
        } else if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
            groupbytemp = data.getInvto();
        } else if ("DEPARTURE DATE".equalsIgnoreCase(groupby)) {
            groupbytemp = data.getDepartdate();
        }

        if (!temprefno.equalsIgnoreCase(data.getRefno())) {
            if (!"".equalsIgnoreCase(temprefno)) {
                HSSFRow rowtotal = sheet.createRow(count + i);
                // Set align Text
                HSSFCell cellTotal = rowtotal.createCell(0);
                cellTotal.setCellValue("Total");
                cellTotal.setCellStyle(styleTotal);
                if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
                    HSSFCell cellTotal01 = rowtotal.createCell(5);
                    cellTotal01.setCellValue(totalpax.doubleValue());
                    cellTotal01.setCellStyle(styleC26);
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("B" + (count + i + 1) + ":E" + (count + i + 1)));
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("G" + (count + i + 1) + ":M" + (count + i + 1)));
                    for (int j = 1; j < 16; j++) {
                        if (j != 5 && j != 13 && j != 14 && j != 15) {
                            HSSFCell cellTotal05 = rowtotal.createCell(j);
                            cellTotal05.setCellStyle(styleC29);
                        }
                    }
                } else {
                    HSSFCell cellTotal01 = rowtotal.createCell(4);
                    cellTotal01.setCellValue(totalpax.doubleValue());
                    cellTotal01.setCellStyle(styleC26);
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("B" + (count + i + 1) + ":D" + (count + i + 1)));
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("F" + (count + i + 1) + ":M" + (count + i + 1)));
                    for (int j = 1; j < 16; j++) {
                        if (j != 4 && j != 13 && j != 14 && j != 15) {
                            HSSFCell cellTotal05 = rowtotal.createCell(j);
                            cellTotal05.setCellStyle(styleC29);
                        }
                    }
                }
                HSSFCell cellTotal02 = rowtotal.createCell(13);
                cellTotal02.setCellValue(totalsale.doubleValue());
                cellTotal02.setCellStyle(styleC25);
                HSSFCell cellTotal03 = rowtotal.createCell(14);
                cellTotal03.setCellValue(totalcost.doubleValue());
                cellTotal03.setCellStyle(styleC25);
                HSSFCell cellTotal04 = rowtotal.createCell(15);
                cellTotal04.setCellValue(totalprofit.doubleValue());
                cellTotal04.setCellStyle(styleC25);

                totalpaxAll = totalpaxAll.add(totalpax);
                totalsaleAll = totalsaleAll.add(totalsale);
                totalcostAll = totalcostAll.add(totalcost);
                totalprofitAll = totalprofitAll.add(totalprofit);

                totalpax = new BigDecimal(BigInteger.ZERO);
                totalsale = new BigDecimal(BigInteger.ZERO);
                totalcost = new BigDecimal(BigInteger.ZERO);
                totalprofit = new BigDecimal(BigInteger.ZERO);

                count = count + 1;
                ktemp = count + 2 + i;
            }
        }

        if (!temp.equalsIgnoreCase(groupbytemp)) {
            if (!"group".equalsIgnoreCase(temp)) {
                HSSFRow row = sheet.createRow(count + i);
                // Set align Text
                HSSFCell cellTotal = row.createCell(0);
                cellTotal.setCellValue("");
                cellTotal.setCellStyle(styleC30);
                if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
                    HSSFCell cellTotal01 = row.createCell(5);
                    cellTotal01.setCellValue(totalpaxAll.doubleValue());
                    cellTotal01.setCellStyle(styleTotal);
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("A" + (count + i + 1) + ":E" + (count + i + 1)));
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("G" + (count + i + 1) + ":M" + (count + i + 1)));
                    for (int j = 1; j < 16; j++) {
                        if (j != 5 && j != 13 && j != 14 && j != 15) {
                            HSSFCell cellTotal05 = row.createCell(j);
                            cellTotal05.setCellStyle(styleC29);
                        }
                    }
                } else {
                    HSSFCell cellTotal01 = row.createCell(4);
                    cellTotal01.setCellValue(totalpaxAll.doubleValue());
                    cellTotal01.setCellStyle(styleTotal);
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("A" + (count + i + 1) + ":D" + (count + i + 1)));
                    sheet.addMergedRegion(
                            CellRangeAddress.valueOf("F" + (count + i + 1) + ":M" + (count + i + 1)));
                    for (int j = 1; j < 16; j++) {
                        if (j != 4 && j != 13 && j != 14 && j != 15) {
                            HSSFCell cellTotal05 = row.createCell(j);
                            cellTotal05.setCellStyle(styleC29);
                        }
                    }
                }
                HSSFCell cellTotal02 = row.createCell(13);
                cellTotal02.setCellValue(totalsaleAll.doubleValue());
                cellTotal02.setCellStyle(styleTotalCurrency);
                HSSFCell cellTotal03 = row.createCell(14);
                cellTotal03.setCellValue(totalcostAll.doubleValue());
                cellTotal03.setCellStyle(styleTotalCurrency);
                HSSFCell cellTotal04 = row.createCell(15);
                cellTotal04.setCellValue(totalprofitAll.doubleValue());
                cellTotal04.setCellStyle(styleTotalCurrency);

                totalpaxAll = new BigDecimal(BigInteger.ZERO);
                totalsaleAll = new BigDecimal(BigInteger.ZERO);
                totalcostAll = new BigDecimal(BigInteger.ZERO);
                totalprofitAll = new BigDecimal(BigInteger.ZERO);

                count = count + 2;
                ktemp = count + 3 + i;
            }

            int counts = count + i;
            int countss = count + 1 + i;
            System.out.println(" Print Header Table Group count ::  " + count + " ==== i ==== " + i);
            // Row Agent Name
            System.out.println(" Print Header Group counts ::  " + counts);
            HSSFRow row008 = sheet.createRow(counts);
            HSSFCell cell0081 = row008.createCell(0);
            cell0081.setCellValue(groupby);
            cell0081.setCellStyle(styleGroup);
            HSSFCell cell0082 = row008.createCell(1);
            cell0082.setCellValue(groupbytemp);
            cell0082.setCellStyle(styleGroupdata);
            sheet.addMergedRegion(CellRangeAddress.valueOf("B" + (counts + 1) + ":D" + (counts + 1)));
            temp = groupbytemp;

            System.out.println(" Print Header Table countss ::  " + countss);
            HSSFRow row09 = sheet.createRow(countss);
            HSSFCell cell091 = row09.createCell(0);
            cell091.setCellValue("REF NO");
            cell091.setCellStyle(styleC3);
            sheet.autoSizeColumn(0);
            System.out.println(" groupby " + groupby);
            if ("OWNER".equalsIgnoreCase(groupby)) {
                HSSFCell cell092 = row09.createCell(1);
                cell092.setCellValue("TOUR NAME");
                cell092.setCellStyle(styleC3);
                sheet.autoSizeColumn(1);
                HSSFCell cell093 = row09.createCell(2);
                cell093.setCellValue("DEPARTURE DATE");
                sheet.autoSizeColumn(2);
                cell093.setCellStyle(styleC3);
                HSSFCell cell094 = row09.createCell(3);
                cell094.setCellValue("CITY");
                cell094.setCellStyle(styleC3);
                sheet.autoSizeColumn(3);
                HSSFCell cell095 = row09.createCell(4);
                cell095.setCellValue("PAX");
                cell095.setCellStyle(styleC3);
                sheet.autoSizeColumn(4);
                HSSFCell cell096 = row09.createCell(5);
                cell096.setCellValue("CLIENT NAME");
                cell096.setCellStyle(styleC3);
                sheet.autoSizeColumn(5);
            } else if ("PRODUCT TYPE".equalsIgnoreCase(groupby)) {

            } else if ("CITY".equalsIgnoreCase(groupby)) {
                HSSFCell cell092 = row09.createCell(1);
                cell092.setCellValue("OWNER");
                cell092.setCellStyle(styleC3);
                sheet.autoSizeColumn(1);
                HSSFCell cell093 = row09.createCell(2);
                cell093.setCellValue("TOUR NAME");
                sheet.autoSizeColumn(2);
                cell093.setCellStyle(styleC3);
                HSSFCell cell094 = row09.createCell(3);
                cell094.setCellValue("DEPARTURE DATE");
                cell094.setCellStyle(styleC3);
                sheet.autoSizeColumn(3);
                HSSFCell cell095 = row09.createCell(4);
                cell095.setCellValue("PAX");
                cell095.setCellStyle(styleC3);
                sheet.autoSizeColumn(4);
                HSSFCell cell096 = row09.createCell(5);
                cell096.setCellValue("CLIENT NAME");
                cell096.setCellStyle(styleC3);
                sheet.autoSizeColumn(5);
            } else if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
                HSSFCell cell092 = row09.createCell(1);
                cell092.setCellValue("OWNER");
                cell092.setCellStyle(styleC3);
                sheet.autoSizeColumn(1);
                HSSFCell cell093 = row09.createCell(2);
                cell093.setCellValue("TOUR NAME");
                sheet.autoSizeColumn(2);
                cell093.setCellStyle(styleC3);
                HSSFCell cell094 = row09.createCell(3);
                cell094.setCellValue("DEPARTURE DATE");
                cell094.setCellStyle(styleC3);
                sheet.autoSizeColumn(3);
                HSSFCell cell095 = row09.createCell(4);
                cell095.setCellValue("CITY");
                cell095.setCellStyle(styleC3);
                sheet.autoSizeColumn(4);
                HSSFCell cell096 = row09.createCell(5);
                cell096.setCellValue("PAX");
                cell096.setCellStyle(styleC3);
                sheet.autoSizeColumn(5);
            } else if ("DEPARTURE DATE".equalsIgnoreCase(groupby)) {
                HSSFCell cell092 = row09.createCell(1);
                cell092.setCellValue("OWNER");
                cell092.setCellStyle(styleC3);
                sheet.autoSizeColumn(1);
                HSSFCell cell093 = row09.createCell(2);
                cell093.setCellValue("TOUR NAME");
                sheet.autoSizeColumn(2);
                cell093.setCellStyle(styleC3);
                HSSFCell cell094 = row09.createCell(3);
                cell094.setCellValue("CITY");
                cell094.setCellStyle(styleC3);
                sheet.autoSizeColumn(3);
                HSSFCell cell095 = row09.createCell(4);
                cell095.setCellValue("PAX");
                cell095.setCellStyle(styleC3);
                sheet.autoSizeColumn(4);
                HSSFCell cell096 = row09.createCell(5);
                cell096.setCellValue("CLIENT NAME");
                cell096.setCellStyle(styleC3);
                sheet.autoSizeColumn(5);
            }

            HSSFCell cell097 = row09.createCell(6);
            cell097.setCellValue("INVOICE NO");
            cell097.setCellStyle(styleC3);
            sheet.autoSizeColumn(6);
            HSSFCell cell098 = row09.createCell(7);
            cell098.setCellValue("RECEIPT NO");
            cell098.setCellStyle(styleC3);
            sheet.autoSizeColumn(7);
            HSSFCell cell099 = row09.createCell(8);
            cell099.setCellValue("TAX INVOICE NO");
            cell099.setCellStyle(styleC3);
            sheet.autoSizeColumn(8);
            HSSFCell cell100 = row09.createCell(9);
            cell100.setCellValue("PRODUCT TYPE");
            cell100.setCellStyle(styleC3);
            sheet.autoSizeColumn(9);
            HSSFCell cell101 = row09.createCell(10);
            cell101.setCellValue("DESCRIPTION");
            cell101.setCellStyle(styleC3);
            sheet.autoSizeColumn(10);
            HSSFCell cell102 = row09.createCell(11);
            cell102.setCellValue("PAY NO");
            cell102.setCellStyle(styleC3);
            sheet.autoSizeColumn(11);
            HSSFCell cell103 = row09.createCell(12);
            cell103.setCellValue("SUPPLIER");
            cell103.setCellStyle(styleC3);
            sheet.autoSizeColumn(12);
            HSSFCell cell104 = row09.createCell(13);
            cell104.setCellValue("SALE PRICE");
            cell104.setCellStyle(styleC3);
            sheet.autoSizeColumn(13);
            HSSFCell cell105 = row09.createCell(14);
            cell105.setCellValue("COST");
            cell105.setCellStyle(styleC3);
            sheet.autoSizeColumn(14);
            HSSFCell cell106 = row09.createCell(15);
            cell106.setCellValue("PROFIT");
            cell106.setCellStyle(styleC3);
            sheet.autoSizeColumn(15);
            count = count + 2;
        }

        //set data 
        System.out.println(" Print Data Table count ::  " + count + " ==== i ==== " + i);
        HSSFRow row = sheet.createRow(count + i);
        if (!temprefno.equalsIgnoreCase(data.getRefno())) {
            HSSFCell celldata01 = row.createCell(0);
            celldata01.setCellValue(data.getRefno());
            celldata01.setCellStyle(styleC29);

            if ("OWNER".equalsIgnoreCase(groupby)) {
                HSSFCell celldata02 = row.createCell(1);
                celldata02.setCellValue(data.getTourname());
                celldata02.setCellStyle(styleC29);
                HSSFCell celldata03 = row.createCell(2);
                celldata03.setCellValue(data.getDepartdate());
                celldata03.setCellStyle(styleC29);
                HSSFCell celldata04 = row.createCell(3);
                celldata04.setCellValue(data.getCity());
                celldata04.setCellStyle(styleC29);
                HSSFCell celldata05 = row.createCell(4);
                celldata05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                        : new BigDecimal(data.getPax()).doubleValue());
                celldata05.setCellStyle(styleC26);
                HSSFCell celldata06 = row.createCell(5);
                celldata06.setCellValue(data.getInvto());
                celldata06.setCellStyle(styleC29);
                sheet.setColumnWidth(3, 256 * 15);
            } else if ("PRODUCT TYPE".equalsIgnoreCase(groupby)) {

            } else if ("CITY".equalsIgnoreCase(groupby)) {
                HSSFCell celldata02 = row.createCell(1);
                celldata02.setCellValue(data.getOwner());
                celldata02.setCellStyle(styleC29);
                HSSFCell celldata03 = row.createCell(2);
                celldata03.setCellValue(data.getTourname());
                celldata03.setCellStyle(styleC29);
                HSSFCell celldata04 = row.createCell(3);
                celldata04.setCellValue(data.getDepartdate());
                celldata04.setCellStyle(styleC29);
                HSSFCell celldata05 = row.createCell(4);
                celldata05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                        : new BigDecimal(data.getPax()).doubleValue());
                celldata05.setCellStyle(styleC26);
                HSSFCell celldata06 = row.createCell(5);
                celldata06.setCellValue(data.getInvto());
                celldata06.setCellStyle(styleC29);
            } else if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
                HSSFCell celldata02 = row.createCell(1);
                celldata02.setCellValue(data.getOwner());
                celldata02.setCellStyle(styleC29);
                HSSFCell celldata03 = row.createCell(2);
                celldata03.setCellValue(data.getTourname());
                celldata03.setCellStyle(styleC29);
                HSSFCell celldata04 = row.createCell(3);
                celldata04.setCellValue(data.getDepartdate());
                celldata04.setCellStyle(styleC29);
                HSSFCell celldata05 = row.createCell(4);
                celldata05.setCellValue(data.getCity());
                celldata05.setCellStyle(styleC29);
                HSSFCell celldata06 = row.createCell(5);
                celldata06.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                        : new BigDecimal(data.getPax()).doubleValue());
                celldata06.setCellStyle(styleC26);
                sheet.setColumnWidth(4, 256 * 15);
            } else if ("DEPARTURE DATE".equalsIgnoreCase(groupby)) {
                HSSFCell celldata02 = row.createCell(1);
                celldata02.setCellValue(data.getOwner());
                celldata02.setCellStyle(styleC29);
                HSSFCell celldata03 = row.createCell(2);
                celldata03.setCellValue(data.getTourname());
                celldata03.setCellStyle(styleC29);
                HSSFCell celldata04 = row.createCell(3);
                celldata04.setCellValue(data.getCity());
                celldata04.setCellStyle(styleC29);
                HSSFCell celldata05 = row.createCell(4);
                celldata05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                        : new BigDecimal(data.getPax()).doubleValue());
                celldata05.setCellStyle(styleC26);
                HSSFCell celldata06 = row.createCell(5);
                celldata06.setCellValue(data.getInvto());
                celldata06.setCellStyle(styleC29);
                sheet.setColumnWidth(3, 256 * 15);
            }
        } else {
            for (int j = 0; j < 6; j++) {
                HSSFCell celldata02 = row.createCell(j);
                celldata02.setCellStyle(styleC29);
            }
            sheet.addMergedRegion(CellRangeAddress.valueOf("A" + (count + i + 1) + ":F" + (count + i + 1)));
        }

        HSSFCell celldata07 = row.createCell(6);
        celldata07.setCellValue(data.getInvno());
        celldata07.setCellStyle(styleC29);
        HSSFCell celldata08 = row.createCell(7);
        celldata08.setCellValue(data.getReceipt());
        celldata08.setCellStyle(styleC29);
        HSSFCell celldata09 = row.createCell(8);
        celldata09.setCellValue(data.getTaxinvoice());
        celldata09.setCellStyle(styleC29);
        HSSFCell celldata10 = row.createCell(9);
        celldata10.setCellValue(data.getProducttype());
        celldata10.setCellStyle(styleC29);
        HSSFCell celldata11 = row.createCell(10);
        celldata11.setCellValue(data.getDescription());
        celldata11.setCellStyle(styleC29);
        HSSFCell celldata12 = row.createCell(11);
        celldata12.setCellValue(data.getPayno());
        celldata12.setCellStyle(styleC29);
        HSSFCell celldata13 = row.createCell(12);
        celldata13.setCellValue(data.getSupplier());
        celldata13.setCellStyle(styleC29);
        HSSFCell celldata14 = row.createCell(13);
        celldata14.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSale())) ? 0
                : new BigDecimal(data.getSale()).doubleValue());
        celldata14.setCellStyle(styleC25);
        HSSFCell celldata15 = row.createCell(14);
        celldata15.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCost())) ? 0
                : new BigDecimal(data.getCost()).doubleValue());
        celldata15.setCellStyle(styleC25);
        HSSFCell celldata16 = row.createCell(15);
        celldata16.setCellValue("".equalsIgnoreCase(String.valueOf(data.getProfit())) ? 0
                : new BigDecimal(data.getProfit()).doubleValue());
        celldata16.setCellStyle(styleC25);

        totalpax = ("".equalsIgnoreCase(String.valueOf(data.getPax())) ? new BigDecimal(BigInteger.ZERO)
                : new BigDecimal(data.getPax()));
        totalsale = totalsale
                .add("".equalsIgnoreCase(String.valueOf(data.getSale())) ? new BigDecimal(BigInteger.ZERO)
                        : new BigDecimal(data.getSale()));
        totalcost = totalcost
                .add("".equalsIgnoreCase(String.valueOf(data.getCost())) ? new BigDecimal(BigInteger.ZERO)
                        : new BigDecimal(data.getCost()));
        totalprofit = totalprofit
                .add("".equalsIgnoreCase(String.valueOf(data.getProfit())) ? new BigDecimal(BigInteger.ZERO)
                        : new BigDecimal(data.getProfit()));

        temprefno = data.getRefno();

        // set total last row
        if (i == (listPayment.size() - 1)) {

            HSSFRow rowtotal = sheet.createRow(count + i + 1);
            // Set align Text
            HSSFCell cellTotal = rowtotal.createCell(0);
            cellTotal.setCellValue("Total");
            cellTotal.setCellStyle(styleTotal);
            if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
                HSSFCell cellTotal01 = rowtotal.createCell(5);
                cellTotal01.setCellValue(totalpax.doubleValue());
                cellTotal01.setCellStyle(styleC26);
                sheet.addMergedRegion(CellRangeAddress.valueOf("B" + (count + i + 2) + ":E" + (count + i + 2)));
                sheet.addMergedRegion(CellRangeAddress.valueOf("G" + (count + i + 2) + ":M" + (count + i + 2)));
                for (int j = 1; j < 16; j++) {
                    if (j != 5 && j != 13 && j != 14 && j != 15) {
                        HSSFCell cellTotal05 = rowtotal.createCell(j);
                        cellTotal05.setCellStyle(styleTotal);
                    }
                }
            } else {
                HSSFCell cellTotal01 = rowtotal.createCell(4);
                cellTotal01.setCellValue(totalpax.doubleValue());
                cellTotal01.setCellStyle(styleC26);
                sheet.addMergedRegion(CellRangeAddress.valueOf("B" + (count + i + 2) + ":D" + (count + i + 2)));
                sheet.addMergedRegion(CellRangeAddress.valueOf("F" + (count + i + 2) + ":M" + (count + i + 2)));
                for (int j = 1; j < 16; j++) {
                    if (j != 4 && j != 13 && j != 14 && j != 15) {
                        HSSFCell cellTotal05 = rowtotal.createCell(j);
                        cellTotal05.setCellStyle(styleC29);
                    }
                }
            }
            HSSFCell cellTotal02 = rowtotal.createCell(13);
            cellTotal02.setCellValue(totalsale.doubleValue());
            cellTotal02.setCellStyle(styleC25);
            HSSFCell cellTotal03 = rowtotal.createCell(14);
            cellTotal03.setCellValue(totalcost.doubleValue());
            cellTotal03.setCellStyle(styleC25);
            HSSFCell cellTotal04 = rowtotal.createCell(15);
            cellTotal04.setCellValue(totalprofit.doubleValue());
            cellTotal04.setCellStyle(styleC25);

            totalpaxAll = totalpaxAll.add(totalpax);
            totalsaleAll = totalsaleAll.add(totalsale);
            totalcostAll = totalcostAll.add(totalcost);
            totalprofitAll = totalprofitAll.add(totalprofit);

            //                    count = count+1;
            //                    ktemp = count+2+i;

            row = sheet.createRow(count + i + 2);
            // Set align Text
            HSSFCell cellTotalAll = row.createCell(0);
            cellTotalAll.setCellValue("");
            cellTotalAll.setCellStyle(styleTotal);
            if ("CLIENT NAME".equalsIgnoreCase(groupby)) {
                HSSFCell cellTotal01 = row.createCell(5);
                cellTotal01.setCellValue(totalpaxAll.doubleValue());
                cellTotal01.setCellStyle(styleTotal);
                sheet.addMergedRegion(CellRangeAddress.valueOf("A" + (count + i + 3) + ":E" + (count + i + 3)));
                sheet.addMergedRegion(CellRangeAddress.valueOf("G" + (count + i + 3) + ":M" + (count + i + 3)));
                for (int j = 1; j < 16; j++) {
                    if (j != 5 && j != 13 && j != 14 && j != 15) {
                        HSSFCell cellTotal05 = row.createCell(j);
                        cellTotal05.setCellStyle(styleC29);
                    }
                }
            } else {
                HSSFCell cellTotal01 = row.createCell(4);
                cellTotal01.setCellValue(totalpaxAll.doubleValue());
                cellTotal01.setCellStyle(styleTotal);
                sheet.addMergedRegion(CellRangeAddress.valueOf("A" + (count + i + 3) + ":D" + (count + i + 3)));
                sheet.addMergedRegion(CellRangeAddress.valueOf("F" + (count + i + 3) + ":M" + (count + i + 3)));
                for (int j = 1; j < 16; j++) {
                    if (j != 4 && j != 13 && j != 14 && j != 15) {
                        HSSFCell cellTotal05 = row.createCell(j);
                        cellTotal05.setCellStyle(styleC29);
                    }
                }
            }
            HSSFCell cellTotalAll02 = row.createCell(13);
            cellTotalAll02.setCellValue(totalsaleAll.doubleValue());
            cellTotalAll02.setCellStyle(styleTotalCurrency);
            HSSFCell cellTotalAll03 = row.createCell(14);
            cellTotalAll03.setCellValue(totalcostAll.doubleValue());
            cellTotalAll03.setCellStyle(styleTotalCurrency);
            HSSFCell cellTotalAll04 = row.createCell(15);
            cellTotalAll04.setCellValue(totalprofitAll.doubleValue());
            cellTotalAll04.setCellStyle(styleTotalCurrency);
        }
    }

    for (int x = 0; x < 17; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(6, 256 * 25);
    sheet.setColumnWidth(7, 256 * 25);
    sheet.setColumnWidth(8, 256 * 25);

    sheet.setColumnWidth(13, 256 * 15);
    sheet.setColumnWidth(14, 256 * 15);
    sheet.setColumnWidth(15, 256 * 15);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• »