EasyExcel(三) 导出excel的拦截器

EasyExcel(三) 导出excel的拦截器

关于easyExcel的基本用法我就不在多说了，有需要的可以自己点击该链接去学习基本的使用，主要对这里面经常用到的一些监听器和拦截器讲一下

一、准备工作

这是我用的easyexcel版本

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

二、导出excel的时候注册的拦截器

拦截器都是继承于WriteHandler，

我一般常用的就是这三个：

1. RowWriteHandler 行导出处理程序

2. CellWriteHandler 单元格导出处理程序
3. SheetWriteHandler 工作表导出处理程序

• RowWriteHandler

public interface RowWriteHandler extends WriteHandler {
    //行创建之前
    void beforeRowCreate(WriteSheetHolder var1, WriteTableHolder var2, Integer var3, Integer var4, Boolean var5);
	//在行创建之后
    void afterRowCreate(WriteSheetHolder var1, WriteTableHolder var2, Row var3, Integer var4, Boolean var5);
	//在行处置之后
    void afterRowDispose(WriteSheetHolder var1, WriteTableHolder var2, Row var3, Integer var4, Boolean var5);
}

​ 示例

可以用来对该行进行特殊处理 比如这一行的样式的修改，我这里是用来做错误回显的，读的时候记录了错误的数据行列，然后通过构造参数传入，就可以具体修改这一行的指定列的样式了

public class CommentWriteHandler extends AbstractRowWriteHandler {

    private Map<Integer, FailRecord> failDataMap;
	// 构造参数传入错误的数据
    public CommentWriteHandler(Map<Integer, FailRecord> failDataMap) {
        this.failDataMap = failDataMap;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {

        if (failDataMap.containsKey(row.getRowNum())) {
            if (!isHead) {
                Sheet sheet = writeSheetHolder.getSheet();
                Cell cell = row.getCell(failDataMap.get(row.getRowNum()).getColumn());
                Workbook workbook = sheet.getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                //设置前景填充样式
//                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //设置前景色为红色
//                cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                //设置垂直居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                Font font = workbook.createFont();
                //设置字体为红色
                font.setColor(Font.COLOR_RED);
                cellStyle.setFont(font);
				//创建设置批注
                Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
                Comment comment = drawingPatriarch.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
                        (short) row.getRowNum(), failDataMap.get(row.getRowNum()).getColumn(), (short) (row.getRowNum() + 1), failDataMap.get(row.getRowNum()).getColumn() + 1));
                comment.setString(new HSSFRichTextString(failDataMap.get(row.getRowNum()).getMsg()));
                cell.setCellComment(comment);
                cell.setCellStyle(cellStyle);
            }
        }
    }
}

• CellWriteHandler

public interface CellWriteHandler extends WriteHandler {
    //单元创建之前
    void beforeCellCreate(WriteSheetHolder var1, WriteTableHolder var2, Row var3, Head var4, Integer var5, Integer var6, Boolean var7);
	//单元创建后
    void afterCellCreate(WriteSheetHolder var1, WriteTableHolder var2, Cell var3, Head var4, Integer var5, Boolean var6);
	//单元数据转换后
    void afterCellDataConverted(WriteSheetHolder var1, WriteTableHolder var2, CellData var3, Cell var4, Head var5, Integer var6, Boolean var7);
	//单元格处理后
    void afterCellDispose(WriteSheetHolder var1, WriteTableHolder var2, List<CellData> var3, Cell var4, Head var5, Integer var6, Boolean var7);
}

这个就没什么示例了 作用和RowWriteHandler 差不了多少。

• SheetWriteHandler

public interface SheetWriteHandler extends WriteHandler {
    //sheet创建之前
    void beforeSheetCreate(WriteWorkbookHolder var1, WriteSheetHolder var2);
    //sheet创建之后
    void afterSheetCreate(WriteWorkbookHolder var1, WriteSheetHolder var2);
}

​ 示例

我主要是用来创建下拉列表之类的操作

public class MySheetWriteHandler implements SheetWriteHandler {

    /**
     * 创建sheet页前的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    /**
     * 创建sheet页后的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //定义选值范围
        String[] sexStrings = new String[]{"男", "女", "未知"};
        //根据index，形成map，可插入多个  这个map可以由构造参数传入，毕竟不能写死
        Map<Integer, String[]> mapDropDown = new HashMap<>();
        mapDropDown.put(2, sexStrings);
        //获取sheet页
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            /***起始行、终止行、起始列、终止列**/
            CellRangeAddressList addressList = new CellRangeAddressList(1, 9999, entry.getKey(), entry.getKey());
            /***设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            /***处理Excel兼容性问题**/
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }
    }
}

--------------最后感谢大家的阅读，愿大家技术越来越流弊！--------------

--------------也希望大家给我点支持，谢谢各位大佬了！！！--------------