关于POI导出Excel多级表头带标题与表尾的封装方法



前言:关于该方法呢主要是来源于该博主提供的资料,我主要是稍微修改下并记录。额外添加的内容就是表格标题,理论上支持导出一级、二级、三级等多级表头Excel文档,测试一级、二级是OK的,先上效果图如下:

  这是导出一级表头的Excel文档效果图:

image

  这是导出二级表头的Excel文档效果图:

image

一级表头的实现

  首先呢说下一级表头是实现,从简单的开始:

先上工具类封装的统一方法:

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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
package com.ylz.packcommon.common.util;

import com.ylz.bizDo.statisticalReports.vo.Record;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/***
* @ClassName: ExcelUtils
* @Description:POI实现导出含多级表头和含有表尾部信息的excel
* @Auther: lyb
* @Date: 2019/12/17 11:21
* @version : V1.0
*/
public class ExcelUtils {

/**
* @Author lyb
* @Description //TODO 多级表头Excel文件导出
* @Date 11:24 2019/12/17
* @Param [sheetName, head, dataList, type, tableEndData,out,titles] sheet名,多级表头,导出数据,导出类型,表尾,输出文件对象,首行标题
* @return org.apache.poi.xssf.usermodel.XSSFWorkbook
**/
public static XSSFWorkbook exportMultilevelHeader(String sheetName, String[][] head, List<?> dataList, Class type, String[][] tableEndData, OutputStream out,String titles) {
/*变量*/
String[] properties;
Object[] rowValue;
List<Object[]> values;
Field[] fields;
XSSFCell cell;
String vo;

/*导出Excel*/
// 第一步,创建一个workBook,对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();

// 表头 标题样式
XSSFFont titleFont = wb.createFont();
titleFont.setFontName("微软雅黑");//字体
titleFont.setFontHeightInPoints((short) 15);// 字体大小
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
titleStyle.setLocked(true);

// 第二步,在workBook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
XSSFRow row;
// 第四步,创建单元格,并设置值表头 设置表头居中
//生成一个Style
XSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中

int mergerNum = 0; //合并数
//添加表格标题
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, type.getDeclaredFields().length- 1));
row = sheet.createRow(0);//创建一行表格
row.setHeight((short) 0x349);//设置高度
cell = row.createCell(0);//创建单元格
cell.setCellStyle(titleStyle);//设置样式
cell.setCellValue(titles);//设置标题

//给单元格设置值
for (int i = 0; i < head.length; i++) {
row = sheet.createRow(i+1);
row.setHeight((short) 700);
for (int j = 0; j < head[i].length; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(head[i][j]);
}
}
Map<Integer, List<Integer>> map = new HashMap<Integer, List<Integer>>(); // 合并行时要跳过的行列
//合并行
for (int i = 0; i < head[head.length - 1].length; i++) {
if ("".equals(head[head.length - 1][i])) {
for (int j = head.length - 2; j >= 0; j--) {
if (!"".equals(head[j][i])) {
sheet.addMergedRegion(new CellRangeAddress(j+1, head.length, i, i)); // 合并单元格
break;
} else {
if (map.containsKey(j)) {
List<Integer> list = map.get(j);
list.add(i);
map.put(j, list);
} else {
List<Integer> list = new ArrayList<Integer>();
list.add(i);
map.put(j, list);
}
}
}
}
}
//合并列
for (int i = 0; i < head.length - 1; i++) {
for (int j = 0; j < head[i].length; j++) {
List<Integer> list = map.get(i);
if (list == null || (list != null && !list.contains(j))) {
if ("".equals(head[i][j])) {
mergerNum++;
if (mergerNum != 0 && j == (head[i].length - 1)) {
sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum, j)); // 合并单元格
mergerNum = 0;
}
} else {
if (mergerNum != 0) {
sheet.addMergedRegion(new CellRangeAddress(i+1, i+1, j - mergerNum - 1, j - 1)); // 合并单元格
mergerNum = 0;
}
}
}
}
}
//解析导出类型
Class<Record> recordClass = Record.class;
if (null == type) {
//导出失败
return null;
} else if (type.equals(recordClass)) {
//导出List<Record>
//获取Record中包含的properties,用于生成表格头及创建Cell
properties = getRecordProperties(dataList, null);
vo = "record";
} else {
//导出List<Bean>
//获取Bean的Field
fields = type.getDeclaredFields();
properties = getRecordProperties(null, fields);
vo = "bean";
}

if (null == head) {
int i = 0;
if (head.length > 0) {
i = head.length - 1;
}
head[i] = properties;
}

// 第五步,写入实体数据
/*表头行数*/
int m = 1;
if (head.length > 0) {
m = head.length;
}
values = getRowValue(dataList, properties, vo);
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(i + m+1); //创建行
rowValue = values.get(i);
// 第四步,创建单元格,并设置值
for (int j = 0; j < properties.length; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
setCellValue(cell, rowValue[j]);
}
}

//第六步,处理表格尾部的数据
if (tableEndData != null && tableEndData.length > 0) {
for (int i = 0; i < tableEndData.length; i++) {
row = sheet.createRow(dataList.size() + m + i);
sheet.addMergedRegion(new CellRangeAddress(dataList.size() + m + i, dataList.size() + m + i, 0, type.getDeclaredFields().length- 1));
for (int j = 0; j < tableEndData[i].length; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
setCellValue(cell, tableEndData[i][j]);
}
}
}
try {
wb.write(out);
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return wb;
}

/**
* @Author lyb
* @Description //TODO 获取Record包含的所有properties
* @Date 11:30 2019/12/17
* @Param [list, fields] 列名,属性
* @return java.lang.String[] 包含properties
**/
private static String[] getRecordProperties(List<?> list, Field[] fields) {
if (null != list && null == fields) {
Record record = (Record) list.get(0);
Set<String> keySet = record.keySet();
List<String> keysList = new ArrayList<>(keySet);
return keysList.toArray(new String[keysList.size()]);
} else if (null != fields && null == list) {
String[] properties = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
properties[i] = fields[i].getName();
}
return properties;
}
return new String[0];
}

/**
* @Author lyb
* @Description //TODO 转换列表数据
* @Date 11:33 2019/12/17
* @Param [list, properties, vo] 数据列表,属性列表,类型
* @return java.util.List<java.lang.Object[]> 转换后的数据
**/
private static List<Object[]> getRowValue(List<?> list, String[] properties, String vo) {
List<Object[]> resultList = new ArrayList<>();
Record record;
if (StringUtils.isBlank(vo)) {
return resultList;
}
else if ("record".equals(vo)) {
for (Object object : list) {
record = (Record) object;
Object[] values = new Object[properties.length]; //定义在外部数组值会被最后写入的覆盖
for (int i = 0; i < properties.length; i++) {

values[i] = record.get(properties[i]);

}
resultList.add(values);
}
return resultList;
}
else if ("bean".equals(vo)) {
for (Object object : list) {
Class cf = object.getClass();
Object[] values = new Object[properties.length]; //定义在外部数组值会被最后写入的覆盖
for (int i = 0; i < properties.length; i++) {
char[] name = properties[i].toCharArray();
name[0] -= 32;
try {
Method method = cf.getMethod("get" + String.valueOf(name));
values[i] = method.invoke(object);
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
}
resultList.add(values);
}
return resultList;
}
return resultList;
}

/**
* @Author lyb
* @Description //TODO 设置单元格值
* @Date 11:34 2019/12/17
* @Param [cell, value] 单元格,值
* @return void
**/
private static void setCellValue(XSSFCell cell, Object value) {
if (value instanceof String) {
cell.setCellValue((String) value);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
cell.setCellType(XSSFCell.CELL_TYPE_BOOLEAN);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
} else if (value instanceof Calendar) {
cell.setCellValue((Calendar) value);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
} else if (value instanceof RichTextString) {
cell.setCellValue((RichTextString) value);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
} else {
cell.setCellValue(String.valueOf(value));
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
}
}

/**
* @Author lyb
* @Description //TODO 测试方法
* @Date 13:26 2019/12/19
* @Param [args]
* @return void
**/
public static void main(String[] args) throws Exception{
// //标题
// String titles="小脆皮";
//
// //表头名
// String[][] headNames = {{"鲁班","小乔","安琪拉","甑姬","王昭君"}};
//
// //表尾名
// String[][] tableEnd = {{"不准看: "}};
//
// List<Testvo> list = new ArrayList<>();
// for (int i=0;i<5;i++) {
// Testvo vo=new Testvo();
// vo.setNo("1");
// vo.setName("鲁班大师");
// vo.setSex("男");
// vo.setAge(26);
// vo.setMoney("13888");
// list.add(vo);
// }
//
// OutputStream out = new FileOutputStream("C:\\Users\\lyb\\Desktop\\测试汇总表.xls");
//
// //导出
// exportMultilevelHeader("测试汇总",headNames,list,Testvo.class,tableEnd,out,titles);

}
}

  这里有一个类需要说明下,就是Record类进行重写了,作用其实就是中间接收我们需导出数据的实体类属性,该类贴码如下:

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
package com.ylz.bizDo.statisticalReports.vo;

/***
* @ClassName: Record
* @Description:该类用于POI多级表头Excel文件导出,用于接收导出实体属性
* @Auther: lyb
* @Date: 2019/12/17 11:30
* @version : V1.0
*/
import java.math.BigDecimal;
import java.util.Date;
import java.util.LinkedHashMap;

public class Record extends LinkedHashMap<String,Object> {
public void set(String field,Object value){
put(field,value);
}
public String getString(String field){
return (String)get(field);
}
public Integer getInteger(String field){
return (Integer)get(field);
}
public Long getLong(String field){
return (Long)get(field);
}
public BigDecimal getBigDecimal(String field){
return (BigDecimal)get(field);
}
public Date getDate(String field){
return (Date)get(field);
}
public Boolean getBoolean(String field){
return (Boolean) get(field);
}
}

  然后呢就是一级表头文档导出的测试方法了:

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
public static void main(String[] args) throws Exception{
//标题
String titles="小脆皮";

//表头名
String[][] headNames = {{"鲁班","小乔","安琪拉","甑姬","王昭君"}};

//表尾名
String[][] tableEnd = {{"不准看: "}};

List<Testvo> list = new ArrayList<>();
for (int i=0;i<5;i++) {
Testvo vo=new Testvo();
vo.setNo("1");
vo.setName("鲁班大师");
vo.setSex("男");
vo.setAge(26);
vo.setMoney("13888");
list.add(vo);
}

OutputStream out = new FileOutputStream("C:\\Users\\lyb\\Desktop\\测试汇总表.xls");

//导出
exportMultilevelHeader("测试汇总",headNames,list,Testvo.class,tableEnd,out,titles);

}

  至于TestVo类就不需要我贴了吧,只是一个测试类只有get、set方法,属性就循环的那些。


  至于二级表头的测试方法如下,主要内容都在工具类里面。基本上都有写注释相信应该可以看明白。

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
/**
* @Author lyb
* @Description //TODO 家庭签约登记统计Excel导出
* @Date 9:43 2019/12/17
* @Param []
* @return java.lang.String
**/
public String findRegistrationStatisticalToExcel() {
try {
//查询条件
RegistrationListQvo qvo = (RegistrationListQvo) getJsonLay(RegistrationListQvo.class);
if(qvo==null){
qvo=new RegistrationListQvo();
}
//登录人信息
CdUser user = this.getSessionUser();
//导出数据
List<RegistrationStatisticalExportVo> listData = sysDao.getStattisticalReportsDao().registrationListExport(qvo);
//Excel导出标题
String titles = "登记表";
//表头名
String[][] headNames = {{"姓名","性别","身份证号","联系电话","签约编码","签约家庭类别", "", "", "", "","","", "重点人群签约服务项目", "","","","","","", "其他人群", "","","","","签约服务包类型","备注"},
{"","","","","","计生失独伤残家庭", "计生独生子女", "计生双女", "五保户", "低保户","建档立卡贫困人口","其他", "老年人","高血压患者","2型糖尿病患者","严重精神障碍患者","结核病患者","孕产妇","0-6岁儿童", "残疾人", "脑血管病患者", "冠心病患者","癌症患者","其他","",""}};

//表尾名
String[][] tableEnd = {{"填报人: 分管院长: 填报时间: 年 月 日"}};
getResponse().reset();
getResponse().setContentType("application/vnd..ms-excel");
getResponse().setHeader("content-Disposition","attachment;filename="+ URLEncoder.encode("汇总表.xls","utf-8"));
ExcelUtils.exportMultilevelHeader("汇总表",headNames,listData, RegistrationStatisticalExportVo.class,tableEnd,getResponse().getOutputStream(),titles);
}catch (Exception e) {
e.printStackTrace();
new ActionException(getClass(), getAct(), getJsons(), e);
}
return null;
}

代码什么的都已经贴完了,也没别的东西了。


本次记录到此结束,欢迎订阅、关注、收藏、评论、点赞哦~~( ̄▽ ̄~)~

哇咔咔(∪。∪)。。。zzz