본문 바로가기
코딩/파이썬

파이썬으로 여러개의 엑셀 파일을 합병하기

뱁새의이중장부 2022. 7. 19. 19:22
반응형

테스트1.xlsx
0.01MB
테스트2.xlsx
0.01MB

안녕하세요.

오늘은 파이썬으로 여러개의 엑셀 파일을 합병해보겠습니다.

 

 

1. 개요

  • openpyxl 라이브러리를 사용합니다.
  • 다수의 엑셀 파일을 합병하고 데이터를 합산합니다.
  • 합병 후 셀 합병, 연산, 배경 색 지정 등 기본적인 서식 지정 기능을 탑재하였습니다.
  • 용도에 따라 완벽한 자동화는 불가능할수도 있습니다.
  • 첨부된 테스트1, 테스트2 파일을 다운받아주세요.

 

2. 전체 코드

#라이브러리 포함하기
import openpyxl
import os
from openpyxl.styles import Font, Alignment, PatternFill, Color, Border, Side

#파일 경로 지정하기
#파일 확장자 지정
file_format = ".xlsx"

#합병할 엑셀 파일의 경로 지정
file_path = "병합할 엑셀 파일의 경로"

#합병할 파일의 목록을 리스트 형태로 저장하기
file_list = [f"{file_path}/{file}" for file in os.listdir(file_path) if file_format in file]


#엑셀 파일 합병할 준비
#엑셀 파일 열어주기
wb_new = openpyxl.Workbook()

#합병할 엑셀 데이터를 저장할 시트
ws1 = wb_new.active
#결과 엑셀 데이터를 저장할 시트
ws2 = wb_new.create_sheet("result")
#불러온 파일의 수를 변수에 저장
num = len(file_list)


#엑셀 병합
#엑셀 시작 지점
i = 2

#엑셀 병합 작업
for file_ss in file_list:
    
    wb_old=openpyxl.load_workbook(file_ss)
    ws=wb_old.worksheets[0]

    for j in range(0, ws.max_column):
       for k in range(0, 11):
           ws1.cell(i+k, j+1).value = ws.cell(k+2, j+1).value
    i+=12

#기본 틀만들기
#틀 만들기 2행, 3행
for q in range(2, 4):
    for k in range(2, 9):
        ws2.cell(q, k).value = ws1.cell(q, k).value

#틀 만들기 학기, 코멘트
for q in range(0, num):
    ws2.cell(row=2, column=9+q).value = ws1.cell(row=14+q*12, column=8).value

#틀 만들기 2열~5열
for q in range(0,5):
    for k in range(3, 13):
        ws2.cell(row=k, column=q+1).value = ws1.cell(row=k, column=q+1).value

#학기, 코멘트 내용 추가
for q in range(4, 13):
    for k in range(0, num):
        ws2.cell(row=q, column=8+k).value = ws1.cell(row=q+k*12, column=8).value


#ws1시트의 빈칸에 숫자 0 넣기
for q in range(4, 13):
    for k in range(6, 8):
        for j in range(0, num):
            if ws1.cell(q+j*12, k).value == None:
                ws1.cell(q+j*12, k).value = 0
                
#ws2 시트의 빈칸에 숫자 0 넣기
for q in range(4, 13):
    for k in range(6, 8):
        if ws2.cell(q, k).value == None:
            ws2.cell(q, k).value = 0


#숫자 연산하기
for q in range(4, 13):
    for k in range(6, 8):
        for j in range(0, num):
            ws2.cell(q, k).value = ws2.cell(q, k).value + ws1.cell(q+j*12, k).value


# 셀 병합하기
#병합
ws2.merge_cells('B2:E2')

'''
#병합 : 일자 28, 29열
for q in range(10, 10+num):
    ws2.merge_cells(start_row=28, start_column=q, end_row=29, end_column=q)      
'''


#셀 정렬하기
#중앙 정렬
ws2.cell(2, 2).alignment = Alignment(horizontal='center')

for k in range(2, 9):
    ws2.cell(3, k).alignment = Alignment(horizontal='center')

for q in range(4, 13):
    for k in range(3, 6):
        ws2.cell(q, k).alignment = Alignment(horizontal='center')

#학기, 코멘트 부분 정렬
for k in range(8, 8+num):
    ws2.cell(2, k).alignment = Alignment(horizontal='right')
    
for k in range(8, 8+num):
    ws2.cell(3, k).alignment = Alignment(horizontal='center')

#우측 정렬
for q in range(4, 13):
    ws2.cell(q, 2).alignment = Alignment(horizontal='right')
    
for q in range(4, 13):
    for k in range(6, 8):
        ws2.cell(q, k).alignment = Alignment(horizontal='right')


#셀 배경 색 채우기
ws2.cell(2, 2).fill = PatternFill(fill_type='solid',
                      fgColor=Color('F2F2F2'))

for q in range(2, 13):
    ws2.cell(q, 2).fill = PatternFill(fill_type='solid',
                      fgColor=Color('F2F2F2'))

for k in range(2,8+num):
    ws2.cell(3, k).fill = PatternFill(fill_type='solid',
                      fgColor=Color('F2F2F2'))


#셀 테두리 설정
BORDER_THIN = 'thin'
for q in range(2, 13):
    for k in range(2, 8+num):
        ws2.cell(q, k).border = Border(
                        left=Side(border_style=BORDER_THIN,
                                    color='000000'),
                        right=Side(border_style=BORDER_THIN,
                                    color='000000'),
                        top=Side(border_style=BORDER_THIN,
                                    color='000000'),
                        bottom=Side(border_style=BORDER_THIN,
                                    color='000000'))


#셀 폰트 설정
ws2.cell(2, 2).font = Font(name='굴림체',
                            size=18)

for q in range(3, 13):
    for k in range(2, 8+num):
        ws2.cell(q, k).font = Font(name='굴림',
                                    size=11)


#셀 높이, 너비 설정
ws2.row_dimensions[1].height = 6.6
ws2.column_dimensions['A'].width = 1.1

ws2.row_dimensions[2].height = 24

ws2.column_dimensions['B'].width = 3.8
ws2.column_dimensions['C'].width = 7
ws2.column_dimensions['D'].width = 15
ws2.column_dimensions['E'].width = 12
ws2.column_dimensions['F'].width = 9
ws2.column_dimensions['G'].width = 12


# 최종 파일 저장
wb_new.save("저장할 경로/파일명.xlsx")

 

 

3. 필요 라이브러리

코드를 실행하기 위해서는 openpyxl이라는 라이브러리가 필요합니다.

라이브러리를 설치하기 위해서는 명령 프롬프트를 열어줘야 합니다.

[윈도우 버튼] + “키보드로 cmd 입력” + “명령 프롬프트 실행”

위 방법으로 명령프롬프트를 실행시켜줍니다.

명령 프롬프트 실행 후 아래 openpyxl의 라이브러리 설치 명령어를 입력하면 됩니다.

pip install openpyxl

 

 

4. 사용방법

(1) 열어줄 파일의 경로를 지정해줍니다.

#합병할 엑셀 파일의 경로 지정
file_path = "병합할 엑셀 파일의 경로"

file_path 변수에 합병할 엑셀 파일이 저장된 폴더의 경로를 저장해줍니다.

경로를 모르겠으면 아래와 같이 합병할 엑셀 파일이 저장된 폴더의 주소창을 누르면 경로를 복사할 수 있습니다.

여기서 주의사항이 있습니다.

폴더의 주소창에서 경로를 복사하면 아래와 같이 경로가 역슬래시로 구분됩니다.

C:\Users\bebsae\바탕 화면

하지만 파이썬에서는 아래와 같이 슬래시로 경로를 구분해줘야합니다.

C:/Users/bebsae/바탕 화면

file_path 변수에 경로를 저장해줄 때 꼭 정방향 슬래시인지 확인해주세요.

 

(2) 파일을 저장할 경로를 지정해줌

# 최종 파일 저장
wb_new.save("저장할 경로/파일명.xlsx")

save 함수에 파일을 저장할 경로와 파일명을 지정해줍니다.

 

(3) 코드 실행

이제 코드를 실행하면 됩니다.

 

 

5. 코드 설명

(1) 라이브러리 불러오기

#라이브러리 포함하기
import openpyxl
import os
from openpyxl.styles import Font, Alignment, PatternFill, Color, Border, Side

파이썬에서 라이브러리를 불러오기 위해서는 import 명령어를 이용해야 합니다.

엑셀을 다루기 위해 openpyxl 라이브러리와 os 라이브러리를 불러옵니다.

 

(2) 불러올 파일의 경로를 지정하기

#파일 경로 지정하기
#파일 확장자 지정
file_format = ".xlsx"

#합병할 엑셀 파일의 경로 지정
file_path = "병합할 엑셀 파일의 경로"

#합병할 파일의 목록을 리스트 형태로 저장하기
file_list = [f"{file_path}/{file}" for file in os.listdir(file_path) if file_format in file]

우리는 xlsx파일을 예시로 하겠습니다.

file_format 변수에 .xlsx 대신 .csv라는 값을 넣어주면 됩니다.

file_path 변수에는 병합할 엑셀 파일의 경로를 지정해줍니다.

마지막으로 file_list 변수에 불러올 파일들을 리스트 형태로 저장해줍니다.

 

(3) 파일 병합 준비

#엑셀 파일 합병할 준비
#엑셀 파일 열어주기
wb_new = openpyxl.Workbook()

#합병할 엑셀 데이터를 저장할 시트
ws1 = wb_new.active
#결과 엑셀 데이터를 저장할 시트
ws2 = wb_new.create_sheet("result")
#불러온 파일의 수를 변수에 저장
num = len(file_list)

openpyxl의 Workbook()함수를 이용해서 가상의 엑셀 파일을 열어줍니다.

active 함수를 이용해서 가상의 엑셀파일에 열려있는 시트를 선택해줍니다.

create_sheet 함수를 통해서 새로운 시트를 만들수도 있습니다.

우리는 ws1 시트에 합병할 데이터를 모아주고, ws2 시트에 합병한 데이터를 저장해주겠습니다.

마지막으로 num이라는 변수에 file_list에 담긴 파일의 수를 저장해줍니다.

 

(4) 엑셀 파일 병합하기

#엑셀 병합
#엑셀 시작 지점
i = 2

#엑셀 병합 작업
for file_ss in file_list:
    
    wb_old=openpyxl.load_workbook(file_ss)
    ws=wb_old.worksheets[0]

    for j in range(0, ws.max_column):
       for k in range(0, 11):
           ws1.cell(i+k, j+1).value = ws.cell(k+2, j+1).value
    i+=12

이제 본격적으로 엑셀 파일을 병합하겠습니다.

i라는 변수에 데이터가 시작되는 행번호를 지정해줍니다.

for 문을 이용해 file_list에 들어있는 파일들을 순서대로 열어줍니다.

이때 file_list에 들어있는 파일명을 file_ss라는 변수에 순서대로 담아서 명령어를 실행합니다.

load_workbook 함수를 이용하여 wb_old라는 변수에 열어준 엑셀 파일을 담아줍니다.

wb_old.worksheets[0]의 의미는 wb_old 변수에 열어준 엑셀 파일의 첫번째 워크시트를 지정하라는 의미이며 이를 ws라는 변수에 담아줍니다.

 

이제 for문을 이용해 ws라는 시트에 담긴 데이터들을 ws1이라는 시트로 옮겨줍니다.

i += 12는 아까 우리가 i라는 변수에 데이터가 시작되는 열이 2열이기 때문에 숫자 2를 저장한것을 기억할겁니다.

우리가 가진 파일은 12열 까지 데이터를 갖고 있습니다.

첫번째 엑셀파일의 데이터를 모두 저장했으면, 2+12열 부터 데이터를 써주기 위해서 변수를 바꿔줍니다.

 

(5) 데이터 양식 만들기

#기본 틀만들기
#틀 만들기 2행, 3행
for q in range(2, 4):
    for k in range(2, 9):
        ws2.cell(q, k).value = ws1.cell(q, k).value

#틀 만들기 학기, 코멘트
for q in range(0, num):
    ws2.cell(row=2, column=9+q).value = ws1.cell(row=14+q*12, column=8).value

#틀 만들기 2열~5열
for q in range(0,5):
    for k in range(3, 13):
        ws2.cell(row=k, column=q+1).value = ws1.cell(row=k, column=q+1).value

#학기, 코멘트 내용 추가
for q in range(4, 13):
    for k in range(0, num):
        ws2.cell(row=q, column=8+k).value = ws1.cell(row=q+k*12, column=8).value

우리는 정해진 양식에 데이터를 집어넣어야 합니다.

ws1 시트에서 ws2 시트로 양식을 불러와서 저장해주도록 합니다.

위 코드를 실행하면 아래와 같은 양식이 만들어집니다.

코드를 조금 더 뜯어보자면

for q in range(2, 4):
    for k in range(2, 9):
        ws2.cell(q, k).value = ws1.cell(q, k).value

위 코드를 이용하면 2행부터 3행까지, 2열부터 8열까지의 데이터를 ws1셀로부터 ws2 셀로 복사해줍니다.

ws1.cell(행, 열).value라는 명령어는

ws1시트에서 지정된 행, 열 주소의 셀(cell) 값(value)을 불러오라는 의미입니다.

 

(6) 숫자 연산하기

#ws1시트의 빈칸에 숫자 0 넣기
for q in range(4, 13):
    for k in range(6, 8):
        for j in range(0, num):
            if ws1.cell(q+j*12, k).value == None:
                ws1.cell(q+j*12, k).value = 0
                
#ws2 시트의 빈칸에 숫자 0 넣기
for q in range(4, 13):
    for k in range(6, 8):
        if ws2.cell(q, k).value == None:
            ws2.cell(q, k).value = 0


#숫자 연산하기
for q in range(4, 13):
    for k in range(6, 8):
        for j in range(0, num):
            ws2.cell(q, k).value = ws2.cell(q, k).value + ws1.cell(q+j*12, k).value

openpyxl을 이용하면 각 셀들을 하나씩 연산해줘야 합니다.

게다가 빈 셀의 값은 0이 아닌 None으로 인식하기 때문에 빈 셀과 숫자값이 들어있는 셀을 연산하기 위해서는

빈 셀에 숫자 0을 넣어줘야 합니다.

 

참고로 ws2 시트의 빈칸에도 숫자 0을 넣어주는 이유는

#숫자 연산하기
for q in range(4, 13):
    for k in range(6, 8):
        for j in range(0, num):
            ws2.cell(q, k).value = ws2.cell(q, k).value + ws1.cell(q+j*12, k).value

위 명령어의 연산 부분에서 ws2 시트의 데이터와 ws1 시트의 데이터를 연산하는데,

처음 만들어진 ws2 시트에는 숫자값이 없이 빈칸만 있기 때문입니다.

 

(7) 셀 병합하기

# 셀 병합하기
#병합
ws2.merge_cells('B2:E2')

'''
#병합 : 일자 28, 29열
for q in range(10, 10+num):
    ws2.merge_cells(start_row=28, start_column=q, end_row=29, end_column=q)      
'''

셀을 병합할때는 merge_cells라는 함수를 사용해줍니다.

이때 다수의 셀 주소를 합병할때는 start_row, start_column, end_row, end_column 값을 모두 지정해주어야 합니다.

 

(8) 셀 정렬하기

#셀 정렬하기
#중앙 정렬
ws2.cell(2, 2).alignment = Alignment(horizontal='center')

for k in range(2, 9):
    ws2.cell(3, k).alignment = Alignment(horizontal='center')

for q in range(4, 13):
    for k in range(3, 6):
        ws2.cell(q, k).alignment = Alignment(horizontal='center')

#학기, 코멘트 부분 정렬
for k in range(8, 8+num):
    ws2.cell(2, k).alignment = Alignment(horizontal='right')
    
for k in range(8, 8+num):
    ws2.cell(3, k).alignment = Alignment(horizontal='center')

#우측 정렬
for q in range(4, 13):
    ws2.cell(q, 2).alignment = Alignment(horizontal='right')
    
for q in range(4, 13):
    for k in range(6, 8):
        ws2.cell(q, k).alignment = Alignment(horizontal='right')

셀을 정렬하기 위해서는 alignment 함수를 이용해야 합니다.

horizontal 옵션은 right, center 등 가로 위치를 지정해주는 옵션입니다.

vertical 옵션은 center, bottom 등 세로 위치를 지정해주는 옵션입니다.

 

(9)셀 배경 색 채우기

#셀 배경 색 채우기
ws2.cell(2, 2).fill = PatternFill(fill_type='solid',
                      fgColor=Color('F2F2F2'))

for q in range(2, 13):
    ws2.cell(q, 2).fill = PatternFill(fill_type='solid',
                      fgColor=Color('F2F2F2'))

for k in range(2,8+num):
    ws2.cell(3, k).fill = PatternFill(fill_type='solid',
                      fgColor=Color('F2F2F2'))

fill 함수를 이용해서 셀 배경 색을 채울 수 있습니다.

색상은 색상코드를 이용해서 지정해줄 수 있습니다.

 

(10) 셀 테두리 설정

#셀 테두리 설정
BORDER_THIN = 'thin'
for q in range(2, 13):
    for k in range(2, 8+num):
        ws2.cell(q, k).border = Border(
                        left=Side(border_style=BORDER_THIN,
                                    color='000000'),
                        right=Side(border_style=BORDER_THIN,
                                    color='000000'),
                        top=Side(border_style=BORDER_THIN,
                                    color='000000'),
                        bottom=Side(border_style=BORDER_THIN,
                                    color='000000'))

BORDER_THIN = 'thin' 형태로 옵션을 지정해주면 됩니다.

또한 테두리 색도 색상코드를 이용해서 지정해주면 됩니다.

 

(11) 셀 폰트 설정

#셀 폰트 설정
ws2.cell(2, 2).font = Font(name='굴림체',
                            size=18)

for q in range(3, 13):
    for k in range(2, 8+num):
        ws2.cell(q, k).font = Font(name='굴림',
                                    size=11)

font 함수를 이용하면 폰트를 설정해줄 수 있습니다.

name 옵션으로 폰트 이름을 지정해주면 되고,

size  옵션으로 폰트 사이즈를 지정해줍니다.

 

(12) 셀 높이, 너비 설정

#셀 높이, 너비 설정
ws2.row_dimensions[1].height = 6.6
ws2.column_dimensions['A'].width = 1.1

ws2.row_dimensions[2].height = 24

ws2.column_dimensions['B'].width = 3.8
ws2.column_dimensions['C'].width = 7
ws2.column_dimensions['D'].width = 15
ws2.column_dimensions['E'].width = 12
ws2.column_dimensions['F'].width = 9
ws2.column_dimensions['G'].width = 12

row_dimensions[].height는 셀 높이를,

column_dimensions[].width는 셀 너비를 지정해줍니다.

 

(13) 병합한 파일 저장하기

# 최종 파일 저장
wb_new.save("저장할 경로/파일명.xlsx")

wb_new 변수에 담긴 엑셀 파일을 save 함수를 이용해 저장해줍니다.

저장할 파일의 이름도 지정해주어야 하는 것 꼭 기억해주세요.

 

 

6. 마치며

위 코드는 완벽하지 않습니다.

왜냐하면 코멘트 셀의 너비를 직접 조절해줘야 하기 때문입니다.

분명 방법이 있겠지만 그 부분은 여러분에게 숙제로 남겨두겠습니다.

각자가 가진 엑셀 파일의 양식에 맞게 코드를 수정해서 사용하시면 되겠습니다.

감사합니다.

반응형
저작자표시 비영리 변경금지

'코딩 > 파이썬' 카테고리의 다른 글

파이썬 sort 함수를 알아보자  (0) 2022.09.08
파이썬 map 함수를 알아보자  (0) 2022.09.07
파이썬으로 여러개의 한글 파일을 PDF 파일로 변환하기(hwp to pdf)  (8) 2022.07.06
Do it! 파이썬 생활프로젝트 후기  (0) 2022.05.25
파이썬 - for 문 사용하는 방법  (0) 2022.05.23

'코딩/파이썬' Related Articles

티스토리툴바