java把数据从数据库导入到excel

[1].[代码]

view source

print ?

001	java把数据从数据库导入到excel

002

003

004	package com.madhouse.sys.util;

005

006	import java.io.File;

007	import java.io.IOException;

008

009	import java.sql.DriverManager;

010	import java.sql.ResultSet;

011	import java.sql.ResultSetMetaData;

012	import java.sql.SQLException;

013

014	import jxl.Workbook;

015	import jxl.write.Label;

016	import jxl.write.WritableSheet;

017	import jxl.write.WritableWorkbook;

018	import jxl.write.WriteException;

019	import jxl.write.biff.RowsExceededException;

020

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

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

023

024	import com.mysql.jdbc.Connection;

025	import com.mysql.jdbc.Statement;

026

027

/**

028	* <li>把数据导入到Excel公用类</li>

029	* </br> This is about <code>ExcelUtil</code>

030

*

031	* @author hjy273

032	* @version 1.0

033	* @date Sep 6, 2008 9:52:52 PM

034

*/

035	public class ExcelUtil {

036

037	private static Log log = LogFactory.getLog(ExcelUtil.class);

038

039	public ExcelUtil() {

040

041

}

042

043	public boolean DB2Excel(ResultSet rs) {

044	boolean flag = false;

045	WritableWorkbook workbook = null;

046	WritableSheet sheet = null;

047	Label label = null;

048

049	// 创建Excel表

050

try {

051	workbook = Workbook.createWorkbook(new File("e:/_report/output.csv"));

052	//workbook = Workbook.createWorkbook(os);

053

054	// 创建Excel表中的sheet

055	sheet = workbook.createSheet("First Sheet", 0);

056

057	// 向Excel中添加数据

058	ResultSetMetaData rsmd = rs.getMetaData();

059	int columnCount = rsmd.getColumnCount();

060	String colName = null;

061	int row = 0;

062

// 添加标题

063	for (int i = 0; i < columnCount; i++) {

064	colName = rsmd.getColumnName(i + 1);

065	label = new Label(i, row, colName);

066	// log.debug("标题:"+i+"---"+row +"---"+ colName);

067	sheet.addCell(label);

068

}

069

row++;

070	log.debug("写入标题成功");

071	while (rs.next()) {

072	for (int i = 0; i < columnCount; i++) {

073	label = new Label(i, row, rs.getString(i + 1));

074	log.debug("行:"+i+"---"+row +"---"+ rs.getString(i+1));

075	sheet.addCell(label);

076

}

077

row++;

078

}

079	log.debug("写入内容成功");

080

081

// 关闭文件

082	workbook.write();

083	workbook.close();

084	log.info("数据成功写入Excel");

085	flag = true;

086	} catch (SQLException e) {

087	log.debug(e.getMessage());

088	} catch (RowsExceededException e) {

089	log.debug(e.getMessage());

090	} catch (WriteException e) {

091	log.debug(e.getMessage());

092	} catch (IOException e) {

093	log.debug(e.getMessage());

094	} finally {

095

try {

096	workbook.close();

097	} catch (Exception e) {

098

}

099

}

100	return flag;

101

}

102

103

/**

104

* 测试方法

105

*

106	* @param args

107

*/

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

109	// write your code

110

try {

111

112	Class.forName("org.gjt.mm.mysql.Driver").newInstance();

113	Connection conn = (Connection) DriverManager

114	.getConnection(

115	"jdbc:mysql://localhost:3306/firewall?useUnicode=true&characterEncoding=utf8",

116	"root", "root");

117	Statement st = (Statement) conn.createStatement();

118	ResultSet rs = st

119	.executeQuery("select * from firewall.tb_operator");

120	if (!new ExcelUtil().DB2Excel(rs)){

121	log.info("数据写入失败");

122

}

123	rs.close();

124	st.close();

125	conn.close();

126

127	} catch (Exception e) {

128	e.printStackTrace();

129

}

130

131

}

132

133

}