PowerShell 检索 Excel 文件内容

<#
.SYNOPSIS
Excel文件内的文字检索

.DESCRIPTION
检索指定目录内的所有Excel文件

.EXAMPLE
PS > Search-Excel.ps1 -TargetDir:"检索目录" -Keayword:"检索文字列"
#>

param (
    [string]$TargetDir = ".\",                            # 默认的检索目录：当前目录
    [string]$Keyword = "检索文字列"                       # 默认的检索文字列
)

# 结果保存的Excel文件名
$dateYMD = Get-Date -Format "yyyyMMddHHmmss"
$resultExcelFile = "D:\Temp\SearchResult.$dateYMD.xlsx"

# 结果保存的CSV文件名
$resultCsvFile = "D:\Temp\SearchResult.$dateYMD.csv"

# 输出CSV文件的各项目的名称
Write-Output '"文件名", "工作表名", "单元格坐标", "单元格值"' > $resultCsvFile

function Search-Excel {
    param (
        [string]$fullPath,
        [string]$keyword,
        [string]$resultExcelFile,
        [string]$resultCsvFile
    )

    # Excel对象生成
    $excel = New-Object -ComObject Excel.Application
    #excel.Visible = $False          # 表示・非表示
    $excel.Application.DisplayAlerts = $False
    $targetWorbook = $excel.Workbooks.Add()
    $targetWorksheet = $targetWorbook.Worksheets.Item(1)

    # 输出Excel文件的各项目的名称
    $targetWorksheet.name = "检索结果"
    $targetWorksheet.Cells.Item(1, 1) = "文件名"
    $targetWorksheet.Cells.Item(1, 2) = "工作表名"
    $targetWorksheet.Cells.Item(1, 3) = "单元格坐标"
    $targetWorksheet.Cells.Item(1, 4) = "单元格值"

    try {
        # 在取得的文件一览中，循环检索指定内容
        $rowNumber = 2
        Get-ChildItem $fullPath -Include *.xls,*.xlsm,*.xlsx -Recurse | ForEach-Object {

            $filePath = $_.FullName
            Write-Output "检索文件名：　$filePath"

            $workbook = $excel.Workbooks.Open($filePath)     # 打开检索的Excel文件

            $workbook.Worksheets | % {
                $sheetName = $_.Name

                $range = $_.UsedRange               # 取得检索范围

                $r = $range.Find($keyword)  # 初回检索

                if ($r -eq $Null) {
                    # 初回检索没有找到检索内容

                } else {

                    # 保存初回检索的位置
                    $firstColumn = $r.Column
                    $firstRow = $r.Row

                    do {
                        [PSCustomObject]@{"FullPath"=$filePath;
                            "Sheet" = $sheetName;
                            "Address" = $r.Address();
                            "Value" = $r.Value()}       # 在命令行出力检索到的内容

                        Write-Output ('"' + $filePath + '","' + $sheetName + '","' +    $r.Address() + '","' + $r.Value() + '"')    >> $resultCsvFile

                        $targetWorksheet.Cells.Item($rowNumber, 1) = $filePath
                        $targetWorksheet.Cells.Item($rowNumber, 2) = $sheetName
                        $targetWorksheet.Cells.Item($rowNumber, 3) = $r.Address()
                        $targetWorksheet.Cells.Item($rowNumber, 4) = $r.Value()
                        $rowNumber++

                        $r = $range.FindNext($r)    # 第二次检索

                        if ($r -eq $Null) {
                            break # 循环终了
                        }

                    } while ($r.Column -ne $firstColumn -Or $r.Row -ne $firstRow)       # 循环条件
                }
            }

            $workbook.Close() | Out-Null # 关闭打开的Excel文件
        }

        # 保存检索到的内容到Excel文件中
        $targetWorbook.SaveAs($resultExcelFile) | Out-Null

    } finally {

        # 关闭打开的结果保存的Excel文件
        $targetWorbook.Close    | Out-Null # Excel终了
        $Excel.Quit() | Out-Null                 # Excel终了
        $Excel = $Null

        [System.GC]::Collect() | Out-Null # 内存回收
    }

    Write-Output "检索的内容已保存到文件。（$resultExcelFile）（$resultCsvFile）"
}

# 执行
Search-Excel $TargetDir $Keyword $resultExcelFile $resultCsvFile