百万数据导出详细代码

后端代码:

1.pom依赖

 <!-- easyExcel导出 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.2</version>
        </dependency>

2.config封装包

①创建config文件夹,新建ExcelConfig文件,粘贴下面的代码, 注意这里面的实体类改成自己要导出数据的实体类

package com.cloud.config;


import com.cloud.domain.Excelstock;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.util.List;

@Slf4j
public class ExcelConfig {

    public static void exportExcel(List<Excelstock> dataList) {
        // 设置从哪一行开始
        int rowIndex = 0;
        // 设置excel表格中的sheet
        int sheetIndex = 1;

        // 创建工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 获取第一个sheet页
        Sheet sheet = workbook.createSheet("Sheet" + sheetIndex);

        // 写入表头
        Row headerRow = sheet.createRow(0);

        headerRow.createCell(1).setCellValue("列2");
        headerRow.createCell(2).setCellValue("列3");
        headerRow.createCell(3).setCellValue("列4");
        headerRow.createCell(4).setCellValue("列5");
        headerRow.createCell(5).setCellValue("列6");
        headerRow.createCell(6).setCellValue("列7");
        headerRow.createCell(7).setCellValue("列8");
        headerRow.createCell(8).setCellValue("列9");
        headerRow.createCell(9).setCellValue("列10");

        // 写入数据
        for (Excelstock excel : dataList) {
            if (rowIndex >= 1048575) {
                sheetIndex++;
                sheet = workbook.createSheet("Sheet" + sheetIndex);
                rowIndex = 0;
            }
            Row row = sheet.createRow(rowIndex++);
            row.createCell(1).setCellValue(excel.getId());
            row.createCell(2).setCellValue(excel.getSymbol());
            row.createCell(3).setCellValue(excel.getName());
            row.createCell(4).setCellValue(excel.getExchange());
            row.createCell(5).setCellValue(excel.getSector());
            row.createCell(6).setCellValue(excel.getIndustry());
            row.createCell(7).setCellValue(excel.getCountry());
            row.createCell(8).setCellValue(excel.getCurrency());
            row.createCell(9).setCellValue(excel.getListingDate());
        }
        // 指定文件输出位置
        String pathName = "D:\\exportExcel\\output.xlsx";

        // 将数据写入文件
        try (FileOutputStream outputStream = new FileOutputStream(pathName)) {
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("异常信息: ", e);
        }
        // 关闭工作簿
        workbook.dispose();
    }
}

3.controller层方法, 在写导出数据方法的controller层 加入下方代码里的注解   , 方法中的for循环是将300万数据分别存到三张表中

    //百万数据导出
    @GetMapping("/multiThread")
    public void multiThread() {
        // WorkStealingPool 处理IO密集型操作 将任务分配给处于空闲状态的CPU核心 2n+1
        ExecutorService executorService = Executors.newWorkStealingPool(17);
        try {
            // 将任务提交给线程池执行,通过Future对象获取任务的执行情况和获取执行结果
            Future<?> future = executorService.submit(new Runnable() {
                @Override
                public void run() {
                    ArrayList<Excelstock> excelList = new ArrayList<>();
                    List<Excelstock> excelstocks1 = excelstockService.list1();
                    List<Excelstock> excelstocks2 = excelstockService.list2();
                    List<Excelstock> excelstocks3 = excelstockService.list3();
                    if (Objects.nonNull(excelstocks1) && Objects.nonNull(excelstocks2) && Objects.nonNull(excelstocks3)) {
                        excelstocks1.forEach(excel1 -> excelList.add(excel1));
                        excelstocks2.forEach(excel2 -> excelList.add(excel2));
                        excelstocks3.forEach(excel3 -> excelList.add(excel3));
                    }
                    ExcelConfig.exportExcel(excelList);
                }
            });
            // 确保任务执行完毕
            future.get();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("异常信息: ", e);
        } finally {
            //关闭线程池
            executorService.shutdown();
        }
    }

深度分页 sql: 

5.普通导出excel方法实体类的字段上需要加注解