教育を夢見て教師になったが、私たちは教育以外に多くの時間を費やしている。
そこで今回の記事では、生活教育委員会における出欠の問題に関する処理の自動化について紹介しようと思う。
学生を直接指導する過程は自動化できないが、文書の過程は自動化できる。
長すぎるものはリンクで代替した。
1. 生活教育委員会
もともと生教委の対象学生は次のような業務処理過程を経る。
学生1人当たり必要な文書が最低4つ必要なので、10人だと40枚だ。
その中で出欠問題は非常に面倒な部分が多かった。
文書に学生の未承認出欠を一つずつすべて記録しなければならないこと。
もともとは担任から学生の出欠をハングル文書で受け取り、一つずつ確認して手書きで移す過程を経ていた。
この過程が非常に煩わしかったため、スプレッドシートとPythonで一発で片付けることにした。
2. アイデア
コーディングで実力よりも重要なのはアイデアそのものである。
どのように文書作成の過程を自動化するか考えてみよう。
- 必要なデータの中から重複する値を集めてスプレッドシートにして配布する。
- 未承認出欠による生教委対象学生の未承認出欠日数、担任の意見書を各担任から受け取る。過去にはこれを印刷物で行っていたが、今はスプレッドシートで集計する。
- スプレッドシートAPIを利用してデータを取得する。
- Pandasを利用してデータを精製する。
- ハングルの埋め込みフィールドとpywin32を使用してhwp、hwpx形式の文書を作成する。
- 担任教師および学生に問題を確認してもらい、一括してスキャンして決裁を上げる。
このような過程を通して文書生成を自動化してみた。
3. 必要なデータ収集
始める前に、それぞれの文書がどんなデータを必要とするか見てみる時間だ。
- 学生自己弁護士
- 名前、学籍番号、性別、事案タイトル、学生の未承認出欠、学生名
- 事実確認書（担任教師作成）
- 学生名、学籍番号、性別、事案タイトル、担任教師名
- 学生事案調査報告書
- 名前、学籍番号、性別、事案タイトル、事案発生日時、事案発生場所、関連学生、事案内容、関連学校生活規定、作成日、作成教師
- 学生生活教育委員会出席および意見提出要求書
- 開催日時、開催場所、名前、学籍番号、事案発生日時、事案発生場所、違反行為、関連規定、作成日
- 書面意見書
- 関連学生姓名、学籍番号、保護者名、学生との関係
- 郵便封筒
- 学生名、郵便番号、住所
これらの中で、該当文書が共通して必要とするデータは太字で示しておいた。
もしこれをスプレッドシートに書くなら、列のインデックスはデータの重複を除去して設定すればよい。
それではシートを作ってみよう。
4. スプレッドシート作成およびAPI設定
シートは以下のように作ってみた。
- 学生の人的事項
- 学籍番号、氏名、姓名
- 内容
- 事案番号、発生日時、未承認出欠、担任意見、作成日、生教委日付
ここで事案内容、違反行為、適用可能な学校生活規定がない理由は、該当事案が出欠に関するものだからである。
これで該当スプレッドシートにアクセスできるAPIを設定する必要がある。
本当はAPI設定まで全部取り上げたかったが、そうやって記事を書くと2週間かかっても書ききれないと思い、リンクで代替することにした。
スプレッドシートのAPI設定は公式文書やjunsugiさんのvelogを参考にしてほしい。
大まかなスプレッドシートのAPI設定過程は以下の通りである。
- API使用設定
- OAuth同意画面の構成
- デスクトップアプリケーションのユーザー認証情報の承認
- Googleクライアントライブラリのインストール
- 使用
5. ハングル文書設定
Pythonを使用する前にハングル文書設定が必要である。
単純にコーディングで全てを処理するには時間がかかるため、入力フィールドを利用してデータが入る場所を大まかに決めておこうと思う。
学生事案調査報告書、学生自己弁論書など文書のデータが入る場所にそれぞれのフィールド名を入力する。
この時、注意すべき点はフィールド名がスプレッドシートの列名と一致している必要がある。
私は以下の文書に様式を指定した。
もし詰まっている部分があれば日常のコーディング(日コ)さんのティストリーを参考にしよう。
6. Pythonコード
まずpipを使用して必要なライブラリをインストールする。
pip install pywin32 pandas google-api-python-client google-auth-httplib2 google-auth-oauthlib
そしてpyまたはipynbファイルを作成して必要なモジュールをimportする。
経験上、管理に必要な変数は一番上にあるのが便利なので、経路や担任教師の名前などを一番上に置いた。
量が多ければjsonファイルで管理するのも良いだろう。
import win32com.client as win32
import pandas as pd
import time
import pathlib
import datetime as dt
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
hwp = win32.gencache.EnsureDispatch('HWPFrame.HwpObject')
hwp.RegisterModule('FilePathCheckDLL', 'SecurityModule')
rootpath = pathlib.Path.cwd()/"school"/"guidance"
errorfilepath = rootpath / "エラーレポート.txt"
beforefilespath = rootpath / "format" / "before"
rulesdfpath = rootpath / "生教委_名簿.xlsx"
addressdfpath = rootpath / "format" / "info.xlsx"
phonedfpath = rootpath / "format" / "phoneNumber.xlsx"
lastsavepath = rootpath / "created"
backuppath = rootpath / "backup"
lastsavepath.mkdir(exist_ok=True)
teachers = {301:"キム〇〇", 302:"パク〇〇", 303: "ナムグン〇", 304:"サゴン〇"}
公式文書からスプレッドシートに必要なコードを引いてきた。
一見複雑に見えるが、シートのIDと範囲を受け取ってデータを返す形である。
def main(spreadID, spreadRange):
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
creds = None
if pathlib.Path(f"{rootpath}/jsonFiles/token.json").exists():
creds = Credentials.from_authorized_user_file(f"{rootpath}/jsonFiles/token.json", SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
f"{rootpath}/jsonFiles/credentials.json", SCOPES
)
creds = flow.run_local_server(port=0)
with open(f"{rootpath}/jsonFiles/token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("sheets", "v4", credentials=creds)
sheet = service.spreadsheets()
result = (
sheet.values()
.get(spreadsheetId=spreadID, range=spreadRange)
.execute()
)
values = result.get("values", [])
if not values:
print("No data found.")
return
return values
except HttpError as err:
print(err)
次に返ってきた資料をデータフレームに加工する番である。
シートから取得したものを、不要なデータを取り除いて加工する。
適用する学校生活規則、学生の住所は全て学籍番号をキーにマッチした。
def load_checkdata():
# スプレッドシートを呼び出してデータ加工
SPREADSHEET_ID = "あなたのスプレッドシートAPIキー"
SPREADRANGE = "シート名!A8:V200"
sheet = main(SPREADSHEET_ID, SPREADRANGE)
df_origin = pd.DataFrame(sheet[1:], columns=sheet[0])
df_origin.drop("順番", axis=1, inplace=True)
df_origin.reset_index(drop=True, inplace=True)
df_origin.dropna(subset="学籍番号", inplace=True)
df_origin = df_origin.loc[df_origin['学籍番号'] != ""]
df_origin.reset_index(drop=True, inplace=True)
df_origin.fillna("", inplace=True)
df_origin["事案タイトル"] = "勤態不良"
df_origin["事案発生場所"] = "該当なし"
df_origin["学生生活規定"] = "1項 未承認遅刻を頻繁にする学生"
df_origin["違反行為"] = "勤態不良"
df_origin["完結有無"] = df_origin["完結有無"].str.replace("TRUE", "終了")
df_origin.rename(columns={"担任意見":"事案内容"},inplace=True)
# たまに学籍番号認識がされないため、数字に変更
excel["学籍番号"] = pd.to_numeric(excel["学籍番号"])
excel.sort_values("学籍番号", inplace=True)
excel.reset_index(drop=True, inplace=True)
# 担任教師名の入力
excel["担任教師"]="担任"
for student in range(len(excel)):
studentClass = int(excel.loc[student, "学籍番号"])//100
excel.loc[student, "担任教師"] = teachers[studentClass]
excel = excel.dropna(subset=["学籍番号"])
excel = excel.drop(excel[excel['完結有無']=="終了"].index)
# 学籍番号を基準に住所入力
address = pd.read_excel(addressdfpath)
phone = pd.read_excel(phonedfpath)
rules = pd.read_excel(rulesdfpath, sheet_name="Sheet2")
excel = pd.merge(excel, address, on="学籍番号", how="left")
excel = pd.merge(excel, phone, on="学籍番号", how="left")
# 違反項目の入力
add_columns = ["違反項目"]
excel[add_columns] = ""
for i in range(len(excel)):
# 違反項目の収集
student_rules = []
for j in range(len(rules)):
if rules.loc[j, "内容"] in excel.loc[i, "学生生活規定"]:
student_rules.append(rules['項目'][j])
excel.loc[i, "違反項目"] = ", ".join(student_rules)
excel.reset_index(drop=True, inplace=True)
excel['学籍番号']=excel["学籍番号"].astype(int)
if len(excel) < 1 :
return
# 万が一元の文書が破損した場合に備えてバックアップしておく
backupfilename = dt.datetime.now().strftime("%Y%m%d %H")
excel.to_excel(backuppath / f"{backupfilename}_backup.xlsx")
return excel
こうすれば学生の住所、郵便番号などが入力されたデータフレームが出来上がる。
データフレームの列がかなり長いが、メモリが豊富なので問題ない。
よく使うコードはモジュール化しておいた。
# ページをシートの行だけコピーする
def copypage(name, df):
name.SetActive_XHwpDocument()
if len(df) > 1:
hwp.MovePos(3)
hwp.Run('SelectAll')
hwp.Run('Copy')
hwp.MovePos(3)
for i in range(len(df)-1):
hwp.MovePos(3)
hwp.Run('Paste')
hwp.MovePos(3)
hwp.Run('DeleteBack')
hwp.Run('DeleteBack')
# 指定されたハングル文書の埋め込みフィールドに文字を入力する
# 名前がnameのハングル文書をアクティブにして埋め込みフィールドにdfのデータを入力する
def fillpage(name, df, field_lst : list):
name.SetActive_XHwpDocument()
for page in range(len(df)):
for field in field_lst :
hwp.PutFieldText(f'{field}{{{{{page}}}}}', df[field].iloc[page])
これで該当データを利用してハングル文書を作成すればよい。
load_checkdataでデータを取得し、pywin32を利用して各文書の埋め込みフィールドに適した値を入力する。
申し訳ないが、コードが少し長い。
分かりにくければ私のアイデアだけ参考にしてほしい。
def start():
# まずはエクセルデータを取得
excel_check = load_checkdata()
if excel is None:
return
if len(excel) < 1 :
return
# 標準フォルダ内の全ファイルを開く
beforefiles = list(beforefilespath.iterdir())
for file in beforefiles:
hwp.Run('FileNew')
hwp.Open(file)
# 変数名の指定
offical = hwp.XHwpDocuments.Item(1)
report_check = hwp.XHwpDocuments.Item(2)
student_excuse = hwp.XHwpDocuments.Item(3)
confire = hwp.XHwpDocuments.Item(4)
mail = hwp.XHwpDocuments.Item(5)
demand = hwp.XHwpDocuments.Item(6)
# 公文作成
offical.SetActive_XHwpDocument()
dateDict = {0: '月', 1:'火', 2:'水', 3:'木', 4:'金', 5:'土', 6:'日'}
date_origin = excel['生教委開催日'].iloc[0]
date = dt.datetime.strptime(str(date_origin), '%Y年 %m月 %d日')
dow = dateDict[date.weekday()]
date_strf = dt.datetime.strftime(date, '%Y.%m.%d.')
once = ["生教委開催日", "曜日", "事案", "人数"]
tables = ["違反行為", "学籍番号", "名前", "事案番号"]
guidanceType = excel["違反行為"].unique()
for one in once :
if one == "曜日":
hwp.PutFieldText(f'{one}{{{{{0}}}}}', dow)
continue
elif one =="事案":
if len(guidanceType) == 1:
hwp.PutFieldText(f'{one}{{{{{0}}}}}', f"{guidanceType[0]} 1件")
else :
hwp.PutFieldText(f'{one}{{{{{0}}}}}', f"{guidanceType[0]} 外 {len(guidanceType)-1}件")
continue
elif one =="人数":
hwp.PutFieldText(f'{one}{{{{{0}}}}}', len(excel))
continue
hwp.PutFieldText(f'{one}{{{{{0}}}}}', date_strf)
for student in range(len(excel)):
for table in tables :
if table == "学籍番号":
studentGrade = int(excel['学籍番号'].iloc[student])//10000
studentClass = int(excel['学籍番号'].iloc[student])//100 - studentGrade*100
hwp.PutFieldText(f'学籍番号{{{{{student}}}}}', f"{studentGrade}-{studentClass}")
continue
if table == "名前":
studentName = excel['名前'].iloc[student]
hwp.PutFieldText(f'名前{{{{{student}}}}}', f"{studentName[0]}{(len(studentName)-1)*'O'}")
continue
hwp.PutFieldText(f'{table}{{{{{student}}}}}', excel[table].iloc[student])
hwp.SaveAs(lastsavepath / beforefiles[0].name)
# 事案調査報告書作成
report_check.SetActive_XHwpDocument()
field_list = [i for i in hwp.GetFieldList().split('\x02')]
copypage(report_check, excel_check)
fillpage(report_check, excel_check, field_list)
hwp.SaveAs(lastsavepath / beforefiles[1].name)
# 自己弁論書作成
student_excuse.SetActive_XHwpDocument()
field_list = [i for i in hwp.GetFieldList().split('\x02')]
copypage(student_excuse, excel_check)
fillpage(student_excuse, excel_check, field_list)
hwp.Run('DeleteBack')
hwp.SaveAs(lastsavepath / beforefiles[2].name)
# 受領確認書作成
confire.SetActive_XHwpDocument()
field_list = [i for i in hwp.GetFieldList().split('\x02')]
copypage(confire, excel)
fillpage(confire, excel, field_list)
hwp.SaveAs(lastsavepath / beforefiles[3].name)
# 封筒出力様式作成
mail.SetActive_XHwpDocument()
field_list = [i for i in hwp.GetFieldList().split('\x02')]
copypage(mail, excel)
fillpage(mail, excel, field_list)
for page in range(len(excel)):
hwp.PutFieldText(f'郵便番号{{{{{page}}}}}', (" ").join(list(str(int(excel["郵便番号"].iloc[page])))))
hwp.SaveAs(lastsavepath / beforefiles[4].name)
# 生教委案内および出席要求書作成
demand.SetActive_XHwpDocument()
field_list = [i for i in hwp.GetFieldList().split('\x02')]
copypage(demand, excel)
fillpage(demand, excel, field_list)
todayDate = dt.date.today().strftime("%Y年 %m月 %d日")
for page in range(len(excel)):
hwp.PutFieldText(f'作成日{{{{{page}}}}}', todayDate)
hwp.SaveAs(lastsavepath / beforefiles[5].name)
hwp.Quit()
こうしてファイルを作成し、担任教師にスプレッドシートで出欠に関する内容を受け取った後、start()関数を一回実行するだけでワンストップで終了する。
もちろん学生を直接対面して事実を確認することは必要だが、文書作成に費やす時間が大幅に減った。
このような方法で生教委事前文書、事後文書、特別教育依頼書まですべての文書過程を自動化できる。
7. 記事を終えて
もしかしたらこんな質問をする人がいるかもしれない。
これ…コードを書くのにもっと時間がかからないのか？
もちろん生活教育委員会文書作成に投じるはずだった時間と同じくらい、コードを書いて学ぶのに時間がかかった。
しかし前者は自己開発の助けにはならないが、後者ははるかに多くの助けになる。
また、余った時間を授業準備にもっと多く費やすことができた。
残念ながら、まだ教員のためにこういったプログラムを作ってくれるだろうという期待は教育庁や教育部にはない。
おそらく今後も期待することはできない可能性が高い。
だからこそ私たちは自活する必要がある。
誰かにとってアイデアとなる記事であることを願う。
댓글을 불러오는 중...