excel导入，使用注解对字段进行逻辑判断（字段是否为空，数据结构等）条件

embedded/2024/12/29 13:00:58/

1.创建一个工具类

package com.yj.utils;import cn.hutool.core.io.FileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.yj.annotation.ImportFieldCheckAnnotation;
import com.yj.exception.ServiceException;
import com.yj.service.SysDictDetailService;
import com.yj.utils.spring.SpringUtils;
import com.yj.vo.SysDictDetailVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.jetbrains.annotations.NotNull;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
import java.util.UUID;
import java.util.regex.Pattern;import static com.yj.constant.ErrorCodeConstants.*;
import static com.yj.utils.ServiceExceptionUtil.exception;/*** @Author: hjc* @Date: 2024/5/25 18:59* @Description:**/
@Slf4j
@Service
public class ExcelUtils<T> {/*** 校验表头是否正确** @param wb* @param columnName* @return* @throws Exception*/public static void verificationExcelHeadLine(int rowNum, Workbook wb, String[] columnName) {try {Sheet sheet = wb.getSheetAt(0);Row row = sheet.getRow(rowNum);if (row != null && row.getLastCellNum() == columnName.length) {int lastCellNum = row.getLastCellNum();for (int idx = 0; idx <lastCellNum ; idx++) {String value = row.getCell(idx).getStringCellValue();if (StringUtils.isBlank(value) || !value.trim().startsWith(columnName[idx])) {throw  exception(FILE_HEAD_ERROR);}}}else{throw  exception(FILE_HEAD_ERROR);}} catch (ServiceException e){throw e;} catch (Exception ex) {log.info("表头验证失败=={}",ex);throw  exception(FILE_HEAD_ERROR);}}/*** 保存失败文件* @param errorInfoList* @param aclass* @return*/public  String saveFailResultFile(List<T> errorInfoList,Class aclass) {ByteArrayOutputStream os = new ByteArrayOutputStream();String failResultFileName = "failresult" + UUID.randomUUID().toString().replace("-", "") + ".xlsx";ExcelWriter excelWriter = EasyExcel.write(os, aclass).build();WriteSheet sheet = EasyExcel.writerSheet(0, "Sheet1").build();excelWriter.write(errorInfoList, sheet);excelWriter.finish();InputStream is = new ByteArrayInputStream(os.toByteArray());String tempPath=System.getProperty("user.dir")+File.separator+"temp"+ File.separator;File dir = new File(tempPath);if (!dir.exists()) {dir.mkdirs();}FileUtil.writeFromStream(is, tempPath + failResultFileName);return failResultFileName;}/*** 文件类型校验* @param file* @return*/@NotNullpublic static String fileTypeCheck(MultipartFile file) {if (file == null) {log.error("上传文件为空");throw exception(FILE_NOT_NULL);}//获取原始文件名String fileName = file.getOriginalFilename();//文件后缀名校验if (!(fileName.endsWith("xls") || fileName.endsWith("xlsx"))) {log.error("文件上传格式错误");exception(FILE_TYPE_ERROR);}return fileName;}/*** 设置响应结果** @param response    响应结果对象* @param rawFileName 文件名* @throws UnsupportedEncodingException 不支持编码异常*/public static  void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {//设置内容类型response.setContentType("application/vnd.vnd.ms-excel");//设置编码格式response.setCharacterEncoding("utf-8");//设置导出文件名称（避免乱码）String fileName = URLEncoder.encode(rawFileName.concat(".xlsx"), "UTF-8");// 设置响应头response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);}/*** 字段校验** @param fields* @param t*/public   List<String>  importCheck(Field[] fields, T t, List<String> errorTips) {try {for (Field field : fields) {field.setAccessible(true);Object obj = field.get(t);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);ImportFieldCheckAnnotation classFieldTypeAnnotation = field.getAnnotation(ImportFieldCheckAnnotation.class);String[] chineseName = annotation.value();if (ObjectUtils.isNull(obj) &&!ObjectUtils.isNull(classFieldTypeAnnotation)&&!classFieldTypeAnnotation.permitNull()) {errorTips.add(chineseName[0] + "不能为空");continue;}if(!ObjectUtils.isNull(obj)&&!ObjectUtils.isNull(classFieldTypeAnnotation)&&classFieldTypeAnnotation.isNumber()){if (!this.isNumeric((String) obj)) {errorTips.add(chineseName[0]+"字段为整型");}else{String str= (String) obj;int min = classFieldTypeAnnotation.min();int max = classFieldTypeAnnotation.max();if(str.length()>8||Integer.valueOf(str)>max||Integer.valueOf(str)<min){errorTips.add(chineseName[0] + "数据范围"+min+"~"+max);}}}if(!ObjectUtils.isNull(obj)&&!ObjectUtils.isNull(classFieldTypeAnnotation)&&classFieldTypeAnnotation.isDouble()){if (!this.doubleCheck((String) obj)) {if(StringUtils.isNotBlank(classFieldTypeAnnotation.msg())){errorTips.add(classFieldTypeAnnotation.msg());}else {errorTips.add(chineseName[0] + "字段格式错误");}}else{String str= (String) obj;int min = classFieldTypeAnnotation.min();int max = classFieldTypeAnnotation.max();if(str.length()>12||Double.valueOf(str)>max||Double.valueOf(str)<min){errorTips.add(chineseName[0] + "数据范围"+min+"~"+max);}}}if(!ObjectUtils.isNull(classFieldTypeAnnotation)&&!ObjectUtils.isNull(obj)){boolean isTtr = obj instanceof String;if(isTtr) {String str = (String) obj;if (classFieldTypeAnnotation.length()!=0&&str.length() > classFieldTypeAnnotation.length()) {if(StringUtils.isNotBlank(classFieldTypeAnnotation.msg())){errorTips.add(classFieldTypeAnnotation.msg());}else {errorTips.add(chineseName[0] + "字符长度过长");}}}}//校验下拉选的值if(!ObjectUtils.isNull(classFieldTypeAnnotation)&&!ObjectUtils.isNull(obj)&&StringUtils.isNotBlank(classFieldTypeAnnotation.dictCode())){SysDictDetailService sysDictDetailService = SpringUtils.getBean(SysDictDetailService.class);SysDictDetailVO sysDictDetailVO = new SysDictDetailVO();sysDictDetailVO.setDictCode(classFieldTypeAnnotation.dictCode());sysDictDetailVO.setDictLabel((String) obj);List<SysDictDetailVO> list = sysDictDetailService.list(sysDictDetailVO);if(CollectionUtils.isEmpty(list)){errorTips.add(chineseName[0] + "不匹配");}}//校验正则if(!ObjectUtils.isNull(classFieldTypeAnnotation)&&!ObjectUtils.isNull(obj)&&StringUtils.isNotBlank(classFieldTypeAnnotation.regex())){boolean matches = this.regexCheck((String) obj,classFieldTypeAnnotation.regex());if(!matches){if(StringUtils.isNotBlank(classFieldTypeAnnotation.msg())){errorTips.add(classFieldTypeAnnotation.msg());}else {errorTips.add(chineseName[0] + "字段格式错误");}}}}} catch (IllegalAccessException e) {log.info("导入失败",e);errorTips.add("导入失败");}return errorTips;}public boolean isNumeric(String str) {return str.matches("\\d+");}/*** 正则表达式校验* @param ipAddress* @param regex* @return*/public  boolean regexCheck(String ipAddress,String regex) {if ((ipAddress != null) && (!ipAddress.isEmpty())) {return Pattern.matches(regex, ipAddress);}return false;}public boolean doubleCheck(String str) {return str.matches("\\d{1,6}(\\.?)(\\d{0,4})");}}

2.自定义一个注解

package com.yj.annotation;import java.lang.annotation.*;/*** 字段注解*   @author hjc*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ImportFieldCheckAnnotation {/*** 数字类型*/boolean isNumber() default false;boolean isDouble() default false;/*** 字符长度* @return*/int length() default 0;/*** 最大值* @return*/int max() default 0;/*** 最小值* @return*/int min() default 0;/*** 字段是否可以为空* @return*/boolean permitNull() default false;/*** 字典值校验* @return*/String dictCode() default "";/*** 正则表达式校验*/String regex() default "";/*** 提示*/String msg() default "";}

3.创建实体类，使用注解

@Data
public class Cloud {@ApiModelProperty(value = "区域")@ExcelProperty(value = "区域", index = 0)@ImportFieldCheckAnnotation(length = 100,dictCode = "region")private String regionName;@ApiModelProperty(value = "业务")@ExcelProperty(value = "业务", index = 1)@ImportFieldCheckAnnotation(length = 100)private String institution;@ExcelProperty(value = "名称", index = 2)@ApiModelProperty(value = "名称")@ImportFieldCheckAnnotation(length = 100)private String Name;@ExcelProperty(value = "实例", index = 3)@ApiModelProperty(value = "实例")@ImportFieldCheckAnnotation(permitNull = true,length = 100)private String instance@ExcelProperty(value = "天数", index = 4)@ApiModelProperty(value = "天数")@ImportFieldCheckAnnotation(permitNull = true,isNumber = true,max = 10000)private String testDays;}

4.Controller

 public ResultVO<ExcelReturnVO> upload(MultipartFile file) {ExcelReturnVO excelReturnVO = new ExcelReturnVO();String fileName = ExcelUtils.fileTypeCheck(file);Workbook wb = null;String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);try {InputStream fin = file.getInputStream();if ("xls".equals(suffix)) {wb = new HSSFWorkbook(fin);} else if ("xlsx".equals(suffix)) {wb = new XSSFWorkbook(fin);}String[] columnName ={"区域", "业务", "名称", "时间", "实例", "天数"};
//判断导入表头是否正确ExcelUtils.verificationExcelHeadLine(1, wb, columnName);
//读取数据转换成实体类列表
//EasyExcel.read(file.getInputStream(),Cloud.class)
//从第二行开始读
//.headRowNumber(2)
//第一个表
//.sheet(0).doRead();

2.效验操作

private void checkData(Cloud inputEntity, List<CloudError> errorInfoList) {List<String> errorTips = new ArrayList<>();释放时间格式错误 格式：YYYY/MM/DD ");}//数据校验
//获取Cloud类 的所有属性Field[] declaredFields = inputEntity.getClass().getDeclaredFields();ExcelUtils<Cloud > excelUtils=new ExcelUtils();
//使用自定义的方法对所有属性进行解析效验excelUtils.importCheck(declaredFields, inputEntity, errorTips);


http://www.ppmy.cn/embedded/149379.html

相关文章

攻防世界web第三题file_include

攻防世界web第三题file_include

<?php highlight_file(__FILE__);include("./check.php");if(isset($_GET[filename])){$filename $_GET[filename];include($filename);} ?>这是题目 惯例&#xff1a; 代码审查&#xff1a; 1.可以看到include(“./check.php”);猜测是同级目录下有一个ch…
阅读更多...
【数据库】PostgreSQL（持续更新）

【数据库】PostgreSQL（持续更新）

目录 K8S 部署基本使用高级特性 K8S 部署 # pg.yaml --- apiVersion: v1 kind: PersistentVolume metadata:name: tv-postgres-pvnamespace: locallabels:storage: tv-postgres-pv spec:accessModes:- ReadWriteOncecapacity:storage: 50Gi # 按需修改&#xff0c;需要保持与…
阅读更多...
【每日学点鸿蒙知识】推送指定页面参数、Devtools 做Web调试、图片的加载与压缩、三方so 打进hap包、Url获取参数

【每日学点鸿蒙知识】推送指定页面参数、Devtools 做Web调试、图片的加载与压缩、三方so 打进hap包、Url获取参数

1、HarmonyOS 定向推送指定页面怎么推送&#xff0c;带参数&#xff1f; 可以参考文档&#xff1a;https://developer.huawei.com/consumer/cn/doc/harmonyos-references-V5/js-apis-router-V5 2、HarmonyOS Devtools 做Web调试&#xff1f; 参照以下链接,每一步都不可缺少&…
阅读更多...
webrtc-internals调试工具

webrtc-internals调试工具

Google 的 Chrome&#xff08;87 或更高版本&#xff09;WebRTC 内部工具是一套内置于 Chrome 浏览器中的调试工具; webrtc-internals 能够查看有关视频和音频轨道、使用的编解码器以及流的一般质量的详细信息。这些知识对于解决音频和视频质量差的问题非常有帮助。 webrtc-int…
阅读更多...
基于Oauth2的SSO单点登录---后端

基于Oauth2的SSO单点登录---后端

【springbootvue项目&#xff08;十三&#xff09;】 Springboot整合Spring SecurityJWT 【springbootvue项目&#xff08;十四&#xff09;】基于Oauth2的SSO单点登录&#xff08;一&#xff09;整体流程介绍 【springbootvue项目&#xff08;十五&#xff09;】基于Oauth2的…
阅读更多...
安卓APP-HTTPS抓包Frida Hook教程

安卓APP-HTTPS抓包Frida Hook教程

文章目录 安卓APP-HTTPS抓包Frida Hook教程本机环境环境搭建bluestacks 环境搭建frida环境搭建抓包流程抓包及https解密方法流量分析查看包名安卓APP-HTTPS抓包Frida Hook教程 本机环境 windows 11bluestacks 5.11.56.1003 p64adb 31.0.2Root Checker 6.5.3wireshark 4.0.5fri…
阅读更多...
使用gdb调试C程序

使用gdb调试C程序

1.程序样例 test.c: #include <stdio.h>int main () { unsigned long long int n, sum; n 1; sum 0; while (n < 100) { sum sum n; n n 1; } return 0; }2.编译程序 gcc -g test.c -o test3.运行调试 gdb test --silent 4.调…
阅读更多...
基于GEE云计算、多源遥感、高光谱遥感技术蓝碳储量估算；红树林植被指数计算及提取

基于GEE云计算、多源遥感、高光谱遥感技术蓝碳储量估算；红树林植被指数计算及提取

海洋是地球上最大的“碳库”,“蓝碳”即海洋活动以及海洋生物&#xff08;特别是红树林、盐沼和海草&#xff09;能够吸收大气中的二氧化碳&#xff0c;并将其固定、储存在海洋中的过程、活动和机制。而维持与提升我国海岸带蓝碳潜力是缓解气候变化的低成本、高效益的方案&…
阅读更多...
最新文章

Copyright @ 2022~2023