EasyExcel 是阿里巴巴的一个基于Java的简单方便省内存的读写Excel开源项目。
常用方法,见官方文档:https://easyexcel.opensource.alibaba.com/docs/current/
下面介绍,Alibaba EasyExcel:给每个单元格,设置不同的格式或样式。
因为在复杂表格中,可能每行每列的格式样式都会不一样,这时如果使用之前的批量设置行,设置列,肯定是无法实现的,这个时候就需要 使用 CellWriteHandler 来自定义单元格的格式与样式。
一、创建一个 CustomCellWriteHandler 类,实现 CellWriteHandler 接口:
public class CustomCellWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
}
以上代码,可以看出,实现 CellWriteHandler 接口,需要覆盖它的一些方法,这些方法,从字面意思基本就知道是干什么操作的;
在这里,我们只需要 关注 afterCellCreate 方法,也就是在单元格创建之后去设置想要的 格式或样式。
二、afterCellCreate 方法参数说明:
public void afterCellCreate(WriteSheetHolder writeSheetHolder, // 可通过此对象 获取 Sheet 、 Workbook 对象
WriteTableHolder writeTableHolder,
Cell cell, // 当前单元格式对象
Head head, // 头对象(如果没有头,则为空)
Integer relativeRowIndex, // 相对头行数(去掉头开始算行,没有头则为空)
Boolean isHead) { // 如果没有头,则为 false
}
三、具体 afterCellCreate 方法实现:
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); // 获取 Workbook对象
CellStyle cellStyle = workbook.createCellStyle(); // 创建一个 CellStyle对象
if (isHead) { // 如果是头,设置对应格式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());// 背景设置
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 16);
headWriteCellStyle.setWriteFont(headWriteFont);
cellStyle.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex());
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
cellStyle.setFont(font);
} else {
// 设置内容行列对应格式
if (relativeRowIndex == 0) { // 如果是内容的第一行
cellStyle.setAlignment(HorizontalAlignment.LEFT);
} else {
// 内容其它行
cellStyle.setAlignment(HorizontalAlignment.CENTER);
}
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 边框
cellStyle.setBorderLeft(BorderStyle.THIN);//细实线
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
}
cell.setCellStyle(cellStyle);
}