今回はエクセルの会員名簿ファイルから、有効期限内の会員だけを抽出して、別のテキストファイルに出力する作業をPythonでプログラミングします。
会員制のサービス運営において、ある条件の会員だけを抽出したいケースは良くあります。エクセルのフィルター機能を使えば簡単ですが、名簿ファイルはマスターファイルとしてそのままにしておいて、抽出結果だけを別ファイルに出力する方が管理しやすくなります。
そこで、今回はPythonを使って、エクセルを起動しないでエクセルファイルを読み取り、有効期限内の会員番号だけテキストファイルに抽出します。作業は一瞬で終わります。
この記事の目次
準備（Pythonとライブラリのインストール）
1. Pythonのインストール
以下の記事を参考にWindowsにPythonをインストールしてください。
Gammasoft.jp2017-05-222. ライブラリのインストール
エクセルファイルを読み書きするには、専用のライブラリをインストールする必要があります。今回はオープンソースのOpenPyXL を利用します。
まず、コマンドプロンプト（黒い背景の画面）を開きます。「Windowsキー（）+ r 」を入力すると、以下のような画面が表示されるので、「cmd」 と入力して OK をクリックするとコマンドプロンプトが開きます。
コマンドプロンプトにpy -m pip install openpyxl と入力して、Enterを押すと、以下のようにopenpyxl のインストールが開始します。”Successfully installed ~”と表示されたらインストール成功です。
pipは、Pythonのパッケージを管理するためのツールです。pipを使うことで、上記のように簡単にライブラリをインストールできます。詳しくは以下の記事を参照してください。
Gammasoft.jp2017-05-22プログラミング
早速プログラミングします。以下の手順で実際に作業してみてください。
1. メニューからPythonのIDLEを開きます。
2. エディタを開いて、コードを貼り付ける
IDLEツールバーのメニューから File ▶ New File を選択してエディタを開いてください。
そこに、以下のプログラムコードをコピーして貼り付けてください。
import datetime
import openpyxl
xlsx_file = "members.xlsx"
text_file = "validmembers.txt"
id_list = []
now = datetime.datetime.now()
# エクセルブック
wb = openpyxl.load_workbook(xlsx_file, data_only=True)
# エクセルシート
ws = wb["Sheet1"]
# ヘッダーを除いて順番に読み込む
for row in ws.iter_rows(min_row=2):
# 会員番号
member_id = row[0].value
# 有効期限
expire_date = row[3].value
# 有効期限内かどうかチェック
if expire_date >= now:
id_list.append(member_id)
# 並び替える
id_list.sort()
# テキストファイル書き込み
with open(text_file, "w") as f:
for i in id_list:
f.write(str(i) + "\n")
IDLEの新しいウィンドウに貼り付けたイメージは以下のようになります。ここで、バックスラッシュ（\）は、貼り付けると以下のように（¥）に変わります。
3. コードの保存
IDLEのツールバーのメニューから File ▶ Save を選択して、memberfilter.pyという名前で、適当なフォルダに保存します。
サンプルファイル
以下のリンクから、サンプルの会員名簿ファイルをダウンロードしたら解凍して、プログラムと同じフォルダにコピーします
エクセルファイルのサンプル（members.zip）ダウンロード
ファイル（members.xlsx）の中身は、以下のようになっています。
プログラム実行
WindowsにPythonをインストールすると、拡張子がpyのPythonファイルはダブルクリックで実行できますので、memberfilter.pyをダブルクリックしてください。
以下のようなvalidmembers.txtというファイルが、プログラムと同じフォルダに作成されます。
有効期限内の会員が正しく抽出されているかメモ帳で確認してみてください（リストされる会員はプログラムを実行した日時で異なります）。
その他の実行方法
以下のようにIDLEのツールバーのメニューから Run ▶ Run Module を選択してもプログラムを実行できます。
カスタマイズ例
今回のコードをベースにいくつかのカスタマイズ例を考えてみます。
ケース1：氏名も一緒に出力する
以下のようにカスタマイズすると会員番号と氏名のCSVファイルを出力できます。
import datetime
import openpyxl
xlsx_file = "members.xlsx"
text_file = "validmembers.txt"
id_list = []
now = datetime.datetime.now()
# エクセルブック
wb = openpyxl.load_workbook(xlsx_file, data_only=True)
# エクセルシート
ws = wb["Sheet1"]
# ヘッダーを除いて順番に読み込む
for row in ws.iter_rows(min_row=2):
# 会員番号
member_id = row[0].value
# 氏名
member_name = row[1].value
# 有効期限
expire_date = row[3].value
# 有効期限内かどうかチェック
if expire_date >= now:
id_list.append([member_id, member_name])
# 並び替える
id_list.sort()
# テキストファイル書き込み
with open(text_file, "w") as f:
for i in id_list:
f.write(str(i[0]) + "," + i[1] + "\n")
実行すると以下のように有効期限内の会員番号と氏名がCSV形式で保存されます（リストされる会員はプログラムを実行した日時で異なります）。
ケース2：有効期限切れが180日以内に迫った会員番号だけ抽出する
import datetime
import openpyxl
xlsx_file = "members.xlsx"
text_file = "validmembers.txt"
id_list = []
now = datetime.datetime.now()
prev = now + datetime.timedelta(days=180)
# エクセルブック
wb = openpyxl.load_workbook(xlsx_file, data_only=True)
# エクセルシート
ws = wb["Sheet1"]
# ヘッダーを除いて順番に読み込む
for row in ws.iter_rows(min_row=2):
# 会員番号
member_id = row[0].value
# 氏名
member_name = row[1].value
# 有効期限
expire_date = row[3].value
# 有効期限が180日以内に迫っているかどうかチェック
if now <= expire_date <= prev:
id_list.append([member_id, member_name])
# 並び替える
id_list.sort()
# テキストファイル書き込み
with open(text_file, "w") as f:
for i in id_list:
f.write(str(i[0]) + "," + i[1] + "\n")
現在から180日先の日時は以下のように計算します。
prev = now + datetime.timedelta(days=180)有効期限が迫っているかを判定する条件式は、Pythonでは以下のように記述できます。
# 他の言語でよくある書き方
if expire_date >= now and expire_date <= prev:
# Pythonでは以下のように記述できます。
if now <= expire_date <= prev:
実行すると以下のように有効期限が180日以内に迫った会員がリストされます（リストされる会員はプログラムを実行した日時で異なります）。
