一、poi的定义
java中操作excel的有两种比较主流的工具包： jxl 和 poi 。jxl 只能操作excel 95, 97, 2000也即以.xls为后缀的excel。而poi可以操作excel 95及以后的版本，即可操作后缀为 .xls 和 .xlsx两种格式的excel。
poi全称 poor obfuscation implementation,直译为“可怜的模糊实现”，利用poi接口可以通过java操作microsoft office 套件工具的读写功能。官网：http://poi.apache.org ，poi支持office的所有版本，首先去官网下载如下界面：
下载完后，打开“poi-bin-3.15-20160924.tar.gz”获取操作excel需要的jar包，并将这些jar包复制到项目中。对于只操作2003 及以前版本的excel，只需要poi-3.15.jar ，如果需要同时对2007及以后版本进行操作则需要复制
poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar
以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar(但不知为何,我下的jar文件中没有dom4j.jar)这个文件,还是加上dom4j.jar,防止报错.
二、使用junit进行操作excel测试
首先明确excel工作簿对象、工作表对象、行对象、以及单元格对象。
具体代码如下注意要分清楚究竟是2007版本以前,还是2007版本以后(包括2007版本):下面这段代码是2007版本以前的:
这段代码只是将数据写入到excel文件中创建
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
public static void main(string[] args) throws exception { /** * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls * 07版本和07版本以后的做法excel文件的后缀名为.xlsx */ //创建新工作簿 hssfworkbook workbook = new hssfworkbook(); //新建工作表 hssfsheet sheet = workbook.createsheet("hello"); //创建行,行号作为参数传递给createrow()方法,第一行从0开始计算 hssfrow row = sheet.createrow(0); //创建单元格,row已经确定了行号,列号作为参数传递给createcell(),第一列从0开始计算 hssfcell cell = row.createcell(2); //设置单元格的值,即c1的值(第一行,第三列) cell.setcellvalue("hello sheet"); //输出到磁盘中 fileoutputstream fos = new fileoutputstream(new file("e:\\root\\sheet\\11.xls")); workbook.write(fos); workbook.close(); fos.close(); } |
结果如下图：
同样也可以对读取excel文件,得到excel文件的数据，并将其打印出来，代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@test public void testreadexcel() throws exception { //创建输入流 fileinputstream fis = new fileinputstream(new file("e:\\root\\sheet\\11.xls")); //通过构造函数传参 hssfworkbook workbook = new hssfworkbook(fis); //获取工作表 hssfsheet sheet = workbook.getsheetat(0); //获取行,行号作为参数传递给getrow方法,第一行从0开始计算 hssfrow row = sheet.getrow(0); //获取单元格,row已经确定了行号,列号作为参数传递给getcell,第一列从0开始计算 hssfcell cell = row.getcell(2); //设置单元格的值,即c1的值(第一行,第三列) string cellvalue = cell.getstringcellvalue(); system.out.println("第一行第三列的值是"+cellvalue); workbook.close(); fis.close(); } |
结果如下图：
上面操作的都是07版本以前的excel文件,即后缀名为.xls,07和07版本以后的excel文件后缀名为.xlsx相应的工作簿的对象名也改为:
|
1
2
|
//创建工作簿xssfworkbook workbook = new xssfworkbook(); |
代码如下,创建excel文件并保存数据到excel文件:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@test public void write07() throws exception { //创建工作簿 xssfworkbook workbook = new xssfworkbook(); //新建工作表 xssfsheet sheet = workbook.createsheet("hello"); //创建行,0表示第一行 xssfrow row = sheet.createrow(0); //创建单元格行号由row确定,列号作为参数传递给createcell;第一列从0开始计算 xssfcell cell = row.createcell(2); //给单元格赋值 cell.setcellvalue("hello sheet"); //创建输出流 fileoutputstream fos = new fileoutputstream(new file("e:\\root\\sheet\\hello.xlsx")); workbook.write(fos); workbook.close(); fos.close(); } |
与之对应的读取数据,代码如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
@test public void read07() throws exception { //创建输入流 fileinputstream fis = new fileinputstream(new file("e:\\root\\sheet\\hello.xlsx")); //由输入流得到工作簿 xssfworkbook workbook = new xssfworkbook(fis); //得到工作表 xssfsheet sheet = workbook.getsheet("hello"); //得到行,0表示第一行 xssfrow row = sheet.getrow(0); //创建单元格行号由row确定,列号作为参数传递给createcell;第一列从0开始计算 xssfcell cell = row.getcell(2); //给单元格赋值 string cellvalue = cell.getstringcellvalue(); system.out.println("c1的值是"+cellvalue); int a[][] = new int[10][30]; for(int i=0;i<a.length;i++) { system.out.println(i); } workbook.close(); fis.close(); } |
问题出现了,也可以解释为需求:当不能确定究竟是读取07以前(例如2003,95,97,2000)还是07版本以后的excel文件,我们当然希望程序能够自动识别,并创建相应的对象,去操作excel文件,代码如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
@test public void reda03and07() throws exception { //读取03或07的版本 string filepath = "e:\\root\\sheet\\hello.xlsx"; if(filepath.matches("^.+\\.(?i)((xls)|(xlsx))$")) { fileinputstream fis = new fileinputstream(filepath); boolean is03excell = filepath.matches("^.+\\.(?i)(xls)$")?true:false; workbook workbook = is03excell ? new hssfworkbook(fis):new xssfworkbook(fis); sheet sheet = workbook.getsheetat(0); row row = sheet.getrow(0); cell cell = row.getcell(2); system.out.println("第一行第一列的数据是:"+cell.getstringcellvalue()); } } |
学完了上面几个例子,接下来就是应用它了,我们经常需要在一个页面中批量导出和批量导出数据,这里就涉及到对excel文件的操作,当然还有其它的文件格式,我们使用一个llist<user> list 来保存,在这里我们写了一个excelutil这个工具类:代码如下:
|
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
|
package com.ittax.core.util;import java.util.list;import javax.servlet.servletoutputstream;import org.apache.poi.hssf.usermodel.hssfcell;import org.apache.poi.hssf.usermodel.hssfcellstyle;import org.apache.poi.hssf.usermodel.hssffont;import org.apache.poi.hssf.usermodel.hssfheader;import org.apache.poi.hssf.usermodel.hssfrow;import org.apache.poi.hssf.usermodel.hssfsheet;import org.apache.poi.hssf.usermodel.hssfworkbook;import org.apache.poi.ss.util.cellrangeaddress;import com.ittax.nsfw.user.entity.user;/** * excel工具类,支持批量导出 * @author lizewu * */public class excelutil { /** * 将用户的信息导入到excel文件中去 * @param userlist 用户列表 * @param out 输出表 */ public static void exportuserexcel(list<user> userlist,servletoutputstream out) { try{ //1.创建工作簿 hssfworkbook workbook = new hssfworkbook(); //1.1创建合并单元格对象 cellrangeaddress callrangeaddress = new cellrangeaddress(0,0,0,4);//起始行,结束行,起始列,结束列 //1.2头标题样式 hssfcellstyle headstyle = createcellstyle(workbook,(short)16); //1.3列标题样式 hssfcellstyle colstyle = createcellstyle(workbook,(short)13); //2.创建工作表 hssfsheet sheet = workbook.createsheet("用户列表"); //2.1加载合并单元格对象 sheet.addmergedregion(callrangeaddress); //设置默认列宽 sheet.setdefaultcolumnwidth(25); //3.创建行 //3.1创建头标题行;并且设置头标题 hssfrow row = sheet.createrow(0); hssfcell cell = row.createcell(0); //加载单元格样式 cell.setcellstyle(headstyle); cell.setcellvalue("用户列表"); //3.2创建列标题;并且设置列标题 hssfrow row2 = sheet.createrow(1); string[] titles = {"用户名","账号","所属部门","性别","电子邮箱"}; for(int i=0;i<titles.length;i++) { hssfcell cell2 = row2.createcell(i); //加载单元格样式 cell2.setcellstyle(colstyle); cell2.setcellvalue(titles[i]); } //4.操作单元格;将用户列表写入excel if(userlist != null) { for(int j=0;j<userlist.size();j++) { //创建数据行,前面有两行,头标题行和列标题行 hssfrow row3 = sheet.createrow(j+2); hssfcell cell1 = row3.createcell(0); cell1.setcellvalue(userlist.get(j).getname()); hssfcell cell2 = row3.createcell(1); cell2.setcellvalue(userlist.get(j).getaccount()); hssfcell cell3 = row3.createcell(2); cell3.setcellvalue(userlist.get(j).getdept()); hssfcell cell4 = row3.createcell(3); cell4.setcellvalue(userlist.get(j).isgender()?"男":"女"); hssfcell cell5 = row3.createcell(4); cell5.setcellvalue(userlist.get(j).getemail()); } } //5.输出 workbook.write(out); workbook.close(); //out.close(); }catch(exception e) { e.printstacktrace(); } } /** * * @param workbook * @param fontsize * @return 单元格样式 */ private static hssfcellstyle createcellstyle(hssfworkbook workbook, short fontsize) { // todo auto-generated method stub hssfcellstyle style = workbook.createcellstyle(); style.setalignment(hssfcellstyle.align_center);//水平居中 style.setverticalalignment(hssfcellstyle.vertical_center);//垂直居中 //创建字体 hssffont font = workbook.createfont(); font.setboldweight(hssffont.boldweight_bold); font.setfontheightinpoints(fontsize); //加载字体 style.setfont(font); return style; }} |
紧接着就是在useservice中调用方法并写出exportexcel方法:
|
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
|
@override public void exportexcel(list<user> userlist, servletoutputstream out) { // todo auto-generated method stub excelutil.exportuserexcel(userlist, out); } @override public void importexcel(file file, string excelfilename) { // todo auto-generated method stub //1.创建输入流 try { fileinputstream inputstream = new fileinputstream(file); boolean is03excel = excelfilename.matches("^.+\\.(?i)(xls)$"); //1.读取工作簿 workbook workbook = is03excel?new hssfworkbook(inputstream):new xssfworkbook(inputstream); //2.读取工作表 sheet sheet = workbook.getsheetat(0); //3.读取行 //判断行数大于二,是因为数据从第三行开始插入 if(sheet.getphysicalnumberofrows() > 2) { user user = null; //跳过前两行 for(int k=2;k<sheet.getphysicalnumberofrows();k++ ) { //读取单元格 row row0 = sheet.getrow(k); user = new user(); //用户名 cell cell0 = row0.getcell(0); user.setname(cell0.getstringcellvalue()); //账号 cell cell1 = row0.getcell(1); user.setaccount(cell1.getstringcellvalue()); //所属部门 cell cell2 = row0.getcell(2); user.setdept(cell2.getstringcellvalue()); //设置性别 cell cell3 = row0.getcell(3); boolean gender = cell3.getstringcellvalue() == "男"?true:false; user.setgender(gender); //设置手机 string mobile = ""; cell cell4 = row0.getcell(4); try { mobile = cell4.getstringcellvalue(); } catch (exception e) { // todo auto-generated catch block double dmoblie = cell4.getnumericcellvalue(); mobile = bigdecimal.valueof(dmoblie).tostring(); } user.setmobile(mobile); //设置电子邮箱 cell cell5 = row0.getcell(5); user.setemail(cell5.getstringcellvalue()); //默认用户密码是123456 user.setpassword("123456"); //用户默认状态是有效 user.setstate(user.user_state_valide); //保存用户 save(user); } } workbook.close(); inputstream.close(); } catch (exception e) { // todo auto-generated catch block e.printstacktrace(); } } |
最后就是在action中调用service方法:
|
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
|
//导出用户列表 public void exportexcel() { try { //1.查找用户列表 userlist = userservice.findobjects(); //2.导出 httpservletresponse response = servletactioncontext.getresponse(); //这里设置的文件格式是application/x-excel response.setcontenttype("application/x-excel"); response.setheader("content-disposition", "attachment;filename=" + new string("用户列表.xls".getbytes(), "iso-8859-1")); servletoutputstream outputstream = response.getoutputstream(); userservice.exportexcel(userlist, outputstream); if(outputstream != null) outputstream.close(); }catch(exception e) { e.printstacktrace(); } } public string importexcel() { if(userexcel!= null) { //判断是否是excel文件 if(userexcelfilename.matches("^.+\\.(?i)((xls)|(xlsx))$")) { userservice.importexcel(userexcel, userexcelfilename); } } return"list"; } |
注意的是应该使用servletoutputstream这个类,最后实现了批量导出和导入数据。
导出用户结果如下图;
导入结果如下图;
导入前:
导入后的结果;
ok,关于poi操作excel文件就暂时到此为止了
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持服务器之家！
原文链接：http://www.cnblogs.com/zhuixun/p/6600331.html
