springboot整合easyExcel【事务处理】

说明：这里就不在说明常用excel【poi】的导入导出和easyExcel的对比，感兴趣的小伙伴可以去官网查看一下，话不多说，干代码！

工程目录【项目为spring boot项目，整合mybatis-plus】

库表结构

1.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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>Socket_Demo</artifactId>
        <groupId>Socket_Demo</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <groupId>Socket_Demo</groupId>
    <artifactId>easy_excel</artifactId>
    <packaging>war</packaging>

    <name>easy_excel Maven Webapp</name>
    <!-- FIXME change it to the project's website -->
    <url>http://www.example.com</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
            <exclusions>
                <exclusion>
                    <artifactId>poi</artifactId>
                    <groupId>org.apache.poi</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>poi-ooxml</artifactId>
                    <groupId>org.apache.poi</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>net.sf.saxon</groupId>
            <artifactId>saxon-dom</artifactId>
            <version>8.7</version>
            <scope>compile</scope>
        </dependency>

        <!-- pagehelper分页 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
        </dependency>

        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>

        <!--mysql数据库依赖包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

    </dependencies>

    <build>
        <finalName>easy_excel</finalName>
        <pluginManagement>
            <plugins>
                <plugin>
                    <artifactId>maven-clean-plugin</artifactId>
                    <version>3.1.0</version>
                </plugin>
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.0</version>
                </plugin>
                <plugin>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <version>2.22.1</version>
                </plugin>
                <plugin>
                    <artifactId>maven-war-plugin</artifactId>
                    <version>3.2.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-install-plugin</artifactId>
                    <version>2.5.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-deploy-plugin</artifactId>
                    <version>2.8.2</version>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>
</project>

2.application.yml文件

server:
  port: 8080

#数据源
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
  #日期配置 yyyy-MM-dd HH:mm:ss
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss

#映射xml
mybatis-plus:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.cn.dev.model
  configuration:
    #开启驼峰
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl


#分页插件
pagehelper:
  auto-dialect: mysql
  reasonable: true
  support-methods-arguments: true

3.TestDataListener文件

package com.cn.dev.config;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.fastjson.JSON;
import com.cn.dev.model.Test;
import com.cn.dev.service.TestService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.util.ArrayList;
import java.util.List;

/**
 * @author 李庆伟
 * @date 2021/11/5 15:08
 */
@Slf4j
public class TestDataListener extends AnalysisEventListener<Test> {


    //事务管理
    private DataSourceTransactionManager dataSourceTransactionManager;
    private DefaultTransactionDefinition transactionDefinition;
    private TransactionStatus transactionStatus = null;

    private TestService testService;

    public TestDataListener(TestService testService,
                            DataSourceTransactionManager dataSourceTransactionManager,
                            TransactionDefinition transactionDefinition) {
        this.testService = testService;
        this.dataSourceTransactionManager = dataSourceTransactionManager;
        this.transactionDefinition = new DefaultTransactionDefinition(transactionDefinition);
        //设置事务的隔离级别 ：未提交读写
        this.transactionDefinition.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
        // 手动开启事务
        this.transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
    }


    //每隔5条存储数据库，实际使用中可以设置为2500条，然后清理list ，方便内存回收
    private static final int BATCH_COUNT = 5;
    List<Test> list = new ArrayList<>();

    @Override
    public void invoke(Test data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));

        boolean hasCompleted = transactionStatus.isCompleted();
        // 如果事务已经关闭
        if (hasCompleted){
            return;
        }

        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        boolean hasCompleted = transactionStatus.isCompleted();
        if (hasCompleted){
            return;
        }
        saveData();
        log.info("所有数据解析完成！");
        if (!hasCompleted){
            //提交事务
            dataSourceTransactionManager.commit(transactionStatus);
            log.info("SensitiveWordListener doAfterAllAnalysed：当前事务已提交");
        }
    }

   @Override
   public void onException(Exception exception, AnalysisContext context) throws Exception {
       log.info("导入过程中出现异常会进入该方法，重写了父类方法");
       log.info("结束前事务状态："+  transactionStatus.isCompleted());
       dataSourceTransactionManager.rollback(transactionStatus);
       log.info("结束后事务状态："+  transactionStatus.isCompleted());
       throw exception;
  }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        if (!CollectionUtils.isEmpty(list)) {
            testService.saveBatch(list);
            System.out.println(list);
        }
        //TODO 这里是测试事务，如有需要可以打开注释
        /*
        if(!CollectionUtils.isEmpty(list) && list.get(0).getAge()==6){
            int a = 1/0;
        }
        */
        log.info("存储数据库成功！");
    }
}

4.ExcelUtil工具类

package com.cn.dev.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author 李庆伟
 * @date 2021/11/5 14:55
 */
@Slf4j
public class ExcelUtil {

    /**
     * 导出
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        excelName = URLEncoder.encode(excelName, "UTF-8");//URLEncoder.encode可以防止中文乱码
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }
}

5.Test实体类

package com.cn.dev.model;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.util.Date;

/**
 * @author 李庆伟
 * @date 2021/11/5 14:44
 */
@Data
@TableName(value = "t_excel_test")
public class Test {
    // 主键id
    @TableId
    @ExcelIgnore // 生成报表时忽略，不生成次字段
    private String id;
    @ExcelProperty(value = "姓名", index = 0) // 定义表头名称和位置,0代表第一列
    private String name;
    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;
    @ColumnWidth(20) // 定义列宽
    @DateTimeFormat(value = "yyyy/MM/dd")
    @ExcelProperty(value = "出生日期", index = 2)
    private Date birthday;
}

6.TestController控制层

package com.cn.dev.controller;

import com.alibaba.excel.EasyExcel;
import com.cn.dev.config.TestDataListener;
import com.cn.dev.model.Test;
import com.cn.dev.service.TestService;
import com.cn.dev.utils.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * easyExcel导入导出，加入事务
 * @return {@link }
 * @throws
 * @author 李庆伟
 * @date 2021/11/9 16:54
 */
@Slf4j
@RequestMapping("test")
@RestController
public class TestController {

    public static List<Test> list = new ArrayList<>();

    static {
        for (int i = 0; i < 10 ; i++) {
            int count=(int)(1+Math.random()*(10000-1+1));
            Test test = new Test();
            test.setId(count+"");
            test.setName("思密达 _ " + i);
            test.setAge(i);
            test.setBirthday(new Date());
            list.add(test);
        }
    }

    @Autowired
    private TestService testService;
    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    private TransactionDefinition transactionDefinition;

    // 导出Excel到web
    @GetMapping("/exportWeb")
    public void exportWeb(HttpServletResponse response) {
        try {
            //list为模拟数据，这里就不写查询列表接口
            ExcelUtil.export2Web(response, "用户表", "用户信息", Test.class, list);
        } catch (Exception e) {
            log.error("导出异常:", e);
        }
    }


    // Excel文件导入
    @PostMapping("/upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        //加入事务
        EasyExcel.read(file.getInputStream(), Test.class, new TestDataListener(testService,dataSourceTransactionManager,transactionDefinition)).sheet().doRead();
        return "上传成功";
    }

}

7.TestService业务层接口

package com.cn.dev.service;

import com.cn.dev.model.Test;

import java.util.List;

/**
 * @author 李庆伟
 * @date 2021/11/5 14:20
 */
public interface TestService {

    void saveBatch(List<Test> list);

}

8.TestServiceImpl业务层接口实现类

package com.cn.dev.service.impl;

import com.cn.dev.dao.TestMapper;
import com.cn.dev.model.Test;
import com.cn.dev.service.TestService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.UUID;

/**
 * @author 李庆伟
 * @date 2021/11/5 14:20
 */
@Service
public class TestServiceImpl implements TestService {

    @Resource
    private TestMapper testMapper;

    /**
     * 这里应该写批量插入的，为了方便就不写了
     * [list]
     * @return {@link }
     * @throws
     * @author 李庆伟
     * @date 2021/11/9 16:56
     */
    public void saveBatch(List<Test> list) {
        if(list != null && list.size() > 0){
            for (int i = 0; i < list.size(); i++) {
                Test test = list.get(i);
                test.setId(UUID.randomUUID().toString().replace("-",""));
                testMapper.insert(test);
            }
        }
    }
}

9.TestMapper数据层文件

package com.cn.dev.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cn.dev.model.Test;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author 李庆伟
 * @date 2021/11/8 10:54
 */
@Mapper
public interface TestMapper extends BaseMapper<Test> {

}

10.ExcelApp项目入口启动类

package com.cn.dev;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ExcelApp {
   public static void main(String[] args) {
      SpringApplication.run(ExcelApp.class, args);
   }
}

有时间就积累一点点。。。。。。。。。。。。。。。

进入异常【看打印日志，日志打印完成，抛by zero】：