如何用PowerShell高效读取Excel中高亮单元格的值？

我需要通过PowerShell自动识别Excel中高亮的单元格并获取其值，例如示例工作表里F5单元格的"3"。以下是我写的实现代码：

#Define the path to the Excel file 
$excelFilePath = "C:\Users\Documents\Book3.xlsx"

$objExcel=New-Object -ComObject Excel.Application 
$objExcel.Visible=$True 
$workbook=$objExcel.Workbooks.Open($excelFilePath) 
$sheet = $workbook.Worksheets.Item('Sheet1') 
$sheet.activate()

#Read data from a specific cell based on row number and column number and store it in a variable called $value:
#$value = $Worksheet.Cells.Item($rowNumber,$columnNumber).text

#Initialize an array to hold values of highlighted cells
$highlightedValues = @()

#Define the range of rows and columns to check 
$startRow = 1
$endRow = 10
$columns = 'A','B','C','D','E','F'  # Columns A to F
for ($rowNumber = $startRow; $rowNumber -le $endRow; $rowNumber++) { 
    # Get the cell using row number and column letter
    $cell = $worksheet.Cells.Item($rowNumber, $columnLetter)

    #Check if the cell's interior color is not automatic (not highlighted) 
    if ($cell.Interior.ColorIndex -ne -4142) {  # -4142 represents xlNone 
        # Add the cell value to the array
        $highlightedValues += $cell.Value()
    }
} 
# Print highlighted cell values to the PowerShell console 
if ($highlightedValues.Count -gt 0) { 
    Write-Host "Highlighted cell values:"
    $highlightedValues | ForEach-Object { Write-Host $_ } 
} else { 
    Write-Host "No highlighted cells found."
}

#Save the excel workbook: 
$Workbook.Save()

#Quit from excel application:

请问有没有更高效的实现方法？恳请各位提供建议。

1. 替换COM对象为EPPlus模块 #

用COM对象调用Excel进程不仅速度慢，还会占用额外系统资源，且容易出现进程残留问题。推荐使用EPPlus这个专门处理Excel文件的.NET模块，无需启动Excel客户端，处理速度大幅提升。

安装EPPlus #

Install-Module -Name EPPlus -Scope CurrentUser -Force

示例代码 #

$excelFilePath = "C:\Users\Documents\Book3.xlsx"
$highlightedValues = @()

# 加载EPPlus程序集
Add-Type -Path (Join-Path (Get-Module EPPlus).ModuleBase "EPPlus.dll")

# 打开Excel文件
$package = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $excelFilePath
$worksheet = $package.Workbook.Worksheets["Sheet1"]

# 获取工作表中已使用的单元格范围，避免遍历空单元格
$usedRange = $worksheet.Dimension
if ($usedRange) {
    for ($row = $usedRange.Start.Row; $row -le $usedRange.End.Row; $row++) {
        for ($col = $usedRange.Start.Column; $col -le $usedRange.End.Column; $col++) {
            $cell = $worksheet.Cells[$row, $col]
            # 检查单元格是否有填充颜色（非默认无填充）
            if ($cell.Style.Fill.PatternType -ne [OfficeOpenXml.Style.ExcelFillStyle]::None) {
                $highlightedValues += $cell.Text
            }
        }
    }
}

# 输出结果
if ($highlightedValues.Count -gt 0) {
    Write-Host "高亮单元格的值："
    $highlightedValues | ForEach-Object { Write-Host $_ }
} else {
    Write-Host "未找到高亮单元格。"
}

# 关闭并保存文件
$package.Save()
$package.Dispose()

2. 优化原COM代码的缺陷（如果坚持使用COM） #

你的原代码存在几个问题会导致效率低下或运行错误：

• 变量名不一致：定义了$sheet但用了$worksheet
• 缺少列的循环逻辑：只遍历了行，没遍历$columns数组
• 未正确关闭Excel进程，容易导致残留

优化后的COM代码：

$excelFilePath = "C:\Users\Documents\Book3.xlsx"
$highlightedValues = @()

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False # 设为False可以提升速度，无需显示Excel窗口
$workbook = $objExcel.Workbooks.Open($excelFilePath)
$sheet = $workbook.Worksheets.Item('Sheet1')

$startRow = 1
$endRow = 10
$columns = 'A','B','C','D','E','F'

# 同时遍历行和列
foreach ($columnLetter in $columns) {
    for ($rowNumber = $startRow; $rowNumber -le $endRow; $rowNumber++) {
        $cell = $sheet.Cells.Item($rowNumber, $columnLetter)
        if ($cell.Interior.ColorIndex -ne -4142) {
            $highlightedValues += $cell.Value()
        }
    }
}

# 输出结果
if ($highlightedValues.Count -gt 0) {
    Write-Host "高亮单元格的值："
    $highlightedValues | ForEach-Object { Write-Host $_ }
} else {
    Write-Host "未找到高亮单元格。"
}

# 正确关闭Excel，避免进程残留
$workbook.Save()
$workbook.Close()
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

3. 额外优化点 #

• 限制遍历范围：尽量只遍历有数据的区域，而不是固定的1-10行，比如用$sheet.UsedRange.Rows.Count和$sheet.UsedRange.Columns.Count获取实际使用的行列数
• 避免数组频繁扩容：原代码用$highlightedValues += $cell.Value()会每次创建新数组，推荐用ArrayList替代：

  $highlightedValues = New-Object System.Collections.ArrayList
  # 添加元素时用
  $highlightedValues.Add($cell.Value()) | Out-Null
  

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