PowerShell处理Excel时重复记录修改文件路径问题求助

问题背景 #

编写的Get-ExcelFunction函数可递归遍历目录及子目录中的.xlsx文件，查找并替换指定字符串。处理.config文件时逻辑正常，但处理Excel文件时，每个被修改的文件路径会被重复写入日志（日志示例如下）：

C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder\20230501_HIST_BGE_ELE_GAS - Copy.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder\20230501_HIST_BGE_ELE_GAS - Copy.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder\20230501_HIST_BGE_ELE_GAS.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder\20230501_HIST_BGE_ELE_GAS.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder2\TestingFile.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder2\TestingFile.xlsx

问题原因 #

原代码中，日志写入语句$dir.FullName >> $filePathName放在工作表循环的if判断块内。也就是说，只要Excel文件中有一个工作表包含目标字符串并完成替换，就会写入一次日志。如果一个文件有多个工作表都匹配到替换内容，该文件路径就会被多次写入日志，导致重复。

解决思路及修改代码 #

核心思路是：为每个Excel文件设置一个“是否被修改”的标记，只有当文件至少有一个工作表完成替换后，在所有工作表处理完毕时，统一写入一次日志。

修改后的代码如下：

Function Get-ExcelFunction () {
   
    $rootDir = Get-ChildItem $Path -Filter *.xlsx -Recurse

    ForEach ($dir in $rootDir) { 
        $Excel = New-Object -ComObject Excel.Application
        $Excel.visible = $false
        $Workbook = $Excel.workbooks.open($dir.FullName)
        # 初始化标记：当前文件未被修改
        $isFileModified = $false

        ForEach ($sheet in $Workbook.Worksheets) {
            $Range = $sheet.Range("A1:EZ800").EntireColumn #Range of Cells to look at
            $Search = $Range.find($oldString)
            
            if ($null -ne $Search) {
                $FirstAddress = $search.Address
                do {
                    $Search.value() = $Search.value().Replace($oldString, $newString) #String to Update
                    $Search = $Range.FindNext($Search)
                } while ( $null -ne $Search -and $Search.Address -ne $FirstAddress)
                # 只要有一个工作表完成替换，标记文件已修改
                $isFileModified = $true
            }
        }

        # 所有工作表处理完成后，判断是否修改过，再写入日志
        if ($isFileModified) {
            $dir.FullName >> $filePathName #Write the path to where the file was changed to a change log file
        }

        $WorkBook.Save()
        $WorkBook.Close()
        [void]$Excel.quit()
        # 释放COM对象，避免内存泄漏（可选但推荐）
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
    }
} 

修改后效果 #

修改后的日志将每个被修改的文件路径只写入一次，示例如下：

C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder\20230501_HIST_BGE_ELE_GAS - Copy.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder\20230501_HIST_BGE_ELE_GAS.xlsx
C:\Users\Tara\Documents_Local\ReplaceEmailPowershell\TestFolder2\TestingFile.xlsx

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