【SpringBoot+Mybatis-plus+poi】实现校验导入excel（二）
【SpringBoot+Mybatis-plus+poi】实现校验导入excel（二）（附源码）
实现校验导入excel，我打算用两章实现：
第一章根据工厂模式（三个工厂：导入工厂、校验工厂和实现类导入方法工厂）和简单的非空校验实现user.xlsx的导入校验；
第二章根据工厂模式和自定义注解+反射实现user.xlsx的导入校验。
首先看一下excel文档：
这里我删掉了部分单元格数据，作为非空校验的导入模板；
校验成功则根据id查询数据库的数据，如果结果为空则新增，否则更新；
校验失败则给对应单元格标红，并在最后一列打印错误信息。
上代码：
1. 抽象
1.1. 非空校验注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 非空校验
* @author wp
* @date 2022年1月4日
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface CheckIsNull {
/**
* 错误信息
* @return
*/
String message() default "";
/**
* 字段名称
* @return
*/
String name() default "";
/**
* 单元格索引
* @return
*/
int index();
/**
* 是否为表头数据
* @return
*/
boolean btData() default false;
}
1.2. 非空校验注解配置文件
/**
* CheckAnnotationConfig
* @author wp
* @date 2022年1月4日
*/
public class CheckIsNullAnnotationConfig {
private final String name;
private final int index;
// 表头数据
private final boolean btData;
public CheckIsNullAnnotationConfig(CheckIsNull checkIsNull) {
this.name = checkIsNull.name();
this.index = checkIsNull.index();
this.btData = checkIsNull.btData();
}
public String getName() {
return name;
}
public int getIndex() {
return index;
}
public boolean getBtData() {
return btData;
}
}
1.3. 抽象校验方式
import com.example.import1.vo.CheckVo;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
/**
* 抽象校验方式
* @author wp
* @date 2022年1月4日
*/
public abstract class CheckMethod<V> {
public void annotationMethod(V v, CheckVo checkVo) throws IllegalAccessException {
Class<?> aClass = v.getClass();
Field[] declaredFields = aClass.getDeclaredFields();
for (Field declaredField : declaredFields) {
validation(declaredField, v, checkVo);
}
}
private void validation(Field field, V v, CheckVo checkVo) throws IllegalAccessException {
// 获取每个字段上的所有注解
Annotation[] annotations = field.getAnnotations();
field.setAccessible(true);
for (Annotation annotation : annotations) {
AnnotationType<V> annotationType = new AnnotationType<>();
annotationType.choiceType(annotation, v, field, checkVo);
}
}
}
1.4. 实现抽象校验方式
/**
* CheckMethodOutput
*
* @author wp
* @date 2022年1月4日
*/
public class CheckMethodOutput<V> extends CheckMethod<V>{
}
1.5. 抽象校验类型工厂
import com.example.import1.vo.CheckVo;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
/**
* 抽象校验类型工厂
* @author wp
* @date 2022年1月4日
*/
public interface ICheckAnnotationFactory<V> {
void checkType(Annotation annotation, Field field, V v, CheckVo checkVo) throws IllegalAccessException;
}
1.6. 校验类型
import com.example.import1.vo.CheckVo;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
/**
* 校验类型
* @author wp
* @date 2022年1月4日
*/
public class AnnotationType<V>{
public void choiceType(Annotation annotation, V v, Field field, CheckVo checkVo) throws IllegalAccessException {
// 非空校验
ICheckAnnotationFactory<V> isNullCheckType = new IsNullCheckType<>();
if (annotation instanceof CheckIsNull) {
isNullCheckType.checkType(annotation, field, v, checkVo);
}
}
}
1.7. 非空校验
import com.example.import1.util.CheckoutUtils;
import com.example.import1.vo.CheckVo;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
/**
* 非空校验
* @author wp
* @date 2022年1月4日
* absCheck 预留，私有校验调用
*/
public class IsNullCheckType<V> implements ICheckAnnotationFactory<V> {
@Override
public void checkType(Annotation annotation, Field field, V v, CheckVo checkVo) throws IllegalAccessException {
CheckIsNull checkIsNull = (CheckIsNull) annotation;
CheckIsNullAnnotationConfig checkIsNullAnnotationConfig = new CheckIsNullAnnotationConfig(checkIsNull);
Object o = field.get(v);
String s = null != o ? o.toString() : "";
CheckoutUtils.isNull(checkIsNullAnnotationConfig.getName(), checkVo.getErrmsg(),
checkVo.getRow().getCell(checkIsNullAnnotationConfig.getIndex()),
s, checkVo.getCellStyle(), checkVo.getRow(), checkIsNullAnnotationConfig.getIndex());
}
}
2. 实体（添加注解）
import com.baomidou.mybatisplus.annotation.TableName;
import com.example.import1.dispose.annotation.CheckIsNull;
import lombok.Data;
import lombok.experimental.Accessors;
@Data
@Accessors(chain = true)
@TableName("t_user")
public class TUser {
@CheckIsNull(name = "id", index = 0)
private Integer id;
@CheckIsNull(name = "姓名", index = 1)
private String name;
@CheckIsNull(name = "年龄", index = 2)
private String age;
@CheckIsNull(name = "性别", index = 3)
private String sex;
@CheckIsNull(name = "地址", index = 4)
private String address;
@CheckIsNull(name = "联系方式", index = 5)
private String contactInformation;
}
3. 抽象导入（内含注解反射调用）
import com.example.import1.domain.TUser;
import com.example.import1.vo.CheckVo;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* 抽象导入
* @author wp
*/
public interface IImport<T> {
/**
* 校验
* @param errorMsg
* @param wb
* @param s
* @param allNums
* @param inputStream
* @throws IOException
*/
Integer checkResult(StringBuilder errorMsg, XSSFWorkbook wb, Sheet s, int allNums, InputStream inputStream, List<TUser> users)throws Exception;
/**
* 导入数据
* @param s
* @param allNums
*/
Integer checkData(Sheet s, int allNums, CheckVo checkVo, List<String> dataList,
StringBuilder errorString, int flag, List<TUser> users) throws IllegalAccessException;
/**
* 导入数据处理
* @param s
* @param i
*/
Integer create(Sheet s, int i, CheckVo checkVo, List<String> dataList, StringBuilder errorString, int flag, List<TUser> users) throws IllegalAccessException;
/**
* 校验通过插入/更新数据
* @param zhqJtsyjcs
*/
void createData(List<T> zhqJtsyjcs);
}
3.1. 生产导入方式
import cn.hutool.core.bean.BeanUtil;
import com.example.import1.dispose.IImport;
import com.example.import1.dispose.annotation.CheckMethod;
import com.example.import1.dispose.annotation.CheckMethodOutput;
import com.example.import1.domain.TUser;
import com.example.import1.mapper.TUserMapper;
import com.example.import1.vo.CheckVo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.transaction.annotation.Transactional;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* user导入
* @author wp
*/
public class UserImport implements IImport<TUser> {
private final DataFormatter DATAFORMATTER = new DataFormatter();
//excel起始校验行下标
private static final int START_INDEX = 1;
//excel错误信息下标
private static final int ERROR_INDEX = 6;
private final TUserMapper userMapper;
public UserImport(TUserMapper userMapper) {
this.userMapper = userMapper;
}
@Override
public Integer checkResult(StringBuilder errorMsg, XSSFWorkbook wb, Sheet s, int allNums, InputStream inputStream, List<TUser> users) throws Exception {
// 标识为0，文件校验没有出现错误，执行插入；否则导出错误信息
int flag = 0;
//获取默认单元格样式（在第一行创建一个空单元格获取默认样式）
XSSFCellStyle orgSty = (XSSFCellStyle) s.getRow(0).createCell(100).getCellStyle();
//设置单元格为文本格式
XSSFDataFormat format = wb.createDataFormat();
orgSty.setDataFormat(format.getFormat("@"));
//错误标红单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyle.setDataFormat(format.getFormat("@"));
// 重复数据缓存
List<String> dataList = new ArrayList<>();
CheckVo checkVo = new CheckVo();
checkVo.setCellStyle(cellStyle);
return checkData(s, allNums, checkVo, dataList, errorMsg, flag, users);
}
@Override
public Integer checkData(Sheet s, int allNums, CheckVo checkVo, List<String> dataList,
StringBuilder errorString, int flag, List<TUser> users) throws IllegalAccessException {
for (int i = START_INDEX; i < allNums; i++) {
Row r = s.getRow(i);
if (r == null) {
continue;
}
// 导入用户信息
flag = create(s, i, checkVo, dataList, errorString, flag, users);
}
return flag;
}
@Transactional
public Integer create(Sheet s, int i, CheckVo checkVo, List<String> dataList, StringBuilder errorString, int flag, List<TUser> users) throws IllegalAccessException {
Row r = s.getRow(i);
TUser user = new TUser();
CheckVo checkVo1 = BeanUtil.toBean(checkVo, CheckVo.class);
//定义错误信息msg
StringBuilder errorMsg = new StringBuilder();
checkVo1.setErrmsg(errorMsg);
//获取错误信息清空
Cell orgErrCell = r.getCell(ERROR_INDEX);
if (orgErrCell != null) {
orgErrCell.setCellValue("");
}
TUser userCondition = new TUser();
checkVo1.setRow(r);
// id
String id = DATAFORMATTER.formatCellValue(r.getCell(0));
if (StringUtils.isNotBlank(id)) {
int idInt = Integer.parseInt(id);
userCondition = userMapper.selectById(id);
user.setId(idInt);
}
// 姓名
String name = DATAFORMATTER.formatCellValue(r.getCell(1));
user.setName(name);
// 年龄
String age = DATAFORMATTER.formatCellValue(r.getCell(2));
user.setAge(age);
// 性别
String sex = DATAFORMATTER.formatCellValue(r.getCell(3));
user.setSex(sex);
// 地址
String address = DATAFORMATTER.formatCellValue(r.getCell(4));
user.setAddress(address);
// 联系方式
String contactInformation = DATAFORMATTER.formatCellValue(r.getCell(5));
user.setContactInformation(contactInformation);
CheckMethod<TUser> checkMethod = new CheckMethodOutput<>();
checkMethod.annotationMethod(user, checkVo1);
errorMsg = checkVo1.getErrmsg();
// errorMsg.append(errMag);
if(orgErrCell==null) {
orgErrCell= r.createCell(ERROR_INDEX);
}
orgErrCell.setCellValue(errorMsg.toString());
errorString.append(errorMsg);
// 如果flag
if (StringUtils.isNotBlank(errorString.toString())) {
flag = 1;
}
if (flag == 0 && errorString.length() <= 0 || errorString.toString().equals("")) {
users.add(user);
}else {
users.clear();
}
return flag;
}
@Override
public void createData(List<TUser> users) {
for (TUser user : users) {
TUser userCondition = userMapper.selectById(user.getId());
if (null != userCondition) {
userMapper.updateById(user);
}else {
userMapper.insert(user);
}
}
}
}
4. dao层
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.import1.domain.TUser;
import org.apache.ibatis.annotations.Mapper;
/**
* <p>
* Mapper 接口
* </p>
*
* @author wp
* @since 2022-01-04
*/
@Mapper
public interface TUserMapper extends BaseMapper<TUser> {
}
5. service层（内含实现类工厂）
5.1. api
5.1.1. 实现类工厂
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
/**
* 禽流感环境病原学监测信息Service接口
*
* @author wp
* @date 2021-12-17
*/
public interface ICombineService {
/**
* 通用导入
*/
XSSFWorkbook importExcel(InputStream inputStream) throws Exception;
}
5.1.2. service
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.import1.domain.TUser;
/**
* <p>
* 服务类
* </p>
*
* @author wp
* @since 2022-01-04
*/
public interface IUserService extends IService<TUser> {
}
5.2. 实现类导入方法（实现service）
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.import1.dispose.IImport;
import com.example.import1.dispose.input.UserImport;
import com.example.import1.mapper.TUserMapper;
import com.example.import1.domain.TUser;
import com.example.import1.service.ICombineService;
import com.example.import1.service.IUserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* <p>
* 服务实现类
* </p>
*
* @author wp
* @since 2022-01-04
*/
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<TUserMapper, TUser> implements IUserService, ICombineService {
@Autowired
private TUserMapper userMapper;
@Override
@Transactional
public XSSFWorkbook importExcel(InputStream inputStream) throws Exception {
StringBuilder errorString = new StringBuilder();
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
Sheet s = wb.getSheetAt(0);
int allNums = s.getPhysicalNumberOfRows();
IImport<TUser> check = new UserImport(userMapper);
List<TUser> users = new ArrayList<>();
//校验
Integer flag = check.checkResult(errorString, wb, s, allNums, inputStream, users);
// check.createData(wb, s, allNums);
if (flag == 0) {
check.createData(users);
wb = null;
}
return wb;
}
}
6. 工具类
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
public class CheckoutUtils {
/**
* 非空校验
* @param zhName
* @param errorMsg
* @param cell
* @param cellStyle
* @return
*/
public static boolean isNull(String zhName, StringBuilder errorMsg, Cell cell, String cellString, XSSFCellStyle cellStyle, Row r, int i){
//如果为空则增加错误信息
if(StringUtils.isBlank(cellString)) {
errorMsg.append(zhName).append("必填; ");
setCellStyle(r, i, cell, cellStyle);
return false;
}
CellStyle style = cell.getCellStyle();
if (IndexedColors.RED.getIndex() == style.getFillBackgroundColor()) {
cell.setCellStyle(null);
}
return true;
}
private static void setCellStyle(Row r, int i, Cell cell, XSSFCellStyle cellStyle) {
if (null == cell) {
cell = r.createCell(i);
}
cell.setCellStyle(cellStyle);
}
}
7. 实体Vo
import lombok.Data;
import lombok.experimental.Accessors;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.io.Serializable;
import java.util.List;
@Data
@Accessors(chain = true)
public class CheckVo implements Serializable {
private static final long serialVersionUID = -1494924043115338235L;
/** 错误信息 */
private StringBuilder errmsg;
/** 行数 */
private Row row;
/** 对应单元格 */
private Cell cell;
/** 对应单元格数据 */
private String s;
/** 单元格样式 */
private XSSFCellStyle cellStyle;
}
8. Controller层
import com.example.import1.domain.TUser;
import com.example.import1.service.ICombineService;
import com.example.import1.service.IUserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/**
* <p>
* 前端控制器
* </p>
*
* @author wp
* @since 2022-01-04
*/
@RestController
@RequestMapping("/user")
@Slf4j
public class UserController {
@Autowired
private IUserService userService;
@Autowired
private ICombineService combineService;
/**
* 用户查询
* @return
*/
@GetMapping("/query/user")
public ResponseEntity<List<TUser>> queryUser(){
return ResponseEntity.ok(userService.list());
}
/**
* 用户导入
*/
@PostMapping("/import/user")
public ResponseEntity<Boolean> importExcel(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception {
XSSFWorkbook wb = combineService.importExcel(file.getInputStream());
if (wb!=null){
ServletOutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
wb.close();
outputStream.close();
}
return ResponseEntity.ok(true);
}
}
9. 项目层级
其余和第二章一样，不变
10. 导入结果（校验失败）（注意postman导入失败时要改后缀zip->xlsx）
11. 导入结果（校验成功，数据库对应id数据已改正）
根据自定义注解导入校验，代码灵活度非常高，如果要进行不同维度的校验，只需要添加枚举类，内部添加校验方式；或者直接创建校验方式注解对象，不同校验方式实现不同的校验，进行判断就可以完成。
承接上一章多个类型的excel文件校验，只需要添加特定的实体，上面配置注解即可达到目的。
纯手打，技术有限，请各位大佬批评指正，谢谢！
项目已经提交在git上
导入校验项目git地址：
https://gitee.com/table-tennis-king/import_test.git
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
