如何使用PowerShell获取Excel各工作表最值、平均值并生成summary汇总表

方案一：使用ImportExcel模块（推荐） #

这是PowerShell生态中处理Excel最便捷的第三方模块，无需依赖本地Excel安装，稳定性更高。

1. 安装模块 #

Install-Module -Name ImportExcel -Scope CurrentUser -Force

2. 完整实现脚本 #

# 替换为你的Excel文件路径
$excelPath = "C:\Your\File\Path\target.xlsx"

# 获取所有工作表名称
$sheetNames = (Get-ExcelSheetInfo -Path $excelPath).Name

# 初始化汇总数据容器
$summaryData = @()

foreach ($sheetName in $sheetNames) {
    # 跳过汇总表（避免重复统计）
    if ($sheetName -eq "summary") { continue }

    # 读取当前工作表数据
    $sheetData = Import-Excel -Path $excelPath -WorksheetName $sheetName

    # 提取所有数值型数据（过滤非数值单元格）
    $numericValues = $sheetData.PSObject.Properties.Value | ForEach-Object {
        if ($_ -is [int] -or $_ -is [double] -or $_ -is [decimal]) { $_ }
    }

    # 计算统计值
    $stats = $numericValues | Measure-Object -Maximum -Minimum -Average

    # 整理汇总记录
    $summaryData += [PSCustomObject]@{
        工作表名称 = $sheetName
        最大值     = $stats.Maximum ?? $null
        最小值     = $stats.Minimum ?? $null
        平均值     = if ($stats.Average) { [math]::Round($stats.Average, 2) } else { $null }
    }
}

# 将汇总数据写入工作表（存在则清空原有内容）
$summaryData | Export-Excel -Path $excelPath -WorksheetName "summary" -ClearSheet

关键说明 #

• 自动过滤非数值单元格，避免统计错误
• 若summary工作表已存在，-ClearSheet参数会覆盖原有内容
• 支持带表头的工作表，模块会自动识别表头不影响数值提取

方案二：使用原生COM对象（依赖本地Excel） #

如果无法安装第三方模块，可通过Excel COM对象实现，需确保本地已安装Excel。

完整实现脚本 #

$excelPath = "C:\Your\File\Path\target.xlsx"

# 创建Excel实例
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open($excelPath)

# 创建或获取汇总工作表
$summarySheet = $workbook.Sheets.Item("summary")
if (-not $summarySheet) {
    $summarySheet = $workbook.Sheets.Add()
    $summarySheet.Name = "summary"
}

# 写入汇总表头
$summarySheet.Cells(1, 1) = "工作表名称"
$summarySheet.Cells(1, 2) = "最大值"
$summarySheet.Cells(1, 3) = "最小值"
$summarySheet.Cells(1, 4) = "平均值"
$currentRow = 2

# 遍历所有工作表
foreach ($sheet in $workbook.Sheets) {
    if ($sheet.Name -eq "summary") { continue }

    # 获取工作表已使用区域数据
    $usedRange = $sheet.UsedRange
    $cellValues = $usedRange.Value2

    # 提取数值型数据
    $numericValues = @()
    foreach ($row in $cellValues) {
        foreach ($cell in $row) {
            if ($cell -is [int] -or $cell -is [double]) {
                $numericValues += $cell
            }
        }
    }

    # 计算统计值
    if ($numericValues.Count -gt 0) {
        $max = ($numericValues | Measure-Object -Maximum).Maximum
        $min = ($numericValues | Measure-Object -Minimum).Minimum
        $avg = [math]::Round(($numericValues | Measure-Object -Average).Average, 2)
    } else {
        $max = $min = $avg = $null
    }

    # 写入汇总数据
    $summarySheet.Cells($currentRow, 1) = $sheet.Name
    $summarySheet.Cells($currentRow, 2) = $max
    $summarySheet.Cells($currentRow, 3) = $min
    $summarySheet.Cells($currentRow, 4) = $avg
    $currentRow++
}

# 保存并清理资源
$workbook.Save()
$workbook.Close()
$excel.Quit()

# 强制释放COM对象，避免残留Excel进程
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($usedRange) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($summarySheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

关键说明 #

• 必须手动清理COM对象，否则Excel进程会留在后台
• 处理大文件时效率略低于ImportExcel模块
• 需确保本地Excel版本与脚本兼容

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