PowerShell Ping脚本条件格式化报错求助：实现主机状态Excel着色

一、脚本报错排查与修复 #

你的脚本在Excel条件格式处理、COM对象释放等环节存在多处问题，以下是具体错误点和修复方案：

核心错误点 #

1. 条件格式参数与变量作用域错误：循环结束后$status仅保留最后一个主机的状态，无法匹配所有行；且FormatConditions.Add的参数不符合Excel对象模型要求，无法正确创建规则。
2. 颜色值计算错误：Excel颜色采用BGR格式，你当前的计算逻辑无法生成正确的红/绿色背景。
3. COM对象未释放：脚本末尾的[System.Runtime.Interopservices]是不完整代码，会导致Excel进程在后台残留。
4. 自动变量冲突：foreach ($host in $hosts)中的$host是PowerShell自动变量，建议改用$hostname避免潜在冲突。
5. 数组拼接效率低下：$output += [PSCustomObject]会频繁重建数组，直接将循环结果赋值给$output更高效。

修复后的完整脚本 #

# Set up file paths
$hostList = "C:\Users\Username\Desktop\New folder\hnames.txt"
$outputFile = "C:\Users\Username\Desktop\New folder\output.csv"

# Read list of host names from text file
$hosts = Get-Content $hostList

# Loop through host names and ping each one, build output directly
$output = foreach ($hostname in $hosts) {
    $pingResult = Test-Connection -ComputerName $hostname -Count 1 -Quiet -ErrorAction SilentlyContinue
    
    # Add host and status to output
    [PSCustomObject]@{
        Hostname = $hostname
        Status = if ($pingResult) {"Alive"} else {"Dead"}
    }
}

# Export output array to CSV file
$output | Export-Csv $outputFile -NoTypeInformation

# Set up Excel application
$excel = New-Object -ComObject Excel.Application
# 调试时可设置为$true查看Excel窗口
# $excel.Visible = $true
$workbook = $excel.Workbooks.Open($outputFile)
$worksheet = $workbook.Worksheets.Item(1)

# Set up range of cells to highlight (自动定位到最后一行数据)
$lastRow = $worksheet.Cells($worksheet.Rows.Count, "B").End(-4162).Row # -4162对应xlUp常量
$range = $worksheet.Range("B2:B$lastRow")

# Set up conditional formatting for Dead status (红色背景)
$cfDead = $range.FormatConditions.Add(2, 1, "=Dead") # 2=xlCellValue, 1=xlEqual
$cfDead.Interior.Color = 255 # 红色（BGR格式：0,0,255）
$cfDead.Font.Color = -4142 # 黑色字体

# Set up conditional formatting for Alive status (绿色背景)
$cfAlive = $range.FormatConditions.Add(2, 1, "=Alive")
$cfAlive.Interior.Color = 65280 # 绿色（BGR格式：0,255,0）
$cfAlive.Font.Color = -4142

# Save and close Excel file
$workbook.Save()
$workbook.Close()
# 释放COM对象，避免Excel进程残留
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

二、能否直接在CSV中实现条件格式化？ #

不行。CSV是纯文本格式，仅存储结构化数据，不支持任何样式（颜色、字体格式等）信息。如果想要带条件格式的可视化效果，可选择两种替代方案：

• 继续使用Excel/Google Sheets等表格工具，通过脚本或手动添加条件格式；
• 生成HTML表格，通过CSS实现条件格式，用浏览器打开查看。

生成带样式的HTML示例 #

$htmlFragment = $output | ConvertTo-Html -Property Hostname, Status -Fragment | ForEach-Object {
    $_ -replace '<td>Alive</td>', '<td style="background-color: #00ff00; color: white; padding: 6px;">Alive</td>' `
       -replace '<td>Dead</td>', '<td style="background-color: #ff0000; color: white; padding: 6px;">Dead</td>'
}

$fullHtml = @"
<!DOCTYPE html>
<html>
<head>
    <title>Host Ping Status</title>
    <style>
        table {border-collapse: collapse; margin: 16px;}
        th {background-color: #f0f0f0; padding: 8px; border: 1px solid #ccc;}
        td {border: 1px solid #ccc;}
    </style>
</head>
<body>
    <h2 style="margin-left: 16px;">Host Ping Detection Result</h2>
    $htmlFragment
</body>
</html>
"@

$fullHtml | Out-File "C:\Users\Username\Desktop\New folder\output.html"

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