経緯
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 をダウンロードして使います
- 公式の GitHub リポジトリの右にある Releases をクリック
- ExcelDataReader.3.6.0.nupkg と ExcelDataReader.DataSet.3.6.0.nupkg をクリックしてダウンロードします
- ダウンロードしたファイル 2 つを適当な場所に移し、右クリックしてプロパティを選び、セキュリティ欄の
[ ]許可する(K)にチェックを入れて [ OK ] ボタンをクリックします
- ファイルの拡張子を 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
}
}