EasyExcel导入

导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

Controller

import java.text.ParseException;

import org.springframework.web.multipart.MultipartFile;

/**
 * 导入Excel数据
 * @param file file
 * @return result
 * @author liu
 * @throws ParseException ParseException
 */
@PostMapping("importExcelData")
public Result importExcelData (MultipartFile file) throws ParseException {
    service.importExcelData(file);
    return Result.ok();
}

Service

import java.text.ParseException;

import org.springframework.web.multipart.MultipartFile;

/**
 * 导入Excel数据
 * @param file file
 * @author liu
 * @throws ParseException
 */
void importExcelData(MultipartFile file) throws ParseException;

ServiceImpl

import java.text.ParseException;

import org.springframework.web.multipart.MultipartFile;

@Override
public void importExcelData(MultipartFile file) throws ParseException {
	List<UserData> list = null; //导入使用实体类
	try {
	    list = EasyExcel.read(new BufferedInputStream(file.getInputStream()))
	            .head(UserData.class).sheet().doReadSync();
	} catch (IOException e) {
		e.printStackTrace();
	}
	checkImportData(list);

	//将表格list转换为数据库list
	List<ImportData> importData = excelList2DataList(list);
	
	//得到解析后的数据，新增或修改数据库
	mapper.importExcelData(importData);
}

/**
 * 检验导入格式
 * @param list 数据信息
 * @author liu
 */
private void checkImportData(List<UserData> list) {
    //从第2行开始
    int rowNo = 2;
    //遍历校验所有行和列
    for (UserData userData : list) {
    	String id = userData.getId();
    	if (StringUtils.isEmpty(id)) {
    		throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo,"id"));
    	}
		
		String name = userData.getName();
		if (StringUtils.isEmpty(name)) {
			throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo,"姓名"));
		}

		String age = userData.getAge();
		if (StringUtils.isEmpty(age)) {
			throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo,"年龄"));
		}
		
		String sex = userData.getSex();
		if (StringUtils.isEmpty(sex)) {
			throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo,"性别"));
		}
    }
}

/**
 * 数据转换
 * @param excelList excel数据
 * @return 数据库实体类实体信息
 * @throws ParseException ParseExcption
 * @author liu
 */
private List<> excelList2DataList(List<UserData> excelList)
        throws ParseException {
	List<ImportData> importDataList = new ArrayList<>();  //存放转换后的数据
	for (UserData user: excelList) {
		/**
		 * 数据处理 
		 */
		
		ImportData importData = ImportData.builder()
		        .id(user.getId())
		        .name(user.getName())
		        .age(user.getAge())
		        .sex(user.getSex())
		        .build();
		importDataList.add(importData);
	}
	return importDataList;
}
        

UserData 存放Excel数据信息

import lombok.Data;

@Data
public class UserData {
    
    private String id;

	private String name;
	
	private String age;

	private String sex;
}

ImportData 数据库实体类

import lombok.Data;
import lombok.Builder;

@Data
@Builder
public class ImportData {
    
    private String id;

	private String name;
	
	private String age;

	private String sex;
}

GlobalException 统一异常

/** 
 * 统一异常
 */
public class GlobalException extends RuntimeException {

	private static final long serialVersionUID = 1L;

	private CodeMsg cm;

	public GlobalException(CodeMsg cm) {
		super(cm.toString());
		this.cm = cm;
	}

	public CodeMsg getCm() {
		return cm;
	}
}

CodeMsg 错误码

/**
 * 错误码
 * @author liu 
 */
public Class CodeMsg {
	
	private int code;
	private String msg;

	public static CodeMsg SUCCESS = new CodeMsg(200,"成功");
	public static CodeMsg SERVER_ERROR = new CodeMsg(500100,"服务端异常");
	public static CodeMsg BIND_ERROR = new CodeMsg(500101,"参数校验异常：%s");
	public static CodeMsg REQUEST_ILLEGAL = new CodeMsg(500102,"请求非法");
	public static CodeMsg ACCESS_LIMIT_REACHED = new CodeMsg(500103,"访问太频繁！");
	
	public static CodeMsg USER_NOT_LOGIN = new CodeMsg(500200,"用户未登录");
	public static CodeMsg TOKEN_INVALID = new CodeMsg(500201,"token无效");
	public static CodeMsg USERNAME_NOT_EXIST = new CodeMsg(500202,"用户名不存在");
	public static CodeMsg PASSWORD_ERROR = new CodeMsg(500203,"密码错误");
	public static CodeMsg OVER_MAX_USER_IMPORT_LIMIT = new CodeMsg(500204,"一次最多导入%s条");
	public static CodeMsg IMPORT_FIELD_FORMAT_ERROR = new CodeMsg(500205,"第%s行%s格式错误");
	public static CodeMsg IMPORT_FIELD_IS_EAMPTY = new CodeMsg(500206,"第%s行%s不能为空");

	private CodeMsg() {
	}

	private CodeMsg(int code, String msg) {
		this.code = code;
		this.msg = msg;
	}
	
	public int getCode() {
		return code;
	}

	public void setCode(int code) {
		this.code = code;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public CodeMsg fillArgs(Object... args) {
		int code = this.code;
		String message = String.format(this.msg, args);
		return new CodeMsg(code,message);
	}

	@Override
	public String toString() {
		return "CodeMsg [code=" + code + ",msg=" + msg + "]";
	}
}