从数据库导出数据到Excel表(jxl.jar)

package com.*****.query;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import jxl.*;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

/**
* 对Excel表进行操作
*
* @author ********
*
*/
public class ExcelUtil {
private static Log log = LogFactory.getLog(ExcelUtil.class);

public boolean DB2Excel(ResultSet rs,OutputStream os) {
boolean flag = false;
WritableWorkbook workbook = null;
WritableSheet sheet = null;
Label label = null;

// 创建Excel表
try {
// workbook = Workbook.createWorkbook(new File("d:/output.xls"));
workbook = Workbook.createWorkbook(os);
// 创建Excel表中的sheet
sheet = workbook.createSheet("First Sheet", 0);

// 定义表中的数据
// Label label = new Label(0, 0, "k k 中国人*");
// Label label1 = new Label(0, 1, "A label record");
// Label label3 = new Label(0, 2, "3.14159");
// Number number = new Number(1, 1, 3.1459);

// 向Excel中添加数据

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
String colName = null;
int row = 0;
// 添加标题
for (int i = 0; i < columnCount; i++) {
colName = rsmd.getColumnName(i + 1);
label = new Label(i, row, colName);
// log.info("标题:"+i+"---"+row +"---"+ colName);
sheet.addCell(label);
}
row++;
log.info("写入标题成功");
while (rs.next()) {
for (int i = 0; i < columnCount; i++) {
label = new Label(i, row, rs.getString(i+1));
// log.info("行:"+i+"---"+row +"---"+ rs.getString(i+1));
sheet.addCell(label);
}
row++;
}
log.info("写入内容成功");

// 关闭文件
workbook.write();
workbook.close();
log.info("数据成功写入Excel");
flag = true;
} catch (SQLException e) {
log.info(e.getMessage());
} catch (RowsExceededException e) {
log.info(e.getMessage());
} catch (WriteException e) {
log.info(e.getMessage());
} catch (IOException e) {
log.info(e.getMessage());
} finally{
try {
workbook.close();
} catch (Exception e) {
}
}
return flag;
}

public static void main(String[] args) {
try {

Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection con = DriverManager
.getConnection(
"jdbc:jtds:sqlserver://localhost:1433/abc",
"sa", "sa");
Statement st = con.createStatement();
ResultSet rs = st
.executeQuery("select * from abc.dbo.sss");
// if (!new ExcelUtil().DB2Excel(rs)){
// log.info("数据写入失败");
// }
rs.close();
st.close();
con.close();

} catch (Exception e) {
e.printStackTrace();
}
}
}


在servlet中的使用

// 写入EXCEL
if (action != null &&amp; action.startsWith("excel.q")) {
try {
// response.setContentType("application/vnd.ms-Excel");
//设置文件输出成Excel文件,在网页上的点击链接,就会下载成report.xls
response.setHeader("Content-Disposition", "attachment; filename=report.xls");
st = con.createStatement();
rs = st.executeQuery(sql);
System.out.println(sql);
OutputStream os = response.getOutputStream();

if (!new ExcelUtil().DB2Excel(rs, os)) {
log.info("Excel写入失败");
}
os.flush();
os.close();
} catch (Exception e) {
log.info(e.getMessage());
}
}