如何在PowerShell中遍历Excel单元格区域并实现求和？

问题描述 #

需要在PowerShell中指定Excel单元格区域，遍历每个单元格求和，目前代码卡在列名（如"A"、"AA"）转整数的步骤，现有代码如下：

$range="A1:B2".Split(":")

$startrow= $range[0] -replace "[^0-9]" #equals 1
$startcol= $range[0] -replace "[^A-Z]" #equals "A"
$maxrow= $range[1] -replace "[^0-9]" #equals 2
$maxcol= $range[1] -replace "[^A-Z]" #equals "B"
$row = $startrow; $col = $startcol; $value = 0
while($col -le $maxcol){
$row = $startrow
    while($row -le $maxrow){
    $cellValue = [int]$ws_test.Cells.Item($row,$col).Text
    $value = $value + $cellValue
    $row++
    }
$col++
}

解决方法 #

1. 实现列名转整数的函数 #

可以通过函数将Excel列名转换为对应的整数索引，逻辑参考26进制转十进制：

function Convert-ExcelColumnNameToNumber {
    param(
        [string]$ColumnName
    )
    $columnNumber = 0
    foreach ($char in $ColumnName.ToUpper().ToCharArray()) {
        $columnNumber = $columnNumber * 26 + ([byte]$char - [byte]'A' + 1)
    }
    return $columnNumber
}

将函数应用到原有代码中，修改后的循环逻辑：

$range="A1:B2".Split(":")

$startrow= [int]($range[0] -replace "[^0-9]")
$startcol= Convert-ExcelColumnNameToNumber -ColumnName ($range[0] -replace "[^A-Z]")
$maxrow= [int]($range[1] -replace "[^0-9]")
$maxcol= Convert-ExcelColumnNameToNumber -ColumnName ($range[1] -replace "[^A-Z]")

$value = 0
for ($col = $startcol; $col -le $maxcol; $col++) {
    for ($row = $startrow; $row -le $maxrow; $row++) {
        $cellValue = [int]$ws_test.Cells.Item($row, $col).Text
        $value += $cellValue
    }
}
Write-Host "总和是: $value"

2. 更高效的求和方式：直接利用Excel功能 #

无需手动遍历单元格，借助Excel自身的计算能力或PowerShell的内置命令，能更高效地完成求和：

• 方法一：调用Excel的SUM函数计算

$targetRange = $ws_test.Range("A1:B2")
$sumValue = $ws_test.Evaluate("SUM($($targetRange.Address))")
Write-Host "总和是: $sumValue"

• 方法二：通过PowerShell的Measure-Object求和

$targetRange = $ws_test.Range("A1:B2")
$sumValue = ($targetRange.Value2 | Measure-Object -Sum).Sum
Write-Host "总和是: $sumValue"

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