PowerShell から ExcelDataReader を使って拡張子 xls を攻略したい

経緯

Excel … しかも xls という古代兵器を持ち出され、致命傷を負って倒れそうになったとき、アテナの声が聞こえた
「みやみや、貴方は以前、PowerShell とライブラリの力で打ち勝ったではないですか… 聖闘士には 2 度同じ技は通じないのです！」

冗談です。車田正美先生すみません

xls という古い拡張子の Excel を読み込めるライブラリねーのかな？って調べると、ExcelDataReader というライブラリで読めるようです
※書き込みはできません

そして、Windows 環境で一番仲良くなっている PowerShell で処理をしようと思いたち、 「PowerShell ExcelDataReader」でググると…
あれ…？ 自分の記事 …？

というわけで、初めて使うような気持ちで以前より丁寧に書きます！

環境

  • Windows 10 Pro
  • PowerShell 5.1 ※ 7 系使おうとしたら、VSCode でサクッと動かなかったので 5.1
  • ExcelDataReader 3.6.0
  • ExcelDataReader.DataSet 3.6.0

前準備

ExcelDataReader と ExcelDataReader.Dataset をダウンロードして使います

  1. 公式の GitHub リポジトリの右にある Releases をクリック
  2. ExcelDataReader.3.6.0.nupkg と ExcelDataReader.DataSet.3.6.0.nupkg をクリックしてダウンロードします
  3. ダウンロードしたファイル 2 つを適当な場所に移し、右クリックしてプロパティを選び、セキュリティ欄の [ ]許可する(K) にチェックを入れて [ OK ] ボタンをクリックします
  4. ファイルの拡張子を zip に変えて解凍します

前準備 2

仕事のデータを使うわけにもいかないので、手元でデモデータを作ってしまいました

  • シートは 2 つで、表紙とデータの表
    • 表紙
    • データ

前準備 3

使用するファイルを同じフォルダに配置、PowerShell ファイルを同フォルダに作成
私は C ドライブ直下に learn というフォルダを作りました

PowerShell から ExcelDataReader を使ってデータ読む

とりあえず、昔の記事でやったとこまではサラッと流します

最初にお約束コードを書きます

$ErrorActionPreference = 'Stop'              # エラー時に止める
$ProgressPreference    = "SilentlyContinue"  # インジケーターの抑制

# カレントのパス
$currentPath = Split-Path -Parent $MyInvocation.MyCommand.Path

先にダウンロードしたライブラリから .NET Framework でビルドしている DLL を呼びます
もちろん、判り易いように何処か別のフォルダに移して読んでも OK ですが、以前の記事をみると どの DLL を使ったか が書いてなくて、未来の自分に不親切だなぁっと思って、下記のようなサンプルコードにしました

# DLL の読み込み
[void][Reflection.Assembly]::LoadFile((Join-Path $currentPath '.\ExcelDataReader.3.6.0\lib\net45\ExcelDataReader.dll'))
[void][Reflection.Assembly]::LoadFile((Join-Path $currentPath '.\ExcelDataReader.DataSet.3.6.0\lib\net35\ExcelDataReader.DataSet.dll'))

次に、FileStream を開き、ExcelDataReader で Excel を読み込みます

# FileStream を開く
$stream = [System.IO.File]::Open((Join-Path $currentPath $filename), [System.IO.FileMode]::Open, [System.IO.FileAccess]::Read)

# ExcelReader の生成 
$reader = [ExcelDataReader.ExcelReaderFactory]::CreateReader($stream)

# AsDataSet の設定
$asDataSetConfig = [ExcelDataReader.ExcelDataSetConfiguration]::new()
$asDataSetConfig.UseColumnDataType = $true
$asDataSetConfig.ConfigureDataTable = {
    $conf = [ExcelDataReader.ExcelDataTableConfiguration]::new()
    $conf.UseHeaderRow = $false
    return $conf
}

# データの読み込み
$data = [ExcelDataReader.ExcelDataReaderExtensions]::AsDataSet($reader, $asDataSetConfig)

ここまでで Excel ファイルの読み込みは終わり、 $data に値が入っています
試しにいくつか確認してみます

シートは Tables として格納されています
シートの数を取得する

PS C:\learn> $data.Tables.Count
2

最初のシート名

PS C:\learn> $data.Tables[0].TableName
表紙

シート内のデータアクセスは Rows で行を、Column で列を表します

2 番目のシートの行数

PS C:\learn> $data.Tables[1].Rows.Count
34

2番めのシートの B 列(商品A) 9 行目(1月7日)の値を取得

PS C:\learn> $data.Tables[1].Rows[8].Column1
9
# または
PS C:\learn> $data.Tables[1].Rows[8].Item('Column1')
9

処理が終わったら最後にオブジェクトを開放しました

$reader.Close()
$reader.Dispose()
$reader = $null
$stream.Close()
$stream.Dispose()
$stream = $null
$data.Clear()
$data.Dispose()
$data = $null

Tips な備忘録

Tips になるかわからないけど、すぐ書き方を忘れそうなので備忘録的なやつ

パスワード付き Excel ファイルを開く

ExcelReader を生成するときに、パスワードを設定したオブジェクトを渡せば OK

# ExcelReader の設定
$readerConfig = [ExcelDataReader.ExcelReaderConfiguration]::new()
$readerConfig.Password = 'misono'

# ExcelReader の生成 
$reader = [ExcelDataReader.ExcelReaderFactory]::CreateReader($stream, $readerConfig)

特定のシートのみを読み込む時

AsDataSet の設定で可能
シート名「表紙」以外のシートを読み込む時

# AsDataSet の設定
$asDataSetConfig = [ExcelDataReader.ExcelDataSetConfiguration]::new()
$asDataSetConfig.FilterSheet = {
    param($tableReader, $sheetIndex)
    if ($tableReader.Name -ne '表紙') {
        return $true
    }
}
$asDataSetConfig.UseColumnDataType = $true
$asDataSetConfig.ConfigureDataTable = {
    $conf = [ExcelDataReader.ExcelDataTableConfiguration]::new()
    $conf.UseHeaderRow = $false
    return $conf
}

# データの読み込み
$data = [ExcelDataReader.ExcelDataReaderExtensions]::AsDataSet($reader, $asDataSetConfig)

例

カレントフォルダにある デモデータ.xls を読み込んで、表紙以外の各シートの総合計をコンソールに出力する例

$ErrorActionPreference = 'Stop'              # エラー時に止める
$ProgressPreference    = "SilentlyContinue"  # インジケーターの抑制

# カレントのパス
$currentPath = Split-Path -Parent $MyInvocation.MyCommand.Path

# 読み込むファイル名
$filename = 'デモデータ.xls'

# DLL の読み込み
[void][Reflection.Assembly]::LoadFile((Join-Path $currentPath '.\ExcelDataReader.3.6.0\lib\net45\ExcelDataReader.dll'))
[void][Reflection.Assembly]::LoadFile((Join-Path $currentPath '.\ExcelDataReader.DataSet.3.6.0\lib\net35\ExcelDataReader.DataSet.dll'))

try {
    # FileStream を開く
    $stream = [System.IO.File]::Open((Join-Path $currentPath $filename), [System.IO.FileMode]::Open, [System.IO.FileAccess]::Read)

    # ExcelReader の設定
    $readerConfig = [ExcelDataReader.ExcelReaderConfiguration]::new()
    $readerConfig.Password = 'misono'

    # ExcelReader の生成 
    $reader = [ExcelDataReader.ExcelReaderFactory]::CreateReader($stream, $readerConfig)

    # AsDataSet の設定
    $asDataSetConfig = [ExcelDataReader.ExcelDataSetConfiguration]::new()
    $asDataSetConfig.FilterSheet = {
        param($tableReader, $sheetIndex)
        if ($tableReader.Name -ne '表紙') {
            return $true
        }
    }
    $asDataSetConfig.UseColumnDataType = $true
    $asDataSetConfig.ConfigureDataTable = {
        $conf = [ExcelDataReader.ExcelDataTableConfiguration]::new()
        $conf.UseHeaderRow = $false
        return $conf
    }

    # データの読み込み
    $data = [ExcelDataReader.ExcelDataReaderExtensions]::AsDataSet($reader, $asDataSetConfig)

    # シートの数分繰り返す
    for($sheetCnt = 0; $sheetCnt -lt $data.Tables.Count; $sheetCnt++) {
        $sheetName        = $data.Tables[$sheetCnt].TableName                                  # シート名
        $lastRowNumber    = ($data.Tables[$sheetCnt].Rows.Count - 1)                           # 最後の行番号
        $lastColumnNumber = ($data.Tables[$sheetCnt].Rows[$lastRowNumber].ItemArray.Count - 1) # 最後の列番号
        $allSumValue      = ($data.Tables[$sheetCnt].Rows[$lastRowNumber].Item("Column$($lastColumnNumber)"))

        Write-Host ("シート「{0}」の総合計は {1} です" -f $sheetName, $allSumValue)
    }

} catch {
    throw

} finally {
    # オブジェクトの解放
    if ($reader -is [ExcelDataReader.IExcelDataReader]) {
        $reader.Close()
        $reader.Dispose()
        $reader = $null
    }
    if ($stream -is [System.IO.FileStream]) {
        $stream.Close()
        $stream.Dispose()
        $stream = $null
    }
    if ($data -ne $null -and $data.GetType().FullName -eq "System.Data.DataSet") {
        $data.Clear()
        $data.Dispose()
        $data = $null
    }
}

ProgrammingExcel, PowerShell

Posted by みやみや