一.概念介紹
apachepoi是apache軟件基金會的開放源碼函式庫，poi提供api給java程序對microsoft office格式檔案讀和寫的功能
二.功能相關代碼
1.環境說明：jdk1.7+tomcat7+spring
2.配置文件的配置
pom文件中添加poi所需依賴
|
1
2
3
4
5
6
7
8
9
10
11
|
<!-- 添加poi支持 --> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi</artifactid> <version>3.13</version> </dependency> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>3.13</version> </dependency> |
spring-mvc.xml配置文件上傳
|
1
2
3
4
5
6
7
8
9
|
<bean id="multipartresolver" class="org.springframework.web.multipart.commons.commonsmultipartresolver"> <!-- 默認編碼 --> <property name="defaultencoding" value="utf-8" /> <!-- 文件大小最大值 --> <property name="maxuploadsize" value="10485760000" /> <!-- 內存中的最大值 --> <property name="maxinmemorysize" value="40960" /></bean> |
3.相關工具類及代碼編寫
excel解析工具類（importexcelutil.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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
package com.jointem.hrm.utils;import org.apache.poi.hssf.usermodel.hssfworkbook;import org.apache.poi.ss.usermodel.cell;import org.apache.poi.ss.usermodel.row;import org.apache.poi.ss.usermodel.sheet;import org.apache.poi.ss.usermodel.workbook;import org.apache.poi.ss.util.cellrangeaddress;import org.apache.poi.xssf.usermodel.xssfworkbook;import java.io.ioexception;import java.io.inputstream;import java.text.decimalformat;import java.text.simpledateformat;import java.util.arraylist;import java.util.list;/** * created by jenking on 2017/9/8. */public class importexcelutil { private final static string excel2003l =".xls"; //2003- 版本的excel private final static string excel2007u =".xlsx"; //2007+ 版本的excel /** * 描述：獲取io流中的數據，組裝成list<list<object>>對象 * @param in,filename * @return * @throws ioexception */ public list<list<object>> getbanklistbyexcel(inputstream in,string filename) throws exception{ list<list<object>> list = null; //創建excel工作薄 workbook work = this.getworkbook(in,filename); if(null == work){ throw new exception("創建excel工作薄為空！"); } sheet sheet = null; row row = null; cell cell = null; list = new arraylist<list<object>>(); //遍歷excel中所有的sheet for (int i = 0; i < work.getnumberofsheets(); i++) { sheet = work.getsheetat(i); if(sheet==null){continue;} //遍歷當前sheet中的所有行 system.out.println(sheet.getlastrownum()); for (int j = sheet.getfirstrownum(); j <=sheet.getlastrownum()-11; j++) { row = sheet.getrow(j);// if(row==null||row.getfirstcellnum()==j)// {// continue;// } //遍歷所有的列 list<object> li = new arraylist<object>(); for (int y = row.getfirstcellnum(); y < row.getlastcellnum(); y++) { cell = row.getcell(y); if(this.ismergedregion(sheet,j,y)) { li.add(this.getmergedregionvalue(sheet,j,y)); } else { li.add(this.getcellvalue(cell)); } } list.add(li); } } work.close(); return list; } /** * 描述：根據文件后綴，自適應上傳文件的版本 * @param instr,filename * @return * @throws exception */ public workbook getworkbook(inputstream instr,string filename) throws exception{ workbook wb = null; string filetype = filename.substring(filename.lastindexof(".")); if(excel2003l.equals(filetype)){ wb = new hssfworkbook(instr); //2003- }else if(excel2007u.equals(filetype)){ wb = new xssfworkbook(instr); //2007+ }else{ throw new exception("解析的文件格式有誤！"); } return wb; } /** * 描述：對表格中數值進行格式化 * @param cell * @return */ public object getcellvalue(cell cell){ object value = null; decimalformat df = new decimalformat("0"); //格式化number string字符 simpledateformat sdf = new simpledateformat("yyy-mm-dd"); //日期格式化 decimalformat df2 = new decimalformat("0"); //格式化數字 switch (cell.getcelltype()) { case cell.cell_type_string: value = cell.getrichstringcellvalue().getstring(); break; case cell.cell_type_numeric: if("general".equals(cell.getcellstyle().getdataformatstring())){ value = df.format(cell.getnumericcellvalue()); }else if("m/d/yy".equals(cell.getcellstyle().getdataformatstring())){ value = sdf.format(cell.getdatecellvalue()); }else{ value = df2.format(cell.getnumericcellvalue()); } break; case cell.cell_type_boolean: value = cell.getbooleancellvalue(); break; case cell.cell_type_blank: value = ""; break; default: break; } return value; } /** * 獲取合并單元格的內容 * @param sheet * @param row * @param column * @return */ public object getmergedregionvalue(sheet sheet, int row, int column) { int sheetmergecount = sheet.getnummergedregions(); for (int i = 0; i < sheetmergecount; i++) { cellrangeaddress ca = sheet.getmergedregion(i); int firstcolumn = ca.getfirstcolumn(); int lastcolumn = ca.getlastcolumn(); int firstrow = ca.getfirstrow(); int lastrow = ca.getlastrow(); if (row >= firstrow && row <= lastrow) { if (column >= firstcolumn && column <= lastcolumn) { row frow = sheet.getrow(firstrow); cell fcell = frow.getcell(firstcolumn); return this.getcellvalue(fcell); } } } return null; } /** * 判斷是否是合并單元格 * @param sheet * @param row * @param column * @return */ public boolean ismergedregion(sheet sheet,int row ,int column) { int sheetmergecount = sheet.getnummergedregions(); for (int i = 0; i < sheetmergecount; i++) { cellrangeaddress range = sheet.getmergedregion(i); int firstcolumn = range.getfirstcolumn(); int lastcolumn = range.getlastcolumn(); int firstrow = range.getfirstrow(); int lastrow = range.getlastrow(); if(row >= firstrow && row <= lastrow){ if(column >= firstcolumn && column <= lastcolumn){ return true; } } } return false; }} |
請求控制器（處理頁面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
|
package com.poiexcel.control; import java.io.inputstream; import java.io.printwriter; import java.util.list; import javax.servlet.http.httpservletrequest; import javax.servlet.http.httpservletresponse; import org.springframework.stereotype.controller; import org.springframework.web.bind.annotation.requestmapping; import org.springframework.web.bind.annotation.requestmethod; import org.springframework.web.bind.annotation.responsebody; import org.springframework.web.multipart.multipartfile; import org.springframework.web.multipart.multiparthttpservletrequest; import com.poiexcel.util.importexcelutil; import com.poiexcel.vo.infovo; @controller@requestmapping("/uploadexcel/*") public class uploadexcelcontrol { /** * 描述：通過傳統方式form表單提交方式導入excel文件 * @param request * @throws exception */ @requestmapping(value="upload.do",method={requestmethod.get,requestmethod.post}) public string uploadexcel(httpservletrequest request) throws exception { multiparthttpservletrequest multipartrequest = (multiparthttpservletrequest) request; inputstream in =null; list<list<object>> listob = null; multipartfile file = multipartrequest.getfile("upfile"); if(file.isempty()){ throw new exception("文件不存在！"); } in = file.getinputstream(); listob = new importexcelutil().getbanklistbyexcel(in,file.getoriginalfilename()); in.close(); //該處可調用service相應方法進行數據保存到數據庫中，現只對數據輸出 for (int i = 0; i < listob.size(); i++) { list<object> lo = listob.get(i); infovo vo = new infovo(); vo.setcode(string.valueof(lo.get(0))); vo.setname(string.valueof(lo.get(1))); vo.setdate(string.valueof(lo.get(2))); vo.setmoney(string.valueof(lo.get(3))); system.out.println("打印信息-->機構:"+vo.getcode()+" 名稱："+vo.getname()+" 時間："+vo.getdate()+" 資產："+vo.getmoney()); } return "result"; } |
前端代碼
前端運用了bootstrap的文件上傳組件fileinput,需要引入fileinput.css，fileinput.js，zh.js，bootstrap.css，bootstrap.js,jquery.min.js
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<body><h4>考勤信息錄入</h4><form method="post" enctype="multipart/form-data" id="form1" action="${pagecontext.request.contextpath }/attendance/uploadexcel"> <input id="file-zh" name="upfile" type="file" ></form></body><script> $('#file-zh').fileinput({ language: 'zh', uploadurl: '${pagecontext.request.contextpath }/attendance/uploadexcel', allowedfileextensions : ['xls', 'xlsx'] });</script> |
vo對象，保存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
|
package com.poiexcel.vo; //將excel每一行數值轉換為對象 public class infovo { private string code; private string name; private string date; private string money; public string getcode() { return code; } public void setcode(string code) { this.code = code; } public string getname() { return name; } public void setname(string name) { this.name = name; } public string getdate() { return date; } public void setdate(string date) { this.date = date; } public string getmoney() { return money; } public void setmoney(string money) { this.money = money; } } |
三.效果展示
1.頁面展示
2.后臺信息打印
四.總結
該例子只在控制臺對導入的數據進行了輸出，并沒有進行持久化。如果要持久化，只需在注釋的位置調用service層即可
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：https://blog.csdn.net/qq_32256235/article/details/78068865
