如何用PowerShell将Excel指定区域导入二维数组并合并为CSV

核心问题解决 #

1. 自动定位数据结束行 #

通过Excel COM对象的End方法，从A2单元格向下查找第一个空行，以此确定数据的最后一行，适配不同数据量的文件：

# 从A2开始向下定位最后一个非空行
$startCell = $table.Cells.Item(2, 1)
$lastRow = $startCell.End([Microsoft.Office.Interop.Excel.XlDirection]::xlDown).Row
# 处理A2为空的极端情况（无有效数据）
if ($lastRow -eq 2 -and [string]::IsNullOrEmpty($startCell.Text)) {
    continue
}

2. 读取区域为二维数组 #

直接读取Range的Value属性，返回结果本身就是行×列的二维数组，无需逐个提取单元格文本，效率更高：

$range = $table.Range("A2:C$lastRow")
$dataArray = $range.Value

完整修改代码 #

# 获取目标目录下所有xlsx文件
$files = Get-ChildItem "H:\Mailbox\08_Support\Stackoverflow\" -Include "*.xlsx" -Recurse

# 初始化存储所有合并数据的数组
$allData = @()

# 创建Excel COM对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false # 关闭弹窗提示

try {
    foreach ($file in $files) {
        Write-Host "正在处理文件: $($file.Name)"
        
        $workbook = $excel.Workbooks.Open($file.FullName)
        $worksheet = $workbook.Worksheets.Item(1)

        # 确定数据结束行（A列第一个空行之前的行）
        $startCell = $worksheet.Cells.Item(2, 1)
        $lastRow = $startCell.End([Microsoft.Office.Interop.Excel.XlDirection]::xlDown).Row
        
        # 跳过无数据的文件
        if ($lastRow -eq 2 -and [string]::IsNullOrEmpty($startCell.Text)) {
            $workbook.Close($false)
            continue
        }

        # 读取A2到C列最后一行的区域数据
        $range = $worksheet.Range("A2:C$lastRow")
        $dataArray = $range.Value

        # 将二维数组转为自定义对象，保证CSV列结构统一
        for ($i = 1; $i -le $dataArray.GetLength(0); $i++) {
            $rowObject = [PSCustomObject]@{
                列1 = $dataArray[$i, 1]
                列2 = $dataArray[$i, 2]
                列3 = $dataArray[$i, 3]
                # 可选：添加来源文件标识
                来源文件 = $file.Name
            }
            $allData += $rowObject
        }

        $workbook.Close($false) # 关闭工作簿不保存
    }

    # 导出合并后的CSV
    $allData | Export-Csv -Path "H:\Mailbox\08_Support\Stackoverflow\合并结果.csv" -Encoding UTF8 -NoTypeInformation
    Write-Host "合并完成，结果已保存至合并结果.csv"
}
finally {
    # 强制释放Excel COM对象，避免后台残留进程
    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

代码说明 #

• 动态行定位：无需硬编码行号，自动适配不同数据量的Excel文件
• 高效数据读取：直接通过Range.Value获取二维数组，比逐个读取单元格性能提升明显
• 进程清理：在finally块中强制释放COM对象，避免Excel进程后台残留
• CSV兼容性：将数组转为PSCustomObject，确保导出的CSV列结构统一，可按需添加来源文件信息

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