EasyExcel(三) 导出excel的拦截器
关于easyExcel的基本用法我就不在多说了，有需要的可以自己点击该链接去学习基本的使用，主要对这里面经常用到的一些监听器和拦截器讲一下
一、准备工作
这是我用的easyexcel版本
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>
二、导出excel的时候注册的拦截器
拦截器都是继承于WriteHandler，
我一般常用的就是这三个：
1. RowWriteHandler 行导出处理程序
- CellWriteHandler 单元格导出处理程序
- 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;}@Overridepublic 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*/@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** 创建sheet页后的操作** @param writeWorkbookHolder* @param writeSheetHolder*/@Overridepublic 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);}}
}
--------------最后感谢大家的阅读，愿大家技术越来越流弊！--------------
--------------也希望大家给我点支持，谢谢各位大佬了！！！--------------