エクセルで作られた進捗管理表がPowerShellで扱いにくい問題

# はじめに

進捗管理をエクセルでやるとき、以下のような形にしてしまいがちです。苦しいですね。

|A   |B   |C   |D   |E   |F   |G   |H   |
|:---|:---|:---|:---|:---|:---|:---|:---|
|年度   |   |    |主担当|副担当|予定|実績|進捗率|
|2020年||||||||
|   |第一四半期|||||||
|   |   |喧嘩|猿|犬|2020/4/10|2020/4/8|100%|
|   |   |バーゲン・セール|猫|小判|2020/5/15|2020/5/15|100%|
|   |   |かけっこ|亀|兎|2020/6/20||90%|
|   |第二四半期|    |    |    ||||
|   |   |おしゃれ|豚|真珠|2020/7/10||90%|
|   |   |葬式|馬|念仏|2020/8/15|||
|   |   |散歩|犬|棒|2020/9/20|||

このような表は、小規模なうちは比較的分かりやすい形ですが、規模が大きくなってきたり、複数の表を横断的に見る必要がある時に、見落としなど管理不備が発生しやすくなります。機械的に集約できたり抽出できると管理コストは下がるのですが、このままの形式では機械的に扱うことが困難です。

また、エクセル特有の問題として、割と頻繁にフォーマットが変更されたりします。そのため、プロジェクト内外に似たようなフォーマットの「亜種」が散乱していることがあったりします。つまり、ここで言う「機械的」な処理は、かなり柔軟な変更耐性が求められます。

この記事では、このような辛い表を機械的に扱うための手法を提案します。

# 理想の形

以下のようにすると多少扱いやすくなります。A 列と B 列を下までオートフィル（？）した形です。情報が一行にまとまっています。
「2020年度」しか無い行は要らなくなりましたが、一旦弄らないことにします。このようなデータは PowerShell でフィルタするのは簡単だからです。

|A   |B   |C   |D   |E   |F   |G   |H   |
|:---|:---|:---|:---|:---|:---|:---|:---|
|年度   |   |    |主担当|副担当|予定|実績|進捗率|
|2020年||||||||
|2020年|第一四半期|||||||
|2020年|第一四半期|喧嘩|猿|犬|2020/4/10|2020/4/8|100%|
|2020年|第一四半期|バーゲン・セール|猫|小判|2020/5/15|2020/5/15|100%|
|2020年|第一四半期|かけっこ|亀|兎|2020/6/20||90%|
|2020年|第二四半期|    |    |    ||||
|2020年|第二四半期|おしゃれ|豚|真珠|2020/7/10||90%|
|2020年|第二四半期|葬式|馬|念仏|2020/8/15|||
|2020年|第二四半期|散歩|犬|棒|2020/9/20|||


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

では、早速エクセルのデータを取り込んでいきます。以下のようにします。

```powershell
Import-XlFile -path hoge.xlsx | set xlsx -passthru

Name                           Value
----                           -----
Sheet1                         {@{A=年度; D=主担当; E=副担当; F=予定; G=実績; H=進捗率}, @{A=2020年}, @{B=第一四半期}, @{C=喧嘩; D=猿; E=犬; F=43931; G=43929; H=1}...}
```


`import-xlfile` については以下の記事で書いてます。
[エクセルファイルのデータをPowerShellに取り込む - Qiita](https://qiita.com/asterisk9101/items/de0a76d8b48384c02cda)

```powershell
$xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | ft

A      B          C                D      E      F                  G                  H                   I J
-      -          -                -      -      -                  -                  -                   - -
年度                               主担当 副担当 予定               実績               進捗率
2020年
       第一四半期
                  喧嘩             猿     犬     2020/04/10 0:00:00 2020/04/08 0:00:00 1899/12/31 0:00:00
                  バーゲン・セール 猫     小判   2020/05/15 0:00:00 2020/05/15 0:00:00 1899/12/31 0:00:00
                  かけっこ         亀     兎     2020/06/20 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
       第二四半期                                1900/01/01 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
                  おしゃれ         豚     真珠   2020/07/10 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
                  葬式             馬     念仏   2020/08/15 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
                  散歩             犬     棒     2020/09/20 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00

```

`new-xlcolumnrange` については以下の記事で書いてます。
[エクセルのアドレス形式A:Eを入力するとき列名A,B,C,D,Eを出力する - Qiita](https://qiita.com/asterisk9101/items/aa14eab9b8cce18448ec)


# 1行目を見出しに昇格する

列名がアルファベットになっているとアレなので以下のようにします。

```powershell
$xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | Convert-XlHeader | ft

年度   C1         C2               主担当 副担当 予定               実績               進捗率              C3 C4
----   --         --               ------ ------ ----               ----               ------              -- --
2020年
       第一四半期
                  喧嘩             猿     犬     2020/04/10 0:00:00 2020/04/08 0:00:00 1899/12/31 0:00:00
                  バーゲン・セール 猫     小判   2020/05/15 0:00:00 2020/05/15 0:00:00 1899/12/31 0:00:00
                  かけっこ         亀     兎     2020/06/20 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
       第二四半期                                1900/01/01 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
                  おしゃれ         豚     真珠   2020/07/10 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
                  葬式             馬     念仏   2020/08/15 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
                  散歩             犬     棒     2020/09/20 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
```

`Convert-XlHeader` は以下のような処理です。

```powershell:Convert-XlHeader.ps1
Function Convert-XlHeader {
    Param(
        [Parameter(Mandatory=$True, ValueFrompipeline=$True)]
        [psobject[]]$SheetData
    )
    begin {
        $ErrorActionPreference = "Stop"
        $index = 1
    }
    process {
        $SheetData | Foreach-Object {
            if (-not $Header) {
                $Header = $_ | Select-Object *
                return
            }

            $o = new-object psobject
            $row = $_
            $row.psobject.properties | Foreach-Object {
                $n = $_.Name
                if (-not $Header.$n) {
                    $Header | Add-Member -Force -MemberType NoteProperty -Name $n -Value "C$index"
                    $index++
                }
                $o | Add-Member -MemberType NoteProperty -Name $header.$n -Value $row.$n
            }
            $o # output
        }
    }
}
```

# 列を埋める

というわけで、列を埋めます。

```powershell
$xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | Convert-XlHeader | Expand-XlHierarchicalData -Column "年度",C1 | ft     

年度   C1         C2               主担当 副担当 予定               実績               進捗率              C3 C4
----   --         --               ------ ------ ----               ----               ------              -- --
2020年
2020年 第一四半期
2020年 第一四半期 喧嘩             猿     犬     2020/04/10 0:00:00 2020/04/08 0:00:00 1899/12/31 0:00:00
2020年 第一四半期 バーゲン・セール 猫     小判   2020/05/15 0:00:00 2020/05/15 0:00:00 1899/12/31 0:00:00
2020年 第一四半期 かけっこ         亀     兎     2020/06/20 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
2020年 第二四半期                                1900/01/01 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
2020年 第二四半期 おしゃれ         豚     真珠   2020/07/10 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
2020年 第二四半期 葬式             馬     念仏   2020/08/15 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
2020年 第二四半期 散歩             犬     棒     2020/09/20 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
```

`Expand-XlHierarchicalData` については以下の通り。名前が直訳すぎて恥ずかしいけど面倒なのでそのまま。

```powershell:Expand-XlHierarchicalData.ps1
Function Expand-XlHierarchicalData {
    Param(
        [Parameter(Mandatory=$True, ValueFromPipeline=$True)]
        [psobject[]]$SheetData,

        [Parameter(Mandatory=$False)]
        [string[]]$Column
    )
    begin {
        $ErrorActionPreference = "Stop"
        $o = $null
        $add = {
            $name = $_.Name
            if ($o.psobject.properties.name -contains $name) {
                $o.$name = $_.value
            } else {
                $o | Add-Member -MemberType NoteProperty -Name $name -Value $_.value
            }
        }
    }
    process {
        foreach($data in $SheetData) {
            $Data | Foreach-Object {
                # initialize
                if ($null -eq $o) {
                    $o = $_ | Select-Object *
                }
                if ($null -eq $col) {
                    if ($null -eq $column) {
                        $col = $_.psobject.properties.name
                    } else {
                        $col = $column
                    }
                }

                # copy properties(overwrite)
                $_.psobject.properties | Where-Object { $_.Name -notin $Col } | ForEach-Object $add

                # copy properties(inherit)
                $inherit = $true
                $_.psobject.properties | Where-Object { $_.Name -in $Col } | Where-Object {
                    if (-not $inherit) { return $true }
                    if ($null -eq $_.value) { return $false }
                    $inherit = $false
                    return $true
                } | ForEach-Object $add

                # output / psobject clone
                $o | Select-Object *
            }
        }
    }
}
```

# 後処理

やりたいことはもう終わりましたが、列を集約する方法も覚書程度にまとめておきます。

```powershell
$list = $xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | Convert-XlHeader | Expand-XlHierarchicalData -Column 年度,C1
$list | select-object @{N="作業項目"; E={ $_.年度,$_.C1,$_.C2 -join "\" -replace "\\+","\" }},主担当,副担当,予定,実績,進捗率 | ft

作業項目                           主担当 副担当 予定               実績               進捗率
--------                           ------ ------ ----               ----               ------
2020年\
2020年\第一四半期\
2020年\第一四半期\喧嘩             猿     犬     2020/04/10 0:00:00 2020/04/08 0:00:00 1
2020年\第一四半期\バーゲン・セール 猫     小判   2020/05/15 0:00:00 2020/05/15 0:00:00 1
2020年\第一四半期\かけっこ         亀     兎     2020/06/20 0:00:00 1900/01/01 0:00:00 0.9
2020年\第二四半期\                               1900/01/01 0:00:00 1900/01/01 0:00:00
2020年\第二四半期\おしゃれ         豚     真珠   2020/07/10 0:00:00 1900/01/01 0:00:00 0.9
2020年\第二四半期\葬式             馬     念仏   2020/08/15 0:00:00 1900/01/01 0:00:00
2020年\第二四半期\散歩             犬     棒     2020/09/20 0:00:00 1900/01/01 0:00:00
```

# 終わりに

色々なデータに適用して実用性を確認したいと思います。
大事なのは、この後どのように集約するかですが果たして。。。