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

×用微信扫描并分享
本文链接: https://www.pstips.net/excel-%e5%90%88%e5%b9%b6%e8%87%b3%e4%b8%80%e4%b8%aa%e8%a1%a8.html
请尊重原作者和编辑的辛勤劳动,欢迎转载,并注明出处!

发表评论

电子邮件地址不会被公开。 必填项已用*标注