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

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

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

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

この記事の目次

• 準備
• まず実行してみましょう
• ダブルクリックして実行してみる
• カスタマイズのポイント

準備

1. Pythonのインストール

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

Pythonのインストール方法（Windows）

Pythonはいま大ブレイクのプログラミング言語です。その背景には人工知能ブーム、それを支えるディープラーニングのほとんどがPythonで動かしていることにあります。ですが・・・ Pythonは…

 Gammasoft.jp2017-05-22

2.エクセル読み書き用ライブラリ（モジュール）のインストール

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

まず、以下の手順でコマンドプロンプト（黒い背景の画面）を開いてください。

1. 「Windowsキー（）+ r　」を入力すると、以下のような画面が開きます。



2. 「cmd」 と入力して OK をクリックするとコマンドプロンプトが開きます。

py -m pip install openpyxl と入力して、enterを押すと、以下のようにopenpyxl のインストールが開始します。”Successfully installed”（出力の下から3行目）と表示されていたら、インストール成功です。

Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\Users\taro>py -m pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-2.4.1.tar.gz (154kB)
    100% |################################| 163kB 1.3MB/s
Collecting jdcal (from openpyxl)
  Downloading jdcal-1.3.tar.gz
Collecting et-xmlfile (from openpyxl)
  Downloading et_xmlfile-1.0.1.tar.gz
Installing collected packages: jdcal, et-xmlfile, openpyxl
  Running setup.py install for jdcal ... done
  Running setup.py install for et-xmlfile ... done
  Running setup.py install for openpyxl ... done
Successfully installed et-xmlfile-1.0.1 jdcal-1.3 openpyxl-2.4.1
You are using pip version 8.1.1, however version 9.0.1 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.

C:\Users\taro>

pipとは、Pythonのパッケージ管理システムと呼ばれるもので、pipを使うことで、上記のように簡単にライブラリをインストールできます。

上記の出力の最後には、「新しいpipがあるので、アップデートを検討するように」と表示されることがありますが、ここではアップデートせずそのまま進みます。pipに関して詳しいことは以下の記事を参考にしてください。

ライブラリのインストール － pipの使い方

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

 Gammasoft.jp2017-05-22

まず実行してみましょう

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

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

ツールバーのメニューから File ▶ New File を選択すると新しいウィンドウが開きます。そこに、以下のプログラムコードをコピーして貼り付けてください。

import datetime, openpyxl

# エクセルブックファイル
book = openpyxl.load_workbook('members.xlsx', data_only = True)

# エクセルシート
sheet = book.get_sheet_by_name('Sheet1')

idList = []
now = datetime.datetime.now()

# ヘッダーを除いて順番に読み込む
for row in range(2, sheet.max_row + 1):
    # 会員番号
    memberId = sheet['A' + str(row)].value
    # 有効期限
    expireDate = sheet['D' + str(row)].value
    
    # 有効期限内かどうかチェック
    if expireDate >= now:
        idList.append(memberId)
        
# 並び替える
idList.sort()

# テキストファイル
txtFile = open('validmembers.txt', 'w')

# 書き込み
for id in idList:
    txtFile.write(str(id) + '\n')

txtFile.close()

貼り付けたイメージは以下のようになります。ここで、バックスラッシュ（\）は、貼り付けると以下のように（¥）に変わります。

3. コードの保存

ドキュメントフォルダ（Documents）に「programpy」フォルダを作成しておきます（好きな名前でも大丈夫です）。ツールバーのメニューから File ▶ Save を選択して、「memberfilter.py」という名前で、作成したフォルダに保存します。

4. 会員名簿エクセルファイルの準備（サンプルのダウンロード）

簡単なサンプルを用意しましたので、ダウンロードして先ほど作成した「programpy」フォルダにコピーします

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

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

5. プログラムの実行

以下のようにツールバーのメニューから Run ▶ Run Module を選択するとプログラムが実行されます。

6. 結果の確認

以下のように「validmembers.txt」というファイルが先ほどのフォルダに作成されます。

メモ帳で確認すると以下のように会員番号が抽出されているので、有効期限内の会員であるか確認してみてください。

ダブルクリックして実行してみる

Pythonはexeファイルを作らなくても、そのままでどこでも実行できます。これが、非常に便利なところです。

今回のプログラム「memberfilter.py」を、ダブルクリックすると、その時の日時で有効期限内の会員番号を「validmembers.txt」に出力します。

カスタマイズのポイント

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

ケース1：氏名も一緒に出力する

以下のようにカスタマイズすると会員番号と氏名のCSVファイルを出力できます。

import datetime, openpyxl

# エクセルブックファイル
book = openpyxl.load_workbook('members.xlsx', data_only = True)

# エクセルシート
sheet = book.get_sheet_by_name('Sheet1')

idDict = {}
now = datetime.datetime.now()

# ヘッダーを除いて順番に読み込む
for row in range(2, sheet.max_row + 1):
    # 会員番号
    memberId = sheet['A' + str(row)].value

    # 氏名
    name = sheet['B' + str(row)].value
    
    # 有効期限
    expireDate = sheet['D' + str(row)].value
    
    # 有効期限内かどうかチェック
    if expireDate >= now:
        idDict[memberId] = name
        

# テキストファイル
txtFile = open('validmembers.csv', 'w')

# 書き込み
for id in sorted(idDict):
    txtFile.write(str(id) + ',' + idDict.get(id, '') + '\n')

txtFile.close()

ポイントは、idList = [] ではなく、idDict = {} を使うところです。[] はリスト、{} は辞書（ディクショナリ）です。

# リスト
idList = []

# 辞書（ディクショナリ）
idDict = {}

ケース2：有効期限切れが90日以内に迫った会員番号だけ抽出する

import datetime, openpyxl

# エクセルブックファイル
book = openpyxl.load_workbook('members.xlsx', data_only = True)

# エクセルシート
sheet = book.get_sheet_by_name('Sheet1')

idList = []
now = datetime.datetime.now()
prev = now + datetime.timedelta(days=90)

# ヘッダーを除いて順番に読み込む
for row in range(2, sheet.max_row + 1):
    # 会員番号
    memberId = sheet['A' + str(row)].value
    # 有効期限
    expireDate = sheet['D' + str(row)].value
    
    # 有効期限が90日以内に迫っているかどうかチェック
    if expireDate >= now and expireDate <= prev:
        idList.append(memberId)
        
# 並び替える
idList.sort()

# テキストファイル
txtFile = open('validmembers_prev.txt', 'w')

# 書き込み
for id in idList:
    txtFile.write(str(id) + '\n')

txtFile.close()

現在から90日先の日時は以下のように計算します。

prev = now + datetime.timedelta(days=90)

そして、ポイントは有効期限が迫っているかを以下のような条件で判別します。

    if expireDate >= now and expireDate <= prev:
        idList.append(memberId)