이번 업무자동화 포스팅에서는 같은 양식의 수많은 엑셀파일에서 필요한 값만 취합하는 프로그램을 작성해보려 한다. 예를 들면, 견적서같은 양식에서 고객사에 대한 정보를 한 파일로 취합할 때 사용할 수 있다. 실제 제조업에서 근무하는 사람이면 생산 제품에 대한 성적서, Report를 파일로 보관하기도 한다. 수많은 생산제품 중 특정 data를 취합하고 싶다면 아래 프로그램을 사용하여 쉽게 data를 취합할 수 있다. 아래 양식은 이전에 '같은 양식 여러 엑셀 파일 수정하기'라는 포스팅에서 이미지를 가져온 것이다.
(2021.03.07 - [코딩/업무자동화] - [파이썬/업무자동화] 같은 양식 여러 엑셀 파일을 한번에 수정하기 - openpyxl 모듈)
아래 컨셉으로 코드를 작성하고 포스팅을 진행해볼 예정이다.
Concept1. Random 함수를 통해 가상의 data 엑셀파일 500개를 생성하는 코드를 작성. 이 가상의 엑셀파일을 예시로 이번 포스팅을 진행할 것이다.
Concept2. 각 가상의 엑셀파일은 아래와 같은 양식을 가진다.
Concept3. 사용자는 엑셀파일에서 원하는 셀주소를 입력한다. ex) B2, B3, B4 등 엑셀 셀주소를 말함.
Concept4. 위 셀주소와 가상의 엑셀파일이 생성 된 폴더 주소를 입력받아서 한파일로 원하는 데이터만 취합한다.
1. 가상의 엑셀파일 생성 코드 : 랜덤 숫자 data가 입력 된 다랑 엑셀 파일 생성
랜덤 숫자 data를 생성하려면 random 모듈을 사용하면 된다. random 모듈은 파이썬 기본 내장 모듈로 숫자 data를 랜덤으로 생성하여 제공한다. 이 숫자 data들을 openpyxl 를 통해 엑셀에 입력하여 저장할 수 있다. random 모듈에 대한 설명은 이번 포스팅 주제에서 벗어나므로 설명은 생략하고 필요할 때 따로 정리해보도록 하겠다.
<전체 코드>
import random
import openpyxl as op
#가상 엑셀파일을 생성할 주소
path = r"생성할 주소"
#몇개 생성할것 인가?
n = 500
#random 모듈 및 openpyxl 통한 가상의 엑셀파일 및 랜덤 데이터 생성
for i in range(1,n+1):
wb = op.Workbook()
ws = wb.active
#random모듈의 randrage 함수를 통한 랜덤 숫자 data 생성 및 엑셀입력
for row_num in range(1,11):
ws.cell(row=row_num, column=1).value = str(row_num)+"번째 data"
ws.cell(row=row_num, column=2).value = random.randrange(1,100)
#엑셀 파일 저장
wb.save(path+"/"+str(i)+".xlsx")
<실행 결과>
2. 필요 모듈
필요 모듈은 기존에 많이 사용했던 os 모듈과 openpyxl 모듈을 사용한다. 2개 모듈에 대한 설명은 이전 업무자동화 포스팅에서도 많이 진행해서 이번 글에서는 생략하려고 한다.
<코드>
import openpyxl as op
import os
3. 함수 작성
이번 프로그램의 함수는 총 3가지이다. saveCellAddress, saveData, writeExcel이다. 함수명에서 보면 직관적으로 알 수 있겠지만 saveCellAdderess는 엑셀파일에서 data를 가져올 셀 주소를 저장하는 함수이다. saveData는 셀 주소를 입력받아 필요한 Data들을 딕셔너리로 반환한다. writeExcel은 취합한 Data를 엑셀에 정리하고 저장하는 함수이다.
1) saveCellAddress( )
saveCellAddress 함수는 위에서도 설명하였지만 엑셀에서 취합할 data의 셀주소를 직접 입력받아서 리스트로 반환하는 함수이다. 아래 코드 및 결과를 통해 확인해보도록 하자.
<코드>
#셀주소를 직접 입력받아 저장하는 함수
def saveCellAddress()->list:
num = int(input('몇개 입력받을거야? : '))
#엑셀 셀 주소를 저장하기 위한 빈 리스트 생성
save_cell = []
#입력받을 숫자만큼 데이터를 받고 for문 실행
for i in range(1,num+1):
print(str(i)+"번째 입력 : ")
cell = str(input('첫번째 셀 주소 입력 ex)A1,B1: '))
save_cell.append(cell)
print(save_cell)
return save_cell
<결과>
결과는 위에서 보면 알겠지만 사용자가 입력한 개수만큼 셀 주소를 입력하고 이를 리스트로 반환한다.
2) saveData(path, cell_list)
saveData 함수는 엑셀 파일들이 있는 경로와 위에서 저장한 셀주소 리스트를 입력받아서 필요한 data들을 저장한다. path는 엑셀 파일들이 있는 경로이고, cell_list는 1)에서 반환받은 엑셀 셀 주소 리스트이다.
<코드>
#엑셀 파일 있는 주소, Cell 주소 입력받아 정해진 위치 값들 딕셔너리로 저장
def saveData(path : str, cell_list : list):
excellist = os.listdir(path) #설정한 폴더경로에 있는 엑셀파일명을 리스트화
dic = {} #빈 딕셔너리 생성
#cell 주소당 빈 딕셔너리 생성
for cell in cell_list:
dic[cell] = ''
print(cell_list)
#설정한 cell 주소에 대해 모든 엑셀파일을 루프로 돌면서 데이터를 저장.
for cell in cell_list:
templist = [] # 빈 임시 리스트 생성
for file in excellist: #엑셀파일 list를 루프로 실행
wb = op.load_workbook(path + "/" + file)
ws = wb.active
templist.append(ws[cell].value) #빈 임시 리스트에 필요한 값을 가져옴.
dic[cell] = templist #임시 리스트를 딕셔너리화(key : cell주소, value : 값 리스트)
print(dic)
return dic
<결과>
결과 data가 복잡해보이지만 빨간색으로 표시된 부분을 보면 쉽게 이해할 수 있다. 결과는 딕셔너리이며, 딕셔너리의 Key는 사용자가 입력한 Cell주소이고, Value는 각 엑셀파일 루프를 돌아 읽어온 값이다. 딕셔너리에 대해서는 이전에 작성한 포스팅을 참고하면 좋을 것 같다.
(참고링크 : 2021.06.13 - [코딩/Python] - [Python/파이썬] 자료형 - Dictionary 1편 : 기초)
3) writeExcel(dic)
writeExcel 함수는 2)에서 결과로 반환한 딕셔너리를 입력받아서 새로운 액셀파일에 정리하여 저장한다. 코드에 대한 설명은 아래 주석으로 대신하도록 하겠다.
<코드>
#딕셔너리를 매개변수로 입력받아 새로운 엑셀파일에 취합하는 함수
def writeExcel(dic):
#새로운 엑셀파일 생성
wb = op.Workbook()
ws = wb.active
#엑셀파일 입력을 위한 reference
row_num=1 #행
col_num=1 #열
#딕셔너리 key값을 활용하여 for문 진행
for key in dic.keys():
#각 key값에 따른 리스트를 다시 for문 진행
for data in dic[key]:
ws.cell(row=row_num, column = col_num).value = data # 입력
row_num = row_num + 1 #행값 증가
col_num = col_num+1 #열값 증가
row_num = 1 #각 cell주소에 대한 data 입력이 끝나면 행=1로 초기화
#저장
wb.save("Result.xlsx")
print("생성 완료")
<결과>
위에서 입력했던 셀주소 B2, B3에 따라 2줄의 DATA가 각각 500개씩 입력하여 엑셀파일로 저장됐다.
4. 전체코드 및 .py파일
<전체코드>
import openpyxl as op
import os
#셀주소를 직접 입력받아 저장하는 함수
def saveCellAddress()->list:
num = int(input('몇개 입력받을거야? : '))
#엑셀 셀 주소를 저장하기 위한 빈 리스트 생성
save_cell = []
#입력받을 숫자만큼 데이터를 받고 for문 실행
for i in range(1,num+1):
print(str(i)+"번째 입력 : ")
cell = str(input('첫번째 셀 주소 입력 ex)A1,B1: '))
save_cell.append(cell)
print(save_cell)
return save_cell
#엑셀 파일 있는 주소, Cell 주소 입력받아 정해진 위치 값들 딕셔너리로 저장
def saveData(path : str, cell_list : list):
excellist = os.listdir(path) #설정한 폴더경로에 있는 엑셀파일명을 리스트화
dic = {} #빈 딕셔너리 생성
#cell 주소당 빈 딕셔너리 생성
for cell in cell_list:
dic[cell] = ''
#설정한 cell 주소에 대해 모든 엑셀파일을 루프로 돌면서 데이터를 저장.
for cell in cell_list:
templist = [] # 빈 임시 리스트 생성
for file in excellist: #엑셀파일 list를 루프로 실행
wb = op.load_workbook(path + "/" + file)
ws = wb.active
templist.append(ws[cell].value) #빈 임시 리스트에 필요한 값을 가져옴.
dic[cell] = templist #임시 리스트를 딕셔너리화(key : cell주소, value : 값 리스트)
print(dic)
return dic
#딕셔너리를 매개변수로 입력받아 새로운 엑셀파일에 취합하는 함수
def writeExcel(dic):
#새로운 엑셀파일 생성
wb = op.Workbook()
ws = wb.active
#엑셀파일 입력을 위한 reference
row_num=1 #행
col_num=1 #열
#딕셔너리 key값을 활용하여 for문 진행
for key in dic.keys():
#각 key값에 따른 리스트를 다시 for문 진행
for data in dic[key]:
ws.cell(row=row_num, column = col_num).value = data # 입력
row_num = row_num + 1 #행값 증가
col_num = col_num+1 #열값 증가
row_num = 1 #각 cell주소에 대한 data 입력이 끝나면 행=1로 초기화
#저장
wb.save("dic_Result.xlsx")
print("생성 완료")
if __name__ == "__main__":
path = r"엑셀 파일이 있는 경로"
cell = saveCellAddress()
dic = saveData(path, cell)
writeExcel(dic)
<첨부파일>
-. makefile.py : 랜덤 숫자 data를 가진 엑셀파일을 만드는 코드
-. collectData.py : data를 취합하는 코드
여기서는 다루진 않겠지만 취합한 data에 대해 통계적인 data를 확인하고 싶다면 pandas, numpy, matplotlib 같은 모듈들을 같이 활용하면 매우 유용할 것이다. 이번 포스팅은 여기서 끝.
'코딩 > 업무자동화' 카테고리의 다른 글
| [파이썬/업무자동화] 여러개 pdf 파일 1개로 합치기(+엑셀 응용) (2) | 2021.08.10 |
|---|---|
| [파이썬/업무자동화] 엑셀 전체 셀 내용 중 특정 문자만 변경/제거하기 (2) | 2021.07.27 |
| [파이썬/업무자동화] PyQt5 콤보박스 응용 - 엑셀 시트 리스트를 콤보박스화하기 (0) | 2021.07.07 |
| [파이썬/업무자동화] 파일명 규칙 따라 폴더 자동 분류하기 (8) | 2021.07.05 |
| [파이썬/업무자동화] 엑셀 데이터를 종류별 자동 분류하기 (13) | 2021.07.02 |
