基于springboot跟poi封装的最便捷的excel导出 - demo例子集 - 博客园
发布时间：2018-11-15
技术：springboot1.5.6 + maven3.0.5 + jdk1.8
概述
Springboot最便捷的Excel导出,只需要一个配置文件即可搞定
详细
代码下载：http://www.demodashi.com/demo/14471.html
一、准备工作
先在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
<`dependencies`>
<`dependency`>
<`groupId>junit</groupId`>
<`artifactId>junit</artifactId`>
<`version>4.11</version`>
<`scope>test</scope`>
</`dependency`>
<`dependency`>
<`groupId>javax.servlet</groupId`>
<`artifactId>jstl</artifactId`>
<`version>1.2</version`>
</`dependency`>
<`dependency`>
<`groupId>org.springframework.boot</groupId`>
<`artifactId>spring-boot-starter-cache</artifactId`>
</`dependency`>
<`dependency`>
<`groupId>org.springframework.boot</groupId`>
<`artifactId>spring-boot-starter-web</artifactId`>
</`dependency`>
<!-- 使用Jasper引擎解析JSP -->
<`dependency`>
<`groupId>org.apache.tomcat.embed</groupId`>
<`artifactId>tomcat-embed-jasper</artifactId`>
<`scope>provided</scope`>
</`dependency`>
<!-- jstl标签 -->
<`dependency`>
<`groupId>javax.servlet</groupId`>
<`artifactId>jstl</artifactId`>
</`dependency`>
<`dependency`>
<`groupId>org.apache.commons</groupId`>
<`artifactId>commons-lang3</artifactId`>
<`version>3.8.1</version`>
</`dependency`>
<`dependency`>
<`groupId>com.google.guava</groupId`>
<`artifactId>guava</artifactId`>
<`version>27.0-jre</version`>
</`dependency`>
<`dependency`>
<`groupId>org.apache.poi</groupId`>
<`artifactId>poi</artifactId`>
<`version>3.17</version`>
</`dependency`>
<`dependency`>
<`groupId>org.apache.poi</groupId`>
<`artifactId>poi-ooxml</artifactId`>
<`version>3.17</version`>
</`dependency`>
</`dependencies`>
二、程序实现
1、添加excel导出的person-export-config文件
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
<?`xml version="1.0" encoding="UTF-8"?>`
<`exportFile`>
<`fileName>exportConfig</fileName`>
<`exportType>0</exportType`>
<`cell`>
<`title>序号</title`>
<`alias>index</alias`>
</`cell`>
<`cell`>
<`title>姓名</title`>
<`alias>name</alias`>
</`cell`>
<`cell`>
<`title>年龄</title`>
<`alias>age</alias`>
</`cell`>
<`cell`>
<`title>性别</title`>
<`alias>sex</alias`>
</`cell`>
<`cell`>
<`title>日期</title`>
<`alias>date</alias`>
</`cell`>
</`exportFile`>
其中exportType:0表示导出EXCEL2007，exportType:1表示导出csv文件
title导出展示列的title名，alias表示映射的字段名
2、从classpath获取person-export-config.xml文件
从classpath获取person-export-coing.xml文件并转为inputStresm
1
2
3
4
ClassPathResource classPathResource = new ClassPathResource(`"export/person-export-config.xml"`);
InputStream inputStream = classPathResource.getInputStream();
ExportConfig exportConfig = ExportConfigFactory.getExportConfig(inputStream);
3、解析person-export-config.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
37
38
39
40
41
42
43
44
45
private static ExportConfig getExportCells(InputStream inputStream) throws FileExportException {
ExportConfig exportConfig = new ExportConfig();
DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder = null`;`
Document document = null`;`
try {
dBuilder = dbFactory.newDocumentBuilder();
document = dBuilder.parse(inputStream);
} catch (ParserConfigurationException | SAXException | IOException e) {
throw new FileExportException(e, "pares xml error"`);`
}
Element root = document.getDocumentElement();
NodeList elements = root.getElementsByTagName(`"cell"`);
List<ExportCell> exportCells = initElement(elements);
String fileName = ""`;`
String exportType1 = ""`;`
try {
fileName = ConfigParser.getNodeText(root, "fileName"`);`
exportType1 = ConfigParser.getNodeText(root, "exportType"`);`
} catch (FileImportException e) {
throw new FileExportException(e);
}
if (StringUtils.isEmpty(fileName)) {
throw new FileExportException(`"用于导出的xml文档 <fileName> 为空"`);
}
if (StringUtils.isEmpty(exportType1) || !StringUtils.isNumeric(exportType1)) {
throw new FileExportException(`"用于导出的xml文档 <exportType> 为空"`);
}
exportConfig.setFileName(fileName);
ExportType exportType = ExportType.getExportType(Integer.valueOf(exportType1));
if (exportType == null`) {`
throw new FileExportException(`"找不到相应的ExportType 解析xml得到的exportType 是" + exportType1);`
}
exportConfig.setExportType(exportType);
exportConfig.setExportCells(exportCells);
return exportConfig;
}
这时我们得到一个ExportConfig对象如下：
1
2
3
4
5
public class ExportConfig extends BaseEntity {
private String fileName;`//输出的文件名`
private ExportType exportType;`//0 表示 excel, 1 表示csv`
private List<ExportCell> exportCells;
4、添加要输出到excel的list对象
1
2
3
4
5
6
7
8
9
10
List<Map> lists = new LinkedList<>();
for (`int i = 0; i <` `10; i++) {`
Map<String, Object> maps = new HashMap<>();
maps.put(`"index"`, i);
maps.put(`"name"`, "张三" + i);
maps.put(`"age"`, Float.valueOf(i));
maps.put(`"sex",` `"男");`
maps.put(`"date"`, new Date());
lists.add(maps);
}
在实际项目中map可以是具体的实体类对象比如Person,只要对象里面的字段跟person-export-config里的alias标签对应上即可（如下图所示）。
5、获取ExportResult对象
具体代码如下：
1
ExportResult exportResult = FileExportor.exportResult(exportConfig, lists);
将exportConfig对象跟要输出到excel的lists对象传入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public static ExportResult exportResult(ExportConfig exportConfig, List<?> data) throws FileExportException {
ExportType exportType = exportConfig.getExportType();
switch (exportType) {
case EXCEL2007:
Workbook workbook = new ExcelExportImpl().getExportResult(data, exportConfig.getExportCells());
ExportExcelResult exportExcelResult = new ExportExcelResult();
exportExcelResult.setWorkbook(workbook);
exportExcelResult.setFileName(exportConfig.getFileName());
return exportExcelResult;
case CSV:
StringBuilder stringBuilder = new CSVExportImpl().getExportResult(data, exportConfig.getExportCells());
ExportCSVResult exportCSVResult = new ExportCSVResult();
exportCSVResult.setResult(stringBuilder.toString());
exportCSVResult.setFileName(exportConfig.getFileName());
return exportCSVResult;
}
throw new FileExportException(`"找不到对应的export type, export type is " + exportType.getNumber());`
}
6、最后将数据通过outputstream导出到excel
1
2
3
4
5
String fileName = "person统计" +`".xlsx"`;
setResponseHeader(response, fileName);
OutputStream outputStream = response.getOutputStream();
exportResult.export(outputStream);
1
2
3
4
5
6
7
8
public void export(OutputStream outputStream) throws FileExportException{
try {
workbook.write(outputStream);
outputStream.close();
} catch (IOException e) {
throw new FileExportException(`"[Error occurred while export excel message is] " + e);`
}
}
三、项目结构图
四、运行效果图
五、补充
本例子主要对poi导出excel进行了一个封装，通过xml配置文件配置跟实体类一一对应的字段，可灵活配置，在实际项目中非常实用。
代码下载：http://www.demodashi.com/demo/14471.html
注：本文著作权归作者，由demo大师发表，拒绝转载，转载需要作者授权
原网址: 访问
创建于: 2021-02-02 16:58:40
目录: default
标签: 无
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论