一、背景
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
