java实现数据库导出ECXL表格

需求：将数据库中的某些表格导出成excl表格。

流程：

1.在本地环境中创建excl表格；

package test.cxg.demo1;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

import test.cxg.demo2.DataBase;

/***
* 将数据库中的数据导出成Excel 说明：该类是在apache的poi组件来实现的
* 用户只要提供给writeExcel方法文件名，列名,和一个sql查询语句就可以导出数据到excel文件
* 
*/
public class ResultSetToExcel {
	/**
     * 写Excel操作
     * @param fileName文件名，但不需要后缀名
     * @param coloumItems字段名，即表中的每一列的名称
     * @param sql数据库查询语句
     */
	public static String path="D:/data_copy/";
	public static void writeExcel(String fileName, String[] coloumItems,String sql) {
		//写文件
		File folder=new File(path);
		if (!folder.exists()) {
			folder.mkdir();
		}
		//连接数据库
		Connection connection=DataBase.createConn();
		PreparedStatement preparedStatement=DataBase.prepare(connection, sql);
		ResultSet resultSet=null;
		try {
			resultSet=preparedStatement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//文件输出流
		FileOutputStream fileOutputStream=null;
		 try {
	            fileOutputStream = new FileOutputStream(path + fileName+".xls");
	            HSSFWorkbook workbook = new HSSFWorkbook();
	            HSSFSheet sheet = workbook.createSheet();
	            createTag(coloumItems, sheet);// 写表格的列名
	            createValue(resultSet, sheet);// 获取数据集，然后获得数据，写文件
	            workbook.write(fileOutputStream);
	            fileOutputStream.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        } finally {
	            if (fileOutputStream != null) {
	                try {
	                    fileOutputStream.close();
	                } catch (IOException e) {
	                    e.printStackTrace();
	                }
	            }
	        }
		 DataBase.close(preparedStatement);
		 DataBase.close(connection);
	}
	/**
     * 创建表格表头
     * 
     * @param tags
     * @param s
     */
    private static void createTag(String[] tags, HSSFSheet s) {
        HSSFRow row = s.createRow(0);
        HSSFCell cell = null;
        for (int i = 0; i < tags.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(tags[i]);
        }
    }
 
    /**
     * 设置表格内容
     * 
     * @param res
     * @param s
     */
    private static void createValue(java.sql.ResultSet res, HSSFSheet s) {
        try {
            int flag = 1;
            int count = res.getMetaData().getColumnCount();
            HSSFRow row = null;
            HSSFCell cell = null;
            while (res.next()) {
                row = s.createRow(flag);
                for (int i = 1; i <= count; i++) {
                    cell = row.createCell(i - 1);
                    Object obj = res.getObject(i);
                    cell.setCellValue(obj + "");
                }
                flag++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.连接数据库，读取数据库；

package test.cxg.demo2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * 封装数据库的相关的操作
 * @author Administrator
 *
 */
public class DataBase {
	/**
	 * 连接数据库
	 * @return
	 */
	public static Connection createConn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/so", "root", "");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 数据库的读取
     * @param conn
     * @param sql
     * @return
     */
    public static PreparedStatement prepare(Connection conn, String sql) {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ps;
    }
    /**
     * 关闭连接 
     * @param conn
     */
    public static void close(Connection conn) {
         
        try {
            conn.close();
            conn = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close(Statement stmt) {
        try {
            stmt.close();
            stmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close(ResultSet rs) {
        try {
            rs.close();
            rs = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3.输出处理的结果集。

package test.cxg.demo3;

import test.cxg.demo1.ResultSetToExcel;

public class test {

	public static void main(String[] args) {
         //创建相关参数
		  String fileName = "网站用户";// 文件名，不带路径，不带.xls后缀
		  String[] coloumItems = { "用户id", "创建时间", "会员ID", "昵称" ,"类型","卖商","微信邀请函会员情况",
				  "代理级别","父ID","孩子数量","总销量", "本月销量", "账户", "会员微信", "所有上级"};
		  String sql = "select id,gmt_create,member_id,nickname,itype,soshop_id,"
		  		+ "wxinvit_member_id,soagent,parent,childrennum,total,months,"
		  		+ "account,openid,family from soshopmember;";
	      //开始写表格   
	      ResultSetToExcel.writeExcel(fileName, coloumItems, sql);
	      System.out.println("数据导出成功！");
	      
	}

}