[업무자동화]파이썬으로 청구서 자동생성하기

파이썬을 업무 생산성을 높이기 위한 툴로서 사용한 예시를 보여드리고자 합니다.

제가 현업에서 청구업무 담당자가 힘들어 하는 청구내역서 작성 업무를 파이썬과 이전 포스트에서 소개했던 Streamlit을 이용하여 자동화했던 방법에 대해 설명드리고자 합니다.

이런 방식을 활용하면 교육 이수증 만들기, 매월 매출보고서 만들기 등 공통의 양식을 사용하는데 데이터만 변경하면 되는 업무에 활용하면 도움이 많이 될 것 같습니다.

 

1.  자동화 업무프로세스 

• 기본 청구서 양식 엑셀파일 만들기
• 바뀔 정보를 관리하는 기초데이터 엑셀 파일 만들기
• 파이썬에서 양식에 입력할 데이터를 기초데이터 엑셀 파일을 불러오기
• 파이썬으로 엑셀에 저장한 항목에 해당하는 변수(variable)를 선언하고 값을 지정하기
• 파이썬으로 해당 변수 값을 엑셀의 해당 셀에 입력하고 파일이름 만들어 저장하기

2.  청구서 기본 양식 만들기

• 외부 입력받을 데이터를 지정 (아래의 노란색 박스 표시)
  - 서비스브랜드, 청구서작성일, 청구처명, 청구월, 
  - 서비스명1, 서비스명2, 서비스명3, 공급가액
  - 입금계좌번호
• 자동 계산 가능한 항목은 입력에서 제외
  - 부가가치세는 공급가엑의 10% 계산식 적용
  - 합계는 공급가액 + 부가가시세 계산식 적용
  - 총청구금액은 합계필드의 금액합계(SUM) 적용

청구서양식

 

3.  기초데이터 엑셀만들기

• 상기 입력할 데이터 항목별도 기초 데이터를 엑셀로 만들기
  - 항목별 필수 입력과 선택 입력(서비스 1개 이상) 항목에 따라 데이터 입력하여 생성

청구서 기초데이터

 

4.  파이썬에 기초데이터 불러오고, 변수에 저장하기

• 엑셀로 만든 기초데이터를 불러와서
• 같은 형식의 청구서 양식을 만들어야 하므로 청구처명을 추출하여 리스트로 만들고,
• for 문으로 해당 청구양식에 입력할 데이터를 변수에 저장

import pandas as pd

#청구 고개사 정보 엑셀 불러오기
customer_raw = pd.read_excel('c:/data/customer_bill.xlxs')

#고객사 리스트만 별도 추출하기
customer_name_list = customer_raw['청구처명']

#엑셀에 입력할 항목 변수에 저장하기
for name in customer_name_list:
    print('청구처명',name)
    customer_bill = customer_raw[customer_raw['청구처명'] == name]
    print('bill-',customer_bill)
    customer_bill_brand = customer_bill.iloc[0,0]  #청구서용 청구처명 불러오기
    customer_bill_date = customer_bill.iloc[0,1]  #청구서용 청구작성일 불러오기
    customer_bill_name = customer_bill.iloc[0,2]  #청구서용 청구처명 불러오기
    customer_bill_month = customer_bill.iloc[0,3]  #청구서용 청구월 불러오기
    service1 = customer_bill.iloc[0,4] #서비스명1 불러오기
    service1_cost = customer_bill.iloc[0,5] #공급가액1 불러오기
    service2 = customer_bill.iloc[0,6] #서비스명2 불러오기
    service2_cost = customer_bill.iloc[0,7] #공급가액2 불러오기
    service3 = customer_bill.iloc[0,8] #서비스명3 불러오기
    service3_cost = customer_bill.iloc[0,9] #공급가액3 불러오기
    customer_account = customer_bill.iloc[0,10] #계좌번호 불러오기
    print('서비스명-',service1)

 

5.  청구양식에 해당 데이터 입력하기

• for문 안에서 청구서 엑셀양식을 불러오고,
• 양식에 필요한 값을 엑셀 셀 위치에 지정하여 입력하고,
• 엑셀파일의 파일이름을 청구처명와 청구월로 자동으로 생성하여 지정한 폴더에 저장

#for문 아래 이어서 작성

#청구서 양식 파일 불러오기
wb = load_workbook('c:/data/basic-form.xlsx')
 
#청구서 입력할 워크시트 선택하기
ws1 = wb['청구서']

#청구내역서 세부항목별 셀에 데이터 입력하기
ws1['B2'].value = customer_bill_brand  #브랜드명
ws1['B4'].value = customer_bill_name  #청구처명
ws1['B6'].value = f'{customer_bill_month}월 이용대금 청구서'
ws1['O1'].value = customer_bill_date.strftime('%Y-%m-%d')  #청구서작성일자
ws1['B15'].value = service1  #서비스명1
ws1['B16'].value = service2  #서비스명2
ws1['B17'].value = service3  #서비스명3
ws1['H15'].value = service1_cost  #공급가액1
ws1['H16'].value = service2_cost  #공급가액2
ws1['H17'].value = service3_cost  #공급가액3
ws1['I25'].value = customer_account  #계좌번호

#청구내역서 저장하기
wb.save(f'c:/data/output/{customer_bill_name}_{customer_bill_month}월_청구내역서.xlsx')

 

상기와 같이 for 문을 통해 일괄로 양식생성을 쉽게 할 수 있습니다.

 

6.  엑셀파일 셀의 형식 변경하기

• 기본 양식파일에 셀별 형식(폰트, 배치, 외곽선, 데이터포맷 등)을 지정해 놓고 데이터만 입력하면 별도 형식지정 필요없지만
• 입력하는 데이터가 가변적인 데이터 리스트 출력의 경우는 입력한 데이터 각각에 형식을 지정필요
• 다음은 셀별 데이터 형식을 지정하는 방법입니다. (예시)

font = openpyxl.styles.Font(name='맑은 고딕', size=10)
border = openpyxl.styles.Border(left=openpyxl.styles.Side(style='thin'),             
                right=openpyxl.styles.Side(style='thin'),
                top=openpyxl.styles.Side(style='thin'),
                bottom=openpyxl.styles.Side(style='thin'))

ws1[셀번호].alignment = Alignment(horizontal='right', vertical='center')
ws1[셀번호].font = font
ws1[셀번호].border = border
ws1[셀번호].number_format = '#,##0'

 

엑셀 파일내에서 셀단위 데이터 입력하는 방법에 대해 간략하게 예시를 통해 알아 보았습니다.

상기 예시로 모든 양식의 데이터를 만들 수는 없겠지만 기본적인 도움이 되기를 바랍니다.