Spring Boot整合EasyExcel导入导出
ps:2021，对本篇博客进行一个更新，重新写了一个demo，并对easyExcel封装了一个工具类，以下是demo的完整内容：
一、项目结构：
1、使用jar包 pom.xml:
<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.4.2</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>demo</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.58</version></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.8.1</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>javax.validation</groupId><artifactId>validation-api</artifactId><version>2.0.1.Final</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins></build></project>
2、配置文件 application.yml:
spring:datasource:username: rootpassword: 123456url: jdbc:mysql://localhost:3306/employees?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTCdriver-class-name: com.mysql.cj.jdbc.Driver
3、工具包 utils**：**
分为valida和excel以及一个断言工具类；valida包下用于校验对象属性是否满足要求，不了解可以去了解hibernate-validator。excel包下是用于对excel进行解析。AssertUtil断言工具类，用于对判断当前是否该抛异常。
二、valida 下所有类
package com.example.demo.utils.valida;/*** 新增数据 Group** @author Lucifer*/public interface AddGroup {}package com.example.demo.utils.valida;import javax.validation.GroupSequence;/*** 定义校验顺序（组顺序），如果AddGroup组失败，则UpdateGroup组不会再校验** @author Lucifer*/@GroupSequence({AddGroup.class,UpdateGroup.class})public interface Group {}package com.example.demo.utils.valida;/*** 更新数据 Group* @author Lucifer*/public interface UpdateGroup {}package com.example.demo.utils.valida;import com.alibaba.excel.annotation.ExcelProperty;import javax.validation.ConstraintViolation;import javax.validation.Validation;import javax.validation.Validator;import javax.validation.groups.Default;import java.lang.reflect.Field;import java.util.Set;/*** hibernate-validator校验工具类** @author Lucifer*/public class ValidationUtil {private static Validator validator;static {validator = Validation.buildDefaultValidatorFactory().getValidator();}/*** 校验对象* 例: ValidationUtil.validateEntity(user,AddGroup.class),* 那么会对AddGroup分组下的属性和Default Group（ps:未加group属性，都是Default Group）下属性进行校验** @param object 待校验对象* @param groups 待校验的组* @throws RuntimeException 校验不通过，则报ServerException异常*/public static void validateEntity(Object object, Class<?>... groups)throws RuntimeException {Set<ConstraintViolation<Object>> constraintViolations = validator.validate(object, groups);if (!constraintViolations.isEmpty()) {ConstraintViolation<Object> constraint = constraintViolations.iterator().next();throw new RuntimeException(constraint.getMessage());}}/*** 校验默认分组，并将异常拼接起来** @param obj* @param <T>* @return* @throws NoSuchFieldException*/public static <T> String validateEntity(T obj) throws NoSuchFieldException {StringBuilder result = new StringBuilder();Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);if (set != null && !set.isEmpty()) {for (ConstraintViolation<T> cv : set) {Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);//拼接错误信息，包含当前出错数据的标题名字+错误信息result.append(annotation.value()[0] + cv.getMessage()).append(";");}}return result.toString();}}
三、excel 下所有类
package com.example.demo.utils.excel;import com.alibaba.excel.EasyExcel;import lombok.extern.slf4j.Slf4j;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.InputStream;import java.net.URLEncoder;import java.util.List;import java.util.function.Function;@Slf4jpublic class ExcelUtil {/*** 写excel** @param response* @param list* @param fileName* @param sheetName* @param clazz* @throws Exception*/public static void writeExcel(HttpServletResponse response, List<?> list, String fileName, String sheetName, Class clazz) {try {response.setCharacterEncoding("utf8");response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);} catch (IOException e) {e.printStackTrace();}}/*** 读取excel,并返回错误错误行号及原因** @param file excel文件* @param tClass excel读取的实体类* @param function function接口* @param tClass excelParams BaseExcelListener监听器所需参数* @param <T>*/public static <T extends BaseExcelModel> List<ErrorModel> readExcel(MultipartFile file, Class<T> tClass, Function<List<T>,List<ErrorModel>> function, ExcelParams excelParams) {//todo 做文件格式校验以及文件大小校验BaseExcelListener<T> listener = new BaseExcelListener<T>(function, excelParams);try (InputStream inputStream = file.getInputStream()) {EasyExcel.read(inputStream, tClass, listener).sheet().doRead();} catch (IOException e) {log.error("readExcel error:{}", e);throw new RuntimeException("readExcel error");}return listener.getErrorList();}/*** 同上* @param file* @param tClass* @param function* @param <T>* @return*/public static <T extends BaseExcelModel> List<ErrorModel> readExcel(MultipartFile file, Class<T> tClass, Function<List<T>,List<ErrorModel>> function) {return readExcel(file, tClass, function, null);}}package com.example.demo.utils.excel;import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;/*** excel监听器构造器所需参数对象** @author Lucifer*/@Data@AllArgsConstructor@NoArgsConstructor@Builderpublic class ExcelParams {/*** 读取时抛出异常是否继续读取,true:跳过继续读取, false:停止读取*/private Boolean continueAfterThrowing;/*** 每隔N条存执行一次方法*/private Integer batchCount;/*** 是否在解析excel时，通过ValidationUtil校验数据*/private Boolean validateBeforeAddData;}package com.example.demo.utils.excel;import com.alibaba.excel.annotation.ExcelProperty;import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;/*** 错误信息对象** @author lucifer*/@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class ErrorModel {@ExcelProperty("行号")private Integer rowNo;@ExcelProperty("原始数据")private String resourceBody;@ExcelProperty("错误原因")private String errorMessage;}package com.example.demo.utils.excel;import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;import com.baomidou.mybatisplus.annotation.TableField;import lombok.Data;/*** //TODO** @author: zhang_zhi_kang*/@Data@ExcelIgnoreUnannotatedpublic class BaseExcelModel {/*** 行号*/@TableField(exist = false)private Integer rowNo;}package com.example.demo.utils.excel;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.alibaba.fastjson.JSON;import com.example.demo.utils.AssertUtil;import com.example.demo.utils.valida.ValidationUtil;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.StringUtils;import java.util.ArrayList;import java.util.List;import java.util.Objects;import java.util.function.Function;/*** excel监听器** @author: Lucifer*/@Slf4jpublic class BaseExcelListener<Model extends BaseExcelModel> extends AnalysisEventListener<Model> {/*** data*/private List<Model> dataList = new ArrayList<>();/*** 失败执行data*/private List<ErrorModel> errorList = new ArrayList<>();/*** <p>读取时抛出异常是否继续读取.</p>* <p>true:跳过继续读取 , false:停止读取 , 默认true .</p>*/private boolean continueAfterThrowing = true;/*** 每隔N条存执行一次方法,* 如果是入库操作,可使用默认的3000条,然后清理list,方便内存回收*/private Integer batchCount = 3_000;/*** 该方法用于对读取excel过程中对每一行的数据进行校验操作,* 如果不需要对每行数据进行校验,则直接返回false即可.*/private boolean validateBeforeAddData = true;private Function<List<Model>, List<ErrorModel>> function;public BaseExcelListener(Function<List<Model>, List<ErrorModel>> function, ExcelParams excelParams) {AssertUtil.isNull(function, "param(function) must not null");this.function = function;if (!Objects.isNull(excelParams)) {this.continueAfterThrowing = excelParams.getContinueAfterThrowing();this.batchCount = excelParams.getBatchCount();this.validateBeforeAddData = excelParams.getValidateBeforeAddData();}}/*** 这个每一条数据解析都会来调用** @param data* @param context*/@Overridepublic void invoke(Model data, AnalysisContext context) {String json = JSON.toJSONString(data);log.info("解析到一条数据:{}", json);//设置行号（用于赋值在doService中返回的ErrorModel）data.setRowNo(getCurrentRowIndex(context));if (validateBeforeAddData) {validateBeforeAddData(data, context, json);} else {dataList.add(data);}// 达到batchCount了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (dataList.size() >= batchCount) {doService();// 存储完成清理 dataListdataList.clear();}}/*** 校验data** @param data* @param context* @param json*/private void validateBeforeAddData(Model data, AnalysisContext context, String json) {String errorMessage;try {errorMessage = ValidationUtil.validateEntity(data);} catch (NoSuchFieldException e) {log.error("BaseExcelListener invoke,error:{}:", e);errorMessage = "该类没有指定名称的字段,error:" + e.getMessage();}if (StringUtils.isNotBlank(errorMessage)) {errorList.add(new ErrorModel(getCurrentRowIndex(context), json, errorMessage));} else {dataList.add(data);}}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {doService();// 存储完成清理 listdataList.clear();}/*** 保存数据库* 需要由调用者自己实现*/public void doService() {log.info("数据库保存开始.......");//这里可以拿到你处理业务时，返回的行号、原始数据、异常信息List<ErrorModel> errorModels = function.apply(dataList);errorList.addAll(errorModels);log.info("数据库保存结束.......");}/*** 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则继续读取下一行。* 如果不重写该方法,默认抛出异常,停止读取** @param exception exception* @param context context*/@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {// 如果continueAfterThrowing为false,则直接将异常抛出if (!continueAfterThrowing) {throw exception;}//当前sheet,当前行Integer rowIndex = context.readRowHolder().getRowIndex();log.error("读取发生错误! 错误SheetNo:{},错误行号:{},ERROR:{}", getCurrentSheetNo(context), context.readRowHolder().getRowIndex(), exception);errorList.add(new ErrorModel(rowIndex, JSON.toJSONString(getCurrentRowAnalysisResult(context)), exception.getMessage()));}/*** 获取当前读取的sheet no** @param context 定义了获取读取excel相关属性的方法* @return current sheet no*/private Integer getCurrentSheetNo(AnalysisContext context) {return context.readSheetHolder().getSheetNo();}/*** 获取当前读取的行号** @param context 定义了获取读取excel相关属性的方法* @return current row index*/private Integer getCurrentRowIndex(AnalysisContext context) {return context.readRowHolder().getRowIndex();}/*** 获取当前正在读取的行数据** @param context* @return*/private Object getCurrentRowAnalysisResult(AnalysisContext context) {return context.readRowHolder().getCurrentRowAnalysisResult();}/*** 获取错误的行号,和错误信息** @return Map<Integer, List < String>>*/public List<ErrorModel> getErrorList() {return errorList;}/*** 获取data数据** @return*/public List<Model> getData() {return dataList;}/*** 异常条数*/public Integer getErrorSize() {return errorList.size();}}
四、断言工具类
package com.example.demo.utils;import java.util.Objects;import java.util.function.Supplier;/*** 断言工具类** @author: Lucifer*/public class AssertUtil {public static void isNull(Object object, String message) {if (Objects.isNull(object)) {throw new RuntimeException(message);}}/*** 判断object参数是否为空* Assert.isNull(null, IllegalArgumentException::new);** @param object object类型* @param errorSupplier 错误抛出异常接口*/public static <X extends Throwable> void isNull(Object object, Supplier<X> errorSupplier) throws X {if (Objects.isNull(object)) {throw errorSupplier.get();}}}
五、测试
1、测试代码
package com.example.demo.pojo;import com.alibaba.excel.annotation.ExcelProperty;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableName;import com.example.demo.utils.excel.BaseExcelModel;import com.fasterxml.jackson.annotation.JsonFormat;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import org.springframework.format.annotation.DateTimeFormat;import java.util.Date;@AllArgsConstructor@NoArgsConstructor@Data@TableName("employees")public class Employees extends BaseExcelModel {@TableField("emp_no")@ExcelProperty(value = "员工编号")private Integer empNo;@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")@DateTimeFormat(pattern = "yyyy-MM-dd")@ExcelProperty(value = "生日")@TableField("birth_date")private Date birthDate;@TableField("first_name")@ExcelProperty(value = "名字")private String firstName;@TableField("last_name")@ExcelProperty(value = "姓")private String lastName;@TableField("gender")@ExcelProperty(value = "性别")private String gender;@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")@DateTimeFormat(pattern = "yyyy-MM-dd")@ExcelProperty(value = "入职日期")@TableField("hire_date")private Date hireDate;}package com.example.demo.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.demo.pojo.Employees;import org.apache.ibatis.annotations.Mapper;/*** //TODO** @author: lucifer*/@Mapperpublic interface ExcelMapper extends BaseMapper<Employees> {}package com.example.demo.service;import com.alibaba.fastjson.JSON;import com.example.demo.pojo.Employees;import com.example.demo.utils.excel.ErrorModel;import org.springframework.stereotype.Service;import java.util.ArrayList;import java.util.List;import java.util.Optional;/*** //TODO** @author: Lucifer*/@Servicepublic class ExcelService {public List<ErrorModel> importExcel(List<Employees> employees) {//查询出数据库重复的(模拟)//用employees中的唯一字段去数据库查询List<Employees> repeatList = new ArrayList<>();//repeatList 里面的数据实际应该有数据库查询所得//此处最好是用in 然后一条sql查询这个批次所有的，但是in中不要过大，3000差不多了List<ErrorModel> errorModels = new ArrayList<>();Optional.ofNullable(repeatList).orElse(new ArrayList<>()).forEach(emp ->errorModels.add(ErrorModel.builder().rowNo(emp.getRowNo()).resourceBody(JSON.toJSONString(employees)).errorMessage("xxxx重复").build()));//因处可以将插入数据库的操作写在另外一个service当中，比如叫xxxExcelService,然后//插入数据库的方法假如叫做insertDb();在insertDb方法中用@Transactional(rollbackFor = Exception.class,propagation=Propagation.REQUIRES_NEW)//然后在当前这个service中注入xxxExcelService，调用insertDb方法，并将其try catch住，//这里不要抛异常，而是将其构造成ErrorModel对象，统一返回，这个insertDb的事务因为是//另起的事务，所以一旦出现异常是会回滚数据库操作的。return errorModels;}}package com.example.demo.controller;import com.example.demo.mapper.ExcelMapper;import com.example.demo.pojo.Employees;import com.example.demo.service.ExcelService;import com.example.demo.utils.excel.ExcelUtil;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;import javax.annotation.Resource;import javax.servlet.http.HttpServletResponse;import java.util.List;/*** 测试** @author: lucifer*/@RestControllerpublic class TestController {@Resourceprivate ExcelMapper excelMapper;@Autowiredprivate ExcelService excelService;@GetMapping("test1")public void testExportExcel(HttpServletResponse httpServletResponse) {long startTime = System.currentTimeMillis();List<Employees> employees = excelMapper.selectList(null);ExcelUtil.writeExcel(httpServletResponse, employees, "文件名", "工作薄名", Employees.class);System.out.println("数据量:"+employees.size()+",耗时（毫秒）:"+(System.currentTimeMillis()-startTime));}@PostMapping("test1")public void testImportExcel(MultipartFile file) {//如果记录日志，可以用异步去插入数据，日志信息主表（另开一个事务）ExcelUtil.readExcel(file, Employees.class, obj -> excelService.importExcel(obj));//插入日志详情表，并更新日志主表（另开一个事务）}}
2、测试结果
导出：
导入：
懒得测了(#^.^#)
迈不过友情╰
港控/mmm°
不念不忘少年蓝@
小灰灰
忘是亡心i
还没有评论，来说两句吧...