简介
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导入,导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法。
集成
pom 中引入依赖即可
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<!--easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
|
整合工具类 EasyPoiUtil
|
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
161
|
package cn.common.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.common.exception.ZXException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author huangy
* @date 2019/6/28 14:57
*/
public class EasyPoiUtil {
/**
* 导出Excel，包括文件名以及表名。是否创建表头
*
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param isCreateHeader 是否创建表头
* @param fileName 文件名
* @param response
* @return
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 导出Excel 默认格式 默认有创建表头
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
/**
* map多sheet形式导出
* @param list
* @param fileName
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
/**
* 常规默认导出方式
* @param list
* @param pojoClass
* @param fileName
* @param response
* @param exportParams
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
ExcelExportUtil.closeExportBigExcel();
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
/**
* 多sheet默认导出方式
* @param list
* @param fileName
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
ExcelExportUtil.closeExportBigExcel();
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
/**
* 下载excel
* @param fileName
* @param response
* @param workbook
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new ZXException(e.getMessage());
}
}
/**
* 导入 文件路径形式
* @param filePath
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
throw new ZXException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new ZXException(e.getMessage());
}
return list;
}
/**
* 导入 MultipartFile 形式
* @param file
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
throw new ZXException("excel文件不能为空");
} catch (Exception e) {
throw new ZXException(e.getMessage());
}
return 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
|
public class BlackListExport {
@Excel(name = "客户姓名", width = 15, orderNum = "2")
private String name;
@Excel(name = "备注", width = 10, orderNum = "1")
private String remark;
@Excel(name = "手机号", width = 15, orderNum = "0")
private String phone;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public BlackListExport() {
}
public BlackListExport(String name, String remark, String phone) {
this.name = name;
this.remark = remark;
this.phone = phone;
}
}
|
接口
|
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
|
@ApiOperation(value = "easyPoiUtil 导出测试")
@GetMapping(value = "/poi/export1")
public void export1(HttpServletResponse response){
List<BlackListExport> list=new ArrayList<>();
for(int i=0;i<10000;i++){
list.add(new BlackListExport(i+"",i+"",i+""));
}
EasyPoiUtil.exportExcel(list,"zx","huangy",BlackListExport.class,"zx.xls",response);
}
/**
* 如果填充不同sheet得data数据列表使用相同得list对象进行填充的话，
* 会出现第一次填充得sheet有数据，后续其他使用相同list对象进行data填充得sheet没有数据展示。
* @param response
*/
@ApiOperation(value = "多sheet 导出测试")
@GetMapping(value = "/poi/export2")
public void export2(HttpServletResponse response){
// 查询数据,此处省略
List list = new ArrayList<>();
list.add(new BlackListExport("姓名1","备注1","手机1")) ;
list.add(new BlackListExport("姓名2","备注2","手机2")) ;
list.add(new BlackListExport("姓名3","备注3","手机3")) ;
List list2 = new ArrayList<>();
list2.add(new BlackListExport("姓名-1","备注-1","手机-1")) ;
list2.add(new BlackListExport("姓名-2","备注-2","手机-2")) ;
list2.add(new BlackListExport("姓名-3","备注-3","手机-3")) ;
List<Map<String,Object>> sheetsList = new ArrayList<>();
for(int i=1;i<=4;i++){
// 设置导出配置
// 创建参数对象（用来设定excel得sheet得内容等信息）
ExportParams params = new ExportParams() ;
// 设置sheet得名称
params.setSheetName("表格"+i);
//创建sheet使用的map
Map<String,Object> dataMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
dataMap.put("title",params) ;
// 模版导出对应得实体类型
dataMap.put("entity",BlackListExport.class) ;
// sheet中要填充得数据
if(i%2==0){
dataMap.put("data",list) ;
}else {
dataMap.put("data",list2) ;
}
sheetsList.add(dataMap);
}
EasyPoiUtil.exportExcel(sheetsList,"hy.xls",response);
}
|
到此这篇关于Java使用easypoi快速导入导出的实现的文章就介绍到这了,更多相关Java easypoi导入导出内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/qq_37209293/article/details/94025049