采用jxl实现数据库结果集导出到excel文件 ExcelSQLServletJDBCApache
程序员文章站
2024-02-23 12:20:28
...
采用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();
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();