欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

EasyExcel导入

程序员文章站 2022-08-13 22:53:40
导入依赖 com.alibaba easyexcel 2.1.6Controllerimport java.text.ParseException;import org.springframework....

导入依赖

<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 + "]";
	}
}

本文地址:https://blog.csdn.net/weixin_43887814/article/details/107663752