这篇文章主要为大家详细介绍了如何使用Java实现excel动态列导出,文中的示例代码讲解详细,具有一定的借鉴价值,感兴趣的小伙伴可以跟随小编一起学习一下
excel动态列，只好用poi来写了，也并不复杂，一样就这个件事情抽像为几步，就是套路了，开发效率就上去了。
准备空模板
导出操作与excel模板的导出一样，可以参考
excel导出标准化自定义SheetWriteHandler
要通过pos自己创建每一样，像模板一样创建即可.
1
2
3
4
WriteSheet sheet0 = EasyExcel.writerSheet(0)
//标题
.registerWriteHandler(new GoodsInvRdSumWriteHandler(goodsInvRdSumListDto.getHeader()))
.build();
主要重写afterSheetCreate,也就是一行行的创建excel模板
1
2
3
4
5
6
7
8
9
10
11
12
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
this.centerCellStyle = createCellContentStyle(workbook,HorizontalAlignment.CENTER,BorderStyle.THIN);
this.leftCellStyle = createCellContentStyle(workbook,HorizontalAlignment.LEFT,BorderStyle.THIN);
this.rightCellStyle = createCellContentStyle(workbook,HorizontalAlignment.RIGHT,BorderStyle.THIN);
Sheet sheet = workbook.getSheetAt(0);
row1(sheet,workbook);
row2(sheet,workbook);
row34(sheet);
row5(sheet);
}
第一行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
* 第一行是标题
* @param sheet
*/
private void row1(Sheet sheet,Workbook workbook){
Row row = sheet.createRow(0);
row.setHeight((short) (50 * 20));
Cell cell = row.createCell(0);
cell.setCellValue("商品收发汇总表");
cell.setCellStyle(getHeadCellStyle(workbook, this.centerCellStyle));
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 9+this.dynamicHeader.size()*2-1);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyleNoBorder(sheet, cellRangeAddress);
}
第二行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
* 第二行 公司名称、日期
* @param sheet
*/
private void row2(Sheet sheet,Workbook workbook){
Row row = sheet.createRow(1);
CellStyle subHeaderStyle = createCellContentStyle(workbook, HorizontalAlignment.LEFT,BorderStyle.NONE);
// 公司名称
Cell cell = row.createCell(0);
cell.setCellStyle(subHeaderStyle);
cell.setCellValue("公司：{companyName} 日期：{startBillDate}至{endBillDate}");
sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 9+this.dynamicHeader.size()*2-1));
}
第三行,第四行涉及到动态列的创建和合并表头
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
private void row34(Sheet sheet){
Row row3 = sheet.createRow(2);
Row row4 = sheet.createRow(3);
// 商品编码
Cell cell = row3.createCell(0);
cell.setCellValue("商品编码");
cell.setCellStyle(this.centerCellStyle);
CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 3, 0, 0);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
// 商品名称
cell = row3.createCell(1);
cell.setCellValue("商品名称");
cell.setCellStyle(this.centerCellStyle);
cellRangeAddress = new CellRangeAddress(2, 3, 1, 1);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
// 商品规格
cell = row3.createCell(2);
cell.setCellValue("商品规格");
cell.setCellStyle(this.centerCellStyle);
cellRangeAddress = new CellRangeAddress(2, 3, 2, 2);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
//动态列
int dySize = this.dynamicHeader.size();
if (dySize>0){
for (int i=0; i<dySize; i++){
Map<String,Object> colMap = this.dynamicHeader.get(i);
String busiType = String.valueOf(colMap.get("prop")).replace("busi_", BaseConstant.Separate.NONE);
BusinessTypeEnum businessTypeEnum = BusinessTypeEnum.getInvBusinessTypeEnum(busiType);
// 第3行——合并表头
cell = row3.createCell(3+i*2);
cell.setCellValue(businessTypeEnum.display());
cell.setCellStyle(this.centerCellStyle);
cellRangeAddress = new CellRangeAddress(2, 2, 3+i*2, 4+i*2);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
// 第4行——成本
cell = row4.createCell(3+i*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("数量");
// 第4行——数量
cell = row4.createCell(4+i*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("成本");
}
}
// 入库合计
cell = row3.createCell(3+dySize*2);
cell.setCellValue("入库合计");
cell.setCellStyle(this.centerCellStyle);
cellRangeAddress = new CellRangeAddress(2, 2, 3+dySize*2, 4+dySize*2);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
// 入库合计——成本
cell = row4.createCell(3+dySize*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("数量");
// 入库合计——数量
cell = row4.createCell(4+dySize*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("成本");
// 出库合计
cell = row3.createCell(5+dySize*2);
cell.setCellValue("出库合计");
cell.setCellStyle(this.centerCellStyle);
cellRangeAddress = new CellRangeAddress(2, 2, 5+dySize*2, 6+dySize*2);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
// 出库合计——成本
cell = row4.createCell(5+dySize*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("数量");
// 出库合计——数量
cell = row4.createCell(6+dySize*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("成本");
// 结余
cell = row3.createCell(7+dySize*2);
cell.setCellValue("结余");
cell.setCellStyle(this.centerCellStyle);
cellRangeAddress = new CellRangeAddress(2, 2, 7+dySize*2, 8+dySize*2);
sheet.addMergedRegionUnsafe(cellRangeAddress);
setMergedRegionStyle(sheet, cellRangeAddress);
// 结余——成本
cell = row4.createCell(7+dySize*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("数量");
// 结余——数量
cell = row4.createCell(8+dySize*2);
cell.setCellStyle(this.centerCellStyle);
cell.setCellValue("成本");
}
第五行是数据域
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
/**
* 第五行：数据域
* @param sheet
*/
private void row5(Sheet sheet){
Row row = sheet.createRow(4);
// 商品编码
Cell cell = row.createCell(0);
cell.setCellStyle(this.leftCellStyle);
cell.setCellValue("{.stockCode}");
// 商品名称
cell = row.createCell(1);
cell.setCellStyle(this.leftCellStyle);
cell.setCellValue("{.stockName}");
// 商品规格
cell = row.createCell(2);
cell.setCellStyle(this.leftCellStyle);
cell.setCellValue("{.stockModel}");
// 动态列
int dySize = this.dynamicHeader.size();
if (!CheckEmptyUtil.isEmpty(this.dynamicHeader)){
for (int i=0; i<dySize; i++){
Map<String,Object> colMap = this.dynamicHeader.get(i);
List<Map<String,String>> chidren = (List<Map<String,String>>)colMap.get("children");
// 数量
Map<String,String> countMap = chidren.get(0);
cell = row.createCell(3+i*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue(String.format("{.%s}", countMap.get("prop")));
// 成本
Map<String,String> costMap = chidren.get(1);
cell = row.createCell(4+i*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue(String.format("{.%s}", costMap.get("prop")));
}
}
// 入库合计
cell = row.createCell(3+dySize*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue("{.count_total_in}");
cell = row.createCell(4+dySize*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue("{.cost_total_in}");
// 出库合计
cell = row.createCell(5+dySize*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue("{.count_total_out}");
cell = row.createCell(6+dySize*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue("{.cost_total_out}");
// 结余
cell = row.createCell(7+dySize*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue("{.final_count}");
cell = row.createCell(8+dySize*2);
cell.setCellStyle(this.rightCellStyle);
cell.setCellValue("{.final_cost}");
}
表格样式这里只写一个,其他的参考pos文档即可,不要每一个单元都重新创建单元格样式,那样非常消耗性能.
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
private CellStyle createCellContentStyle(Workbook workbook, HorizontalAlignment align,BorderStyle borderStyle) {
CellStyle style = workbook.createCellStyle();
// 设置对齐样式
style.setAlignment(align);
//背景为白色
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 设置边框样式
// 下边框
style.setBorderBottom(borderStyle);
// 左边框
style.setBorderLeft(borderStyle);
// 上边框
style.setBorderTop(borderStyle);
// 右边框
style.setBorderRight(borderStyle);
// 生成字体
Font font = workbook.createFont();
font.setFontName("宋体");
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 粗体显示
font.setBold(false);
// 选择创建的字体格式
style.setFont(font);
return style;
}
