如何用PowerShell选择Excel指定范围快速导出追加到CSV文件

这是此前Stack Overflow问题《How to use powershell to select and copy columns and rows in which data is present in new workbook.》的衍生版本。
需求是 从多个Excel工作簿中提取固定列，将所有数据汇总导出到同一个CSV文件中，所有待处理Excel的目标列位置完全一致。
当前使用的实现代码如下：

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false
$counter = 0

$input_folder = "C:\Users\user\Documents\excelfiles"
$output_folder = "C:\Users\user\Documents\csvdump"

Get-ChildItem $input_folder -File |
Foreach-Object {
    $counter++
    $wb = $xl.Workbooks.Open($_.FullName, 0, 1, 5, "")
    try {
        $ws = $wb.Worksheets.item('Calls') # => This specific worksheet
        $rowMax = ($ws.UsedRange.Rows).count
        for ($i=1; $i -le $rowMax-1; $i++) {
            $newRow = New-Object -Type PSObject -Property @{
                'Type'                  = $ws.Cells.Item(1+$i,1).text
                'Direction'             = $ws.Cells.Item(1+$i,2).text
                'From'                  = $ws.Cells.Item(1+$i,3).text
                'To'                    = $ws.Cells.Item(1+$i,4).text
            }
        $newRow | Export-Csv -Path $("$output_folder\$ESO_Output") -Append  -noType -Force 
            }
        }

    } catch {
        Write-host "No such workbook" -ForegroundColor Red
        # Return
    }
}

当前代码可以正常运行，但执行效率极低：原因是需要逐单元格读取Excel内容，PowerShell逐行构造对象后再逐行追加写入CSV文件。需求是找到方法直接在Excel中选中指定范围（列数乘以$ws.UsedRange.Rows的总行数），去掉表头后直接将整段范围数据作为数组批量追加到CSV文件，大幅提升处理效率。

核心优化思路有三点：

• 直接读取Excel指定范围的二维数组，代替逐单元格读取，减少COM接口交互次数
• 内存中批量构造目标对象数组，代替逐行生成对象
• 全量数据汇总完成后一次性写入CSV，代替逐行追加IO操作

优化后代码 #

# 初始化Excel COM对象
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false

# 配置路径参数
$input_folder = "C:\Users\user\Documents\excelfiles"
$output_folder = "C:\Users\user\Documents\csvdump"
$output_file = Join-Path $output_folder "merge_result.csv"
# 定义要提取的列对应的属性名，顺序和Excel列顺序一致
$colProps = @('Type', 'Direction', 'From', 'To')
# 初始化结果数组
$allResults = @()

Get-ChildItem $input_folder -File | ForEach-Object {
    $wb = $xl.Workbooks.Open($_.FullName, 0, 1, 5, "")
    try {
        $ws = $wb.Worksheets.Item('Calls')
        $usedRange = $ws.UsedRange
        $rowCount = $usedRange.Rows.Count
        # 跳过表头，直接读取第2行到最后一行、第1到4列的所有数据，返回二维数组
        $dataArray = $ws.Range($ws.Cells(2,1), $ws.Cells($rowCount, 4)).Value
        # 遍历二维数组构造对象
        for ($i=1; $i -le $dataArray.GetUpperBound(0); $i++) {
            $rowObj = [PSCustomObject]@{}
            for ($j=1; $j -le $dataArray.GetUpperBound(1); $j++) {
                $rowObj | Add-Member -NotePropertyName $colProps[$j-1] -NotePropertyValue $dataArray[$i,$j]
            }
            $allResults += $rowObj
        }
        $wb.Close()
    }
    catch {
        Write-Host "文件$($_.FullName)处理失败：无对应工作表" -ForegroundColor Red
    }
}

# 一次性导出所有数据到CSV
$allResults | Export-Csv -Path $output_file -NoTypeInformation -Force -Encoding UTF8

# 释放COM对象，避免Excel进程残留
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
Remove-Variable xl

效率提升说明 #

• 批量读取范围数据的耗时仅为逐单元格读取的1%~5%，文件数量越多、单文件行数越多提升越明显
• 一次性写入CSV避免了频繁的文件IO操作，进一步降低耗时
• 新增COM对象释放逻辑，避免后台残留Excel进程占用资源

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