采用jxl实现数据库结果集导出到excel文件
采用jxl实现数据库结果集导出到excel文件
关键字:
jxl, excel, servlet
代码:
servlet:
import
jxl.WorkbookSettings;
import
jxl.Workbook;
import
jxl.write.WritableWorkbook;
import
jxl.write.WritableSheet;
import
jxl.write.Label;
import
jxl.write.WriteException;
import
org.springframework.web.context.WebApplicationContext;
import
org.springframework.web.context.support.WebApplicationContextUtils;
import
org.springframework.jdbc.core.JdbcTemplate;
import
org.springframework.jdbc.core.ResultSetExtractor;
import
org.springframework.jdbc.support.JdbcUtils;
import
org.springframework.dao.DataAccessException;
import
org.apache.commons.logging.Log;
import
org.apache.commons.logging.LogFactory;
import
org.apache.commons.lang.StringUtils;
import
org.apache.commons.lang.ArrayUtils;
import
javax.servlet.http.HttpServlet;
import
javax.servlet.http.HttpServletRequest;
import
javax.servlet.http.HttpServletResponse;
import
javax.servlet.ServletException;
import
javax.servlet.ServletConfig;
import
java.util.Locale;
import
java.util.HashMap;
import
java.util.Map;
import
java.io.IOException;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.ResultSetMetaData;
/** */
/**
*
Title:ExcelGenerator servlet
*
Description: 采用jxl实现数据库结果集导出到excel文件。
*
Copyright: Copyright.com (c) 2003
*
Company:
* History:
* create
*
*
@author
youlq
*
@version
1.0
*/
public
class
ExcelGenerator
extends
HttpServlet
{
//
设定每个Sheet的行数
private
int
pagesize
=
5000
;
private
WorkbookSettings workbookSettings
=
new
WorkbookSettings();
//
springframework 的 WebApplicationContext
public
static
WebApplicationContext wac
=
null
;
//
springframework 的 jdbc 操作模版类
public
static
JdbcTemplate jdbcTemplate
=
null
;
protected
final
Log logger
=
LogFactory.getLog(getClass());
/** */
/**
* 初始化
*
*
@param
config
*
@throws
ServletException
*/
public
void
init(ServletConfig config)
throws
ServletException
{
super
.init(config);
try
{
if
(
null
!=
getInitParameter(
"
pagesize
"
))
{
pagesize
=
Integer.parseInt(getInitParameter(
"
pagesize
"
));
}
workbookSettings.setLocale(Locale.getDefault());
wac
=
WebApplicationContextUtils.getRequiredWebApplicationContext(getServletContext());
jdbcTemplate
=
(JdbcTemplate)wac.getBean(
"
jdbcTemplate
"
);
}
catch
(Exception e)
{
logger.error(
"
ExcelGenerator init() error !
"
+
e, e.getCause());
e.printStackTrace();
}
}
public
String getServletInfo()
{
return
"
Servlet used to generate excel output
"
;
}
public
void
doGet(HttpServletRequest request, HttpServletResponse response)
throws
ServletException, IOException
{
generateExcel(request, response);
}
public
void
doPost(HttpServletRequest request, HttpServletResponse response)
throws
ServletException, IOException
{
generateExcel(request, response);
}
/** */
/**
* in:
* field1#Title&field2#Title&field3#Title
* out:
* {
* field1:Title,
* field2:Title
* field3:Title
* }
*
*
@param
columnTitle
*/
public
static
HashMap generateColumnTitleMap(String columnTitle)
{
HashMap map
=
new
HashMap();
String[] level1
=
StringUtils.split(columnTitle,
"
&
"
);
if
(ArrayUtils.isEmpty(level1))
return
null
;
for
(
int
i
=
0
;i
<
level1.length;i
++
)
{
String[] level2
=
StringUtils.split(level1[i],
"
#
"
);
if
(ArrayUtils.isEmpty(level2)
||
level2.length
!=
2
)
return
null
;
map.put(level2[
0
].toLowerCase(), level2[
1
]);
}
return
map;
}
public
void
generateExcel(HttpServletRequest request, HttpServletResponse response)
throws
ServletException, IOException
{
//
todo 只允许本机调用。
request.getRemoteHost();
request.getServerName();
response.setHeader(
"
Content-Disposition
"
,
"
attachment;
"
);
response.setContentType(
"
application/x-msdownload
"
);
String sql
=
(String)request.getSession().getAttribute(
"
ExcelGenerator_sql
"
);
String columnTitle
=
(String)request.getSession().getAttribute(
"
ExcelGenerator_columntitle
"
);
Map columnTitleMap
=
null
;
if
(StringUtils.isBlank(sql))
throw
new
ServletException(
"
sql 字符串为空!
"
);
if
(
!
StringUtils.isBlank(columnTitle))
{
columnTitleMap
=
generateColumnTitleMap(columnTitle);
if
(
null
==
columnTitleMap)
{
logger.error(
"
generateColumnTitleMap error !columnTitle=
"
+
columnTitle);
}
}
final
WritableWorkbook writableWorkbook
=
Workbook.createWorkbook(response.getOutputStream(), workbookSettings);
if
(jdbcTemplate
==
null
)
throw
new
ServletException(
"
ExcelGenerator 没有初始化成功!jdbcTemplate==null。
"
);
final
Map columnTitleMap1
=
columnTitleMap;
jdbcTemplate.query(sql,
new
ResultSetExtractor()
{
public
Object extractData(ResultSet rs)
throws
SQLException, DataAccessException
{
try
{
int
counter
=
0
;
int
page
=
1
;
WritableSheet writableSheet
=
writableWorkbook.createSheet(
"
第
"
+
page
+
"
页
"
,
0
);
ResultSetMetaData rsmd
=
rs.getMetaData();
int
columnCount
=
rsmd.getColumnCount();
String[] columnNames
=
new
String[columnCount];
for
(
int
i
=
1
;i
<=
columnCount;i
++
)
{
columnNames[i
-
1
]
=
rsmd.getColumnName(i).toLowerCase();
if
(columnTitleMap1
==
null
)
{
writableSheet.addCell(
new
Label(i
-
1
, counter, columnNames[i
-
1
]));
}
else
{
writableSheet.addCell(
new
Label(i
-
1
, counter, (String)columnTitleMap1.get(columnNames[i
-
1
])));
}
}
counter
=
1
;
Object oValue
=
null
;
String value
=
null
;
while
(rs.next())
{
//
row
for
(
int
i
=
1
;i
<=
columnCount;i
++
)
{
oValue
=
JdbcUtils.getResultSetValue(rs, i);
if
(oValue
==
null
)
{
value
=
""
;
}
else
{
value
=
oValue.toString();
}
writableSheet.addCell(
new
Label(i
-
1
, counter, value));
}
if
(counter
++>
pagesize)
{
counter
=
0
;
writableSheet
=
writableWorkbook.createSheet(
"
第
"
+
(
++
page)
+
"
页
"
,
0
);
}
}
}
catch
(WriteException e)
{
e.printStackTrace();
}
return
null
;
}
}
);
writableWorkbook.write();
try
{
writableWorkbook.close();
}
catch
(WriteException e)
{
logger.error(
"
writableWorkbook.close() error !
"
+
e, e.getCause());
e.printStackTrace();
}
}
}
web.xml
<
servlet
>
<
servlet-name
>
ExcelGenerator
SPAN style="COLOR: #800000">servlet-name
>
<
servlet-class
>
com.fsti.xmnms.web.servlet.ExcelGenerator
SPAN style="COLOR: #800000">servlet-class
>
<
init-param
>
<
param-name
>
pagesize
SPAN style="COLOR: #800000">param-name
>
<
param-value
>
5000
SPAN style="COLOR: #800000">param-value
>
SPAN style="COLOR: #800000">init-param
>
<
load-on-startup
>
3
SPAN style="COLOR: #800000">load-on-startup
>
SPAN style="COLOR: #800000">servlet
>
<
servlet-mapping
>
<
servlet-name
>
ExcelGenerator
SPAN style="COLOR: #800000">servlet-name
>
<
url-pattern
>
*.xls
SPAN style="COLOR: #800000">url-pattern
>
SPAN style="COLOR: #800000">servlet-mapping
>
测试页面:
@ page contentType
=
"
text/html;charset=GB2312
"
language
=
"
java
"
%>]]>
<
html
>
<
head
>
<
title
>
ExcelGenerator test
SPAN style="COLOR: #000000">title
>
SPAN style="COLOR: #000000">head
>
<
body
>
String
sql
=
"
select id,source_id,user_label from alarm_state
"
;
String
columntitle
=
"
id#ID&source_id#源设备&user_label#用户标签
"
;
session
.
setAttribute(
"
ExcelGenerator_sql
"
,
sql);
session
.
setAttribute(
"
ExcelGenerator_columntitle
"
,
columntitle);
response
.
sendRedirect(
"
asd.xls
"
);
%>]]>
SPAN style="COLOR: #000000">body
>
SPAN style="COLOR: #000000">html
>