当前位置： 首页 > news >正文

数据分页异步后台导出excel

news 2025/8/5 8:20:01

数据分页异步后台导出excel

异步导出设计


下面根据如上设计列出具体方案（没有文件服务器，直接从后台服务下载）

表设计

DROP TABLE IF EXISTS `task`;CREATE TABLE `task` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`type` varchar(2) NOT NULL DEFAULT '1' COMMENT '1：导出',`status` varchar(100) DEFAULT NULL COMMENT '状态',`create_time` datetime DEFAULT CURRENT_TIMESTAMP,`start_time` datetime DEFAULT NULL,`end_time` datetime DEFAULT NULL,`err_msg` text,`url` varchar(1000) DEFAULT NULL COMMENT '文件地址',`progress` decimal(3,2) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;

异步导出类

package com.async_export_demo.export;import com.async_export_demo.config.MyExportConfig;
import com.async_export_demo.model.Task;
import com.async_export_demo.service.TaskService;
import com.async_export_demo.util.SpringContextUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.Getter;
import lombok.extern.java.Log;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.MathContext;
import java.math.RoundingMode;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.RejectedExecutionException;
import java.util.concurrent.RejectedExecutionHandler;
import java.util.concurrent.ThreadPoolExecutor;@Component
@Log
public class AsyncExportService {private static final ThreadPoolTaskExecutor EXECUTOR = new ThreadPoolTaskExecutor();/*** 进度，保留两位小数*/private static MathContext MATH_CONTEXT = new MathContext(2, RoundingMode.HALF_UP);static {//设置核心线程数EXECUTOR.setCorePoolSize(2);//设置最大线程数EXECUTOR.setMaxPoolSize(4);//设置线程被回收的空闲时长EXECUTOR.setKeepAliveSeconds(6);//设置队列容量EXECUTOR.setQueueCapacity(2);//设置线程前缀EXECUTOR.setThreadNamePrefix("export-");//设置拒绝策略EXECUTOR.setRejectedExecutionHandler(new AbortPolicy());//初始化线程池EXECUTOR.initialize();}@Autowiredprivate TaskService taskService;@Autowiredprivate Map<ExportEnum, DataProvider> dataProviderMap;@Autowiredprivate MyExportConfig myExportConfig;// 导出配置类public static class ExportConfig {private int pageSize = 1000;  // 每页数据量private int maxRetry = 3;     // 失败重试次数private String fileType = "xlsx"; // 文件类型(csv/xlsx)private boolean compress = true; // 是否压缩}public static class ExportContext {private final Map<String, Object> params = new HashMap<>();public void put(String key, Object value) {params.put(key, value);}public Object get(String key) {return params.get(key);}}/*** 因为我想让线程池执行拒绝策略时可以拿到 taskId，以便可以更新状态，所以新定义了一个Runnable*/public static class ExportRunnable implements Runnable {@Getterprivate final BigInteger taskId;private final Runnable runnable;public ExportRunnable(BigInteger taskId, Runnable runnable) {this.taskId = taskId;this.runnable = runnable;}@Overridepublic void run() {runnable.run();}}/*** 线程池拒绝策略*/public static class AbortPolicy implements RejectedExecutionHandler {/*** Creates an {@code AbortPolicy}.*/public AbortPolicy() {}/*** Always throws RejectedExecutionException.** @param r the runnable task requested to be executed* @param e the executor attempting to execute this task* @throws RejectedExecutionException always*/public void rejectedExecution(Runnable r, ThreadPoolExecutor e) {RejectedExecutionException ex = new RejectedExecutionException("Task " + r.toString() +" rejected from " +e.toString());if (r instanceof ExportRunnable) {ExportRunnable r2 = (ExportRunnable) r;Task entity = new Task();entity.setId(r2.getTaskId());entity.setStatus("FAILED");entity.setErrMsg(ex.getMessage());TaskService taskService1 = SpringContextUtil.getApplicationContext().getBean(TaskService.class);taskService1.updateExportTask(entity);}throw ex;}}public BigInteger asyncExport(ExportEnum exportEnum) {// 超时时长毫秒long timeout = myExportConfig.getTimeoutUnit().toMillis(myExportConfig.getTimeout());DataProcessor dataProcessor = new DefaultDataProcessor(myExportConfig, exportEnum.getClz());return asyncExport(null, dataProviderMap.get(exportEnum), dataProcessor, timeout);}public BigInteger asyncExport(ExportConfig config, DataProvider<?> dataProvider, DataProcessor dataProcessor, long timeout) {BigInteger taskId = taskService.addExportTask();EXECUTOR.execute(new ExportRunnable(taskId, () -> {try {updateTaskStart(taskId);executeExport(taskId, dataProvider, dataProcessor, timeout);updateTaskEnd(taskId, dataProcessor.getUrl());} catch (Exception e) {updateTaskFail(taskId, e.getMessage());}}));/*try {updateTaskStart(taskId);executeExport(taskId, dataProvider, dataProcessor);updateTaskEnd(taskId);} catch (Exception e) {updateTaskFail(taskId, e.getMessage());}*/return taskId;}private <T> void executeExport(BigInteger taskId, DataProvider<T> dataProvider, DataProcessor dataProcessor, long timeout) {long start = System.currentTimeMillis();long currentPage = 1;Page<T> page = null;// 新建上下文对象,如需传递参数可以放在里面ExportContext context = new ExportContext();do {// todo 有没其他方法判断是否超时？ScheduledExecutorService?// 超时if (System.currentTimeMillis() - start > timeout) {throw new RejectedExecutionException("Task " + taskId + " rejected,系统执行任务超时");}page = dataProvider.getPageData(currentPage++, 1000, context);if (!Objects.isNull(page) && Objects.nonNull(page.getRecords()) && !page.getRecords().isEmpty()) {dataProcessor.processData(page.getRecords());}// 更新导出进度updateTaskProgress(taskId, page);} while (page.hasNext());dataProcessor.end();log.info("executeExport cost:" + (System.currentTimeMillis() - start));}private void updateTaskFail(BigInteger taskId, String message) {Task entity = new Task();entity.setId(taskId);entity.setStatus("FAILED");entity.setErrMsg(message);taskService.updateExportTask(entity);}private void updateTaskStart(BigInteger taskId) {Task entity = new Task();entity.setId(taskId);entity.setStatus("导出中");entity.setStartTime(LocalDateTime.now());taskService.updateExportTask(entity);}private void updateTaskEnd(BigInteger taskId, String url) {Task entity = new Task();entity.setId(taskId);entity.setStatus("SUCCESS");entity.setErrMsg(null);entity.setEndTime(LocalDateTime.now());entity.setUrl(url);taskService.updateExportTask(entity);}// todo// 暂时将进度更新到数据库，可优化private void updateTaskProgress(BigInteger taskId, Page<?> page) {Task entity = new Task();entity.setId(taskId);if (page.getTotal() <= 0) {entity.setProgress(new BigDecimal(1));} else {entity.setProgress(new BigDecimal(page.getCurrent()).divide(new BigDecimal(page.getPages()), MATH_CONTEXT));}log.info("current:" + page.getCurrent() + " totalPage:" + page.getPages() + " progress " + entity.getProgress().toString());taskService.updateExportTask(entity);}}

主要方法为：

public BigInteger asyncExport(ExportConfig config, DataProvider<?> dataProvider, DataProcessor dataProcessor, long timeout) {
}

DataProvider 数据提供接口

可根据分页分批提供数据

package com.async_export_demo.export;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;/*** 数据提供器接口（不同业务实现）*/
public interface DataProvider<T> {Page<T> getPageData(long currentPage, long pageSize, AsyncExportService.ExportContext context);ExportEnum getType();
}

DataProvider 实现类

package com.async_export_demo.export;import com.async_export_demo.mapper.OrderMapper;
import com.async_export_demo.model.Order;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;@Component
public class OrderDataProvider implements DataProvider<Order> {@Autowiredprivate OrderMapper orderMapper;@Overridepublic Page<Order> getPageData(long currentPage, long pageSize, AsyncExportService.ExportContext context) {Page<Order> queryPage = new Page<>(currentPage, pageSize);if (currentPage > 1) {// 分页总数只需要查询一次queryPage.setSearchCount(false);}QueryWrapper<Order> queryWrapper = new QueryWrapper<>();// 只导出前100万queryWrapper.le("id", 4000000);Page<Order> result = orderMapper.selectPage(queryPage, queryWrapper);if (currentPage <= 1) {context.put("total", result.getTotal());} else {result.setTotal((Long) context.get("total"));}return result;}@Overridepublic ExportEnum getType() {return ExportEnum.ORDER;}}

DataProcessor 数据处理接口

package com.async_export_demo.export;import java.util.List;/*** 数据处理器接口（不同业务实现）*/
public interface DataProcessor {void processData(List<?> data);void end();String getUrl();
}

DataProcessor数据处理实现类

package com.async_export_demo.export;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.async_export_demo.config.MyExportConfig;import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.LinkedList;
import java.util.List;public class DefaultDataProcessor implements DataProcessor {private static final DateTimeFormatter DTF = DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS");/*** 一个sheet页最多允许的数据条数*/private static final long SHEET_MAX_SIZE = 200000;private String absoluteFileName;private String fileName;private long processedDataCount = 0;private ExcelWriter excelWriter;private MyExportConfig exportConfig;private final LinkedList<WriteSheet> writeSheets = new LinkedList<>();public DefaultDataProcessor(MyExportConfig exportConfig, Class<?> head) {this.exportConfig = exportConfig;this.absoluteFileName = getFileName(exportConfig);this.excelWriter = EasyExcel.write(absoluteFileName, head).build();}@Overridepublic void processData(List<?> data) {WriteSheet writeSheet = getWriteSheet(data.size());this.excelWriter.write(data, writeSheet);processedDataCount += data.size();}@Overridepublic void end() {this.excelWriter.close();}@Overridepublic String getUrl() {// todo// 实际可能需要将本地文件推送到文件服务器，最终从文件服务器下载，此处直接返回本地地址return exportConfig.getDomain() + "/" + fileName;}private String getFileName(MyExportConfig exportConfig) {String fileName = LocalDateTime.now().format(DTF) + ".xlsx";this.fileName = fileName;return exportConfig.getRootPath() + "\\" + fileName;}private WriteSheet getWriteSheet(long currentProcessCount) {if (processedDataCount == 0) {WriteSheet writeSheet = EasyExcel.writerSheet(0, "模板").build();writeSheets.add(writeSheet);} else {long count = processedDataCount + currentProcessCount - SHEET_MAX_SIZE * (writeSheets.size() - 1);if (count > SHEET_MAX_SIZE) {writeSheets.add(EasyExcel.writerSheet(writeSheets.size(), "模板" + writeSheets.size()).build());}}return writeSheets.getLast();}}

Controller

package com.async_export_demo.controller;import com.async_export_demo.export.AsyncExportService;
import com.async_export_demo.export.ExportEnum;
import com.async_export_demo.export.SyncExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigInteger;
import java.net.URLEncoder;@RestController
@RequestMapping(value = "/export")
public class ExportController {@Autowiredprivate AsyncExportService asyncExportService;@Autowiredprivate SyncExportService syncExportService;@GetMapping(value = "/exportAsync")public String exportData(@RequestParam("type") ExportEnum exportEnum) {BigInteger taskId = asyncExportService.asyncExport(exportEnum);return taskId.toString();}}

前端

<!DOCTYPE html>
<!-- saved from url=(0018) -->
<html class="wide wow-animation desktop landscape rd-navbar-fullwidth-linked" lang="en">
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/><title></title><style>table, th, td {border: 1px solid black;border-collapse: collapse; /* 可选，用于合并相邻单元格的边框 */}th, td {padding: 10px; /* 可选，设置单元格内边距 */}</style>
</head>
<body>
<!-- Page-->
<div id="root"><button onclick="exportData()">导出</button><table><thead><tr><th style="width: 100px;text-align: left">任务id</th><th style="width: 100px;text-align: left">类型</th><th style="width: 100px;text-align: left">状态</th><th style="width: 200px;text-align: left">创建时间</th><th style="width: 200px;text-align: left">开始时间</th><th style="width: 200px;text-align: left">结束时间</th><th style="width: 200px;text-align: left">错误信息</th><th style="width: 200px;text-align: left">下载地址</th><th>进度</th></tr></thead><tbody><tr th:each="task ,taskIndex: ${tasks}"><td th:text="${task.id}">任务id</td><td th:text="${task.type}">类型</td><td th:text="${task.status}">状态</td><td th:text="${task.createTime}">创建时间</td><td th:text="${task.startTime}">开始时间</td><td th:text="${task.endTime}">结束时间</td><td th:text="${task.errMsg}">错误信息</td><td><a th:href="${task.url}" th:text="${task.url}">下载地址</a></td><td th:text="${task.progress}">进度</td></tr></tbody></table>
</div>
<script>function exportData() {var xhr = new XMLHttpRequest();xhr.open('GET', '/export/exportAsync?type=ORDER', true);xhr.onload = function () {if (this.status >= 200 && this.status < 300) {console.log(this.responseText); // 处理响应数据} else {console.error('Request failed:', this.statusText);}};xhr.onerror = function () {console.error('Request error');};xhr.send();}function refresh() {// 你的刷新逻辑console.log('刷新一次');location.reload();// 例如，你可以在这里进行页面局部刷新或者数据更新setTimeout(refresh, 4000); // 1000毫秒后再次调用refresh函数}// demo, 暴力刷新。。。。setTimeout(refresh, 4000); // 初始调用一次以开始循环
</script></body>
</html>

测试

代码地址：https://gitee.com/husong_zone/async_export_demo

http://www.lryc.cn/news/609790.html

相关文章：

  • VBA-Excel图片下载到本地文件夹
  • 基于知识图谱增强的RAG系统阅读笔记(一)提升大语言模型的准确性
  • 从exec到Shell：深度解析Linux进程等待，程序替换与自主Shell实现
  • Assistant API——构建基于大语言模型的智能体应用
  • 在 C++ 中实现类似 Vue 3 的 Pinia 状态管理库
  • 反转字符串中的元音字母：Swift 双指针一步到位
  • 数据在内存中的存储深度解析
  • 【基础完全搜索】USACO Bronze 2019 January - 猜动物Guess the Animal
  • [找出字符串中第一个匹配项的下标]
  • OCR 精准识别验讫章：让登记与校验更智能
  • 嵌入式 - 数据结构：查找至双向链表
  • 用户管理——配置文件和命令
  • 【数据库】使用Sql Server创建索引优化查询速度，一般2万多数据后，通过非索引时间字段排序查询出现超时情况
  • Linux-Shell脚本基础用法
  • 【VSCode】 使用 SFTP 插件实现多服务器同步
  • 随机森林知识点整理：从原理到实战
  • 区块链基础之Merkle树
  • 数据结构——单向链表
  • CMakeLists.txt学习
  • 《JavaScript高级程序设计》读书笔记 35 - 代理捕获器、反射方法以及代理模式
  • React 19 + Next.js 15 中实现混合布局
  • React配置proxy跨域
  • ref和reactive的区别
  • 通过 Flink 和 CDC 从 Oracle 数据库获取增量数据，并将这些增量数据同步到 MySQL 数据库中
  • [GESP202306 四级] 2023年6月GESP C++四级上机题超详细题解，附带讲解视频！
  • Spring Boot + ShardingSphere 实现分库分表 + 读写分离实战
  • AWS VPC Transit Gateway 可观测最佳实践
  • 【物联网】基于树莓派的物联网开发【23】——树莓派安装SQLite嵌入式数据库
  • 16_OpenCV_漫水填充(floodFill)
  • Nginx vs Spring Cloud Gateway：限流功能深度对比与实践指南