Python（win32com）で2段階ドロップダウンリスト作成

# 背景
システムから出力されるExcelデータで
ドロップダウンが連動させる仕組みを作りたくて調べました。


# 完成の動き
![2024-07-07_18h15_35.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/7ce8b18f-5c47-a0e3-8c70-fea53be9417f.gif)


# データ

![2024-07-07_16h40_32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/2243e87f-d2ac-d5a4-e582-fafbee1c8202.png)

![2024-07-07_16h37_57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/62e8ed95-497e-8a75-12e3-727d84afd39a.png)


↓コピペ用


| 北海道 | 東北 | 関東 | 中部 | 近畿 | 中国 | 四国 | 九州 |
| --- | --- | --- | --- | --- | --- | --- | --- |
|北海道  | 青森県 | 茨城県 | 新潟県 | 三重県 | 鳥取県 | 徳島県 | 福岡県 |
|  | 岩手県 | 栃木県 | 富山県 | 滋賀県 | 島根県 | 香川県 | 佐賀県 |
|  | 宮城県 | 群馬県 | 石川県 | 京都府 | 岡山県 | 愛媛県 | 長崎県 |
|  | 秋田県 | 埼玉県 | 福井県 | 大阪府 | 広島県 | 高知県 | 熊本県 |
|  | 山形県 | 千葉県 | 山梨県 | 兵庫県 | 山口県 |  | 大分県 |
|  | 福島県 | 東京都 | 長野県 | 奈良県 |  |  | 宮崎県 |
|  |  | 神奈川県 | 岐阜県 | 和歌山県 |  |  | 鹿児島県 |
|  |  |  | 静岡県 |  |  |  | 沖縄県 |
|  |  |  | 愛知県 |  |  |  |  |


# なぜwin32comか

詳しい話はこちらを参考にさせてもらいました。
https://qiita.com/kumarstack55/items/9ae3432446afca06497f

最初はopenpyxlで実施しようと思いましたが
ドロップダウンリスト作成で躓いてwin32comに切り替えました。

win32comはほぼVBAの設定と近くてわからない場合は
VBAの公式サイトを調べるのが良いみたいです。
実際にわからない場合はVBAのブログを探して一部適当に試して実装しました。


# コード

## ワークブックの取得

```py
from pathlib import Path
import win32com.client

# Excelのインスタンスを作成する
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = True  # Excelを表示する

# 開く
abspath = str(Path(r"export.xlsx").resolve())
workbook = excel.Workbooks.Open(abspath, UpdateLinks=0, ReadOnly=False)
```

## シートの取得

```py
pulldown_sheet= workbook.Worksheets("プルダウン")
data_sheet= workbook.Worksheets("データ")
```

## テーブルの作成

データが入っている最終行・列の取得に少し調べました。
こちらのS5の内容を参考に最終行・列を作成しています。
https://www.niji.or.jp/home/toru/notes/8.html

新しいヘッダーを作らないためには`Source`と`Destination`は同じ範囲にする必要があるらしい。
https://stackoverflow.com/questions/43645854/listobject-contains-extra-headers-after-adding

それでも新しいヘッダーが入ってしまったので[公式サイト](https://learn.microsoft.com/ja-jp/office/vba/api/excel.listobjects.add)を参考に
`XlListObjectHasHeaders`,`LinkSource`の設定をいじっていたら
1行目がヘッダー判定されました。


```py
max_row = pulldown_sheet.UsedRange.Rows.Count
max_column = pulldown_sheet.UsedRange.Columns.Count
table_range = pulldown_sheet.Range(pulldown_sheet.Cells(1, 1), pulldown_sheet.Cells(max_row, max_column))
table = pulldown_sheet.ListObjects.Add(
    Source=table_range,
    Destination=table_range,
    XlListObjectHasHeaders=True,
    LinkSource=False
)
table.name = "プルダウン"  # テーブルの名前を設定
```

実行で次の状態になります。

![2024-07-07_17h20_18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/d0e81a4d-bd78-44a0-30a8-917562d58fb1.png)


## 地域のプルダウンの設定

Typeは数値で指定する必要があり番号はこちらで確認します。
https://learn.microsoft.com/ja-jp/dotnet/api/microsoft.office.interop.excel.xldvtype?view=excel-pia

今回は3のリストを選択しています。


```py
data_sheet_max_row = data_sheet.Rows.Count
data_sheet.Range(f"A2:A{max_row}").Validation.Add(
    Type=3,
    AlertStyle=1,
    Operator=2,  # ドロップダウンリストにチェック
    Formula1="=プルダウン!$A$1:$H$1"
)
```

コード実行でデータシートのA列にプルダウン設定ができます。
![2024-07-07_17h40_26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/c68f944c-da45-f154-7760-f4fc3be7ae9a.png)


## データの追加

次の処理がデータがないと動かないので追加します。
```py
data_sheet.Range("A2").Value = '東北'
data_sheet.Range("A3").Value = '東北'
data_sheet.Range("A4").Value = '関東'
data_sheet.Range("A5").Value = '近畿'
data_sheet.Range("A6").Value = '中部'
data_sheet.Range("A7").Value = '四国'
```


## 2段階目のドロップダウンリスト作成

A列に対象の項目名が入っていないとエラーになってしまうのでtryを入れています。

```py
for row in range(2, data_sheet.UsedRange.Rows.Count):
    try:
        data_sheet.Range(f"B{row}").Validation.Add(
            Type=3,
            AlertStyle=1,
            Operator=1,
            Formula1=f'=INDIRECT("プルダウン[\"&A{row}&\"]")'
        )
    except:
        pass
```

実行するとこのような形で設定ができています。

![2024-07-07_17h52_22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/0dadf6dd-a806-7fec-d752-843b41582fba.png)


元の値には次の情報が入っています。
```
=INDIRECT("プルダウン["&A3&"]")
```

![2024-07-07_17h54_55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/428239/78830b98-61cd-309f-dc6e-8b263dfaf028.png)


# 全コード

最後に保存とExcelを閉じる処理をいれています。

```py
from pathlib import Path
import win32com.client

# Excelのインスタンスを作成する
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = False  # Excelを表示する

# 開く
abspath = str(Path(r"export.xlsx").resolve())
workbook = excel.Workbooks.Open(abspath, UpdateLinks=0, ReadOnly=False)


pulldown_sheet= workbook.Worksheets("プルダウン")
data_sheet= workbook.Worksheets("データ")

max_row = pulldown_sheet.UsedRange.Rows.Count
max_column = pulldown_sheet.UsedRange.Columns.Count
table_range = pulldown_sheet.Range(pulldown_sheet.Cells(1, 1), pulldown_sheet.Cells(max_row, max_column))
table = pulldown_sheet.ListObjects.Add(
    Source=table_range,
    Destination=table_range,
    XlListObjectHasHeaders=True,
    LinkSource=False
)
table.name = "プルダウン"  # テーブルの名前を設定



data_sheet_max_row = data_sheet.Rows.Count
data_sheet.Range(f"A2:A{max_row}").Validation.Add(
    Type=3,
    AlertStyle=1,
    Operator=2,  # ドロップダウンリストにチェック
    Formula1="=プルダウン!$A$1:$H$1"
)

# データ追加
data_sheet.Range("A2").Value = '東北'
data_sheet.Range("A3").Value = '東北'
data_sheet.Range("A4").Value = '関東'
data_sheet.Range("A5").Value = '近畿'
data_sheet.Range("A6").Value = '中部'
data_sheet.Range("A7").Value = '四国'


for row in range(2, data_sheet.UsedRange.Rows.Count):
    try:
        data_sheet.Range(f"B{row}").Validation.Add(
            Type=3,
            AlertStyle=1,
            Operator=1,
            Formula1=f'=INDIRECT("プルダウン[\"&A{row}&\"]")'
        )
    except:
        pass

workbook.SaveAs(Filename="プルダウン追加.xlsx")
excel.quit()
```

# 最後に

備忘録として記載しました。
VBAの公式サイトは（おそらく）初めて情報検索に使いましたが
情報探しにくさがわかりました。

VBAer大変！