학교 업무 자동화 - 생활교육위원회 문서 자동화

학교 업무 자동화 - 생활교육위원회 문서 자동화-1

교육을 꿈꾸며 교사가 되었지만, 우리는 교육이 아닌 것에 너무 많은 시간을 소모한다.

그래서 이번 글에서는 생활교육위원회에서 출결 사안에 대한 처리 자동화에 대해 소개해보려고 한다.

학생을 직접 지도하는 과정은 자동화가 불가능하지만, 문서의 과정은 자동화 할 수 있다.

길이가 너무 긴 것은 링크로 대체했다.

1. 생활교육위원회

원래 생교위 대상 학생은 다음과 같은 업무 처리과정을 거친다.

학교 업무 자동화 - 생활교육위원회 문서 자동화-2

학생 한명당 필요한 문서가 최소 4개이니, 10명이면 40장이다.

그 중에서 출결 사안은 상당히 까다로운 부분이 많았다.

문서에 학생의 미인정 출결을 일일이 다 기록해야 하는 것.

학교 업무 자동화 - 생활교육위원회 문서 자동화-3

원래는 담임에게서 학생의 출결을 한글 문서로 받아 하나씩 확인한 후 수기로 옮기는 과정을 거쳤다.

해당 과정이 너무 번거러워서 spreadsheet와 python으로 한방에 끝내보기로 했다.

2. 아이디어

학교 업무 자동화 - 생활교육위원회 문서 자동화-4

코딩에서 실력보다 더 중요한 것은 아이디어 그 자체이다.

어떻게 문서 작성 과정을 자동화 할지 생각해보자.

필요한 데이터들 중 중복되는 값들을 추려내 스프레드 시트로 만들고 배포한다.
미인정 출결에 의한 생교위 대상 학생의 미인정 출결일수, 담임 의견서를 각 담임에게 받는다. 과거에는 이것을 인쇄물로 했다면, 이제는 spreadsheet로 취합하는 것이다.
spreadsheet API를 이용해 데이터를 가져온다.
pandas를 이용해 데이터를 정제한다.
한글의 누름틀과 pywin32를 이용해 hwp, hwpx 형식의 문서를 작성한다.
담임교사 및 학생들에게 사안을 확인받고 한꺼번에 스캔하여 결재를 올린다.

이러한 과정을 통해 문서 생성을 자동화 해보았다.

3. 필요한 데이터 수집

학교 업무 자동화 - 생활교육위원회 문서 자동화-5

시작하기 전, 각각의 문서가 어떤 데이터를 필요로 하는지 살펴볼 시간이다.

학생 자기 변론서
- 이름, 학번, 성별, 사안제목, 학생의 미인정 출결, 학생이름
사실 확인서(담임교사 작성)
- 학생이름, 학번, 성별, 사안제목, 담임교사 이름
학생 사안조사 보고서
- 이름, 학번, 성별, 사안제목, 사안발생일시, 사안발생장소, 관련학생, 사안내용, 관련 학교생활규정, 작성일, 작성교사
학생생활교육위원회 출석 및 의견 제출 요청서
- 개최 일시, 개최장소, 이름, 학번, 사안발생일시, 사안발생장소, 위반행위, 관련규정, 작성일
서면의견서
- 관련학생 성명, 학번, 보호자 성명, 학생과의 관계
우편봉투
- 학생이름, 우편번호, 주소

이들 중에서 해당 문서들이 공통적으로 필요하는 데이터는 진한 색으로 표시해보았다.

만약 이것을 spreadsheet로 작성한다면, 열의 인덱스는 데이터 중복을 제거하고 설정하면 된다.

그럼 시트를 만들어보자.

4. spreadsheet 제작 및 API 설정

시트는 아래와 같이 만들어 보았다.

학교 업무 자동화 - 생활교육위원회 문서 자동화-6

학생 인적사항
- 학번, 이름, 성명
내용
- 사안번호, 발생일시, 미인정출결, 담임의견, 작성일, 생교위 날짜

여기서 사안내용, 위반행위, 적용가능한 학교생활규정 등이 빠진 이유는 해당 사안이 출결에 대한 사안이기 때문이다.

이제 해당 스프레드 시트에 접근할 수 있는 API을 설정해야 한다.

학교 업무 자동화 - 생활교육위원회 문서 자동화-7

원래는 API 설정까지 모두 다루고 싶었으나, 그렇게 글을 쓰면 2주가 걸려도 다 못 쓸것 같아서 그냥 링크로 대체한다.

spreadsheet의 api 설정은 공식문서나 junsugi님의 velog를 참고하길 바란다.

대충 spreadsheet의 API설정 과정은 아래와 같다.

API 사용 설정
OAuth 동의 화면 구성
데스크톱 애플리케이션의 사용자 인증 정보 승인
Google 클라이언트 라이브러리 설치
사용

5. 한글문서 세팅

python을 이용하기 전에 한글문서 세팅이 필요하다.

그냥 코딩으로 모든걸 처리하기에는 시간이 많이 걸리기에, 입력필드를 이용해 데이터가 들어갈 자리를 대충 정해주려고 한다.

학교 업무 자동화 - 생활교육위원회 문서 자동화-8

학생 사안 조사 보고서, 학생 자기 변론서 등 문서의 데이터가 들어갈 자리에 각각의 필드명을 입력해준다.

이때 주의해야 할 점은 필드의 이름이 spreadsheet의 열이름과 일치해야 한다.

나는 아래와 같은 문서들에 양식을 지정했다.

학교 업무 자동화 - 생활교육위원회 문서 자동화-9

혹시 막히는 부분이 있다면 일상의 코딩(일코)님의 티스토리를 참고하자.

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)

이제 리턴받은 자료를 dataframe으로 가공할 차례이다.

시트로 불러온 것을 필요없는 데이터들은 제거하고 가공한다.

적용할 학교 생활규칙, 학생의 주소들은 모두 학번을 키로 매칭했다.

def load_checkdata():
    # 스프레드 시트를 호출하고 데이터 가공
    SPREADSHEET_ID = "당신의 스프레드시트 API key"
    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()