エクセルファイルのデータをPowerShellに取り込む

はじめに

xls 形式のファイルは xlsx 形式で保存し直してください。 Windows10 以上で動きます。

成果物

Import-XlFile.ps1

Function Import-XlFile {
    Param(
        [Parameter(Mandatory=$True)]
        [string]$Path,
    
        [parameter(Mandatory=$False)]
        [string[]]$SheetName,
    
        [parameter(Mandatory=$False)]
        [switch]$ActiveSheet
    )
    
    $ErrorActionPreference = "STOP"
    
    $temp = $Env:Temp
    $xlsx = Get-Item -Path $Path
    $base = Join-Path $temp $xlsx.BaseName
    $zip = $base + ".zip"
    
    Copy-Item $xlsx $zip -Force
    Expand-Archive $zip -Destination $base -Force
    
    $workbook = [xml](Get-Content -Encoding UTF8 $base\xl\workbook.xml)
    $styles = [xml](Get-Content -Encoding UTF8 $base\xl\styles.xml)
    $sharedStrings = [xml](Get-Content -Encoding UTF8 $base\xl\sharedStrings.xml)
    
    $sheets = @{}
    $workbook.workbook.sheets.sheet | ForEach-Object {
        $xml = "$base\xl\worksheets\" + $_.Id.replace("rId","sheet") + ".xml"
        $sheets.add($_.name, $xml)
    }
    
    $SheetNames = @()
    if ($SheetName.Count -ne 0) {
        $SheetNames = $SheetName
    }
    
    if ($null -eq $SheetName -and -not $ActiveSheet) {
        $SheetNames += $workbook.workbook.sheets.sheet.Name
    }
    
    if ($ActiveSheet) {
        $activeIndex = $workbook.workbook.bookViews.workbookView.activeTab
        $activeTab = $workbook.workbook.sheets.sheet.Name[$activeIndex]
        if(-not $SheetNames.Contains($activeTab)) {
            $SheetNames += $activeTab
        }
    }
    
    $worksheets = @{}
    $SheetNames | ForEach-Object {
        $sn = $_
        $sheetData = [xml](Get-Content -Encoding UTF8 $sheets[$sn])
        $data = $sheetData.worksheet.sheetData.row | Where-Object {
            # for empty sheet
            $_
        } | ForEach-Object {
            $r = $_
            $row = New-Object psobject
            $r.c | ForEach-Object {
                $c = $_

                $address = $c.r -replace '[0-9]'
                $fmtId = $c.s | Where-Object { $_ } | ForEach-Object {
                    [int]$styles.styleSheet.cellXfs.xf[$c.s].numFmtId
                }
                $isDate = $fmtId | ForEach-Object {
                    if (13 -lt $_ -and $_ -lt 37) { return $true }
                    if (44 -lt $_ -and $_ -lt 48) { return $true }
                    if (49 -lt $_ -and $_ -lt 59) { return $true }
                    return $false
                }

                if ($c.t -eq "s") {
                    $value = $sharedStrings.sst.si.t[$c.v]
                } elseif ($isDate) {
                    [double]$serial = $c.v
                    if ($serial -ge 1) {
                        $dateSerial = [Math]::Floor($serial)
                        $date = ([datetime]"1900/1/1").AddDays($dateSerial - 2)
                        $timeSerial = $serial - $dateSerial
                    } else {
                        $date = [datetime]"1900/1/1"
                        $timeSerial = $serial
                    }
                    $value = $date.AddSeconds($timeSerial * 24 * 3600)
                } else {
                    $value = $c.v
                }

                # other?
                $row | Add-Member -Force -MemberType Noteproperty -Name $address -Value $value
            }
            return $row
        }
        $worksheets.add($sn, $data);
    }
    Remove-Item -Force -Recurse $base, $zip
    return $worksheets
}

使い方

. .\Import-XlFile.ps1
$worksheets = Import-XlFile -Path .\hogehoge\xlsx.xlsx -SheetName hoge, fuga, piyo
$worksheets
<#
Name          Value
-----         ------
hoge          {@{A=...; B=...}...}
fuga          {@{A=...; B=...}...}
piyo          {@{A=...; B=...}...}
# >

# シートデータの参照
$worksheets.hoge

おわりに

PowerShell v5(Windows10) で ZIP 展開が標準コマンドで実行できるようになったので、外部コマンドに頼らず COM も使わずにエクセルのデータを取り込むことができるようになった。以前までのやり方よりも依存関係が少なくなり、すっきりした。

自力で XML の解析を行ったことで Excel のシリアル値の流派が別れた理由を知ることができた。

免責

ちゃんとしたテストはやってません。
事務作業に利用しようと思っている程度なので大きな問題にはならないはずです。
文字列が入力されたセルが 1 つしかない場合は PowerShell のよくある問題により誤作動します。

参考資料

https://qiita.com/weal/items/f1d7690e60882d38d7d2
https://qiita.com/wagase/items/6444ce93a24aaaa74981

ImportExcel

追記です。ライブラリがあるというのを教えて頂きました
https://www.powershellgallery.com/packages/ImportExcel/