ASP.NET实现Excel与SQL表数据比对、匹配标记及未匹配项导出

• DeviceName列不匹配异常本质是两个环节的隐式匹配失效：一是OleDb读取Excel默认仅扫描前8行推断列结构，如果表头带空格、隐藏字符、存在合并单元格，读出来的实际列名和预期的DeviceName不一致；二是SqlBulkCopy默认按列位置顺序做映射，只要源DataTable和目标表的列顺序、列名存在偏差，就会抛出System.InvalidOperationException。
• 数据追加不匹配是逻辑缺失：SqlBulkCopy只做批量插入，本身没有任何关联比对、行对齐的能力，直接调用当然只会把Excel数据全量追加成新行，不可能自动和存量表做匹配，更不会自动筛选差集。

第一步：解决列映射异常 #

• 调整OleDb连接字符串，追加HDR=Yes;IMEX=1配置，强制将首行识别为表头、以混合文本模式读取列，避免列类型/列名识别错误。读取完Excel的DataTable后，第一时间遍历Columns集合，把所有列名做Trim处理，同时校验是否存在DeviceName列，提前拦截格式错误的上传文件。
• 调用SqlBulkCopy时不要用默认映射，显式指定列对应关系，代码示例：

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConn))
{
    bulkCopy.DestinationTableName = "dbo.tblReport";
    // 显式绑定列，彻底规避隐式匹配问题
    bulkCopy.ColumnMappings.Add("DeviceName", "DeviceName");
    bulkCopy.WriteToServer(excelDt);
}

注意：永远不要把用户上传的Excel数据直接写入业务表tbl.Devices，固定用独立中转表tblReport存临时上传数据，避免污染存量业务数据。

第二步：完成数据比对与差集导出 #

Excel数据写入中转表后，直接用SQL做关联查询，比在内存里遍历DataTable性能高很多：

• 匹配成功的结果集：通过内连接关联两张表的DeviceName字段，直接生成带ComparedData列的匹配结果

SELECT
    r.DeviceName,
    d.Id,
    d.Name,
    d.Status,
    r.DeviceName AS ComparedData
FROM dbo.tblReport r
INNER JOIN tbl.Devices d ON r.DeviceName = d.DeviceName

• 待清理的未匹配设备：通过左连接找Excel里存在、SQL业务表不存在的记录，就是需要导出的Desktop 5/6/7这类无效数据

SELECT r.DeviceName
FROM dbo.tblReport r
LEFT JOIN tbl.Devices d ON r.DeviceName = d.DeviceName
WHERE d.Id IS NULL

• 导出实现：把上面未匹配的查询结果填充到新的DataTable，用NPOI或EPPlus生成xlsx文件流返回前端下载即可，不需要安装Office组件。

第三步：临时数据清理 #

单次比对流程结束后，及时删除tblReport中本次上传的临时数据，避免和后续上传的文件数据混淆。

内容的提问来源于stack exchange，提问作者user14568805