java把数据从数据库导入到excel
- 格式：doc
- 大小：24.50 KB
- 文档页数：4
gb2j 123456
package com.madhouse.sys.util;
import java.io.File;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import jxl.Workbook;
import bel;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import mons.logging.Log;
import mons.logging.LogFactory;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
/**
*
* This is about ExcelUtil
*
* @author hjy273
* @version 1.0
* @date Sep 6, 2008 9:52:52 PM
*/
public class ExcelUtil {
private static Log log = LogFactory.getLog(ExcelUtil.class);
public ExcelUtil() {
}
public boolean DB2Excel(ResultSet rs) {
boolean flag = false; WritableWorkbook workbook = null;
WritableSheet sheet = null;
Label label = null;
// ´´½¨Excel±í
try {
workbook = Workbook.createWorkbook(new
File("e:/_report/output.csv"));
//workbook = Workbook.createWorkbook(os);
// ´´½¨Excel±íÖеÄsheet
sheet = workbook.createSheet("First Sheet", 0);
// Ïò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.debug("±êÌâ:"+i+"---"+row +"---"+ colName);
sheet.addCell(label);
}
row++;
log.debug("дÈë±êÌâ³É¹¦");
while (rs.next()) {
for (int i = 0; i < columnCount; i++) {
label = new Label(i, row, rs.getString(i + 1));
log.debug("ÐÐ:"+i+"---"+row +"---"+
rs.getString(i+1));
sheet.addCell(label);
}
row++;
}
log.debug("дÈëÄÚÈݳɹ¦");
// ¹Ø±ÕÎļþ
workbook.write();
workbook.close();
("Êý¾Ý³É¹¦Ð´ÈëExcel");
flag = true;
} catch (SQLException e) { log.debug(e.getMessage());
} catch (RowsExceededException e) {
log.debug(e.getMessage());
} catch (WriteException e) {
log.debug(e.getMessage());
} catch (IOException e) {
log.debug(e.getMessage());
} finally {
try {
workbook.close();
} catch (Exception e) {
}
}
return flag;
}
/**
* ²âÊÔ·½·¨
*
* @param args
*/
public static void main(String[] args) {
// write your code
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
Connection conn = (Connection) DriverManager
.getConnection(
"jdbc:mysql://localhost:3306/firewall?useUnicode=true&characterEncoding=utf8",
"root", "root");
Statement st = (Statement) conn.createStatement();
ResultSet rs = st
.executeQuery("select * from
firewall.tb_operator");
if (!new ExcelUtil().DB2Excel(rs)){
("Êý¾ÝдÈëʧ°Ü");
}
rs.close();
st.close();
conn.close();
} catch (Exception e) { e.printStackTrace();
}
}
}
