對 excel 進行讀寫操作是生產環境下常見的業務，網上搜索的實現方式都是基于poi和jxl第三方框架，但都不是很全面。小編由于這兩天剛好需要用到，于是就參考手寫了一個封裝操作工具，基本涵蓋了excel表（分有表頭和無表頭）的創建，并對它們進行讀寫操作。為方便大家，有需要者可以點擊文后點解下載直接使用哦，當然也可以根據自己需求舉一反三自己定制，相信對于聰明的你也不是什么難事。話不多說，直接貼源碼
pom.xml 文件：
java" id="highlighter_259810">
|
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
|
<properties> <project.build.sourceencoding>utf-8</project.build.sourceencoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency> <groupid>junit</groupid> <artifactid>junit</artifactid> <version>4.11</version> <scope>test</scope> </dependency> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi</artifactid> <version>3.17</version> </dependency> <dependency> <groupid>org.projectlombok</groupid> <artifactid>lombok</artifactid> <version>1.18.0</version> <scope>provided</scope> </dependency> <dependency> <groupid>org.slf4j</groupid> <artifactid>slf4j-log4j12</artifactid> <version>1.8.0-beta2</version> <scope>test</scope> </dependency> <dependency> <groupid>log4j</groupid> <artifactid>log4j</artifactid> <version>1.2.17</version> </dependency> <dependency> <groupid>org.slf4j</groupid> <artifactid>slf4j-api</artifactid> <version>1.8.0-beta2</version> </dependency> </dependencies> |
建表工具類：excelbuider.java
|
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
|
/** * 建表工具類 * @author sherman * email:[email protected] * created in 2018/8/24 */ @slf4j public class excelbuilder { private static hssfsheet sheet; private static hssfworkbook wb; private static boolean hasheader; /** * 初始化 * @param excellname 表名 */ public excelbuilder(string excellname) { wb = new hssfworkbook(); sheet = wb.createsheet(excellname); } /** * 設置表頭，裝配表頭數據 * @param value 字符串數組，用來作為表頭的值 * */ public excelbuilder header(string... value) { if (value != null && value.length != 0) { //設置表頭樣式 hssfcellstyle cellstyle = wb.createcellstyle(); cellstyle.setfont(font("黑體", true, 12)); hssfrow row = sheet.createrow(0); for (int i = 0; i < value.length; i++) { hssfcell cell = row.createcell(i); cell.setcellvalue(value[i]); cell.setcellstyle(cellstyle); } hasheader = true; } return this; } /** * excel 表內容裝配 * @param content 待裝配表格內容的二維數組 * @return */ public excelbuilder content(list<list<object>> content) { if (content != null && !content.isempty()) { int index; for (int i = 0; i < content.size(); i++) { index = hasheader == false ? i : i + 1; hssfrow row = sheet.createrow(index); for (int j = 0; j < content.get(i).size(); j++) { string r = ""; object value = content.get(i).get(j); //根據數據類型裝配 if (value instanceof string) { r = (string) value; } else if (value instanceof number) { r = string.valueof(value); } else if (value instanceof bigdecimal) { r = string.valueof(value); } else { if (!(value instanceof date) && !(value instanceof timestamp)) { if (!(value instanceof zoneddatetime) && !(value instanceof localdatetime)) { if (value instanceof enum) { r = ((enum) value).name(); } else if (value != null) { log.info("error of create row, unknow field type: " + value.getclass().getname()); } } else { datetimeformatter formatter = datetimeformatter.ofpattern("yyyy-mm-dd"); r = formatter.format((temporalaccessor) value); } } else { dateformat sdf = new simpledateformat("yyyy-mm-dd"); r = sdf.format(value); } } row.createcell(j).setcellvalue(r); } } } return this; } /** * 自動調整列寬大小 */ public excelbuilder autocolumnwidth() { for (int j = 0; j < sheet.getrow(0).getlastcellnum(); j++) { int maxlength = 0; for (int i = 0; i <= sheet.getlastrownum(); i++) { string value = sheet.getrow(i).getcell(j).getstringcellvalue(); int length = 0; if (value != null) { length = value.getbytes().length; } if (length > maxlength) { maxlength = length; } } sheet.setcolumnwidth(j, maxlength > 30 ? (30 * 256 + 186) : (maxlength * 256 + 186)); } return this; } /** * 實例化 * @param hasheader 是否有表頭 * @return excel表格 */ public abstractexcel build(boolean hasheader) { return hasheader ? new headerexcel(sheet) : new noheaderexcel(sheet); } /** * * @param fontname 字體名字 * @param isbold 是否粗體 * @param fontsize 字體大小 * @return 字體 */ private hssffont font(string fontname, boolean isbold, int fontsize) { hssffont font = wb.createfont(); if (fontname != null) font.setfontname(fontname); else font.setfontname("黑體"); font.setbold(isbold); font.setfontheightinpoints((short) fontsize); return font; }} |
excel的抽象父類：
|
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
|
/** * @author sherman * created in 2018/8/24 */public abstract class abstractexcel { private final hssfsheet sheet; public abstractexcel() { hssfworkbook wb = new hssfworkbook(); sheet = wb.createsheet(); } public abstractexcel(string sheetname){ hssfworkbook wb = new hssfworkbook(); sheet = wb.createsheet(sheetname); } public abstractexcel(hssfsheet sheet) { this.sheet = sheet; } public abstract list<map<string, string>> getpayload(); public void write(outputstream op) throws ioexception { sheet.getworkbook().write(op); sheet.getworkbook().close(); } public string getstringformatcellvalue(hssfcell cell) { string cellval = ""; decimalformat df = new decimalformat("#"); switch (cell.getcelltypeenum()) { case string: cellval = cell.getstringcellvalue(); break; case numeric: string dataformat = cell.getcellstyle().getdataformatstring(); if (dateutil.iscelldateformatted(cell)) { cellval = df.format(cell.getdatecellvalue()); } else if ("@".equals(dataformat)) { cellval = df.format(cell.getnumericcellvalue()); } else { cellval = string.valueof(cell.getnumericcellvalue()); df = new decimalformat("#.#########"); cellval = df.format(double.valueof(cellval)); } break; case boolean: cellval = string.valueof(cell.getbooleancellvalue()); break; case formula: cellval = string.valueof(cell.getcellformula()); break; default: cellval = ""; } return cellval; }} |
有表頭實現類
|
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
|
/** * @author sherman * created in 2018/8/24 */public class headerexcel extends abstractexcel { private final static boolean hasheader = true; private final hssfsheet sheet; public headerexcel(hssfsheet sheet) { super(sheet); this.sheet = sheet; } public headerexcel(string sheetname, string excelpath) { hssfworkbook wb = null; try { wb = new hssfworkbook(new poifsfilesystem(new fileinputstream(excelpath))); } catch (ioexception e) { e.printstacktrace(); } sheet = sheetname == null || sheetname.isempty() ? wb.getsheetat(0) : wb.getsheet(sheetname); } @override public list<map<string, string>> getpayload() { list<map<string, string>> payload = new arraylist<>(); hssfrow headrow = sheet.getrow(0); for (int i = 1; i <= sheet.getlastrownum(); i++) { hssfrow currentrow = sheet.getrow(i); map<string, string> map = new hashmap<>(); for (int j = 0; j < sheet.getrow(i).getlastcellnum(); j++) { map.put(getstringformatcellvalue(headrow.getcell(j)), getstringformatcellvalue(currentrow.getcell(j))); } payload.add(map); } return payload; }} |
無表頭實現類
|
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
|
/** * @author sherman * created in 2018/8/24 */public class noheaderexcel extends abstractexcel { private final static boolean hasheader = false; private hssfsheet sheet; public noheaderexcel(hssfsheet sheet) { super(sheet); this.sheet = sheet; } public noheaderexcel(string sheetname, string excelpath) { hssfworkbook wb = null; try { wb = new hssfworkbook(new poifsfilesystem(new fileinputstream(excelpath))); } catch (ioexception e) { e.printstacktrace(); } sheet = sheetname == null || sheetname.isempty() ? wb.getsheetat(0) : wb.getsheet(sheetname); } @override public list<map<string, string>> getpayload() { list<map<string, string>> payload = new arraylist<>(); for (int i = 0; i < sheet.getlastrownum(); i++) { hssfrow currentrow = sheet.getrow(i); map<string, string> map = new hashmap<>(); for (int j = 0; j <= sheet.getrow(i).getlastcellnum(); j++) { map.put(string.valueof(j), getstringformatcellvalue(currentrow.getcell(j))); } payload.add(map); } return payload; }} |
測試工具類：
|
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
|
/** * unit test for simple app. */public class apptest { /** * 測試建表，寫表操作 */ @test public void testexportexcel() { //測試數據 string[] headers = new string[]{"a","b","c","d","e"}; list<list<object>> valuelist = new linkedlist<>(); for (char i = 'a'; i <= 'e' ; i++) { list<object> rowlist = new linkedlist<>(); for (int j = 0; j <= 4; j++) { rowlist.add(i+string.valueof(j)); } valuelist.add(rowlist); } abstractexcel excel = new excelbuilder("報名表") .header(headers) .content(valuelist) .autocolumnwidth() .build(true); try { file file = new file("e:\\excel\\test.xls"); fileoutputstream op = new fileoutputstream(file); excel.write(op); } catch (ioexception e) { e.printstacktrace(); } } /** * 測試讀取表數據操作 */ @test public void testimportexcel(){ abstractexcel excel = new headerexcel(null,"e:/excel/test.xls"); list<map<string,string>> values = excel.getpayload(); values.foreach(stringstringmap -> { stringstringmap.entryset().foreach(stringstringentry -> { system.out.println(stringstringentry.getkey()+"---->"+stringstringentry.getvalue()); }); }); }} |
附圖：
測試1
測試二：
看起來效果還不錯，當然還有很多不完善的地方，有需要的朋友可以在此基礎上擴展定制，例如讀取表數據結構方式，實現行數增刪改查據或者創建表標題等等。
或者有朋友有更好的實現方案，歡迎前來交流！
最后的最后，當然忘不了附上笨工具的源碼啦！
原文鏈接：https://www.cnblogs.com/yumiaoxia/p/9545750.html
