1 | 使用easyexcel读数据库写入到excel和读excel写入数据库 |
官方仓库
创建Spring + MyBatis项目
- pom依赖
1 |
|
jdbc.properties
1
2
3
4
5=com.mysql.jdbc.Driver
# allowMultiQueries=true 允许批量插入
=jdbc:mysql:///one?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8
=root
=123456log4j.properties
1
2
3
4
5
6
7
8
9
10
11
12
13##define an appender named console
=org.apache.log4j.ConsoleAppender
#The Target value is System.out or System.err
=System.out
#set the layout type of the apperder
=org.apache.log4j.PatternLayout
#set the layout format pattern
=[%-5p] %m%n
##define a logger
#log4j.rootLogger=TRACE,console
=debug,console
=debug,consoleapplicationContext.xml
1 |
|
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
<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
120package 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同时使用
* 用名字匹配的时候,这里需要注意, 如果名字重复, 会导致只有一个 字段读取到数据
*/
(value = "序号")
private Integer id;
(value = "标题")
private String title;
(value = "内容作者")
private String wordInfo;
(value = "内容")
private String text;
(value = "图片链接")
private String imgUrl;
(value = "图片作者")
public String picInfo;
(value = "发布日期")
private String postDate;
(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;
}
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
103package 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 {
(value = "序号", index = 0)
private Integer id;
(value = "标题", index = 1)
private String title;
(value = "内容作者", index = 2)
private String wordInfo;
(value = "内容", index = 3)
private String text;
(value = "图片链接", index = 4)
private String imgUrl;
(value = "图片作者", index = 5)
public String picInfo;
(value = "发布日期", index = 6)
private String postDate;
(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
21package 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
89package 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
*/
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
*/
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
67package 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/>
*/
(SpringJUnit4ClassRunner.class)
(locations = "classpath:applicationContext.xml")
public class Example01 {
private static final String PATH = "/media/nianshao/nie475/java项目/easy_excel_use/files";
private OneDao oneDao;
public void test(){
System.out.println(1);
}
/**
* Description: 从数据库查询数据, 然后将数据写到excel中 <br/>
* @date: 19-12-19 下午5:08 <br/>
* @param: [] <br/>
* @return:void
*/
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
59package 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/>
*/
(SpringJUnit4ClassRunner.class)
(locations = "classpath:applicationContext.xml")
public class Example01 {
private static final String PATH = "/media/nianshao/nie475/java项目/easy_excel_use/files";
private OneDao oneDao;
public void test(){
System.out.println(1);
}
/**
* Description: 读取excel文件, 封装成对象 <br/>
* @date: 19-12-19 下午5:28 <br/>
* @param: [] <br/>
* @return:void
*/
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. 直接读即可
*/
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. 直接写即可
*/
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
*/
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
*/
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 | <!-- <property name="configLocation" value="classpath:mybatis-config.xml"/> mybatis的配置文件--> |
