EasyExcel百万数据导出——大数据量分页查询

Gidler

于 2024-02-20 15:38:57 发布

阅读量258

点赞数 3

文章标签： java 开发语言 excel

本文链接：https://blog.csdn.net/G_idler/article/details/136191418

版权

线程查询结果记录类

package cn.com.cnpc.download.center.util.paralle;

/**
 * @Author: gxh
 * @Description:
 * @Date: Created in 15:22 2023/8/22
 */
public class ParallelResult<R> {
    private Long index;
    private R data;

    public ParallelResult() {
    }

    public ParallelResult(Long index, R data) {
        this.index = index;
        this.data = data;
    }

    public static <R> ParallelResult<R> of(Long index, R data) {
        return new ParallelResult<>(index, data);
    }

    public static <R> ParallelResult<R> empty() {
        return new ParallelResult<>();
    }

    public boolean isEmpty() {
        return index == null && data == null;
    }

    public Long getIndex() {
        return index;
    }

    public R getData() {
        return data;
    }
}

分页查询线程工具类

package cn.com.cnpc.download.center.util.paralle;

import com.google.common.util.concurrent.ThreadFactoryBuilder;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicLong;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Consumer;
import java.util.function.LongFunction;

/**
 * @Author: gxh
 * @Description:
 * @Date: Created in 15:22 2023/8/22
 */
@Slf4j
public class ParallelUtil<R> {

    /**
     * 默认线程数 -获取当前系统可用的处理器数量
     */
    public static final int DEF_PARALLEL_NUM = Runtime.getRuntime().availableProcessors();
    /**
     * 生产者并发线程数
     */
    private int parallelNum;
    /**
     * 生产者核心线程数
     */
    private int corePoolSize;

    /**
     * 总任务数
     */
    private long totalNum;

    /**
     * 消费者函数
     */
    private Consumer<R> resultConsumer;

    /**
     * 生产者函数
     */
    private LongFunction<R> producerFunction;
    /**
     * 生产者将任务放到此队列，消费者从此队列读数据
     */
    private ArrayBlockingQueue<ParallelResult<R>> queue;

    // 生产者线程池
    private ThreadPoolExecutor threadPoolExecutor;

    /**
     * 默认超时时间 5分钟
     */
    private long timeout = 5;

    /**
     * 当线程数大于核心时，这是多余的空闲线程 在终止前等待新任务的最长时间。 单个线程允许执行时间 5分钟 超过
     */
    private int keepAliveSeconds = 5;

    /**
     * 默认超时时间单位
     */
    private TimeUnit timeoutTimeUnit = TimeUnit.MINUTES;

    public static <R> ParallelUtil<R> parallel(Class<R> consumerClass, long totalNum) {
        return parallel(consumerClass, DEF_PARALLEL_NUM, totalNum);
    }

    /**
     * 初始化
     *
     * @param consumerClass 消费的类Class
     * @param parallelNum   并发线程数
     * @param totalNum      并发执行总数（触发asyncProducer函数次数）
     * @param <R>
     * @return
     */
    public static <R> ParallelUtil<R> parallel(Class<R> consumerClass, int parallelNum, long totalNum) {
        ParallelUtil<R> parallelUtil = new ParallelUtil<>();
        int maxParallel = (int) Math.max(1, Math.min(parallelNum, totalNum));
        parallelUtil.parallelNum = maxParallel;
        parallelUtil.corePoolSize = parallelNum;
        if (maxParallel > 1) {
            parallelUtil.corePoolSize = maxParallel / 2;
        }
        parallelUtil.totalNum = totalNum;
        return parallelUtil;
    }

    /**
     * 消费者等待获取任务的超时时间（不设置则默认60秒）
     *
     * @param timeout 值
     * @param unit    时间单位
     * @return
     */
    public ParallelUtil<R> timeout(long timeout, TimeUnit unit) {
        this.timeout = timeout;
        this.timeoutTimeUnit = unit;
        return this;
    }

    /**
     * 异步并发生产者
     *
     * @param producerFunction 生产者函数，参数为1~totalNum，返回值为任意类型
     * @return
     */
    public ParallelUtil<R> asyncProducer(LongFunction<R> producerFunction) {
        this.producerFunction = producerFunction;
        return this;
    }

    /**
     * 消费者(串行有序消费生产者返回的数据)
     *
     * @param resultConsumer
     * @return
     */
    public ParallelUtil<R> syncConsumer(Consumer<R> resultConsumer) {
        this.resultConsumer = resultConsumer;
        return this;
    }

    /**
     * 开始执行
     *
     * @throws InterruptedException
     */
    public void start() throws InterruptedException {
        try {
            // 如果无任务则直接返回
            if (totalNum <= 0) {
                return;
            }
            // 如果只有一个任务，则串行执行，生产者生成的数据直接给到消费者
            if (totalNum == 1) {
                resultConsumer.accept(producerFunction.apply(1));
                return;
            }
            // 初始化队列和线程池
            queue = new ArrayBlockingQueue<>(parallelNum);
            log.info("最大线程数：{}", parallelNum);
            threadPoolExecutor = new ThreadPoolExecutor(1, parallelNum, 10, TimeUnit.SECONDS, new SynchronousQueue<>(),
                    new ThreadFactoryBuilder().setNameFormat("parallel-%d").build(), new ThreadPoolExecutor.CallerRunsPolicy());
            // 生产者开始执行
            Thread producerThread = new Thread(() -> {
                try {
                    AtomicLong indexAtomicLong = new AtomicLong(1);
                    List<CompletableFuture<R>> futureList = new ArrayList<>(parallelNum);
                    for (long index = 1; index <= totalNum; index++) {
                        long finalIndex = index;
                        futureList.add(CompletableFuture.supplyAsync(() -> producerFunction.apply(finalIndex), threadPoolExecutor));
                        if (futureList.size() == parallelNum) {
                            for (CompletableFuture<R> future : futureList) {
                                queue.put(new ParallelResult<>(indexAtomicLong.getAndIncrement(), future.join()));
                            }
                            futureList.clear();
                        }
                    }
                    for (CompletableFuture<R> future : futureList) {
                        queue.put(new ParallelResult<>(indexAtomicLong.getAndIncrement(), future.join()));
                    }
                    futureList.clear();
                } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                    // 添加一个空元素，防止queue.poll等待到超时
                    queue.offer(ParallelResult.empty());
                    throw new RuntimeException(e);
                } catch (Exception e) {
                    // 添加一个空元素，防止queue.poll等待到超时
                    queue.offer(ParallelResult.empty());
                    throw new RuntimeException(e);
                }
            });
            producerThread.setDaemon(true);
            producerThread.start();
            AtomicReference<Throwable> exception = new AtomicReference<>();
            producerThread.setUncaughtExceptionHandler((t, e) -> exception.set(e));
            // 消费者等待消费
            AtomicLong count = new AtomicLong();
            ParallelResult<R> parallelResult;
            // 消费者等待消费
            while ((parallelResult = queue.poll(timeout, timeoutTimeUnit)) != null) {
                // 异常时添加的空元素则直接return
                if (parallelResult.isEmpty()) {
                    break;
                }
                // 消费者消费生产者生产的数据
                resultConsumer.accept(parallelResult.getData());
                count.incrementAndGet();
                // 已最后一条，直接结束，queue.poll等待问题
                if (parallelResult.getIndex() == totalNum) {
                    break;
                }
            }
            if (count.get() != totalNum) {
                if (Objects.isNull(exception.get())) {
                    // 等待，防止子线程异常未捕获 主线程已经获取异常结果
                    Thread.sleep(1);
                }
                if (Objects.nonNull(exception.get())) {
                    // 等待，防止子线程异常未捕获 主线程已经获取异常结果
                    exception.get().printStackTrace();
                }
                throw new RuntimeException(exception.get() == null ? "timeout" : exception.get().getMessage());
            }
        } finally {
            if (threadPoolExecutor != null) {
                threadPoolExecutor.shutdown();
            }
        }
    }

}

Excel 写入工具类（支持长周期下载，可以根据时间分割查询有序写入）

Excel 一个sheet页不要100万数据，多sheet页导出

package cn.com.cnpc.download.center.util.excel;

import cn.com.cnpc.download.center.entity.FileInfoPO;
import cn.com.cnpc.download.center.enums.FileStatusEnum;
import cn.com.cnpc.download.center.service.RedisService;
import cn.com.cnpc.download.center.service.file.FileInfoService;
import cn.com.cnpc.download.center.strategy.handler.MyExcelStylerStrategy;
import cn.com.cnpc.download.center.util.ConnectUtil;
import cn.com.cnpc.download.center.util.paralle.ParallelUtil;
import cn.com.cnpc.download.center.vo.excel.req.WriteHandleVO;
import cn.timesnew.framework.timesnew.basic.util.DateUtils;
import cn.timesnew.framework.timesnew.basic.vo.AuthInfoResVO;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicLong;
import java.util.function.LongFunction;

/**
 * @Author: gxh
 * @Description:
 * @Date: Created in 10:09 2023/9/28
 */
@Slf4j
@Component
@NoArgsConstructor
public class ExcelPartWriteUtil extends ExcelAbstract {

    public static final Integer MORE_EXCEL_SHEET_ROW_MAX_SIZE = 900000;

    private String lockId;

    private String fileName;

    private Class head;

    private FileInfoPO fileInfoPO;

    private WriteHandler writeHandler;

    private ExcelWriter excelWriter;

    private List<WriteHandleVO> writeHandles;

    private AuthInfoResVO authInfoResVO;

    private static RedisService redisService;

    private static FileInfoService fileInfoService;

    protected AtomicLong count = new AtomicLong(0);

    /**
     * 默认线程数
     */
    protected static Integer parallelNum;

    protected int type = 0;

    public static ExcelPartWriteUtil create(String lockId, String fileName, Class head, AuthInfoResVO authInfoResVO) {
        return new ExcelPartWriteUtil(lockId, fileName, head, authInfoResVO);
    }

    public ExcelPartWriteUtil(String lockId, String fileName, Class head, AuthInfoResVO authInfoResVO) {
        this.lockId = lockId;
        this.head = head;
        this.fileName = fileName;
        this.authInfoResVO = authInfoResVO;
    }

    public ExcelPartWriteUtil write(List<WriteHandleVO> list) {
        this.writeHandles = list;
        return this;
    }

    public ExcelPartWriteUtil registerWriteHandler(WriteHandler writeHandler) {
        this.writeHandler = writeHandler;
        return this;
    }

    @Override
    protected void before() {
        log.info("写入开始");
        this.fileInfoPO = fileInfoService.getFile(fileName, authInfoResVO);
        ExcelWriterBuilder writerBuilder = EasyExcel.write(fileInfoPO.getUrl(), head).registerWriteHandler(new MyExcelStylerStrategy());
        if(Objects.nonNull(writeHandler)){
            writerBuilder.registerWriteHandler(writeHandler);
        }
        this.excelWriter = writerBuilder.build();
    }

    @Override
    protected void context() throws Exception {
        AtomicInteger i = new AtomicInteger(1);
        for (WriteHandleVO o:writeHandles) {
            if (Objects.nonNull(o) && o.getExecuteCount() > 0) {
                log.info("一共{}次,现在第{}次,本次执行需要执行查询次数{},查询:{}",writeHandles.size(),i.getAndIncrement(),o.getExecuteCount(),o.getFileName());
                this.pageExcelWriterParallel(o.getExecuteCount(), o.getExecuteFunction());
            }
        }
    }

    public void start() {
        super.run();
    }

    @Override
    protected void error(String message) {
        if (Objects.nonNull(fileInfoPO)) {
            fileInfoPO.setStatus(FileStatusEnum.STATUS_NE1);
            fileInfoPO.setEffectiveDate(DateUtils.now());
            fileInfoService.getBaseMapper().updateById(fileInfoPO);
        }
        ConnectUtil.sendErrorMsg(authInfoResVO.getToken(), message);
        log.info("写入失败：{}", message);
    }

    @Override
    protected void after() {
        excelWriter.finish();
        fileInfoPO.setStatus(FileStatusEnum.STATUS10);
        fileInfoService.getBaseMapper().updateById(fileInfoPO);
        ConnectUtil.sendSuccessMsg(authInfoResVO.getToken(), fileInfoPO.getTaskNo());
        log.info("写入成功");
    }

    @Override
    public void close() {
        if (StringUtils.isNotBlank(lockId)) {
            redisService.unlock(lockId);
        }

        if(Objects.nonNull(excelWriter)){
            excelWriter.finish();
        }
    }


    @Autowired
    public void setParallelNum(@Value("${async.executor.thread.parallelNum:10}") Integer parallelNum) {
        ExcelPartWriteUtil.parallelNum = Math.min((Runtime.getRuntime().availableProcessors() * 2), parallelNum);
    }

    @Autowired
    public void setRedisService(RedisService redisService) {
        ExcelPartWriteUtil.redisService = redisService;
    }

    @Autowired
    public void setFileInfoService(FileInfoService fileInfoService) {
        ExcelPartWriteUtil.fileInfoService = fileInfoService;
    }

    private void pageExcelWriterParallel(long totalPage, LongFunction<List<T>> pageListFunction) throws Exception{
        // 如果无待写入的数据则写入标题
            WriteSheet writeSheet = EasyExcel.writerSheet(1, "Sheet1").build();
            ParallelUtil.parallel(List.class, parallelNum, totalPage)
                    .asyncProducer(pageListFunction::apply)
                    .syncConsumer(pageList -> {
                        writeSheet(count, pageList, head, MORE_EXCEL_SHEET_ROW_MAX_SIZE,
                                true, excelWriter, writeSheet);
                    }).start();
    }


	public void writeSheet(AtomicLong count, List pageList, Class head, Integer maxSize, Boolean isTrans,
                                  ExcelWriter excelWriter, WriteSheet writeSheet) {
        long old = count.get();
        long next = count.addAndGet(pageList.size());
        // 两页不一致 按照数据补
        List list = pageList;
        if (!Objects.equals((old / maxSize), (next / maxSize))) {
            // 获取隔离下标
            Integer index = Math.toIntExact(maxSize - old % maxSize) ;
            list = pageList.subList(0, index);

            writeSheet.setSheetNo((int) (old / maxSize + 1));
            writeSheet.setSheetName("Sheet" + writeSheet.getSheetNo());
            if (isTrans) {
                list = TransUtil.transList(list, head);
            }
            excelWriter.write(list, writeSheet);
            list = pageList.subList(index, pageList.size());
        }
        // 新一页
        writeSheet.setSheetNo((int) (next / maxSize + 1));
        writeSheet.setSheetName("Sheet" + writeSheet.getSheetNo());
        if (isTrans) {
            list = TransUtil.transList(list, head);
        }
        excelWriter.write(list, writeSheet);

    }
 }