对 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:1253950375@qq.com * 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
