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

Introduction

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

Prototype

@Override
public void setWrapText(boolean wrapped) 

Source Link

Document

set whether the text should be wrapped

Usage

From source file:org.extremecomponents.table.view.ExtendXlsView.java

License:Apache License

private Map initStyles(HSSFWorkbook wb, short fontHeight) {
    Map result = new HashMap();
    HSSFCellStyle titleStyle = wb.createCellStyle();
    HSSFCellStyle textStyle = wb.createCellStyle();
    HSSFCellStyle boldStyle = wb.createCellStyle();
    HSSFCellStyle numericStyle = wb.createCellStyle();
    HSSFCellStyle numericStyleBold = wb.createCellStyle();
    HSSFCellStyle moneyStyle = wb.createCellStyle();
    HSSFCellStyle moneyStyleBold = wb.createCellStyle();
    HSSFCellStyle percentStyle = wb.createCellStyle();
    HSSFCellStyle percentStyleBold = wb.createCellStyle();

    result.put("titleStyle", titleStyle);
    result.put("textStyle", textStyle);
    result.put("boldStyle", boldStyle);
    result.put("numericStyle", numericStyle);
    result.put("numericStyleBold", numericStyleBold);
    result.put("moneyStyle", moneyStyle);
    result.put("moneyStyleBold", moneyStyleBold);
    result.put("percentStyle", percentStyle);
    result.put("percentStyleBold", percentStyleBold);

    HSSFDataFormat format = wb.createDataFormat();

    // Global fonts
    HSSFFont font = wb.createFont();// w  w  w .  j ava 2  s  .  co  m
    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    font.setColor(HSSFColor.BLACK.index);
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints(fontHeight);

    HSSFFont fontBold = wb.createFont();
    fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontBold.setColor(HSSFColor.BLACK.index);
    fontBold.setFontName(HSSFFont.FONT_ARIAL);
    fontBold.setFontHeightInPoints(fontHeight);

    // Money Style
    moneyStyle.setFont(font);
    moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyle.setDataFormat(format.getFormat(moneyFormat));

    // Money Style Bold
    moneyStyleBold.setFont(fontBold);
    moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));

    // Percent Style
    percentStyle.setFont(font);
    percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyle.setDataFormat(format.getFormat(percentFormat));

    // Percent Style Bold
    percentStyleBold.setFont(fontBold);
    percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyleBold.setDataFormat(format.getFormat(percentFormat));

    // Standard Numeric Style
    numericStyle.setFont(font);
    numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Standard Numeric Style Bold
    numericStyleBold.setFont(fontBold);
    numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Title Style
    titleStyle.setFont(font);
    titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    // Standard Text Style
    textStyle.setFont(font);
    textStyle.setWrapText(true);

    // Standard Text Style
    boldStyle.setFont(fontBold);
    boldStyle.setWrapText(true);

    return result;
}

---

From source file:org.jfree.workbook.io.XLWriter.java

License:Open Source License

/**
 * Creates a new cell in the HSSFRow, based on the contents of the supplied cell.
 * //from   www .  j a v  a2s.  c o  m
 * @param hssfWorkbook  the workbook.
 * @param hssfRow  the row.
 * @param worksheet  the worksheet.
 * @param cell  the cell.
 */
private void createHSSFCell(HSSFWorkbook hssfWorkbook, HSSFRow hssfRow, Worksheet worksheet, Cell cell) {

    HSSFCell hssfCell = hssfRow.createCell((short) cell.getColumn());
    if (cell.getType() == Cell.LABEL_TYPE) {
        hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        hssfCell.setCellValue(cell.getContent());
    } else if (cell.getType() == Cell.VALUE_TYPE) {
        hssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        hssfCell.setCellValue(Double.valueOf(cell.getContent()).doubleValue());
    } else if (cell.getType() == Cell.DATE_TYPE) {
        hssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        hssfCell.setCellValue(Double.valueOf(cell.getContent()).doubleValue());
    }

    HSSFCellStyle hssfStyle = hssfWorkbook.createCellStyle();

    Style style = worksheet.getStyles().getStyle(cell.getRow(), cell.getColumn());

    hssfStyle.setAlignment(getXLHorizontalAlignment(style));
    hssfStyle.setVerticalAlignment(getXLVerticalAlignment(style));
    hssfStyle.setWrapText(style.isWrapText());

    //hssfStyle.setFillBackgroundColor(style.getBackgroundColor());
    hssfStyle.setBorderTop(getXLBorder(style.getBorder().getTop()));
    hssfStyle.setBorderBottom(getXLBorder(style.getBorder().getBottom()));
    hssfStyle.setBorderLeft(getXLBorder(style.getBorder().getLeft()));
    hssfStyle.setBorderRight(getXLBorder(style.getBorder().getRight()));

    hssfCell.setCellStyle(hssfStyle);

}

---

From source file:org.metaeffekt.core.inventory.processor.writer.InventoryWriter.java

License:Apache License

private HSSFCellStyle createHeaderStyle(HSSFWorkbook myWorkBook) {
    Font headerFont = myWorkBook.createFont();
    headerFont.setColor(Font.COLOR_NORMAL);

    HSSFPalette palette = myWorkBook.getCustomPalette();
    HSSFColor headerColor = palette.findSimilarColor((byte) 149, (byte) 179, (byte) 215);

    HSSFCellStyle headerStyle = myWorkBook.createCellStyle();
    headerStyle.setFillForegroundColor(headerColor.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setFont(headerFont);/*from   w  ww.j  a v a  2 s  .  c om*/
    headerStyle.setWrapText(true);
    return headerStyle;
}

---

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

License:Apache License

/**
 * This is function excelTongHopChiTiet/*  w w w.  ja v a2 s .  c o m*/
 * Version: 1.0
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelTongHopChiTiet(ResourceRequest req, ResourceResponse res) {
    try {
        String id_donvi = req.getParameter("id_donvi");
        String id_loaihoso = req.getParameter("id_loaihoso");
        String thang = req.getParameter("thang");
        String nam = req.getParameter("nam");
        CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl();
        Set<Long> setIDNhomThuTuc = new HashSet<Long>(0);
        List<ReportTongHopChiTiet> listTongHopChiTiet = ReportTongHopChiTietLocalServiceUtil
                .getListReportTongHopChiTietGroupByIDThuTuc(id_donvi, id_loaihoso, thang, nam);
        if (id_donvi != null && !id_donvi.equals("0")) {
            coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(id_donvi));
        }
        for (int i = 0; i < listTongHopChiTiet.size(); i++) {
            setIDNhomThuTuc.add(listTongHopChiTiet.get(i).getNHOMTHUTUCHANHCHINHID());
        }
        HSSFWorkbook workbook = new HSSFWorkbook();

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

        // 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
        //header bao cao
        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) 14));
        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) 14));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);

        rowNum++;

        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude");

        String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.thutuc");
        String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong");
        String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan");
        String kytruocchuyenqua = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua");
        String hosodagiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet");
        String hosodanggiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet");
        String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso");
        String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu");
        String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso");
        String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som");
        String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung");
        String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre");
        String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan");
        String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan");
        String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso,
                chuadenhan, daquahan, cdruthoso, hosotralai, ghichu };

        rowNum++;
        int[] width = { 1500, 10000, 2000, 2000, 5000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000,
                3000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14));
        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) 6));

        rowNum = rowNum + 2;
        HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum);
        HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1);
        sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8));
        sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11));
        for (int i = 0; i < header.length; i++) {
            if (i < 5 || i > 11) {
                sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i));
            }
            HSSFCell cell = headerRow1.createCell(i);
            cell.setCellStyle(styleHeader);
            cell.setCellValue(header[i]);

            HSSFCell cell2 = headerRow2.createCell(i);
            cell2.setCellStyle(styleHeader);
            cell2.setCellValue(header[i]);
            if (i == 5) {
                cell.setCellValue(hosodagiaiquyet);
            }
            if (i == 9) {
                cell.setCellValue(hosodanggiaiquyet);
            }
            sheet.setColumnWidth(i, width[i]);
        }
        rowNum++;
        //end header bao cao
        //noi dung bao cao
        rowNum++;
        int stt = 0;
        int tongNhan = 0;
        int tongTiepNhan = 0;
        int tongTonDau = 0;
        int tongDaGiaiQuyet = 0;
        int tongSom = 0;
        int tongDung = 0;
        int tongTre = 0;
        int tongDangGiaiQuyet = 0;
        int tongChuaDenHan = 0;
        int tongDaQuaHan = 0;
        int tongRutHoSo = 0;
        int tongKhongHopLe = 0;
        int tongTonCuoi = 0;
        for (Long s : setIDNhomThuTuc) {
            stt++;
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 14));
            HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum);
            HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0);
            cellNhomTTHC.setCellValue(
                    stt + ". " + NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinh(s).getTen());
            cellNhomTTHC.setCellStyle(styleCellNhomTTHC);
            for (int i = 1; i < 15; i++) {
                rowNhomTTHC.createCell(i).setCellStyle(styleCellCenter);
            }
            rowNum++;
            for (int a = 0; a < listTongHopChiTiet.size(); a++) {
                // khai bao dong va cac cell
                if (listTongHopChiTiet.get(a).getNHOMTHUTUCHANHCHINHID() == s) {
                    tongNhan += listTongHopChiTiet.get(a).getTONGNHAN();
                    tongTiepNhan += listTongHopChiTiet.get(a).getTIEPNHAN();
                    tongTonDau += listTongHopChiTiet.get(a).getTONDAU();
                    tongDaGiaiQuyet += listTongHopChiTiet.get(a).getTONGDAGIAIQUYET();
                    tongSom += listTongHopChiTiet.get(a).getSOM();
                    tongDung += listTongHopChiTiet.get(a).getDUNG();
                    tongTre += listTongHopChiTiet.get(a).getTRE();
                    tongDangGiaiQuyet += listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET();
                    tongChuaDenHan += listTongHopChiTiet.get(a).getCHUADENHAN();
                    tongDaQuaHan += listTongHopChiTiet.get(a).getQUAHAN();
                    tongRutHoSo += listTongHopChiTiet.get(a).getRUTHOSO();
                    tongKhongHopLe += listTongHopChiTiet.get(a).getKHONGHOPLE();
                    tongTonCuoi += listTongHopChiTiet.get(a).getTONCUOI();
                    sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
                    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);
                    HSSFCell cell13 = row.createCell(13);
                    HSSFCell cell14 = row.createCell(14);
                    // set style cho cac cell
                    cell.setCellStyle(styleCellLeft);
                    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);
                    cell13.setCellStyle(styleCellCenter);
                    cell14.setCellStyle(styleCellCenter);
                    // set gia tri cho cac cell
                    cell.setCellValue(ThuTucHanhChinhLocalServiceUtil
                            .getThuTucHanhChinh(listTongHopChiTiet.get(a).getTHUTUCHANHCHINHID()).getTen());
                    //cell1.setCellValue("");
                    cell2.setCellValue(listTongHopChiTiet.get(a).getTONGNHAN());
                    cell3.setCellValue(listTongHopChiTiet.get(a).getTIEPNHAN());
                    cell4.setCellValue(listTongHopChiTiet.get(a).getTONDAU());
                    cell5.setCellValue(listTongHopChiTiet.get(a).getTONGDAGIAIQUYET());
                    cell6.setCellValue(listTongHopChiTiet.get(a).getSOM());
                    cell7.setCellValue(listTongHopChiTiet.get(a).getDUNG());
                    cell8.setCellValue(listTongHopChiTiet.get(a).getTRE());
                    cell9.setCellValue(listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET());
                    cell10.setCellValue(listTongHopChiTiet.get(a).getCHUADENHAN());
                    cell11.setCellValue(listTongHopChiTiet.get(a).getQUAHAN());
                    cell12.setCellValue(listTongHopChiTiet.get(a).getRUTHOSO());
                    cell13.setCellValue(listTongHopChiTiet.get(a).getKHONGHOPLE());
                    cell14.setCellValue(listTongHopChiTiet.get(a).getTONCUOI());
                    rowNum++;
                }
            }
        }
        //row tong
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
        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);
        HSSFCell cell13 = row.createCell(13);
        HSSFCell cell14 = row.createCell(14);
        // set style cho cac cell
        cell.setCellStyle(styleHeader);
        cell1.setCellStyle(styleHeader);
        cell2.setCellStyle(styleHeader);
        cell3.setCellStyle(styleHeader);
        cell4.setCellStyle(styleHeader);
        cell5.setCellStyle(styleHeader);
        cell6.setCellStyle(styleHeader);
        cell7.setCellStyle(styleHeader);
        cell8.setCellStyle(styleHeader);
        cell9.setCellStyle(styleHeader);
        cell10.setCellStyle(styleHeader);
        cell11.setCellStyle(styleHeader);
        cell12.setCellStyle(styleHeader);
        cell13.setCellStyle(styleHeader);
        cell14.setCellStyle(styleHeader);
        // set gia tri cho cac cell
        cell.setCellValue("Tng");
        //cell1.setCellValue("");
        cell2.setCellValue(tongNhan);
        cell3.setCellValue(tongTiepNhan);
        cell4.setCellValue(tongTonDau);
        cell5.setCellValue(tongDaGiaiQuyet);
        cell6.setCellValue(tongSom);
        cell7.setCellValue(tongDung);
        cell8.setCellValue(tongTre);
        cell9.setCellValue(tongDangGiaiQuyet);
        cell10.setCellValue(tongChuaDenHan);
        cell11.setCellValue(tongDaQuaHan);
        cell12.setCellValue(tongRutHoSo);
        cell13.setCellValue(tongKhongHopLe);
        cell14.setCellValue(tongTonCuoi);
        //ket thuc row tong
        //ket thuc noi dung bao cao

        res.setContentType("application/vnd.ms-excel");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotonghop.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 checkHosoByNHOMTTHCID
 * Version: 1.0// www.j  ava  2s.  c om
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelTongHopNam(ResourceRequest req, ResourceResponse res) {
    try {
        String id_donvi = req.getParameter("id_donvi");
        String id_linhvuc = req.getParameter("id_linhvuc");
        String nam = req.getParameter("nam");
        CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl();
        Set<Long> setIDNhomThuTuc = new HashSet<Long>(0);
        List<ReportTongHop> listTongHopChiTiet = ActionUtil.getListReportTongHop(id_donvi, id_linhvuc, nam);
        if (id_donvi != null && !id_donvi.equals("0")) {
            coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(id_donvi));
        }
        for (int i = 0; i < listTongHopChiTiet.size(); i++) {
            setIDNhomThuTuc.add(listTongHopChiTiet.get(i).getNHOMTHUTUCHANHCHINHID());
        }
        HSSFWorkbook workbook = new HSSFWorkbook();

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

        // 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
        //header bao cao
        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) 14));
        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) 14));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);

        rowNum++;

        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude");

        String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.thang");
        String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong");
        String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan");
        String kytruocchuyenqua = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua");
        String hosodagiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet");
        String hosodanggiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet");
        String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso");
        String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu");
        String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso");
        String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som");
        String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung");
        String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre");
        String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan");
        String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan");
        String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso,
                chuadenhan, daquahan, cdruthoso, hosotralai, ghichu };

        rowNum++;
        int[] width = { 1500, 10000, 2000, 2000, 5000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000,
                3000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14));
        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) 6));

        rowNum = rowNum + 2;
        HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum);
        HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1);
        sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8));
        sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11));
        for (int i = 0; i < header.length; i++) {
            if (i < 5 || i > 11) {
                sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i));
            }
            HSSFCell cell = headerRow1.createCell(i);
            cell.setCellStyle(styleHeader);
            cell.setCellValue(header[i]);

            HSSFCell cell2 = headerRow2.createCell(i);
            cell2.setCellStyle(styleHeader);
            cell2.setCellValue(header[i]);
            if (i == 5) {
                cell.setCellValue(hosodagiaiquyet);
            }
            if (i == 9) {
                cell.setCellValue(hosodanggiaiquyet);
            }
            sheet.setColumnWidth(i, width[i]);
        }
        rowNum++;
        //end header bao cao
        //noi dung bao cao
        rowNum++;
        int stt = 0;
        int tongNhan = 0;
        int tongTiepNhan = 0;
        int tongTonDau = 0;
        int tongDaGiaiQuyet = 0;
        int tongSom = 0;
        int tongDung = 0;
        int tongTre = 0;
        int tongDangGiaiQuyet = 0;
        int tongChuaDenHan = 0;
        int tongDaQuaHan = 0;
        int tongRutHoSo = 0;
        int tongKhongHopLe = 0;
        int tongTonCuoi = 0;
        for (Long s : setIDNhomThuTuc) {
            stt++;
            sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 14));
            HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum);
            HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0);
            cellNhomTTHC.setCellValue(
                    stt + ". " + NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinh(s).getTen());
            cellNhomTTHC.setCellStyle(styleCellNhomTTHC);
            for (int i = 1; i < 15; i++) {
                rowNhomTTHC.createCell(i).setCellStyle(styleCellCenter);
            }
            rowNum++;
            for (int a = 0; a < listTongHopChiTiet.size(); a++) {
                // khai bao dong va cac cell
                if (listTongHopChiTiet.get(a).getNHOMTHUTUCHANHCHINHID() == s) {
                    tongNhan += listTongHopChiTiet.get(a).getTONGNHAN();
                    tongTiepNhan += listTongHopChiTiet.get(a).getTIEPNHAN();
                    tongTonDau += listTongHopChiTiet.get(a).getTONDAU();
                    tongDaGiaiQuyet += listTongHopChiTiet.get(a).getTONGDAGIAIQUYET();
                    tongSom += listTongHopChiTiet.get(a).getSOM();
                    tongDung += listTongHopChiTiet.get(a).getDUNG();
                    tongTre += listTongHopChiTiet.get(a).getTRE();
                    tongDangGiaiQuyet += listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET();
                    tongChuaDenHan += listTongHopChiTiet.get(a).getCHUADENHAN();
                    tongDaQuaHan += listTongHopChiTiet.get(a).getQUAHAN();
                    tongRutHoSo += listTongHopChiTiet.get(a).getRUTHOSO();
                    tongKhongHopLe += listTongHopChiTiet.get(a).getKHONGHOPLE();
                    tongTonCuoi += listTongHopChiTiet.get(a).getTONCUOI();
                    sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
                    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);
                    HSSFCell cell13 = row.createCell(13);
                    HSSFCell cell14 = row.createCell(14);
                    // set style cho cac cell
                    cell.setCellStyle(styleCellLeft);
                    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);
                    cell13.setCellStyle(styleCellCenter);
                    cell14.setCellStyle(styleCellCenter);
                    // set gia tri cho cac cell
                    cell.setCellValue("Thng " + listTongHopChiTiet.get(a).getTHANG());
                    //cell1.setCellValue("");
                    cell2.setCellValue(listTongHopChiTiet.get(a).getTONGNHAN());
                    cell3.setCellValue(listTongHopChiTiet.get(a).getTIEPNHAN());
                    cell4.setCellValue(listTongHopChiTiet.get(a).getTONDAU());
                    cell5.setCellValue(listTongHopChiTiet.get(a).getTONGDAGIAIQUYET());
                    cell6.setCellValue(listTongHopChiTiet.get(a).getSOM());
                    cell7.setCellValue(listTongHopChiTiet.get(a).getDUNG());
                    cell8.setCellValue(listTongHopChiTiet.get(a).getTRE());
                    cell9.setCellValue(listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET());
                    cell10.setCellValue(listTongHopChiTiet.get(a).getCHUADENHAN());
                    cell11.setCellValue(listTongHopChiTiet.get(a).getQUAHAN());
                    cell12.setCellValue(listTongHopChiTiet.get(a).getRUTHOSO());
                    cell13.setCellValue(listTongHopChiTiet.get(a).getKHONGHOPLE());
                    cell14.setCellValue(listTongHopChiTiet.get(a).getTONCUOI());
                    rowNum++;
                }
            }
        }
        //row tong
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
        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);
        HSSFCell cell13 = row.createCell(13);
        HSSFCell cell14 = row.createCell(14);
        // set style cho cac cell
        cell.setCellStyle(styleHeader);
        cell1.setCellStyle(styleHeader);
        cell2.setCellStyle(styleHeader);
        cell3.setCellStyle(styleHeader);
        cell4.setCellStyle(styleHeader);
        cell5.setCellStyle(styleHeader);
        cell6.setCellStyle(styleHeader);
        cell7.setCellStyle(styleHeader);
        cell8.setCellStyle(styleHeader);
        cell9.setCellStyle(styleHeader);
        cell10.setCellStyle(styleHeader);
        cell11.setCellStyle(styleHeader);
        cell12.setCellStyle(styleHeader);
        cell13.setCellStyle(styleHeader);
        cell14.setCellStyle(styleHeader);
        // set gia tri cho cac cell
        cell.setCellValue("Tng");
        //cell1.setCellValue("");
        cell2.setCellValue(tongNhan);
        cell3.setCellValue(tongTiepNhan);
        cell4.setCellValue(tongTonDau);
        cell5.setCellValue(tongDaGiaiQuyet);
        cell6.setCellValue(tongSom);
        cell7.setCellValue(tongDung);
        cell8.setCellValue(tongTre);
        cell9.setCellValue(tongDangGiaiQuyet);
        cell10.setCellValue(tongChuaDenHan);
        cell11.setCellValue(tongDaQuaHan);
        cell12.setCellValue(tongRutHoSo);
        cell13.setCellValue(tongKhongHopLe);
        cell14.setCellValue(tongTonCuoi);
        //ket thuc row tong
        //ket thuc noi dung bao cao

        res.setContentType("application/vnd.ms-excel");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotonghop.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 checkHosoByNHOMTTHCID
 * Version: 1.0/*from w w  w  .j  ava 2s .  c  o  m*/
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelTonghopTongquat(ResourceRequest req, ResourceResponse res) {
    try {
        String thang = req.getParameter("thang");
        String nam = req.getParameter("nam");
        String id_donvi = req.getParameter("id_donvi");
        String id_linhvuc = req.getParameter("id_linhvuc");
        String coQuanQuanLyId = req.getParameter("id_coquanquanly");
        CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl();
        if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) {
            coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId));
        }
        List<ReportTongHop> listtonghop = ReportTongHopLocalServiceUtil
                .getListReportTongHopGroupByIDNhomThuTuc(id_donvi, id_linhvuc, thang, nam);
        HSSFWorkbook workbook = new HSSFWorkbook();

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

        // 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 styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle();
        styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellRightNobold.setWrapText(true);

        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
        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) 14));
        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) 14));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);

        rowNum++;

        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude");

        String nhomtthc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.nhomtthc");
        String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong");
        String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan");
        String kytruocchuyenqua = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua");
        String hosodagiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet");
        String hosodanggiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet");
        String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso");
        String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu");
        String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso");
        String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som");
        String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung");
        String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre");
        String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan");
        String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan");
        String[] header = { "STT", nhomtthc, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso,
                chuadenhan, daquahan, cdruthoso, hosotralai, ghichu };

        rowNum++;
        int[] width = { 1500, 10000, 2000, 2000, 3000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000,
                3000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14));
        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) 6));

        rowNum = rowNum + 2;
        HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum);
        HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1);
        sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8));
        sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11));
        for (int i = 0; i < header.length; i++) {
            if (i < 5 || i > 11) {
                sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i));
            }
            HSSFCell cell = headerRow1.createCell(i);
            cell.setCellStyle(styleHeader);
            cell.setCellValue(header[i]);

            HSSFCell cell2 = headerRow2.createCell(i);
            cell2.setCellStyle(styleHeader);
            cell2.setCellValue(header[i]);
            if (i == 5) {
                cell.setCellValue(hosodagiaiquyet);
            }
            if (i == 9) {
                cell.setCellValue(hosodanggiaiquyet);
            }
            sheet.setColumnWidth(i, width[i]);
        }
        rowNum += 2;
        int tongNhan = 0;
        int tongTiepNhan = 0;
        int tongTonDau = 0;
        int tongDaGiaiQuyet = 0;
        int tongSom = 0;
        int tongDung = 0;
        int tongTre = 0;
        int tongDangGiaiQuyet = 0;
        int tongChuaDenHan = 0;
        int tongDaQuaHan = 0;
        int tongRutHoSo = 0;
        int tongKhongHopLe = 0;
        int tongTonCuoi = 0;
        int stt = 1;
        for (int a = 0; a < listtonghop.size(); a++) {
            tongNhan += listtonghop.get(a).getTONGNHAN();
            tongTiepNhan += listtonghop.get(a).getTIEPNHAN();
            tongTonDau += listtonghop.get(a).getTONDAU();
            tongDaGiaiQuyet += listtonghop.get(a).getTONGDAGIAIQUYET();
            tongSom += listtonghop.get(a).getSOM();
            tongDung += listtonghop.get(a).getDUNG();
            tongTre += listtonghop.get(a).getTRE();
            tongDangGiaiQuyet += listtonghop.get(a).getTONGDANGGIAIQUYET();
            tongChuaDenHan += listtonghop.get(a).getCHUADENHAN();
            tongDaQuaHan += listtonghop.get(a).getQUAHAN();
            tongRutHoSo += listtonghop.get(a).getRUTHOSO();
            tongKhongHopLe += listtonghop.get(a).getKHONGHOPLE();
            tongTonCuoi += listtonghop.get(a).getTONCUOI();
            // 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);
            HSSFCell cell13 = row.createCell(13);
            HSSFCell cell14 = row.createCell(14);
            // set style cho cac cell
            cell.setCellStyle(styleCellLeft);
            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);
            cell13.setCellStyle(styleCellCenter);
            cell14.setCellStyle(styleCellCenter);
            // set gia tri cho cac cell
            cell.setCellValue(stt);
            //cell1.setCellValue("");
            cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil
                    .getNhomThuTucHanhChinh(listtonghop.get(a).getNHOMTHUTUCHANHCHINHID()).getTen());
            cell2.setCellValue(listtonghop.get(a).getTONGNHAN());
            cell3.setCellValue(listtonghop.get(a).getTIEPNHAN());
            cell4.setCellValue(listtonghop.get(a).getTONDAU());
            cell5.setCellValue(listtonghop.get(a).getTONGDAGIAIQUYET());
            cell6.setCellValue(listtonghop.get(a).getSOM());
            cell7.setCellValue(listtonghop.get(a).getDUNG());
            cell8.setCellValue(listtonghop.get(a).getTRE());
            cell9.setCellValue(listtonghop.get(a).getTONGDANGGIAIQUYET());
            cell10.setCellValue(listtonghop.get(a).getCHUADENHAN());
            cell11.setCellValue(listtonghop.get(a).getQUAHAN());
            cell12.setCellValue(listtonghop.get(a).getRUTHOSO());
            cell13.setCellValue(listtonghop.get(a).getKHONGHOPLE());
            cell14.setCellValue(listtonghop.get(a).getTONCUOI());
            rowNum++;
            stt++;
        }
        //row tong
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
        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);
        HSSFCell cell13 = row.createCell(13);
        HSSFCell cell14 = row.createCell(14);
        // set style cho cac cell
        cell.setCellStyle(styleHeader);
        cell1.setCellStyle(styleHeader);
        cell2.setCellStyle(styleHeader);
        cell3.setCellStyle(styleHeader);
        cell4.setCellStyle(styleHeader);
        cell5.setCellStyle(styleHeader);
        cell6.setCellStyle(styleHeader);
        cell7.setCellStyle(styleHeader);
        cell8.setCellStyle(styleHeader);
        cell9.setCellStyle(styleHeader);
        cell10.setCellStyle(styleHeader);
        cell11.setCellStyle(styleHeader);
        cell12.setCellStyle(styleHeader);
        cell13.setCellStyle(styleHeader);
        cell14.setCellStyle(styleHeader);
        // set gia tri cho cac cell
        cell.setCellValue("Tng");
        //cell1.setCellValue("");
        cell2.setCellValue(tongNhan);
        cell3.setCellValue(tongTiepNhan);
        cell4.setCellValue(tongTonDau);
        cell5.setCellValue(tongDaGiaiQuyet);
        cell6.setCellValue(tongSom);
        cell7.setCellValue(tongDung);
        cell8.setCellValue(tongTre);
        cell9.setCellValue(tongDangGiaiQuyet);
        cell10.setCellValue(tongChuaDenHan);
        cell11.setCellValue(tongDaQuaHan);
        cell12.setCellValue(tongRutHoSo);
        cell13.setCellValue(tongKhongHopLe);
        cell14.setCellValue(tongTonCuoi);
        //ket thuc row tong
        //ket thuc noi dung bao cao

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

---

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

License:Apache License

/**
 * This is function checkHosoByNHOMTTHCID
 * Version: 1.0/*from w w w. ja  v a  2 s  .com*/
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelThongke(ResourceRequest req, ResourceResponse res) {
    try {
        String thang = req.getParameter("thang");
        String nam = req.getParameter("nam");
        String coQuanQuanLyId = req.getParameter("id_coquanquanly");
        //         CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl();
        //         if(coQuanQuanLyId!=null && !coQuanQuanLyId.equals("0")){
        //            coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId));
        //         }
        List<ReportThongKe> listthongke = ReportThongKeLocalServiceUtil
                .getListReportThongKeGroupByIDCoQuan(coQuanQuanLyId, thang, nam);
        HSSFWorkbook workbook = new HSSFWorkbook();

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

        // 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 styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle();
        styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellRightNobold.setWrapText(true);

        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
        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) 14));
        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("");
        rowTenDonVi.getCell(0).setCellStyle(styleCellHeader);

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

        rowNum++;

        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tieude");

        String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.coquan");
        String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong");
        String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan");
        String kytruocchuyenqua = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua");
        String hosodagiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet");
        String hosodanggiaiquyet = ActionUtil.getLanguage(req,
                "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet");
        String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso");
        String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu");
        String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso");
        String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som");
        String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung");
        String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre");
        String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan");
        String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan");
        String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso,
                chuadenhan, daquahan, cdruthoso, hosotralai, ghichu };

        rowNum++;
        int[] width = { 1500, 10000, 2000, 2000, 3000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000,
                3000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14));
        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) 6));

        rowNum = rowNum + 2;
        HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum);
        HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1);
        sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8));
        sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11));
        for (int i = 0; i < header.length; i++) {
            if (i < 5 || i > 11) {
                sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i));
            }
            HSSFCell cell = headerRow1.createCell(i);
            cell.setCellStyle(styleHeader);
            cell.setCellValue(header[i]);

            HSSFCell cell2 = headerRow2.createCell(i);
            cell2.setCellStyle(styleHeader);
            cell2.setCellValue(header[i]);
            if (i == 5) {
                cell.setCellValue(hosodagiaiquyet);
            }
            if (i == 9) {
                cell.setCellValue(hosodanggiaiquyet);
            }
            sheet.setColumnWidth(i, width[i]);
        }
        rowNum += 2;
        long tongnhan = 0;
        long tongtiepnhan = 0;
        long tondau = 0;
        long dagiaiquyet = 0;
        long tongsom = 0;
        long tongdung = 0;
        long tongtre = 0;
        long danggiaiquyet = 0;
        long tongchuadenhan = 0;
        long quahan = 0;
        long ruthoso = 0;
        long khonghople = 0;
        long toncuoi = 0;
        int stt = 1;
        for (int a = 0; a < listthongke.size(); a++) {
            ReportThongKe thongke = listthongke.get(a);
            tongnhan += thongke.getTONGNHAN();
            tongtiepnhan += thongke.getTIEPNHAN();
            tondau += thongke.getTONDAU();
            dagiaiquyet += thongke.getTONGDAGIAIQUYET();
            tongsom += thongke.getSOM();
            tongdung += thongke.getDUNG();
            tongtre += thongke.getTRE();
            danggiaiquyet += thongke.getTONGDANGGIAIQUYET();
            tongchuadenhan += thongke.getCHUADENHAN();
            quahan += thongke.getQUAHAN();
            ruthoso += thongke.getRUTHOSO();
            khonghople += thongke.getKHONGHOPLE();
            toncuoi += thongke.getTONCUOI();
            // 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);
            HSSFCell cell13 = row.createCell(13);
            HSSFCell cell14 = row.createCell(14);
            // set style cho cac cell
            cell.setCellStyle(styleCellLeft);
            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);
            cell13.setCellStyle(styleCellCenter);
            cell14.setCellStyle(styleCellCenter);
            // set gia tri cho cac cell
            cell.setCellValue(stt);
            //cell1.setCellValue("");
            cell1.setCellValue(CoQuanQuanLyLocalServiceUtil
                    .getCoQuanQuanLy(listthongke.get(a).getCOQUANQUANLYID()).getTen());
            cell2.setCellValue(listthongke.get(a).getTONGNHAN());
            cell3.setCellValue(listthongke.get(a).getTIEPNHAN());
            cell4.setCellValue(listthongke.get(a).getTONDAU());
            cell5.setCellValue(listthongke.get(a).getTONGDAGIAIQUYET());
            cell6.setCellValue(listthongke.get(a).getSOM());
            cell7.setCellValue(listthongke.get(a).getDUNG());
            cell8.setCellValue(listthongke.get(a).getTRE());
            cell9.setCellValue(listthongke.get(a).getTONGDANGGIAIQUYET());
            cell10.setCellValue(listthongke.get(a).getCHUADENHAN());
            cell11.setCellValue(listthongke.get(a).getQUAHAN());
            cell12.setCellValue(listthongke.get(a).getRUTHOSO());
            cell13.setCellValue(listthongke.get(a).getKHONGHOPLE());
            cell14.setCellValue(listthongke.get(a).getTONCUOI());
            rowNum++;
            stt++;
        }
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1));
        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);
        HSSFCell cell13 = row.createCell(13);
        HSSFCell cell14 = row.createCell(14);
        // set style cho cac cell
        //cell.setCellStyle(styleCellCenter);
        cell.setCellStyle(styleCellCenter);
        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);
        cell13.setCellStyle(styleCellCenter);
        cell14.setCellStyle(styleCellCenter);
        // set gia tri cho cac cell

        //cell1.setCellValue("");
        cell.setCellValue("Tng cng");
        cell2.setCellValue(tongnhan);
        cell3.setCellValue(tongtiepnhan);
        cell4.setCellValue(tondau);
        cell5.setCellValue(dagiaiquyet);
        cell6.setCellValue(tongsom);
        cell7.setCellValue(tongdung);
        cell8.setCellValue(tongtre);
        cell9.setCellValue(danggiaiquyet);
        cell10.setCellValue(tongchuadenhan);
        cell11.setCellValue(quahan);
        cell12.setCellValue(ruthoso);
        cell13.setCellValue(khonghople);
        cell14.setCellValue(toncuoi);

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

---

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

License:Apache License

/**
 * This is function excelLephichitiet//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 req
 * @param res
 */
public static void excelLephichitiet(ResourceRequest req, ResourceResponse res) {
    try {
        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("Tinh_hinh_thu_le_phi_chi_tiet");

        // 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) 9));
        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) 9));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);
        rowNum++;
        List<NhomThuTucHanhChinh> listnhomtt = NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhs(0,
                NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhsCount());
        List<Lephi_chitiet> listlephichitiet = Lephi_chitietLocalServiceUtil
                .lephichitiet(ParamUtil.getString(req, "id_loaihoso"), tungay, denngay);

        String loaihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.loaihoso");
        String nguoinop = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.nguoinop");
        String chuhoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.chuhoso");
        String diachi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.diachi");
        String sobohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.sobohoso");
        String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.lephi");
        String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.phihoso");
        String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.tonglephi");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.ghichu");
        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.tieude");
        String[] header = { "STT", loaihoso, nguoinop, chuhoso, diachi, sobohoso, lephi, phihoso, tonglephi,
                ghichu };

        rowNum++;
        int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 9));
        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) 9));
        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 j = 0; j < listnhomtt.size(); j++) {
            if (ActionUtil.checkHosoByNHOMTTHCID(listnhomtt.get(j).getId(), listlephichitiet)) {
                sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 9));
                HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum);
                HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0);
                cellNhomTTHC.setCellValue(listnhomtt.get(j).getTen());
                cellNhomTTHC.setCellStyle(styleCellNhomTTHC);
                rowNhomTTHC.createCell(9).setCellStyle(styleCellNhomTTHC);
                rowNum++;
                for (int a = 0; a < listlephichitiet.size(); a++) {
                    // khai bao dong va cac cell
                    if (listnhomtt.get(j).getId() == listlephichitiet.get(a).getNHOMTHUTUCHANHCHINHID()) {
                        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);
                        // 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);
                        // set gia tri cho cac cell
                        cell.setCellValue(stt);
                        cell1.setCellValue(listlephichitiet.get(a).getTENHOSO());
                        cell2.setCellValue(listlephichitiet.get(a).getNGUOINOP());
                        cell3.setCellValue(listlephichitiet.get(a).getCHUHOSO());
                        cell4.setCellValue(listlephichitiet.get(a).getDIACHI());
                        cell5.setCellValue(listlephichitiet.get(a).getSOBOHOSO());
                        cell6.setCellValue(listlephichitiet.get(a).getLEPHI());
                        cell7.setCellValue(listlephichitiet.get(a).getPHIHOSO());
                        cell8.setCellValue(listlephichitiet.get(a).getTONGLEPHI());

                        tong = tong + Long.valueOf(listlephichitiet.get(a).getTONGLEPHI());
                        rowNum++;
                    }
                }
            }
        }

        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 7));
        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);
        // set style cho cac cell
        cell.setCellStyle(styleCellRight);
        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);
        // set value for cells of rowtong
        cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong"));
        cell8.setCellValue(tong);
        res.setContentType("application/vnd.ms-excel");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.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 excelLephilinhvuc//from   ww  w  .  j av a 2s . c  o  m
 * Version: 1.0
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelLephilinhvuc(ResourceRequest req, ResourceResponse res) {
    try {
        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("Tinh_hinh_thu_le_phi_theo_linh_vuc");

        // 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 styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle();
        styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellRightNobold.setWrapText(true);

        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) 6));
        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) 6));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);

        rowNum++;
        List<Lephi_linhvuc> listlephilinhvuc = Lephi_linhvucLocalServiceUtil
                .lephilinhvuc(ParamUtil.getString(req, "id_linhvuc"), tungay, denngay);

        String linhvuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.linhvuc");
        String tongsohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tongsohoso");
        String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.lephi");
        String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.phihoso");
        String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tonglephi");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.ghichu");
        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tieude");
        String[] header = { "STT", linhvuc, tongsohoso, lephi, phihoso, tonglephi, ghichu };

        rowNum++;
        int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 6));
        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) 6));
        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 < listlephilinhvuc.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);
            // set style cho cac cell
            cell.setCellStyle(styleCellCenter);
            cell1.setCellStyle(styleCellLeft);
            cell2.setCellStyle(styleCellCenter);
            cell3.setCellStyle(styleCellRightNobold);
            cell4.setCellStyle(styleCellRightNobold);
            cell5.setCellStyle(styleCellRightNobold);
            cell6.setCellStyle(styleCellRightNobold);
            // set gia tri cho cac cell
            cell.setCellValue(stt);
            cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil
                    .getNhomThuTucHanhChinh(listlephilinhvuc.get(a).getNHOMTHUTUCHANHCHINHID()).getTen());
            cell2.setCellValue(listlephilinhvuc.get(a).getTONGHOSO());
            cell3.setCellValue(listlephilinhvuc.get(a).getLEPHIHOSO());
            cell4.setCellValue(listlephilinhvuc.get(a).getPHIHOSO());
            cell5.setCellValue(listlephilinhvuc.get(a).getTONGLEPHI());

            tong = tong + Long.valueOf(listlephilinhvuc.get(a).getTONGLEPHI());
            rowNum++;
        }

        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 4));
        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);
        // set style cho cac cell
        cell.setCellStyle(styleCellRight);
        cell1.setCellStyle(styleCellCenter);
        cell2.setCellStyle(styleCellCenter);
        cell3.setCellStyle(styleCellCenter);
        cell4.setCellStyle(styleCellCenter);
        cell5.setCellStyle(styleCellRightNobold);
        cell6.setCellStyle(styleCellCenter);
        // set value for cells of rowtong
        cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong"));
        cell5.setCellValue(tong);
        res.setContentType("application/vnd.ms-excel");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls");
        OutputStream out = res.getPortletOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
    }
}

---

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

License:Apache License

/**
 * This is function excelLephitonghop//ww  w  .  j a va2 s  .co m
 * Version: 1.0
 *  
 * History: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  ------------------------------------------------- 
 *  3-March-2013  Nam Dinh    Create new
 * @param req
 * @param res
 */
public static void excelLephitonghop(ResourceRequest req, ResourceResponse res) {
    try {
        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("Tinh_hinh_thu_le_phi_tong_hop");

        // 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 styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle();
        styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleCellRightNobold.setWrapText(true);

        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) 6));
        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) 6));
        rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp"));
        rowTenDonVi.getCell(2).setCellStyle(styleCellHeader);

        rowNum++;
        List<NhomThuTucHanhChinh> listnhomtt = NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhs(0,
                NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhsCount());
        List<Lephi_tonghop> listlephitonghop = Lephi_tonghopLocalServiceUtil
                .lephitonghop(ParamUtil.getString(req, "id_loaihoso"), tungay, denngay);

        String linhvuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.linhvuc");
        String tongsohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tongsohoso");
        String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.lephi");
        String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.phihoso");
        String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tonglephi");
        String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.ghichu");
        String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephitonghop.tieude");
        String[] header = { "STT", linhvuc, tongsohoso, lephi, phihoso, tonglephi, ghichu };

        rowNum++;
        int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 };
        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 6));
        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) 6));
        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 j = 0; j < listnhomtt.size(); j++) {
            if (ActionUtil.checkLePhiTongHopByNHOMTTHCID(listnhomtt.get(j).getId(), listlephitonghop)) {
                sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6));
                HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum);
                HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0);
                cellNhomTTHC.setCellValue(listnhomtt.get(j).getTen());
                cellNhomTTHC.setCellStyle(styleCellNhomTTHC);
                rowNhomTTHC.createCell(6).setCellStyle(styleCellNhomTTHC);
                rowNum++;
                for (int a = 0; a < listlephitonghop.size(); a++) {
                    // khai bao dong va cac cell
                    if (listnhomtt.get(j).getId() == listlephitonghop.get(a).getNHOMTHUTUCHANHCHINHID()) {
                        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);
                        // set style cho cac cell
                        cell.setCellStyle(styleCellCenter);
                        cell1.setCellStyle(styleCellLeft);
                        cell2.setCellStyle(styleCellCenter);
                        cell3.setCellStyle(styleCellRightNobold);
                        cell4.setCellStyle(styleCellRightNobold);
                        cell5.setCellStyle(styleCellRightNobold);
                        cell6.setCellStyle(styleCellRightNobold);
                        // set gia tri cho cac cell
                        cell.setCellValue(stt);
                        cell1.setCellValue(
                                ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephitonghop.thang") + " "
                                        + listlephitonghop.get(a).getTHANGNHAN());
                        cell2.setCellValue(listlephitonghop.get(a).getTONGHOSO());
                        cell3.setCellValue(listlephitonghop.get(a).getLEPHI());
                        cell4.setCellValue(listlephitonghop.get(a).getPHIHOSO());
                        cell5.setCellValue(listlephitonghop.get(a).getTONGLEPHI());

                        tong = tong + Long.valueOf(listlephitonghop.get(a).getTONGLEPHI());
                        rowNum++;
                    }
                }
            }
        }

        sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 4));
        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);
        // set style cho cac cell
        cell.setCellStyle(styleCellRight);
        cell1.setCellStyle(styleCellCenter);
        cell2.setCellStyle(styleCellCenter);
        cell3.setCellStyle(styleCellCenter);
        cell4.setCellStyle(styleCellCenter);
        cell5.setCellStyle(styleCellRightNobold);
        cell6.setCellStyle(styleCellCenter);
        // set value for cells of rowtong
        cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong"));
        cell5.setCellValue(tong);
        res.setContentType("application/vnd.ms-excel");
        res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate");
        res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls");
        OutputStream out = res.getPortletOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »