PowerShell+Excelでフィルタ機能がうまくいかないときは、非表示の行や列を確認

非表示の列が原因でした。

やりたかったこと

COMオブジェクト(Excel.Application)を使ってExcelをPowershellから操作する処理を実装していました。

1. Excel開く
2. Book開く
3. Sheet開く
4. Rangeを定義する
5. フィルタがあったら解除して、
6. AutoFilter メソッドでフィルタ、
7. SpeciaCells メソッドで xlCellTypeeVisible のものを抽出
8. Foreach で回す
9. N行目を取ってくる

という簡単な処理でした。

こんな感じに、1列目にステータスが書かれています。
「使う」と「使うかも」のORでフィルタして、6列目の内容を取ってくる処理でした。

起きたこと

ある時から正しい値が取れなくなくなりました。

あるときこのスクリプトが動かなくなりました。
デバッグすると、なぜかフィルタした結果のForEachが2倍呼ばれており、関係ないデータを結果を出すように。

抽出結果は3件なのに、6件呼ばれています。

よーく見ると、ExcelのC列が非表示になってました。

試しに非表示列を増やしてみました。3倍になりました。非表示にした列を基準として、指定したカラムの内容が取れている模様。

教訓

Excel シートを使ったフィルタ処理を入れる場合は、処理前に非表示のセルを全部表示しましょう。

何なら一時的にCSVやXMLに出力してそれをPowershellで読み込む、というのもありかもしれないですね。
COMの処理がもう少し早いならフィルタもPowershellでやるのですが、ループで一つずつしゅとくしてハッシュテーブルやPSObjectにすると結構重たいので悩ましい。

サンプルコード

AutoFilterがうまくいかない.ps1

try
{
    # シート、フィルタ列、とってくる列
    $sheetName = "Sheet1"
    $FilterColNum = 1
    $GetColNum = 6
    Write-Host "$sheetname の $filterColnum 列目をフィルタして $GetColNum 列目を取ってくる。" -ForegroundColor Green

    # Excel 開く
    $path = "c:\temp\サンプル.xlsx"
    $excel = New-Object -ComObject Excel.Application
    $excel.DisplayAlerts = $false # 閉じるときに保存するか聞かない
    $book = $excel.Workbooks.Open($path,$null,$true) # $Path を 読み取り専用で開く

    # ステップ実行するときは $True になっているとわかりやすい
    $Excel.Visible = $false

    # A2～G5のレンジを定義(2行1列 & 5行7列)
    $sheet = $book.sheets.item($SheetName)
    $range = $sheet.range($sheet.cells(2,1), $sheet.cells(5,7))

    # フィルタ設定を解除（もし既にフィルタが設定されている場合）
    $Sheet.AutoFilterMode = $False
    
    # **** 非表示のセルを再表示 ****
    $Range.EntireRow.Hidden = $false
    $Range.EntireColumn.Hidden = $false
    # **** ↑のコードが無いとこの記事が再現できます ****
    
    # 「使う」または「使うかも」のものを抽出して、6列目の値をとってくる、という趣旨のコード

    $xlFilterValues = [Microsoft.Office.Interop.Excel.XlAutoFilterOperator]::xlFilterValues 
    $null = $range.AutoFilter($FilterColNum,@("使う","使うかも"),$xlFilterValues)

    # フィルタした結果が $FilterdRangeに入る
    $FilterdRange = $range.SpecialCells([Microsoft.Office.Interop.Excel.XlCellType]::xlCellTypeVisible)

    # 何行抽出されたか
    write-host "抽出結果は $($filterdRange.Rows.Count) 行"

    # 出力
    $i=1
    foreach($row in $FilterdRange.Rows)
    {
        Write-Host "$i : $($row.Cells.Item(1,$GetColNum).Value2)"
        $i ++
    }

    write-host "完了" -ForegroundColor Green
}
catch
{
    throw
}
finally
{
    # 後始末
    if((Test-Path "variable:book"))
    {
	    $Book.close($false)  #保存せずに閉じる
    }
    if((Test-Path "variable:excel"))
    {
	    $excel.Quit() #保存せずに閉じる
    }

    @("book","Excel") | Foreach-Object { ` 
	    if((Test-Path "variable:$_"))
	    {
		    [System.Runtime.Interopservices.Marshal]::ReleaseComObject( (Get-Variable $_ -ValueOnly)) | Out-Null
	    }
    }
}