EasyExcel中修改表头颜色、自定义表头颜色字体等

EasyExcel中修改表头颜色、自定义表头颜色字体等

需求描述：

EasyExcel的表头的默认颜色以及字体设置和excel中的不一致，产品要求修改。

EasyExcel版本为2.2.8。

默认设置参考：

com.alibaba.excel.util.StyleUtil这个类

 /**
 * @param workbook
 * @return
 */
public static CellStyle buildDefaultCellStyle(Workbook workbook) {
    CellStyle newCellStyle = workbook.createCellStyle();
    newCellStyle.setWrapText(true);
    newCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    newCellStyle.setAlignment(HorizontalAlignment.CENTER);
    newCellStyle.setLocked(true);
    newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    newCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    newCellStyle.setBorderTop(BorderStyle.THIN);
    newCellStyle.setBorderBottom(BorderStyle.THIN);
    newCellStyle.setBorderLeft(BorderStyle.THIN);
    newCellStyle.setBorderRight(BorderStyle.THIN);
    return newCellStyle;
}

实现思路：

参考easyExcel的拦截器HorizontalCellStyleStrategy实现思路，自定义拦截器。

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

public class CustomCellWriteHandler extends AbstractCellStyleStrategy implements CellWriteHandler {

    private XSSFCellStyle headCellStyle;

    @Override
    protected void initCellStyle(Workbook workbook) {
        headCellStyle = (XSSFCellStyle) StyleUtil.buildHeadCellStyle(workbook, null);
        headCellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(231, 230, 230)));
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        headCellStyle.setFont(font);
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        cell.setCellStyle(headCellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

    }

}

测试：

测试代码是之前的一个sheet页中导出多张表以及动态表头

 /**
     * 导出
     *
     * @param response 响应
     * @throws IOException ioexception
     */
    public void export(HttpServletResponse response) throws IOException {
        String fileName = System.currentTimeMillis() + "";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        // 提前构建输出流--添加自定义拦截器
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();
        // 构建sheet页--表示不加表头
        WriteSheet writeSheet = EasyExcel.writerSheet("sheet").needHead(Boolean.FALSE).build();
        // 表头的数量
        int num = 0;
        // 模拟写5张表
        for (int i = 0; i < 5; i++) {
            // 获取第二行表头
            List<List<String>> contentHeader = this.buildHeader();
            // 生成第一行表头--这样两行表头长宽一致
            List<List<String>> nameHeader = contentHeader.stream().map(item -> Collections.singletonList("城市表")).collect(Collectors.toList());
            // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
            // 第一次写入会创建头
            WriteTable writeTable0 = EasyExcel.writerTable(num).needHead(Boolean.TRUE).head(nameHeader).build();
            excelWriter.write(null, writeSheet, writeTable0);

            WriteTable writeTable1 = EasyExcel.writerTable(num + 1).needHead(Boolean.TRUE).head(contentHeader).build();
            List<List<String>> body = this.buildBody();
            excelWriter.write(body, writeSheet, writeTable1);
            // 插入两次表头加2
            num = num + 2;
        }
        excelWriter.finish();
    }

    /**
     * 模拟表头
     *
     * @return {@link List}<{@link List}<{@link String}>>
     */
    public List<List<String>> buildHeader() {
        List<List<String>> header = new ArrayList<>();
        header.add(Collections.singletonList("序号"));
        header.add(Collections.singletonList("城市"));
        header.add(Collections.singletonList("省会"));
        header.add(Collections.singletonList("得分"));
        return header;
    }

    /**
     * 模拟表内容
     *
     * @return {@link List}<{@link List}<{@link String}>>
     */
    public List<List<String>> buildBody() {
        List<List<String>> result = new ArrayList<>();
        result.add(Lists.newArrayList("1", "南京", "江苏", "100"));
        result.add(Lists.newArrayList("2", "无锡", "江苏", "100"));
        result.add(Lists.newArrayList("3", "苏州", "江苏", "100"));
        result.add(Lists.newArrayList("4", "常州", "江苏", "100"));
        // 插入一个空行
        result.add(Collections.emptyList());
        return result;
    }

前后效果图：

原先：

修改后:

效果已经达到预期，各位同学可以在这个基础上实现一些自定义需求。

欢迎指正！