使用foreach循环对比两个Excel文件做技能匹配时运行过慢问题咨询

核心性能瓶颈 #

• 循环内频繁调用Excel COM对象的Cells.Item()读取数据：COM交互的开销是纯内存操作的上千倍，是运行缓慢的最主要原因
• 资源数据重复解析：7000行资源的技能字段，在内层循环里被重复解析了350次，总计产生245万次无效的字符串切割、匹配操作
• 冗余逻辑：同一份技能匹配逻辑重复写了5次，还有多次重复执行-match判断，额外增加了大量无效开销
• 另外代码存在隐性bug：匹配s4、s5技能时，错误使用了s3的长度计算子串，会导致熟练度提取错误

优化步骤 #

1. 一次性读取所有Excel数据到内存 #

提前把岗位表、资源表的所有需要的字段全部读入PowerShell数组，完全避免循环内操作COM对象：

# 提前读取所有岗位数据并预处理
$roles = @()
for ($i = 2; $i -le $totalNoOfRecords; $i++){
    $role = [PSCustomObject]@{
        Id = $MODWorkSheet.Cells.Item($i,$RoleIDColNo).text
        SkillsRaw = $MODWorkSheet.Cells.Item($i,$RoleSkillcolNo).text
        LvlFrom = $MODWorkSheet.Cells.Item($i,$RoleLvlFromColNo).text
        LvlTo = $MODWorkSheet.Cells.Item($i,$RoleLvlToColNo).text
        SearchSkills = @()
    }
    # 提前清洗岗位需要匹配的技能列表
    $cleanSkill = $role.SkillsRaw -replace 'p.? - ' -replace '[0-9] - ',''
    $skills = $cleanSkill.Split('|') | Where-Object { $_ } | ForEach-Object { $_.Split("(")[0].Trim() }
    $role.SearchSkills = $skills
    $roles += $role
}

# 提前读取所有资源数据，预解析为技能-熟练字典
$resources = @()
$resSkillMaps = @()
for ($j = 2; $j -le $totalNoOfResRecords; $j++){
    $res = [PSCustomObject]@{
        EID = $ResWorkSheet.Cells.Item($j,$ResEIDColNo).text
        AvlDate = $ResWorkSheet.Cells.Item($j,$ResAvlDatecolumnNo).text
        Clearance = $ResWorkSheet.Cells.Item($j,$ResClearancecolumnNo).text
        CurrClient = $ResWorkSheet.Cells.Item($j,$ResCurClientcolumnNo).text
        Level = $ResWorkSheet.Cells.Item($j,$ResLevelcolumnNo).text
    }
    # 用正则一次性匹配所有技能和熟练度，仅执行7000次
    $resSkillStr = $ResWorkSheet.Cells.Item($j,$ResSkillcolumnNo).text
    $skillMap = @{}
    $matches = [regex]::Matches($resSkillStr, '([^\(|]+)\((\d+)\)')
    foreach ($m in $matches) {
        $skillName = $m.Groups[1].Value.Trim()
        $proficiency = $m.Groups[2].Value
        $skillMap[$skillName] = $proficiency
    }
    $resources += $res
    $resSkillMaps += $skillMap
}

2. 纯内存完成匹配逻辑 #

预处理完所有数据后，匹配逻辑完全在内存中执行，350*7000的匹配量仅需几秒即可完成：

$results = @()
foreach ($role in $roles) {
    $roleSkills = $role.SearchSkills
    for ($j=0; $j -lt $resources.Count; $j++) {
        $res = $resources[$j]
        $skillMap = $resSkillMaps[$j]
        $matchedProf = @()
        $hasMatch = $false
        foreach ($s in $roleSkills) {
            if ($skillMap.ContainsKey($s)) {
                $matchedProf += $skillMap[$s]
                $hasMatch = $true
            } else {
                $matchedProf += ""
            }
        }
        # 补全最多5个技能的空位对齐输出格式
        while ($matchedProf.Count -lt 5) { $matchedProf += "" }
        if ($hasMatch) {
            $resultLine = [PSCustomObject]@{
                RoleId = $role.Id
                ResEID = $res.EID
                S1 = $roleSkills[0]
                S1Pro = $matchedProf[0]
                S2 = $roleSkills[1]
                S2Pro = $matchedProf[1]
                S3 = $roleSkills[2]
                S3Pro = $matchedProf[2]
                S4 = $roleSkills[3]
                S4Pro = $matchedProf[3]
                S5 = $roleSkills[4]
                S5Pro = $matchedProf[4]
            }
            $results += $resultLine
            # 正式运行建议注释掉Write-Host，控制台输出也会大幅拖慢速度
            # Write-Host $resultLine.RoleId, $resultLine.ResEID, $resultLine.S1, $resultLine.S1Pro, $resultLine.S2, $resultLine.S2Pro, $resultLine.S3, $resultLine.S3Pro, $resultLine.S4, $resultLine.S4Pro, $resultLine.S5, $resultLine.S5Pro
        }
    }
}

3. 一次性写入结果 #

匹配完成后把结果数组一次性导出，比逐行写入Excel快上百倍：

# 直接导出CSV，Excel可直接打开
$results | Export-Csv -Path "技能匹配结果.csv" -Encoding UTF8 -NoTypeInformation

优化完成后整体运行时间可从数小时压缩到1分钟以内。

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