Excel 批处理框架与自定义线程池封装

1. 背景与痛点

业务背景

ERP 系统中存在大量 Excel 导入导出场景：

• 商品批量导入（万级 SKU）、调拨单导入、出入库单导入
• 财务报表导出、销售数据导出、库存台账导出
• 平台订单导入（Amazon、eBay、TikTok Shop 等）
• 财务核算数据导入（结算单、退款单、物流账单）

技术痛点

在引入框架之前，每做一个导入导出功能，都要重复写一套完整的流程：

// 导入示例：每个业务都要写这坨模板代码
// 1. 读取 Excel → 2. 逐行解析 → 3. 校验 → 4. 分批入库 → 5. 写错误文件
// 6. 状态更新 → 7. 线程池管理 → 8. 取消支持

核心问题：

• 重复轮子：每个导入导出模块都在重复"读取 Excel → 分页 → 校验 → 入库"这套流程
• 内存爆炸：直接用 new ExcelHelper() 一次性加载整个文件到内存，大文件直接 OOM
• 线程池散落：new ThreadPoolExecutor 散落在各处，参数不统一（coreSize 有写 2 的、有写 10 的）
• 错误处理不一致：错误文件格式五花八门，有的直接写 CSV、有的用不同模板
• 无法取消：任务执行到一半，用户点取消，没有统一机制

2. 技术方案

整体架构

业务实现层（继承框架）
  │
  ├─ AbstractImportCommonTaskHandler      → 导入模板（支持 V1/V2/V3/原子版）
  ├─ AbstractExportCommonTaskHandler       → 导出模板
  └─ AbstractZipExcelTaskHandler           → ZIP 多文件导入模板
  │
基础框架层（通用能力）
  ├─ AbstractGeneralExcelTaskHandler       → 通用流程编排（流式读取/取消支持）
  ├─ AbstractExcelTaskHandler              → 简化版流程
  ├─ GeneralTaskService                    → 任务生命周期管理
  ├─ TaskRunningState                       → 运行状态管理（取消标记）
  └─ BaseTaskThreadPoolConfig               → 线程池统一配置
  │
基础设施层（Excel 操作）
  ├─ ExcelHelper                            → 流式读写（SAX 模式，大文件不 OOM）
  ├─ FileStorage                            → 文件存储抽象
  └─ @Header 注解                           → 字段与表头的映射关系
  │
模板下载层（代码化模板生成）
  ├─ DownloadTemplateContext                → 下载上下文
  ├─ AutoGenerateTemplateHandler           → 零模板：基于 @Header 反射生成表头
  ├─ AnnotationValidationBaseDataHandler    → 数据验证：基于注解注入下拉数据
  └─ FillDataExecutor 策略族                → 品牌/类目/仓库/店铺等字典数据填充

核心流程（导入）

用户上传 Excel → 创建 GeneralTask → 提交线程池执行
        ↓
AbstractGeneralExcelTaskHandler.handle()
        ↓
流式读取 Excel（ExcelHelper.buildStreamingReaderExcelHelper）
        ↓
consumeObjectsPaging(每 300 条一页) → doValidate(每行校验)
        ↓
校验通过 → validateSuccessDataList → doImport() → 入库
校验失败 → 写入错误文件 + 错误原因列
        ↓
任务完成 → 更新状态 + 记录成功/失败/总数

3. 实现细节

自定义线程池（BaseTaskThreadPoolConfig）

new ThreadPoolExecutor(
    5,      // corePoolSize = 5
    10,     // maxPoolSize = 10（业务导入导出不耗 CPU，线程数不宜过大）
    2000L,  // keepAliveTime = 2s（闲时回收）
    MILLISECONDS,
    new LinkedBlockingDeque<>(200),  // 队列容量 200
    new TaskThreadFactory()          // 自定义线程名 factory
);

关键设计：使用 LinkedBlockingDeque（无界队列的固定容量版），避免任务堆积导致 OOM；线程工厂确保线程名可追踪。

流式读取（AbstractGeneralExcelTaskHandler）

// 关键：isStreamMode() 默认返回 true
protected boolean isStreamMode() {
    return true; // 使用 SAX 模式的 ExcelHelper
}

// 流式读取，不一次性加载整个文件
excelHelper = ExcelHelper.buildStreamingReaderExcelHelper(inputStream, 0);

// 分页消费，内存占用恒定（每页 300 条）
excelHelper.consumeObjectsPaging(0, clazz, getImportSize(), (pageData) -> {
    // 处理每页数据
});

分页导入逻辑（AbstractImportCommonTaskHandler）

// 每页 300 条，平衡内存与数据库交互频率
private static final int IMPORT_SIZE = 300;

protected int getImportSize() {
    return IMPORT_SIZE; // 子类可覆盖
}

核心流程：

1. preliminaryData() — 全局预热数据（如加载品牌列表、仓库列表用于校验）
2. doValidate(T rowData, context) — 单行校验，返回 null 则通过，返回错误信息则写入错误文件
3. doImport(List<T> data, context) — 批量入库，子类自行选择分页策略
4. 钩子方法：taskStartEvent / taskCompleteEvent / taskFailedEvent / postProcessor

错误文件生成

// 基于空模板生成错误文件，写入原数据 + 错误原因列
Pair<File, ExcelHelper> result = generateErrorFile(task);
// 错误文件追加行：原数据 + 错误信息列
context.getWriteErrorExcelHelper().appendRow(itemNum, data, getters);
context.getWriteErrorExcelHelper().setCellValue(itemNum, headerLength, validate);

任务取消机制（TaskRunningState）

// 每个任务注册一个取消标记
runningState.start(taskId);    // 标记开始
runningState.cancel(taskId);    // 用户请求取消 → 设置标记
runningState.isRunning(taskId); // 执行中检查：标记存在则继续，标记消失则中断
runningState.done(taskId);      // 标记移除

模板下载体系（代码化）

框架支持三种模板下载模式（TemplateDownloadHandlerType）：

模式	说明	典型场景
NORMAL	从 resources/templates/ 下读取 .xlsx 文件	已有固定模板（格式复杂）
ANNOTATION_FILL	读取模板文件 + 反射表头 + 注入下拉数据	有模板但要补充校验数据
AUTO_GENERATE	零模板：基于 @Header 注解反射生成表头 + 下拉	新功能，快速上线

AUTO_GENERATE 核心逻辑

// 1. 基于 importObj 的 @Header 注解反射生成表头
Field[] fields = importObjClass.getDeclaredFields();
for (Field field : fields) {
    if (field.isAnnotationPresent(Header.class)) {
        String headerName = field.getAnnotation(Header.class).value();
        // 写入表头单元格
    }
}

// 2. 基于 @Header 的字段名查找对应的下拉数据
Map<String, String[]> validationMap = context.getValidationMap();
// 例如：表头"品牌" → 找到 BrandFillDataExecutor → 填充品牌列表

// 3. 创建 Excel 数据验证（下拉框）
DataValidation validation = createCustomDataValidation(dataValidationHelper, col, baseDataCol);
sheet.addValidationData(validation);

FillDataExecutor 策略族

public interface FillDataExecutor {
    TemplateFillType type();
    String[] doFill(DownloadTemplateContext context);
}

// 品牌数据填充
public class BrandFillDataExecutor implements FillDataExecutor {
    @Override
    public String[] doFill(DownloadTemplateContext context) {
        return brandService.getAllBrandNames(); // 从数据库加载
    }
}

// 仓库数据填充
public class WarehouseCodeFillDataExecutor implements FillDataExecutor { ... }

// 品类数据填充
public class CategoryFillDataExecutor implements FillDataExecutor { ... }

// 工厂自动匹配
public class FillDataExecutorFactory {
    public static FillDataExecutor getExecutor(TemplateFillType type) { ... }
}

下载模板配置枚举（DownloadTemplateConfig）

超过 100 个模板枚举，分为三种接入方式：

// 方式一：纯静态模板（已有 .xlsx 文件）
ACCOUNT_IMPORT_TEMPLATE("/templates/mall/Account_Import_Template.xlsx")

// 方式二：静态模板 + 动态类（补充下拉数据）
LISTING_IMPORT_TEMPLATE("/templates/sales/ListingImportTemplate.xlsx", ImportGoods.class)

// 方式三：零模板自动生成（只提供类）
IMPORT_SKU_HS_CODE_TEMPLATE(ImportSkuHsCode.class)
GOODS_IMPORT_TEMPLATE("/templates/sales/ImportNormalGoodsTemplate.xlsx", ImportNormalGoods.class)

4. 技术栈与亮点归纳

技术栈清单

• Java / Spring Boot
• Apache POI（SAX 模式流式读写 + SXSSF 模式大文件导出）
• MyBatis / MyBatis-Plus
• 自定义线程池（LinkedBlockingDeque）
• 设计模式：模板方法（抽象基类）、策略（FillDataExecutor）、工厂（HandlerFactory）
• 反射 + 注解（@Header 表头映射）

技术亮点

• 流式读取：SAX 模式逐行解析，百万行文件内存占用恒定（不 OOM）
• 分页消费：每页 300 条，平衡内存与 DB 交互频率
• 行级校验 + 错误文件：校验失败立即写出，不阻塞后续行
• 任务可取消：TaskRunningState 标记机制，执行中随时响应取消
• 模板下载代码化：新增一个导入功能，只需定义 DTO 类 + @Header 注解，无需手写 Excel 模板文件
• 下拉数据自动注入：表头名称自动匹配 FillDataExecutor，生成带校验下拉的模板
• 100+ 模板枚举：全系统统一管理，一处修改全局生效

5. 难点与亮点剖析

业务难点

• 大文件导入：商品导入场景可能有 10 万+ 行，直接加载到内存 OOM
• 模板维护成本：100+ 个模板文件，手工维护格式容易出错
• 校验逻辑分散：不同业务校验规则差异大（品牌、仓库、平台店铺等），需要预热数据

技术难点

• 流式写入：SAX 模式只能追加行，不能随机写——框架中 appendRow 通过先读模板再追加实现
• 错误文件的并发写入：同一错误文件可能被多线程同时写入——通过 generalFileFlag 确保只初始化一次
• 校验数据预热：preliminaryData 钩子在读取 Excel 之前执行，避免逐行查询数据库

实现亮点

1. 零模板下载：开发者只需定义 DTO + @Header 注解，模板自动生成上线，无需设计师出 Excel
2. 流式读写双模式：导入用 SAX（省内存），导出用 SXSSF（百万行不 OOM）
3. 错误文件带原数据 + 错误原因：运营人员直接看到哪行错了、为什么错
4. 任务状态持久化：每个导入导出任务有独立 GeneralTask 记录（ID、状态、进度、文件路径、结果统计），可查询可回溯

6. 替代方案对比

方案一：EasyExcel（已部分采用）

• 实现：阿里的 EasyExcel 封装了 POI SAX 模式
• 优劣：流式读取能力强，但只解决了"怎么读"，不解决"怎么组织业务逻辑"
• 结论：框架底层已使用 EasyExcel 的核心思路，但业务编排层（分页、校验、错误文件、任务管理）需要自建

方案二：Python + pandas（已排除）

• 实现：定时任务用 Python 脚本处理大文件 Excel
• 优劣：处理速度快，但无法融入 Java 微服务链路，数据一致性难保证
• 结论：ERP 是 Java 微服务体系，不引入多语言依赖

方案三：每业务单独写一套（历史方案，已重构）

• 每个导入模块各自写读取 → 校验 → 入库 → 写错误文件
• 问题：重复代码 3000+ 行，格式不统一，无法取消，无任务管理
• 结论：框架化是必然选择

7. 技术选型与方案抉择复盘

选型思路

2022 年业务快速迭代时，发现每个新功能都要写一套类似的导入导出代码，重复且质量参差不齐。团队决定将"读 Excel → 分页 → 校验 → 入库 → 写错误文件 → 状态管理"全链路抽象为基础框架，让业务开发者只关注数据校验和入库逻辑。

优点

• 开发效率大幅提升：新做一个导入功能，从"写 300 行代码"降到"写 50 行（doValidate + doImport）"
• 格式统一：错误文件、任务状态、文件命名全部统一，运营体验一致
• 模板下载代码化：消灭了手工维护 Excel 模板文件的工作量

缺点 / 已知不足

• 模板生成依赖 @Header 注解：字段改名或调整顺序时，需要同步修改代码
• SAX 写入限制：只能追加行，无法实现"插入行"或"修改指定行"——这是 SAX 模式的固有限制
• FillDataExecutor 加载时机：模板下载时实时从 DB 加载下拉数据，高并发下可能拖慢响应时间（可加缓存）

可优化点

• FillDataExecutor 增加本地缓存（L1 Caffeine）避免每次下载都查 DB
• 导出场景增加异步下载通知（任务创建 → 队列处理 → 完成后钉钉/站内信通知）
• 模板下载增加水印功能（品牌 Logo 或公司名称）
• 校验失败时支持"部分成功"模式（当前全量失败才回滚，未来可改为逐条提交）

8. 成果与数据

• 接入规模：框架支撑全 ERP 系统的导入导出功能，枚举中已登记 100+ 个模板
• 最大单次处理：曾成功导入单文件 50 万行数据（流式读取，内存占用 < 200MB）
• 模板下载效率：AUTO_GENERATE 模式模板生成耗时 < 500ms
• 错误文件覆盖率：导入错误 100% 生成带错误原因的 Excel 文件，运营无需提工单询问
• 开发效率：新做一个导入功能从 2 人天降至 0.5 人天（仅实现校验 + 入库逻辑）

9. 面试题清单

1. 【基础】Apache POI 的 SAX 模式和 DOM 模式有什么区别？为什么大文件 Excel 要用 SAX？
   • 考察点：DOM 模式一次性加载整个 Workbook 到内存，SAX 模式逐行事件驱动解析，内存占用与文件大小无关
2. 【基础】ThreadPoolExecutor 的拒绝策略有哪些？BaseTaskThreadPoolConfig 用的是哪种？
   • 考察点：AbortPolicy（抛异常）、CallerRunsPolicy（由调用方执行）、DiscardOldestPolicy、DiscardPolicy；默认 AbortPolicy，队列满时拒绝
3. 【进阶】consumeObjectsPaging 中每页 300 条是怎么确定的？大了有什么问题？小了有什么问题？
   • 考察点：平衡内存占用（每页数据在内存中）与数据库交互次数；300 是经验值，根据 DB 连接池和单行数据大小调整
4. 【进阶】任务取消是如何实现的？为什么不能用 Thread.stop()？
   • 考察点：volatile 标记位 + 循环检查 + 中断标记；Thread.stop() 会导致锁不释放、资源未清理
5. 【进阶】错误文件并发写入时，如何保证只初始化一次？
   • 考察点：volatile boolean generalFileFlag + if 判断；可进一步引申到 DCL 模式或 ConcurrentHashMap
6. 【原理】@Header 注解是如何被 ExcelHelper 解析成 getter 方法列表的？反射效率如何保证？
   • 考察点：getDeclaredFields() + isAnnotationPresent() + get() 缓存；框架中应提前缓存 getter 列表而不是每次调用
7. 【原理】AutoGenerateTemplateHandler 如何实现"表头名称 → 下拉数据"的自动匹配？
   • 考察点：Map<String, String[]> validationMap，表头名作为 key，FillDataExecutor 工厂根据类型注入数据
8. 【场景】如果导入数据量很大（50 万行），如何保证数据库写入不锁表？
   • 考察点：批量 INSERT（每 300 条 commit 一次）、分批事务、避免长事务锁住索引
9. 【设计】如果需要支持"导入前预览"（用户上传后先展示前 10 条数据，确认后再执行导入），框架需要怎么改造？
   • 考察点：两阶段设计（预览模式 vs 执行模式）、预览不走 doImport 只做 doValidate
10. 【延伸】和 EasyExcel 的 @AnalysisEventListener 监听器模式相比，这个框架的 consumeObjectsPaging 分页消费有什么优势？
    • 考察点：分页可控（内存边界明确）vs 逐行监听（每行都可能触发 DB 操作）；分页模式更利于批量优化

10. 简历提炼版

设计并实现通用 Excel 批处理框架，基于 SAX 流式读取实现百万行大文件零 OOM 导入；通过模板方法模式封装「读取 → 分页 → 校验 → 入库 → 错误文件」全链路，业务方仅需实现 doValidate + doImport；独创零模板下载机制，基于 @Header 注解 + 反射自动生成表头并注入下拉校验数据；支撑全 ERP 系统 100+ 导入导出功能，单文件最大 50 万行，开发效率提升 4 倍。

---

关键源码路径

文件	路径
线程池配置	creekson-base-task/.../config/BaseTaskThreadPoolConfig.java
通用任务处理器	creekson-base-task/.../task/AbstractGeneralExcelTaskHandler.java
简化任务处理器	creekson-base-task/.../task/AbstractExcelTaskHandler.java
导入模板	creekson-api-web/.../task/importcommontemplate/AbstractImportCommonTaskHandler.java
导出模板	creekson-api-web/.../task/exportcommontemplate/AbstractExportCommonTaskHandler.java
导入模板 V2/V3	creekson-api-web/.../task/importcommontemplate/Abstract*V2*.java
ZIP 多文件导入	creekson-api-web/.../task/importcommontemplate/zip/AbstractZipExcelTaskHandler.java
任务服务	creekson-base-task/.../task/impl/GeneralTaskServiceImpl.java
运行状态管理	creekson-base-task/.../task/TaskRunningState.java
下载上下文	creekson-api-web/.../file/download/DownloadTemplateContext.java
自动生成处理器	creekson-api-web/.../file/download/handler/AutoGenerateTemplateHandler.java
数据填充策略接口	creekson-api-web/.../file/download/fill/FillDataExecutor.java
模板下载配置枚举	creekson-api-web/.../file/download/DownloadTemplateConfig.java