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

问题描述

我有一个选项可以将datatable导出到excel表。

我试图将表格中所有单元格的字体类型设置为Calibri。

但下面的代码只分配标题的字体类型，而不是其余单元格的字体类型。
我如何能够为表格中的所有单元格全局设置字体类型？

I have an option to export the datatable to an excel sheet.
I am tryin to set the font type as "Calibri" for all the cell in the sheet.
But the below code is assigning only the font type for the header not for the rest of the cells
How can I able to set the font type globally for all the cell in the sheet ?

  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的屏幕截图：

Here is the screen shot of the excel:

推荐答案

编辑你的代码变成这样： / p>

edit your code became like this :

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);
                }
            }
        }
    }


}

这篇关于JSF - 在Apache POI中使用HSSFFont在JSF中为Excel表格设置全局字体类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！