1.将POI组件中的jar包放入项目中路径下的WEB-INF/lib目录中
2.在javabean中创建一个poi的java文件,中间出了创建getXXX(),setXXX()方法外,加入readRecord(int rsRow)方法,返回值是list集合(需要遍历);
3.必须创建一个HSSFSheet实例，使用POI组件中的createSheet("sheetname")创建工作表，根据显示内容大小创建指定大小的单元格。
4.最后创建一JSP页面在看到合同的同时还能打印下载到Excel
首先将POI组件中的jar包放入项目中路径下的WEB-INF/lib目录中，然后便可以在JSP中使用POI组件了。
新建一数据库连接类Conn.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
|
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.Statement;
public
class
Conn {
private
final
String dbDriver=
"com.mysql.jdbc.Driver"
;
private
final
String url=
"jdbc:mysql://localhost:3306/employee"
;
private
final
String userName =
"root"
;
private
final
String password =
"ldm"
;
private
Connection con=
null
;
Statement s=
null
;
public
Conn(){
try
{
Class.forName(dbDriver);
con=DriverManager.getConnection(url,userName,password);
}
catch
(Exception e){
System.out.println(e.getMessage());
}
}
public
ResultSet executeQuery(String sql){
ResultSet rs=
null
;
try
{
Conn conn=
new
Conn();
s=con.createStatement();
rs=s.executeQuery(sql);
}
catch
(Exception e){
e.printStackTrace();
}
return
rs;
}
public
void
close(){
try
{
if
(s!=
null
||con!=
null
){
s.close();
con.close();
}
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
为了将从数据库中取出的数据放入List,创建JavaBean,命名为POITest.java;这个JavaBean除了setXXX()和getXXX()方法之外，还有一个根据用户需要显示的行数从数据库中取值ReadRecord(int rsRow)方法，它的返回值类型是List集合，程序代码如下：
|
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
192
193
194
195
|
import
java.io.File;
import
java.io.FileOutputStream;
import
java.io.IOException;
import
java.sql.ResultSet;
import
java.util.List;
import
java.util.ArrayList;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
POITest {
private
int
id;
private
String studentCode;
private
String name;
private
String sex;
private
String institute;
private
String department;
private
String classes;
private
String phone;
private
String address;
public
int
getId() {
return
id;
}
public
void
setId(
int
id) {
this
.id = id;
}
public
String getStudentCode() {
return
studentCode;
}
public
void
setStudentCode(String studentCode) {
this
.studentCode = studentCode;
}
public
String getName() {
return
name;
}
public
void
setName(String name) {
this
.name = name;
}
public
String getSex() {
return
sex;
}
public
void
setSex(String sex) {
this
.sex = sex;
}
public
String getInstitute() {
return
institute;
}
public
void
setInstitute(String institute) {
this
.institute = institute;
}
public
String getDepartment() {
return
department;
}
public
void
setDepartment(String department) {
this
.department = department;
}
public
String getClasses() {
return
classes;
}
public
void
setClasses(String classes) {
this
.classes = classes;
}
public
String getPhone() {
return
phone;
}
public
void
setPhone(String phone) {
this
.phone = phone;
}
public
String getAddress() {
return
address;
}
public
void
setAddress(String address) {
this
.address = address;
}
public
static
List ReadRecord(
int
rsRow){
Conn conn=
new
Conn();
String sql=
"select * from student where id<='"
+rsRow+
"'"
;
ResultSet rs=conn.executeQuery(sql);
List l=
new
ArrayList();
try
{
while
(rs.next()){
POITest p=
new
POITest();
p.setId(rs.getInt(
"id"
));
p.setStudentCode(rs.getString(
"studentCode"
));
p.setName(rs.getString(
"name"
));
p.setSex(rs.getString(
"sex"
));
p.setInstitute(rs.getString(
"institute"
));
p.setDepartment(rs.getString(
"department"
));
p.setClasses(rs.getString(
"classes"
));
p.setPhone(rs.getString(
"phone"
));
p.setAddress(rs.getString(
"address"
));
l.add(p);
}
conn.close();
}
catch
(Exception e){
e.printStackTrace();
}
return
l;
}
public
static
List ReadRecord(){
Conn conn=
new
Conn();
//连接数据库
String sql=
"select * from student"
;
//查询学生信息
ResultSet rs=conn.executeQuery(sql);
//执行查询
List l=
new
ArrayList();
//定义List集合
try
{
while
(rs.next()){
POITest p=
new
POITest();
//实例化对象
p.setId(rs.getInt(
"id"
));
//为对象赋值
p.setStudentCode(rs.getString(
"studentCode"
));
p.setName(rs.getString(
"name"
));
p.setSex(rs.getString(
"sex"
));
p.setInstitute(rs.getString(
"institute"
));
p.setDepartment(rs.getString(
"department"
));
p.setClasses(rs.getString(
"classes"
));
p.setPhone(rs.getString(
"phone"
));
p.setAddress(rs.getString(
"address"
));
l.add(p);
//将对象添加到List集合中
}
conn.close();
//关闭数据库
}
catch
(Exception e){
e.printStackTrace();
}
return
l;
//返回集合
}
/*
* 为了添加工作表，必须创建一个HSSFSheet实例，使用POI组件中的createSheet("sheetname")创建工作表，根据显示内容大小创建指定大小的单元格。
* 程序代码如下：
*
*/
public
void
WriteCell(
int
rsRow){
HSSFWorkbook wb=
new
HSSFWorkbook();
HSSFSheet sheet=wb.createSheet(
"poiexample"
);
//建立名为poiexample的报表
sheet.setColumnWidth(
0
,
2500
);
sheet.setColumnWidth(
1
,
6000
);
sheet.setColumnWidth(
2
,
3500
);
sheet.setColumnWidth(
3
,
9000
);
sheet.setColumnWidth(
4
,
9000
);
sheet.setColumnWidth(
5
,
8000
);
sheet.setColumnWidth(
6
,
8000
);
sheet.setColumnWidth(
7
,
9000
);
sheet.setColumnWidth(
8
,
9000
);
HSSFRow row=sheet.createRow(
0
);
HSSFCell cell[]=
new
HSSFCell[
9
];
for
(
int
i=
0
;i<
9
;i++){
//遍历每行的每列
cell[i]=row.createCell(i);
}
cell[
0
].setCellValue(
"id号"
);
//在单元格写入文字
cell[
1
].setCellValue(
"学号"
);
cell[
2
].setCellValue(
"姓名"
);
cell[
3
].setCellValue(
"性别"
);
cell[
4
].setCellValue(
"系别"
);
cell[
5
].setCellValue(
"专业"
);
cell[
6
].setCellValue(
"班级"
);
cell[
7
].setCellValue(
"电话"
);
cell[
8
].setCellValue(
"地址"
);
List l=POITest.ReadRecord(rsRow);
if
(l.size()>
0
&& l!=
null
){
for
(
int
i=
0
;i<l.size();i++){
POITest p2=(POITest)l.get(i);
HSSFRow datarow=sheet.createRow(i+
1
);
HSSFCell data[]=
new
HSSFCell[
9
];
for
(
int
j=
0
;j<
9
;j++){
data[j]=datarow.createCell(j);
}
data[
0
].setCellValue(p2.getId());
data[
1
].setCellValue(p2.getStudentCode());
data[
2
].setCellValue(p2.getName());
data[
3
].setCellValue(p2.getSex());
data[
4
].setCellValue(p2.getInstitute());
data[
5
].setCellValue(p2.getDepartment());
data[
6
].setCellValue(p2.getClasses());
data[
7
].setCellValue(p2.getPhone());
data[
8
].setCellValue(p2.getAddress());
}
}
File file=
new
File(
"e:\\workbook.xls"
);
try
{
FileOutputStream fileOut=
new
FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
}
catch
(IOException e){
e.printStackTrace();
}
}
}
|
最后创建一JSP页面，命名为PoiTest.jsp;程序代码如下：
|
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
|
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*,com.wsy.POITest,java.io.*,java.io.FileOutputStream,
org.apache.poi.hssf.usermodel.*,java.util.*,java.text.*"%>
<
jsp:useBean
id
=
"poi"
scope
=
"page"
class
=
"com.wsy.POITest"
/>
<!
DOCTYPE
html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<
html
>
<
head
>
<
meta
http-equiv
=
"Content-Type"
content
=
"text/html; charset=UTF-8"
>
<
title
>学生管理系统</
title
>
</
head
>
<
script
type
=
"text/javascript"
language
=
"javascript"
>
function fn1(){
<%poi.WriteCell(9); %>
alert("导出报表完毕！导出位置为e:\\workbook.xls！！！");
}
</
script
>
<
body
>
<
table
border
=
"1"
>
<
tr
><
td
>ID号</
td
><
td
>学号</
td
><
td
>姓名</
td
><
td
>性别</
td
><
td
>系别</
td
><
td
>专业</
td
><
td
>班级</
td
><
td
>电话</
td
><
td
>地址</
td
></
tr
>
<% List l=poi.ReadRecord();
for(int i=0;i<
l.size
();i++){
POITest p2=(POITest)l.get(i);
%><
tr
>
<
td
><%=p2.getId() %><
td
>
<
td
><%=p2.getStudentCode() %></
td
>
<
td
><%=p2.getName()%></
td
>
<
td
><%=p2.getSex() %></
td
>
<
td
><%=p2.getInstitute() %></
td
>
<
td
><%=p2.getDepartment() %></
td
>
<
td
><%=p2.getClasses() %></
td
>
<
td
><%=p2.getPhone() %></
td
>
<
td
><%=p2.getAddress() %></
td
></
tr
>
<%
}
%>
</
table
><
br
/>
<
input
type
=
"button"
value
=
"导出报表"
onClick
=
'fn1()'
/>
</
body
>
</
html
>
|
重启Tomcat服务器，在IE浏览器中运行JSP页面；效果如下：
单击“导出报表”按钮，出现一个提示框
打开E盘，可以看到已经生成一个名为workbook.xls的文件，打开此文件，如下：
1710
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
