Powershell 列内容筛选拆分表


$path=”C:\Users\lwang52\OneDrive – Schlumberger\Desktop\oa.xlsx”

Function Unmerge-ExcelCol{

param(
[parameter(mandatory=$true)]$Path,
[parameter(mandatory=$true)]$SheetIndex,
[parameter(mandatory=$true)]$Range

)
$xl=New-Object -ComObject Excel.Application
$xl.Visible=$false
$xl.AlertBeforeOverwriting=$false
$xl.DisplayAlerts=$false
$wb=$xl.Workbooks.Open($path)
$ws=$wb.Sheets.Item($SheetIndex)
#$allindex=$DataCol+65535
#$all=$ws.range(“$allindex”).end([Microsoft.Office.Interop.Excel.XLDirection]::xlUp).row
$names=$ws.Range(“$Range”).value2|Select-Object -Unique

foreach($name in $names){
$ws.Range($Range).AutoFilter(1,$name,[Microsoft.Office.Interop.Excel.XlAutoFilterOperator]::xlAnd)
$ws.UsedRange.Copy()
$wd=$wb.Sheets.Add()
$wd.Name=$name
$wd.Range(“A1”).PasteSpecial([Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteValuesAndNumberFormats,[Microsoft.Office.Interop.Excel.XlPasteSpecialOperation]::xlPasteSpecialOperationNone,$false,$false)|Out-Null

}
$wb.save()
$wb.close()
$xl.Quit()

}
Unmerge-ExcelCol -Path $path -SheetIndex 1 -Range “H2:H4000”

×用微信扫描并分享
本文链接: https://www.pstips.net/powershell-%e5%88%97%e5%86%85%e5%ae%b9%e7%ad%9b%e9%80%89%e6%8b%86%e5%88%86%e8%a1%a8.html
请尊重原作者和编辑的辛勤劳动,欢迎转载,并注明出处!

发表评论

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