Excel导入导出为什么总出错？模板、错误行和事务回滚实战

企业系统里Excel 导入导出看起来是小功能实际上很容易成为上线后的高频问题。用户说“我就是传个表”系统却可能遇到模板版本不一致、列名被改、日期格式混乱、编码重复、必填项为空、字典值写错、权限范围不清、导入一半失败等问题。最糟糕的体验不是导入失败而是只弹一句“导入失败”。用户不知道哪一行错了不知道怎么改也不知道已经成功了多少条。开发再去查日志业务人员再重新整理文件时间全浪费在来回沟通上。所以 Excel 导入导出不是“读文件、写数据库”这么简单。更稳的设计应该包含模板版本、字段说明、预校验、错误行回执、导入策略、事务边界、重复导入控制、导出权限和导出字段脱敏。本文用一个通用场景做示例批量导入产品资料。第 17 行规格为空第 18 行产品编码和系统已有数据重复用户需要拿到一份带错误说明的回执文件修完后重新上传。这个例子也适用于客户资料、资产台账、供应商、合同清单、库存期初数据等批量维护场景。示例环境Java 17、Spring Boot 风格服务层、MySQL 8.x。代码只保留关键骨架重点放在导入导出的设计边界和验证方式。目录Excel导入导出先分清四个阶段输入案例批量导入产品资料模板设计别让用户猜字段含义MySQL表结构批次、行明细和错误回执实现骨架先校验再入库导出设计权限、字段和脱敏SQL验证上线后查导入质量异常边界和上线验收小结和延伸阅读一、Excel导入导出先分清四个阶段一个可靠的 Excel 导入不应该从“直接入库”开始而应该分成四个阶段。阶段目标产物模板下载告诉用户该填什么、怎么填带版本号、字段说明和示例行的模板预校验找出格式、必填、字典、重复、权限问题校验结果和错误行确认导入用户确认采用全回滚还是部分成功策略导入批次和行状态结果回执告诉用户成功多少、失败多少、失败原因成功统计和错误回执文件导出也要分阶段先按 DataScope 查数据再按角色决定字段再做脱敏最后生成文件。不能因为“用户能看到列表”就默认能导出所有字段。很多系统的问题出在把这几个阶段揉成一个按钮。用户上传 Excel 后系统边读边入库遇到错误就抛异常。这样做简单但一旦数据量稍大、错误稍多就很难解释结果。图1模板、预校验、确认导入和结果回执要分开设计。二、输入案例批量导入产品资料本文固定一组输入用来贯穿后面的模型、代码和 SQL 验证。导入批次IMP-20260925-001 导入对象产品资料 模板版本PRODUCT_IMPORT_V3 上传人user_id 1008产品运营 所属公司company_id 1 导入策略先预校验确认后导入默认全回滚可配置部分成功 文件行数100 行数据另有 1 行表头和 1 行示例说明 第17行错误规格为空 第18行错误产品编码 P-1008 已存在 预期结果98 行可导入2 行失败生成错误回执标出 Excel 原始行号和错误原因这个案例里的关键点不是产品资料而是用户需要知道“哪一行错了、为什么错、怎么改”。如果系统只告诉他“导入失败”他只能一行一行猜。因此导入结果要保留原始行号。用户在 Excel 里看到的是第 17 行、第 18 行系统回执也必须对应这个行号而不是告诉他“第 15 条数据错误”。表头、示例行、隐藏行都可能让行号错位。三、模板设计别让用户猜字段含义好的导入模板不是空白表头而是一个小型说明文档。模板至少要包含这些信息内容作用模板版本判断用户是不是用了旧模板字段中文名让业务人员知道填什么字段编码让系统稳定识别列是否必填提前减少空值错误示例值说明日期、金额、字典写法字典说明告诉用户可选值注意事项说明编码唯一、名称长度、导入策略不要只靠列顺序识别字段。用户可能插入列、隐藏列、移动列。更稳的做法是在隐藏行或模板元数据里保存字段编码例如product_code、product_name、spec、unit。解析时按字段编码映射而不是按第几列硬读。模板版本也很重要。系统升级后如果新增了“产品分类”必填字段旧模板继续上传就会产生一批解释不清的错误。模板里应该有版本号上传时先校验版本版本不匹配时提示重新下载模板。图2模板不是空白表头要把版本、字段编码、必填和示例说清楚。四、MySQL表结构批次、行明细和错误回执导入不能只记录最终数据。至少要有批次表和行明细表方便用户回看导入结果也方便开发排查。CREATETABLEimport_batch(idBIGINTPRIMARYKEYAUTO_INCREMENT,company_idBIGINTNOTNULL,batch_noVARCHAR(64)NOTNULL,template_codeVARCHAR(80)NOTNULL,template_versionVARCHAR(32)NOTNULL,import_objectVARCHAR(80)NOTNULL,import_strategyVARCHAR(32)NOTNULL,statusVARCHAR(32)NOTNULL,total_rowsINTNOTNULLDEFAULT0,success_rowsINTNOTNULLDEFAULT0,failed_rowsINTNOTNULLDEFAULT0,source_file_idBIGINTNULL,receipt_file_idBIGINTNULL,create_byBIGINTNOTNULL,create_timeDATETIMENOTNULL,finish_timeDATETIMENULL,UNIQUEKEYuk_import_batch_no(company_id,batch_no),KEYidx_import_batch_status(company_id,status,create_time));CREATETABLEimport_row_result(idBIGINTPRIMARYKEYAUTO_INCREMENT,batch_idBIGINTNOTNULL,excel_row_noINTNOTNULL,row_keyVARCHAR(120)NULL,row_statusVARCHAR(32)NOTNULL,error_codeVARCHAR(80)NULL,error_messageVARCHAR(500)NULL,raw_json JSONNULL,normalized_json JSONNULL,create_timeDATETIMENOTNULL,UNIQUEKEYuk_import_row(batch_id,excel_row_no),KEYidx_import_row_status(batch_id,row_status));import_batch记录一次导入的总体情况。用户回来查历史导入时应该能看到总行数、成功行数、失败行数、源文件和回执文件。import_row_result记录每一行的校验结果。错误行必须保存 Excel 原始行号、错误原因和原始数据。这样才能生成回执也能解释为什么某一行没入库。import_strategy可以是ALL_OR_NOTHING或PARTIAL_SUCCESS。前者表示有一行失败就整批不入库适合财务、库存期初等强一致场景后者表示正确行可以入库错误行回执给用户修复适合产品资料、客户资料等维护场景。图3批次表看整体结果行明细表解释每一行成功或失败。五、实现骨架先校验再入库导入实现可以分成两步解析校验、确认入库。第一步只解析文件和校验不直接写业务表。它负责检查模板版本、必填字段、数据类型、字典值、重复编码和权限范围。publicImportPreviewpreview(ImportFilefile,LongoperatorId){TemplateMetatemplatetemplateService.readTemplateMeta(file);templateService.requireSupported(PRODUCT_IMPORT,template.version());ListImportRowrowsexcelReader.readRows(file,template);ListRowResultresultsnewArrayList();for(ImportRowrow:rows){RowValidatorvalidatorRowValidator.forRow(row);validator.required(product_code,产品编码);validator.required(product_name,产品名称);validator.required(spec,规格);validator.dictionary(unit,product_unit,dictService);validator.unique(product_code,productRepository::existsByCode);results.add(validator.result());}returnimportBatchRepository.savePreview(PRODUCT_IMPORT,template.version(),operatorId,results);}第二步根据用户确认的策略入库。Transactional(rollbackForException.class)publicImportResultconfirmImport(LongbatchId,ImportStrategystrategy,LongoperatorId){ImportBatchbatchimportBatchRepository.lockById(batchId);ListRowResultrowsimportRowRepository.listByBatch(batchId);longfailedrows.stream().filter(RowResult::failed).count();if(strategyImportStrategy.ALL_OR_NOTHINGfailed0){thrownewServiceException(存在错误行不能执行整批导入);}for(RowResultrow:rows){if(row.failed()){continue;}ProductproductproductMapper.from(row.normalizedJson());productRepository.insert(product);importRowRepository.markImported(row.id());}importBatchRepository.finish(batchId,strategy,operatorId);returnimportBatchRepository.summary(batchId);}这两段代码只保留骨架实际项目里还要考虑大文件流式读取、批量插入、缓存字典、批量查重、错误回执生成等。但核心原则不变先让用户看见错误再决定是否入库。六、导出设计权限、字段和脱敏导出经常被低估。很多系统列表页做了权限过滤但导出接口直接查全表页面隐藏了成本价导出却把成本价带出去页面手机号脱敏导出却是明文。导出至少要控制三件事。第一数据范围要和列表一致。用户在页面只能看本部门数据导出也只能导出本部门数据。不要为导出单独写一条绕过 DataScope 的 SQL。第二字段范围要按角色控制。普通员工可以导出产品编码、名称、规格管理员可以导出更多字段成本价、供应商底价、客户手机号等字段要按权限决定。第三敏感字段要脱敏或审批。导出比页面风险更高因为文件可以转发、复制、上传到外部。对敏感数据量较大的导出可以做异步任务和下载有效期。可以把导出也当成一次业务动作记录下来exportTypePRODUCT operatorId1008 dataScopeDEPT fieldsproduct_code,product_name,spec,unit rowCount1280 fileExpireTime2026-09-26 10:30:00导入和导出是一组能力。导入强调“别把坏数据写进去”导出强调“别把不该看的数据带出去”。图4导出要同时控制数据范围、字段范围和敏感字段。七、SQL验证上线后查导入质量导入功能上线后要能查导入成功率、错误类型和重复导入情况。查看最近导入批次SELECTbatch_no,template_version,import_strategy,status,total_rows,success_rows,failed_rows,create_timeFROMimport_batchWHEREcompany_id1ORDERBYcreate_timeDESCLIMIT20;查看错误类型分布SELECTr.error_code,COUNT(*)AScntFROMimport_row_result rJOINimport_batch bONb.idr.batch_idWHEREb.import_objectPRODUCT_IMPORTANDb.create_timeDATE_SUB(NOW(),INTERVAL7DAY)ANDr.row_statusFAILEDGROUPBYr.error_codeORDERBYcntDESC;预期结果示例error_code | cnt REQUIRED_SPEC | 12 DUPLICATE_CODE | 5 INVALID_UNIT | 3检查是否有批次统计和行明细不一致SELECTb.id,b.batch_no,b.total_rows,COUNT(r.id)ASrow_countFROMimport_batch bLEFTJOINimport_row_result rONr.batch_idb.idGROUPBYb.id,b.batch_no,b.total_rowsHAVINGb.total_rowsCOUNT(r.id);预期结果empty set检查导出是否出现超权限字段可以从导出日志或文件生成记录里查SELECTexport_no,operator_id,fields,create_timeFROMexport_taskWHEREfieldsLIKE%cost_price%ANDcreate_timeDATE_SUB(NOW(),INTERVAL7DAY);这条不一定为空但每条都应该能解释谁导出的为什么有权限导出成本价。图5导入验收看错误行和批次一致性导出验收看权限字段和敏感数据。八、异常边界和上线验收Excel 功能上线前至少要考虑这些边界。旧模板上传模板版本不匹配时不要尝试“兼容一下”。应该明确提示用户下载新模板避免字段错位造成脏数据。错误行号错位错误回执必须显示 Excel 原始行号。用户不关心系统内部第几条数据他只关心 Excel 第几行要改。重复导入同一文件重复上传、同一批次重复确认、同一编码重复入库都要有防护。可以用批次号、文件摘要、业务唯一键共同控制。部分成功策略部分成功适合基础资料维护但不适合所有场景。财务期初、库存期初、组织架构导入更适合全回滚否则会出现数据不完整。大文件导入不要把大文件一次性读进内存。超过一定行数后建议异步任务处理并让用户在任务中心查看结果。导出超时大批量导出不要同步阻塞页面。可以生成导出任务完成后通知用户下载文件设置有效期。上线验收可以按下面清单执行模板包含版本号、字段编码、必填说明和示例行。上传旧模板会被明确拒绝。必填为空、字典错误、编码重复都能定位到 Excel 原始行号。错误回执能保留原始数据和错误原因。全回滚策略下有错误行不会写入任何业务数据。部分成功策略下成功行入库失败行生成回执。重复确认同一批次不会重复入库。导入批次统计和行明细数量一致。导出复用列表 DataScope。导出字段按角色控制敏感字段有脱敏或审批。九、小结和延伸阅读Excel 导入导出的核心不是把文件读出来也不是把数据写进去而是让用户和系统对“哪份模板、哪一行、哪个字段、错在哪里、是否入库、能否导出”有同一套可验证口径。实际落地时可以先把模板版本、预校验、错误回执和批次记录做好。代码不一定一开始很复杂但流程要清楚。只要用户能拿到明确错误行开发能用 SQL 查清导入结果后续再优化大文件、异步任务和导出审批就有基础。延伸阅读Apache POI 官方文档Spring Framework声明式事务管理MySQL 8.4CREATE TABLE