[alibaba/easyexcel]怎么给单元格添加RGB颜色（背景色或前景色）

7

使用HSSFWorkbook的customPalette自定义画布颜色
可以在上面设置几个颜色索引，但是注意了，索引的范围是0x8 到 0x40

cellStyle.setFillForegroundColor(short colorIndex) 设置自定义的颜色索引 (好像使用HSSFWorkbook以为着只能生成xls，不是xlsx)

final short CUSTOM_COLOR_INDEX_1 = 8;
final short CUSTOM_COLOR_INDEX_2 = 9;
WriteCellStyle contentWriteCellStyle1 = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
// FillPatternType所以可以不指定
contentWriteCellStyle1.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle1.setFillForegroundColor(CUSTOM_COLOR_INDEX_1);
WriteCellStyle contentWriteCellStyle2 = new WriteCellStyle();
contentWriteCellStyle2.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle2.setFillForegroundColor(CUSTOM_COLOR_INDEX_2);

EasyExcel.write("your excel filepath").registerWriteHandler(new AbstractWorkbookWriteHandler() {
    @Override
    public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {
        super.afterWorkbookCreate(writeWorkbookHolder);
        java.awt.Color color1 = java.awt.Color.decode("#555555");
        java.awt.Color color2 = java.awt.Color.decode("#445544");
        HSSFWorkbook wb = (HSSFWorkbook) writeWorkbookHolder.getWorkbook();
        wb.getCustomPalette().setColorAtIndex(CUSTOM_COLOR_INDEX_1, (byte) color1.getRed(),
                (byte) color1.getGreen(), (byte) color1.getBlue());
        wb.getCustomPalette().setColorAtIndex(CUSTOM_COLOR_INDEX_2, (byte) color2.getRed(),
                (byte) color2.getGreen(), (byte) color2.getBlue());
    }
}).registerWriteHandler(new AbstractCellWriteHandler() {
    // cache it
    List<CellStyle> cachedShiftCellStyles = Lists.newArrayList();

    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
            List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (cachedShiftCellStyles.isEmpty()) {
            cachedShiftCellStyles.add(StyleUtil.buildContentCellStyle(writeSheetHolder.getSheet().getWorkbook(),
                    contentWriteCellStyle1));
            cachedShiftCellStyles.add(StyleUtil.buildContentCellStyle(writeSheetHolder.getSheet().getWorkbook(),
                    contentWriteCellStyle2));
        }
        if (!isHead && relativeRowIndex < 3) {
            cell.setCellStyle(cachedShiftCellStyles.get(0));
        } else {
            cell.setCellStyle(cachedShiftCellStyles.get(0));
        }
    };
}).excelType(ExcelTypeEnum.XLS).sheet("anything").doWrite(null);

woostju

2

HSSFWorkbook wb = (HSSFWorkbook) writeWorkbookHolder.getWorkbook(); 这里会报错 org.apache.poi.xssf.streaming.SXSSFWorkbook cannot be cast to org.apache.poi.hssf.usermodel.HSSFWorkbook 请问怎么解决？

foreverEnjoyLife

9

可以通过registerWriteHandler注册多个WriteHandler多个抽象子类覆盖方法进行设置：

EasyExcel.write(response.getOutputStream(), head).registerWriteHandler(new AbstractCellWriteHandler() { @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead); if (isHead) { WriteWorkbookHolder writeWorkbookHolder = writeSheetHolder.getParentWriteWorkbookHolder(); SXSSFWorkbook workbook = (SXSSFWorkbook) writeWorkbookHolder.getWorkbook(); XSSFCellStyle xssfCellStyle = (XSSFCellStyle) workbook.getCellStyleAt(cell.getColumnIndex() + 1); xssfCellStyle.setFillForegroundColor(new XSSFColor(Color.decode("#ECF4FF"))); } } }).sheet("Sheet1").doWrite(data);

foreverEnjoyLife

7

我也被这个问题困扰了很长时间，无意中看到了poi的处理方式，如下

XSSFCellStyle aa = .createCellStyle();
aa.setFillForegroundColor(new XSSFColor(new Color(255,255,255))); 
aa.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

我在使用easyExcel的workbook的时候转换了相对应的格式，处理单元格样式的时候，我分了两种情况，

单个单元格的情况
easyExcel表头的情况

先说第一种情况，这里可以设置16色，也可以设置RGB

Workbook workbook = writeWorkbookHolder.getWorkbook();
CellStyle cellStyle = initCellStyle(workbook);
XSSFCellStyle xssfCellStyle =(XSSFCellStyle)workbook.createCellStyle();
xssfCellStyle.cloneStyleFrom(cellStyle);
xssfCellStyle.setFillForegroundColor(new XSSFColor(new Color(255,255,255)));
xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(xssfCellStyle);

注意：最后一定要把这个样式放到cell这个单元格中，

第二种情况，设置easyExcel表头的RGB颜色首先设置标题头颜色策略，但是不要设置头的部分，只设置内容部分，可以在easyExcel文档中找到“自定义样式”

// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short)11);// 字体大小
contentWriteFont.setFontName("微软雅黑"); // 字体样式
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);   // 下边框
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);     // 左边框
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);      // 上边框
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);    // 右边框
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
        new HorizontalCellStyleStrategy(null, contentWriteCellStyle);

使用这个策略我就不说了自定义拦截器，对head进行单独处理

public class CustomHeadWriteHandler extends AbstractCellWriteHandler {

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer relativeRowIndex, Boolean isHead) {
        // 设置标题头样式
        if (isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = initCellStyle(workbook);
            XSSFCellStyle xssfCellStyle = (XSSFCellStyle)workbook.createCellStyle();
            xssfCellStyle.cloneStyleFrom(cellStyle);
            xssfCellStyle.setFillForegroundColor(new XSSFColor(new Color(255,255,255)));
            xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(xssfCellStyle);
        }
    }
}

上面的方法

private CellStyle initCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);   // 下边框
        style.setBorderLeft(BorderStyle.THIN);     // 左边框
        style.setBorderTop(BorderStyle.THIN);      // 上边框
        style.setBorderRight(BorderStyle.THIN);    // 右边框

        Font font = workbook.createFont();
        font.setFontName("微软雅黑"); // 字体样式
        font.setBold(false);    // 是否加粗
        font.setFontHeightInPoints((short)11);   // 字体大小
        style.setFont(font);
        return style;
    }

wendongchao

8

自定义拦截器，对head进行单独处理，可以对head头的每一列进行颜色设置