简单的使用pol架包来导出mysql中的数据

使用这个需要导入pol架包
下载地址：https://www.apache.org/dyn/closer.lua/poi/release/src/poi-src-3.17-20170915.zip
这个链接中包括了源码

直接上干货

实体类：

package com.excel;public class Employee {    private Integer id;    private String name;    private boolean sex;    public Employee() {    }    public Employee(Integer id, String name, boolean sex) {        this.id = id;        this.name = name;        this.sex = sex;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public boolean getSex() {        return sex;    }    public void setSex(boolean sex) {        this.sex = sex;    }    @Override    public String toString() {        return "Employee [id=" + id + ", name=" + name + ", sex=" + sex + "]";    }}

数据库

package com.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBUtil {    // 连接四要素    private static String driver = "com.mysql.jdbc.Driver";    private static String userName = "root";    private static String password = "123456";    private static String databaseSource = "jdbc:mysql://localhost:3306/test_main_db?characterEncoding=UTF-8";    private Connection conn = null;    private PreparedStatement pstmt = null;    private ResultSet rs = null;    /**     * 主要功能：连接数据库     *      * @return     */    public Connection getConnection() {        try {            // 如果数据库连接没有关闭，直接使用            if (conn != null && !conn.isClosed()) {                return conn;            }        } catch (Exception e) {            e.printStackTrace();        }        try {            Class.forName(driver);            conn = DriverManager.getConnection(databaseSource, userName, password);        } catch (Exception e) {            e.printStackTrace();        }        return conn;    }    /**     * 主要功能：执行sql语句[增删改]     *      * @param sql     * @param objs     * @return     */    public boolean executeNonQuery(String sql, Object[] objs) {        int rowCount = 0;        try {            pstmt = getConnection().prepareStatement(sql);            if (objs != null) {                for (int i = 0; i < objs.length; i++) {                    pstmt.setObject(i + 1, objs[i]);                }            }            rowCount = pstmt.executeUpdate();        } catch (Exception e) {            e.printStackTrace();        }        return rowCount>0;    }    /**     * 主要功能：执行sql语句[查询]     *      * @param sql     * @param objs     * @return     */    public ResultSet executeQuery(String sql, Object[] objs) {        try {            pstmt = getConnection().prepareStatement(sql);            if (objs != null) {                for (int i = 0; i < objs.length; i++) {                    pstmt.setObject(i + 1, objs[i]);                }            }            rs = pstmt.executeQuery();        } catch (SQLException e) {            e.printStackTrace();        }        return rs;    }    /**     * 主要功能：断开与数据库的连接     */    public void closeDB(DBUtil db){        try{            if(rs!=null){                rs.close();            }            if(pstmt!=null){                pstmt.close();            }            if(conn!=null){                conn.close();            }        }catch(Exception e){            System.out.println(e);        }    }}

数据库中获取数据

public class EmpDao{    /**     * 获取数据     * @return     */    public List<Employee> query() {        List<Employee> employees = null;        DBUtil db = new DBUtil();        String sql="select emp.id,emp.name,emp.sex from t_empinfo";        ResultSet rs = db.executeQuery(sql, null);        if (rs == null)            return null;        employees = new ArrayList<Employee>();        try {            while (rs.next()) {                Employee employee = new Employee();                employee.setId(rs.getInt("emp.id"));                employee.setName(rs.getString("emp.name"));                employee.setSex(rs.getBoolean("emp.sex"));                employees.add(employee);            }        } catch (SQLException e) {            e.printStackTrace();        }        db.closeDB(db);        return employees;    }}

数据导出类

import java.io.File;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;import java.util.UUID;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 org.apache.poi.ss.util.CellRangeAddress;public class ExportExcel_my {    private static HSSFWorkbook workbook;    private static HSSFSheet sheet;    private  ExportExcel_my() {    }    public static void excelExport(List<?> datas,Map<String, Object> columnName,String sheetName,String fileName,String fileUrl){        initHSSWorkbook(sheetName);        createTitleRow(sheetName,columnName);        createDateRow(columnName);        createHeadRow(columnName);        createContentRow(datas,columnName);        writeFile(fileName,fileUrl);    }    /**     * 将导出的文件写入本地     * @param fileName     */    private static void writeFile(String fileName,String fileUrl) {        try {            File file=new File(fileUrl);            file.mkdirs();            UUID uuid = UUID.randomUUID();            if(fileName.isEmpty() | fileName=="")                fileName = uuid + ".xls";            File file2=new File(file, fileName+".xls");            OutputStream os=new FileOutputStream(file2);            workbook.write(os);            os.close();            workbook.close();        } catch (Exception e) {            e.printStackTrace();        }    }    /**     * 创建文本行     * 需要使用到反射机制     * @param datas 数据     * @param columnName    列名     */    private static void createContentRow(List<?> datas, Map<String, Object> columnName) {        try {            int i=0;            for(Object obj:datas){                HSSFRow hssfRow=sheet.createRow(3+i);                int j=0;                for(String column:columnName.keySet()){                    String methodStr="get"+column.substring(0, 1).toUpperCase()+column.substring(1);                    System.out.println(methodStr);                    Method method=obj.getClass().getMethod(methodStr, null);                    // 通过反射机制调用方法                    String value=method.invoke(obj, null).toString();                    HSSFCell cell=hssfRow.createCell(j);                    cell.setCellValue(value);                    j++;                }                i++;            }        } catch (Exception e) {            e.printStackTrace();        }    }    /**     * 创建列名行     * @param columnName     */    private static void createHeadRow(Map<String, Object> columnName) {        HSSFRow hssfRow=sheet.createRow(2);        HSSFCell cell=null;        int i=0;        for(String str:columnName.keySet()){            cell=hssfRow.createCell(i);            cell.setCellValue(columnName.get(str).toString());            i++;        }    }    /**     * 创建日期行     * @param columnName     */    private static void createDateRow(Map<String, Object> columnName) {        CellRangeAddress cellRangeAddress=new CellRangeAddress(1, 1, 0, columnName.size()-1);        sheet.addMergedRegion(cellRangeAddress);        HSSFCell cell=sheet.createRow(1).createCell(0);        cell.setCellValue(new SimpleDateFormat().format(new Date()));    }    /**     * 创建表格标题     * @param sheetName 工作表名称     * @param columnName      */    private static void createTitleRow(String sheetName, Map<String, Object> columnName) {        CellRangeAddress cellRangeAddress=new CellRangeAddress(0, 0, 0, columnName.size()-1);        sheet.addMergedRegion(cellRangeAddress);        HSSFRow hssfRow=sheet.createRow(0);        HSSFCell cell=hssfRow.createCell(0);        cell.setCellValue(sheetName);    }    /**     * 初始化HSSFWorkbook     * @param sheetName 工作表名称     */    private static void initHSSWorkbook(String sheetName) {        workbook=new HSSFWorkbook();        sheet=workbook.createSheet(sheetName);    }}

测试类

public class Test{    /**     * excel的导出     */    public static void main(String[] args) throws IOException {        ArrayList<Employee> employees = (ArrayList<Employee>) m.query();        System.out.println(employees.size());        Map<String, Object> titleMap=new HashMap<String, Object>();        titleMap.put("id", "职员编号");        titleMap.put("name", "姓名");        titleMap.put("sex", "性别");        System.out.println("start导出");        long start = System.currentTimeMillis();        ExportExcel_my.excelExport(employees, titleMap, "职员表", "花名册", "d:/excel temp");        long end = System.currentTimeMillis();        System.out.println("end导出");        System.out.println("耗时："+(end-start)+"ms");    }}

到此，数据就导出成功了