使用EasyExcel将MySQL查询数据写至Execl中

使用阿里巴巴的EasyExcel将MySQL查询数据写至Execl中

1.依赖包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.11</version>
</dependency>

2.上代码

代码如下（示例）：

import com.alibaba.excel.EasyExcel;

import java.sql.*;
import java.util.*;

public class MysqlAndEasyExcel {
    //ip地址
    static final String IP = "localhost";
    //端口
    static final String PORT = "3306";
    //数据库
    static final String DATABASE = "test";
    //数据库用户名
    static final String USER = "root";
    //数据库密码
    static final String PASSWORD = "root";
    //输出文件位置
    private final static String fileName = ".\\输出路径\\测试.xlsx";
    public static void main(String[] args) {
        //SQL查询语句
        String sql = "SELECT a.* FROM `user` AS a LIMIT 0,100000";
        List<Map<String, Object>> list = getData(sql);
        easyUtil(list);
    }

    /**
     * 查询数据
     *
     * @param sql
     * @return
     */
    public static List<Map<String, Object>> getData(String sql) {
        List<Map<String, Object>> list = new ArrayList<>();//存储数据
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Success");
        } catch (Exception e) {
            System.out.println("error");
            e.printStackTrace();
        }
        try {
            Connection connect = DriverManager.getConnection("jdbc:mysql://" + IP + ":" + PORT + "/" + DATABASE
                    + "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT&useOldAliasMetadataBehavior=true",
                    USER, PASSWORD);
            System.out.println("Success");
            Statement stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//            System.out.println(sql);
            ResultSet rset = stmt.executeQuery(sql);
            rset.last();
            int rowCount = rset.getRow();
            ResultSetMetaData rsMeta = rset.getMetaData();
            int columnCount = rsMeta.getColumnCount();
            Map<String, Object> head = new LinkedHashMap<>();//标题
            for (int i = 1; i <= columnCount; i++) {
                head.put("Title" + i, rsMeta.getColumnName(i));
            }
            list.add(head);//添加标题
            // 追加数据
            for (int i = 1; i <= rowCount; i++) {
                Map<String, Object> data = new LinkedHashMap<>();//数据
                //sql移动到第i行
                rset.absolute(i);
                // 创建列索引
                for (int j = 1; j <= columnCount; j++) {
                    data.put("Data" + i + j, rset.getString(j));
                    //System.out.println(i+"-"+j+"=>"+rset.getString(j));
                }
                list.add(data);
            }
            try {
                stmt.close();
                connect.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public static void easyUtil(List<Map<String, Object>> list) {
        List<List<Object>> list2 = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            List<Object> objects = new ArrayList<>();
            Collection<Object> values;
            values = list.get(i).values();
            for (Object value : values) {
                //空值补空格
                objects.add(value != null ? value.toString() : "");
            }
            list2.add(objects);
        }
        EasyExcel.write(fileName).sheet("工作簿（sheet名字）").doWrite(list2);
        System.out.println("导出成功");
    }
}

---

总结

提示：将MySQL查询数据导到Execl中简单脚本。