代码之家 › 专栏 › 技术社区 › Karthik

JSF-在Apache POI中使用HSSFFont为JSF中的excel表设置全局字体类型

export-to-excel primefaces jsf-2 excel

Karthik · 技术社区 · 11 年前

我可以选择将数据表导出为excel表。
我正在尝试将工作表中所有单元格的字体类型设置为“Calibri”。
但下面的代码只为页眉指定字体类型,而不是为其余单元格指定字体类型
如何为工作表中的所有单元格全局设置字体类型?

  public void exportToXLS(Object document) {

        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);
        wb.setSheetName(0, "report_lists"); // set sheet name
        sheet.shiftRows(0, sheet.getLastRowNum(), 4); // shifting the rows to
        HSSFRow header = sheet.getRow(4);
        header.getCell(1).setCellValue("Test Group");
        header.getCell(2).setCellValue("Category");

        HSSFRow firstrow = sheet.getRow(0);
        firstrow.createCell(0).setCellValue("Actuals");

        SimpleDateFormat sdf = new SimpleDateFormat(
                "EEE MMM d HH:mm:ss 'CDT'  yyyy ");
        Date date = new Date();
        sdf.setTimeZone(TimeZone.getTimeZone("GMT-5"));
        String reportDate = sdf.format(date);
        HSSFRow thirdrow = sheet.getRow(3);
        thirdrow.createCell(0).setCellValue(reportDate);

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFFont fontHeader = (HSSFFont) wb.createFont();
        fontHeader.setFontName("Calibri");
        cellStyle.setFont(fontHeader);
        System.out.println(" header.getPhysicalNumberOfCells();::::"
                + header.getPhysicalNumberOfCells());

        //only for changing font for header
        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);
            // sheet.setDefaultColumnStyle(i, cellStyle);

        }
        System.out.println("sheet.getLastRowNum():::" + sheet.getLastRowNum());
        for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
            HSSFRow row = sheet.getRow(j);
            if (row != null) {
                System.out.println(" j>>>" + j);
                // you can add sysout or debug here to check if all row passed
                // successfully
                for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                    HSSFCell cell = row.getCell(i);
                    if (cell != null) {
                        System.out.println(" i++" + i);
                        // you can add sysout or debug here to check if all cell
                        // passed successfully
                        HSSFCell celll = header.getCell(i);
                        celll.setCellStyle(cellStyle);
                    }
                }
            }
        }


    }

以下是excel的屏幕截图:
enter image description here

1 回复 | 直到 11 年前

Angga 11 年前

编辑你的代码变成这样:

public void exportToXLS(Object document) {

    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    wb.setSheetName(0, "report_lists"); // set sheet name
    sheet.shiftRows(0, sheet.getLastRowNum(), 4); // shifting the rows to
    HSSFRow header = sheet.getRow(4);
    header.getCell(1).setCellValue("Test Group");
    header.getCell(2).setCellValue("Category");

    HSSFRow firstrow = sheet.getRow(0);
    firstrow.createCell(0).setCellValue("Actuals");

    SimpleDateFormat sdf = new SimpleDateFormat(
            "EEE MMM d HH:mm:ss 'CDT'  yyyy ");
    Date date = new Date();
    sdf.setTimeZone(TimeZone.getTimeZone("GMT-5"));
    String reportDate = sdf.format(date);
    HSSFRow thirdrow = sheet.getRow(3);
    thirdrow.createCell(0).setCellValue(reportDate);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont fontHeader = (HSSFFont) wb.createFont();
    fontHeader.setFontName("Calibri");
    cellStyle.setFont(fontHeader);
    System.out.println(" header.getPhysicalNumberOfCells();::::"
            + header.getPhysicalNumberOfCells());

    //only for changing font for header
    //for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
    //    HSSFCell cell = header.getCell(i);
    //    cell.setCellStyle(cellStyle);
        // sheet.setDefaultColumnStyle(i, cellStyle);

    //} you dont need this because the code bellow will change all style including header

    System.out.println("sheet.getLastRowNum():::" + sheet.getLastRowNum());
    for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
        HSSFRow row = sheet.getRow(j);
        if (row != null) {
            System.out.println(" j>>>" + j);
            // you can add sysout or debug here to check if all row passed
            // successfully
            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                HSSFCell cell = row.getCell(i);
                if (cell != null) {
                    System.out.println(" i++" + i);
                    //HSSFCell celll = header.getCell(i); <<you also don't need this, this is the root of your problem, you choose to change header only instead of already declares cell
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }


}