Excel 合并至一个表

Function Merger-Excel{

param(
[parameter(mandatory=$true)][string]$SrcFolder,
[parameter(mandatory=$true)][string]$DstPath,
[parameter(mandatory=$true)][int]$SrcSheetIndex,
[parameter(mandatory=$true)][int]$DstcSheetIndex,
[parameter(mandatory=$true)][string]$StartColumn,
[parameter(mandatory=$true)][string]$EndColumn,
[parameter(mandatory=$true)][string]$ColumnIndex,
[parameter(mandatory=$true)][int]$StartRow

)
$files=Get-ChildItem -Path $SrcFolder -Filter *.xlsx
$xl=New-Object -ComObject excel.application
$xl.visible=$false
$xl.AlertBeforeOverwriting=$false
$xl.DisplayAlerts=$false
$wb_dst=$xl.Workbooks.Open($DstPath)
$ws_dst=$wb_dst.Sheets.Item($DstcSheetIndex)

for($i=0;$i -lt $files.Count;$i++){

$wb=$xl.workbooks.open($files[$i].FullName)
$ws=$wb.Sheets.Item($SrcSheetIndex)
$srcrange=”$ColumnIndex”+65535
$all=$ws.range($srcrange).end([Microsoft.office.Interop.Excel.xlDirection]::xlup).row #井号列
$Range=$StartColumn+$StartRow+”:”+$EndColumn+$all
$ws.Range($Range).Copy()|Out-Null
$dstrange=”$ColumnIndex”+65535
$Dst_all=$ws_dst.range($dstrange).end([Microsoft.office.Interop.Excel.xlDirection]::xlUp).row+1
$dst=$ColumnIndex+$Dst_all
$ws_dst.Range($dst).PasteSpecial([Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteValuesAndNumberFormats,[Microsoft.Office.Interop.Excel.XlPasteSpecialOperation]::xlPasteSpecialOperationNone,$false,$false)|Out-Null
$wb.Close()
}
$wb_dst.save()
$wb_dst.close()
}
Merger-Excel -SrcFolder “C:\Users\lwang52\OneDrive – Schlumberger\Desktop\test” -DstPath “C:\Users\lwang52\OneDrive – Schlumberger\Desktop\test.xlsx” -SrcSheetIndex 1 -DstcSheetIndex 1 -StartColumn “A” -EndColumn “B” -ColumnIndex “A” -StartRow 2