easyexcel案例之类型转换，合并单元格，批注，下拉框，导入校验(expiringmap)

朴灿烈づ我的快乐病毒、 2024-03-30 12:55 36阅读 0赞

#### 文章目录 ####

*  easyexcel案例之类型转换，合并单元格，批注，下拉框，导入校验
 *   *  一、依赖
     *  二、导出
     *   *  1.类型转换导出
         *  2.自定义文件标题
         *  3.合并单元格导出
         *   *  注解方式
             *  通过 registerWriteHandler 方法注册进去
             *  自定义合并规则进行合并
         *  4.批注和下拉框导出
         *   *  批注信息类
             *  自定义批注处理器
             *  自定义下拉框处理器
             *  工具类
             *  测试
     *  三、导入
     *   *  1、修改导入模板实体类
         *  2、添加expiringmap依赖
         *  3、自定义监听器处理导入数据
         *  4、读取文件和错误信息下载

## easyexcel案例之类型转换，合并单元格，批注，下拉框，导入校验 ##

### 一、依赖 ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.10</version>
            </dependency>
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.6.3</version>
            </dependency>

### 二、导出 ###

#### 1.类型转换导出 ####

> 工具类

package com.hl.easyexcel.controller;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.WriteTable;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import com.hl.easyexcel.util.ExcelListener;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.springframework.web.multipart.MultipartFile;
     
    import javax.servlet.http.HttpServletResponse;
    import java.io.BufferedInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.List;
     
    @Slf4j
    public class ExcelUtil {
        
        /**
         * 导出数据为excel文件
         *
         * @param filename       文件名称
         * @param dataResult     集合内的bean对象类型要与clazz参数一致
         * @param clazz          集合内的bean对象类型要与clazz参数一致
         * @param response       HttpServlet响应对象
         */
        public static void export(String filename, String sheetName,List<?> dataResult, Class<?> clazz, HttpServletResponse response) {
        
            response.setStatus(200);
            OutputStream outputStream = null;
            try {
        
                if (StringUtils.isBlank(filename)) {
        
                    throw new RuntimeException("'filename' 不能为空");
                }
                String fileName = filename.concat(".xlsx");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
                outputStream = response.getOutputStream();
    
    
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(getStyleStrategy())
                        // 导出文件名
                        .autoCloseStream(Boolean.TRUE).sheet(sheetName)
                        .doWrite(dataResult);
     
            } catch (Exception e) {
        
                log.error("导出excel数据异常：", e);
                throw new RuntimeException(e);
            } finally {
        
                if (outputStream != null) {
        
                    try {
        
                        outputStream.flush();
                        outputStream.close();
                    } catch (IOException e) {
        
                        log.error("导出数据关闭流异常", e);
                    }
                }
            }
        }
    
     
        /**
         *  设置表格内容居中显示策略
         */
        public static HorizontalCellStyleStrategy getStyleStrategy(){
        
            // 这里需要设置不关闭流
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
             headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
            //设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)15);
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            WriteFont headWriteFont2 = new WriteFont();
            headWriteFont2.setFontHeightInPoints((short)13);
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        }
    }

> 导出实体类

package com.hl.easyexcel.domain;
    
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.format.DateTimeFormat;
    import com.alibaba.excel.annotation.format.NumberFormat;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import com.hl.easyexcel.controller.CustomSexConverterHandler;
    import lombok.Data;
    import lombok.experimental.Accessors;
     
    import java.io.Serializable;
    import java.util.Date;
    
    /**
     * 资质信息导出实体
     */
    @Data
    @Accessors(chain = true) //Lombok注解，链式赋值使用
    public class ExportExcelVo implements Serializable {
        
        private static final long serialVersionUID = 1L;
     
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业信息", "企业名称"}, index = 0)
        private String name;
     
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业信息", "社会统一信用代码"}, index = 1)
        private String creditCode;
     
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","公司法人"}, index = 2)
        private String legalPerson;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","性别"}, index = 3,converter = CustomSexConverterHandler.class)
        private Integer gender;
     
        @ExcelProperty(value = {
        "企业信息","区域"}, index = 4)
        private String province;
    
        @ColumnWidth(25)
        @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
        @ExcelProperty(value = {
        "企业信息","录入时间"}, index = 5)
        private Date createTime;
     
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","公司股东"}, index = 6)
        private String stockholder;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业信息","企业联系方式"}, index = 7)
        private String contact;
    
        @NumberFormat("#.##%")
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","增长收益"},index = 8)
        private Double doubleData;
     
    }

> 自定义性别类型转换器

package com.hl.easyexcel.controller;
    
    import com.alibaba.excel.converters.Converter;
    import com.alibaba.excel.enums.CellDataTypeEnum;
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.GlobalConfiguration;
    import com.alibaba.excel.metadata.property.ExcelContentProperty;
    
    /**
     * @ClassName CustomSexConverterHandler
     * @Description 自定义性别类型转换器
     * @Author hl
     * @Date 2022/12/28 10:39
     * @Version 1.0
     */
    public class CustomSexConverterHandler implements Converter<Integer> {
        
        @Override
        public Class supportJavaTypeKey() {
        
            return Integer.class;
        }
    
        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
        
            return CellDataTypeEnum.STRING;
        }
    
        /**
         * 导入
         */
        @Override
        public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        
           return "男".equals(cellData.getStringValue()) ? 1 : 0;
        }
    
        /**
         * 导出
         */
        @Override
        public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        
            if (integer.equals(1)){
        
                return new CellData("男");
            }else {
        
                return new CellData("女");
            }
        }
    }

> controller中的请求方法

/**
         * excel数据导出
         * @param size  导出条数， 也可以是用户需要导出数据的条件
         */
        @RequestMapping(value = "/export", method = RequestMethod.GET)
        public HttpResponseTemp<?> export(Long size, HttpServletResponse response){
        
            // 模拟根据条件在数据库查询数据
            ArrayList<ExportExcelVo> excelVos = new ArrayList<>();
            for (int i = 1; i <= size; i++) {
        
                ExportExcelVo excelVo = new ExportExcelVo();
                excelVo.setContact(String.valueOf(10000000000L + i));
                excelVo.setName("公司名称" + i);
                excelVo.setCreditCode("社会性用代码" + i);
                excelVo.setProvince("地区" + i);
                excelVo.setLegalPerson("法人" + i);
                excelVo.setStockholder("投资人" + i);
                excelVo.setCreateTime(new Date());
                if (i % 2 == 0){
        
                    excelVo.setGender(1);
                }else {
        
                    excelVo.setGender(0);
                }
                excelVo.setDoubleData(0.11);
                excelVos.add(excelVo);
            }
     
            String fileName = "数据导出" + System.currentTimeMillis();
     
            try {
        
                ExcelUtil.export(fileName,"数据", excelVos, ExportExcelVo.class, response);
            } catch (Exception e) {
        
                return ResultStat.SERVER_INTERNAL_ERROR.wrap("数据导出失败！" + e.getMessage());
            }
            return ResultStat.OK.wrap("数据导出成功");
        }

![在这里插入图片描述][667261eede18431a80ba870f975db5be.png_pic_center]

#### 2.自定义文件标题 ####

> 自定义文件表头名称处理器，这里需要合并多少列需要看情况

> 请求接口和实体类

@RequestMapping(value = "/export3", method = RequestMethod.GET)
        public HttpResponseTemp<?> export3(Long size, HttpServletResponse response){
        
            // 模拟根据条件在数据库查询数据
            ArrayList<ExportExcelVo2> excelVos = new ArrayList<>();
            for (int i = 1; i <= size; i++) {
        
                ExportExcelVo2 excelVo = new ExportExcelVo2();
                excelVo.setContact(String.valueOf(10000000000L + i));
                excelVo.setName("公司名称" + i);
                excelVo.setCreditCode("社会性用代码" + i);
                excelVo.setProvince("地区" + i);
                excelVo.setLegalPerson("法人" + i);
                excelVo.setStockholder("投资人" + i);
                excelVo.setCreateTime(new Date());
                if (i % 2 == 0){
        
                    excelVo.setGender(1);
                }else {
        
                    excelVo.setGender(0);
                }
                excelVo.setDoubleData(0.11);
                excelVos.add(excelVo);
            }
    
            String fileName = "数据导出" + System.currentTimeMillis();
    
            try {
        
                ExcelUtil.exportWithTitle(fileName,"数据", excelVos, ExportExcelVo2.class, response);
            } catch (Exception e) {
        
                return ResultStat.SERVER_INTERNAL_ERROR.wrap("数据导出失败！" + e.getMessage());
            }
            return ResultStat.OK.wrap("数据导出成功");
        }
    
    @Data
    @Accessors(chain = true) //Lombok注解，链式赋值使用
    public class ExportExcelVo2 implements Serializable {
        
        private static final long serialVersionUID = 1L;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业名称"}, index = 0)
        private String name;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "社会统一信用代码"}, index = 1)
        private String creditCode;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "公司法人"}, index = 2)
        private String legalPerson;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "性别"}, index = 3,converter = CustomSexConverterHandler.class)
        private Integer gender;
    
        @ExcelProperty(value = {
        "区域"}, index = 4)
        private String province;
    
        @ColumnWidth(25)
        @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
        @ExcelProperty(value = {
        "录入时间"}, index = 5)
        private Date createTime;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "公司股东"}, index = 6)
        private String stockholder;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业联系方式"}, index = 7)
        private String contact;
    
        @NumberFormat("#.##%")
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "增长收益"},index = 8)
        private Double doubleData;
    }

> 工具类

public static void exportWithTitle(String filename, String sheetName, ArrayList<ExportExcelVo2> dataResult, Class<ExportExcelVo2> clazz, HttpServletResponse response) {
        
            response.setStatus(200);
            OutputStream outputStream = null;
            try {
        
                if (StringUtils.isBlank(filename)) {
        
                    throw new RuntimeException("'filename' 不能为空");
                }
                String fileName = filename.concat(".xlsx");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
                outputStream = response.getOutputStream();
    
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(new CustomSheetTitleHandler())//自定义表头文件名称
                        .registerWriteHandler(getStyleStrategy())
                        .relativeHeadRowIndex(1)//标题占了一行
                        // 导出文件名
                        .autoCloseStream(Boolean.TRUE).sheet(sheetName)
                        .doWrite(dataResult);
            } catch (Exception e) {
        
                log.error("导出excel数据异常：", e);
                throw new RuntimeException(e);
            } finally {
        
                if (outputStream != null) {
        
                    try {
        
                        outputStream.flush();
                        outputStream.close();
                    } catch (IOException e) {
        
                        log.error("导出数据关闭流异常", e);
                    }
                }
            }
        }

![在这里插入图片描述][04a002e5ca7e46ceb941739519a05b99.png_pic_center]

#### 3.合并单元格导出 ####

> 版本 2.2.6+

##### 注解方式 #####

注解这里只能使用一种，不可以合并多个，要合并多个使用下面那种方式

@Data
    @Accessors(chain = true) //Lombok注解，链式赋值使用
    // 将第3-7行的8列合并成一个单元格
    @OnceAbsoluteMerge(firstRowIndex = 2, lastRowIndex = 6, firstColumnIndex = 8, lastColumnIndex = 8)
    // 将第6-7行的2-3列合并成一个单元格
    // @OnceAbsoluteMerge(firstRowIndex = 5, lastRowIndex = 6, firstColumnIndex = 1, lastColumnIndex = 2)
    public class ExportExcelVo implements Serializable {
        }

![在这里插入图片描述][7705daa5e56e4d058d231a9a9a6b4ae9.png_pic_center]

![在这里插入图片描述][a5b8eb631e1145bc9841ed26c1df2188.png_pic_center]

##### 通过 registerWriteHandler 方法注册进去 #####

EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(getStyleStrategy())
                        //将第3-7行的8列合并成一个单元格
                        .registerWriteHandler(new OnceAbsoluteMergeStrategy(2,6,8,8))
                        //将第6-7行的2-3列合并成一个单元格
                        .registerWriteHandler(new OnceAbsoluteMergeStrategy(5,6,1,2))
                        // 导出文件名
                        .autoCloseStream(Boolean.TRUE).sheet(sheetName)
                        .doWrite(dataResult);

![在这里插入图片描述][a5230f1895f0469883454a56a7f3cc3c.png_pic_center]

##### 自定义合并规则进行合并 #####

> 自定义合并策略

package com.hl.easyexcel.controller;
    
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.util.List;
    
    public class ExcelFillCellMergeStrategy implements CellWriteHandler {
        
        private int[] mergeColumnIndex;
        private int mergeRowIndex;
    
        public ExcelFillCellMergeStrategy() {
        
        }
    
        public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
    
        public int[] getMergeColumnIndex() {
        
            return mergeColumnIndex;
        }
    
        public void setMergeColumnIndex(int[] mergeColumnIndex) {
        
            this.mergeColumnIndex = mergeColumnIndex;
        }
    
        public int getMergeRowIndex() {
        
            return mergeRowIndex;
        }
    
        public void setMergeRowIndex(int mergeRowIndex) {
        
            this.mergeRowIndex = mergeRowIndex;
        }
    
        @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 integer, Boolean aBoolean) {
        
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        
           //当前行
            int curRowIndex = cell.getRowIndex();
            //当前列
            int curColIndex = cell.getColumnIndex();
    
            if (curRowIndex > mergeRowIndex) {
        
                for (int columnIndex : mergeColumnIndex) {
        
                    if (curColIndex == columnIndex) {
        
                        mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }
    
        /**
         * 当前单元格向上合并
         * @param cell             当前单元格
         * @param curRowIndex      当前行
         * @param curColIndex      当前列
         */
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        
            //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
    
            // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
            //
            if (curData.equals(preData)) {
        
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
        
                    CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                    // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                    if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
        
                        sheet.removeMergedRegion(i);
                        cellRangeAddr.setLastRow(curRowIndex);
                        sheet.addMergedRegion(cellRangeAddr);
                        isMerged = true;
                    }
                }
                // 若上一个单元格未被合并，则新增合并单元
                if (!isMerged) {
        
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }
    }

> 工具类

public static void exportMerge(String filename, String sheetName,List<?> dataResult, Class<?> clazz, HttpServletResponse response,ExcelFillCellMergeStrategy excelFillCellMergeStrategy) {
        
            response.setStatus(200);
            OutputStream outputStream = null;
            try {
        
                if (StringUtils.isBlank(filename)) {
        
                    throw new RuntimeException("'filename' 不能为空");
                }
                String fileName = filename.concat(".xlsx");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
                outputStream = response.getOutputStream();
    
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(getStyleStrategy())
                        .registerWriteHandler(excelFillCellMergeStrategy)//合并策略
                        // 导出文件名
                        .autoCloseStream(Boolean.TRUE).sheet(sheetName)
                        .doWrite(dataResult);
            } catch (Exception e) {
        
                log.error("导出excel数据异常：", e);
                throw new RuntimeException(e);
            } finally {
        
                if (outputStream != null) {
        
                    try {
        
                        outputStream.flush();
                        outputStream.close();
                    } catch (IOException e) {
        
                        log.error("导出数据关闭流异常", e);
                    }
                }
            }
        }

> 测试

@RequestMapping(value = "/export2", method = RequestMethod.GET)
        public HttpResponseTemp<?> export2(Long size, HttpServletResponse response){
        
            // 模拟根据条件在数据库查询数据
            ArrayList<ExportExcelVo> excelVos = new ArrayList<>();
            for (int i = 1; i <= size; i++) {
        
                ExportExcelVo excelVo = new ExportExcelVo();
                excelVo.setContact(String.valueOf(10000000000L + i));
                excelVo.setName("公司名称" + i);
                excelVo.setCreditCode("社会性用代码" + i);
                excelVo.setProvince("地区" + i);
                excelVo.setLegalPerson("法人" + i);
                excelVo.setStockholder("投资人" + i);
                excelVo.setCreateTime(new Date());
                if (i == 1 || i ==2){
        
                    excelVo.setGender(1);
                    excelVo.setDoubleData(0.11);
                }else {
        
                    excelVo.setGender(0);
                    excelVo.setDoubleData(0.32);
                }
                excelVos.add(excelVo);
            }
    
            String fileName = "数据导出" + System.currentTimeMillis();
    
            try {
        
                //需要合并的列
                int[] mergeColumnIndex = {
        3,8};
                //从第几行后开始合并
                int mergeRowIndex = 2;
                //自定义合并策略
                ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex);
                ExcelUtil.exportMerge(fileName,"数据", excelVos, ExportExcelVo.class, response,excelFillCellMergeStrategy);
            } catch (Exception e) {
        
                return ResultStat.SERVER_INTERNAL_ERROR.wrap("数据导出失败！" + e.getMessage());
            }
            return ResultStat.OK.wrap("数据导出成功");
        }

![在这里插入图片描述][db086a9d76d549fd939d28531d4e933a.png_pic_center]

#### 4.批注和下拉框导出 ####

##### 批注信息类 #####

package com.hl.easyexcel.controller.anno;
     
    import lombok.Data;
     
    /**
     * 批注信息类
     *
     * @author xudongmaster
     */
    @Data
    public class CommentModel {
        
        /**
         * sheet页名称
         */
        private String sheetName;
        /**
         * 列索引
         */
        private int colIndex;
        /**
         * 行索引
         */
        private int rowIndex;
        /**
         * 行索引
         */
        private String commentContent;
     
        /**
         * 生成批注信息
         *
         * @param sheetName      sheet页名称
         * @param rowIndex       行号
         * @param columnIndex    列号
         * @param commentContent 批注内容
         * @return
         */
        public static CommentModel createCommentModel(String sheetName, int rowIndex, int columnIndex, String commentContent) {
        
            CommentModel commentModel = new CommentModel();
            //sheet页名称
            commentModel.setSheetName(sheetName);
            //行号
            commentModel.setRowIndex(rowIndex);
            //列号
            commentModel.setColIndex(columnIndex);
            //批注内容
            commentModel.setCommentContent(commentContent);
            return commentModel;
        }
    }

##### 自定义批注处理器 #####

package com.hl.easyexcel.controller.anno;
     
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
    import com.alibaba.excel.write.metadata.holder.*;
    import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    
    import java.util.*;
    import java.util.stream.Collectors;
     
    /**
     * 自定义批注处理器
     *
     * @author xudongmaster
     */
    public class CommentWriteHandler extends AbstractRowWriteHandler {
        
     
     
        /**
         * sheet页名称列表
         */
        private List<String> sheetNameList;
        List<CommentModel> commentList = new ArrayList<>();
     
        /**
         * 自定义批注适配器构造方法
         *
         * @param commentList 批注信息
         * @param extension   文件后缀（xlsx、xls）
         */
        public CommentWriteHandler(List<CommentModel> commentList, String extension) {
        
            if (CollUtil.isEmpty(commentList)) {
        
                return;
            }
            //文件不为指定的格式时，默认为Xlsx
            if (!StrUtil.equals(extension, "xlsx") && !StrUtil.equals(extension, "xls")) {
        
                extension = "xlsx";
            }
            this.commentList = commentList.stream().filter(x ->
                    StrUtil.isNotBlank(x.getSheetName()) && x.getColIndex() >=0 && x.getRowIndex() >= 0 && StrUtil.isNotBlank(x.getCommentContent())
            ).collect(Collectors.toList());
            sheetNameList = this.commentList.stream().map(CommentModel::getSheetName).distinct().collect(Collectors.toList());
            this.extension = extension;
        }
     
        /**
         * 文档后缀名
         */
        private String extension;
     
        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                    Integer relativeRowIndex, Boolean isHead) {
        
            Sheet sheet = writeSheetHolder.getSheet();
            //不需要添加批注，或者当前sheet页不需要添加批注
            if (CollUtil.isEmpty(commentList) || !sheetNameList.contains(sheet.getSheetName())) {
        
                return;
            }
            //获取当前行的批注信息
            List<CommentModel> rowCommentList = commentList.stream().filter(x ->
                    StrUtil.equals(x.getSheetName(), sheet.getSheetName())
                            && Objects.equals(relativeRowIndex, x.getRowIndex())).collect(Collectors.toList());
            //当前行没有批注信息
            if (CollUtil.isEmpty(rowCommentList)) {
        
                return;
            }
            List<Integer> colIndexList = rowCommentList.stream().map(CommentModel::getColIndex).distinct().collect(Collectors.toList());
            for (Integer colIndex : colIndexList) {
        
                //同一单元格的批注信息
                List<CommentModel> cellCommentList = rowCommentList.stream().filter(x ->
                        Objects.equals(colIndex, x.getColIndex())).collect(Collectors.toList());
                if (CollUtil.isEmpty(cellCommentList)) {
        
                    continue;
                }
                //批注内容拼成一条
                String commentContent = cellCommentList.stream().map(CommentModel::getCommentContent).collect(Collectors.joining());
                Cell cell = row.getCell(colIndex);
                addComment(cell, commentContent, extension);
            }
            //删除批注信息
            commentList.remove(rowCommentList);
            //重新获取要添加的sheet页姓名
            sheetNameList = commentList.stream().map(CommentModel::getSheetName).distinct().collect(Collectors.toList());
        }
    
        /**
         * 给Cell添加批注
         *
         * @param cell 单元格
         * @param value 批注内容
         * @param extension 扩展名
         */
        public static void addComment(Cell cell, String value, String extension) {
        
            Sheet sheet = cell.getSheet();
            cell.removeCellComment();
            if ("xls".equals(extension)) {
        
                ClientAnchor anchor = new HSSFClientAnchor();
                // 关键修改
                anchor.setDx1(0);
                anchor.setDx2(0);
                anchor.setDy1(0);
                anchor.setDy2(0);
                anchor.setCol1(cell.getColumnIndex());
                anchor.setRow1(cell.getRowIndex());
                anchor.setCol2(cell.getColumnIndex() + 3);
                anchor.setRow2(cell.getRowIndex() + 4);
                // 结束
                Drawing<?> drawing = sheet.createDrawingPatriarch();
                Comment comment = drawing.createCellComment(anchor);
                // 输入批注信息
                comment.setString(new HSSFRichTextString(value));
                // 将批注添加到单元格对象中
                cell.setCellComment(comment);
            } else if ("xlsx".equals(extension)) {
        
                ClientAnchor anchor = new XSSFClientAnchor();
                // 关键修改
                anchor.setDx1(0);
                anchor.setDx2(0);
                anchor.setDy1(0);
                anchor.setDy2(0);
                anchor.setCol1(cell.getColumnIndex());
                anchor.setRow1(cell.getRowIndex());
                anchor.setCol2(cell.getColumnIndex() + 3);
                anchor.setRow2(cell.getRowIndex() + 4);
                // 结束
                Drawing<?> drawing = sheet.createDrawingPatriarch();
                Comment comment = drawing.createCellComment(anchor);
                // 输入批注信息
                comment.setString(new XSSFRichTextString(value));
                // 将批注添加到单元格对象中
                cell.setCellComment(comment);
            }
        }
    }

##### 自定义下拉框处理器 #####

package com.hl.easyexcel.controller;
    
    import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddressList;
     
    import java.util.Map;
    import java.util.TreeMap;
     
    /**
     * 这个类的作用主要是给列增加下拉框
     * 主要是为了方便用户填写数据
     */
    public class CustomSheetSelectHandler implements SheetWriteHandler {
        
     
        /**
         * 存放下拉内容的集合
         * key为列的下标， value为下拉内容数组
         */
        private final Map<Integer, String[]> map = new TreeMap<>();
     
        /**
         * 工作簿下标，从0开始
         */
        private int index = 0;
     
        /**
         * 给多少行添加下拉框，这里默认给2000行
         */
        private final int batchSize = 2000;
     
     
     
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
     
        }
     
        /**
         * 宝藏在此：如果下拉框内容总的长度超过255，会导致Cell有下拉框，但是下拉内容显示不了，
         * 这时我们可以新建一个sheet，将其隐藏，然后将里面的内容引用到我们的下拉框列就可以。
         * 值得细品
         */
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
            // excel下标从0开始，这里第二列的下拉选择内容
            map.put(2, new String[]{
        "张三", "李四"});
            // excel下标从0开始，这里第三列的下拉选择内容
            map.put(3, new String[]{
        "男", "女"});
     
            DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
            map.forEach((k, v) -> {
        
                // 创建sheet，突破下拉框255的限制
                // 获取一个workbook
                Workbook workbook = writeWorkbookHolder.getWorkbook();
                // 定义sheet的名称
                String sheetName = "sheet" + k;
                // 1.创建一个隐藏的sheet 名称为 proviceSheet
                Sheet proviceSheet = workbook.createSheet(sheetName);
                // 从第二个工作簿开始隐藏
                this.index++;
                // 设置隐藏
                workbook.setSheetHidden(this.index, true);
                // 2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
                for (int i = 0, length = v.length; i < length; i++) {
        
                    // i:表示你开始的行数 0表示你开始的列数
                    proviceSheet.createRow(i).createCell(0).setCellValue(v[i]);
                }
                Name category1Name = workbook.createName();
                category1Name.setNameName(sheetName);
                // 4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
                category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (v.length));
                // 5 将刚才设置的sheet引用到你的下拉列表中,1表示从行的序号1开始（开始行，通常行的序号为0的行是表头），50表示行的序号50（结束行），表示从行的序号1到50，k表示开始列序号和结束列序号
                CellRangeAddressList addressList = new CellRangeAddressList(1, batchSize, k, k);
                DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
                DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
     
                // 阻止输入非下拉选项的值
                dataValidation3.setErrorStyle(DataValidation.ErrorStyle.STOP);
                dataValidation3.setShowErrorBox(true);
                dataValidation3.setSuppressDropDownArrow(true);
                dataValidation3.createErrorBox("提示", "此值与单元格定义格式不一致");
                // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
                writeSheetHolder.getSheet().addValidationData(dataValidation3);
            });
        }
    }

##### 工具类 #####

public static void exportTemplate(String filename, String sheetName,List<?> dataResult, Class<?> clazz, HttpServletResponse response) {
        
            response.setStatus(200);
            OutputStream outputStream = null;
            try {
        
                if (StringUtils.isBlank(filename)) {
        
                    throw new RuntimeException("'filename' 不能为空");
                }
                String fileName = filename.concat(".xlsx");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
                outputStream = response.getOutputStream();
    
                List<CommentModel> commentList = new ArrayList<>();
                commentList.add(CommentModel.createCommentModel(sheetName, 1, 0, "第一条批注。"));
                commentList.add(CommentModel.createCommentModel(sheetName, 1, 1, "第二条批注。"));
    
    
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(getStyleStrategy())
                        .registerWriteHandler(new CommentWriteHandler(commentList,"xlsx"))
                        .registerWriteHandler(new CustomSheetSelectHandler())
                        // 导出文件名
                        .autoCloseStream(Boolean.TRUE).sheet(sheetName)
                        .doWrite(dataResult);
            } catch (Exception e) {
        
                log.error("导出excel数据异常：", e);
                throw new RuntimeException(e);
            } finally {
        
                if (outputStream != null) {
        
                    try {
        
                        outputStream.flush();
                        outputStream.close();
                    } catch (IOException e) {
        
                        log.error("导出数据关闭流异常", e);
                    }
                }
            }
        }

##### 测试 #####

@RequestMapping(value = "/template", method = RequestMethod.GET)
        public HttpResponseTemp<?> template(HttpServletResponse response){
        
            String fileName = "导入模板下载" + System.currentTimeMillis();
            try {
        
                ExcelUtil.exportTemplate(fileName,"模板", null, ImportExcelVo.class, response);
            } catch (Exception e) {
        
                return ResultStat.SERVER_INTERNAL_ERROR.wrap("模板下载失败" + e.getMessage());
            }
            return ResultStat.OK.wrap("模板下载成功！");
        }
    
    @Data
    public class ImportExcelVo implements Serializable {
        
        private static final long serialVersionUID = 1L;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业信息", "企业名称"}, index = 0)
        private String name;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业信息", "社会统一信用代码"}, index = 1)
        private String creditCode;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","公司法人"}, index = 2)
        private String legalPerson;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","性别"}, index = 3,converter = CustomSexConverterHandler.class)
        private Integer gender;
    
        @ExcelProperty(value = {
        "企业信息","区域"}, index = 4)
        private String province;
    
        @ColumnWidth(25)
        @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
        @ExcelProperty(value = {
        "企业信息","录入时间"}, index = 5)
        private Date createTime;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "企业信息","公司股东"}, index = 6)
        private String stockholder;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业信息","企业联系方式"}, index = 7)
        private String contact;
    }

![在这里插入图片描述][1d60832786414302bdbc983a4856f1d9.png_pic_center]

### 三、导入 ###

#### 1、修改导入模板实体类 ####

> 添加字段rowNum记录是哪一行数据，和重写equals和hashCode方法判断数据是否重复

@Data
    public class ImportExcelVo implements Serializable {
        
        private static final long serialVersionUID = 1L;
    
        @ExcelIgnore
        private Integer rowNum;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业名称"}, index = 0)
        private String name;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "社会统一信用代码"}, index = 1)
        private String creditCode;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "公司法人"}, index = 2)
        private String legalPerson;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "性别"}, index = 3,converter = CustomSexConverterHandler.class)
        private Integer gender;
    
        @ExcelProperty(value = {
        "区域"}, index = 4)
        private String province;
    
        @ColumnWidth(25)
        @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
        @ExcelProperty(value = {
        "录入时间"}, index = 5)
        private Date createTime;
    
        @ColumnWidth(15)
        @ExcelProperty(value = {
        "公司股东"}, index = 6)
        private String stockholder;
    
        @ColumnWidth(25)
        @ExcelProperty(value = {
        "企业联系方式"}, index = 7)
        private String contact;
    
        @Override
        public boolean equals(Object o) {
        
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            ImportExcelVo that = (ImportExcelVo) o;
            return Objects.equals(name, that.name) && Objects.equals(creditCode, that.creditCode) && Objects.equals(legalPerson, that.legalPerson) && Objects.equals(gender, that.gender) && Objects.equals(province, that.province) && Objects.equals(createTime, that.createTime) && Objects.equals(stockholder, that.stockholder) && Objects.equals(contact, that.contact);
        }
    
        @Override
        public int hashCode() {
        
            return Objects.hash(name, creditCode, legalPerson, gender, province, createTime, stockholder, contact);
        }
    }

#### 2、添加expiringmap依赖 ####

> 添加expiringmap依赖，用来解决，导入错误信息，放入一个有时间限制的map中，然后进行下载错误信息

<dependency>
                <groupId>net.jodah</groupId>
                <artifactId>expiringmap</artifactId>
                <version>0.5.10</version>
            </dependency>

expiringmap的使用

#### 3、自定义监听器处理导入数据 ####

package com.hl.easyexcel.controller;
    
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.util.ObjectUtil;
    import cn.hutool.json.JSONUtil;
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.hl.easyexcel.domain.ImportErrorInfo;
    import com.hl.easyexcel.domain.ImportExcelVo;
    import lombok.SneakyThrows;
    import lombok.extern.slf4j.Slf4j;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    @Slf4j
    public final class ExcelListener extends AnalysisEventListener<ImportExcelVo> {
        
    
        /**
         * 此线程变量用来存储错误提示，只是提示表头是否符合模板要求
         */
        public static final ThreadLocal<Exception> RESP = new ThreadLocal<>();
    
        /**
         * 处理重复数据
         */
        private static final ThreadLocal<List<ImportExcelVo>> threadLocal = new ThreadLocal<>();
        /**
         * 批处理阈值1000
         */
        private static final int BATCH_COUNT = 1000;
    
        /**
         * 自定义用于暂时存储data
         * 可以通过实例获取该值
         */
        private final List<ImportExcelVo> dataList = new ArrayList<>();
    
        /**
         * 返回提示语
         */
        private final List<ImportErrorInfo> errorInfoList = new ArrayList<>();
    
        /**
         * 第几行有错误,这里根据表头来进行判断
         */
        private int row = 1;
    
        /**
         * 重写invokeHeadMap方法，校验表头
         *headMap的key是表头下标，value是内容
         */
        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        
            // headMap.containsKey(下标)，先判空
            // headMap.get(0).equals() 判内容是否相符
            log.info(JSONUtil.toJsonStr(headMap));
            if ((!headMap.containsKey(0) || !headMap.get(0).trim().equals("企业名称"))
                    || (!headMap.containsKey(1) || !headMap.get(1).trim().equals("社会统一信用代码"))
                    || (!headMap.containsKey(2) || !headMap.get(2).trim().equals("公司法人"))
                    || (!headMap.containsKey(3) || !headMap.get(3).trim().equals("性别"))
                    || (!headMap.containsKey(4) || !headMap.get(4).trim().equals("区域"))
                    || (!headMap.containsKey(5) || !headMap.get(5).trim().equals("录入时间"))
                    || (!headMap.containsKey(6) || !headMap.get(6).trim().equals("公司股东"))
                    || (!headMap.containsKey(7) || !headMap.get(7).trim().equals("企业联系方式")) ){
        
                RESP.set(new RuntimeException("表头错误"));
            }
    
        }
    
        /**
         * 具体校验逻辑
         */
        @Override
        public void invoke(ImportExcelVo importExcelVo, AnalysisContext analysisContext) {
        
            log.info(importExcelVo.toString());
            //给每条数据设置行数
            importExcelVo.setRowNum(++row);
            //检查重复数据
            checkDuplicateData(importExcelVo,errorInfoList);
            //校验参数
            Integer gender = importExcelVo.getGender();
            if (gender != 0){
        
                saveError(importExcelVo.getRowNum(),"性别不能为男",errorInfoList);
            }
    
            //存入数据
            dataList.add(importExcelVo);
    
        }
    
        /**
         * excel读取完都会调用此方法
         */
        @SneakyThrows
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
        
            if (ObjectUtil.isNotNull(RESP) && ObjectUtil.isNotNull(RESP.get())){
        
                try {
        
                    throw RESP.get();
                } finally {
        
                    //最后清空线程变量的值
                    RESP.remove();
                }
            }
    
            //解析结束销毁不用的资源
            //注意不要调用datas.clear(),否则getDatas为null
    
            // 获取总行数（含表头）
            Integer totalRowNum = context.readSheetHolder().getApproximateTotalRowNumber();
            totalRowNum = totalRowNum-1;
            if(totalRowNum.compareTo(0) == 0){
        
                throw new RuntimeException("数据为空");
            }
            if(totalRowNum > BATCH_COUNT){
        
                throw new RuntimeException("excel中的数据条数大于1000条");
            }
        }
    
        private void checkDuplicateData(ImportExcelVo inputEntity, List<ImportErrorInfo> errorInfoList) {
        
            List<ImportExcelVo> threadLocalVal = threadLocal.get();
            if (CollUtil.isEmpty(threadLocalVal)) {
        
                threadLocalVal = new ArrayList<>();
            }
    
            threadLocalVal.forEach(e -> {
        
                if (e.equals(inputEntity)) {
        
                    ImportErrorInfo errorInfo = new ImportErrorInfo();
                    errorInfo.setLine(inputEntity.getRowNum());
                    errorInfo.setMsg("数据与第" + e.getRowNum() + "行重复");
                    errorInfoList.add(errorInfo);
                }
            });
            // 添加本行数据对象到ThreadLocal中
            threadLocalVal.add(inputEntity);
            threadLocal.set(threadLocalVal);
        }
    
        /**
         * 保存提示信息到集合
         * @param rowIndex 行数
         * @param desc 提示信息
         * @param errorInfoList 存入集合
         */
        private void saveError(Integer rowIndex, String desc, List<ImportErrorInfo> errorInfoList) {
        
            ImportErrorInfo errorInfo = new ImportErrorInfo();
            errorInfo.setLine(rowIndex);
            errorInfo.setMsg(desc);
            errorInfoList.add(errorInfo);
        }
    
    
        /**
         * 返回数据
         * @return 返回读取的数据集合
         **/
        public List<ImportExcelVo> getDataList() {
        
            return dataList;
        }
    
        /**
         * 返回数据
         * @return 返回提示集合
         **/
        public List<ImportErrorInfo> getErrorInfoList() {
        
            return errorInfoList;
        }
    
        /**
         * 返回重复数据
         * @return 返回提示集合
         */
        public ThreadLocal<List<ImportExcelVo>> getThreadLocal() {
        
            return threadLocal;
        }
    }

#### 4、读取文件和错误信息下载 ####

private static final ExpiringMap<String,List<ImportErrorInfo>> expiryFileMap  = ExpiringMap.builder()
                .maxSize(100)
                .expiration(180, TimeUnit.SECONDS)
                .expirationPolicy(ExpirationPolicy.ACCESSED)
                .variableExpiration()
                .build();
    
        /**
         * 导入
         */
        @PostMapping("/import")
        @ResponseBody
        public HttpResponseTemp<?> importManualEntry(@RequestParam(value = "file") MultipartFile file){
        
            ExcelListener listener = new ExcelListener();
            try {
        
                //headRowNumber(2)从第3行开始读取，使用getDataList()方法取出数据
                EasyExcel.read(file.getInputStream(), ImportExcelVo.class,listener).headRowNumber(1).sheet(0).doRead();
            }catch (Exception e){
        
                throw new RuntimeException(e);
            }finally {
        
                // 清除threadLocal 防止内存泄漏
                ThreadLocal<List<ImportExcelVo>> threadLocal = listener.getThreadLocal();
                if (threadLocal != null) {
        
                    threadLocal.remove();
                }
            }
            //正确信息
            List<ImportExcelVo> excelList = listener.getDataList();
            //基础校验有错误，则直接返回错误信息
            List<ImportErrorInfo> errorInfoList = listener.getErrorInfoList();
            if(ObjectUtil.isNotEmpty(errorInfoList)){
        
                errorInfoList.sort(Comparator.comparing(ImportErrorInfo::getLine));
                String uuid = IdUtil.randomUUID();
                expiryFileMap.put(uuid,errorInfoList);
                return ResultStat.SERVER_INTERNAL_ERROR.wrap("数据导入失败:" + uuid);
            }else {
        
                System.out.println(excelList);
                return ResultStat.OK.wrap("数据导入完成");
            }
        }
    
        @GetMapping("/downloadErrorFile/{fileId}")
        public void downloadErrorFile(@PathVariable String fileId,HttpServletResponse response){
        
            //从存储错误信息的map中把数据取出来，然后再下载导出错误文件模板
            List<ImportErrorInfo> dataList = expiryFileMap.get(fileId);
            if (CollUtil.isNotEmpty(dataList)) {
        
                String name = "导入错误信息";
                ExcelUtil.export(name,"数据", dataList, ImportErrorInfo.class, response);
            } else {
        
                throw ApiException.wrapMessage(ResultStat.SERVER_INTERNAL_ERROR,"文件不存在");
            }
        }

> 导入文件样式

![在这里插入图片描述][3dc3cfa7c2d4449481300efb44294694.png_pic_center]

> 下载的错误信息

![在这里插入图片描述][2695e9d7c0614faebb1a406fdac7aff8.png_pic_center]

[667261eede18431a80ba870f975db5be.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/01f0a008f7cb4770a0e910b161137840.png
[04a002e5ca7e46ceb941739519a05b99.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/6447bf54b829431db1f4ddee55331093.png
[7705daa5e56e4d058d231a9a9a6b4ae9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/cc4d1c5bc7dd42af8177f94dc5fbaeba.png
[a5b8eb631e1145bc9841ed26c1df2188.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e684c7f820434acea05bc882aa5ef781.png
[a5230f1895f0469883454a56a7f3cc3c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/f7d913af7b6142d4b711e2121eb5b784.png
[db086a9d76d549fd939d28531d4e933a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/4f1b69a829e64b4ab7318fe0033f089f.png
[1d60832786414302bdbc983a4856f1d9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d3860d5e48b94cd5aa622f27b9fd5090.png
[3dc3cfa7c2d4449481300efb44294694.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/85f31124b914431ebfb82aecebdd4d01.png
[2695e9d7c0614faebb1a406fdac7aff8.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8032d905803c4a55845530c1c0dc92b1.png