Spring boot使用POI导出EXCEL的简单教程

为什么写这笔记

  • 导出excel在web管理系统中是一个经典的功能。
  • 项目已经有新的解决方案,即让前端基于获取的json数据通过js插件来生成excel。好处在于任何的页面上的json数据都可以这样导出Excel,方便很多。
  • 所以,后端已经不需要提供这个功能,删除无用代码,把自制的工具代码搬到笔记这边

pom.xml

首先是jar问题,至少baidu搜索出来的前一页教程都没提及这个问题,也可能是本人使用的版本问题,这里使用的是poi4.0.0

1
2
3
4
5
6
7
8
9
10
11
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<!-- 操作xlsx格式的excel文件 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.0.0</version>
</dependency>

poi有两种操作excel的包,一个是poi-ooxml-schemas用于操作xls格式的Excel表-2003版,自Excel2007版文件格式都为xlsx,所以这里导入poi-excelant的包。

生成Excel表并导入数据

下面的操作搬运自 – poi官网教程

创建Excel文件

XSSFWorkbook需要导入poi-excelant的jar包,HSSFWorkbook需要导入poi-ooxml-schemas的jar包。

1
2
3
4
5
6
7
8
9
10
// 创建xls格式的excel文件
Workbook wb = new HSSFWorkbook();
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
// 创建xlsx格式的excel文件
Workbook wb = new XSSFWorkbook();
try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
wb.write(fileOut);
}

创建表

一个Excel文件可以包含多个表,不清楚的话,桌面新建一个Excel文件,看左下角的sheet1、sheet2、sheet3就知道了
多个sheet

1
2
3
Workbook wb = new XSSFWorkbook();  // or new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheetName");
Sheet sheet2 = wb.createSheet("second sheet");

sheet的名字有如下规则要求:

  • 命名不允许有:(空字符)0x0000、(全角空格)0x0003、冒号(:)、斜杠(\)、星号(*)、问号(?)、反斜杠(/)、中括号([、])
  • 命名不能以单引号(‘)开头或结尾

apache也提供了检验修正命名的工具

1
2
3
4
5
6
//默认将不符合规定的字符替换为半角空格
String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
//也可以自定义替换字符
String safeName2 = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]",'z');// returns "zO'Brien's saleszzz"
Sheet sheet3 = wb.createSheet(safeName);
Sheet sheet4 = wb.createSheet(safeName2);

替换名字的源码的实现很简单,这里看看apache的校验修正方式。

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
public static String createSafeSheetName(final String nameProposal, char replaceChar) {
if (nameProposal == null) {
return "null";
}
if (nameProposal.length() < 1) {
return "empty";
}
final int length = Math.min(31, nameProposal.length());
final String shortenname = nameProposal.substring(0, length);
final StringBuilder result = new StringBuilder(shortenname);
for (int i=0; i<length; i++) {
char ch = result.charAt(i);
switch (ch) {
case '\u0000':
case '\u0003':
case ':':
case '/':
case '\\':
case '?':
case '*':
case ']':
case '[':
result.setCharAt(i, replaceChar);
break;
case '\'':
if (i==0 || i==length-1) {
result.setCharAt(i, replaceChar);
}
break;
default:
// all other chars OK
}
}
return result.toString();
}

创建阵列(行、列)

1
2
3
4
5
6
7
8
9
10
11
Sheet sheet = wb.createSheet("new sheet");
// 使用sheet对象创建行对象,从零开始为第一行
Row row = sheet.createRow(0);
// 使用row对象创建列对象,从零开始为第一列
Cell cell = row.createCell(0);
// 为该列赋值
cell.setCellValue(1);
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue("hello wolrd");
// 通过指定的行和列来确认一个具体的单元格
sheet.createRow(1).createCell(0).setCellValue(111);

完整的导出数据过程

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
//使用lombok生成getter/setter方法
@Data
public class UserInfo implements Serializable {

private static final long serialVersionUID = 1L;

private Integer id;

private String username;

private Integer age;
}

//直接写controller了,
@RequestMapping("/test")
public class TestController {

@Autowired
private IUserInfoService userInfoService;

@GetMapping("/downLoadUserInfoExcel")
public void getExcel(HttpServletResponse response){
//告诉浏览器下载excel文件,并通过response写回文件
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=UserInfo.xlsx");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
List<UserInfo> userInfos = userInfoService.getListUserInfo();
for (int i = 0; i < userInfos.size(); i++) {
Row row = sheet.createRow(i);
row.createCell(0).setCellValue(userInfos.get(i).getId());
row.createCell(1).setCellValue(userInfos.get(i).getUserName());
row.createCell(2).setCellValue(userInfos.get(i).getAge());
}
try(OutputStream outputStream = response.getOutputStream()){
workbook.write(outputStream);
}catch (IOException ex){
ex.printStackTrace();
}
}

小工具

用于任意一种类型的数据excel表导出的小工具,先来看看使用方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Workbook workbook = new XSSFWorkbook();
new SheetDataUtil<UserInfo>(workbook.createSheet(safeName))
//设置列标题 如果不设置列标题,默认使用字段名
.setHeaders("用户id","用户名","年龄")
// 也可以 关闭列标题
//.enableHeaders(false)
.setData(userInfoService.getListUserInfo())
//需要忽略的字段
.setIgnoredFields("serialVersionUID")
.exportData();
try(OutputStream outputStream = response.getOutputStream()){
workbook.write(outputStream);
}catch (IOException ex){
ex.printStackTrace();
}

以下是源码:

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
162
163
164
165
166
167
168
169
170
171
172
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;

/**
* Description:
* 基于POI封装的导表工具
* 用于只对任意一种数据格式导入Excel表的数据导入工具
* TODO 基本上可以使用,但是需要细节完善。缺点在于,当字段是引用类型时……
*
* @author zcolder
* @date 2018-10-19
*/
@Deprecated
public class SheetDataUtil<T> {
/**
* 用于自增的行号
*/
private int lineNumber;

/**
* 自定义每列的标题
*/
private ArrayList<String> customHeaders;

/**
* 默认每列的标题为属性名
*/
private ArrayList<String> defaultHeaders;

/**
* 表对象
*/
private Sheet sheet;

/**
* 用于标记是否存在自定义每列的标题
*/
private boolean isExistHeaders;

/**
* 用于标记是否关闭导入标题
*/
private boolean isImportHeaders;

/**
* 数据
*/
private Collection<T> data;

/**
* 泛型T对象的所有属性的Getter方法
*/
private ArrayList<String> getterMethodNames = new ArrayList<>();

/**
* 需要忽略的字段
*/
private ArrayList<String> ignoredFields;

/**
* 存在要忽略的字段
*/
private boolean isExistIgnoredFields;

/**
* 行 对象
*/
private Row row;

public SheetDataUtil(Sheet sheet) {
this.sheet = sheet;
}

public SheetDataUtil<T> setData(Collection<T> data) {
this.data = data;
return this;
}

public SheetDataUtil<T> enableHeaders(boolean isImportHeadersFlag) {
this.isImportHeaders = isImportHeadersFlag;
return this;
}

public SheetDataUtil<T> setHeaders(String... headers) {
return setHeaders(new ArrayList<>(Arrays.asList(headers)));
}

public SheetDataUtil<T> setHeaders(ArrayList<String> headers) {
this.customHeaders = headers;
this.isExistHeaders = true;
return this;
}

public SheetDataUtil<T> setIgnoredFields(String... fieldName) {
return setIgnoredFields(new ArrayList<>(Arrays.asList(fieldName)));
}

public SheetDataUtil<T> setIgnoredFields(ArrayList<String> fieldName) {
this.ignoredFields = fieldName;
this.isExistIgnoredFields = true;
return this;
}

public void exportData() throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
// 通过过反射获取所有字段,并组装Getter方法名
setGetterMethodNames(data.iterator().next().getClass());
// 导入每列的标题,如果用户有自定义
if (isImportHeaders) {
importHeaders();
}
// 循环将数据填充入excel表
for (T t : data) {
row = sheet.createRow(lineNumber++);
int i = 0;
for (String getterMethodName : getterMethodNames) {
// 通过获取get方法,来获取实例的属性值
Method getterMethod = t.getClass().getMethod(getterMethodName);
Object value = getterMethod.invoke(t);
// 此处的判断顺序 按照类型的使用概率
if (value instanceof Number) {
if (value instanceof Integer) {
row.createCell(i).setCellValue((Integer) value);
} else if (value instanceof Long) {
row.createCell(i).setCellValue((Long) value);
} else if (value instanceof Float) {
row.createCell(i).setCellValue((Float) value);
} else if (value instanceof Double) {
row.createCell(i).setCellValue((Double) value);
} else if (value instanceof Byte) {
row.createCell(i).setCellValue((Byte) value);
} else if (value instanceof Short) {
row.createCell(i).setCellValue((Short) value);
}
}
// 除了数字类型之外,其他都转为文本
row.createCell(i).setCellValue(value.toString());
i++;
}
}
}

private void importHeaders() {
ArrayList<String> headers = isExistHeaders ? customHeaders : defaultHeaders;
row = sheet.createRow(lineNumber++);
for (int i = 0; i < headers.size(); i++) {
row.createCell(i).setCellValue(headers.get(i));
}
}

private void setGetterMethodNames(Class clazz) {
defaultHeaders = new ArrayList<>();
for (Field field : clazz.getDeclaredFields()) {
String fieldName = field.getName();
// 忽略不需要的字段
if (isExistIgnoredFields && ignoredFields.contains(fieldName)) {
continue;
}
//拼接getter方法名,并添加
getterMethodNames.add("get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1));
defaultHeaders.add(fieldName);
}
}
}
zcolder wechat
写得不好?加我QQ开始喷我!