先看图,可以看出有很多合并列,此为经常用到的需求,为了简化需求,这个类应运而生。未经许可,禁止转载
http://www.xdemo.org/poi-customize-columns/ 未经许可,禁止转载
写在前面,之前发布了一个ExcelUtils工具类参见:
http://www.xdemo.org/java-excel-poi-import-export-annotation-reflect/
http://www.xdemo.org/java-poi-excel/
1. ExcelUtils由于代码较多,已经分开到三个类中,这个类已经不再推荐使用(不代表不可用,仍然可用)
具体使用如下三个类:
@see org.xdemo.superutil.thirdparty.excel.BeanToExcel
@see org.xdemo.superutil.thirdparty.excel.ExcelToBean
@see org.xdemo.superutil.thirdparty.excel.CustomizeToExcel
2.BeanToExcel是根据注解,将数据到处到Excel中
3.ExcelToBean是导入Excel,转化成相应的Bean
4.CustomizeToExcel这个类中的方法是新增的,支持自定义表头,主要有四个方法
/**
* 获取工作簿对象
* @param columns List<ExcelColumn> 列定义
* @param list List<Map<String, Object>> 数据
* @return Workbook
* @throws IOException
*/
static org.apache.poi.ss.usermodel.Workbook getWorkbook(List<ExcelColumn> columns, List<Map<String,Object>> list)
/**
* 获取工作簿对象
* @param columnsJsonArray String 列定义,String类型的JSON数组
* @param list List<Map<String, Object>> 数据
* @return Workbook
* @throws IOException
*/
static org.apache.poi.ss.usermodel.Workbook getWorkbook(String columnsJsonArray, List<Map<String,Object>> list)
/**
* 写入到文件
* @param columns List<ExcelColumn> 列定义
* @param list List<Map<String, Object>> 数据
* @param filePath 文件路径
* @throws Exception
*/
static void toFile(List<ExcelColumn> columns, List<Map<String,Object>> list, String filePath)
/**
* 写入到文件
* @param columnsJsonArray String 列定义
* @param list List<Map<String, Object>> 数据
* @param filePath 文件路径
* @throws Exception
*/
static void toFile(String columnsJsonArray, List<Map<String,Object>> list, String filePath)
5. ExcelColumn类包含了title,field,width,children定义
//列名
private String title;
//列对应的数据中的field
private String field;
//列宽
private int width=0;
//子列
private List<ExcelColumn> children;
6. CustomizeToExcel具体使用示例代码
注意:
6.1 日期格式默认为yyyy-MM-dd HH:mm:ss
6.2 width宽度定义只对叶子节点有效
6.3 field定义只对叶子节点有效
6.4 默认固定列头
6.5 使用步骤
6.5.1 定义列头
6.5.2 定义数据List<Map<String,Object>>类型
6.6 关于合并列的说明
合并列,就是子节点的集合
节点A
|-children:[{节点A1},{节点A2}]
|-field
|-width
|-title
关键类CustomizeToExcel http://www.xdemo.org/poi-customize-columns/ 未经许可,禁止转载
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
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
|
package
org.xdemo.superutil.thirdparty.excel;
import
java.io.FileOutputStream;
import
java.io.IOException;
import
java.math.BigDecimal;
import
java.text.SimpleDateFormat;
import
java.util.ArrayList;
import
java.util.Date;
import
java.util.HashMap;
import
java.util.Iterator;
import
java.util.List;
import
java.util.Map;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.ss.usermodel.Cell;
import
org.apache.poi.ss.usermodel.CellStyle;
import
org.apache.poi.ss.usermodel.Font;
import
org.apache.poi.ss.usermodel.Row;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.apache.poi.ss.usermodel.Workbook;
import
org.apache.poi.ss.util.CellRangeAddress;
import
org.apache.poi.xssf.streaming.SXSSFWorkbook;
import
org.apache.poi.xssf.usermodel.XSSFCellStyle;
import
org.apache.poi.xssf.usermodel.XSSFColor;
import
org.apache.poi.xssf.usermodel.XSSFFont;
import
org.xdemo.superutil.j2se.RandomUtils;
import
org.xdemo.superutil.thirdparty.gson.GsonTools;
import
com.google.gson.JsonArray;
import
com.google.gson.JsonElement;
import
com.google.gson.JsonParser;
/**
* 自定义
*
* @author Goofy 自定义表头的Excel
*/
public
class
CustomizeToExcel {
// 创建工作簿
private
static
Workbook wb =
new
SXSSFWorkbook();
// 创建一个工作表sheet
private
static
Sheet sheet = wb.createSheet();
private
static
List<String> fields =
new
ArrayList<String>();
private
static
SimpleDateFormat sdf =
new
SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss"
);
/**
* 获取工作簿对象
*
* @param columns
* List<ExcelColumn> 列定义
* @param list
* List<Map<String, Object>> 数据
* @return Workbook
* @throws IOException
*/
public
static
Workbook getWorkbook(List<ExcelColumn> columns, List<Map<String, Object>> list)
throws
IOException {
GsonTools gt =
new
GsonTools(
false
,
null
,
null
,
null
);
return
getWorkbook(gt.toJson(columns), list);
}
/**
* 获取工作簿对象
*
* @param columnsJsonArray
* String 列定义
* @param list
* List<Map<String, Object>> 数据
* @return Workbook
* @throws IOException
*/
public
static
Workbook getWorkbook(String columnsJsonArray, List<Map<String, Object>> list)
throws
IOException {
init();
JsonArray array =
new
JsonArray();
JsonParser parser =
new
JsonParser();
array = parser.parse(columnsJsonArray).getAsJsonArray();
// 写标题
writeTitles(array,
0
,
null
);
// 合并列
mergeColumns();
// 合并行
mergeRows();
// 写入行分隔符
writeDelimiter();
// 冻结表头
freezeTitle();
// 写数据
writeData(list);
return
wb;
}
/**
* 写入到文件
*
* @param columns
* List<ExcelColumn> 列定义
* @param list
* List<Map<String, Object>> 数据
* @param filePath
* 文件路径
* @throws Exception
*/
public
static
void
toFile(List<ExcelColumn> columns, List<Map<String, Object>> list, String filePath)
throws
Exception {
GsonTools gt =
new
GsonTools(
false
,
null
,
null
,
null
);
toFile(gt.toJson(columns), list, filePath);
}
/**
* 冻结表头
*/
private
static
void
freezeTitle() {
int
rowsCount = sheet.getPhysicalNumberOfRows();
sheet.createFreezePane(
0
, rowsCount -
1
);
}
/**
* 初始化workbook,sheet,fields对象,防止多次连续调用发生错误
*/
private
static
void
init() {
wb =
new
SXSSFWorkbook();
sheet = wb.createSheet();
fields =
new
ArrayList<String>();
}
/**
* 写入到文件
*
* @param columnsJsonArray
* String 列定义
* @param list
* List<Map<String, Object>> 数据
* @param filePath
* 文件路径
* @throws Exception
*/
public
static
void
toFile(String columnsJsonArray, List<Map<String, Object>> list, String filePath)
throws
Exception {
FileOutputStream out =
null
;
try
{
getWorkbook(columnsJsonArray, list);
out =
new
FileOutputStream(filePath);
wb.write(out);
out.close();
}
catch
(Exception e) {
throw
e;
}
finally
{
if
(out !=
null
) {
out.close();
}
}
}
/**
* 写入行数据
*
* @param list
*/
private
static
void
writeData(List<Map<String, Object>> list) {
// 行数
int
rowsCount = sheet.getPhysicalNumberOfRows();
// 列数
int
colsCount = sheet.getRow(
0
).getPhysicalNumberOfCells();
Row row;
Cell cell;
CellStyle csTop = wb.createCellStyle();
csTop.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
CellStyle csBottom = wb.createCellStyle();
csBottom.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM_DASHED);
Map<String, Object> map =
new
HashMap<String, Object>();
for
(
int
r =
0
; r < list.size(); r++) {
row = sheet.createRow(r + rowsCount -
1
);
map = list.get(r);
for
(
int
c =
0
; c < colsCount; c++) {
cell = row.createCell(c);
if
(r ==
0
) {
cell.setCellStyle(csTop);
}
if
(r == list.size() -
1
) {
cell.setCellStyle(csBottom);
}
Object v = map.get(fields.get(c));
if
(v ==
null
) {
cell.setCellValue(
""
);
}
else
{
if
(v.getClass().equals(Date.
class
)) {
cell.setCellValue(sdf.format((Date) v));
}
else
{
cell.setCellValue(v.toString());
}
}
}
}
}
/**
* 写入列头下分隔符
*/
private
static
void
writeDelimiter() {
// 行数
int
rowsCount = sheet.getPhysicalNumberOfRows();
// 列数
int
colsCount = sheet.getRow(
0
).getPhysicalNumberOfCells();
Cell cell;
CellStyle cs = wb.createCellStyle();
Row row = sheet.createRow(rowsCount);
for
(
int
c =
0
; c < colsCount; c++) {
cell = row.createCell(c);
cs.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
cell.setCellStyle(cs);
}
}
/**
* 合并行
*/
private
static
void
mergeRows() {
// 行数
int
rowsCount = sheet.getPhysicalNumberOfRows();
// 列数
int
colsCount = sheet.getRow(
0
).getPhysicalNumberOfCells();
Row row =
null
;
Cell cell =
null
;
int
rowSpan =
0
;
for
(
int
c =
0
; c < colsCount; c++) {
rowSpan =
0
;
for
(
int
r = rowsCount -
1
; r > -
1
; r--) {
row = sheet.getRow(r);
cell = row.getCell(c);
if
(cell !=
null
&& r == rowsCount -
1
) {
break
;
}
else
if
(cell !=
null
&& r != rowsCount -
1
) {
// 合并列
sheet.addMergedRegion(
new
CellRangeAddress(rowsCount - rowSpan -
1
, rowsCount -
1
, c, c));
break
;
}
else
{
// 行合并数+1
rowSpan++;
}
}
}
}
/**
* 合并列
*/
private
static
void
mergeColumns() {
// 行数
int
rowsCount = sheet.getPhysicalNumberOfRows();
// 列数
int
colsCount = sheet.getRow(
0
).getPhysicalNumberOfCells();
Row row =
null
;
Cell cell1 =
null
;
Cell cell2 =
null
;
int
colSpan =
0
;
for
(
int
r =
0
; r < rowsCount; r++) {
row = sheet.getRow(r);
// 重置
colSpan =
0
;
row = sheet.getRow(r);
for
(
int
c =
0
; c < colsCount; c++) {
cell1 = row.getCell(c);
cell2 = row.getCell(c +
1
);
if
(cell1 ==
null
) {
// 如果当前单元格是空的,跳过,继续当前行的后一个单元格查找
if
(c == colsCount -
1
) {
break
;
}
else
{
continue
;
}
}
if
(cell2 ==
null
) {
// 说明当前行已经到最后一个单元格了
if
(colSpan >=
1
) {
// 判断colSpan是否大于等于1,大于1就要合并了
// 合并行中连续相同的值的单元格
sheet.addMergedRegion(
new
CellRangeAddress(r, r, c - colSpan, c));
break
;
}
}
if
(cell1 !=
null
&& cell2 !=
null
) {
// 如果当前单元格和下一个单元格内容相同,那么colSpan加1
if
(cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
colSpan++;
}
else
{
// 如果当前单元格和下一个不等,那么判断colSpan是否大于等于1
if
(colSpan >=
1
) {
// 合并行中连续相同的值的单元格
sheet.addMergedRegion(
new
CellRangeAddress(r, r, c - colSpan, c));
// 合并后重置colSpan
colSpan =
0
;
continue
;
}
}
}
}
}
}
/**
* 写入标题
*
* @param array
* JSON数组
* @param rowIndex
* 行号
* @param parentTitle
* 父节点名称
*/
private
static
void
writeTitles(JsonArray array,
int
rowIndex, String parentTitle) {
Iterator<JsonElement> it = array.iterator();
Row row = sheet.getRow(rowIndex) ==
null
? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);
Row lastRow = rowIndex ==
0
?
null
: sheet.getRow(rowIndex -
1
);
int
_colIndex = -
1
;
// 遍历当前行,获取行最多的单元格的个数,因为如果之前行之前有单元格是空的,会对列索引造成问题
int
currentMaxColumns =
0
;
for
(
int
x =
0
; x < rowIndex; x++) {
Row rr = sheet.getRow(x);
if
(rr.getPhysicalNumberOfCells() > currentMaxColumns) {
currentMaxColumns = rr.getPhysicalNumberOfCells();
}
}
// 查找上一级的列开始位置
if
(lastRow !=
null
&& parentTitle !=
null
) {
for
(
int
i =
0
; i < currentMaxColumns; i++) {
if
(lastRow.getCell(i) !=
null
&& lastRow.getCell(i).getStringCellValue() !=
null
&& lastRow.getCell(i).getStringCellValue().equals(parentTitle)) {
_colIndex = i;
break
;
}
}
}
_colIndex = _colIndex == -
1
?
0
: _colIndex;
Cell cell;
while
(it.hasNext()) {
JsonElement e = it.next();
// 是否是叶子节点
boolean
isLeaf =
false
;
// 获取子节点数量
int
count = getSubNodesCount(e);
// 没有子节点就是叶子节点,如果子节点为0,将count设置为1,是为了能够让下面的for循环执行一次,将标题写入
if
(count ==
0
) {
count =
1
;
isLeaf =
true
;
}
else
{
isLeaf =
false
;
}
XSSFCellStyle cs = (XSSFCellStyle) wb.createCellStyle();
// 水平居中
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 垂直居中
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置前景色
cs.setFillForegroundColor(
new
XSSFColor(
new
java.awt.Color(RandomUtils.getRandomInt(
0
,
255
),
185
, RandomUtils.getRandomInt(
0
,
255
))));
// 字体样式
Font font = wb.createFont();
// 粗体
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 设置字体
cs.setFont(font);
// 根据子节点的数量,写入相应数量的父节点的名称,待完成后合并列
for
(
int
i =
0
; i < count; i++) {
cell = row.createCell(_colIndex++);
if
(isLeaf) {
// 如果没有子节点,就是浅红色的
cs.setFillForegroundColor(
new
XSSFColor(
new
java.awt.Color(
255
,
199
,
206
)));
if
(e.getAsJsonObject().get(
"width"
) !=
null
&& e.getAsJsonObject().get(
"width"
).getAsInt() !=
0
) {
fields.add(e.getAsJsonObject().get(
"field"
).getAsString());
// 设置列宽,列宽是根据叶子节点来的,其他地方定义不生效,l列索引由于上面已经+1了,所以这里要减1
sheet.setColumnWidth(_colIndex -
1
, e.getAsJsonObject().get(
"width"
).getAsInt() *
256
);
}
}
cell.setCellStyle(cs);
cell.setCellValue(e.getAsJsonObject().get(
"title"
).getAsString());
// 判断是否写到最后一个父节点名称
if
(i == count -
1
) {
// 如果有子节点,递归写入子节点
if
(e.getAsJsonObject().get(
"children"
) !=
null
&& e.getAsJsonObject().get(
"children"
).getAsJsonArray().size() !=
0
) {
writeTitles(e.getAsJsonObject().get(
"children"
).getAsJsonArray(), rowIndex +
1
, e.getAsJsonObject().get(
"title"
).getAsString());
}
}
}
}
}
/**
* 获取当前节点的子节点数量
*
* @param e
* @return
*/
private
static
int
getSubNodesCount(JsonElement e) {
JsonArray array = e.getAsJsonObject().get(
"children"
) ==
null
?
null
: e.getAsJsonObject().get(
"children"
).getAsJsonArray();
if
(array ==
null
)
return
0
;
Iterator<JsonElement> it = array.iterator();
int
count = array.size();
while
(it.hasNext()) {
int
c = getSubNodesCount(it.next());
count += c >
0
? c -
1
: c;
}
return
count;
}
}
|
ExcelColumn列定义http://www.xdemo.org/poi-customize-columns/ 未经许可,禁止转载
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
|
package
org.xdemo.superutil.thirdparty.excel;
import
java.util.List;
/**
* Excel列定义
* @author Goofy
*/
public
class
ExcelColumn {
//列名
private
String title;
//列对应的数据中的field
private
String field;
//列宽
private
int
width=
0
;
//子列
private
List<ExcelColumn> children;
public
ExcelColumn(){}
public
ExcelColumn(String title, String field,
int
width) {
super
();
this
.title = title;
this
.field = field;
this
.width = width;
}
//省略get和set
}
|
测试代码http://www.xdemo.org/poi-customize-columns/ 未经许可,禁止转载
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
|
public
static
void
main(String[] args)
throws
Exception {
List<ExcelColumn> columns =
new
ArrayList<ExcelColumn>();
ExcelColumn a =
new
ExcelColumn(
"A"
,
"A"
,
20
);
ExcelColumn b =
new
ExcelColumn(
"B"
,
"B"
,
20
);
List<ExcelColumn> columns2 =
new
ArrayList<ExcelColumn>();
columns2.add(
new
ExcelColumn(
"C"
,
"C"
,
30
));
columns2.add(
new
ExcelColumn(
"D"
,
"D"
,
30
));
columns2.add(
new
ExcelColumn(
"E"
,
"E"
,
30
));
b.setChildren(columns2);
columns.add(b);
// a.setChildren(columns);
List<ExcelColumn> cs =
new
ArrayList<ExcelColumn>();
cs.add(a);
cs.add(
new
ExcelColumn(
"F"
,
"F"
,
12
));
cs.add(
new
ExcelColumn(
"G"
,
"G"
,
22
));
cs.add(b);
// 造数据
List<Map<String, Object>> list =
new
ArrayList<Map<String, Object>>();
Map<String, Object> map1 =
new
HashMap<String, Object>();
map1.put(
"C"
,
"CCC"
);
map1.put(
"D"
,
22
);
map1.put(
"E"
,
new
Date());
list.add(map1);
Map<String, Object> map2 =
new
HashMap<String, Object>();
map2.put(
"C"
,
"11"
);
map2.put(
"D"
,
321
);
map2.put(
"E"
,
new
Date());
list.add(map2);
Map<String, Object> map3 =
new
HashMap<String, Object>();
map3.put(
"C"
,
"3333"
);
map3.put(
"D"
,
'd'
);
map3.put(
"E"
,
new
BigDecimal(
1111
));
list.add(map3);
list.add(map1);
list.add(map2);
list.add(map3);
CustomizeToExcel
.toFile(
"[{\"title\":\"A\",\"field\":\"A\",\"width\":22},{\"title\":\"B\",\"field\":\"B\",\"width\":20,\"children\":[{\"title\":\"C\",\"field\":\"C\",\"width\":30},{\"title\":\"D\",\"field\":\"D\",\"width\":30},{\"title\":\"E\",\"field\":\"E\",\"width\":30,\"children\":[{\"title\":\"X\",\"field\":\"X\",\"width\":22},{\"title\":\"Y\",\"field\":\"Y\",\"width\":22,\"children\":[{\"title\":\"EE\",\"field\":\"EE\",\"width\":22},{\"title\":\"SS\",\"field\":\"SS\",\"width\":22,\"children\":[{\"title\":\"uu\",\"field\":\"uu\",\"width\":22},{\"title\":\"i\",\"field\":\"i\",\"width\":22},{\"title\":\"o\",\"field\":\"o\",\"width\":22}]}]}]}]},{\"title\":\"Z\",\"field\":\"Z\",\"width\":22}]"
,
list,
"D:\\x1.xlsx"
);
CustomizeToExcel.toFile(columns, list,
"D:\\x2.xlsx"
);
}
|