EasyExcel 格式设置大全

EasyExcel 是阿里巴巴开源的基于 POI 的 Excel 处理工具，虽然以简单易用和大数据量处理著称，但也提供了丰富的格式设置功能。以下是 EasyExcel 格式设置的全面指南：

1. 基础格式设置

1.1 单元格样式设置

// 创建样式配置
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 设置字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
headWriteFont.setBold(true);
headWriteFont.setColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
// 设置边框
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);

// 应用到表头
HorizontalCellStyleStrategy styleStrategy = 
    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

1.2 内容样式设置

WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置内容垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置自动换行
contentWriteCellStyle.setWrapped(true);

2. 高级格式设置

2.1 自定义样式策略

public class CustomStyleStrategy extends AbstractHorizontalCellStyleStrategy {
    @Override
    protected WriteCellStyle headCellStyle(Head head) {
        // 自定义表头样式
        WriteCellStyle style = new WriteCellStyle();
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        return style;
    }

    @Override
    protected WriteCellStyle contentCellStyle(WriteSheetHolder writeSheetHolder) {
        // 自定义内容样式
        WriteCellStyle style = new WriteCellStyle();
        style.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        return style;
    }
}

2.2 条件格式设置

// 使用拦截器实现条件格式
public class CustomCellWriteHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, 
            WriteTableHolder writeTableHolder, Cell cell, Head head, 
            Integer relativeRowIndex, Boolean isHead) {
        if (!isHead && cell.getColumnIndex() == 2) {
            // 对第三列设置特殊格式
            CellStyle cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(cellStyle);
        }
    }
}

3. 数字格式设置

3.1 设置数字格式

WriteCellStyle numberStyle = new WriteCellStyle();
// 设置数字格式为保留两位小数
numberStyle.setDataFormat((short)4);  // 0.00格式

// 应用到特定列
Map<Integer, WriteCellStyle> styleMap = new HashMap<>();
styleMap.put(2, numberStyle);  // 第三列应用此样式

HorizontalCellStyleStrategy styleStrategy = 
    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle, styleMap);

3.2 自定义数字格式

// 创建自定义数据格式
Workbook workbook = new SXSSFWorkbook();
short format = workbook.createDataFormat().getFormat("#,##0.00_);[Red](#,##0.00)");

WriteCellStyle style = new WriteCellStyle();
style.setDataFormat(format);

4. 字体设置

4.1 基本字体设置

WriteFont font = new WriteFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)14);
font.setBold(true);
font.setColor(IndexedColors.RED.getIndex());
font.setItalic(true);
font.setUnderline(Font.U_SINGLE);

WriteCellStyle style = new WriteCellStyle();
style.setWriteFont(font);

4.2 多行表头字体设置

// 复杂表头样式
WriteCellStyle headStyle = new WriteCellStyle();
WriteFont headFont = new WriteFont();
headFont.setFontHeightInPoints((short)10);
headStyle.setWriteFont(headFont);

// 使用注解设置表头
@ExcelProperty(value = {"主标题", "次标题"})
private String name;

5. 行高和列宽设置

5.1 设置行高

// 通过拦截器设置行高
public class CustomRowWriteHandler implements RowWriteHandler {
    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, 
            WriteTableHolder writeTableHolder, Row row, 
            Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            row.setHeightInPoints(30);  // 表头行高30
        } else {
            row.setHeightInPoints(20);  // 内容行高20
        }
    }
}

5.2 设置列宽

// 通过拦截器设置列宽
public class CustomSheetWriteHandler implements SheetWriteHandler {
    @Override
    public void afterSheetCreate(WriteSheetHolder writeSheetHolder, 
            WriteTableHolder writeTableHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        // 设置第一列宽度
        sheet.setColumnWidth(0, 5000);
        // 设置第二列自动调整宽度
        sheet.autoSizeColumn(1);
    }
}

6. 合并单元格

// 使用拦截器合并单元格
public class CustomMergeStrategy implements CellWriteHandler {
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, 
            WriteTableHolder writeTableHolder, List<CellData> cellDataList, 
            Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 合并第一行的1-3列
        if (isHead && cell.getRowIndex() == 0 && cell.getColumnIndex() == 0) {
            Sheet sheet = writeSheetHolder.getSheet();
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
            sheet.addMergedRegion(region);
        }
    }
}

7. 边框设置

WriteCellStyle style = new WriteCellStyle();
// 设置细边框
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);

// 设置边框颜色
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());

8. 实际应用示例

// 完整导出示例
public void exportWithStyle(HttpServletResponse response) throws IOException {
    // 准备数据
    List<User> users = getUsers();
    
    // 设置响应头
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment;filename=users.xlsx");
    
    // 表头样式
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)12);
    headWriteFont.setColor(IndexedColors.WHITE.getIndex());
    headWriteCellStyle.setWriteFont(headWriteFont);
    
    // 内容样式
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    
    // 数字列特殊样式
    WriteCellStyle numberStyle = new WriteCellStyle();
    numberStyle.setDataFormat((short)4);
    Map<Integer, WriteCellStyle> styleMap = new HashMap<>();
    styleMap.put(2, numberStyle);  // 第三列是数字
    
    // 构建样式策略
    HorizontalCellStyleStrategy styleStrategy = 
        new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle, styleMap);
    
    // 导出
    EasyExcel.write(response.getOutputStream(), User.class)
             .registerWriteHandler(styleStrategy)
             .registerWriteHandler(new CustomRowWriteHandler())  // 行高
             .registerWriteHandler(new CustomSheetWriteHandler())  // 列宽
             .registerWriteHandler(new CustomMergeStrategy())  // 合并单元格
             .sheet("用户列表")
             .doWrite(users);
}

9. 常见问题

1. 样式不生效：

   • 确保正确注册了样式策略

   • 检查样式设置的优先级

   • 确认没有其他拦截器覆盖了样式

2. 性能问题：

   • 避免在循环中创建样式对象

   • 重用样式对象

   • 对于大数据量，考虑使用 SXSSF 模式

3. 格式兼容性：

   • 某些复杂格式可能在旧版 Excel 中不兼容

   • 测试不同 Excel 版本的兼容性

EasyExcel 的格式设置虽然不如原生 POI 那样细致，但对于大多数业务场景已经足够，且在大数据量处理上有明显优势。