最近在做一个J2EE项目,就用了下JXL,发现还挺好用。 于是乎封装了一个简单易用、通用、动态的从数据库导出到Excel的方法,可以动态的指定导出那些列,显示什么名字,按什么顺序显示;支持本地文件存储和JSP/Servlet文件下载。
下载地址:
1,http://jexcelapi.sourceforge.net/
2,http://andykhan.com/jexcelapi/index.html
本方法包括两个类,Column是辅助类,Excel是主类:
/**
* 用于Excel导出的辅助类,映射数据结果集(ResultSet)内列名的元数据和Excel内的显示列名
* Date: 2007-1-11
* Author: nescafe
*/
public class Column {
private int index;
private String metaName;
private String displayName;
/**
* 构造函数
* @param index 显示顺序,0 为显示的第一列
* @param meta 元列名,在ResultSet内的名字,必须大写
* @param display 显示列名,在Excel内的显示,可以是任何文字
*/
public Column(int index, String meta, String display){
this.index = index;
this.metaName = meta;
this.displayName = display;
}
/**
* 显示列名,在Excel内的显示,可以是任何文字
* @return
*/
public String getDisplayName() {
return displayName;
}
/**
* 显示顺序,0 为显示的第一列
* @return
*/
public int getIndex() {
return index;
}
/**
* 元列名,在ResultSet内的名字,必须大写
* @return
*/
public String getMetaName() {
return metaName;
}
public void setDisplayName(String displayName) {
this.displayName = displayName;
}
public void setIndex(int index) {
this.index = index;
}
public void setMetaName(String metaName) {
this.metaName = metaName;
}
}
/**
* 简单的Excel操作,完成与数据库的动态导出
* Date: 2007-1-11
* Author: nescafe
*/
public class Excel {
/**
* @param args
*/
public static void main(String[] args) {
File f=new File("c://kk.xls");
try {
f.createNewFile();
/*
此处初始化一个数据库连接池,pool
Connection可以用其他方式取得,不一定用pool
*/
Connection conn = pool.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("Select t.*, t.rowid from mis2_personal_weekly_job_plan t Where Rownum Between 1 And 2");
HashMap map = new HashMap();
map.put("ID", new Column(0, "ID", "编号"));
map.put("JOB_CONTENT", new Column(1, "JOB_CONTENT", "工作内容"));
map.put("JOB_TARGET", new Column(2, "JOB_TARGET", "工作目标"));
export(new FileOutputStream(f), null, rs, map);
}
catch (Exception e) {
e.printStackTrace();
}
}
/**
* 从数据库读数据,写入Excel
* @param os 数据流,如果是写本地文件的话,可以是FileOutputStream;
* 如果是写Web下载的话,可以是ServletOupputStream
* @param title Excel工作簿的标题,如果不用的话,可以写null或者""
* @param rs 数据结果集
* @param map 数据结果集对应Excel表列名映射:key对应数据结果集的列名,必须是大写;
* value,目前只能对应Column对象
* @throws Exception 方法内的父类异常有SQLException和IOException
*/
public static void export(OutputStream os, String title, ResultSet rs, Map map)throws Exception{
jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
jxl.write.WritableSheet wsheet = wbook.createSheet("第一页", 0); // sheet名称
jxl.write.WritableFont wfont = null; // 字体
jxl.write.WritableCellFormat wcfFC = null; // 字体格式
jxl.write.Label wlabel = null; // Excel表格的Cell
// 设置excel标题字体
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD,
false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfont);
// 添加excel标题
jxl.write.Label wlabel1 = new jxl.write.Label(5, 0, title, wcfFC);
wsheet.addCell(wlabel1);
// 设置列名字体
// 如果有标题的话,要设置一下偏移
int offset = 2;
if(title == null || title.trim().equals(""))
offset = 0;
else{
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD,
false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfont);
}
//根据原数据和map来创建Excel的列名
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
for(int i = 1; i <= count; i++){
String name = rsmd.getColumnName(i).toUpperCase();
if(map.containsKey(name)){
Column col = (Column)map.get(name);
wlabel = new jxl.write.Label(col.getIndex(), offset, col.getDisplayName());
wsheet.addCell(wlabel);
}
}
// 设置正文字体
wfont = new jxl.write.WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD,
false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfont);
//往Excel输出数据
int rowIndex = 1 + offset;
Collection array = map.values();
while(rs.next()){
Iterator it = array.iterator();
while(it.hasNext()){
Column col = (Column)it.next();
String value = rs.getString(col.getMetaName());
wlabel = new jxl.write.Label(col.getIndex(), rowIndex, value);
wsheet.addCell(wlabel);
}
rowIndex++;
}
wbook.write(); // 写入文件
wbook.close();
os.flush();
os.close();
}
}
//JSP页面的下载如下,同样的代码也可以改成Servlet的
<%
ConnectionPool pool = ConnectionPool.getInstance();
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
conn = pool.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("Select t.*, t.rowid from mis2_personal_weekly_job_plan t Where Rownum Between 1 And 2");
HashMap map = new HashMap();
map.put("ID", new Column(0, "ID", "编号"));
map.put("JOB_CONTENT", new Column(1, "JOB_CONTENT", "工作内容"));
map.put("JOB_TARGET", new Column(2, "JOB_TARGET", "工作目标"));
String fileName = "周工作计划.xls";
response.reset();
response.setContentType("application/vnd.ms-excel");
//response.addHeader("Content-Disposition","attachment;filename=" + fileName);
Excel.export(response.getOutputStream(), "", rs, map);
%>