从数据库读取数据到Excel

直接上代码：

<?xml version="1.0" encoding="UTF-8"?>
<beans>
	<table classname="classname" class="com.lt.util.policy.matching.vo.FlightCourseVo" tablename="b_flight_course">
		<property name="id" column="id" type="java.lang.Integer"></property>

		<property name="airlineCode" column="airline_code" type="java.lang.String"></property>

		<property name="departureAirport" column="departure_airport" type="java.lang.String"></property>

		<property name="arrivalAirport" column="arrival_airport" type="java.lang.String"></property>

		<property name="flightNo" column="flight_no" type="java.lang.String"></property>

		<property name="type" column="type" type="java.lang.Integer"></property>

		<property name="price" column="price" type="java.lang.Double"></property>

		<property name="distance" column="distance" type="java.lang.Double"></property>

		<property name="international" column="international" type="java.lang.Integer"></property>

		<property name="startDate" column="start_date" type="java.util.Date"></property>

		<property name="expirationDate" column="expiration_date" type="java.util.Date"></property>

		<property name="flightCourse" column="flight_course" type="java.lang.String"></property>
	</table>
</beans>

package com.matching;

import java.io.File;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;

import com.lt.util.policy.matching.vo.FlightCourseVo;

/**
 * 
 * @author guyanming
 * @time 2013-03-22 17:20:00
 * 
 */
public class WriteDBToExcel {

    public static List<Map<String, String>> getBFlightCourse(String XmlName) throws Exception {
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        Map<String, String> map = new HashMap<String, String>();
        Map<String, String> mapnum = new HashMap<String, String>();
        // 参数类型
        Map<String, String> maptype = new HashMap<String, String>();
        SAXBuilder saxBuilder = new SAXBuilder();
        String currenFilePath = MatchingTest.class.getResource(XmlName).getPath();
        File file = new File(currenFilePath);
        Document document = saxBuilder.build(file);
        Element element = document.getRootElement();
        List<Element> list = element.getChildren();
        for (int i = 0; i < list.size(); i++) {
            Element ele = list.get(i);
            String tablename = ele.getAttributeValue("tablename");
            String classname = ele.getAttributeValue("classname");
            String clazz = ele.getAttributeValue("class");
            map.put("tablename", tablename);
            map.put(classname, clazz);
            int num = 0;
            for (Element elepro : (List<Element>) ele.getChildren()) {
                String VoName = elepro.getAttributeValue("name");
                String column = elepro.getAttributeValue("column");
                String columnType = elepro.getAttributeValue("type");
                mapnum.put(String.valueOf(num), VoName);
                map.put(VoName, column);
                maptype.put(VoName, columnType);
                num++;
            }
        }
        listMap.add(mapnum);
        listMap.add(map);
        listMap.add(maptype);
        return listMap;
    }

    public void writeExcel(List<FlightCourseVo> list) {
        try {
            // 创建一个可写入的excel文件对象
            WritableWorkbook workbook = Workbook.createWorkbook(new File("D://guyanming.xls"));
            // 使用第一张工作表，命名为“b_flight_course”
            WritableSheet sheet = workbook.createSheet("b_flight_course", 0);

            // String[] tableHead = { "id", "airlineCode", "departureAirport",
            // "arrivalAirport", "flightNo", "type",
            // "price", "distance", "international", "startDate",
            // "expirationDate", "flightCourse" };
            String[] tableHead = new String[mapnum.size()];

            for (int i = 0; i < mapnum.size(); i++) {
                tableHead[i] = mapnum.get(String.valueOf(i));
            }

            // 表头处理
            for (int i = 0; i < tableHead.length; i++) {
                Label label = new Label(i, 0, tableHead[i]);
                sheet.addCell(label);
            }
            FlightCourseVo fc = null;
            for (int i = 0; i < list.size(); i++) {
                fc = list.get(i);
                for (int j = 0; j < tableHead.length; j++) {
                    String methodName = "get" + tableHead[j].substring(0, 1).toUpperCase() + tableHead[j].substring(1);
                    Method m = fc.getClass().getMethod(methodName);
                    Object obj = m.invoke(fc);
                    Label labelCFC = new Label(j, i + 1, String.valueOf(obj));
                    sheet.addCell(labelCFC);
                }
            }
            // 关闭对象，释放资源
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    private static List<Object> getFlightCourseVoList(Map<String, String> mapnum, Map<String, String> map,
            Map<String, String> maptype, String SqlWhere) throws Exception {
        String table = map.get("tablename");
        StringBuffer sql = new StringBuffer("");
        sql.append(" select ");
        String columnName = null;
        String column = null;
        for (int i = 0; i < mapnum.size(); i++) {
            columnName = mapnum.get(String.valueOf(i));
            column = map.get(columnName);
            if ((i + 1) == mapnum.size()) {
                sql.append(" " + column);
                sql.append(" as ");
                sql.append(columnName + " ");
            } else {
                sql.append(" " + column);
                sql.append(" as ");
                sql.append(columnName + ",");
            }
        }
        sql.append(" from ");
        sql.append(table);
        sql.append(" where ");
        if (SqlWhere == null || "".equals(SqlWhere)) {
            sql.append("1=1");
        } else {
            sql.append(SqlWhere);
        }

        java.sql.Connection conn = NFDUtil.getDBConnection();
        ResultSet rs = conn.createStatement().executeQuery(sql.toString());
        String classname = map.get("classname");
        List<Object> fcList = new ArrayList<Object>();
        Object object = null;
        String MethodNameLost = null;
        String Methodtype = null;
        String MethodName = null;
        while (rs.next()) {
            object = Class.forName(classname).newInstance();
            for (int i = 0; i < mapnum.size(); i++) {
                MethodNameLost = mapnum.get(String.valueOf(i));
                Methodtype = maptype.get(MethodNameLost);
                MethodName = "set" + MethodNameLost.substring(0, 1).toUpperCase() + MethodNameLost.substring(1);
                Method m = object.getClass().getMethod(MethodName, Class.forName(Methodtype));
                // java.lang.Integer
                if ("java.lang.Integer".equals(Methodtype)) {
                    Object temp1 = rs.getInt(MethodNameLost);
                    m.invoke(object, temp1);
                }
                // java.lang.String
                else if ("java.lang.String".equals(Methodtype)) {
                    Object temp2 = rs.getString(MethodNameLost);
                    m.invoke(object, temp2);
                }
                // java.lang.Double
                else if ("java.lang.Double".equals(Methodtype)) {
                    Object temp3 = rs.getDouble(MethodNameLost);
                    m.invoke(object, temp3);
                }
                // java.util.Date
                else if ("java.util.Date".equals(Methodtype)) {
                    Object temp4 = rs.getDate(MethodNameLost);
                    m.invoke(object, temp4);
                }

            }
            fcList.add(object);
        }
        conn.close();
        return fcList;
    }

    public static void ExcuteExcel(String fileName, String SqlWhere) throws Exception {
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        listMap = WriteDBToExcel.getBFlightCourse(fileName);
        mapnum = listMap.get(0);
        map = listMap.get(1);
        maptype = listMap.get(2);
        List list = WriteDBToExcel.getFlightCourseVoList(mapnum, map, maptype, SqlWhere);
        try {
            WriteDBToExcel me = new WriteDBToExcel();
            File f = new File("D://guyanming.xls");
            // 生成一个可读取的excel文件对象
            me.workbook = Workbook.getWorkbook(f);
            // 写入数据
            me.writeExcel(list);
            me.workbook.close();

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String[] arges) throws Exception {
        System.out.println("********************game begin***********************");
        WriteDBToExcel.ExcuteExcel("BFlightCourse.xml", "type = 1 and international = 0");
        System.out.println("********************game over************************");
    }

    private Workbook workbook;

    static Map<String, String> map = null;

    static Map<String, String> mapnum = null;

    static Map<String, String> maptype = null;
}