【PowerShell】Excelブックを開かずにシート名の一覧を作成する

Excelブックを開かずにシート名を取得することができるPowerShellの自作関数を紹介しようと思います。

xlsmやxlsxといったOffice Open XML形式だけに対応しています。

Office Open XMLは、国際的に仕様が定められており、中身はxmlの集まりです。

仕様の決まっているxmlなので、その中からシート名だけを抽出することができます。

xls形式(97-2003の形式)はOffice Open XMLではないため、今回の記事には対応していませんので悪しからず。

PowerShellでExcelのシート名の一覧を作成する関数

Excelブックを開かずに一覧を作成できるから便利かも。

 

function Get-WorksheetsList{
    [Cmdletbinding()]
    param([Parameter(Mandatory,ValueFromPipeline)]$ExcelPath)
    begin
    {
        Add-Type -Assemblyname System.IO.Compression.Filesystem
    }
    process
    {
        foreach($path in $ExcelPath)
        {
            if(!(Test-Path -LiteralPath $path))
            {
                Write-Error -Message ("ファイルが存在しません。[{0}]" -f $path)
                continue
            }
            if($path -is [string])
            {
                $item=Get-Item $path
            }
            else
            {
                $item=Get-Item $path.fullname
            }
            if($item.Extension -notin ('.xlsx','.xlsm'))
            {
                Write-Error -Message ("xlsx、xlsm形式以外は処理対象外です。[{0}]" -f $item.FullName)
                continue
            }
            #ZipfileClassの力を借りてzipのままファイルストリームを取得する
            $stream=($item |foreach{[System.IO.Compression.Zipfile]::Openread($_.fullname).Entries|Where-Object{$_.name -eq "workbook.xml"}}).Open()
            #ストリームを読み込むオブジェクトを準備する
            $reader=New-object System.IO.StreamReader -ArgumentList $stream
            #ストリームを最後まで読み込んでxmlにしてタグ名でシート名を取得する
            $list=([xml]($reader.ReadToEnd())).GetElementsByTagName('sheet').name
            #出力用のPSオブジェクトを作成する
            $list|foreach{[pscustomobject](@{"SheetName"=$_;"Name"=$item.Name;"FullName"=$item.FullName;})}
            #一応全て片付ける
            $reader.Close();$reader.Dispose();$stream.Close();$stream.Dispose()
        }
    }
    end
    {
        #最後のお片付け
        [gc]::Collect()
    }
}

Office Open XML

ひとこと

Zipファイルの中身を覗くことができるSystem.IO.Compression.Filesystemのメソッド群が便利すぎて涙が出てくる・・・。

使い方

Excelブックのシート名の一覧を作成する関数の使い方です。

単発でも使えますし、パイプでも使えるように作ったつもりです。

単発で使う場合

引数に対象のExcelファイルのパスを指定します。

 

Get-WorksheetsList -ExcelPath "C:\users\user\Desktop\Book1.xls"

すると、フルパスとシート名、ファイル名が返ってきます。

存在しないファイルを指定するとエラーになります。

対応していない形式のファイルを指定するとエラーになります。

パイプラインで使う場合

パイプラインにも対応しています。

次のようにGet-ChildItemコマンドレットなどで複数のファイルをパイプで渡すことで複数のファイルを処理することができます。

 

Get-ChildItem "C:\Users\USER\Documents\*xls*"|Get-WorksheetsList

これで特定のフォルダにあるExcelファイルにどんなシート名のシートがあるか簡単に一覧にすることができますね！

需要があるかどうかは知らんけども。

とりあえずフルパスとシート名、ファイル名を出力していますが、

「シート名だけが欲しい！！」というときもありますよね。

そういうときは、Select-Objectで必要なプロパティだけを選んであげてください。

 

Get-ChildItem "*.xls"|Get-WorksheetsList|Select-Object Sheetname,Name

注意

開いているExcelはエラーになります！！

終わりに

Excelを開かずにExcelの情報を得ることができるのは便利ですよね。

開かない分、処理も軽くなり、速くなっていいことばかりです。

Office Open XMLの仕様を理解していれば、理論上はシート名以外にもExcelの情報を取得することができます。

しかしまだOffice Open XMLの仕様を理解できていないのでシート名以外を得る方法はイマイチわかっておりません。

わかり次第、また記事にしたいと思います。

というわけで、ここまでお読みいただき、ありがとうございました。