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

## 経緯

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

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

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

そして、Windows 環境で一番仲良くなっている PowerShell で処理をしようと思いたち、 「PowerShell ExcelDataReader」でググると...
あれ...？ [自分の記事](https://qiita.com/miyamiya/items/40fd054bc744dbea7c6a) ...？

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

## 環境

* 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 をクリック
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/6b53bde6-bd69-2b1a-3581-7c0815b3f244.png)
2. ExcelDataReader.3.6.0.nupkg と ExcelDataReader.DataSet.3.6.0.nupkg をクリックしてダウンロードします
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/d960e97a-4e6f-f25a-3d90-3e6c9bc77c93.png)
3. ダウンロードしたファイル 2 つを適当な場所に移し、右クリックしてプロパティを選び、セキュリティ欄の `[ ]許可する(K)` にチェックを入れて [ OK ] ボタンをクリックします
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/9f7d11b0-8a2d-e29b-57b6-2148079e8bcd.png)
4. ファイルの拡張子を zip に変えて解凍します
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/32747e60-6e6f-2827-2743-2486fa86a52e.png)

## 前準備 2

仕事のデータを使うわけにもいかないので、手元でデモデータを作ってしまいました
* シートは 2 つで、表紙とデータの表
  - 表紙  
    ![2022-04-30_23h58_42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/4f40d402-539e-5543-0500-c96db4c96dad.png)
  - データ  
    ![2022-05-01_00h01_05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/942205c8-f8c3-2915-cf08-f9a19c8e96d3.png)

## 前準備 3

使用するファイルを同じフォルダに配置、PowerShell ファイルを同フォルダに作成  
私は C ドライブ直下に learn というフォルダを作りました
![2022-05-01_00h50_45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/fa319052-beff-88c0-8271-4edf5d0fb52d.png)

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

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

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

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

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

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

```powershell
# 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` として格納されています
シートの数を取得する
```powershell
PS C:\learn> $data.Tables.Count
2
```

最初のシート名
```powershell
PS C:\learn> $data.Tables[0].TableName
表紙
```

シート内のデータアクセスは Rows で行を、Column で列を表します
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/118961/c317b420-319d-9b36-4f39-0a56811bc0d9.png)

2 番目のシートの行数
```powershell
PS C:\learn> $data.Tables[1].Rows.Count
34
```

2番めのシートの B 列(商品A) 9 行目(1月7日)の値を取得
```powershell
PS C:\learn> $data.Tables[1].Rows[8].Column1
9
# または
PS C:\learn> $data.Tables[1].Rows[8].Item('Column1')
9
```

処理が終わったら最後にオブジェクトを開放しました
```powershell
$reader.Close()
$reader.Dispose()
$reader = $null
$stream.Close()
$stream.Dispose()
$stream = $null
$data.Clear()
$data.Dispose()
$data = $null
```

## Tips な備忘録

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

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

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

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

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

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

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

```powershell
# 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** を読み込んで、表紙以外の各シートの総合計をコンソールに出力する例

```powershell
$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
    }
}
```