EasyExcel(三) 导出excel的拦截器

我就是我 2022-11-26 14:57 151阅读 0赞

# EasyExcel(三) 导出excel的拦截器 #

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

## 一、准备工作 ##

这是我用的easyexcel版本

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

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

拦截器都是继承于WriteHandler，

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

1. RowWriteHandler 行导出处理程序

1.  CellWriteHandler 单元格导出处理程序
2.  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);
            }
        }
    }

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1phY2tfdHpo_size_16_color_FFFFFF_t_70]

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

[easyExcel]: https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/read/ReadTest.java
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1phY2tfdHpo_size_16_color_FFFFFF_t_70]: /images/20221124/17e4c567a7924ab1b0f7cf2ebeb3f924.png