一.概念介绍
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
