[파이썬 신병 교육대] MDB로 부터 Packing List 자동출력 프로그램_1주차_이등병

파이썬 신병 교육대 1주차 이등병

https://youtu.be/DtIgm4BPggQ

유튜버 김왼손의 왼손코딩님이 파이썬 신병 교육대를 열었다.

12월 12일(일) 21시 온라인 입소식과 함께 이등병으로 시작하게 되었다.

사실 개인적으로는 다른 일정 때문에 입소식은 녹화본으로 대신했다.

이번 교육의 특이점은 파이썬 문법을 가르쳐 주지 않는다는 것이다.

뭐든지 간에 본인이 만들고 싶은 것을 정하고,

(정해졌다면) 그것을 완성하기 위해 필요한 것을 찾아내는 팁을 알려 줄 뿐이었다.

병장 전역을 하기 위해서는

만들기로 결정한 프로그램을 4주 뒤에 완성하면 된다!

 

"Packing List 자동 출력 프로그램"을 만들자!

 

회사(제조업)에서 생산 완료된 개별 제품의 데이터를 MDB로 관리하고 있다.

로트번호, 스펙, 중량이 중요한 데이터인데,

해당 데이터로 부터 원하는 로트번호에 대한 Packing List를 자동 출력하는 프로그램을 만들기로 했다.

---

구현내용

프로그램 실행화면

1. pyodbc 모듈로 mdb 파일의 모든 데이터를 가져오는 쿼리를 작성했다.
2. xlrd, xlwt 모듈로 mdb에서 가져온 데이터를 pk.xls에 저장했다.
3. tkinter 모듈로 GUI를 구현했다. 고객사 리스트는 customer.txt 파일에 저장되어 있다(현재는 추가만 되고 삭제는 txt 파일을 직접 수정해야 한다).
4. pandas 모듈로 FIELD2 컬럼에서 로트번호(lot)가 일치하는 데이터를 Packing List로 만들고 pk_result.xlsx 파일을 생성했다.
5. openpyxl 모듈을 이용해 pk_result.xlsx 의 packing 시트에 제목(Packing List), 고객사를 표시하였다.
6. VBA로 별도의 pk_print.xlsm 엑셀 매크로 파일을 만들었다.
7. win32com 모듈을 이용해 pk_print.xlsm을 실행하였다. pk_print.xlsm 파일을 열리면 pk_result.xlsx의 내용을 가져와 프린터로 출력한다.

---

작성한 코드

import pandas as pd # pip install pandas
from openpyxl import load_workbook # pip install openpyxl
from openpyxl.styles import Font, Alignment
import os
import win32com.client # pip install pywin32
import pyodbc # pip install pyodbc
import xlwt # pip install xlwt, pip install xlrd
import tkinter
from math import *
from tkinter import *

# mdb를 xls로 변환
MDB = 'C:\pyworkspace\packing\IDCMAINDB.mdb'
DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
PWD = ''

# connect to db
con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()

# run a query and get the results 
SQL = """SELECT * FROM TWEIGHT""" # your query goes here
rows = cur.execute(SQL).fetchall()
cur.close()
con.close()

wb = xlwt.Workbook()
ws = wb.add_sheet("Weighing Data") # use table name for worksheet name
cols = ['WDATE', 'SEQ_NO', 'FIELD1', 'NET_WEIGHT', 'TARE_WEIGHT', 'GROSS_WEIGHT', 'UNIT_WEIGHT', 'BARCODE', 'FIELD2', 'FIELD3', 'FIELD4', 'OPERATOR'] # renamed colum headings
wbRow = 0 # counter for workbook row
i=0
while i<12:
    ws.write(wbRow, i, cols[i]) # write column heading to first row
    i+=1

for row in rows:
    wbRow += 1 # increment workbook row counter
    i=0
    while i<12:
        ws.write(wbRow, i, row[i])
        i+=1

# wb.save(os.path.abspath('./pk.xls'))
wb.save(r"C:\pyworkspace\packing\pk.xls")

# tkinter GUI적용
root=Tk()
root.title("PACKING LIST 출력")
root.geometry("300x300+100+100")
root.resizable(False, False)

# 로트번호 입력
lotLab = Label(root, text="로트번호")
lotLab.place(x=20, y=20)
txt = Entry(root)
txt.place(x=80, y=20, height=25)

# 고객사 추가
add_customerLab = Label(root, text="고객추가")
add_customerLab.place(x=20, y=50)
add_txt = Entry(root)
add_txt.place(x=80, y=50, height=25)

# 고객사 리스트 스크롤바
customerLab = Label(root, text="고객사")
customerLab.place(x=20, y=80)

frame=tkinter.Frame(root)

scrollbar=tkinter.Scrollbar(frame, orient="vertical")
scrollbar.pack(side="right", fill="y")

# 고객 리스트 정보 customer.txt 가져오기
f = open('customer.txt', 'r')
customerList = f.read().splitlines()
f.close()

listbox=tkinter.Listbox(frame, yscrollcommand = scrollbar.set)
for customer in customerList: listbox.insert(END, customer)
listbox.pack()

scrollbar["command"]=listbox.yview

frame.place(x=80, y=80, height=140)

# 회사 UI 적용
image = tkinter.PhotoImage(file=r'C:\pyworkspace\packing\UI_100.png')

ui = tkinter.Label(root, image=image)
ui.place(x = 95, y = 270)

# Packing list 작성
def pkPrint(lot,company):
    xl = pd.read_excel(r"C:\pyworkspace\packing\pk.xls")

    xl_mask = xl['FIELD2'] == lot
    filtered_xl = xl[xl_mask]
    pack = filtered_xl.groupby(['FIELD2','FIELD3','NET_WEIGHT'])['GROSS_WEIGHT'].agg(['count','sum'])

    GROSS_WEIGHT=filtered_xl.groupby(['FIELD2','FIELD3'])['GROSS_WEIGHT'].agg(['count','sum'],as_index=False).mean()
    print(GROSS_WEIGHT)

    path = os.path.abspath(r"C:\pyworkspace\packing\pk_result.xlsx")

    with pd.ExcelWriter(path) as writer:
        pack.to_excel(writer,'packing', startcol=0,startrow=2)
        GROSS_WEIGHT.to_excel(writer,'packing',header=False, startcol=6,startrow=2)

    wb = load_workbook(filename = path, read_only=False, data_only=False)
    ws = wb['packing']

    ws.merge_cells('A1:H1')
    ws['A1'] = 'Packing List'
    ws['A2'] = '고객사 : '
    ws['B2'] = company

    ca1 = ws['A1']
    ca1.font = Font(size=15, bold=True)
    wb.font = Font(size=15, bold=True)
    ca1.alignment = Alignment(horizontal='center', vertical='center')

    wb.save(path)

    # xlsx to PDF 
    excel = win32com.client.Dispatch("Excel.Application") # Microsoft Excel 열기
    
    # Excel 파일 읽기
    sheets = excel.Workbooks.Open(os.path.abspath(r"C:\pyworkspace\packing\pk_print.xlsm"))
    sheets.Worksheets[0]
    work_sheets = sheets.Worksheets[0]
    work_sheets.ExportAsFixedFormat(0,r'C:\pyworkspace\packing\pk_result.pdf') # Convert into PDF File(이 과정이 생략되면 인쇄 안됨)
    excel.Quit() # 엑셀 종료(이 과정이 생략되면 다시 실행시 에러 발생)

# 버튼 클릭 이벤트 핸들러
def okClick():
    lot = txt.get()
    SelectList = listbox.curselection()
    company=''.join([listbox.get(i) for i in SelectList]) # 리스트 값 문자열 변환
    pkPrint(lot,company)

def add_click():
    ctm = add_txt.get()

    # 고객 리스트 정보 추가
    f = open('customer.txt', 'a')
    f.write(ctm+'\n')
    f.close()

    listbox.delete(0, END)

    # 고객 리스트 정보 customer.txt 가져오기
    f = open('customer.txt', 'r')
    customerList = f.read().splitlines()
    f.close()

    for customer in customerList: listbox.insert(END, customer)

# 입력 버튼
btn = Button(root, text="OK", width=15, command=okClick)
btn.place(x=90, y=230)

add_btn = Button(root, text="추가", width=5, command=add_click)
add_btn.place(x=230, y=50)

root.mainloop()

---

개선사항

1. 리스트 고객사 삭제 기능
2. 잘못된 로트 번호 입력시 처리
3. 경로 문제(현재는 C:\pyworkspace\packing\ 에서만 실행 가능)