背景

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

完成の動き

データ

↓コピペ用

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

なぜwin32comか

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

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

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

コード

ワークブックの取得

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)

シートの取得

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

それでも新しいヘッダーが入ってしまったので公式サイトを参考に
XlListObjectHasHeaders,LinkSourceの設定をいじっていたら
1行目がヘッダー判定されました。

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 = "プルダウン"  # テーブルの名前を設定

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

地域のプルダウンの設定

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

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

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列にプルダウン設定ができます。

データの追加

次の処理がデータがないと動かないので追加します。

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を入れています。

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

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

元の値には次の情報が入っています。

=INDIRECT("プルダウン["&A3&"]")

全コード

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

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大変！