easypoi导入案例

easypoi导入案例

一、依赖

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>

二、导出模板

1、excel模板实体类(同下)

2、具体实现类

    public void exportTemplate(HttpServletResponse response) {
        CodingUtils.checkUserIdBefore();

        List<SignalTemplateVO> list = new ArrayList<>();

        String title = "信号模板";
        String time = DateUtil.timeStamp2Date2(System.currentTimeMillis(), "yyyyMMddHHmmss");
        String fileName = "设备信号模板(" + time + ")";
        try {
            EasyPoiUtil.exportExcel2Signal(list, title, "设备信号", SignalTemplateVO.class,
                    fileName, true, true, true,response);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

3、easypoi工具类中的方法

这里设置下拉框，ExcelType必须为HSSF

ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);

package com.mye.cloudboxdcim.framework.engine.poi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.mye.cloudboxdcim.framework.engine.poi.myeasypoi.ExcelExportTitleStyle;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @ClassName EasyPoiUtil
 * @Description easypoi 工具类
 * @Author hl
 * @Date 2022/11/6 12:27
 * @Version 1.0
 */
public class EasyPoiUtil {

    /**
     * excel 导出
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param isStyle        是否自定义表头样式
     * @param response 响应流
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader,boolean isStyle, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        if (isStyle){
            exportParams.setStyle(ExcelExportTitleStyle.class);
        }
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出闪断震荡规则模板
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param isStyle        是否自定义表头样式
     * @param isSelectList   是否自定义下拉框
     * @param response 响应流
     */
    public static void exportExcel2FlashShockRule(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader,boolean isStyle,boolean isSelectList, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        if (isStyle){
            exportParams.setStyle(ExcelExportTitleStyle.class);
        }
        if (isSelectList){
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            //规则状态
            selectList(workbook,2,1000,4,4,new String[]{"启用","停用"});
            //告警级别
            selectList(workbook,2,1000,5,5,new String[]{"提示","次要","重要","紧急"});
            //闪断分析状态
            selectList(workbook,2,1000,7,7,new String[]{"启用","停用"});
            //闪断状态
            selectList(workbook,2,1000,9,9,new String[]{"丢弃","屏蔽"});
            //震荡状态
            selectList(workbook,2,1000,10,10,new String[]{"启用","停用"});
            //震荡处理策略
            selectList(workbook,2,1000,15,15,new String[]{"产生振荡告警并将触发振荡后的源告警显示在屏蔽告警中","产生振荡告警源告警直接上报","产生振荡告警并丢弃触发振荡后的源告警","重定义触发振荡后的源告警级别"});
            //源告警级别
            selectList(workbook,2,1000,16,16,new String[]{"提示","次要","重要","紧急"});
            //优先级
            selectList(workbook,2,1000,17,17,new String[]{"最高","高","中","低","最低"});

            downLoadExcel(fileName, response, workbook);
        }
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }


    /**
     * excel 导出设备信号模板
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param isStyle        是否自定义表头样式
     * @param isSelectList   是否自定义下拉框
     * @param response 响应流
     */
    public static void exportExcel2Signal(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader,boolean isStyle,boolean isSelectList, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        if (isStyle){
            exportParams.setStyle(ExcelExportTitleStyle.class);
        }
        if (isSelectList){
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            //信号属性类型
            selectList(workbook,2,1000,5,5,new String[]{"AI","DI","Other","DO"});
            //类型
            selectList(workbook,2,1000,10,10,new String[]{"采集信号","统计信号"});
            downLoadExcel(fileName, response, workbook);
        }
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + EasyPoiUtil.ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

   /**
      * @MethodName selectList
      * @Description  生成下拉列表
      * @param workbook
      * @param firstRow 下拉单元格行号 从0开始
      * @param lastRow 下拉单元格结束行号
      * @param firstCol 下拉单元格列号 从0开始
      * @param lastCol  下拉单元格结束列号
      * @param dataList 动态生成的下拉内容
      * @Author hl
      * @Date 2022/11/22 14:21
      */
    public static void selectList(Workbook workbook,int firstRow,int lastRow,int firstCol,int lastCol,String[] dataList ){
        Sheet sheet = workbook.getSheetAt(0);

        //生成下拉列表
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        //生成下拉框内容
        DVConstraint dvConstraint  = DVConstraint.createExplicitListConstraint(dataList);
        HSSFDataValidation dataValidation  = new HSSFDataValidation(cellRangeAddressList, dvConstraint);

        //设置错误信息提示
        dataValidation.setShowErrorBox(true);
        //对sheet页生效
        sheet.addValidationData(dataValidation );
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
    }


    public static <T>  ExcelImportResult<?> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass,
                                          Integer sheetIndex, boolean isVerify, IExcelVerifyHandler handler) throws IOException {
        try {
            ImportParams importParams = new ImportParams();
            importParams.setTitleRows(1); // 设置标题列占几行
            importParams.setHeadRows(2);  // 设置字段名称占几行 即header
            importParams.setNeedVerify(true);//开启校验
            importParams.setVerifyHandler(handler);// MyVerifyHandler这个类是自己创建的
            importParams.setStartSheetIndex(0);  // 设置从第几张表格开始读取，这里0代表第一张表，默认从第一张表读取
            return new ExcelImportService().importExcelByIs(file.getInputStream(), pojoClass, importParams, true);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    public static <T> ExcelImportResult importExcelResult(MultipartFile file, Class<T> pojoClass) throws IOException {

        try {
            ImportParams importParams = new ImportParams();
            importParams.setTitleRows(1); // 设置标题列占几行
            importParams.setHeadRows(2);  // 设置字段名称占几行 即header
            importParams.setNeedVerify(true);//开启校验
//            importParams.setVerifyHandler(new MyVerifyHandler());// MyVerifyHandler这个类是自己创建的
            importParams.setStartSheetIndex(0);  // 设置从第几张表格开始读取，这里0代表第一张表，默认从第一张表读取
            return new ExcelImportService().importExcelByIs(file.getInputStream(), pojoClass, importParams, true);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerfiy  是否检验excel内容
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerify(needVerfiy);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        /**
         * 文件类型
         */
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}

4、自定义样式类

package com.mye.cloudboxdcim.framework.engine.poi.myeasypoi;

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import cn.hutool.core.util.ObjectUtil;
import org.apache.poi.ss.usermodel.*;

/**
 * @ClassName EasyPoiUtil
 * @Description 导出自定义title的工具类
 * @Author hl
 * @Date 2022/11/6 12:27
 * @Version 1.0
 */
public class ExcelExportTitleStyle extends AbstractExcelExportStyler implements IExcelExportStyler {

    public ExcelExportTitleStyle(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        // 自定义字体
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE1.getIndex());
        font.setBold(true);
        font.setFontName("宋体");
        titleStyle.setFont(font);

		// 自定义背景色
        titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        //自动换行
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

}

三、导入校验

1、excel模板实体类

这个模板实体类，也就是导入的时候excel的样式，例如表头字段需要一样，也是导入校验的实体类，需要实现 IExcelDataModel, IExcelModel 重写 equals和hashCode方法和rowNum、errorMsg的get\set方法

package com.mye.cloudboxdcim.framework.api.vo.device;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.mye.cloudboxdcim.framework.engine.validator.anno.ContainsDataValid;
import com.mye.cloudboxdcim.framework.engine.validator.anno.HaveNoBlankValid;
import lombok.Data;
import org.hibernate.validator.constraints.Length;
import javax.validation.constraints.*;
import java.util.Objects;

/**
 * @ClassName SignalTemplateVO
 * @Description 信号模板出参
 * @Author hl
 * @Date 2022/11/6 14:06
 * @Version 1.0
 */
@Data
@ExcelTarget("SignalTemplateVO")
public class SignalTemplateVO implements IExcelDataModel, IExcelModel {

    /**
     * 行号
     */
    private int rowNum;

    /**
     * 错误消息
     */
    private String errorMsg;

    /**
     * 设备型号名称
     */
    @Excel(name = "设备型号名称(必填)",width = 20,orderNum = "0",isImportField = "true")
    @NotBlank(message = "设备型号名称不可以为空")
    @Length(min = 1, max = 32, message = "设备型号名称长度需要在32个字以内")
    private String deviceModelName;
    /**
     * 信号名称
     */
    @Excel(name = "信号名称(必填)",width = 20,orderNum = "1",isImportField = "true")
    @NotBlank(message = "信号名称不可以为空")
    @Pattern(regexp = "^[\\u4e00-\\u9fa5\\ A-Za-z0-9\\-\\_]{2,50}",message = "信号名称只能包含汉字,字母,数字,下划线,中横线,长度是2~50")
    private String name;

    /**
     * 描述
     */
    @Excel(name = "描述",width = 20,orderNum = "2",isImportField = "true")
    @HaveNoBlankValid(message = "描述长度最大为200",value = "description") //这个是自定义注解 集成hibernate-validator实现
    private String description;

    /**
     * 指标名称
     */
    @Excel(name = "指标名称(必填)",width = 20,orderNum = "3",isImportField = "true")
    @NotBlank(message = "指标名称不可以为空")
    @Pattern(regexp = "[a-zA-Z_:][a-zA-Z0-9_:]{2,200}",message = "指标名称只能包含字母、数字和下划线且以字母开头,长度是3~200")
    private String metricName;

    /**
     * 信号组名称
     */
    @Excel(name = "信号组名称(必填)",width = 20,orderNum = "4",isImportField = "true")
    @NotBlank(message = "信号分组名称不可以为空")
    @Pattern(regexp = "^[\\u4e00-\\u9fa5\\ A-Za-z0-9\\-\\_]{2,50}",message = "信号分组名称只能包含汉字,字母,数字,下划线,中横线,长度是2~50")
    private String signalGroup;

    /**
     * 信号属性(AI,DI,Other,DO)
     */
    @Excel(name = "信号属性类型(必填)",width = 20,orderNum = "5",isImportField = "true")
    @NotBlank(message = "信号属性不可以为空")
    @ContainsDataValid(message = "信号属性不正确",values = {"AI","DI","Other","DO"}) //这个是自定义注解 集成hibernate-validator实现
    private String propertyType;

    /**
     * 枚举量(DI时才有)
     */
    @Excel(name = "枚举量",width = 20,orderNum = "6",isImportField = "true")
    @HaveNoBlankValid(message = "枚举量格式不合法,例子(1:a)",value = "enumConstant") //这个是自定义注解 集成hibernate-validator实现
    private String enumConstant;

    /**
     * 精度
     */
    @Excel(name = "精度(必填)",width = 20,orderNum = "7",type = 10,isImportField = "true")
    @NotNull(message = "精度不能为空")
    @Min(value = 0,message = "精度最小值为0")
    @Max(value = 4,message = "精度最大值为4")
    private Integer precisions;

    /**
     * 单位
     */
    @Excel(name = "单位",width = 20,orderNum = "8",isImportField = "true")
    @HaveNoBlankValid(message = "单位长度最大为10",value = "unit") //这个是自定义注解 集成hibernate-validator实现
    private String unit;

    /**
     * 点索引
     */
    @Excel(name = "点索引(必填)",width = 20,orderNum = "9",type = 10,isImportField = "true")
    @NotNull(message = "点索引不能为空")
    @Min(value = 1,message = "点索引最小值为1")
    @Max(value = 9999,message = "点索引最大值为9999")
    private Integer pointIndex;

    /**
     * 类型（采集信号,统计信号）
     */
    @Excel(name = "类型(必填)",width = 20,orderNum = "10",isImportField = "true")
    @NotBlank(message = "信号类型不可以为空")
    @ContainsDataValid(message = "信号类型不正确",values = {"采集信号","统计信号"}) //这个是自定义注解 集成hibernate-validator实现
    private String type;


    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()){
            return false;
        }
        SignalTemplateVO that = (SignalTemplateVO) o;
        return Objects.equals(name, that.name) && Objects.equals(deviceModelName, that.deviceModelName)
                && Objects.equals(metricName,that.metricName)&&Objects.equals(pointIndex,that.pointIndex);
    }

    @Override
    public int hashCode() {
        return Objects.hash(name, deviceModelName,metricName,pointIndex);
    }
    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }

    @Override
    public int getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }
}

2、具体实现类

public HttpResponseTemp<?> importByDeviceModelId(MultipartFile file,HttpServletResponse response) {
        checkFileType(file);

        ExcelImportResult<SignalTemplateVO> result;
        try {
            result = getDataByFile(file);
        }catch (Exception e){
            throw ApiException.wrapMessage(ResultStat.PARAM_ERROR,"Excel 读取失败，请检查模板");
        }
    
        List<SignalErrorVO> errorVOList = getFailData(result);
        if (CollUtil.isNotEmpty(errorVOList)){
            String time = DateUtil.timeStamp2Date2(System.currentTimeMillis(), "yyyyMMddHHmmss");
            String fileName = "信号导入错误信息(" + time + ")";
            try {
                EasyPoiUtil.exportExcel(errorVOList, "信号导入错误信息", "错误信息", SignalErrorVO.class, fileName, true, true, response);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            return ResultStat.PARAM_ERROR.wrap("","导入模板中数据错误");
        }else {
            //成功数据
            handleSuccessData(result);
            return ResultStat.OK.wrap("", "导入数据成功");
        }
    }

    private List<SignalErrorVO> getFailData(ExcelImportResult<SignalTemplateVO> result) {
        List<SignalErrorVO> errorVOList = new ArrayList<>();
        if (ObjectUtil.isNotNull(result)){
            List<SignalTemplateVO> failList = result.getFailList();
            if (CollUtil.isNotEmpty(failList)) {
                failList.stream().filter(Objects::nonNull).forEach(s -> {
                    int line = s.getRowNum() + 1;
                    String msg = "第" + line + "行的错误是：" + s.getErrorMsg();
                    SignalErrorVO signalErrorVO = new SignalErrorVO();
                    signalErrorVO.setLine("第" + line + "行");
                    signalErrorVO.setMsg(msg);
                    errorVOList.add(signalErrorVO);
                });
            }
        }
        return errorVOList;
    }

    private ExcelImportResult<SignalTemplateVO> getDataByFile(MultipartFile file) {
        ExcelImportResult<SignalTemplateVO> result;
        SignalImportVerifyHandler signalImportVerifyHandler = new SignalImportVerifyHandler(deviceSignalMapper,deviceModelMapper);
        try {
            ImportParams importParams = new ImportParams();
            importParams.setTitleRows(1); // 设置标题列占几行
            importParams.setHeadRows(1);  // 设置字段名称占几行 即header
            importParams.setNeedVerify(true);//开启校验
            importParams.setVerifyHandler(signalImportVerifyHandler);// SignalImportVerifyHandler这个类是自己创建的
            importParams.setStartSheetIndex(0);  // 设置从第几张表格开始读取，这里0代表第一张表，默认从第一张表读取
            ExcelImportService excelImportService = new ExcelImportService();
            result = excelImportService.importExcelByIs(file.getInputStream(), SignalTemplateVO.class, importParams, true);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 清除threadLocal 防止内存泄漏
            ThreadLocal<List<SignalTemplateVO>> threadLocal = signalImportVerifyHandler.getThreadLocal();
            if (threadLocal != null) {
                threadLocal.remove();
            }
        }
        return result;
    }

    private void checkFileType(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        if (StrUtil.isNotBlank(fileName)) {
            if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
                throw ApiException.wrapMessage(ResultStat.PARAM_ERROR, "文件格式不对,请上传excel格式文件");
            }
        }
    }

3、自定义信号导入校验类

package com.mye.cloudboxdcim.framework.engine.poi.myeasypoi;

import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.mye.cloudboxdcim.framework.api.mapper.device.DeviceModelMapper;
import com.mye.cloudboxdcim.framework.api.mapper.device.DeviceSignalMapper;
import com.mye.cloudboxdcim.framework.api.pojo.devicemodel.DeviceModel;
import com.mye.cloudboxdcim.framework.api.pojo.devicesignal.DeviceSignal;
import com.mye.cloudboxdcim.framework.api.vo.device.SignalTemplateVO;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.StringJoiner;

/**
 * 自定义信号导入校验类
 */
@Component
public class SignalImportVerifyHandler implements IExcelVerifyHandler<SignalTemplateVO> {

    private final ThreadLocal<List<SignalTemplateVO>> threadLocal = new ThreadLocal<>();


    private final DeviceSignalMapper deviceSignalMapper;

    private final DeviceModelMapper deviceModelMapper;

    public SignalImportVerifyHandler(DeviceSignalMapper deviceSignalMapper,DeviceModelMapper deviceModelMapper){
        this.deviceModelMapper = deviceModelMapper;
        this.deviceSignalMapper = deviceSignalMapper;
    }



    @Override
    public ExcelVerifyHandlerResult verifyHandler(SignalTemplateVO inputEntity) {

        StringJoiner joiner = new StringJoiner(",");
        //根据名称查看数据库中是否存在
        String name = inputEntity.getName();
        if (checkName(name)) {
            joiner.add("信号名称已经存在:" + name);
        }
        //检查设备型号是否存在
        DeviceModel deviceModel = checkModelName(inputEntity.getDeviceModelName());
        if (ObjectUtil.isNull(deviceModel)){
            joiner.add("设备型号不存在:" + inputEntity.getDeviceModelName());
        } else {
            //检查索引值
            Integer deviceModelId = deviceModel.getId();
            Integer pointIndex = inputEntity.getPointIndex();
            if (checkPointIndex(pointIndex,deviceModelId)){
                joiner.add(deviceModel.getName() + "对应的索引值(" + pointIndex + ")已经存在");
            }
        }

        List<SignalTemplateVO> threadLocalVal = threadLocal.get();
        if (CollUtil.isEmpty(threadLocalVal)){
            threadLocalVal = new ArrayList<>();
        }

        threadLocalVal.forEach(e -> {
            if (e.equals(inputEntity)) {
                int lineNumber = e.getRowNum() + 1;
                joiner.add("数据与第" + lineNumber + "行重复");
            }
        });
        // 添加本行数据对象到ThreadLocal中
        threadLocalVal.add(inputEntity);
        threadLocal.set(threadLocalVal);

        if (joiner.length() != 0) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }

    private Boolean checkPointIndex(Integer pointIndex,Integer deviceModelId) {
        LambdaQueryWrapper<DeviceSignal> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(DeviceSignal::getDeviceModelId,deviceModelId).eq(DeviceSignal::getPointIndex,pointIndex);

        DeviceSignal deviceSignal = deviceSignalMapper.selectOne(queryWrapper);
        return ObjectUtil.isNotNull(deviceSignal);

    }

    private Boolean checkName(String name) {
        LambdaQueryWrapper<DeviceSignal> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(DeviceSignal::getName,name);

        DeviceSignal deviceSignal = deviceSignalMapper.selectOne(queryWrapper);
        return ObjectUtil.isNotNull(deviceSignal);
    }

    private DeviceModel checkModelName(String name) {
        LambdaQueryWrapper<DeviceModel> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(DeviceModel::getName,name);

        return deviceModelMapper.selectOne(queryWrapper);
    }

    public ThreadLocal<List<SignalTemplateVO>> getThreadLocal() {
        return threadLocal;
    }
}