一、背景
apache poi 是创建和维护操作各种符合office open xml（ooxml）标准和微软的ole 2复合文档格式（ole2）的java api。用它可以使用java读取和创建,修改ms excel文件.而且,还可以使用java读取和创建ms word和mspowerpoint文件。apache poi 提供java操作excel解决方案（适用于excel97-2008）。
根据指定格式的json文件生成对应的excel文件，需求如下
- 支持多sheet
- 支持单元格合并
- 支持插入图片
- 支持单元格样式可定制
- 需要 标题(title)，表头(head)，数据(data) ，表尾(foot) 明确区分
二、效果预览
三、数据格式
由于是生成excel文件，这里值考虑生成xlsx格式的excel文件，数据多表头默认考虑使用 | 表示，不在使用colspan rowspan作为。如需要表示两列两行，第一列合并表头格式为: a|b,a|c生成的表格为
| a | |
| b | c |
前端通过post的方式将需要生成的数据构造成符合要求的json文件提交跟后台。根据以上需求定义json格式如下
|
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
|
{"savename": "生成excel的文件名.xlsx","userstyles": [{"id": "1", //不能出现重复，在需要设置单元样式的地方，可以直接将style赋值为此值"style": {"font": { //设置字体基本格式"blod": true,//是否加粗"italic": true, //是否倾斜"color": "#ff0000",//字体颜色"name": "微软雅黑", //字体名称"height": 20 //大小},"fmtstr": "", //单元格格式，#,##0.00_);#,##0.00;0 千分位"align": "",//水平对齐方式 left right center"valign": "",//垂直对齐方式 top center bottom"bordercolor": "", //设置边框颜色 如 #ff0000"bgcolor": "" //设置单元格填充颜色}}],"sheets": [{"sheetname": "", //sheet名称"title": [], // 对应sheet标题区域数据"titlemerge": [], //对应sheet标题区域合并信息"head": [{}], //表头信息"data": [], //数据信息"datamerge": [], //数据合并信息"foot": [], //表尾信息"footmerge": [], //表尾合并信息"img": [] //图片信息，需要将图片转换base64}]} |
简要说明
head 数组中为json对象格式为
|
1
2
3
4
5
6
7
8
|
{"name": "a|b", //表头名称，多表头用|分割"type": "str", //此列数据类型 str num ,在excel中日期也是数字类型，通过fmtstr,显示为日期格式"field": "f_field1", //备用字段，可不用"style": { //此列数据为列默认样式，可以是style对象，也可以是在userstyles中定义的id值"align": "center"}} |
在数组 title data foot 中，列表中的数据，可以是一个单独的值如 1,”a”，也可以是一个对象，当为对象时，格式为
|
1
2
3
4
5
|
{"value": "", //单元格具体的值"type": "", //单元格类型，默认str"style": {} //单元格样式 可以是style对象，也可以是在userstyles中定义的id值，如果没设置，默认取head总此列对应的style} |
titlemerge、datamerge、footmerge数组值为逗号分隔的字符串，其含义为 "开始行，结束行，开始列，结束列"，索引从0开始。如在title中有两行三列数据，现在需要合并一行两列数据对应的值为"0，0，0，1"
img数组中值为对象，格式
|
1
2
3
4
5
6
7
|
{"col": 1, //图片开始列"row": 0, //开始行"colspan": 1,//列跨度，最小值1"rowspan": 2, //行跨度，最小值1"data": "" //base64图片数据如: "data:image/png;base64,ivbo...ggg=="} |
四、关键实现
07以后的excle文件，其实是一个压缩包，里边是一个个的xml文件，其中每一个sheet是一个xml文件，样式是一个xml文件，图片是对应的图片文件，放在media文件夹中，所以，代码思路依次为
- 构建 xssfworkbook 对象
- 生成样式
- 依次生成，title head data foot 行数据
- 依次处理合并信息 titlemerge datamerge footmerge
- 添加图片信息
- 输出文件流
功能入口如下
|
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
|
@overridepublic void buildoutputstream() throws fileproducerexception {// 处理传入的json数据sheets = this.jsondata.getjsonarray(this.sheets);iterator<object> sheetiter = sheets.iterator();if (sheets.isempty()) {this.responsedata.seterrcode(1001);this.responsedata.setsuccess(false);this.responsedata.seterrmsg("无数据可生成");throw new fileproducerexception();}wb = new xssfworkbook();// 建立全局格式jsonarray userstyles = this.jsondata.getjsonarray(this.userstyles);this.inituserstyles(userstyles);this.initdefaultheadstyle(); xssfsheet ws;jsonobject sheet;jsonarray sheetdata;jsonarray sheettitle;jsonarray sheethead;jsonarray sheetfoot;jsonarray sheetimgs; string sheetname;int sheetindex = 0;while (sheetiter.hasnext()) {sheet = (jsonobject) sheetiter.next();// 获取sheet名称sheetname = sheet.getstring(this.sheet_name);ws = wb.createsheet();if (stringutils.isnotblank(sheetname)) {wb.setsheetname(sheetindex, sheetname);}int sheetrowindex = 0;sheettitle = sheet.getjsonarray(this.sheet_title);this.setmergecells(ws, sheet.getjsonarray(this.sheet_title_merge),sheetrowindex);sheetrowindex = this.createrandom(ws, sheettitle, sheetrowindex); sheethead = sheet.getjsonarray(this.sheet_head);sheetrowindex = this.createheadcolumn(ws, sheethead, sheetrowindex); this.setmergecells(ws, sheet.getjsonarray(this.sheet_data_merge),sheetrowindex);sheetdata = sheet.getjsonarray(this.sheet_data);sheetrowindex = this.createdata(ws, sheetdata, sheetrowindex); sheetfoot = sheet.getjsonarray(this.sheet_foot);this.setmergecells(ws, sheet.getjsonarray(this.sheet_foot_merge),sheetrowindex);sheetrowindex = this.createrandom(ws, sheetfoot, sheetrowindex); sheetimgs = sheet.getjsonarray(this.sheet_img); this.setsheetimages(ws, sheetimgs);} // 返回输出流try {bytearrayoutputstream os = new bytearrayoutputstream();wb.write(os);this.outstreams.add(os);} catch (ioexception e) {throw new fileproducerexception(e.getmessage(), e.getcause());}} |
生成单元格样式对象，包括字体 边框 背景 对齐方式
|
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
|
private xssfcellstyle createcellstyle(jsonobject style) { xssfcellstyle cellstyle = wb.createcellstyle();// 设置字体jsonobject font = style.getjsonobject(this.style_font);font excelfont = this.createfont(font);if (excelfont != null) {cellstyle.setfont(excelfont);}// border统一黑色cellstyle.setborderbottom(borderstyle.thin);cellstyle.setbordertop(borderstyle.thin);cellstyle.setborderleft(borderstyle.thin);cellstyle.setborderright(borderstyle.thin); string bordercolor = style.getstring(this.border_color);if (stringutils.isnotblank(bordercolor)) {xssfcolor xfbordercolor = new xssfcolor(new color(integer.parseint(bordercolor.substring(1), 16)));cellstyle.setbordercolor(borderside.bottom, xfbordercolor);cellstyle.setbordercolor(borderside.top, xfbordercolor);cellstyle.setbordercolor(borderside.left, xfbordercolor);cellstyle.setbordercolor(borderside.right, xfbordercolor);}// 背景色string bgcolor = style.getstring(this.background_color);if (stringutils.isnotblank(bgcolor)) {xssfcolor cellbgcolor = new xssfcolor(new color(integer.parseint(bgcolor.substring(1), 16)));cellstyle.setfillforegroundcolor(cellbgcolor);cellstyle.setfillpattern(fillpatterntype.solid_foreground);}// 对齐方式string halignment = style.getstring(this.halignment);if (stringutils.isnotblank(halignment))cellstyle.setalignment(horizontalalignment.valueof(halignment.touppercase()));string valignment = style.getstring(this.valignment);if (stringutils.isnotblank(valignment))cellstyle.setverticalalignment(verticalalignment.valueof(valignment.touppercase()));// 自动换行truecellstyle.setwraptext(true); // 格式string fmt = style.getstring(this.fmtstring);if (stringutils.isnotblank(fmt))cellstyle.setdataformat(wb.createdataformat().getformat(fmt));return cellstyle;} |
创建字体样式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
private font createfont(jsonobject fontcfg) {if (fontcfg == null)return null;xssffont font = wb.createfont();font.setfontname(fontcfg.getstring(this.font_name));boolean fontboole = fontcfg.getboolean(font_blod);if (fontboole != null)font.setbold(fontboole.booleanvalue());fontboole = fontcfg.getboolean(this.font_italic);if (fontboole != null)font.setitalic(fontboole.booleanvalue());fontboole = fontcfg.getboolean(this.font_underline);if (fontboole != null && fontboole.booleanvalue() == true)font.setunderline(fontunderline.single.getbytevalue());short fontheight = fontcfg.getshort(this.font_height);if (fontheight != null)font.setfontheightinpoints(fontheight);string colorstr = fontcfg.getstring(this.font_color);if (colorstr != null) {font.setcolor(new xssfcolor(new color(integer.parseint(colorstr.substring(1), 16))));}return font;} |
处理表头,表过多表头处理，采用 | 分割的方式，传入head长度为列数据，name中有几个 | 就知道表头有几行。所以针对表头处理有以下几个步骤
- 生成默认列样式
- 填充所有列数据，求出最大行数
- 横向合并内容相同的单元
- 纵向合并空白的单元格
|
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
|
private int createheadcolumn(xssfsheet ws, jsonarray sheethead,int sheetrowindex) {if (sheethead == null)return sheetrowindex;iterator<object> headiter = sheethead.iterator();jsonobject curhead = null;int colindex = 0;object colstyle = null;int colsize = sheethead.size();headtypes = new string[colsize];headcellstylekeys = new string[colsize];int[] headcollevel = new int[colsize];string colname = null;string[] colnameary = null;int maxlevel = 0;int collevel = 0;xssfcell headcell = null;arraylist<arraylist<string>> headvaluelist = new arraylist<arraylist<string>>();while (headiter.hasnext()) {curhead = (jsonobject) headiter.next();// 处理默认样式if (curhead.containskey(this.column_style)) {colstyle = curhead.get(this.column_style);if (colstyle instanceof jsonobject) {headcellstylekeys[colindex] = this.columnstyle_prev+ colindex;this.userstyles.put(headcellstylekeys[colindex],this.createcellstyle((jsonobject) colstyle));} else if (this.userstyles.containskey(colstyle)) {headcellstylekeys[colindex] = (string) colstyle;}}// 处理默认列宽if (curhead.containskey(this.column_width)) {ws.setdefaultcolumnwidth(pixtoexcelwdith(curhead.getintvalue(this.column_width)));}// 保存列样式if (curhead.containskey(this.column_type)) {headtypes[colindex] = curhead.getstring(this.column_type);} else {headtypes[colindex] = this.celltypestring;}// 处理多表头colname = curhead.getstring(this.column_name);colnameary = colname.split("\\|");collevel = colnameary.length;headcollevel[colindex] = collevel;if (collevel > maxlevel) {maxlevel = collevel;}for (int i = 0; i < collevel; i++) {if (headvaluelist.size() <= i) {headvaluelist.add(new arraylist<string>());}headvaluelist.get(i).add(colindex, colnameary[i]);xssfrow row = ws.getrow(sheetrowindex + i);if (row == null) {row = ws.createrow(sheetrowindex + i);}headcell = row.createcell(colindex);headcell.setcellvalue(colnameary[i]);headcell.setcellstyle(this.userstyles.get(this.headstyle_key));}colindex++;} // 横向合并iterator<arraylist<string>> a = headvaluelist.iterator();jsonarray headmerge = new jsonarray();string prev = "";string curent = null;int lrowindex = 0;int startcol = 0;int mergecol = 0;arraylist<string> columninfo = null;while (a.hasnext()) {startcol = 0;mergecol = 0;prev = "";columninfo = a.next();// 第三列才能知道，第一列和第二列是否合并columninfo.add("");iterator<string> b = columninfo.iterator();xssfcell lastrowcell = null;while (b.hasnext()) {curent = b.next();if (lrowindex > 0) {lastrowcell = ws.getrow(sheetrowindex + lrowindex - 1).getcell(startcol);}if (prev.equalsignorecase(curent) && lrowindex == 0) {ws.getrow(sheetrowindex + lrowindex).getcell(startcol).setcelltype(cell.cell_type_blank);mergecol++;} else if (prev.equalsignorecase(curent)&& lrowindex > 0&& stringutils.isblank(lastrowcell.getstringcellvalue())) {ws.getrow(sheetrowindex + lrowindex).getcell(startcol).setcelltype(cell.cell_type_blank);mergecol++;} else {if (mergecol > 0 && startcol > 0) {headmerge.add(string.format("%d,%d,%d,%d", lrowindex,lrowindex, startcol - mergecol - 1,startcol - 1));mergecol = 0;}}startcol++;prev = curent;}lrowindex++;}for (int i = 0; i < colsize; i++) {if (headcollevel[i] < maxlevel) { // 存在列合并headmerge.add(string.format("%d,%d,%d,%d", headcollevel[i] - 1,maxlevel - 1, i, i));for (int r = headcollevel[i]; r < maxlevel; r++) {ws.getrow(sheetrowindex + r).createcell(i).setcellstyle(this.userstyles.get(this.headstyle_key));}}} this.setmergecells(ws, headmerge, sheetrowindex);return sheetrowindex + maxlevel;} |
添加图片,默认采用单元格描点方式，将图片固定指定的单元格区域内
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
private void addimg(xssfsheet ws, jsonobject img, xssfcreationhelper chelper) {string imgbase64 = img.getstring(this.sheet_img_data);if (stringutils.isblank(imgbase64))return;string[] imgary = imgbase64.split(",");system.out.println(imgary[0]);byte[] imgbyte = base64.decodebase64(imgary[1]);int imgidx = wb.addpicture(imgbyte, workbook.picture_type_jpeg);xssfdrawing drawimg = ws.createdrawingpatriarch();xssfclientanchor anchor = chelper.createclientanchor();int col = img.getintvalue(this.sheet_img_col);int row = img.getintvalue(this.sheet_img_row);anchor.setcol1(col);anchor.setrow1(row);xssfpicture pict = drawimg.createpicture(anchor, imgidx);integer colspan = img.getinteger(this.sheet_img_colspan);if (colspan == null)colspan = 1;integer rowspan = img.getinteger(this.sheet_img_rowspan);if (rowspan == null)rowspan = 1;pict.resize(colspan, rowspan);} |
五、总结
这次通过传入json对象生成样式丰富的excel文件，对于poi操作office文档又更加熟悉一些。相对于解析excel文档，生成就不用考虑文件格式，如：兼容2003格式，考虑大文件sax方式解析。相对于js前端生成excel文件，增加了对生成后文件二次加工的可能性，所以在功能入口中，采用了生成二进制流的方式。文件生成好后，可以继续发送邮件，上传ftp等操作。
重点说明
- 对于各数据区域数据，保持区域数据独立性（数据索引值）
- 对于图片开始行和开始列，索引值是针对一个完整的sheet
- 对于表头区域，多表头采用 | 分割，减少部分传输数据
- excel中style为所有sheet共享样式。
好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：https://www.cnblogs.com/yfrs/p/poiexcel.html
