从数据库导出数据到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());

}

}