GAMMASOFT

Pythonでエクセルの会員名簿から有効期限内の会員番号だけテキストファイルに抽出する

2017/5/22

今回はエクセルの会員名簿ファイルから、有効期限内の会員だけを抽出して、別のテキストファイルに出力する作業をPythonでプログラミングします。

会員制のサービス運営において、ある条件の会員だけを抽出したいケースは良くあります。エクセルのフィルター機能を使えば簡単ですが、名簿ファイルはマスターファイルとしてそのままにしておいて、抽出結果だけを別ファイルに出力する方が管理しやすくなります。

そこで、今回はPythonを使って、エクセルを起動しないでエクセルファイルを読み取り、有効期限内の会員番号だけテキストファイルに抽出します。作業は一瞬で終わります。

この記事の目次

準備（Pythonとライブラリのインストール）

1. Pythonのインストール

以下の記事を参考にWindowsにPythonをインストールしてください。

Pythonのインストール方法（Windows）
Pythonはいま大ブレイクのプログラミング言語です。その背景には人工知能ブーム、それを支えるディープラーニングのほとんどがPythonで動かしていることにあります。ですが・・・ Pythonは…
 Gammasoft.jp2017-05-22

2. ライブラリのインストール

エクセルファイルを読み書きするには、専用のライブラリをインストールする必要があります。今回はオープンソースのOpenPyXL を利用します。

まず、コマンドプロンプト（黒い背景の画面）を開きます。「Windowsキー（）+ r　」を入力すると、以下のような画面が表示されるので、「cmd」 と入力して OK をクリックするとコマンドプロンプトが開きます。

open cmd

コマンドプロンプトにpy -m pip install openpyxl と入力して、Enterを押すと、以下のようにopenpyxl のインストールが開始します。”Successfully installed ~”と表示されたらインストール成功です。

openpyxl install

pipは、Pythonのパッケージを管理するためのツールです。pipを使うことで、上記のように簡単にライブラリをインストールできます。詳しくは以下の記事を参照してください。

Pythonライブラリのインストール － pipの使い方
Pythonには豊富な標準ライブラリが備わっています。システム、ファイルIO、関数、データベース、ウェブ等、非常に多彩です。Pythonが「バッテリー同梱（batteries included）」と呼…
 Gammasoft.jp2017-05-22

プログラミング

早速プログラミングします。以下の手順で実際に作業してみてください。

1. メニューからPythonのIDLEを開きます。

python idle select from menu

2. エディタを開いて、コードを貼り付ける

IDLEツールバーのメニューから File ▶ New File を選択してエディタを開いてください。

idle new editor

そこに、以下のプログラムコードをコピーして貼り付けてください。

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の新しいウィンドウに貼り付けたイメージは以下のようになります。ここで、バックスラッシュ（\）は、貼り付けると以下のように（¥）に変わります。

python idle select from menu

3. コードの保存

IDLEのツールバーのメニューから File ▶ Save を選択して、memberfilter.pyという名前で、適当なフォルダに保存します。

サンプルファイル

以下のリンクから、サンプルの会員名簿ファイルをダウンロードしたら解凍して、プログラムと同じフォルダにコピーします

エクセルファイルのサンプル（members.zip）ダウンロード

ファイル（members.xlsx）の中身は、以下のようになっています。

members spreadsheet

プログラム実行

WindowsにPythonをインストールすると、拡張子がpyのPythonファイルはダブルクリックで実行できますので、memberfilter.pyをダブルクリックしてください。

以下のようなvalidmembers.txtというファイルが、プログラムと同じフォルダに作成されます。

python idle select from menu

有効期限内の会員が正しく抽出されているかメモ帳で確認してみてください（リストされる会員はプログラムを実行した日時で異なります）。

その他の実行方法

以下のようにIDLEのツールバーのメニューから Run ▶ Run Module を選択してもプログラムを実行できます。

python idle select from menu

カスタマイズ例

今回のコードをベースにいくつかのカスタマイズ例を考えてみます。

ケース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形式で保存されます（リストされる会員はプログラムを実行した日時で異なります）。

member 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日以内に迫った会員がリストされます（リストされる会員はプログラムを実行した日時で異なります）。