https://life-is-command.com/powershell-excel/
https://qiita.com/miyamiya/items/161372111b68bad0744a
https://qiita.com/vicugna-pacos/items/54c5e20d3c3ffae94518
https://social.technet.microsoft.com/Forums/ja-JP/6c116e2b-5f14-4b72-b3dc-254c4597cb52/hashtablesort?forum=powershellja
想定シナリオ:
6列のエクセル(1行目ヘッダー)から
ヘッダーを除いて1,3,5列のみ取得して、3列目の昇順にソートしてエクセルへ出力
# テスト用ファイル作成
$excel = new-object -com excel.application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$wb1 = $excel.workbooks.add()
$wb1.worksheets(1).cells(1,1) = "col1"
$wb1.worksheets(1).cells(1,2) = "col2"
$wb1.worksheets(1).cells(1,3) = "col3"
$wb1.worksheets(1).cells(1,4) = "col4"
$wb1.worksheets(1).cells(1,5) = "col5"
$wb1.worksheets(1).cells(1,6) = "col6"
for($i=1;$i -le 10;$i++){
$wb1.worksheets(1).cells($i+1,1) = "A"+$i
$wb1.worksheets(1).cells($i+1,2) = "B"+$i
$wb1.worksheets(1).cells($i+1,3) = Get-Random -Maximum 100
$wb1.worksheets(1).cells($i+1,4) = $i*4
$wb1.worksheets(1).cells($i+1,5) = $i*5
$wb1.worksheets(1).cells($i+1,6) = $i*6
}
$wb1.SaveAs("H:\tmp\a.xlsx")
[void]$wb1.close()
# ファイル加工
$wb2 = $excel.Workbooks.Open("H:\tmp\a.xlsx")
$ws2 = $wb2.Sheets(1)
# シートの内容を取得
# 1カラム目が空になったら終了
$array = @()
$i = 2
while ($ws2.cells($i,1).Value() -ne $null) {
$tmp = @{col1 = $ws2.cells($i,1).Value(); col2 = $ws2.cells($i,3).Value(); col3 = $ws2.cells($i,5).Value()}
$array += $tmp
$i++
}
foreach($i in $array) {Write-Host $i.col1 ":" $i.col2 ":" $i.col3}
# ソート済み辞書(連想配列)をエクセルに書き込む
$wb3 = $excel.workbooks.add()
$sortKey=@{Expression={$_.Item("col2")};ascending=$true}
$i = 1
$array | Sort-Object ($sortKey) | `
%{$wb3.worksheets(1).cells($i,1)=$_.col1; `
$wb3.worksheets(1).cells($i,2)=$_.col2; `
$wb3.worksheets(1).cells($i,3)=$_.col3; `
$i++}
$wb3.SaveAs("H:\tmp\b.xlsx")
[void]$wb2.close()
[void]$wb3.close()
# ワークブックデータ確認
$wb4 = $excel.Workbooks.Open("H:\tmp\b.xlsx")
$ws4 = $wb4.Sheets(1)
$i = 1
while ($ws4.Range("A"+$i).Value() -ne $null) {
Write-Host $ws4.Range("A"+$i+":C"+$i).Value()
$i++
}
[void]$wb4.close()
# Excel の終了 変数の破棄
[void]$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb1)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb2)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb3)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb4)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws2)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws4)
