简单的使用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");
    }

}

到此，数据就导出成功了