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

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

官方仓库

创建Spring + MyBatis项目

  • pom依赖
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
<?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

    1
    2
    3
    4
    5
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ##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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    <?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用到的, 根据名字匹配

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    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, 从数据库查询之后 封装这个类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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);
    }
  • 监听器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    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

  • 测试类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    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的数据, 存入数据库

  • 测试类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    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() 就是监听器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    /**
    * 最简单的读
    * <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();
    }
  • 简单的写

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    /**
    * 最简单的写
    * <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();
    }
  • 监听器的格式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    // 有个很重要的点 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映射文件

1
2
<!--        <property name="configLocation" value="classpath:mybatis-config.xml"/> mybatis的配置文件-->
<property name="mapperLocations" value="classpath:mapper/OneMapper.xml"/>
-------------------本文结束 感谢您的阅读-------------------
坚持原创技术分享,您的支持将鼓励我继续创作!
0%