조의금 정리하기(1)

이전에 생각한 방식대로 우선 여러 은행을 통해 입금된 내역에 대해 정리할 수 있도록 만들었다.

각 은행들은 대부분 입금 내역을 기간별로 설정하여 엑셀로 내보내기 할 수 있다.

본인이 얻은 '신한은행', '카카오뱅크', '농협은행'의 엑셀에서 얻은 입금 내역들을 하나의 엑셀로 정리하도록 하였다.

import os, pandas
import win32com.client
import openpyxl
import xlrd

global data_dir, bank_name, df_temp

data_dir = 'F:/VSC_Project/funeral_project/data'
#os.getcwd().replace('\\', '/')
# os.listdir(경로) 경로의 파일 리스트

def checking_excel_encrypted(file_name):
    if file_name.find('.xlsx') != -1:
        # xlsx 확장자 파일에 대해 암호화 확인
        try:
            xl = openpyxl.load_workbook(file_name, read_only=True)
            xl.close()
            return False
        except Exception as e:
            return True
    else:
        try:
            wb = xlrd.open_workbook(file_name, on_demand=True)
            wb.release_resources()
            return False
        except Exception as e:
            return True


# 농협 엑셀 : (9,E)=금액, (9,H) = 이름
# 신한 엑셀 : (8,E)=금액, (8,F) = 이름
def uf_excel_reader(file_name):
    '''
    매개변수로 받은 엑셀에 존재하는 이름, 금액을 추출하여
    결측치를 제거하고, 병합하여 하나의 데이터 프레임으로 리턴
    '''
    print('파일명 : ', file_name)
    print('위 이름의 엑셀을 엽니다.')
    print()
    excel_file_name = data_dir + '/' + file_name
    if checking_excel_encrypted(excel_file_name) == False:
        df_temp = pandas.read_excel(excel_file_name, header=None)
        data_start_row = 0
        money_col = 0
        name_col = 0
        if bank_name == '농협':
            data_start_row = 8
            money_col = 4
            name_col = 7
        elif bank_name == '신한':
            data_start_row = 7
            money_col = 4
            name_col = 5
        r_money = (df_temp[data_start_row:][money_col]) # 금액
        r_name = (df_temp[data_start_row:][name_col]) # 이름
        test = pandas.concat([r_name, r_money], axis=1)
        test.reset_index(inplace=True, drop=True)
        test.rename(columns={name_col:'이름', money_col:'금액'}, inplace=True)
        test = test.dropna(axis=0) # 결측치 제거
        return test
    else:
        xlApp = win32com.client.Dispatch("Excel.Application")
        xlApp.Visible = False
        print('파일명 : ' + file_name)
        excel_password = input('위 엑셀파일의 비밀번호를 입력하세요. >')

        try:
            book = xlApp.Workbooks.Open(excel_file_name, False, True, None, excel_password)
            ws = book.ActiveSheet
            for i in range(12, 38):
                print(ws.Cells(i, 4).Value, ws.Cells(i, 7).Value)
            xlApp.Quit()
            return 0
        except Exception as e:
            if str(e).find('암호가 잘못되었습니다.') != -1:
                print('비밀번호가 틀렸습니다.')
            else:
                print('오류 : ', e)
    
    


def uf_excel_writer(df_data):
    # 엑셀이 존재하지 않으면 생성, 존재한다면 시트1에 데이터 이어쓰기
    try:
        writer = pandas.ExcelWriter('result_data.xlsx', mode = 'a', if_sheet_exists='overlay', engine='openpyxl')
        last_row = writer.sheets['시트1'].max_row
        if last_row == 1:
            df_data.to_excel(writer, sheet_name = '시트1', index = False, encoding = 'utf-8', startrow = 0, startcol = 0)
            writer.close()
        else:
            df_data.to_excel(writer, sheet_name = '시트1', startrow = int(writer.sheets['시트1'].max_row), startcol = 0, index = False, header = None)
            writer.close()
    except Exception as e:
        if str(e).find('No such file') != -1:
            print(f'엑셀이 없어 생성합니다.')
        else:
            print(f'오류 : {e}')
        df_data.to_excel(
            excel_writer = 'result_data.xlsx',
            sheet_name = '시트1',
            index = False
            )


data_list = os.listdir(data_dir)
bank_name = '농협' # 파일명으로 은행 가를수 있는가?

for file_list in data_list:
    if file_list.find('농협') != -1:
        bank_name = '농협'
    elif file_list.find('신한') != -1:
        bank_name = '신한'
    elif file_list.find('카카오') != -1:
        bank_name = '카카오'
    else:
        bank_name = ''
    dataframe_result = uf_excel_reader(file_list)
    uf_excel_writer(dataframe_result)

8번째 줄의 data_dir에 엑셀 파일을 모아둘 수 있는 디렉토리 경로를 작성해야한다.

 - 본인은 F 드라이브 이하 VSC_Project .. 와 같이 엑셀 파일을 모아둔 경로를 작성하였다. 해당 부분을 수정하여 사용하는 것은 하드 코딩이나 다름없으니 사용자에게 어느 폴더에 엑셀을 모았는지 물어보는 것이 필요하다.

 

46~49번째 줄에 대해서 설명하면 추가적으로 다른 은행에 대한 엑셀을 얻어도 코드를 그대로 복사하고, 데이터의 시작행과 입금 금액, 입금자 열만 설정한다면 타 은행에 대한 정보도 추가 가능할 것으로 보인다.

 - 자동화 하려고 했으나, 각 은행별로 입금금액에 대한 명칭이 다르고, 위치도 다르니 개발자가 여러 은행에 대해 엑셀을 얻어보고 어느정도 정형화된 엑셀 내보내기를 파악해야한다.

 

65번째 줄에는 엑셀파일이 비밀번호에 걸려 있는 경우에 대해 예외처리하였다.

해당 부분에 대해서 시간이 많이 소모되었다. 처음엔 엑셀이 비밀번호 걸린지만 판단하면 된다고 생각했으나, xls 확장자와 xlsx 확장자는 다르게 판단해야함을 알게되어 여러번 수정을 거쳤다. ( 비밀번호가 걸려있는지 확인하기 위해 checking_excel_encrypted(file_name) 함수를 작성하였다. )

 

 

해당 글을 작성하면서 발전시켜야 할 부분들이 보였다.

간단하게는 사용자에게 비밀번호 입력이나 어떤 파일을 읽고 있는지에 대해서 프롬프트 창을 활용하도록 하였다.

사용자가 누구든 간에 본인이 작성한 코드를 테스트하는 것이 아니라면 프롬프트 창을 활용하는 것은 별로라고 배웠고, 그렇게 생각한다. 그러므로 위에서 이야기한 엑셀이 모여있는 폴더 설정과 비밀번호 작성 등의 부분을 UI를 이용해야 한다고 생각한다.

과거에 사용해봤던 PyQT를 이용하여 간단한 UI를 작성하고, 사용자가 보기에 조금 더 간단하게 만들어야 한다고 생각한다.