java实现数据库导出ECXL表格

来源：互联网发布：mac os sierra重新安装编辑：程序博客网时间：2024/04/27 14:22

需求：将数据库中的某些表格导出成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("数据导出成功！");      }}

0 0