Example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setVerticalAlignment

List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setVerticalAlignment

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCellStyle
  6. setVerticalAlignment

Introduction

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

Prototype

@Override
public void setVerticalAlignment(VerticalAlignment align)

Source Link

Document

set the type of vertical alignment for the cell

Usage

From source file:org.oep.cmon.report.portlet.util.ActionUtil.java

License:Apache License

/**
 * This is function excel BaoCaochungthuc
 * Version: 1.0//  w  ww  .  ja va2s .  c o m
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelBaocaochungthuc(ResourceRequest req, ResourceResponse res) {
    try {
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        String coQuanQuanLyId = req.getParameter("coQuanQuanLyId");
        CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl();
        if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) {
            coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId));
        }
        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFSheet sheet = workbook.createSheet("Baocaohosochungthuc");

        // define a cell style
        HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont();
        tableHeaderFont.setBoldweight((short) 5000);

        HSSFFont ngaythangFont = (HSSFFont) workbook.createFont();
        ngaythangFont.setItalic(true);

        HSSFFont titleFont = (HSSFFont) workbook.createFont();
        titleFont.setBoldweight((short) 5000);
        titleFont.setFontHeightInPoints((short) 15);

        HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle();
        styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellHeader.setFont(tableHeaderFont);
        styleCellHeader.setWrapText(true);

        HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle();
        styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellNgaythang.setFont(ngaythangFont);
        styleCellNgaythang.setWrapText(true);

        HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle();
        styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellCenter.setWrapText(true);

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

        HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle();
        styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellRight.setWrapText(true);
        styleCellRight.setFont(tableHeaderFont);

        HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle();
        styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleHeader.setFont(tableHeaderFont);
        styleHeader.setWrapText(true);

        HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle();
        styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellNhomTTHC.setFont(tableHeaderFont);

        HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle();
        styleTitle.setFont(titleFont);
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // end style
        String tungay = ParamUtil.getString(req, "tungay");
        String denngay = ParamUtil.getString(req, "denngay");
        int rowNum = 1;
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
        HSSFRow rowUBND = sheet.createRow(rowNum);
        rowUBND.createCell(0).setCellValue("");
        rowUBND.getCell(0).setCellStyle(styleCellHeader);

        sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 11));
        rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn"));
        rowUBND.getCell(2).setCellStyle(styleCellHeader);

        rowNum++;

        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
        HSSFRow rowTenDonVi = sheet.createRow(rowNum);
        rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen());
        rowTenDonVi.getCell(0).setCellStyle(styleCellHeader);

        sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 11));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);
        rowNum++;
        String str_loaihoso = ParamUtil.getString(req, "id_loaihoso");
        List<HoSoChungThuc> listhosochungthuc = ActionUtil.listhsct(str_loaihoso, tungay, denngay);

        String sochungthuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sochungthuc");
        String nguoinop = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoinop");
        String diachi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.diachi");
        String noidungcongviec = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaochungthuc.noidungcongviec");
        String loaigiayto = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.loaigiayto");
        String sobo = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sobo");
        String sotrang = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sotrang");
        String ngaynhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.ngaynhan");
        String ngaytraketqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.ngaytraketqua");
        String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.lephi");
        String nguoithuchien = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoithuchien");
        String nguoiky = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoiky");
        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.tieude");
        String[] header = { "STT", sochungthuc, nguoinop, diachi, noidungcongviec, loaigiayto, sobo, sotrang,
                ngaynhan, ngaytraketqua, lephi, nguoithuchien, nguoiky };

        rowNum++;
        int[] width = { 1500, 2000, 5000, 5000, 5000, 5000, 2000, 2000, 3000, 3000, 2000, 4000, 4000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 12));
        HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styleTitle);
        titleCell.setCellValue(tieude);

        rowNum += 2;

        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 12));
        HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum);
        HSSFCell ngaythangCell = ngaythangRow.createCell(0);
        ngaythangCell.setCellStyle(styleCellNgaythang);
        ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay
                + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay);

        rowNum = rowNum + 3;
        HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum);
        int colNum = 0;
        for (int i = 0; i < header.length; i++) {
            HSSFCell cell = headerRow.createCell(colNum);
            cell.setCellStyle(styleHeader);
            cell.setCellValue(header[i]);
            sheet.setColumnWidth(i, width[i]);
            colNum++;
        }
        rowNum++;
        long tong = 0;
        int stt = 0;
        for (int a = 0; a < listhosochungthuc.size(); a++) {
            // khai bao dong va cac cell
            stt++;
            HSSFRow row = (HSSFRow) sheet.createRow(rowNum);
            HSSFCell cell = row.createCell(0);
            HSSFCell cell1 = row.createCell(1);
            HSSFCell cell2 = row.createCell(2);
            HSSFCell cell3 = row.createCell(3);
            HSSFCell cell4 = row.createCell(4);
            HSSFCell cell5 = row.createCell(5);
            HSSFCell cell6 = row.createCell(6);
            HSSFCell cell7 = row.createCell(7);
            HSSFCell cell8 = row.createCell(8);
            HSSFCell cell9 = row.createCell(9);
            HSSFCell cell10 = row.createCell(10);
            HSSFCell cell11 = row.createCell(11);
            HSSFCell cell12 = row.createCell(12);
            // set style cho cac cell
            cell.setCellStyle(styleCellCenter);
            cell1.setCellStyle(styleCellLeft);
            cell2.setCellStyle(styleCellCenter);
            cell3.setCellStyle(styleCellCenter);
            cell4.setCellStyle(styleCellCenter);
            cell5.setCellStyle(styleCellCenter);
            cell6.setCellStyle(styleCellCenter);
            cell7.setCellStyle(styleCellCenter);
            cell8.setCellStyle(styleCellCenter);
            cell9.setCellStyle(styleCellCenter);
            cell10.setCellStyle(styleCellCenter);
            cell11.setCellStyle(styleCellCenter);
            cell12.setCellStyle(styleCellCenter);

            // set gia tri cho cac cell
            String diachithuongtru = listhosochungthuc.get(a).getDiaChiThuongTruNguoiNop() != null
                    ? listhosochungthuc.get(a).getDiaChiThuongTruNguoiNop()
                    : "";
            String dienthoaididong = listhosochungthuc.get(a).getSoDienThoaiDiDongNguoiNop() != null
                    ? "T:" + listhosochungthuc.get(a).getSoDienThoaiDiDongNguoiNop()
                    : "";
            cell.setCellValue(stt);
            cell1.setCellValue(listhosochungthuc.get(a).getSoChungThuc() != null
                    ? listhosochungthuc.get(a).getSoChungThuc()
                    : "");
            cell2.setCellValue(listhosochungthuc.get(a).getHoTenNguoiNopHoSo() != null
                    ? listhosochungthuc.get(a).getHoTenNguoiNopHoSo()
                    : "");
            cell3.setCellValue(diachithuongtru + "\n" + dienthoaididong);
            cell4.setCellValue(listhosochungthuc.get(a).getTenChungThuc());
            cell5.setCellValue(
                    listhosochungthuc.get(a).getThuTucHanhChinhId() != null ? ThuTucHanhChinhLocalServiceUtil
                            .getThuTucHanhChinh(listhosochungthuc.get(a).getThuTucHanhChinhId()).getTen() : "");
            cell6.setCellValue(listhosochungthuc.get(a).getSoBoHoSo());
            cell7.setCellValue(listhosochungthuc.get(a).getSoTo());
            cell8.setCellValue(listhosochungthuc.get(a).getNgayNopHoSo() != null
                    ? df.format(listhosochungthuc.get(a).getNgayNopHoSo())
                    : "");
            cell9.setCellValue(listhosochungthuc.get(a).getNgayTraKetQua() != null
                    ? df.format(listhosochungthuc.get(a).getNgayTraKetQua())
                    : "");
            cell10.setCellValue(listhosochungthuc.get(a).getLePhi());
            cell11.setCellValue(
                    listhosochungthuc.get(a).getCanBoTiepNhanId() != null
                            ? CongChucLocalServiceUtil
                                    .fetchCongChuc(listhosochungthuc.get(a).getCanBoTiepNhanId()).getHoVaTen()
                            : "");
            cell12.setCellValue(
                    listhosochungthuc.get(a).getTenCanBoKy() != null ? listhosochungthuc.get(a).getTenCanBoKy()
                            : "");
            rowNum++;
        }

        res.setContentType("application/vnd.ms-excel");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaohosochungthuc.xls");
        OutputStream out = res.getPortletOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:org.oep.cmon.report.portlet.util.ActionUtil.java

License:Apache License

/**
 * This is function excel report detail/*from  w w  w.j a v a 2 s  . c  o  m*/
 * Version: 1.0
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param resourceRequest
 * @param resourceResponse
 */
public static void excelbaocaochitiet(ResourceRequest resourceRequest, ResourceResponse resourceResponse) {
    try {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("new sheet");
        HSSFFont Row2_font = workbook.createFont();
        Row2_font.setFontName(HSSFFont.FONT_ARIAL);
        Row2_font.setFontHeightInPoints((short) 13);
        Row2_font.setUnderline(HSSFFont.U_SINGLE);

        HSSFFont ngaythang_font = workbook.createFont();
        ngaythang_font.setFontName(HSSFFont.FONT_ARIAL);
        ngaythang_font.setFontHeightInPoints((short) 13);
        ngaythang_font.setItalic(true);

        HSSFFont style_font = workbook.createFont();
        style_font.setFontHeightInPoints((short) 13);

        HSSFFont trangthai_font = workbook.createFont();
        trangthai_font.setFontName(HSSFFont.FONT_ARIAL);
        trangthai_font.setFontHeightInPoints((short) 11);
        trangthai_font.setItalic(true);

        /*define a cell style*/
        HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont();
        tableHeaderFont.setBoldweight((short) 3500);
        HSSFFont titleFont = (HSSFFont) workbook.createFont();
        titleFont.setBoldweight((short) 3500);
        titleFont.setFontHeightInPoints((short) 13);

        HSSFFont titleTongStyle_font = (HSSFFont) workbook.createFont();
        titleTongStyle_font.setFontHeightInPoints((short) 13);
        titleTongStyle_font.setBoldweight((short) 3500);

        HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle();
        styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellCenter.setWrapText(true);

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

        HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle();
        styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellRight.setWrapText(true);

        HSSFCellStyle titleTongStyle = (HSSFCellStyle) workbook.createCellStyle();
        titleTongStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleTongStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleTongStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleTongStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleTongStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        titleTongStyle.setFont(titleTongStyle_font);

        HSSFCellStyle titleLinhvucStyle = (HSSFCellStyle) workbook.createCellStyle();
        titleLinhvucStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleLinhvucStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleLinhvucStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleLinhvucStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleLinhvucStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        titleLinhvucStyle.setFont(titleTongStyle_font);

        HSSFCellStyle styleQuochieu = (HSSFCellStyle) workbook.createCellStyle();
        styleQuochieu.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleQuochieu.setFont(style_font);

        HSSFCellStyle styleRow2 = (HSSFCellStyle) workbook.createCellStyle();
        styleRow2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleRow2.setFont(Row2_font);

        HSSFCellStyle ngaythang_style = (HSSFCellStyle) workbook.createCellStyle();
        ngaythang_style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        ngaythang_style.setFont(ngaythang_font);

        HSSFCellStyle style_trangthai = (HSSFCellStyle) workbook.createCellStyle();
        style_trangthai.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_trangthai.setFont(trangthai_font);

        HSSFCellStyle style_tungay_denngay = (HSSFCellStyle) workbook.createCellStyle();
        style_tungay_denngay.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_tungay_denngay.setFont(trangthai_font);

        HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle();
        styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleHeader.setFont(tableHeaderFont);
        styleHeader.setWrapText(true);

        HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle();
        styleTitle.setFont(titleFont);
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        /*end style*/
        int rowNum = 0;
        String[] header = { "STT", "Lnh vc",
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tenhoso"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.sohoso"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.trangthai"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.diachi"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.dienthoai"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.nguoinophoso"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.chuhoso"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ngaynhan"),
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ngaytra"),
                "Tnh trng\n h s",
                LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ghichu") };
        int[] width = { 1200, 3750, 3750, 4200, 3650, 3650, 3650, 3650, 3750, 3750, 3750, 3750, 3750 };

        String[] listloaihoso = null;
        if (Validator.isNotNull(ParamUtil.getParameterValues(resourceRequest, "chon_hoso"))) {
            listloaihoso = ParamUtil.getParameterValues(resourceRequest, "chon_hoso");
        }
        String ngaynhan_tungay = "";
        if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "ngaynhan_tungay"))) {
            ngaynhan_tungay = ParamUtil.getString(resourceRequest, "ngaynhan_tungay");
        }
        String ngaynhan_denngay = "";
        if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "ngaynhan_denngay"))) {
            ngaynhan_denngay = ParamUtil.getString(resourceRequest, "ngaynhan_denngay");
        }
        String trangthai_hoso = "";
        if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "trangthai_hoso"))) {
            trangthai_hoso = ParamUtil.getString(resourceRequest, "trangthai_hoso");
        }
        String tinhtrang = "";
        if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "tinhtrang_hoso"))) {
            tinhtrang = ParamUtil.getString(resourceRequest, "tinhtrang_hoso");
        }
        String loaidangky = "";
        if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "loaidangky_hoso"))) {
            loaidangky = ParamUtil.getString(resourceRequest, "loaidangky_hoso");
        }
        String ten_donvi = "";
        if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "chon_donvi"))) {
            ten_donvi = ParamUtil.getString(resourceRequest, "chon_donvi");
        }
        List<Baocaochitiet> list = BaocaochitietLocalServiceUtil.hienthiDanhsachBaocao(listloaihoso,
                ngaynhan_tungay, ngaynhan_denngay, trangthai_hoso, tinhtrang, loaidangky, QueryUtil.ALL_POS,
                QueryUtil.ALL_POS);

        sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 0, (short) 2));
        HSSFRow Quochieu = sheet.createRow(rowNum);
        HSSFCell Quochieucell_1 = Quochieu.createCell(0);
        HSSFCell Quochieucell_2 = Quochieu.createCell(3);
        Quochieucell_1.setCellStyle(styleQuochieu);
        Quochieucell_2.setCellStyle(styleQuochieu);
        Quochieucell_1.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.qlc"));
        sheet.addMergedRegion(new Region(rowNum, (short) 3, (short) 0, (short) 12));
        Quochieucell_2.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.chxhcnvn"));
        rowNum = rowNum + 1;

        sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 1, (short) 2));
        HSSFRow Row_2 = sheet.createRow(rowNum);
        //HSSFCell Quan = Row_2.createCell(0);
        //Quan.setCellValue(LanguageUtil.get((PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG) ,resourceRequest.getLocale(),"vn.dtt.cmon.report.excel.qlc"));
        //Quan.setCellStyle(styleRow2);

        sheet.addMergedRegion(new Region(rowNum, (short) 3, (short) 1, (short) 12));
        HSSFCell Tieungu = Row_2.createCell(3);
        Tieungu.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.dltdhp"));
        Tieungu.setCellStyle(styleRow2);
        rowNum = rowNum + 1;

        Calendar dateTime = Calendar.getInstance();
        SimpleDateFormat ngay_format = new SimpleDateFormat("dd");
        SimpleDateFormat thang_format = new SimpleDateFormat("MM");
        SimpleDateFormat nam_format = new SimpleDateFormat("yyyy");
        Date time = dateTime.getTime();
        String day = ngay_format.format(time);
        String month = thang_format.format(time);
        String year = nam_format.format(time);
        sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 2, (short) 10));
        HSSFRow Row_3 = sheet.createRow(rowNum);
        HSSFCell ngaythang = Row_3.createCell(0);
        ngaythang.setCellValue(ten_donvi + ", "
                + LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.ngay")
                + " " + day + " "
                + LanguageUtil
                        .get((PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                                resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.thang")
                + " " + month + " "
                + LanguageUtil.get(
                        (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                        resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nam")
                + " " + year);
        ngaythang.setCellStyle(ngaythang_style);
        rowNum = rowNum + 1;

        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10));
        HSSFRow titleRow = sheet.createRow(rowNum);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tieude"));
        titleCell.setCellStyle(styleTitle);
        if (ngaynhan_tungay != "" && ngaynhan_denngay != "") {
            rowNum = rowNum + 1;
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10));
            HSSFRow TimeReportRow = sheet.createRow(rowNum);
            HSSFCell TimeReportCell = TimeReportRow.createCell(0);
            TimeReportCell.setCellValue(LanguageUtil.get(
                    (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                    resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay
                    + " "
                    + LanguageUtil.get(
                            (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                            resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay")
                    + ": " + ngaynhan_denngay);
            TimeReportCell.setCellStyle(style_tungay_denngay);
        } else if (ngaynhan_tungay != "" && ngaynhan_denngay == "") {
            rowNum = rowNum + 1;
            ngaynhan_denngay = "01/12/" + ngaynhan_tungay.substring(6, 10);
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10));
            HSSFRow TimeReportRow = sheet.createRow(rowNum);
            HSSFCell TimeReportCell = TimeReportRow.createCell(0);
            TimeReportCell.setCellValue(LanguageUtil.get(
                    (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                    resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay
                    + " "
                    + LanguageUtil.get(
                            (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                            resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay")
                    + ": " + ngaynhan_denngay);
            TimeReportCell.setCellStyle(style_tungay_denngay);
        } else if (ngaynhan_tungay == "" && ngaynhan_denngay != "") {
            rowNum = rowNum + 1;
            ngaynhan_tungay = "01/01/" + ngaynhan_denngay.substring(6, 10);
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10));
            HSSFRow TimeReportRow = sheet.createRow(rowNum);
            HSSFCell TimeReportCell = TimeReportRow.createCell(0);
            TimeReportCell.setCellValue(LanguageUtil.get(
                    (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                    resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay
                    + " "
                    + LanguageUtil.get(
                            (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                            resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay")
                    + ": " + ngaynhan_denngay);
            TimeReportCell.setCellStyle(style_tungay_denngay);
        } else {
            rowNum = rowNum + 1;
            ngaynhan_tungay = "01/01/" + year;
            ngaynhan_denngay = "01/12/" + year;
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10));
            HSSFRow TimeReportRow = sheet.createRow(rowNum);
            HSSFCell TimeReportCell = TimeReportRow.createCell(0);
            TimeReportCell.setCellValue(LanguageUtil.get(
                    (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                    resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay
                    + " "
                    + LanguageUtil.get(
                            (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                            resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay")
                    + ": " + ngaynhan_denngay);
            TimeReportCell.setCellStyle(style_tungay_denngay);
        }
        if (!trangthai_hoso.equals("")) {
            rowNum = rowNum + 1;
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10));
            HSSFRow TimeReportRow = sheet.createRow(rowNum);
            HSSFCell TimeReportCell = TimeReportRow.createCell(0);
            TimeReportCell
                    .setCellValue("("
                            + LanguageUtil.get(
                                    (PortletConfig) resourceRequest
                                            .getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                                    resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.trangthai")
                            + ": " + TrangThaiHoSoLocalServiceUtil
                                    .getTrangThaiHoSo(Long.parseLong(trangthai_hoso)).getTrangThai()
                            + ")");
            TimeReportCell.setCellStyle(style_trangthai);
        }
        rowNum = rowNum + 3;
        HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum);
        int colNum = 0;
        for (int i = 0; i < header.length; i++) {
            HSSFCell cell = headerRow.createCell(colNum);
            cell.setCellStyle(styleHeader);
            cell.setCellValue(header[i]);
            sheet.setColumnWidth(i, width[i]);
            colNum++;
        }
        rowNum++;
        long count = 0;
        if (list != null && list.size() > 0) {
            long linhvucId = 0L;
            for (int a = 0; a < list.size(); a++) {
                String tinh_trang = "";
                if (Validator.isNotNull(list.get(a).getNGAYTRAKETQUA())
                        && Validator.isNotNull(list.get(a).getNGAYHENTRAKETQUA())) {
                    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                    Date ngaytra = df.parse(list.get(a).getNGAYTRAKETQUA());
                    Date ngayhentra = df.parse(list.get(a).getNGAYHENTRAKETQUA());
                    if (ngaytra.before(ngayhentra)) {
                        tinh_trang = "Sm hn";
                    } else if (ngaytra.equals(ngayhentra)) {
                        tinh_trang = "ng hn";
                    } else if (ngaytra.after(ngayhentra)) {
                        tinh_trang = "Tr hn";
                    }
                }
                //khai bao dong va cac cell
                HSSFRow row = (HSSFRow) sheet.createRow(rowNum);
                HSSFCell cell = row.createCell(0);
                HSSFCell cell1 = row.createCell(1);
                HSSFCell cell2 = row.createCell(2);
                HSSFCell cell3 = row.createCell(3);
                HSSFCell cell4 = row.createCell(4);
                HSSFCell cell5 = row.createCell(5);
                HSSFCell cell6 = row.createCell(6);
                HSSFCell cell7 = row.createCell(7);
                HSSFCell cell8 = row.createCell(8);
                HSSFCell cell9 = row.createCell(9);
                HSSFCell cell10 = row.createCell(10);
                HSSFCell cell11 = row.createCell(11);
                HSSFCell cell12 = row.createCell(12);
                //set style cho cac cell
                cell.setCellStyle(styleCellCenter);
                cell1.setCellStyle(styleCellLeft);
                cell2.setCellStyle(styleCellCenter);
                cell3.setCellStyle(styleCellCenter);
                cell4.setCellStyle(styleCellCenter);
                cell5.setCellStyle(styleCellCenter);
                cell6.setCellStyle(styleCellCenter);
                cell7.setCellStyle(styleCellCenter);
                cell8.setCellStyle(styleCellCenter);
                cell9.setCellStyle(styleCellCenter);
                cell10.setCellStyle(styleCellCenter);
                cell11.setCellStyle(styleCellCenter);
                cell12.setCellStyle(styleCellCenter);
                //set gia tri cho cac cell
                cell.setCellValue((a + 1));
                cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil
                        .getNhomThuTucHanhChinh(list.get(a).getLINHVUCID()).getTen());
                cell2.setCellValue(list.get(a).getTEN());
                cell3.setCellValue(list.get(a).getMASOHOSO());
                cell4.setCellValue(list.get(a).getTRANGTHAI());
                cell5.setCellValue(list.get(a).getDIACHIHIENNAY());
                cell6.setCellValue(list.get(a).getDIENTHOAIDIDONG());
                cell7.setCellValue(list.get(a).getHOTENNGUOINOPHOSO());
                cell8.setCellValue(list.get(a).getNGUOIDAIDIENPHAPLUAT());
                cell9.setCellValue(list.get(a).getNGAYNHANHOSO());
                cell10.setCellValue(list.get(a).getNGAYTRAKETQUA());
                cell11.setCellValue(tinh_trang);
                cell12.setCellValue(list.get(a).getGHICHU());
                count = a;
                rowNum++;
            }
            count = count + 1;
        } else {
            //khai bao dong va cac cell
            HSSFRow row = (HSSFRow) sheet.createRow(rowNum);
            HSSFCell cell = row.createCell(0);
            HSSFCell cell1 = row.createCell(1);
            HSSFCell cell2 = row.createCell(2);
            HSSFCell cell3 = row.createCell(3);
            HSSFCell cell4 = row.createCell(4);
            HSSFCell cell5 = row.createCell(5);
            HSSFCell cell6 = row.createCell(6);
            HSSFCell cell7 = row.createCell(7);
            HSSFCell cell8 = row.createCell(8);
            HSSFCell cell9 = row.createCell(9);
            HSSFCell cell10 = row.createCell(10);
            HSSFCell cell11 = row.createCell(11);
            HSSFCell cell12 = row.createCell(12);
            //set style cho cac cell
            cell.setCellStyle(styleCellCenter);
            cell1.setCellStyle(styleCellLeft);
            cell2.setCellStyle(styleCellCenter);
            cell3.setCellStyle(styleCellCenter);
            cell4.setCellStyle(styleCellCenter);
            cell5.setCellStyle(styleCellCenter);
            cell6.setCellStyle(styleCellCenter);
            cell7.setCellStyle(styleCellCenter);
            cell8.setCellStyle(styleCellCenter);
            cell9.setCellStyle(styleCellCenter);
            cell10.setCellStyle(styleCellCenter);
            cell11.setCellStyle(styleCellCenter);
            cell12.setCellStyle(styleCellCenter);
            //set gia tri cho cac cell
            cell.setCellValue("");
            cell1.setCellValue("");
            cell2.setCellValue("");
            cell3.setCellValue("");
            cell4.setCellValue("");
            cell5.setCellValue("");
            cell6.setCellValue("");
            cell7.setCellValue("");
            cell8.setCellValue("");
            cell9.setCellValue("");
            cell10.setCellValue("");
            cell11.setCellValue("");
            cell12.setCellValue("");
            count = 0;
            rowNum++;
        }
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 11));
        HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum);
        HSSFCell cell = rowTong.createCell(0);
        HSSFCell cell1 = rowTong.createCell(1);
        HSSFCell cell2 = rowTong.createCell(2);
        HSSFCell cell3 = rowTong.createCell(3);
        HSSFCell cell4 = rowTong.createCell(4);
        HSSFCell cell5 = rowTong.createCell(5);
        HSSFCell cell6 = rowTong.createCell(6);
        HSSFCell cell7 = rowTong.createCell(7);
        HSSFCell cell8 = rowTong.createCell(8);
        HSSFCell cell9 = rowTong.createCell(9);
        HSSFCell cell10 = rowTong.createCell(10);
        HSSFCell cell11 = rowTong.createCell(11);
        HSSFCell cell12 = rowTong.createCell(12);
        //set style cho cac cell
        cell.setCellStyle(titleTongStyle);
        cell1.setCellStyle(styleCellCenter);
        cell2.setCellStyle(styleCellCenter);
        cell3.setCellStyle(styleCellCenter);
        cell4.setCellStyle(styleCellCenter);
        cell5.setCellStyle(styleCellCenter);
        cell6.setCellStyle(styleCellCenter);
        cell7.setCellStyle(styleCellCenter);
        cell8.setCellStyle(styleCellCenter);
        cell9.setCellStyle(styleCellCenter);
        cell10.setCellStyle(styleCellCenter);
        cell11.setCellStyle(styleCellCenter);
        cell12.setCellStyle(styleCellCenter);
        //set value for cells of rowtong 
        cell.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tonghoso"));
        cell12.setCellValue(count);

        rowNum = rowNum + 2;
        sheet.addMergedRegion(new Region(rowNum, (short) 1, rowNum, (short) 2));
        HSSFRow nguoilap_baocaoRow = sheet.createRow(rowNum);
        HSSFCell nguoilap_Cell = nguoilap_baocaoRow.createCell(1);
        nguoilap_Cell.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nguoilap"));
        nguoilap_Cell.setCellStyle(styleTitle);

        sheet.addMergedRegion(new Region(rowNum, (short) 7, rowNum, (short) 10));
        HSSFCell title_nguoibaocao_Cell = nguoilap_baocaoRow.createCell(7);
        title_nguoibaocao_Cell.setCellValue(LanguageUtil.get(
                (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG),
                resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nguoibaocao"));
        title_nguoibaocao_Cell.setCellStyle(styleTitle);

        rowNum = rowNum + 4;

        if (Validator.isNotNull(resourceRequest.getAttribute("THEME_DISPLAY"))) {
            CongChuc congchuc = new CongChucImpl();
            ThemeDisplay themdisplay = (ThemeDisplay) resourceRequest.getAttribute("THEME_DISPLAY");
            User user = themdisplay.getUser();
            TaiKhoanNguoiDung taikhoan = new TaiKhoanNguoiDungImpl();
            taikhoan = TaiKhoanNguoiDungLocalServiceUtil.findByTaiKhoanNguoiDungId(user.getUserId());
            congchuc = ActionUtil.getCongchucByTaiKhoanNguoiDungId(taikhoan.getId());

            sheet.addMergedRegion(new Region(rowNum, (short) 1, rowNum, (short) 2));
            HSSFRow nguoi_baocaoRow = sheet.createRow(rowNum);
            HSSFCell nguoibaocao_Cell = nguoi_baocaoRow.createCell(1);
            nguoibaocao_Cell.setCellValue(congchuc.getHoVaTen());
            nguoibaocao_Cell.setCellStyle(styleTitle);
        }

        resourceResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
        resourceResponse.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        resourceResponse.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaochitiet.xls");
        OutputStream out = resourceResponse.getPortletOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
    }
}

From source file:org.openmrs.module.kenyaemr.export.ExportLayouter.java

License:Open Source License

/**
 * Builds the column headers/*from  ww w  .j  a  va  2  s.co m*/
 * 
 * @param worksheet
 * @param startRowIndex
 *            starting row offset
 * @param startColIndex
 *            starting column offset
 */
public static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) {

    // Create font style for the headers
    Font font = worksheet.getWorkbook().createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Create cell style for the headers
    HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();
    headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    headerCellStyle.setWrapText(true);
    headerCellStyle.setFont(font);
    headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

    // Create the column headers
    HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2);
    rowHeader.setHeight((short) 500);

    HSSFCell cell1 = rowHeader.createCell(startColIndex + 0);
    cell1.setCellValue("Order Date");
    cell1.setCellStyle(headerCellStyle);

    HSSFCell cell2 = rowHeader.createCell(startColIndex + 1);
    cell2.setCellValue("Patient Identifier");
    cell2.setCellStyle(headerCellStyle);

    HSSFCell cell3 = rowHeader.createCell(startColIndex + 2);
    cell3.setCellValue("Name");
    cell3.setCellStyle(headerCellStyle);

    HSSFCell cell4 = rowHeader.createCell(startColIndex + 3);
    cell4.setCellValue("Age");
    cell4.setCellStyle(headerCellStyle);

    HSSFCell cell5 = rowHeader.createCell(startColIndex + 4);
    cell5.setCellValue("Gender");
    cell5.setCellStyle(headerCellStyle);

    HSSFCell cell6 = rowHeader.createCell(startColIndex + 5);
    cell6.setCellValue("Sample No.");
    cell6.setCellStyle(headerCellStyle);

    HSSFCell cell7 = rowHeader.createCell(startColIndex + 6);
    cell7.setCellValue("Lab");
    cell7.setCellStyle(headerCellStyle);

    HSSFCell cell8 = rowHeader.createCell(startColIndex + 7);
    cell8.setCellValue("Test");
    cell8.setCellStyle(headerCellStyle);

    HSSFCell cell9 = rowHeader.createCell(startColIndex + 8);
    cell9.setCellValue("Test name");
    cell9.setCellStyle(headerCellStyle);

    HSSFCell cell10 = rowHeader.createCell(startColIndex + 9);
    cell10.setCellValue("Result");
    cell10.setCellStyle(headerCellStyle);
}

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) {

    // Copy cell content
    destination.setCellType(source.getCellType());
    switch (source.getCellType()) {
    case HSSFCell.CELL_TYPE_BOOLEAN:
        destination.setCellValue(source.getBooleanCellValue());
        break;/* w  w w  .  j  ava  2s  .  c  o m*/
    case HSSFCell.CELL_TYPE_FORMULA:
    case HSSFCell.CELL_TYPE_STRING:
        destination.setCellValue(source.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        destination.setCellValue(source.getNumericCellValue());
        break;
    }

    // Copy cell style
    HSSFCellStyle sourceCellStyle = source.getCellStyle();
    HSSFCellStyle destinationCellStyle = workbook.createCellStyle();
    destinationCellStyle.setAlignment(sourceCellStyle.getAlignment());
    destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
    destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
    destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight());
    destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop());
    destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
    destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat());
    destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor());
    destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor());
    destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern());
    destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex()));
    destinationCellStyle.setHidden(sourceCellStyle.getHidden());
    destinationCellStyle.setIndention(sourceCellStyle.getIndention());
    destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
    destinationCellStyle.setLocked(sourceCellStyle.getLocked());
    destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
    destinationCellStyle.setRotation(sourceCellStyle.getRotation());
    destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
    destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
    destinationCellStyle.setWrapText(sourceCellStyle.getWrapText());
    destination.setCellStyle(destinationCellStyle);
}

From source file:org.tentackle.ui.FormTableUtilityPopup.java

License:Open Source License

/**
 * Converts the table to an excel spreadsheet.
 * @param file the output file/*from w w w  .  ja  v a  2  s.  c om*/
 * @param onlySelected true if export only selected rows
 * @throws IOException if export failed
 */
public void excel(File file, boolean onlySelected) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    TableModel model = table.getModel();
    TableColumnModel columnModel = table.getColumnModel();

    int[] selectedRows = onlySelected ? table.getSelectedRows() : new int[] {};

    int rows = onlySelected ? selectedRows.length : model.getRowCount(); // number of data rows
    int cols = columnModel.getColumnCount(); // number of data columns

    short srow = 0; // current spreadsheet row

    // local copies cause might be changed
    String xTitle = this.title;
    String xIntro = this.intro;

    if (xTitle == null) {
        // get default from window title
        Window parent = FormHelper.getParentWindow(table);
        try {
            // paint page-title
            xTitle = ((FormWindow) parent).getTitle();
        } catch (Exception e) {
            xTitle = null;
        }
    }
    if (xTitle != null) {
        HSSFRow row = sheet.createRow(srow);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs.setFont(font);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(cs);
        cell.setCellValue(new HSSFRichTextString(xTitle));
        // region rowFrom, colFrom, rowTo, colTo
        sheet.addMergedRegion(new CellRangeAddress(0, srow, 0, cols - 1));
        srow++;
    }

    if (xIntro != null || onlySelected) {
        HSSFRow row = sheet.createRow(srow);
        HSSFCell cell = row.createCell(0);
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setWrapText(true);
        cell.setCellStyle(cs);
        if (onlySelected) {
            if (xIntro == null) {
                xIntro = "";
            } else {
                xIntro += ", ";
            }
            xIntro += Locales.bundle.getString("<nur_selektierte_Zeilen>");
        }
        cell.setCellValue(new HSSFRichTextString(xIntro));
        sheet.addMergedRegion(new CellRangeAddress(srow, srow + 2, 0, cols - 1));
        srow += 3;
    }

    // column headers
    boolean isAbstractFormTableModel = model instanceof AbstractFormTableModel;
    srow++; // always skip one line
    HSSFRow row = sheet.createRow(srow);
    HSSFFont font = wb.createFont();
    font.setItalic(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle cs = wb.createCellStyle();
    cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cs.setFont(font);
    for (int c = 0; c < cols; c++) {
        HSSFCell cell = row.createCell(c);
        cell.setCellValue(new HSSFRichTextString(isAbstractFormTableModel
                ? ((AbstractFormTableModel) model)
                        .getDisplayedColumnName(columnModel.getColumn(c).getModelIndex())
                : model.getColumnName(columnModel.getColumn(c).getModelIndex())));
        cell.setCellStyle(cs);
    }
    srow++;

    // default cell-style for date
    HSSFCellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    // cellstyles for numbers
    List<HSSFCellStyle> numberStyles = new ArrayList<HSSFCellStyle>();
    HSSFDataFormat format = wb.createDataFormat();

    for (int r = 0; r < rows; r++) {

        int modelRow = onlySelected ? selectedRows[r] : r;

        row = sheet.createRow(srow + (short) r);

        for (int i = 0; i < cols; i++) {

            int c = columnModel.getColumn(i).getModelIndex();

            Object value = model.getValueAt(modelRow, c);

            HSSFCell cell = row.createCell(i);

            if (value instanceof Boolean) {
                cell.setCellValue(((Boolean) value).booleanValue());
            } else if (value instanceof BMoney) {
                BMoney money = (BMoney) value;
                cell.setCellValue(money.doubleValue());

                String fmt = "#,##0";
                if (money.scale() > 0) {
                    fmt += ".";
                    for (int j = 0; j < money.scale(); j++) {
                        fmt += "0";
                    }
                }
                // create format
                short fmtIndex = format.getFormat(fmt);

                // check if there is already a cellstyle with this scale
                Iterator<HSSFCellStyle> iter = numberStyles.iterator();
                boolean found = false;
                while (iter.hasNext()) {
                    cs = iter.next();
                    if (cs.getDataFormat() == fmtIndex) {
                        // reuse that
                        found = true;
                        break;
                    }
                }
                if (!found) {
                    // create a new style
                    cs = wb.createCellStyle();
                    cs.setDataFormat(fmtIndex);
                    numberStyles.add(cs);
                }
                cell.setCellStyle(cs);
            } else if (value instanceof Number) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellValue((Date) value);
                cell.setCellStyle(dateStyle);
            } else if (value instanceof GregorianCalendar) {
                cell.setCellValue((GregorianCalendar) value);
                cell.setCellStyle(dateStyle);
            } else if (value != null) {
                cell.setCellValue(new HSSFRichTextString(value.toString()));
            }
        }
    }

    // set the width for each column
    for (int c = 0; c < cols; c++) {
        short width = (short) (columnModel.getColumn(c).getWidth() * 45); // is a reasonable value
        sheet.setColumnWidth(c, width);
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();

    // open Excel
    URLHelper.openURL(file.getPath());
}

From source file:org.waterforpeople.mapping.dataexport.SurveyFormExporter.java

License:Open Source License

/**
 * Writes the survey as an XLS document//from  ww  w  . j  a va 2s .c  o m
 */
private void writeSurvey(String title, File fileName, List<QuestionGroupDto> groupList,
        Map<QuestionGroupDto, List<QuestionDto>> questions) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    sheet.setColumnWidth(0, COL_WIDTH);
    sheet.setColumnWidth(1, COL_WIDTH);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont headerFont = wb.createFont();
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    HSSFCellStyle questionStyle = wb.createCellStyle();
    questionStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    questionStyle.setWrapText(true);

    HSSFCellStyle depStyle = wb.createCellStyle();
    depStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont depFont = wb.createFont();
    depFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    depFont.setItalic(true);
    depStyle.setFont(depFont);

    int curRow = 0;
    HSSFRow row = sheet.createRow(curRow++);
    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
    createCell(row, 0, title, headerStyle);
    row = sheet.createRow(curRow++);
    createCell(row, 0, QUESTION_HEADER, headerStyle);
    createCell(row, 1, RESPONSE_HEADER, headerStyle);

    Long count = 1L;
    if (questions != null) {
        for (int i = 0; i < groupList.size(); i++) {
            HSSFRow groupHeaderRow = sheet.createRow(curRow++);
            sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
            createCell(groupHeaderRow, 0, groupList.get(i).getDisplayName(), headerStyle);

            for (QuestionDto q : questions.get(groupList.get(i))) {
                int questionStartRow = curRow;
                HSSFRow tempRow = sheet.createRow(curRow++);
                if (q.getQuestionDependency() != null) {
                    // if there is a dependency, add a row about not
                    // answering unless the dependency is satisfied
                    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
                    Long qNum = idToNumberMap.get(q.getQuestionDependency().getQuestionId());
                    createCell(tempRow, 0,
                            DEP_HEAD + q.getQuestionDependency().getAnswerValue() + DEP_HEAD_TO + "Q" + qNum,
                            depStyle);
                    tempRow = sheet.createRow(curRow++);
                    questionStartRow = curRow;
                }
                createCell(tempRow, 0, (count++) + ". " + formText(q.getText(), q.getTranslationMap()),
                        questionStyle);
                if (q.getOptionContainerDto() != null && q.getOptionContainerDto().getOptionsList() != null) {
                    for (QuestionOptionDto opt : q.getOptionContainerDto().getOptionsList()) {
                        tempRow = sheet.createRow(curRow++);
                        createCell(tempRow, 1, formText(opt.getText(), opt.getTranslationMap()) + SMALL_BLANK,
                                null);
                    }
                    sheet.addMergedRegion(new CellRangeAddress(questionStartRow, curRow - 1, 0, 0));
                } else {
                    createCell(tempRow, 1, BLANK, null);
                }
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream(fileName);
    wb.write(fileOut);
    fileOut.close();
}

From source file:reports.nutritionexcel.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    try {/*from   ww  w . j a  va 2s .c  o  m*/
        response.setContentType("text/html;charset=UTF-8");
        session = request.getSession();

        dbConn conn = new dbConn();
        //get the existing data for the month, year and facility that is already on session

        String month = "";
        String year = "";
        String facil = "";

        String form = "nutrition";

        //=====================================================================================================

        year = "2015";
        month = "5";
        String county = "";

        String header = "";

        String reportType = "";
        if (request.getParameter("reportType") != null) {
            reportType = request.getParameter("reportType");
        }
        String reportDuration = "";
        if (request.getParameter("reportDuration") != null) {
            reportDuration = request.getParameter("reportDuration");
        }
        if (request.getParameter("year") != null) {
            year = request.getParameter("year");
        }

        if (request.getParameter("facility") != null && reportType.equals("2")) {
            facil = request.getParameter("facility");

            String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='"
                    + facil + "'";
            conn.rs = conn.st.executeQuery(getfacil);

            while (conn.rs.next()) {

                header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + "    MFL CODE  :  "
                        + conn.rs.getString(2) + "  ";

            }

        }

        if (request.getParameter("county") != null && reportType.equals("2")) {
            county = request.getParameter("county");

            String getcounty = "select County from county where CountyID='" + county + "'";
            conn.rs = conn.st.executeQuery(getcounty);

            while (conn.rs.next()) {

                header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " ";

            }

        }

        if (request.getParameter("month") != null && reportDuration.equals("4")) {
            month = request.getParameter("month");

            String getmonth = "select name as monthname from month where id='" + month + "'";
            conn.rs = conn.st.executeQuery(getmonth);

            while (conn.rs.next()) {

                header += " MONTH : " + conn.rs.getString(1).toUpperCase() + " ";

            }

        }

        header += " YEAR : " + year + "";

        String facilitywhere = "";
        String yearwhere = "";
        String monthwhere = "";
        String countywhere = "";
        String duration = "";
        String semi_annual = "";
        String quarter = "";

        //==================================================================================================
        //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

        int yearcopy = Integer.parseInt(year);

        //        reportType="2";
        //        year=2015;
        //        reportDuration="3";
        String yearmonth = "" + year;
        int prevYear = yearcopy - 1;
        int maxYearMonth = 0;
        int monthcopy = 0;
        //        GET REPORT DURATION============================================

        if (reportDuration.equals("1")) {
            yearmonth += "_AnnualReport";
            duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09";
        } else if (reportDuration.equals("2")) {
            semi_annual = request.getParameter("semi_annual");
            //        semi_annual="2";
            if (semi_annual.equals("1")) {
                yearmonth = prevYear + "_Oct_" + year + "_Mar";
                duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03";
            } else {
                yearmonth += "_Apr_Sep";
                duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09";
            }
        }

        else if (reportDuration.equals("3")) {
            String startMonth, endMonth;
            quarter = request.getParameter("quarter");
            //       quarter="3";
            String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'";
            conn.rs = conn.st.executeQuery(getMonths);
            if (conn.rs.next() == true) {

                String months[] = conn.rs.getString(1).split(",");
                startMonth = months[0];
                endMonth = months[2];
                if (quarter.equals("1")) {
                    duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND "
                            + prevYear + "" + endMonth;
                    yearmonth = prevYear + "_" + conn.rs.getString(2);
                } else {
                    yearmonth = year + "_" + conn.rs.getString(2);
                    duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + ""
                            + endMonth;
                }
            }
        }

        else if (reportDuration.equals("4")) {
            monthcopy = Integer.parseInt(request.getParameter("month"));

            //     month=5;
            if (monthcopy >= 10) {
                yearmonth = prevYear + "_" + month;
                duration = " " + form + ".yearmonth=" + prevYear + "" + month;
            } else {
                duration = " " + form + ".yearmonth=" + year + "0" + month;
                yearmonth = year + "_(" + month + ")";
            }
        } else {
            duration = "";
        }

        //======================================================================   
        //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

        String getexistingdata = "";

        if (!county.equals("")) {

            countywhere = " and countyid = '" + county + "'";

        }

        if (!facil.equals("") && reportType.equalsIgnoreCase("2")) {

            facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'";

        }

        //String joinedwhwere=" where 1=1 "+yearwhere+" && "+duration;  

        String joinedwhwere = " where 1=1 " + facilitywhere + "  " + yearwhere + " && " + duration;

        //=====================================================================================================    

        //=====================================================================================================    

        //=====================================================================================================    

        //______________________________________________________________________________________
        //                       NOW CREATE THE WORKSHEETS          
        //______________________________________________________________________________________  

        HSSFWorkbook wb = new HSSFWorkbook();

        //______________________________________________________________________________________
        //______________________________________________________________________________________

        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 18);
        font.setFontName("Cambria");
        font.setColor((short) 0000);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font2 = wb.createFont();
        font2.setFontName("Cambria");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

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

        HSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        stylex.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.BLACK.index);
        fontx.setFontName("Cambria");
        fontx.setColor((short) 0000);
        fontx.setBoldweight((short) 07);
        stylex.setFont(fontx);
        stylex.setWrapText(true);

        HSSFSheet shet = wb.createSheet(form);

        //create headers for that worksheet

        HSSFRow rw = shet.createRow(0);
        rw.setHeightInPoints(25);
        HSSFCell cl0 = rw.createCell(0);
        cl0.setCellValue("3.1.9: Nutrition");
        cl0.setCellStyle(stylex);

        for (int a = 1; a <= 6; a++) {
            HSSFCell clx = rw.createCell(a);
            clx.setCellValue("");
            clx.setCellStyle(stylex);
        }

        HSSFRow rw1 = shet.createRow(1);
        rw1.setHeightInPoints(23);
        HSSFCell cl = rw1.createCell(0);
        cl.setCellValue(header);
        cl.setCellStyle(stylex);

        for (int a = 1; a <= 6; a++) {
            HSSFCell clx = rw1.createCell(a);
            clx.setCellValue("");
            clx.setCellStyle(stylex);
        }

        HSSFRow rw2 = shet.createRow(2);
        rw2.setHeightInPoints(23);
        HSSFCell cl3 = rw2.createCell(0);
        cl3.setCellValue("3.1.9.2 Population Based Nutrition Service Delivery");
        cl3.setCellStyle(stylex);
        HSSFCell cl3a = rw2.createCell(1);
        cl3a.setCellValue("");
        cl3a.setCellStyle(stylex);
        HSSFCell cl31 = rw2.createCell(2);
        cl31.setCellValue(
                "Number of people trained in child health care and nutrition through USG-supported health area programs");
        cl31.setCellStyle(style2);

        for (int a = 3; a <= 5; a++) {
            HSSFCell clx = rw2.createCell(a);
            clx.setCellValue("");
            clx.setCellStyle(style2);
        }

        shet.addMergedRegion(new CellRangeAddress(2, 11, 0, 1));
        shet.addMergedRegion(new CellRangeAddress(2, 2, 2, 5));
        shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
        shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
        shet.addMergedRegion(new CellRangeAddress(12, 20, 0, 0));

        shet.setColumnWidth(0, 3000);
        shet.setColumnWidth(1, 3000);
        shet.setColumnWidth(2, 16000);
        shet.setColumnWidth(3, 6900);
        shet.setColumnWidth(4, 3000);
        shet.setColumnWidth(5, 3000);

        getexistingdata = "select  sum(MCHCCNtrTM) as MCHCCNtrTM,    MCHCCNtrTMC,    sum(MCHCCNtrTF) as MCHCCNtrTF,    MCHCCNtrTFC,   sum(MCHCCNtrTT) as MCHCCNtrTT,    MCHCCNtrTTC,    sum(MCHNtrnCHWTrain) as MCHNtrnCHWTrain,   sum(MCHNutChRch) as MCHNutChRch,   sum(MCHNtrnWasted) as MCHNtrnWasted,   sum(MCHNtrnUnderweight) as MCHNtrnUnderweight,   sum(MCHChild5D) as MCHChild5D,   sum(MCHNtrnHealthFacility) as MCHNtrnHealthFacility,   sum(MCHVaccVitA) as MCHVaccVitA,   sum(MCHNtrnFoodOVC) as MCHNtrnFoodOVC,   sum(MCHNtrnFoodPLHIV) as MCHNtrnFoodPLHIV,   sum(MCHNtrnFood) as MCHNtrnFood,   sum(C51DCM) as C51DCM,   sum(C51DCF) as C51DCF,   sum(C51DC) as C51DC,    sum(C51DAM) as C51DAM,   sum(C51DAF) as C51DAF,   sum(C51DA) as C51DA,    sum(C51DP) as C51DP,    sum(C51DMT) as C51DMT,    sum(C51DFT) as C51DFT,    sum(C51DT) as C51DT  from "
                + form
                + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID )  on "
                + form + ".SubPartnerID = subpartnera.SubPartnerID   " + joinedwhwere + "  ";

        //System.out.println(getexistingdata);

        String MCHCCNtrTM = "";
        String MCHCCNtrTF = "";
        String MCHCCNtrTT = "";

        String MCHCCNtrTMC = "0";
        String MCHCCNtrTFC = "0";
        String MCHCCNtrTTC = "0";

        String MCHCCNtrTMCH = "0";
        String MCHCCNtrTFCH = "0";
        String MCHCCNtrTTCH = "0";

        String MCHNtrnCHWTrain = "";
        String MCHNutChRch = "";
        String MCHNtrnWasted = "";
        String MCHNtrnUnderweight = "";
        String MCHChild5D = "";
        String MCHNtrnHealthFacility = "";
        String MCHVaccVitA = "";
        String MCHNtrnFoodOVC = "";
        String MCHNtrnFoodPLHIV = "";
        String MCHNtrnFood = "";
        String C51DCM = "";
        String C51DCF = "";
        String C51DC = "";
        String C51DAM = "";
        String C51DAF = "";
        String C51DA = "";
        String C51DP = "";
        String C51DMT = "";
        String C51DFT = "";
        String C51DT = "";

        String distid = "";

        if (session.getAttribute("subcountyid") != null) {
            distid = session.getAttribute("subcountyid").toString();
        }

        int counter = 0;

        conn.rs = conn.st.executeQuery(getexistingdata);
        while (conn.rs.next()) {
            //now check if form was updated and if its one month after data entry
            //now load the column values here

            MCHCCNtrTM = conn.rs.getString("MCHCCNtrTM");
            if (MCHCCNtrTM == null) {
                MCHCCNtrTM = "";
            }

            MCHCCNtrTF = conn.rs.getString("MCHCCNtrTF");
            if (MCHCCNtrTF == null) {
                MCHCCNtrTF = "";
            }

            MCHCCNtrTT = conn.rs.getString("MCHCCNtrTT");
            if (MCHCCNtrTT == null) {
                MCHCCNtrTT = "";
            }

            MCHCCNtrTMC = conn.rs.getString("MCHCCNtrTMC");
            if (MCHCCNtrTMC == null) {
                MCHCCNtrTMC = "0";
            }

            MCHCCNtrTFC = conn.rs.getString("MCHCCNtrTFC");
            if (MCHCCNtrTFC == null) {
                MCHCCNtrTFC = "0";
            }

            MCHCCNtrTTC = conn.rs.getString("MCHCCNtrTTC");
            if (MCHCCNtrTTC == null) {
                MCHCCNtrTTC = "0";
            }

            MCHNtrnCHWTrain = conn.rs.getString("MCHNtrnCHWTrain");
            if (MCHNtrnCHWTrain == null) {
                MCHNtrnCHWTrain = "";
            }

            MCHNutChRch = conn.rs.getString("MCHNutChRch");
            if (MCHNutChRch == null) {
                MCHNutChRch = "";
            }

            MCHNtrnWasted = conn.rs.getString("MCHNtrnWasted");
            if (MCHNtrnWasted == null) {
                MCHNtrnWasted = "";
            }

            MCHNtrnUnderweight = conn.rs.getString("MCHNtrnUnderweight");
            if (MCHNtrnUnderweight == null) {
                MCHNtrnUnderweight = "";
            }

            MCHChild5D = conn.rs.getString("MCHChild5D");
            if (MCHChild5D == null) {
                MCHChild5D = "";
            }

            MCHNtrnHealthFacility = conn.rs.getString("MCHNtrnHealthFacility");
            if (MCHNtrnHealthFacility == null) {
                MCHNtrnHealthFacility = "";
            }

            MCHVaccVitA = conn.rs.getString("MCHVaccVitA");
            if (MCHVaccVitA == null) {
                MCHVaccVitA = "";
            }

            MCHNtrnFoodOVC = conn.rs.getString("MCHNtrnFoodOVC");
            if (MCHNtrnFoodOVC == null) {
                MCHNtrnFoodOVC = "";
            }

            MCHNtrnFoodPLHIV = conn.rs.getString("MCHNtrnFoodPLHIV");
            if (MCHNtrnFoodPLHIV == null) {
                MCHNtrnFoodPLHIV = "";
            }

            MCHNtrnFood = conn.rs.getString("MCHNtrnFood");
            if (MCHNtrnFood == null) {
                MCHNtrnFood = "";
            }

            C51DCM = conn.rs.getString("C51DCM");
            if (C51DCM == null) {
                C51DCM = "";
            }

            C51DCF = conn.rs.getString("C51DCF");
            if (C51DCF == null) {
                C51DCF = "";
            }

            C51DC = conn.rs.getString("C51DC");
            if (C51DC == null) {
                C51DC = "";
            }

            C51DAM = conn.rs.getString("C51DAM");
            if (C51DAM == null) {
                C51DAM = "";
            }

            C51DAF = conn.rs.getString("C51DAF");
            if (C51DAF == null) {
                C51DAF = "";
            }

            C51DA = conn.rs.getString("C51DA");
            if (C51DA == null) {
                C51DA = "";
            }

            C51DP = conn.rs.getString("C51DP");
            if (C51DP == null) {
                C51DP = "";
            }

            C51DMT = conn.rs.getString("C51DMT");
            if (C51DMT == null) {
                C51DMT = "";
            }

            C51DFT = conn.rs.getString("C51DFT");
            if (C51DFT == null) {
                C51DFT = "";
            }

            C51DT = conn.rs.getString("C51DT");
            if (C51DT == null) {
                C51DT = "";
            }

        }

        String createdtable = "";

        if (1 == 1) {

            int r = 3;

            HSSFCell cl3d = rw2.createCell(6);
            cl3d.setCellValue(MCHCCNtrTTC);
            cl3d.setCellStyle(style2);

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("Number of Men");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHCCNtrTM);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }

            //==================================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("Number of Women");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHCCNtrTF);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Total Number of people trained in child health care and nutrition through USG-supported health area programs");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHCCNtrTT);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }

            //============================================================================================   

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("Number of Community health workers trained in child health and/or nutrition");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnCHWTrain);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }

            //===========================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("Number of children reached by USG-supported nutrition programs");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNutChRch);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }

            //===========================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Total number of children under five who are wasted (with weight for height Z score < - 2)");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnWasted);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Total number of children under five who are underweight (with weight for age Z score < - 2) (see Indicator");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnUnderweight);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }
            //===========================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("Total number of children under five years");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHChild5D);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }
            //===========================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(style2);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Number of health facilities with established capacity to manage acute under-nutrition");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnHealthFacility);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));
                }
                r++;
            }
            //===========================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("HIV and Nutrition");
                clx0.setCellStyle(stylex);

                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Number of children under 5 years of age who received Vitamin A from USG-supported programs");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHVaccVitA);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    //shet.addMergedRegion(new CellRangeAddress(2,11,0,0));
                }
                r++;
            }
            //===========================================================================================================

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("C2.3.D:");
                clx1a.setCellStyle(stylex);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Number of HIV  positive clinically malnourished clients who received therapeutic and/or supplementary food < 18");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnFoodOVC);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    shet.addMergedRegion(new CellRangeAddress(r, r + 2, 1, 1));
                }
                r++;
            }
            //===========================================================================================================    

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Number of HIV  positive clinically malnourished clients who received therapeutic and/or supplementary food 18+ (PLHIV)");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnFoodPLHIV);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                r++;
            }

            //=========================================================================================================== 

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Number of HIV  positive clinically malnourished clients who received therapeutic and/or supplementary food - Total");
                clx.setCellStyle(style2);

                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue(MCHNtrnFood);
                clx1.setCellStyle(style2);

                for (int a = 3; a <= 5; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue("");
                    clx2.setCellStyle(style2);
                    shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                r++;
            }
            //===========================================================================================================      
            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);
                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("");
                clx.setCellStyle(style2);
                HSSFCell clx3 = rwx.createCell(3);
                clx3.setCellValue("");
                clx3.setCellStyle(style2);
                HSSFCell clx1 = rwx.createCell(6);
                clx1.setCellValue("");
                clx1.setCellStyle(style2);

                String haeade[] = { "Male", "Female", "Total" };

                for (int a = 4; a <= 6; a++) {
                    HSSFCell clx2 = rwx.createCell(a);
                    clx2.setCellValue(haeade[a - 4]);
                    clx2.setCellStyle(style2);
                    //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                shet.addMergedRegion(new CellRangeAddress(r, r, 2, 3));
                r++;
            }
            //=========================================================================================================== 
            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("C5.1.D:");
                clx1a.setCellStyle(stylex);

                HSSFCell clx1b = rwx.createCell(3);
                clx1b.setCellValue("< 18");
                clx1b.setCellStyle(style2);

                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue(
                        "Number of eligible clients who received food and / or other nutrition Services");
                clx.setCellStyle(style2);

                String haeade[] = { C51DCM, C51DCF, C51DC };

                for (int a = 0; a < haeade.length; a++) {
                    HSSFCell clx2 = rwx.createCell(a + 4);
                    clx2.setCellValue(haeade[a]);
                    clx2.setCellStyle(style2);
                    //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                //shet.addMergedRegion(new CellRangeAddress(r,r,2,3));       
                shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1));
                shet.addMergedRegion(new CellRangeAddress(r, r + 3, 2, 2));

                r++;
            }
            //===========================================================================================================    

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);

                HSSFCell clx1b = rwx.createCell(3);
                clx1b.setCellValue(">=18");
                clx1b.setCellStyle(style2);

                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("");
                clx.setCellStyle(style2);

                String haeade[] = { C51DAM, C51DAF, C51DA };

                for (int a = 0; a < haeade.length; a++) {
                    HSSFCell clx2 = rwx.createCell(a + 4);
                    clx2.setCellValue(haeade[a]);
                    clx2.setCellStyle(style2);
                    //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                //shet.addMergedRegion(new CellRangeAddress(r,r,2,3));       

                r++;
            }
            //===========================================================================================================    

            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);

                HSSFCell clx1b = rwx.createCell(3);
                clx1b.setCellValue("Pregnant/Lactating (PMTCT 1.5)");
                clx1b.setCellStyle(style2);

                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("");
                clx.setCellStyle(style2);

                String haeade[] = { "", "", C51DP };

                for (int a = 0; a < haeade.length; a++) {
                    HSSFCell clx2 = rwx.createCell(a + 4);
                    clx2.setCellValue(haeade[a]);
                    clx2.setCellStyle(style2);
                    //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                shet.addMergedRegion(new CellRangeAddress(r, r, 4, 5));

                r++;
            }
            //===========================================================================================================    
            if (1 == 1) {

                HSSFRow rwx = shet.createRow(r);
                rwx.setHeightInPoints(23);
                HSSFCell clx0 = rwx.createCell(0);
                clx0.setCellValue("");
                clx0.setCellStyle(stylex);
                HSSFCell clx1a = rwx.createCell(1);
                clx1a.setCellValue("");
                clx1a.setCellStyle(stylex);

                HSSFCell clx1b = rwx.createCell(3);
                clx1b.setCellValue("Total");
                clx1b.setCellStyle(style2);

                HSSFCell clx = rwx.createCell(2);
                clx.setCellValue("");
                clx.setCellStyle(style2);

                String haeade[] = { C51DMT, C51DFT, C51DT };

                for (int a = 0; a < haeade.length; a++) {
                    HSSFCell clx2 = rwx.createCell(a + 4);
                    clx2.setCellValue(haeade[a]);
                    clx2.setCellStyle(style2);
                    //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5));

                    // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1));
                }
                //shet.addMergedRegion(new CellRangeAddress(r,r,2,3));       

                r++;
            }
            //===========================================================================================================    

            createdtable += header
                    + "<br/><table   border='1' style='border-color: #e5e5e5;margin-bottom: 3px;font-size:11;'><tr class='form-actions'>"
                    + "<th rowspan='10' colspan='2'><b style='text-align:center;'>3.1.9.2 <br/> population-based Nutrition Service Delivery</b></th>"
                    + "<td colspan='4'><b>Number of People trained in child health care and nutrition through USG-supported health area programs</b></td><td><b>"
                    + MCHCCNtrTTC + "</b></td></tr>";
            createdtable += "<tr><td colspan='4' style='text-align:left;'>No of Men </td><td>" + MCHCCNtrTM
                    + "</td></tr>";
            createdtable += "<tr><td colspan='4' style='text-align:left;'>No of Women </td><td>" + MCHCCNtrTF
                    + "</td></tr>";
            createdtable += "<tr><td colspan='4'><b>Total Number of people trained in child health care and nutrition through USG-supported health area programs</b></td><td>"
                    + MCHCCNtrTT + "</td></tr>";
            createdtable += "<tr><td colspan='4'><b>Number of Community health workers trained in child health and/or nutrition</b></td><td>"
                    + MCHNtrnCHWTrain + "</td></tr>";
            createdtable += "<tr><td colspan='4'><b>Number of children reached by USG-supported nutrition programs</b></td><td>"
                    + MCHNutChRch + "</td></tr>";
            createdtable += "<tr><td colspan='4'>Total number of children under five who are wasted (with weight for height Z score < - 2)</td><td>"
                    + MCHNtrnWasted + "</td></tr>";
            createdtable += "<tr><td colspan='4'>Total number of children under five who are underweight (with weight for age Z score < - 2) (SEE Indicator </td><td>"
                    + MCHNtrnUnderweight + "</td></tr>";
            createdtable += "<tr><td colspan='4'>Total number of children under five years</td><td>"
                    + MCHChild5D + "</td></tr>";
            createdtable += "<tr><td colspan='4'>Number of health facilities with established capacity to manage acute under-nutrition</td><td>"
                    + MCHNtrnHealthFacility + "</td></tr>";

            createdtable += "<tr><td colspan='1' rowspan='9'>HIV and Nutrition</td><td></td> <td colspan='4'> <b> Number of children under 5 years of age who received Vitamin A from USG-supported programs </b> </td><td>"
                    + MCHVaccVitA + "</td></tr>";
            createdtable += "<tr><td rowspan='3' colspan='1'> <b> C2.3.D </b> </td> <td colspan='4'>Number of HIV  positive clinically malnourished clients who received therapeutic and/or supplementary food < 18 </td><td>"
                    + MCHNtrnFoodOVC + "</td></tr>";
            createdtable += "<tr><td colspan='4'>Number of HIV  positive clinically malnourished clients who received therapeutic and/or supplementary food 18+ (PLHIV)</td><td>"
                    + MCHNtrnFoodPLHIV + "</td></tr>";
            createdtable += "<tr><td colspan='4'>Number of HIV  positive clinically malnourished clients who received therapeutic and/or supplementary food -<b> Total</b></td><td>"
                    + MCHNtrnFood + "</td></tr>";

            createdtable += "<tr> <td></td><td></td><td></td><td><b>Male</b></td><td><b>Female</b></td><td><b>Total</b></td></tr>";
            createdtable += "<tr><td rowspan='4' colspan='1'> <b>C5.1.D </b> </td> <td colspan='1' rowspan='4'>Number of eligible clients who received food and / or other nutrition Services</td><td> <b> less Than 18 </b>  </td> <td>"
                    + C51DCM + "</td><td>" + C51DCF + "</td><td>" + C51DC + "</td></tr>";
            createdtable += "<tr><td> <b> >=18 </b> </td> <td>" + C51DAM + "</td><td>" + C51DAF + "</td><td>"
                    + C51DA + "</td></tr>";
            createdtable += "<tr><td colspan='3'> <b> Pregnant/Lactating (PMTCT 1.5)</b> </td><td>" + C51DP
                    + "</td></tr>";
            createdtable += "<tr><td> <b> Total </b>  </td> <td> " + C51DMT + " </td> <td> " + C51DFT
                    + " </td> <td> " + C51DT + " </td></tr></table>";

        }

        System.out.println(createdtable);

        if (conn.conn != null) {
            conn.conn.close();
        }
        if (conn.rs != null) {
            conn.rs.close();
        }
        if (conn.rs1 != null) {
            conn.rs1.close();
        }
        if (conn.rs2 != null) {
            conn.rs2.close();
        }
        if (conn.st != null) {
            conn.st.close();
        }
        if (conn.st2 != null) {
            conn.st2.close();
        }

        IdGenerator IG = new IdGenerator();
        String createdOn = IG.CreatedOn();

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=" + form + yearmonth + "_Generated_On_" + createdOn + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
        outStream.close();

    } catch (SQLException ex) {
        Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:ro.nextreports.engine.exporter.XlsExporter.java

License:Apache License

private HSSFCellStyle buildBandElementStyle(BandElement bandElement, Object value, int gridRow, int gridColumn,
        int colSpan) {
    Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan);
    HSSFCellStyle cellStyle;
    HSSFFont cellFont = null;//from   ww  w . j  a  va  2 s . c o  m
    int fontKey = -1;
    // we have to create new fonts and styles if some formatting conditions are met  
    // also for subreports we may have a subreportCellStyle passed by ReportBandElement 
    boolean cacheFont = false;
    boolean cacheAllFont = false;
    if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) {
        fontKey = getFontKey(style);
        if (fontKey != -1) {
            cellFont = condFonts.get(fontKey);
        }
        cellStyle = wb.createCellStyle();
        if (cellFont == null) {
            cellFont = wb.createFont();
            cacheFont = true;
        }
        modifiedStyle[gridRow][gridColumn] = false;
    } else {
        cellStyle = styles[gridRow][gridColumn];
        fontKey = getFontKey(style);
        if (fontKey != -1) {
            cellFont = fonts.get(fontKey);
        }
        if ((cellFont == null) && (bandElement != null)) {
            cellFont = wb.createFont();
            cacheAllFont = true;
        }
    }

    // HSSFPalette cellPal = wb.getCustomPalette();        
    if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) {
        String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY);
        cellFont.setFontName(val);
    }
    if (style.containsKey(StyleFormatConstants.FONT_SIZE)) {
        Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE);
        cellFont.setFontHeightInPoints(val.shortValue());
    }
    if (style.containsKey(StyleFormatConstants.FONT_COLOR)) {
        Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR);
        cellFont.setColor(ExcelColorSupport.getNearestColor(val));
    }
    if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) {
        if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        }
        if (StyleFormatConstants.FONT_STYLE_BOLD.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setItalic(true);
        }
        if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellFont.setItalic(true);
        }
    }

    if (cacheFont && (fontKey != -1)) {
        condFonts.put(fontKey, cellFont);
    }
    if (cacheAllFont && (fontKey != -1)) {
        fonts.put(fontKey, cellFont);
    }
    if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) {
        Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val));
    }
    if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) {
        if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT
                .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
            cellStyle.setAlignment((short) 1);
        }
        if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT
                .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
            cellStyle.setAlignment((short) 3);
        }
        if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER
                .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
            cellStyle.setAlignment((short) 2);
        }
    }

    if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) {
        if (StyleFormatConstants.VERTICAL_ALIGN_TOP
                .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        }
        if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE
                .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        }
        if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM
                .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
        }
    } else {
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    }

    short left = 0, right = 0, top = 0, bottom = 0;
    Color leftColor = Color.BLACK, rightColor = Color.BLACK, topColor = Color.BLACK, bottomColor = Color.BLACK;
    if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT);
        //
        left = val.shortValue();
        if (left == BORDER_THIN_VALUE) {
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        }
        if (left == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (left == BORDER_THICK_VALUE) {
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        }

        Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR);
        leftColor = color;
        cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT);
        //
        right = val.shortValue();
        if (right == BORDER_THIN_VALUE) {
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        }
        if (right == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (right == BORDER_THICK_VALUE) {
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
        }
        Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR);
        rightColor = color;
        cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_TOP)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP);
        //
        top = val.shortValue();
        if (top == BORDER_THIN_VALUE) {
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        }
        if (top == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (top == BORDER_THICK_VALUE) {
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
        }
        Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR);
        topColor = color;
        cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM);
        //
        bottom = val.shortValue();
        if (bottom == BORDER_THIN_VALUE) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        }
        if (bottom == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (bottom == BORDER_THICK_VALUE) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        }
        Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR);
        bottomColor = color;
        cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    border = new Border(left, right, top, bottom);
    border.setLeftColor(leftColor);
    border.setRightColor(rightColor);
    border.setTopColor(topColor);
    border.setBottomColor(bottomColor);

    if (cellFont != null) {
        cellStyle.setFont(cellFont);
    }

    if (style.containsKey(StyleFormatConstants.PATTERN)) {
        String pattern = (String) style.get(StyleFormatConstants.PATTERN);
        HSSFDataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat(pattern));
    }

    if (bandElement != null) {
        cellStyle.setWrapText(bandElement.isWrapText());
    }

    cellStyle = updateSubreportBandElementStyle(cellStyle, bandElement, value, gridRow, gridColumn, colSpan);

    return cellStyle;
}

From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java

@Override
public void exportData(String selectedFile, AnProperties props,
        ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception {
    if (selectedFile == null) {
        return;/*from  ww w . j  ava  2  s.  c  o m*/
    }
    System.setProperty("java.awt.headless", "true");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFPrintSetup ps = sheet.getPrintSetup();
    ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    ps.setLandscape(true);

    HSSFCellStyle cellStyleT = wb.createCellStyle();
    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font1.setFontHeightInPoints((short) 8);
    cellStyleT.setFont(font1);

    int rnumber = 0;
    HSSFRow row = sheet.createRow(0);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
    HSSFCell cell = row.createCell(0);

    cell.setCellValue(
            new HSSFRichTextString(" ???  ???  - "
                    + DateConverters.getDateToStr(LocalDate.now())));
    cell.setCellStyle(cellStyleT);
    rnumber++;

    if (props != null) {

        String val = " :  " + DateConverters.getDateToStr(props.getDateFrom())
                + "  " + DateConverters.getDateToStr(props.getDateTo());
        createHeaderCell(sheet, rnumber, val, cellStyleT);
        rnumber++;
        //----------
        if (props.isSalMode()) {
            String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE
                    ? " "
                    : "  ";
            val = "? " + type + "| " + props.getSaler().getNameSaler();
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
            if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) {
                type = props.isSalDirectSales() ? "? "
                        : " ";
                val = " : " + type;
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.isSalFixedDepartment()) {
                    val = " : ";
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }
        }
        if (props.isGoodMode()) {
            if (props.getGoodClassLev0() != null) {
                val = "? 1: " + props.getGoodClassLev0().getName();
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.getGoodClassLev1() != null) {
                    val = "? 2: " + props.getGoodClassLev1().getName();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    if (props.getGoodClassLev2() != null) {
                        val = "? 3: " + props.getGoodClassLev2().getName();
                        createHeaderCell(sheet, rnumber, val, cellStyleT);
                        rnumber++;
                        if (props.getGoodClassLev3() != null) {
                            val = "? 4: " + props.getGoodClassLev3().getName();
                            createHeaderCell(sheet, rnumber, val, cellStyleT);
                            rnumber++;
                        }
                    }
                }

                if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) {
                    val = " : " + props.getGoodCustomSearch();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

                if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) {
                    val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    val = " : "
                            + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }
                if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) {
                    val = "+: ";
                    for (LocalDate ld : props.getGoodsIndateLst()) {
                        val += DateConverters.getDateToStr(ld) + "; ";
                    }
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }

        }

        if (!props.getClLst().isEmpty()) {
            if (props.isClIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }

            val = "+: ";
            for (ClientBean cb : props.getClLst()) {
                val += cb.getClientCl() + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }

        if (!props.getVLst().isEmpty()) {
            if (props.isVIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }
            val = "";
            for (String v : props.getVLst()) {
                val += v + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }
    }
    //----------                                    

    HSSFCellStyle cellStyleH = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleH.setFont(font);
    cellStyleH.setWrapText(true);
    cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleH.setBorderLeft((short) 1);
    cellStyleH.setBorderRight((short) 1);
    cellStyleH.setBorderTop((short) 1);
    cellStyleH.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleHh = wb.createCellStyle();
    font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleHh.setFont(font);
    cellStyleHh.setWrapText(true);
    cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleHh.setBorderLeft((short) 1);
    cellStyleHh.setBorderRight((short) 1);
    cellStyleHh.setBorderTop((short) 1);
    cellStyleHh.setBorderBottom((short) 1);

    //filling table
    HSSFCellStyle cellStyleN = wb.createCellStyle();
    cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleN.setBorderLeft((short) 1);
    cellStyleN.setBorderRight((short) 1);
    cellStyleN.setBorderTop((short) 1);
    cellStyleN.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleI = wb.createCellStyle();
    cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setBorderLeft((short) 1);
    cellStyleI.setBorderRight((short) 1);
    cellStyleI.setBorderTop((short) 1);
    cellStyleI.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleD = wb.createCellStyle();
    cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
    HSSFDataFormat df = wb.createDataFormat();
    cellStyleD.setDataFormat(df.getFormat("#,##0.0"));
    cellStyleD.setBorderLeft((short) 1);
    cellStyleD.setBorderRight((short) 1);
    cellStyleD.setBorderTop((short) 1);
    cellStyleD.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleP = wb.createCellStyle();
    cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);

    cellStyleP.setDataFormat(df.getFormat("0.0\\%"));
    cellStyleP.setBorderLeft((short) 1);
    cellStyleP.setBorderRight((short) 1);
    cellStyleP.setBorderTop((short) 1);
    cellStyleP.setBorderBottom((short) 1);

    // filling column headers
    row = sheet.createRow(rnumber);

    String rowTitle = null;

    row = sheet.createRow(rnumber);
    row.setHeightInPoints(40);
    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString("?"));
    cell.setCellStyle(cellStyleH);

    for (int t = 0; t < columnTitles.size(); t++) {
        cell = row.createCell(t + 1);
        cell.setCellValue(new HSSFRichTextString(columnTitles.get(t)));
        cell.setCellStyle(cellStyleH);
    }

    // filling table with data
    rnumber++;

    for (ObservableList<Object> line : exportData) {
        row = sheet.createRow(rnumber);
        cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString((String) line.get(0)));
        cell.setCellStyle(cellStyleN);
        for (int i = 1; i < line.size(); i++) {
            Double val = (Double) line.get(i);
            cell = row.createCell(i);
            cell.setCellStyle(cellStyleD);
            cell.setCellValue(val);
        }
        rnumber++;
    }
    for (int t = 0; t < columnTitles.size(); t++) {
        sheet.autoSizeColumn((short) t);
    }
    saveWorkBook(wb, selectedFile);
    execute(selectedFile);
}