数据库导出excel文件

package com.xiaohe.mianshi.dbtest;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
* 尝试从数据库中导出数据
* @author HYF 3.27
*/
public class TestDB {

/*
 * 将所创建的excel文件保存到指定的文件中
 */
public static void main(String[] args) {

    String fileName = "dbTest" + System.currentTimeMillis() + ".xls";// 文件名

    String sheetName = "数据库导出测试";// sheet名

    String[] title = { "id", "login", "name", "pass", "sex", "phone",
            "email" };// 标题

    List<User> users = test();

    String[][] value = new String[users.size()][];

    for (int i = 0; i < users.size(); i++) {
        value[i] = new String[title.length];
        // 将对象转换为String
        User u = users.get(i);
        value[i][0] = u.getId()+"";
        value[i][1] = u.getLogin();
        value[i][2] = u.getName();
        value[i][3] = u.getPass();
        value[i][4] = u.getSex();
        value[i][5] = u.getPhone();
        value[i][6] = u.getEmail();
    }
    HSSFWorkbook wb = createWB(sheetName,title,value,null);

    //将文件保存到指定位置
    try {
        OutputStream os=new FileOutputStream(new File(fileName));
        wb.write(os);
        os.flush();
        os.close();
        System.out.println("数据导出成功！！");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

/*
 * 创建Excel对象，并设置相应的参数
 */
public static HSSFWorkbook createWB(String sheetName, String[] title,
        String[][] values, HSSFWorkbook wb) {
    // 1.创建一个webbook,对应一个excel文件
    if (wb == null){
        wb = new HSSFWorkbook();
    }
    // 2.在webbook中添加一个sheet，对应excel中的sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    // 3.在excel中添加表头
    HSSFRow row = sheet.createRow(0);
    // 4.创建单元格，设置表头，表头居中
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
    HSSFCell cell = null;
    // 创建表头
    for (int i = 0; i < title.length; i++) {
        cell = row.createCell(i);
        cell.setCellValue(title[i]);
        cell.setCellStyle(style);
    }
    // 创建内容
    for (int i = 0; i < values.length; i++) {
        row = sheet.createRow(i + 1);
        for (int j = 0; j < values[i].length; j++) {
            row.createCell(j).setCellValue(values[i][j]);
        }
    }
    return wb;

}

/*
 * 从数据库中获取数据
 */
public static List<User> test() {
    User user = null;
    Connection conn = null;
    String sql = null;
    ResultSet rs = null;
    Statement st = null;
    String url = "jdbc:mysql://localhost:3306/shop";
    List<User> users = new ArrayList<User>();
    try {

        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("成功加载mysql驱动！！");
        conn = DriverManager.getConnection(url, "root", "root");
        st = conn.createStatement();
        sql = "select * from user";
        rs = st.executeQuery(sql);
        while (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setEmail(rs.getString("email"));
            user.setLogin(rs.getString("login"));
            user.setName(rs.getString("name"));
            user.setPass(rs.getString("pass"));
            user.setPhone(rs.getString("phone"));
            user.setSex(rs.getString("sex"));
            users.add(user);
        }
        System.out.println(users);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return users;
}

}