使用easyexcel读数据库写入到excel和读excel写入数据库

2020-01-02 / easyexcel excel

使用easyexcel读数据库写入到excel和读excel写入数据库


使用easyexcel读数据库写入到excel和读excel写入数据库

官方仓库

创建Spring + MyBatis项目

pom依赖


<?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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.lacknb</groupId>
    <artifactId>easy_excel_use</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>6</source>
                    <target>6</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <properties>
        <spring.version>5.1.5.RELEASE</spring.version>
        <mybatis.version>3.4.5</mybatis.version>
        <tomcat.version>9.0.12</tomcat.version>
        <mybatis-spring.version>1.3.2</mybatis-spring.version>
        <com.alibaba>1.1.10</com.alibaba>
        <mysql-connector>5.1.43</mysql-connector>
    </properties>

    <dependencies>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

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

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!--                        <dependency>-->
        <!--                            <groupId>org.springframework</groupId>-->
        <!--                            <artifactId>spring-aop</artifactId>-->
        <!--                            <version>${spring.version}</version>-->
        <!--                        </dependency>-->

        <!--                        <dependency>-->
        <!--                            <groupId>org.springframework</groupId>-->
        <!--                            <artifactId>spring-tx</artifactId>-->
        <!--                            <version>${spring.version}</version>-->
        <!--                        </dependency>-->

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>


        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>

        <!--    spring与mybatis整合包-->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis-spring.version}</version>
        </dependency>


        <!--        Druid数据源-->
        <!--         https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${com.alibaba}</version>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector}</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.29</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.12</version>
        </dependency>

    </dependencies>


</project>

jdbc.properties


jdbc.driver=com.mysql.jdbc.Driver
# allowMultiQueries=true 允许批量插入
jdbc.url=jdbc:mysql:///one?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=123456

log4j.properties


##define an appender named console
log4j.appender.console=org.apache.log4j.ConsoleAppender
#The Target value is System.out or System.err
log4j.appender.console.Target=System.out
#set the layout type of the apperder
log4j.appender.console.layout=org.apache.log4j.PatternLayout
#set the layout format pattern
log4j.appender.console.layout.ConversionPattern=[%-5p] %m%n

##define a logger
#log4j.rootLogger=TRACE,console
log4j.logger.cn.lacknb.listener.OneDataListener=debug,console
log4j.logger.cn.lacknb.dao.OneDao=debug,console

applicationContext.xml


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:component-scan base-package="cn.lacknb" />
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!--配置mybatis工厂 同时指定数据源 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
<!--        <property name="configLocation" value="classpath:mybatis-config.xml"/> mybatis的配置文件-->
        <property name="mapperLocations" value="classpath:mapper/OneMapper.xml"/>
    </bean>

    <!-- 配置一个可以批量执行的sqlSession -->
<!--    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">-->
<!--        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />-->
<!--        <constructor-arg name="executorType" value="BATCH"/>-->
<!--    </bean>-->

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.lacknb.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>


</beans>

OneMapper.xml


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.lacknb.dao.OneDao">

    <resultMap id="oneMap" type="cn.lacknb.beans.OneBo">
        <id property="id" column="id"/>
        <result column="title" property="title"/>
        <result column="words_info" property="wordInfo"/>
        <result column="text" property="text"/>
        <result column="img_url" property="imgUrl"/>
        <result column="pic_info" property="picInfo"/>
        <result column="post_date" property="postDate"/>
        <result column="share_url" property="shareUrl"/>
    </resultMap>

    <select id="selectAll" resultMap="oneMap">
        select * from t_one
    </select>

    <insert id="addOne">
        insert into t_one (id, title, words_info, text, pic_info, img_url, post_date, share_url) values
         <foreach collection="list" separator="," item="item">
            (#{item.id}, #{item.title}, #{item.wordInfo}, #{item.text}, #{item.picInfo},
             #{item.imgUrl}, #{item.postDate}, #{item.shareUrl})
         </foreach>
    </insert>
</mapper>

One.java, 这是读excel用到的, 根据名字匹配


package cn.lacknb.beans;

import com.alibaba.excel.annotation.ExcelProperty;

/**
 * ClassName: One <br/>
 * Description: 读excel文件, 数据封装成这个类 <br/>
 * date: 2019年12月19日 16:40 <br/>
 *
 * @author nianshao <br/>
 */
public class One {

    /**
     * Description:  指定列的下标, 或列名.
     * 这里不建议 name和index同时使用
     * 用名字匹配的时候,这里需要注意, 如果名字重复, 会导致只有一个 字段读取到数据
     */

    @ExcelProperty(value = "序号")
    private Integer id;

    @ExcelProperty(value = "标题")
    private String title;

    @ExcelProperty(value = "内容作者")
    private String wordInfo;

    @ExcelProperty(value = "内容")
    private String text;

    @ExcelProperty(value = "图片链接")
    private String imgUrl;

    @ExcelProperty(value = "图片作者")
    public String picInfo;

    @ExcelProperty(value = "发布日期")
    private String postDate;

    @ExcelProperty(value = "分享链接")
    private String shareUrl;

    public String getShareUrl() {
        return shareUrl;
    }

    public void setShareUrl(String shareUrl) {
        this.shareUrl = shareUrl;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getWordInfo() {
        return wordInfo;
    }

    public void setWordInfo(String wordInfo) {
        this.wordInfo = wordInfo;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public String getImgUrl() {
        return imgUrl;
    }

    public void setImgUrl(String imgUrl) {
        this.imgUrl = imgUrl;
    }

    public String getPicInfo() {
        return picInfo;
    }

    public void setPicInfo(String picInfo) {
        this.picInfo = picInfo;
    }

    public String getPostDate() {
        return postDate;
    }

    public void setPostDate(String postDate) {
        this.postDate = postDate;
    }

    @Override
    public String toString() {
        return "One{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", wordInfo='" + wordInfo + '\'' +
                ", text='" + text + '\'' +
                ", imgUrl='" + imgUrl + '\'' +
                ", picInfo='" + picInfo + '\'' +
                ", postDate='" + postDate + '\'' +
                '}';
    }
}

OneBo.java, 从数据库查询之后封装这个类


package cn.lacknb.beans;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;

/**
 * ClassName: OneBo <br/>
 * Description: 用来接收 从数据库查询的数据, 然后写入excel <br/>
 * date: 2019年12月19日 16:28 <br/>
 *
 * @author nianshao <br/>
 */

public class OneBo extends BaseRowModel {

    @ExcelProperty(value = "序号", index = 0)
    private Integer id;

    @ExcelProperty(value = "标题", index = 1)
    private String title;

    @ExcelProperty(value = "内容作者", index = 2)
    private String wordInfo;

    @ExcelProperty(value = "内容", index = 3)
    private String text;

    @ExcelProperty(value = "图片链接", index = 4)
    private String imgUrl;

    @ExcelProperty(value = "图片作者", index = 5)
    public String picInfo;

    @ExcelProperty(value = "发布日期", index = 6)
    private String postDate;

    @ExcelProperty(value = "分享链接", index = 7)
    private String shareUrl;

    public String getShareUrl() {
        return shareUrl;
    }

    public void setShareUrl(String shareUrl) {
        this.shareUrl = shareUrl;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getWordInfo() {
        return wordInfo;
    }

    public void setWordInfo(String wordInfo) {
        this.wordInfo = wordInfo;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public String getImgUrl() {
        return imgUrl;
    }

    public void setImgUrl(String imgUrl) {
        this.imgUrl = imgUrl;
    }

    public String getPicInfo() {
        return picInfo;
    }

    public void setPicInfo(String picInfo) {
        this.picInfo = picInfo;
    }

    public String getPostDate() {
        return postDate;
    }

    public void setPostDate(String postDate) {
        this.postDate = postDate;
    }
}

OneDao.java


package cn.lacknb.dao;

import cn.lacknb.beans.One;
import cn.lacknb.beans.OneBo;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * ClassName: OneDao <br/>
 * Description:  <br/>
 * date: 2019年12月19日 16:45 <br/>
 *
 * @author nianshao <br/>
 */

public interface OneDao {

    List<OneBo> selectAll();
    int addOne(@Param("list") List<One> list);
}

监听器


package cn.lacknb.listener;

import cn.lacknb.beans.One;
import cn.lacknb.dao.OneDao;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * ClassName: OneDataLisenter <br/>
 * Description:  <br/>
 * date: 2019年12月19日 17:48 <br/>
 *
 * @author nianshao <br/>
 */
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class OneDataListener extends AnalysisEventListener<One> {
    private static final Logger LOGGER = LoggerFactory.getLogger(OneDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<One> list = new ArrayList<One>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private OneDao oneDao;

//    public DemoDataListener() {
//        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//        demoDAO = new DemoDAO();
//    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param oneDao
     */
    public OneDataListener(OneDao oneDao) {
        this.oneDao = oneDao;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(One data, AnalysisContext context) {
//        System.out.println(data);
        LOGGER.info("解析到一条数据:{}", data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        if (list.size() != 0){
            saveData();
        }
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        oneDao.addOne(list);
        LOGGER.info("存储数据库成功！");
    }
}

从数据库中读取数据存到excel

测试类


package cn.lacknb.test;

import cn.lacknb.beans.One;
import cn.lacknb.beans.OneBo;
import cn.lacknb.dao.OneDao;
import cn.lacknb.listener.OneDataListener;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import static com.alibaba.excel.support.ExcelTypeEnum.XLSX;

/**
 * ClassName: Example01 <br/>
 * Description:  <br/>
 * date: 2019年12月19日 12:49 <br/>
 *
 * @author nianshao <br/>
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class Example01 {

    private static final String PATH = "/media/nianshao/nie475/java项目/easy_excel_use/files";

    @Autowired
    private OneDao oneDao;

    @Before
    public void test(){
        System.out.println(1);
    }

    /**
     * Description: 从数据库查询数据, 然后将数据写到excel中 <br/>
     * @date: 19-12-19 下午5:08 <br/>
     * @param: [] <br/>
     * @return:void
     */
    @Test
    public void test01() throws IOException {
        OutputStream out = new FileOutputStream(PATH + "/one.xlsx");
        long time1 = System.currentTimeMillis();
        ExcelWriter writer = new ExcelWriter(out, XLSX);
        Sheet sheet = new Sheet(1, 0, OneBo.class, "sheet的名字", null);
        sheet.setAutoWidth(Boolean.TRUE);
        List<OneBo> collect = oneDao.selectAll();
        writer.write(collect, sheet);
        writer.finish();
        out.close();
        long time2 = System.currentTimeMillis();
        System.out.println("总耗时: " + (time2 - time1) / 1000);
    }

}

读取excel的数据，　存入数据库

测试类


package cn.lacknb.test;

import cn.lacknb.beans.One;
import cn.lacknb.beans.OneBo;
import cn.lacknb.dao.OneDao;
import cn.lacknb.listener.OneDataListener;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import static com.alibaba.excel.support.ExcelTypeEnum.XLSX;

/**
 * ClassName: Example01 <br/>
 * Description:  <br/>
 * date: 2019年12月19日 12:49 <br/>
 *
 * @author nianshao <br/>
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class Example01 {

    private static final String PATH = "/media/nianshao/nie475/java项目/easy_excel_use/files";

    @Autowired
    private OneDao oneDao;

    @Before
    public void test(){
        System.out.println(1);
    }
    /**
     * Description:　读取excel文件, 封装成对象 <br/>
     * @date: 19-12-19 下午5:28 <br/>
     * @param: [] <br/>
     * @return:void
     */
    @Test
    public void test02() throws FileNotFoundException {
        long time1 = System.currentTimeMillis();

        EasyExcel.read(PATH + "/one.xlsx", One.class, new OneDataListener(oneDao)).sheet().doRead();
        long time2 = System.currentTimeMillis();
        System.out.println("总耗时: " + (time2 - time1) / 1000);
    }
}

这里的读和写, 用的两种不同的方法

官网上例子的两种方法

简单的读, 这里的new DemoDataListener() 就是监听器


/**
 * 最简单的读
 * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * <p>3. 直接读即可
 */
@Test
public void simpleRead() {
    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法1：
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

    // 写法2：
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
}

简单的写


/**
 * 最简单的写
 * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>2. 直接写即可
 */
@Test
public void simpleWrite() {
    // 写法1
    String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

    // 写法2
    fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
    excelWriter.write(data(), writeSheet);
    // 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}

监听器的格式


// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

2630条数据

从数据库中提取, 写入到excel中

总耗时7秒

从excel文件中获取数据, 存到数据库中

总耗时 14秒

遇到的坑

往数据库中存数据的时候, 数据库的链接一定要加utf8, 否则会乱码.

在applicationContext.xml中, 导入mybatis的配置文件的时候, 一定要记住mapperLocation与configLocation的区别

configLocation 是导入的mybatis的配置文件

mapperLocations是导入mybatis的mapper映射文件


<!--        <property name="configLocation" value="classpath:mybatis-config.xml"/> mybatis的配置文件-->
        <property name="mapperLocations" value="classpath:mapper/OneMapper.xml"/>

标题：使用easyexcel读数据库写入到excel和读excel写入数据库
作者：gitsilence
地址：https://blog.lacknb.cn/articles/2019/12/20/1577974146187.html

gitsilence 的个人博客

Coding...

使用easyexcel读数据库写入到excel和读excel写入数据库

官方仓库

创建Spring + MyBatis项目

从数据库中读取数据存到excel

读取excel的数据，　存入数据库

遇到的坑

使用easyexcel读数据库写入到excel和读excel写入数据库

官方仓库

创建Spring + MyBatis项目

从数据库中读取数据存到excel

读取excel的数据， 存入数据库

遇到的坑

读取excel的数据，　存入数据库