目录
使用easyexcel读数据库写入到excel和读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