PowerShell脚本生成多列HTML报表数据全为0问题求助

问题描述 #

编写的PowerShell脚本用于从报告提取数据生成HTML报表，单列输出正常，但切换为多列（按星期几拆分Good/Bad计数）时所有列数据均显示为0。

原因分析 #

1. 正则匹配逻辑错误：原脚本采用三层循环（行→站点编码→星期），对每行逐个匹配所有站点和星期的组合，若报告中行的结构并非站点编码...星期...状态的顺序，正则$siteCode.*$day会完全匹配失败，导致计数始终为0。
2. 大小写敏感问题：正则中硬编码了Pending、Unknown等首字母大写的状态值，若报告中对应状态为小写（如pending），会导致匹配失效。
3. 冗余循环导致效率低下：三层嵌套循环会重复对同一行执行大量无效匹配，既浪费资源又容易引发逻辑错误。

修复方案 #

• 改为单行提取关键信息：对每行直接使用正则提取站点编码、星期和状态，避免嵌套循环的匹配冗余。
• 使用不区分大小写的匹配：在正则中加入(?i)标记，忽略大小写差异，确保状态值匹配准确。
• 简化计数更新逻辑：提取到有效信息后直接更新对应站点和星期的Good/Bad计数。

修复后的完整脚本 #

# Define the output file path 
$outputFilePath = ".\report.html"

# Define the path to the file containing site codes
$siteCodesFile = ".\Site.txt"

# Define the path to the text file
$filePath = ".\20240405.txt"

# Read the site codes from the file
$siteCodes = Get-Content -Path $siteCodesFile

# Define the days of the week
$daysOfWeek = "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"

# Initialize counts for each site code and each day of the week
$siteCodeDayCounts = @{}
foreach ($siteCode in $siteCodes) {
    $siteCodeDayCounts[$siteCode] = @{}
    foreach ($day in $daysOfWeek) {
        $siteCodeDayCounts[$siteCode][$day] = @{
            "Good" = 0
            "Bad" = 0
        }
    }
}

# Read the content of the text file
$content = Get-Content -Path $filePath

# Retrieve the day from the report
$reportDay = $content[1] -replace ".*Sent:\s*|\s*,.*"
Write-Host "Report Day: $reportDay"

# 正则表达式：提取站点编码、星期、状态（不区分大小写）
# 需根据实际报告结构调整捕获组顺序，示例假设行结构包含 站点编码、星期、状态
$linePattern = '(?i)({0}).*?({1}).*?(functional|pending|not functional|unknown)' -f ($siteCodes -join '|'), ($daysOfWeek -join '|')

# Loop through each line in the content
foreach ($line in $content) {
    if ($line -match $linePattern) {
        $matchedSite = $matches[1]
        $matchedDay = $matches[2]
        $status = $matches[3].Trim()

        # 仅处理已定义的站点和星期
        if ($siteCodes -contains $matchedSite -and $daysOfWeek -contains $matchedDay) {
            switch -Regex ($status) {
                '(?i)functional|pending' { $siteCodeDayCounts[$matchedSite][$matchedDay].Good++ }
                '(?i)not functional|unknown' { $siteCodeDayCounts[$matchedSite][$matchedDay].Bad++ }
            }
        }
    }
}

# Output the HTML
$htmlOutput = @"
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Site Code Report</title>
<style>
    table {
        width: 100%;
        border-collapse: collapse;
    }
    th, td {
        border: 1px solid black;
        padding: 8px;
        text-align: center;
    }
    th {
        background-color: #f2f2f2;
    }
</style>
</head>
<body>
<h2>Site Code Report for $reportDay</h2>
<table>
<tr>
<th>Site Code</th>
"@

# Add table headers for each day of the week
foreach ($day in $daysOfWeek) {
    $htmlOutput += "<th>$day Good</th><th>$day Bad</th>"
}

$htmlOutput += @"
</tr>
"@

# Populate table rows with data for each site code
foreach ($siteCode in $siteCodes) {
    $htmlOutput += "<tr><td>$siteCode</td>"
    foreach ($day in $daysOfWeek) {
        $goodCount = $siteCodeDayCounts[$siteCode][$day]["Good"]
        $badCount = $siteCodeDayCounts[$siteCode][$day]["Bad"]
        $htmlOutput += "<td>$goodCount</td><td>$badCount</td>"
    }
    $htmlOutput += "</tr>"
}

$htmlOutput += @"
</table>
</body>
</html>
"@

# Output the HTML
$htmlOutput | Out-File -FilePath $outputFilePath -Encoding UTF8

Write-Host "HTML report generated successfully."

关键修改说明 #

1. 动态生成正则表达式：将站点编码和星期列表拼接为正则选择项，一次性匹配所有可能的有效组合，避免嵌套循环。
2. 不区分大小写匹配：通过(?i)标记忽略大小写，确保pending和Pending、unknown和Unknown都能被匹配。
3. 提取后直接更新计数：匹配到有效信息后，通过switch语句判断状态，直接更新对应站点和星期的计数，逻辑更清晰高效。
4. 有效性校验：确保匹配到的站点和星期属于预定义列表，避免无效数据干扰计数。

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