Spring Boot整合EasyExcel导入导出

蔚落 2023-05-30 09:13 19阅读 0赞

###       ps:2021，对本篇博客进行一个更新，重新写了一个demo，并对easyExcel封装了一个工具类，以下是demo的完整内容： ###

### 一、项目结构： ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70][]

**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/> 
        </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: root
        password: 123456
        url: jdbc:mysql://localhost:3306/employees?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        driver-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;
    
    @Slf4j
    public 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
    @Builder
    public 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
    @Builder
    public 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
    @ExcelIgnoreUnannotated
    public 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
     */
    @Slf4j
    public 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
         */
        @Override
        public 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了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            if (dataList.size() >= batchCount) {
                doService();
                // 存储完成清理 dataList
                dataList.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
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            doService();
            // 存储完成清理 list
            dataList.clear();
        }
    
        /**
         * 保存数据库
         * 需要由调用者自己实现
         */
        public void doService() {
            log.info("数据库保存开始.......");
            //这里可以拿到你处理业务时，返回的行号、原始数据、异常信息
            List<ErrorModel> errorModels = function.apply(dataList);
            errorList.addAll(errorModels);
            log.info("数据库保存结束.......");
        }
    
        /**
         * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则继续读取下一行。
         * 如果不重写该方法,默认抛出异常,停止读取
         *
         * @param exception exception
         * @param context   context
         */
        @Override
        public 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
     */
    @Mapper
    public 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
     */
    @Service
    public 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
     */
    @RestController
    public class TestController {
    
        @Resource
        private ExcelMapper excelMapper;
        @Autowired
        private 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、测试结果**

**导出：**

![20210209130556424.png][]

**导入：**

**懒得测了(\#^.^\#)**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210209124402561.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2,size_16,color_FFFFFF,t_70
[20210209130556424.png]: https://img-blog.csdnimg.cn/20210209130556424.png