PowerShell操作Excel遇工作表重名报错，求新增/复用工作表方案

核心问题分析 #

• 工作表存在性判断失效：原代码用$Sheets1 -Match $OutSheet直接比较工作表对象而非名称，导致无法正确识别已存在的工作表，反复触发新建操作。
• 循环逻辑错误：遍历目标工作簿所有工作表时，只要有一个表名称不匹配就创建新表，会重复创建同名工作表，触发"That name is already taken"报错。
• 集合未实时更新：$DestSheets = $Dest.Worksheets是初始集合，新建工作表后不会自动同步，后续判断仍基于旧数据。

修复后的完整代码 #

# 创建新工作簿
$Dest = $Excel.Workbooks.Add()

# 打开第一个文件
$Source = $Excel.Workbooks.Open($Files[0])
$FileName = $Source.Name
$SourceSheets = $Source.Worksheets

# 创建Key数组
$Array = @()

ForEach ($Sheet in $SourceSheets){
    # 获取数据最后一行并复制指定范围
    $lastRow = ($Sheet.UsedRange.Rows | Select -Last 1).Row
    $allRows = $Sheet.Range("L6","M$lastRow")

    # 在目标工作簿新建工作表并命名
    $OutputSheet = $Dest.Worksheets.Add()
    $OutputSheet.Name = $Sheet.Name

    # 写入表头并粘贴数据
    $OutputSheet.Cells.Item(1,1) = "Key"
    $OutputSheet.Cells.Item(1,2) = $FileName

    if([String]::IsNullOrEmpty($OutputSheet.Range("A2").Value2)){ 
        $allRows.Copy() | Out-Null
        [void]$OutputSheet.Range("A2").PasteSpecial(-4163)
    }

    $OutputSheet.UsedRange.EntireColumn.AutoFit() | Out-Null

    # 将Key值添加到数组
    $keyRange = $Sheet.Range("L6","L$lastRow").Value2
    foreach ($key in $keyRange){
        $Array += [PSCustomObject]@{ Key = $key }
    }
}

# 保存并关闭第一个文件相关资源
$Dest.SaveAs("$ParentFolder\Output Folder\SampleOutput2.xlsx")
$Dest.Close()
$Source.Close()

# 处理第二个文件
$Dest = $Excel.Workbooks.Open("$ParentFolder\Output Folder\SampleOutput2.xlsx")

$File = $Files[1]
Write-Host "Found File2"
$Source1 = $Excel.Workbooks.Open($File)
$FileName1 = $Source1.Name
$SourceSheets1 = $Source1.Worksheets

ForEach ($Sheet1 in $SourceSheets1) {
    # 检查目标工作簿是否已存在同名工作表
    $existingSheet = $Dest.Worksheets | Where-Object { $_.Name -eq $Sheet1.Name }

    if ($existingSheet) {
        Write-Host "工作表 $($Sheet1.Name) 已存在，执行对应操作"
        $existingSheet.Cells.Item(1,3) = $FileName1
    }
    else {
        Write-Host "工作表 $($Sheet1.Name) 不存在，新建工作表"
        $newSheet = $Dest.Worksheets.Add()
        $newSheet.Name = $Sheet1.Name
        # 可在此添加新建工作表后的初始化逻辑
    }
}

# 保存并清理资源
$Dest.Save()
$Dest.Close()
$Source1.Close()

# 关闭Excel进程，避免内存泄漏
[void]$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null

关键修复点说明 #

1. 精准判断工作表存在性：通过Where-Object匹配工作表名称，确保识别已存在的表。
2. 简化循环逻辑：对每个源工作表仅做一次存在性检查，避免重复创建操作。
3. 移除冗余循环：删除第一个循环中遍历所有目标工作表的无效逻辑，直接操作当前新建的工作表。
4. 完善资源管理：添加源文件关闭和Excel进程清理代码，防止内存泄漏。
5. 修复Key数组逻辑：改为遍历实际Key值，确保数组内容正确。

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