java中如何导入数据,如何用Java把数据从数据库导入到Excel中

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 jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException;

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

import com.mysql.jdbc.Connection; import com.mysql.jdbc.Statement;

/**  *

把数据导入到Excel公用类  * 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();    log.info("数据成功写入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)){      log.info("数据写入失败");     }    rs.close();    st.close();    conn.close();

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

}

}

代码片段：

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

*

把数据导入到Excel公用类

029

* This is about ExcelUtil

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

}