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

Pythonで仕事自動化:第2回の課題

エクセル形式のファイル(*.xlsx)を読み取り、会員名簿から有効期限内の会員番号だけテキストファイルに出力する。

members spreadsheet

会員制のサービス運営において、名簿管理はやはりエクセルが便利です。ある条件の会員だけを振り分ける場合は、フィルターを使えば簡単にできます。そして、その結果を別のファイルに出力することも多いと思います。

しかし、毎回同じ条件で振り分ける場合は、そのたびにエクセルを開いて、フィルターをかけて、コピペして、別ファイルに保存して、という作業を繰り返さなければなりません。面倒です。マクロを使うこともできますが、エクセルは開かないといけません。

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

この記事の目次

準備

1. Pythonのインストール

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

PythonをWindowsにインストールしてプログラミング学習を始める(ガンマソフト株式会社)
https://gammasoft.jp/python/python-install-on-windows/

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

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

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

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

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

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

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

C:\Users\taro>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があるので、アップデートを検討するように」と表示されることがありますが、ここではアップデートせずそのまま進みます。

まず実行してみましょう

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

python idle select from menu

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()

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

python idle select from menu

3. コードの保存

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

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

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

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

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

members spreadsheet

5. プログラムの実行

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

python idle select from menu

6. 結果の確認

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

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

python idle select from menu

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

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)