6
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

コマンドレットの結果を Excel に出力する

Last updated at Posted at 2019-03-31

概要

PowerShellのコマンドレットの結果1を Excel ファイルに出力するための汎用的な関数を作りました。Out-GridView コマンドレットや ConvertTo-Html コマンドレットの様な感覚で使えます。

内部的には、Export-Csv コマンドレットを用いて一旦テンポラリファイルに CSV 形式で出力し、その CSV ファイルを Excel でオープンして簡単な整形を行なった後、指定されたファイル名の Excel ファイル(拡張子 .xlsx)に保存しています。

使い方

Out-GridView、ConvertTo-Html、Out-File などのコマンドレットと同様にパイプラインでコマンドレットの出力を Export-Excel 関数(エイリアス:ee)に渡します(第一引数に Excel ファイル名の指定が必要)。MaxColumnWidth パラメータ(エイリアス:MCW,w)でセルの幅の最大値を指定できます(既定値は 50)。

使用例

実行中プロセスの ID, プロセス名, CPU, 非ページメモリ, ページアウト可能メモリ, ワーキングセット を Excel に保存。

PS> ps | select Id, ProcessName, CPU, NPM, PM, WS | ee process.xlsx

コード

プロファイル設定($profile のパスが示すファイル)に下記の関数定義を加えるか、拡張子 .ps1 のファイルに保存してドットソースで実行してください2

Function Export-Excel {
<#
    .SYNOPSIS
    パイプ経由で入力されたコマンドレットの結果を Excelファイルに出力します。
    (別名:ee)

    .DESCRIPTION
    内部的には、Export-Csv コマンドレットを用いて一旦テンポラリファイルに
    CSV 形式で出力し、その CSV ファイルを Excel でオープンして簡単な整形を
    行なった後、指定されたファイル名の Excel ファイル(拡張子 .xlsx)に
    保存しています。

    <CommonParameters> はサポートしていません。

    .PARAMETER FilePath
    Excel ファイルのパスを指定します。このパラメータは必須です。
    拡張子が .xlsx と異なる場合は、末尾に .xlsx が付加されます。

    .PARAMETER LiteralPath
    Excel ファイルのパスを指定します。FilePath パラメータと異なり、ワイルド
    カードは認識されません。拡張子が .xlsx と異なる場合は、末尾に .xlsx が
    付加されます。(別名:PSPath)

    .PARAMETER Force
    書き込み禁止のファイルに対しても上書きを行ないます。

    .PARAMETER NoClobber
    ファイルの上書きを禁止します。(エイリアス:NoOverwrite)

    .PARAMETER MaxColumnWidth
    セルの列幅の最大値を指定します。既定値は 50.0 です。(別名:MCW,w)

    .PARAMETER InputObject
    入力オブジェクトをパイプ経由で受け取ります。

    .EXAMPLE
    実行中プロセスの ID, プロセス名, CPU, 非ページメモリ, ページアウト可能
    メモリ, ワーキングセット を Excel に保存

    PS> ps | select Id, ProcessName, CPU, NPM, PM, WS | ee process.xlsx

    .INPUTS
    コレクション(配列)型オブジェクトのパイプ経由入力

    .OUTPUTS
    Excelファイル

    .NOTES
    作者: earthdiver1
    バージョン: V0.13
    クリエイティブ・コモンズ 表示 - 継承 4.0 国際 ライセンスの下に提供されています。
#>
    [Alias("ee")][CmdletBinding(DefaultParameterSetName="ByPath")]
    param(
        [Parameter(ParameterSetName="ByPath", Mandatory=$true, Position=0)]
        [ValidateNotNullOrEmpty()]
        [String]$FilePath,

        [Parameter(ParameterSetName="ByLiteralPath", Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [Alias("PSPath")][String]$LiteralPath,

        [switch]$Force,

        [Alias("NoOverwrite")][Switch]$NoClobber,

        [ValidateRange(0, 255)]
        [Alias("MCW","w")][Double]$MaxColumnWidth = 50,

        [Parameter(ValueFromPipeline=$True)]
        [ValidateNotNull()]
        [PSObject]$InputObject
    )

    $ErrorActionPreference = "Stop"

    $MyName = $MyInvocation.InvocationName

    if ($InputObject -eq $Null) { return }

    if ($PSBoundParameters.ContainsKey("FilePath")) { # -FilePath が指定された場合
        if ($FilePath -NotMatch '\.xlsx$') { $FilePath += ".xlsx" }
        $excelfile = $FilePath | Resolve-Path -EA SilentlyContinue | Convert-Path | ? { Test-Path $_ -PathType Leaf }
        if ($?) { # ワイルドカードパス
            if ($excelfile.count -eq 0) {
                throw "$MyName : ワイルドカード パス $FilePath がファイルに解決されなかったため、操作を実行できません。"
            } elseif ($excelfile.count -gt 1) {
                throw "$MyName : パスが複数のファイルに解決されるため、操作を実行できません。このコマンドを複数ファイルに使用できません。"
            }
        } else {
            $excelfile = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($FilePath)
        }
    } else {                                          # -LiteralPath が指定された場合
        if ($LiteralPath -NotMatch '\.xlsx$') { $LiteralPath += ".xlsx" }
        $excelfile = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($LiteralPath)
    }

    if (Test-Path $excelfile) {
        if ($PSBoundParameters.ContainsKey("NoClobber")) { 
            throw "$MyName : ファイル '$excelfile' は既に存在します。"
        } elseif ((Get-ChildItem $excelfile).IsReadOnly -and (-not $Force)) {
            throw "$MyName : パス '$excelfile' へのアクセスが拒否されました。"
        }
    }

    Add-Type -AssemblyName Microsoft.Office.Interop.Excel
    $xlCSV             = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV
    $xlWorkbookDefault = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
    $xlHAlignCenter    = [Microsoft.Office.Interop.Excel.XlHAlign]::xlHAlignCenter
    $xlShiftDown       = [Microsoft.Office.Interop.Excel.XlInsertShiftDirection]::xlShiftDown
    $xlShiftToRight    = [Microsoft.Office.Interop.Excel.XlInsertShiftDirection]::xlShiftToRight
    $default = [Type]::Missing

    try {
        $tmpfile = [IO.Path]::GetTempFileName() | Rename-Item -NewName { $_.Replace(".tmp",".csv") } -PassThru
        $Input | Export-Csv -Append -Encoding UTF8 -LiteralPath $tmpfile -NoTypeInformation
        if (Test-Path $excelfile) { Remove-Item -LiteralPath $excelfile -Force }
        & {
            $excel = New-Object -ComObject Excel.Application
            $excel.Visible = $False
            $excel.DisplayAlerts = $false
            $wb  = $excel.Workbooks.Open($tmpfile)
            $ws = $wb.Sheets[1]
            $ws.Name = "Sheet1"
            $ws.Columns.Item(1).Insert($xlShiftToRight) | Out-Null
            $ws.Cells.Item(1,1).Value = "No."
            $ur = $ws.UsedRange
            $ws.Range($ws.Cells.Item(1,2), $ws.Cells.Item(1,$ur.Columns.Count)).AutoFilter() | Out-Null
            $ws.Range($ws.Cells.Item(2,1), $ws.Cells.Item($ur.Rows.Count,1)).Formula = "=ROW()-2"
            $ur.Rows.Item(1).Font.Bold = $True
            $ur.Rows.Item(1).HorizontalAlignment = $xlHAlignCenter
            $ur.Rows.Item(1).Interior.ColorIndex = 15
            $ur.Columns.Item(1).HorizontalAlignment = $xlHAlignCenter
            $ur.Borders.LineStyle = 1
            $ur.EntireColumn.AutoFit() | Out-Null
            $ur.Columns | ? { $_.ColumnWidth -gt $MaxColumnWidth } | % { $_.ColumnWidth = [double]$MaxColumnWidth }
            $ur.WrapText = $True
            $ws.PageSetup.PrintArea = ($ur.Cells.Item(1,1).Address(0,0) + ":" +
                                       $ur.Cells.Item($ur.Rows.Count,$ur.Columns.Count).Address(0,0))
            $ws.PageSetup.Zoom = $false
            $ws.PageSetup.FitToPagesTall = 999
            $ws.PageSetup.FitToPagesWide = 1
            $ws.Rows.Item(1).Insert($xlShiftDown) | Out-Null
            $ws.Columns.Item(1).Insert($xlShiftToRight) | Out-Null
            $ws.Columns.Item(1).ColumnWidth = 1.67
            $wb.SaveAs($excelfile,$xlWorkbookDefault)
            $wb.Close()
            $excel.Quit()
        }
    } catch {
        throw
    } finally {
        [GC]::Collect()
        if (Test-Path $tmpfile) { Remove-Item $tmpfile -Force }
    }
}

おまけ

HTML形式のソート可能な表に出力する関数 Export-HtmlTable はこちら
(2019.04.03 HTML 表作成ライブラリを sortable-table から DataTable に変更。)

  1. 正確にはコレクション(配列)型オブジェクトの属性値

  2. スクリプトの実行が出来ない旨のエラーが出る場合は実行ポリシーを変更する必要があります(こちらを参照)。

6
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?