前引
在项目中经常有导出Excel的需求，为了方便就封装了一个导出Excel到前端的工具类，使用下来还比较好用
- 使用JDK版本：1.8
- spring boot版本：2.6.15
- 前端请求：axios
Java代码
Excel所需Maven依赖
1
2
3
4
5
| <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
|
完整代码如下：
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
173
174
175
176
177
178
179
180
181
182
| public class excelUtils {
public static CellStyle headStyle(Workbook workbook) {
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 12);
headerStyle.setFont(headerFont);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return headerStyle;
}
public static CellStyle tableStyle(Workbook workbook) {
CellStyle tableStyle = workbook.createCellStyle();
tableStyle.setAlignment(HorizontalAlignment.CENTER);
tableStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return tableStyle;
}
public static String getFieldString(Object object, String key) {
if(object instanceof Map){
return String.valueOf(((Map<?, ?>) object).get(key));
}
Field field = null;
String value = "";
try {
field = object.getClass().getDeclaredField(key);
field.setAccessible(true);
String orgStr = String.valueOf(field.get(object));
if (orgStr != null) {
value = orgStr;
if (orgStr.startsWith("[") && orgStr.endsWith("]")) {
value = orgStr
.replace("[", "")
.replace("]", "")
.replace("\"", "");
}
}
} catch (NoSuchFieldException e) {
throw new RuntimeException(e);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
return value;
}
public static void exportExcel(HttpServletResponse response, List<?> srcList, List<String> keys, List<String> titles, String excelName) {
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet(excelName);
Row titleRoe = sheet.createRow(0);
CellStyle headStyle = headStyle(workbook);
for (int i = 0; i < titles.size(); i++) {
Cell cell = titleRoe.createCell(i);
cell.setCellValue(titles.get(i));
cell.setCellStyle(headStyle);
}
CellStyle tableStyle = tableStyle(workbook);
for (int i = 0; i < srcList.size(); i++) {
Object object = srcList.get(i);
Row rowData = sheet.createRow(i + 1);
rowData.setHeight((short) 400);
for (int j = 0; j < keys.size(); j++) {
String key = keys.get(j);
Cell cell = rowData.createCell(j);
cell.setCellStyle(tableStyle);
String value = getFieldString(object, key);
cell.setCellValue(value);
}
if (i % 100 == 0) {
try {
((SXSSFSheet) sheet).flushRows(100);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
LocalDateTime localDateTime = LocalDateTime.now();
String fileName = excelName + localDateTime.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx";
try {
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
} catch (UnsupportedEncodingException e) {
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
throw new RuntimeException(e);
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Accept-Ranges", "bytes");
try (ByteArrayOutputStream fileOutputStream = new ByteArrayOutputStream();
ServletOutputStream out = response.getOutputStream()) {
workbook.write(fileOutputStream);
byte[] fileBytes = fileOutputStream.toByteArray();
response.setHeader("Content-Length", String.valueOf(fileBytes.length));
int chunkSize = 256;
int totalSize = fileBytes.length;
int currentPosition = 0;
while (currentPosition < totalSize) {
int remaining = totalSize - currentPosition;
int chunk = Math.min(chunkSize, remaining);
out.write(fileBytes, currentPosition, chunk);
out.flush();
currentPosition += chunk;
}
out.close();
workbook.close();
} catch (IOException e) {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<>();
map.put("message", "下载文件失败: " + e.getMessage());
try {
response.getWriter().println(JSON.toJSONString(map));
} catch (IOException ee) {
ee.printStackTrace();
}
e.printStackTrace();
}
}
}
|
前端请求示例
请求接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| export const outExcel = async (
data: any,
downloadProgress: Function,
) => {
return await request({
url: "/test/outExcel",
method: "post",
data,
responseType: "blob",
onDownloadProgress: (event: any) => {
downloadProgress(event);
},
});
}
|
注：
- 这里的代码是已经封装过的统一封装的axios
- responseType： “blob” 指定文件为二进制
- onDownloadProgress利用回调函数返回下载进度
下载示例
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
| const exportExcel = async () => {
isProcessing.value = true;
let response = null;
try {
response = await props.downloadMethod(props.searchForm, (event: any) => {
progress.value = (event.loaded / event.total) * 100;
if (progress.value > 0) {
isProcessing.value = false;
}
});
} catch (e: any) {
isProcessing.value = false;
isError.value = true;
}
if (response == null) return;
if (!response.status) {
isError.value = true;
}
const fileName =
"教师参与情况" + formatDate(new Date(), "yy-MM-dd hh:mm:ss") + ".xlsx";
const blob = new Blob([response.data], { type: "application/vnd.ms-excel" });
const downloadLink = document.createElement("a");
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = decodeURI(fileName);
downloadLink.style.display = "none";
document.body.appendChild(downloadLink);
downloadLink.click();
document.body.removeChild(downloadLink);
};
|
将props.downloadMethod替换为下载接口
**props.searchFormt替换为筛选数据
微信
