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”