一、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