EasyExcel中自定义拦截器的运用

在EasyExcel中自定义拦截器不仅可以帮助我们不止步于数据的填充，而且可以对样式、单元格合并等带来便捷的功能。下面直接开始

我们定义一个MergeWriteHandler的类继承AbstractMergeStrategy实现CellWriteHandler

public class MergeLastWriteHandler extends AbstractMergeStrategy implements CellWriteHandler 

当中我们重写merge方法

@Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {

    }

我们可以在重写的方法中得到形参中的Cel，那我们可以通过调用cell.getStringCellValue()得到当前单元格的内容，判断当前单元格的内容是否是目标单元格，例如下面代码

if (cell.getStringCellValue().equals("说明")) {
            cell.setCellValue("说明：这是一条说明");
            //获取表格最后一行
            int lastRowNum = sheet.getLastRowNum();
            CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);
            sheet.addMergedRegionUnsafe(region);
        }

并且这里我们通过sheet中的 getLastRowNum()获取最后一行，最终通过CellRangeAddress来进行单元格合并，从下面源码我们可以了解到合并的规则是什么（通过int firstRow, int lastRow, int firstCol, int lastCol）

/**
	 * Creates new cell range. Indexes are zero-based.
	 *
	 * @param firstRow Index of first row
	 * @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
	 * @param firstCol Index of first column
	 * @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
	 */
	public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
		super(firstRow, lastRow, firstCol, lastCol);

		if (lastRow < firstRow || lastCol < firstCol) {
			throw new IllegalArgumentException("Invalid cell range, having lastRow < firstRow || lastCol < firstCol, " +
					"had rows " + lastRow + " >= " + firstRow + " or cells " + lastCol + " >= " + firstCol);
		}
	}

这样就可以实现合并单元格，不过这里可能会出现一个问题

java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
    at org.apache.poi.xssf.streaming.SXSSFCell.typeMismatch(SXSSFCell.java:943)
	at org.apache.poi.xssf.streaming.SXSSFCell.getStringCellValue(SXSSFCell.java:460)

因为会访问所有的单元格，有可能会出现是不是字符串类型的单元格，所以我们最好在开始的时候对其进行处理一次

if (cell.getCellType().equals(CellType.NUMERIC)){
            double numericCellValue = cell.getNumericCellValue();
            String s = Double.toString(numericCellValue);
            String substring = s.substring(0, s.indexOf("."));
            cell.setCellValue(substring);
        }

但这里可能我们还需要一个操作，例如如果我们全局配置了表框线条，但是不想当前的单元格有线条，如何处理呢，定义CustomCellWriteHandler拦截器继承AbstractCellWriteHandler

public class CustomCellWriteHandler extends AbstractCellWriteHandler{}

重写当中的afterCellDispose方法，得到

 @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        super.afterCellDispose(context);
    }

现在我们对其进行操作

  Cell cell = context.getCell();
  if(BooleanUtils.isNotTrue(context.getHead())){
    if(cell.getStringCellValue().contains("说明"))){
                Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setBorderTop(BorderStyle.THIN);
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                cell.setCellStyle(cellStyle);
                context.getFirstCellData().setWriteCellStyle(null); //关键代码，不设置不生效
            }
}

最后只需要在写入的时候，把拦截器放进去就可以了，看完整代码

public class CustomCellWriteHandler extends AbstractCellWriteHandler {

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        short height = 600;
        row.setHeight(height);
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {

        Cell cell = context.getCell();
        if(BooleanUtils.isNotTrue(context.getHead())){
            if(cell.getStringCellValue().contains("说明"))){
                Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setBorderTop(BorderStyle.THIN);
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                cell.setCellStyle(cellStyle);
                context.getFirstCellData().setWriteCellStyle(null);
            }
        }
        super.afterCellDispose(context);
    }
}

合并单元格的拦截器

public class MergeLastWriteHandler extends AbstractMergeStrategy implements CellWriteHandler {

    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置对齐
        //headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 背景色, 设置为绿色，也是默认颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 字体
        //WriteFont headWriteFont = new WriteFont();
        //headWriteFont.setFontHeightInPoints((short) 12);
        //headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        //contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {

        if (cell.getCellType() == CellType.NUMERIC) {
            double numericCellValue = cell.getNumericCellValue();
            String s = Double.toString(numericCellValue);
            String substring = s.substring(0, s.indexOf("."));
            cell.setCellValue(substring);
        }

        if (cell.getStringCellValue().equals("说明")) {
            cell.setCellValue("说明：这是一条说明");
            //获取表格最后一行
            int lastRowNum = sheet.getLastRowNum();
            CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);
            sheet.addMergedRegionUnsafe(region);
        }
        
    }
}

看一下Controller层

@GetMapping("/excelWrapper")
    public void excelWrapper(HttpServletResponse response) throws IOException {
        try {
            List<User> userList =  DataByExcel();  //获取数据的列表
            List<BudgetForm> budgetForm = BeanUtil.copyToList(userList,BudgetForm.class);
            String fileName = one.getProjectName() + ".xlsx";
                  response.setContentType("application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName );
            // 创建ExcelWriter对象
            WriteSheet writeSheet = EasyExcel
                    .writerSheet("表格sheet")
                    .registerWriteHandler(new MergeLastWriteHandler())
                    .registerWriteHandler(new CustomCellWriteHandler())
                    .build();
            // 向Excel中写入数据
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), BudgetForm.class).build();

            excelWriter.write(userList , writeSheet);

            // 关闭流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

对表头设置（自己对应表格设置对应字段）

@Data
@ContentRowHeight(47) //内容行高
@HeadRowHeight(35)//表头行高
public class BudgetForm implements Serializable  {


    @ColumnWidth(6)
    @ExcelProperty(value ={"表格","序号"} ,index = 0)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private Integer serialNumber;

    @ColumnWidth(15)
    @ExcelProperty(value ={"表格","名称"} ,index = 1)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String name;

    @ColumnWidth(26)
    @ExcelProperty(value = {"表格","备注"},index = 2)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String remark;

    @ColumnWidth(26)
    @ExcelProperty(value = {"表格","其他"},index = 3)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String budgetary;

    @ExcelIgnore
    @ApiModelProperty("合计")
    private String total;


}