有段时间我接到一个需求业务部门每天把Excel报价单丢进一个共享目录希望系统能把新数据自动读进SQL Server全程不靠人点按钮。听到这个需求我的第一反应是“读个Excel写个库而已”真动手才发现容易的部分是Excel读取和SQL写入难的部分全在“实时”这两个字上文件刚传到一半要不要读同一个文件被触发多次怎么去重Excel单元格类型五花八门怎么统一这些问题要是不处理上生产环境就是事故现场。下面我把这套方案完整拆开讲从选型到部署照着做基本能复现一套可用的实时导入链路。1. 需求拆解先弄清楚“实时”到底指什么开发最忌讳拿到标题就开写代码。接到“Excel实时导入数据库”这种需求第一件事不是找Excel库而是把“实时”这个词定义清楚。是秒级延迟还是分钟级就能接受触发源在人还是在系统这些直接决定整个架构长什么样。1.1 两种技术路径的对比与选择我见过不少团队一上来就写定时任务每5分钟扫一次目录有文件就导入。这当然是一种“实时”但准确说法是“准实时”延迟取决于轮询间隔轮询太频繁又白耗资源。另一种做法是走文件系统事件驱动也就是用FileSystemWatcher监听目录文件一落盘就触发导入流程。两条路我都实际跑过各有适用场景。方案触发方式典型延迟复杂度适合场景定时扫描目录轮询等于轮询间隔通常30秒~5分钟低文件量不大延迟容忍度高文件系统监听事件驱动秒级中对时效有要求文件到达节奏不固定数据库轮询轮询等于轮询间隔中上游没有文件数据在中间表里当时我这边业务方明确要求“文件放到目录里1分钟内必须能在数据库查到”而且要支持连续放文件。基于这个诉求文件监听更合适。如果你只做每天一次的批量同步定时扫描反而更省心没必要为了“实时”两个字硬上事件驱动。1.2 整体数据流设计确定方案后我把整个流程画成一条流水线后续所有代码都是围绕这条线展开文件落盘 → 监听器发现新文件 → 等待文件写入完成且不被占用 → 用NPOI解析Excel → 按映射规则把列对应到数据库字段 → 逐行校验数据类型和必填项 → 用SqlBulkCopy批量写入 → 记录导入日志和文件指纹 → 把源文件移动到归档目录。这个设计里有个关键思路监听器只负责“发现文件”不负责“处理文件”。发现事件触发后把文件路径丢给一个后台处理队列由单线程的消费者去执行后续步骤。这样能避免同一个文件被多个并发任务重复处理也能在文件写入到一半时做等待重试。后面每一章我会拆开讲。2. Excel读取选型NPOI为什么是稳妥答案Excel读取在C#生态里可选方案不少但真正适合放到服务器上的其实不多。这里我把常见方案挨个对比一遍重点说清楚为什么我最终选了NPOI以及NPOI用起来有哪些文档里不会写的坑。2.1 COM加载项和OLEDB为什么先被排除先说Excel COM组件。很多老项目会直接引Microsoft.Office.Interop.Excel代码写起来很直观但在生产环境里它几乎是灾难服务器上必须装完整版Office授权和性能先不谈COM实例在并发访问时会因为权限、会话冲突报一堆莫名其妙的错进程一旦挂掉Excel.exe会残留在服务器里越积越多最后把内存吃光。别问我是怎么知道的清理僵尸进程这种事干一次就够了。OLEDB是另一种常见选择写法类似连接数据库通过Jet或ACE驱动把xlsx当数据源来查询。但它的类型推断很不稳定——同一列如果前面十几行都是数字、后面出现一个文本驱动会直接把这个字段的值丢成Null或截断而且ACE驱动在服务器上有32位和64位之分发布时打包错一个就全盘崩。处理简单的小文件还行做正式方案不够稳。NPOI是完全托管代码不需要服务器装Office支持xls和xlsx两种格式底层直接操作文件字节流速度中等偏上部署时只需要丢一个DLL进程序目录。对我这种长期维护多个项目的场景来说这是最省心的选择。2.2 NPOI基础读取封装与单元格类型陷阱NPOI读取xlsx的核心对象是XSSFWorkbook它会把整个工作簿加载到内存。基础流程很简单打开文件流 → 创建Workbook → 取Sheet → 遍历Row和Cell。真正容易踩坑的是单元格类型判断。using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; public ListListstring ReadExcel(string filePath, string sheetName) { var result new ListListstring(); using (var fs File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { var workbook new XSSFWorkbook(fs); var sheet workbook.GetSheet(sheetName); if (sheet null) throw new Exception($Sheet [{sheetName}] 不存在); for (int rowIndex sheet.FirstRowNum; rowIndex sheet.LastRowNum; rowIndex) { var row sheet.GetRow(rowIndex); if (row null) continue; // 空行直接跳过 var rowData new Liststring(); foreach (var cell in row.Cells) { rowData.Add(ConvertCellToString(cell)); } result.Add(rowData); } } return result; } private string ConvertCellToString(ICell cell) { switch (cell.CellType) { case CellType.String: return cell.StringCellValue; case CellType.Numeric: // Numeric可能是数字也可能是日期要单独判断 if (DateUtil.IsCellDateFormatted(cell)) { return cell.DateCellValue.ToString(yyyy-MM-dd HH:mm:ss); } else { return cell.NumericCellValue.ToString(); } case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Formula: // 公式单元格要小心直接取结果需要用到FormulaEvaluator return new XSSFFormulaEvaluator(workbook).Evaluate(cell).FormatAsString(); default: return string.Empty; } }这里面有个非常经典的坑Excel里的订单号、编号、身份证号这类列在Excel里经常是“数值格式”但业务上完全是字符串语义。用NPOI读出来NumericCellValue返回的是double比如订单号123456被读成123456.0你要是不处理就写库数据库里存进去一个怪异的小数点字符串下游联调直接疯掉。我实际处理时会把读取结果统一转成字符串再结合目标字段类型去重新解析。比如映射到数据库的nvarchar列就直接把double转成long再转字符串去掉小数点映射到decimal列再用decimal.TryParse做一次严格转换。总之读Excel时永远不要信任原始的CellType一定要知道这一列在数据库里是什么类型以此为准去做二次转换。2.3 超大Excel的内存分水岭XSSFWorkbook会把整个xlsx解析到内存一个20万行、十几列的文件差不多要占500MB以上的托管堆。如果你的业务偶尔有超大文件要么靠记忆配置扛要么换流式读取。NPOI的XSSFReader可以按Sheet流式解析代码写起来会比XSSFWorkbook繁琐不少但内存占用能降一个量级。我的经验是10万行以内直接用XSSFWorkbook简单可靠超过30万行建议考虑XSSFReader方案或者直接在业务层限制单文件行数超出就拆分。另外要注意File.Open时务必用FileShare.ReadWrite否则文件正在被Excel或另一个进程写入时你这里一开就会抛“文件被占用”。允许共享读写只表示“你能打开这个文件流”读到不完整的数据是后面重试逻辑要解决的这里先保证不抛异常。3. 数据库写入优化从逐行Insert到SqlBulkCopyExcel解析完下一关就是写入SQL Server。大多数新手会写个循环一行一行ExecuteNonQuery本地测试几千行没感觉生产环境丢过来一个几万行的文件可能跑几分钟还超时。这一章我用实际测试数据说明三种写法的差距以及SqlBulkCopy接入时要注意的细节。3.1 三种写入办法的真实耗时对比我拿一份模拟数据做过测试10万行、12列、单表导入目标表无索引干扰普通机械硬盘。三种写法的实测结果如下写入方式耗时复杂度问题逐行Insert每行单独事务约40秒低太慢连接往返次数太多拼接VALUES批量Insert每2000行一条SQL约7秒中要对参数拼接做处理SQL文本很大SqlBulkCopy约2秒中需要先把数据放进DataTable内存占一些不同机器、不同网络环境下绝对值会有波动但量级差距是稳定的。SqlBulkCopy走的是SQL Server的批量加载协议相当于直接把一大块数据灌进表不走普通T-SQL的逐行解析路径所以性能碾压前两种。我这边日常业务量还没大到需要流式分片DataTable方案完全够用。核心代码长这样using System.Data; using Microsoft.Data.SqlClient; public void BulkInsert(DataTable dt, string connectionString, string targetTable) { using (var bulk new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock)) { bulk.DestinationTableName targetTable; bulk.BatchSize 5000; bulk.BulkCopyTimeout 120; // 列映射DataTable的列 - 目标表的列 foreach (DataColumn col in dt.Columns) { bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulk.WriteToServer(dt); } }3.2 SqlBulkCopy接入时的三个细节第一自增主键不要映射进去。目标表如果带identity主键你在DataTable里加一列主键值传给SqlBulkCopy它会尝试按这个值插入极易导致主键冲突。正确做法是DataTable里干脆不建这一列让数据库自动生成。第二列映射的关系按列名走。Excel源列的顺序和数据库表列顺序通常不一致SqlBulkCopy的ColumnMappings恰恰是按列名映射所以只要你把Excel表头正确转换成DataTable的列名字段顺序就不是问题。第三数据必须提前校验不能指望数据库报错。SqlBulkCopy一旦批量里有一行数据有问题它会抛异常并终止整批导入错误信息只会告诉你“从bcp客户端收到一个无效的列长度”具体哪行哪个字段有问题完全看不出来。所以校验一定要在进DataTable之前完成。3.3 字段映射配置化让业务人员自己改表头真实项目里业务部门经常改Excel模板的列名你不可能每次都改代码重新发布。我一般会把映射关系抽到一个JSON配置里{ sheetName: Sheet1, mappings: [ { excelColumn: 订单号, dbColumn: OrderNo, type: string }, { excelColumn: 客户名称, dbColumn: CustomerName, type: string }, { excelColumn: 金额, dbColumn: Amount, type: decimal }, { excelColumn: 下单日期, dbColumn: OrderDate, type: datetime, format: yyyy-MM-dd } ] }读取配置后先把Excel第一行表头读出来建立“表头文字 → 列索引”的映射字典再根据字典把每行的单元格值取出来放到对应数据库列名的DataTable列里。这样业务方改列名只需要同步改一下配置文件程序重启加载一次即可。var headerMap new Dictionarystring, int(); for (int i 0; i headerRow.Cells.Count; i) { var cellText ConvertCellToString(headerRow.Cells[i]); if (!string.IsNullOrWhiteSpace(cellText)) { headerMap[cellText.Trim()] i; } } foreach (var mapping in config.Mappings) { if (!headerMap.ContainsKey(mapping.ExcelColumn)) { throw new Exception($Excel中缺少必须的表头列: {mapping.ExcelColumn}); } int colIndex headerMap[mapping.ExcelColumn]; var rawValue ConvertCellToString(dataRow.GetCell(colIndex)); var convertedValue ConvertByType(rawValue, mapping.Type, mapping.Format); dt.Rows[dt.Rows.Count - 1][mapping.DbColumn] convertedValue; }配置化之后新增一种Excel模板甚至不用改代码加一份配置文件就行。这个收益在项目后续维护期非常明显。4. 让“实时”可靠起来文件监听与并发处理如果说读取和写入是硬功夫文件监听这一段就是软功夫坑最多也最容易阴沟翻船。我最初天真地以为挂个FileSystemWatcher、订阅Created事件就大功告成结果测试时同一个文件被导了三遍还有一次文件传了一半就开读直接异常。这些坑每个都在生产环境等着你。4.1 文件监听不是挂了FileSystemWatcher就能完事FileSystemWatcher的典型问题是写入一个大文件时Created事件在文件刚创建瞬间就触发了后续写入过程还会触发多个Changed事件而且某些网络环境下同一个文件可能触发两次Created。更麻烦的是事件触发时文件不一定写完你这时候去读要么读到一半数据要么因为文件被占用直接打不开。我总结的应对策略是三层防护事件触发后不立即处理先进入待处理队列延迟几秒再开始。真正处理前反复尝试以FileShare.ReadWrite方式打开文件打不开就等1秒重试直到超时。用文件指纹机制保证同一个文件被多次触发也不会重复导入下一章细讲。private bool WaitForFileReady(string filePath, int timeoutSeconds) { var deadline DateTime.Now.AddSeconds(timeoutSeconds); while (DateTime.Now deadline) { try { using (var fs File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { return fs.Length 0; } } catch (Exception) { Thread.Sleep(1000); } } return false; }这段代码看起来简单但它是整个实时导入链路里最重要的函数之一。文件没就绪之前后面的解析、校验、写入全是添乱。4.2 一个不会重复导入的监听处理骨架监听器主类我通常写成队列加单线程Worker模式核心逻辑是事件回调只把文件路径加入队列后台一个循环线程从队列取文件处理。这样天然防并发避免同一个文件被两个线程同时处理。public class FileWatcherService : IDisposable { private readonly FileSystemWatcher _watcher; private readonly ConcurrentQueuestring _pendingFiles new ConcurrentQueuestring(); private readonly SemaphoreSlim _signal new SemaphoreSlim(0); private CancellationTokenSource _cts new CancellationTokenSource(); public void Start(string watchFolder) { _watcher new FileSystemWatcher(watchFolder) { IncludeSubdirectories false, EnableRaisingEvents true }; _watcher.Created OnFileCreated; _watcher.Renamed OnFileRenamed; Task.Run(() ProcessLoop(_cts.Token)); } private void OnFileCreated(object sender, FileSystemEventArgs e) { if (e.Name.StartsWith(~$)) return; // Office临时文件不处理 if (!Path.GetExtension(e.Name).Equals(.xlsx, StringComparison.OrdinalIgnoreCase)) return; _pendingFiles.Enqueue(e.FullPath); _signal.Release(); } private void ProcessLoop(CancellationToken token) { while (!token.IsCancellationRequested) { _signal.Wait(token); if (_pendingFiles.TryDequeue(out string filePath)) { try { ProcessFile(filePath); ArchiveFile(filePath); } catch (Exception ex) { // 记录日志后移到异常目录避免影响后续文件 MoveToErrorFolder(filePath, ex); } } } } }这个骨架有几个细节值得说明过滤~$开头的文件是因为Excel打开文件编辑时会先创建一个临时锁文件不过滤的话监听器会被它打扰处理完的文件必须移走或归档否则目录里的文件会越积越多下次扫描可能还会重复触发。4.3 网络共享目录的保险方案轻量轮询FileSystemWatcher挂在本地目录表现稳定但挂在NAS或者Windows共享目录上时在某些系统版本下会静默失效——事件不触发你完全察觉不到。我踩过一次后对网络共享目录就不太信任事件监听了。替代方案是降级成轻量轮询每5秒扫一次目录拿文件名加文件指纹判断是否处理过。5秒的延迟对绝大多数业务完全够用换来的是稳定可靠。轮询逻辑很简单就是一个循环加目录扫描while (!_cancelled) { foreach (var filePath in Directory.GetFiles(watchFolder, *.xlsx)) { if (IsAlreadyHandled(filePath)) continue; ProcessFile(filePath); MarkAsHandled(filePath); } Thread.Sleep(5000); }你甚至可以把事件监听和轮询做成双保险事件触发时立即处理轮询作为兜底扫描相当于给系统上了双保险。我目前的项目里就是这种组合运行一年多没有再出过“文件没导入”的投诉。5. 防止重复导入与数据质量校验实时导入最怕的是脏数据写进正式表。要么同一个文件被导了两次出现重复记录要么Excel里某行格式不对导到一半报错卡死。这章讲我踩过之后沉淀下来的两个关键机制文件指纹去重和前置校验。5.1 文件指纹去重不能只靠文件名第一次做这个需求时我用“文件名修改时间”判断是否处理过结果业务人员把同一个文件复制一份改名或者修改了内容但文件名不变系统就抓瞎了。后来我在数据库里加了一张导入记录表核心字段如下字段类型说明Idbigint identity主键FileNamenvarchar(255)原始文件名FileHashvarchar(64)文件内容SHA256FileSizebigint文件大小ImportTimedatetime导入时间RowCountint导入行数Statusvarchar(20)Success/Failed每次处理文件前先计算整个文件的SHA256哈希查一下表里是否已有相同哈希且状态为Success记录。有就直接跳过没有才继续导入。这样文件名改不改、修改时间变不变都不影响判断只要内容一样就默认是同一个文件。public string ComputeFileHash(string filePath) { using (var sha256 System.Security.Cryptography.SHA256.Create()) using (var fs File.OpenRead(filePath)) { var hashBytes sha256.ComputeHash(fs); return Convert.ToHexString(hashBytes).ToLowerInvariant(); } }文件处理完再移动到归档目录移动时如果目标目录已有同名文件就加时间戳重命名。这样源目录始终保持清爽业务方也知道文件已经被系统吸走了。5.2 校验顺序与错误报告设计数据校验必须在进DataTable之前完成而且要做分层校验不能想到哪校验到哪。我一般情况下按这个顺序先验证表头是否存在且字段齐全如果表头不对文件整体拒收比什么都快再逐行过滤空行然后按映射类型做转换比如金额列不是数字就记录错误最后是自定义业务规则比如订单号必须以某个前缀开头、日期不能晚于今天等。校验时不能遇到错误就中断。一个几百行的文件可能有一半行有格式问题你应该把每一条错误都记录下来最后汇总而不是只报第一行错误让用户反复改上传。错误信息的收集格式是这样的var errors new Liststring(); errors.Add($Sheet: {sheetName}, 行号: {rowIndex}, 列: {columnName}, 原始值: {rawValue}, 错误原因: {reason});收集完成后把错误列表生成一个CSV文件放到一个专门的“错误反馈”目录同时在日志里输出汇总信息。业务人员拿到CSV可以快速定位是哪几行出了问题改完后重新放文件即可。这个体验和“导入失败错误未知”完全是两个级别。我还会在导入异常时把原始Excel文件移进异常目录并附一个同名的错误说明文本。这样既不影响正常文件流转又能保留现场方便排查问题。6. 完整主流程编排与生产部署建议前面的内容把各个模块单独讲了一遍最后把它们串起来看完整主流程以及生产环境中容易被忽略的细节。这部分虽然放在最后但往往决定方案能不能真正稳定运行。6.1 模块装配与主流程骨架ProcessFile是整条链路的入口它的职责是严格按顺序完成文件就绪检查、指纹去重、解析、校验、写入、记录日志、移动文件。下面的C#代码展示了核心骨架private void ProcessFile(string filePath) { // 1. 等待文件写入完成 if (!WaitForFileReady(filePath, timeoutSeconds: 30)) { throw new Exception(文件在30秒内未就绪可能一直处于写入状态); } // 2. 计算文件指纹查重 var hash ComputeFileHash(filePath); if (IsFileImported(hash)) { return; // 已导入过直接跳过 } try { // 3. 解析Excel并验证表头 var excelData ReadExcelWithHeader(filePath, config.SheetName); ValidateHeader(excelData.Header, config.Mappings); // 4. 逐行校验并构建DataTable var dt BuildDataTable(excelData.Header, excelData.Rows, config.Mappings); // 5. 批量写入数据库 BulkInsert(dt, connectionString, config.TargetTable); // 6. 记录导入日志 LogImport(hash, filePath, dt.Rows.Count, Success); } catch (Exception ex) { LogImport(hash, filePath, 0, Failed); throw; // 由外层统一处理异常并归档 } }这个流程看着很普通但每一步都是踩过坑后沉淀下来的。比如查重必须在解析Excel之前做否则超大文件每次触发都要白解析一遍浪费大量CPU和内存又比如文件就绪检查必须放在查重前面否则文件还在写入时计算哈希哈希值就不完整后面的查重和导入都会错乱。6.2 实测数据与资源表现我用一份25MB左右的xlsx做了一次完整链路测试文件包含10万行、12列数据目标表为SQL Server 2019本地千兆网络。各阶段耗时如下阶段耗时等待文件就绪约0秒文件已写完SHA256哈希计算约0.3秒NPOI解析Excel约1.8秒校验与DataTable构建约2.2秒SqlBulkCopy写入约2.1秒归档与日志记录约0.2秒整体从发现文件到数据库可查询大约6-7秒。进程内存峰值约350MB主要是NPOI的XSSFWorkbook对象占的。这个表现对于“文件进目录→数据库可见”的需求来说完全够用。如果你要处理百万行级别的文件我建议把NPOI换成流式解析组件并且把SqlBulkCopy的BatchSize调大配合分区表和索引策略。但如果只是常规业务Excel导入上面这套组合已经非常稳了。6.3 部署时容易被忽略的几个点第一点是宿主环境。这种实时导入服务最好不要塞在IIS里跑IIS应用池回收会导致后台任务中断。我一般做成独立的Windows服务或者用Topshelf把控制台程序包装成服务失败自动重启。第二点是连接串和文件路径的配置。开发环境和生产环境的数据库地址、归档路径可能完全不同建议都放到AppSettings或独立配置文件中不要硬编码。文件路径配成UNC网络路径时要确保运行服务账号有对应目录的读写权限这个权限问题和代码无关但排查起来最容易让人犯迷糊。第三点是临时目录和归档目录要分开。处理前先把文件从源目录拷贝到本地临时目录再解析解析成功再归档。这样做的好处是即使处理过程中文件被外部程序删掉或者网络断连源文件已经有一个副本了不会影响到业务流程。还有一个小细节如果业务方有“同一个Excel文件更新后再次导入”的需求文件名不变但内容变化哈希就会变因此会被当作新文件再次导入。这既保证了灵活性又会带来潜在的重复数据问题。我在实际项目里会跟业务方约定源文件一旦入库不允许修改后重放需要修正就直接在数据库里改从流程上消灭隐患。最后分享一个小技巧。接到外部文件时先做一次“预检”——用NPOI只读Excel的表头和前两行数据3秒内就能判断文件模板是否合法、关键列是否存在。如果表头不对直接在预检阶段就拒收根本不用把整个大文件解析完再报错。这样既节省资源也大幅提升业务方改文件的沟通效率。这套实时导入链路后来我在几个项目里反复复用核心体会就是一句话复杂度从来不在SQL语句而在文件从出现到稳定的那几秒以及系统对意外情况的容忍度。把这层想透了方案自然就稳了。
