武術大会の演目名簿を自動作成してほしいという依頼がありました
身内から突然相談がありました。
少しだけ構えつつ、作業していたパソコンをのぞきましたところ、そこにはExcelで作られた大会参加者名簿と会員名簿に大量のVLOOKUP関数を設定をして複数のシートを関連付けているようでした。ただいくら設定しても思ったように動かないので、手段は問わないので自動編集したものを出力する仕組みを作ってくれと頼まれました。
将来性の拡張を見据えつつ、希望納期は1日後という要求でした。典型的な身内のむちゃぶりです。
◇ 要求定義をする
まずは要求仕様のヒアリングです。近々武術団体の大会があり、これに向けて大会参加者名簿を作成しているようです。この大会参加者名簿には当日「誰が」、「どの演目に参加するか」をまとめていました。これに加えて集団演目であるため、参加される方々の配置を考えるための一覧表が必要なんだそうです。具体的には前列の参加者はその演目を中心にやっている教室を受講している方々を優先したいそうです。
このほかに会員名簿には「各種個人情報」と「受講している教室」が一覧にまとめられていました。
つまり大会参加者名簿と会員名簿の情報を合体させたものを用意し、別途「演目名」と「その演目を中心にやっている教室名」を記入した検索条件を設ければ情報を特定できると考えました。
以上の結果から、このようなシステムを提案しました。
検索条件、大会参加者名簿、会員名簿をまとめたExcelファイルを作成する
Excelファイルを読み込み、大会参加者名簿と会員名簿をデータベースにする
Excelファイルの検索条件からデータベース内で該当する情報を取得する
該当する情報を新しいExcelタブを作って一覧にまとめ、これを保存する
◇ 要求仕様をシステムにする
前回の記事で作成したものは自分のために作ったものでしたので丁寧に設計から行いましたが、今回は要求定義から一気にコードに落としてしまいます。
1. 検索条件、大会参加者名簿、会員名簿をまとめたExcelファイルを作成する
大会参加者名簿と会員名簿はいま作成しているExcelファイルをそのまま取り込めなければシステムにする意味がないとまで断言されてしまいましたので、大会参加者名簿と会員名簿は手を付けずにExcelファイル内に検索条件のタブを新たに設けました。本当に普段からB2C、C2Cで活動されている方には頭が下がります。
$$
\begin{array}{l:l}
種目 & クラス \\ \hline
AAA & AA
\end{array}
\normalsize
$$
既に作成された大会参加者名簿は以下のとおりです。種目数は大会によって異なるということです。
$$
\scriptsize
\begin{array}{l:l:l:l:l:l:l:l:l}
氏名 & 種目 & & & & & & & \\
& AAA & BBB & CCC & DDD & EEE & FFF & GGG & HHH \\ \hline
参加者A & ○ & & & ○ & & & & \\
参加者B & ○ & ○ & & & & & & \\
参加者D & ○ & & & ○ & & & &
\end{array}
\normalsize
$$
既に作成された会員名簿は以下のとおりです。クラスは増減しているということです。
$$
\scriptsize
\begin{array}{l:l:l:l:l:l:l:l:l:l:l:l}
氏名 & 生年月日 & 住所 & 自宅電話 & 携帯電話 & クラス & & & & & & \\ & & & & & AA & BB & CC & DD & EE & FF & GG \\ \hline
参加者A & 2022/5/18 & XXX & XX-XX-XX & XX-XX-XX & ○ & & & & & & \\ 参加者C & 2022/5/18 & XXX & XX-XX-XX & XX-XX-XX & ○ & & & & & & \\ 参加者D & 2022/5/18 & XXX & XX-XX-XX & XX-XX-XX & & ○ & & & & &
\end{array}
\normalsize
$$
2. Excelファイルを読み込み、大会参加者名簿と会員名簿をデータベースにする
Excelファイルを開いて、該当データを抜き出し、これを編集したものをデータベースにしたいと思います。pythonにはExcelファイルを操作できるopenpyxlとデータベースを作成するsqlite3がありますので今回もPythonで作成したいと思います。
sqlite3は標準インストールされておりますが、openpyxlはインストールが必要です。Linuxであればターミナル、Windowsであればコマンドプロンプトかパワーシェルから以下のコマンドでインストールしてください。
pip install openpyxlまずはExcelファイルを取り込むデータベースを定義します。データベース名はmember.dbとします。member.dbの中に「personal」という会員名簿を保存したテーブルと「taikai」という大会参加者名簿を保存したテーブルを設定します。
各テーブルにはどのようなデータ項目があるのか、データ型はどのようなものか、入力が必須であるか、入力がない場合の値は何であるかなどを一つずつ決めていきます。今回の場合には会員名簿と大会参加者名簿の関連付けを氏名(name)で行いますので、この項目だけは入力必須として、あとは入力を任意としました。
ユーザーはExcelファイルを更新してプログラムを実行するので今回のデータベースはデータ保持が目的ではなく、データベースにした情報から該当情報を抜き出すために一時的にデータベースにしていると言えます。
「それならデータベースにしなくても。。。」
次にいきましょう。
import sqlite3 # sqliteのインポート
conn = sqlite3.connect('member.db') # member.dbを作成する。既に存在すればそちらにアクセスする
cur = conn.cursor() # sqliteを操作するオブジェクトを作成する
cur.execute("DROP TABLE IF EXISTS personal") # member.dbにpersonalテーブルがあれば削除する
cur.execute("DROP TABLE IF EXISTS taikai") # member.dbにtaikaiテーブルがあれば削除する
# personalテーブルの中身を定義する。全部で6項目で必須データはname(氏名)のみ。データはすべてTEXT(str)型
cur.execute("""
CREATE TABLE personal(
name TEXT NOT NULL,
birth TEXT DEFAULT None,
address TEXT DEFAULT None,
hphone TEXT DEFAULT None,
cphone TEXT DEFAULT None,
cls TEXT DEFAULT None
)
""")
# taikaiテーブルの中身を定義する。全部で3項目で必須データはname(氏名)のみ。データはすべてTEXT(str)型
cur.execute("""
CREATE TABLE taikai(
name TEXT NOT NULL,
evnt TEXT DEFAULT None
)
""")
conn.close() # データベースへの接続を終了する3. Excelファイルの検索条件からデータベース内で該当する情報を取得する
Excelファイルの検索条件タブから選びたい情報を特定します。Excelファイルを開くためには先ほどインストールしましたopenpyxlを使います。Excelファイル名はtaikai_data.xlsxです。このプログラムと同じフォルダーにあればファイル名を指定すればファイルを開くことができます。
最後の処理で検索条件の前後に%をつけているのは、後ほどSQL文で検索するときに%が「0文字以上の任意の文字列」を意味するワイルドカードとして使用できるからです。
import openpyxl # Excelファイル操作ライブラリのインポート
import re # 正規表現ライブラリのインポート
wb = openpyxl.load_workbook("taikai_data.xlsx") # pythonプログラムと同じフォルダにあるExcelファイルを開く
ws = wb["検索条件"] # 検索条件タブを選択する
for rows in ws.iter_rows(min_row=2, max_row=2): # 2行目を選択する
keys = [] # セルの値を保存する配列変数keysを初期化する
for col in rows: # 1列目からデータが存在する最終列までループする
keys.append(col.value) # セルの値を配列変数keysに追加していく
for i in range(len(keys)): # 配列変数keys内の要素数だけループする
keys[i] = re.sub(r'^','%',keys[i]) # 配列内にあるセルの値の先頭に%を追加する
keys[i] = re.sub(r'$','%',keys[i]) # 配列内にあるセルの値の末尾に%を追加する次は大会参加者名簿タブの内容をデータベースにします。Excelの内容をそのまま行単位でデータベースに入力しますと、氏名と参加する種目に'○'が入ったデータとなり、どれに参加するのか分かりません。そこで'○'の付いたところを種目名に読み替えてデータベースに入力します。
$$
\scriptsize
\begin{array}{l:l:l:l:l:l:l:l:l}
氏名 & 種目 & & & & & & & \\
& AAA & BBB & CCC & DDD & EEE & FFF & GGG & HHH \\ \hline
参加者A & ○ & & & ○ & & & & \\
参加者B & ○ & ○ & & & & & & \\
参加者D & ○ & & & ○ & & & &
\end{array}
\normalsize
$$
上の参加者Aを行単位でそのまま読み取ると['参加者A', '○',None,None,'○',None,None,None,None]ですが、'○'を種目名に置き換え、'○'がないところを省略しますと['参加者A','AAA, DDD']です。これを下のプログラムで実現します。
import sqlite3 # sqliteのインポート
import openpyxl # Excelファイル操作ライブラリのインポート
ws = wb["大会参加者名簿"] # 大会参加者名簿タブを選択する
aevnt = [] # 種目名を保持する配列変数を初期化する
for row in ws.iter_cols(min_row=2, min_col=2,max_row=2): # 2行目の2列目から最終列までループ
for col in row:
aevnt.append(col.value) # 種目名をaevntに追加していく
for row in ws.iter_rows(min_row=3): # 3行目から処理を始める
values = [] # データベースに登録する配列変数を初期化する
values2 = [] # 種目名の配列変数を初期化する
for col in row: # 対象行の1列目からデータの存在する最終列までループする
if col.column == 1: # 1列目の処理
values.append(col.value) # 氏名を追加
elif col.value == '○': # 2列目以降で'○'が存在する
values2.append(aevnt[col.column-2]) # '○'の代わりに該当する種目名を追加する
mergevalue = ",".join(values2) # 種目名の配列を文字列に変換する
values.append(mergevalue) # 文字列に変換した種目名をデータベースに登録する配列変数に追加する
cur.execute("INSERT INTO taikai VALUES(?,?)",[values[0],values[1]]) # taikaiテーブルにデータを登録する最終行はsqliteを使って編集した大会参加者名簿のデータをtaikaiテーブルに登録しています。taikaiテーブルは前項で書いたようにname, evntのフィールドで構成されています。VALUES(?,?)となっている1番目の?がnameで2番目の?がevntです。ここの値が固定であれば?の代わりに直接値を指定してよいのですが、今回のようにExcelから読み込んだ値で登録する内容が変わる場合には[values[0],values[1]]のように読み込む変数を指定できます。この場合であれば1番目の?にvalues[0]が適用され、2番目の?にvalues[1]が適用されます。
cur.execute("INSERT INTO taikai VALUES(?,?)",[values[0],values[1]]) 最後に会員名簿タブの内容をデータベースにします。プログラムで実行している内容は大会参加者名簿タブの内容と同じですので、もう少しよく考えれば関数でまとめることができるかもしれません。しかし納期は1日でしたので断念しました。
import sqlite3 # sqliteのインポート
import openpyxl # Excelファイル操作ライブラリのインポート
ws = wb["会員名簿"]
acls = []
for row in ws.iter_cols(min_row=2, min_col=6, max_row=2):
for col in row:
acls.append(col.value)
for rows in ws.iter_rows(min_row=3):
values = []
values2 = []
for col in rows:
if col.column < 6:
values.append(col.value)
elif col.value == '○':
values2.append(acls[col.column-6])
mergevalue = ",".join(values2)
values.append(mergevalue)
cur.execute("INSERT INTO personal VALUES(?,?,?,?,?,?)",[values[0],values[1],values[2],values[3],values[4],values[5]])4. 該当する情報を新しいExcelタブを作って一覧にし、これを保存する
Excelファイルの内容を検索条件タブ以外の内容はすべてデータベースに保存しました。この中から検索条件に合致するデータをsqliteで抽出します。
検索条件から特定したその種目の名簿を以下のように出したいということです。その種目を中心にやっているクラスに所属している方はリスト上部に表示し、「○」もつけてほしいという要望がありました。
$$
\begin{array}{l:l:l}
AAA参加名簿& 氏名 & AA \\ \hline
& 参加者A & ○ \\
& 参加者D &
\end{array}
\normalsize
$$
これを実現するため2種類のSQL文で検索を行います。pandasというデータ解析用ライブラリを使用していますので、導入されていない場合にはLinuxであればターミナル、Windowsであればコマンドプロンプトかパワーシェルから以下のコマンドでインストールしてください。
pip install pandas1番目を日本語化しますと「personalテーブルとtaikaiテーブルからnameフィールドと一致するデータ行のみを抽出して結合したテーブルから種目とクラスが検索条件と合致するデータ行を出力する」
2番目を日本語化しますと「personalテーブルとtaikaiテーブルからnameフィールドと一致するデータ行のみを抽出して結合したテーブルから種目が検索条件と合致し、クラスが検索条件と異なるデータ行を出力する」
import pandas as pd
p_member = pd.read_sql_query("SELECT * FROM personal NATURAL INNER JOIN taikai WHERE evnt LIKE ? AND cls LIKE ?", conn, params=keys)
n_member = pd.read_sql_query("SELECT * FROM personal NATURAL INNER JOIN taikai WHERE evnt LIKE ? AND cls NOT LIKE ?", conn, params=keys)最後にデータベースの検索結果をExcelファイルに書き込みます。
ws = wb.create_sheet(keys[0].replace('%','')) # 開いているExcelファイルにタブ追加する。タブ名は検索条件の種目名
srow = 1 # 書き込み開始行
scol = 2 # 書き込み開始列
ws.cell(row=srow, column=scol).value = keys[0].replace('%','') + '参加名簿' # 題名を検索条件の種目+参加名簿とする
ws.cell(row=srow+2, column=scol).value = '氏名' # 1列目の名前を'氏名'とする
ws.cell(row=srow+2, column=scol+1).value = keys[1].replace('%','') # 2列目の名前を検索条件のクラスとする
for i in range(len(p_member)): # 優先する方々を先に編集する
ws.cell(row=srow+3+i, column = scol).value = p_member.name[i] # 名前をデータベースの検索結果より書き込む
ws.cell(row=srow+3+i, column = scol+1).value = '○' # 優先クラスに所属していることを示す'○'を書き込む
for i in range(len(n_member)): # 優先クラスではないが大会参加者名簿にいる方々を編集する
ws.cell(row=srow+3+len(p_member)+i, column = scol).value = n_member.name[i] # 名前をデータベースの検索結果より書き込む
wb.save("taikai_data_out.xlsx") # taikai_data_out.xlsxでExcelファイルを保存する
◇ 完成コード
import sqlite3
import pandas as pd
import openpyxl
import re
conn = sqlite3.connect('member.db')
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS personal")
cur.execute("DROP TABLE IF EXISTS taikai")
cur.execute("""
CREATE TABLE personal(
name TEXT NOT NULL,
birth TEXT DEFAULT None,
address TEXT DEFAULT None,
hphone TEXT DEFAULT None,
cphone TEXT DEFAULT None,
cls TEXT DEFAULT None
)
""")
cur.execute("""
CREATE TABLE taikai(
name TEXT NOT NULL,
evnt TEXT DEFAULT None
)
""")
wb = openpyxl.load_workbook("taikai_data.xlsx")
ws = wb["検索条件"]
for rows in ws.iter_rows(min_row=2, max_row=2):
keys = []
for col in rows:
keys.append(col.value)
for i in range(len(keys)):
keys[i] = re.sub(r'^','%',keys[i])
keys[i] = re.sub(r'$','%',keys[i])
ws = wb["大会参加者名簿"]
aevnt = []
for row in ws.iter_cols(min_row=2, min_col=2,max_row=2):
for col in row:
aevnt.append(col.value)
for row in ws.iter_rows(min_row=3):
values = []
values2 = []
for col in row:
if col.column == 1:
values.append(col.value)
elif col.value == '○':
values2.append(aevnt[col.column-2])
mergevalue = ",".join(values2)
values.append(mergevalue)
cur.execute("INSERT INTO taikai VALUES(?,?)",[values[0],values[1]])
ws = wb["会員名簿"]
acls = []
for row in ws.iter_cols(min_row=2, min_col=6, max_row=2):
for col in row:
acls.append(col.value)
for rows in ws.iter_rows(min_row=3):
values = []
values2 = []
for col in rows:
if col.column < 6:
values.append(col.value)
elif col.value == '○':
values2.append(acls[col.column-6])
mergevalue = ",".join(values2)
values.append(mergevalue)
cur.execute("INSERT INTO personal VALUES(?,?,?,?,?,?)",[values[0],values[1],values[2],values[3],values[4],values[5]])
p_member = pd.read_sql_query("SELECT * FROM personal NATURAL INNER JOIN taikai WHERE evnt LIKE ? AND cls LIKE ?", conn, params=keys)
n_member = pd.read_sql_query("SELECT * FROM personal NATURAL INNER JOIN taikai WHERE evnt LIKE ? AND cls NOT LIKE ?", conn, params=keys)
ws = wb.create_sheet(keys[0].replace('%',''))
srow = 1
scol = 2
ws.cell(row=srow, column=scol).value = keys[0].replace('%','') + '参加名簿'
ws.cell(row=srow+2, column=scol).value = '氏名'
ws.cell(row=srow+2, column=scol+1).value = keys[1].replace('%','')
for i in range(len(p_member)):
ws.cell(row=srow+3+i, column = scol).value = p_member.name[i]
ws.cell(row=srow+3+i, column = scol+1).value = '○'
for i in range(len(n_member)):
ws.cell(row=srow+3+len(p_member)+i, column = scol).value = n_member.name[i]
wb.save("taikai_data_out.xlsx")
conn.close()要件定義からとりあえず動くものを書くと、処理が冗長であったり、直値だらけになりました。そもそもデータベースをcommit(保存)しないプログラムにデータベースを設ける意義はあるのかという設計の根幹を揺るがす疑問があります。
しかし完成したプログラムを使いながら、「これなら今後いろいろできそうだね」という含みのあるコメントをありがたく頂戴しましたので、その機会には今回の課題を見直したいと思います。
この記事が気に入ったらサポートをしてみませんか？