文章目录

• easypoi导入案例
• • 一、依赖
  • 二、导出模板
  • • 1、excel模板实体类(同下)
    • 2、具体实现类
    • 3、easypoi工具类中的方法
    • 4、自定义样式类
  • 三、导入校验
  • • 1、excel模板实体类
    • 2、具体实现类
    • 3、自定义信号导入校验类

easypoi导入案例

一、依赖

        cn.afterturneasypoi-spring-boot-starter4.0.0

二、导出模板

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

2、具体实现类

    public void exportTemplate(HttpServletResponse response) {CodingUtils.checkUserIdBefore();List 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> 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> 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 * @return*/public static  List importExcel(String filePath, Integer titleRows, Integer headerRows, Class 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 * @return*/public static  List importExcel(MultipartFile file, Class pojoClass) throws IOException {return importExcel(file, 1, 1, pojoClass);}/*** excel 导入** @param file       excel文件* @param titleRows  标题行* @param headerRows 表头行* @param pojoClass  pojo类型* @param * @return*/public static  List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass) throws IOException {return importExcel(file, titleRows, headerRows, false, pojoClass);}public static   ExcelImportResult importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass,Integer sheetIndex, boolean isVerify, IExcelVerifyHandler handler) throws IOException {try {ImportParams importParams = new ImportParams();importParams.setTitleRows(1); // 设置标题列占几行importParams.setHeadRows(2);  // 设置字段名称占几行 即headerimportParams.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  ExcelImportResult importExcelResult(MultipartFile file, Class pojoClass) throws IOException {try {ImportParams importParams = new ImportParams();importParams.setTitleRows(1); // 设置标题列占几行importParams.setHeadRows(2);  // 设置字段名称占几行 即headerimportParams.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 * @return*/public static  List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class 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 * @return*/public static  List importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class 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);}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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;@Overridepublic 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);}@Overridepublic int hashCode() {return Objects.hash(name, deviceModelName,metricName,pointIndex);}@Overridepublic String getErrorMsg() {return errorMsg;}@Overridepublic void setErrorMsg(String errorMsg) {this.errorMsg = errorMsg;}@Overridepublic int getRowNum() {return rowNum;}@Overridepublic void setRowNum(int rowNum) {this.rowNum = rowNum;}
}

2、具体实现类

public HttpResponseTemp importByDeviceModelId(MultipartFile file,HttpServletResponse response) {checkFileType(file);ExcelImportResult result;try {result = getDataByFile(file);}catch (Exception e){throw ApiException.wrapMessage(ResultStat.PARAM_ERROR,"Excel 读取失败，请检查模板");}List 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 getFailData(ExcelImportResult result) {List errorVOList = new ArrayList<>();if (ObjectUtil.isNotNull(result)){List 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 getDataByFile(MultipartFile file) {ExcelImportResult result;SignalImportVerifyHandler signalImportVerifyHandler = new SignalImportVerifyHandler(deviceSignalMapper,deviceModelMapper);try {ImportParams importParams = new ImportParams();importParams.setTitleRows(1); // 设置标题列占几行importParams.setHeadRows(1);  // 设置字段名称占几行 即headerimportParams.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> 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 {private final ThreadLocal> threadLocal = new ThreadLocal<>();private final DeviceSignalMapper deviceSignalMapper;private final DeviceModelMapper deviceModelMapper;public SignalImportVerifyHandler(DeviceSignalMapper deviceSignalMapper,DeviceModelMapper deviceModelMapper){this.deviceModelMapper = deviceModelMapper;this.deviceSignalMapper = deviceSignalMapper;}@Overridepublic 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 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 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 queryWrapper = new LambdaQueryWrapper<>();queryWrapper.eq(DeviceSignal::getName,name);DeviceSignal deviceSignal = deviceSignalMapper.selectOne(queryWrapper);return ObjectUtil.isNotNull(deviceSignal);}private DeviceModel checkModelName(String name) {LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();queryWrapper.eq(DeviceModel::getName,name);return deviceModelMapper.selectOne(queryWrapper);}public ThreadLocal> getThreadLocal() {return threadLocal;}
}