从数据库多线程导出数据到Excel(千万级数据导出)，附代码

从数据库多线程导出数据到Excel

在业务中，经常会碰到从数据库导出数据到Excel中的场景，这里总结了使用EasyExcel导出千万级数据的过程。最终实现 千万级数据导出到 一张excel中多个sheet页！

    要知道以 .xlsx结尾的excel文件每个sheet 只能写104万左右的数据量，
    如果想要写入500W条数据到excel，要么分到多个sheet中，每个sheet存100w左右数据，5个sheet存储完；
    要么写到五个xlsx文件中，这可能不是想要的。所以写入到同一个表格文件不同的sheet中去。
    封装的easyexcel只用于写数据，只要内存和硬盘足够大，亿万条数据也不在话下,但是无实际意义吧。

直接上代码：

1. 引入easyexcel依赖

<!--引入easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

2. 封装easyexcel工具类

主要用到EasyExcel、ExcelWriter、WriterSheet等主要类，想要深入研究的同学可以自行看源码。

public class ExcelHandler {

    private List<WriteSheet> sheets;
    private String dirName;
    private static final Integer DEFAULT_SHEETS_NUM = 5;
    private static final Integer DEFAULT_PER_SHEET_NUM = 1000000;

    public ExcelHandler(){}

    public ExcelHandler(String folderName){
        dirName = folderName;
    }

    /**
     *  创建excel和sheet，创建时可以指定sheet数量
     * @param excelName
     * @param clazz
     * @param sumSheet
     * @return
     */
    public ExcelWriter create(String excelName, Class clazz, int sumSheet){
        ExcelWriter excelWriter = EasyExcel.write(route(excelName), clazz.asSubclass(clazz)).build();
        createSheets(sumSheet);
        return excelWriter;
    }

    /**
     *  创建excel和sheet，sheet 数量默认 5， 最高可存放500W 行左右的数据，受每个sheet存放数据的限制
     * @param excelName
     * @param clazz
     * @return
     */
    public ExcelWriter create(String excelName, Class clazz){
        ExcelWriter excelWriter = EasyExcel.write(route(excelName), clazz.asSubclass(clazz)).build();
        createSheets(DEFAULT_SHEETS_NUM);
        return excelWriter;
    }

    /**
     *  写数据到excel， 仅使用一个sheet，不可用于百万以上数据
     * @param excelWriter
     * @param list
     */
    public void write(ExcelWriter excelWriter, List list){
        excelWriter.write(list, sheets.get(0));
    }

    /**
     *  写数据到excel
     * @param excelWriter
     * @param list    每一次的数据
     * @param sheetNum  sheet页码
     */
    public void write(ExcelWriter excelWriter, List list, int sheetNum){
        excelWriter.write(list, sheets.get(sheetNum));
    }

    /**
     *  写完数据关闭(finish 有关流操作)，必须的操作
     * @param excelWriter
     */
    public void finish(ExcelWriter excelWriter){
        excelWriter.finish();
    }

    /**
     *  创建指定数量的sheet
     * @param num
     */
    private void createSheets(int num){
        sheets = new ArrayList();
        for (int i = 0; i <= num; i++) {
            WriteSheet sheet = EasyExcel.writerSheet(i, "sheet"+i).build();
            sheets.add(sheet);
        }
    }

    /**
     *  获取excel存放路径
     * @param excelName
     * @return
     */
    private String route(String excelName){
        if (null == dirName){
            dirName = "D:\\data\\excel";
        }
        String filePath = "D:\\data\\excel\\" + dirName + "/";
        File file = new File(filePath);
        if (!file.exists()){
            file.mkdirs();
        }
        return filePath + excelName + ".xlsx";
    }
}

3. 数据实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(25)
public class Data1 {

    @ExcelProperty("账号")
    private String account;
    @ExcelProperty("密码")
    private String password;
}

4. 如何使用工具类

public class Test {
    public static void main(String[] args) {
        Test tt = new Test();
        tt.test01();
    }

    /**
     *  模拟百万以下数据导入到excel
     */
    private void test00(){
        int num = 0;
        ExcelHandler handler = null;
        ExcelWriter writer = null;
        try {
            // 创建handler对象 -- 参数文件夹名
            handler = new ExcelHandler("test_00");
            writer = handler.create("记录", Data1.class);

            List list = new ArrayList(1024);
            // 方式一：一次性导出
            for (int i = 0; i < 1000000; i++) {
                num++;
                list.add(new Data1("张三"+num, "123abc"+num));
            }
            handler.write(writer, list);


            // 方式二：分多次导出，为了降低导出过程中的内存资源，可以分多次导出
            for (int a = 0; a < 100; a++) { // 模拟拿100次数据，每次拿10000条数据
                for (int i = 0; i < 10000; i++) { // 模拟一次拿10000条数据
                    num++;
                    list.add(new Data1("张三"+num, "123abc"+num));
                }
                handler.write(writer, list);
                // 防止数据重复，及时情况list集合
                list.clear();
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (null != writer){
                handler.finish(writer);
            }
        }
    }

    /**
     *  模拟百万以上数据导入到excel
     */
    private void test01(){
        int num = 0;
        ExcelHandler handler = null;
        ExcelWriter writer = null;
        try {
            // 创建handler对象 -- 参数文件夹名
            handler = new ExcelHandler("test_01");
            writer = handler.create("记录", Data1.class, 10);

            List list = new ArrayList(1024);
            // 此处依旧可以模仿test00去优化
            // TODO 考虑能否做成异步方式，用阻塞队列，生产和消费同事进行，一边从数据库查询数据，一边导入数据到excel

            for (int a = 0; a < 10; a++) { // 模拟分页页数， 每页50W数据
                for (int i = 0; i < 500000; i++) { // 模拟每页数据量
                    num++;
                    list.add(new Data1("张三"+num, "123abc"+num));
                }
                handler.write(writer, list, a);
                // 防止数据重复，及时情况list集合
                list.clear();
            }

            // 考虑从数据库查询数据这种情况
            // 先查询数据总量，然后计算分页数，按照每页50W
            int allCount = 7500000; // 数据总条数 750W
            int pageSize = (int) Math.ceil(allCount/1000000.0);
            writer = handler.create("MySQL数据", Data1.class, pageSize);
            // TODO 效率问题，可以考虑多线程
            // TODO 每个线程处理部分数据，处理 步进的数据量，然后再可以根据下面的分页查询数据
            // TODO 每个处理线程CountDownLaunch处理，主线程 ctl.await()阻塞，所有处理线程完成之后，再进行下面的业务
            for (int k = 0; k < pageSize; k++) {
                // 从数据库分页查询数据
                List<Data1> datasByMySQL = selectByMySQL(new HashMap<>(), k*500000, 500000);
                handler.write(writer, datasByMySQL, k);
                datasByMySQL.clear();
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (null != writer){
                handler.finish(writer);
            }
        }
    }

    /**
     *  从数据库查询数据
     * @param params  条件查询的params
     * @param offset  偏移量
     * @param rows    行数
     * @return
     */
    private List<Data1> selectByMySQL(HashMap<Object, Object> params, int offset, int rows) {
        // TODO 从数据库查询数据  select * from table where param=#{param} limit offset,rows
        return new ArrayList<>();
    }
}