適材適所

システム屋のくらげが気ままに書いているブログです。PowerShellやVBAなどプログラミング系の話をメインに書いています。

MENU

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

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

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

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

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

もちろんバイナリであるxls形式(97-2003の形式)は対応していませんので悪しからず。

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()
    }
}

ひとこと

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

使い方

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

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

単発で使う場合

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

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

f:id:shinmai_papa:20211028102124p:plain

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

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

f:id:shinmai_papa:20211028102133p:plain

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

f:id:shinmai_papa:20211028102129p:plain

パイプラインで使う場合

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

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

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

f:id:shinmai_papa:20211028102139p:plain

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

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

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

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

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

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

注意

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

終わりに

Excelブックのシート名を取得する関数の紹介でした。

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

色々できそうなのでもうちょっと調査してみまーす!!

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