PowerShell技术需求：合并多Excel工作表并以原文件名命名工作表

完全可以实现将合并后的工作表名称改为原Excel文件名，核心是在复制工作表后修改其Name属性，同时需要处理Excel工作表名称的合法性限制（如特殊字符、长度、重名问题）。

修改后的完整代码 #

$ExcelObject = New-Object -ComObject excel.application
$ExcelObject.Visible = $true
# 仅获取目标路径下的Excel文件（可按需调整过滤格式）
$ExcelFiles = Get-ChildItem -Path "C:\Users\XX/XX" -Filter *.xlsx
$Workbook = $ExcelObject.Workbooks.Add()
$DefaultSheet = $Workbook.Sheets.Item("Sheet1")

foreach($ExcelFile in $ExcelFiles) {
    $Everyexcel = $ExcelObject.Workbooks.Open($ExcelFile.FullName)
    $Everysheet = $Everyexcel.Sheets.Item(1)
    
    # 将工作表复制到新工作簿的默认Sheet1前方
    $Everysheet.Copy($DefaultSheet)
    # 定位刚复制的工作表（Copy后新表位于DefaultSheet之前，索引为DefaultSheet.Index -1）
    $NewSheet = $Workbook.Sheets.Item($DefaultSheet.Index - 1)
    
    # 生成合法工作表名称：去除扩展名，替换Excel禁止的特殊字符
    $ValidSheetName = $ExcelFile.BaseName -replace '[\\/:*?"<>|]', '_'
    # 限制名称长度不超过31字符（Excel工作表名称最大限制）
    $ValidSheetName = $ValidSheetName.Substring(0, [Math]::Min($ValidSheetName.Length, 31))
    
    # 处理重名：若名称已存在，自动添加序号
    $Suffix = 1
    $FinalSheetName = $ValidSheetName
    while ($null -ne $Workbook.Sheets.Item($FinalSheetName) -and $FinalSheetName -ne $DefaultSheet.Name) {
        $FinalSheetName = "$ValidSheetName($Suffix)"
        $Suffix++
    }
    
    # 设置新工作表的名称
    $NewSheet.Name = $FinalSheetName
    
    $Everyexcel.Close()
}

# 删除默认的Sheet1（不需要的话可执行此步骤）
$DefaultSheet.Delete()

$Workbook.SaveAs("C:\Users\XX\XX\merge.xlsx")
$ExcelObject.Quit()

# 清理COM对象，避免Excel进程残留后台
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Everysheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Everyexcel) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

关键改动说明 #

• 合法名称处理：用$ExcelFile.BaseName获取不带扩展名的文件名，通过正则替换掉Excel不允许的特殊字符（\/:*?"<>|），同时限制长度不超过31位（Excel工作表名称的硬性限制）。
• 重名规避：如果存在同名文件，自动添加序号后缀（如Report(1)、Report(2)），避免修改名称时触发报错。
• 精准定位新表：Copy($DefaultSheet)会将新工作表放在默认Sheet1的前面，通过$DefaultSheet.Index -1可以准确获取刚复制的工作表对象。
• 进程残留清理：添加COM对象释放和垃圾回收代码，防止运行脚本后Excel进程在后台残留。

额外提示 #

• 若需要处理.xls格式文件，将-Filter *.xlsx改为-Filter *.xls*即可。
• 若原Excel文件包含多个工作表，可修改循环遍历$Everyexcel.Sheets，将表名设置为文件名_原表名的格式，实现全表合并。

内容的提问来源于stack exchange，提问作者Carlos Di Paola