如何用PowerShell将多个CSV合并为带多工作表的单个Excel文件

需要将\\tmp\\目录下所有CSV文件合并为一个名为LDAP.xlsx的Excel文件，保存到\\reports\\目录，每个CSV对应一个以原文件名命名的工作表。当前代码会为每个CSV生成单独的Excel文件，无法实现多合一需求，以下是修改方案及说明。

原代码 #

Clear-Host
# SOURCE
##########
# config file
$conf_file = "C:\\PS_LDAP_searchlight\\config\\searchlight_conf.conf"
$conf_values = Get-Content $conf_file | Out-String | ConvertFrom-StringData

# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path

# PROGRAM
##########
$workingdir = $main_path + $tmp_path + "*.csv"
$reportsdir = $main_path + $reports_path
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $reportsdir + "\\" + $inputCSV.Basename + ".xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
### Set the delimiter (, or ;) according to your regional settings
### $Excel.Application.International(3) = ,
### $Excel.Application.International(5) = ;
$query.TextFileOtherDelimiter = $Excel.Application.International(5)
### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
### Execute & delete the import query
$query.Refresh()
$query.Delete()
### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

# Cleaner
$inputCSV = $null
$outputXLSX = $null

}
## To exclude an item, use the '-exclude' parameter (wildcards if needed)
#remove-item -path $workingdir -exclude *Crab4dq.csv

# CLEANER
###############################
# SOURCE
###############################
# config file
    $conf_file = $null
    $conf_values = $null
# variables from config file
    $main_path = $null
    $tmp_path = $null
    $reports_path = $null
# PROGRAM
###############################
    $workingdir = $null
    $csv = $null
    $reportsdir = $null

修改方案及说明 #

核心修改是只创建一次Excel应用和主工作簿，在循环中为每个CSV添加新工作表并导入数据，最后统一保存退出。关键调整点：

• 将Excel应用实例、主工作簿的创建移到foreach循环外部，避免重复创建/销毁Excel进程
• 循环内新增工作表（而非新建工作簿），并将工作表重命名为CSV文件的基础名称
• 所有CSV数据导入完成后，统一保存主工作簿并退出Excel
• 优化COM对象清理逻辑，避免残留进程

修改后的完整代码 #

Clear-Host
# SOURCE
##########
# config file
$conf_file = "C:\\PS_LDAP_searchlight\\config\\searchlight_conf.conf"
$conf_values = Get-Content $conf_file | Out-String | ConvertFrom-StringData

# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path

# PROGRAM
##########
# 使用Join-Path优化路径拼接，避免分隔符错误
$workingdir = Join-Path -Path $main_path -ChildPath $tmp_path | Join-Path -ChildPath "*.csv"
$reportsdir = Join-Path -Path $main_path -ChildPath $reports_path
$outputXLSX = Join-Path -Path $reportsdir -ChildPath "LDAP.xlsx"
$csvFiles = Get-ChildItem -Path $workingdir

# 仅创建一次Excel应用实例
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Add(1)
# 删除默认空白工作表，后续为每个CSV新建工作表
$defaultSheet = $workbook.Worksheets.Item(1)
$defaultSheet.Delete()

foreach($inputCSV in $csvFiles){
    # 新建工作表
    $worksheet = $workbook.Worksheets.Add()
    # 将工作表重命名为CSV文件的基础名称（不含扩展名）
    $worksheet.Name = $inputCSV.Basename

    # 导入CSV数据到当前工作表
    $TxtConnector = "TEXT;" + $inputCSV.FullName
    $Connector = $worksheet.QueryTables.Add($TxtConnector, $worksheet.Range("A1"))
    $query = $worksheet.QueryTables.Item($Connector.Name)

    # 根据区域设置自动获取分号作为分隔符，逗号分隔可改为International(3)
    $query.TextFileOtherDelimiter = $excel.Application.International(5)
    $query.TextFileParseType = 1
    # 设置所有列为文本格式，避免数值/日期自动转换问题
    $query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
    $query.AdjustColumnWidth = 1

    # 执行导入并清理查询对象
    $query.Refresh()
    $query.Delete()
}

# 保存合并后的Excel文件（51对应.xlsx格式）
$workbook.SaveAs($outputXLSX, 51)
# 退出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()

# CLEANER
###############################
# 清理变量
$conf_file = $null
$conf_values = $null
$main_path = $null
$tmp_path = $null
$reports_path = $null
$workingdir = $null
$csvFiles = $null
$reportsdir = $null
$outputXLSX = $null
$excel = $null
$workbook = $null
$worksheet = $null

额外说明 #

1. 路径处理：用Join-Path替代字符串拼接，避免因分隔符缺失/重复导致的路径错误
2. 工作表命名限制：Excel工作表名称不能超过31字符，且不能包含/ \ ? * : [ ]特殊字符，若CSV文件名不符合要求需提前处理
3. COM对象清理：手动释放COM对象并触发垃圾回收，防止Excel进程在后台残留占用资源
4. 分隔符适配：代码默认使用区域设置的分号，若你的CSV是逗号分隔，可将International(5)改为International(3)
5. 文件覆盖：DisplayAlerts = $False会自动覆盖已存在的LDAP.xlsx，若需覆盖提示可改为$True

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