[파이썬(Python) 이야기 7화] Python을 통해 엑셀(Excel)파일을 생성하는 방법, 한번에 여러 개 파일 생성하기 openpyxl 라이브러리 이용하기

0. 엑셀을 생성한다고?

 

이전 작성된 포스트에서는 이미 만들어진 엑셀파일에 데이터를 넣는 방식으로 프로그램을 작성하였습니다. 하지만 이 방식은 항상 파일을 만들어 놓은 상태에서 데이터를 처리해야 하기에 다양한 결과를 여러 파일로 생성하는 것은 매우 불편한 구조입니다.

 

그래서 이번 포스트에서는 엑셀을 처음부터 만들어 데이터를 처리하는 방식을 알아보고자 합니다. 

 

1. openpyxl 이란?

 

openpyxl은 Excel 2010 이후 버전을 지원합니다. 엑셀은 2007년 이전에 xls 형식을 사용했고 이후에는 형식이 xlsx로  개편되었습니다. xlsx는 기존에 xls에 비해  대량 데이터 지원 가능해졌고 일부 SQL 기능도 이식되는 등 여러가지 장점이 있습니다. python에서는 이를 처리할 라이브러리가 부족했기에 openpyxl 탄생 배경이되었습니다. 

 

그럼 간단한 예제를 통해서 openpyxls를 이욯해 보겠습니다. 아래의 예시는 openpyxls를 통해 xlsx 파일을 생성하는 매우 간단한 코드입니다.  아래 코드를 실행시키면 원하는 위치에 엑셀파일이 생성되는 것을 확인할 수 있습니다. 구조는 workbook을 열고, workbook을 저장, workbook을 닫는 순서로 이뤄집니다. 이 때 worksheet는 자동으로 생성되게 되며, worksheet의 명칭은 Sheet로 생성됩니다. 

 

import openpyxl

wb = openpyxl.Workbook()
new_filenanme = 'C:/Users/ryuje/Desktop/test.xlsx' #경로명은 원하는데로 지정하면됩니다. 
wb.save(new_filenanme)
wb.close()

이번에는 worksheet의 명칭을 변경해 보겠습니다. 엑셀파일을 생성하면 worksheet가 자동으로 생성됩니다. 이 때 worksheet를 조작하기 위해서는 먼저 생성된 워크북을 지정해야합니다. 이를 지정하는 것에 자로 active입니다. 이를 수행하면 workbook에서 활성화된 worksheet를 가리킬 수 있습니다. 여기서는 worksheet가 하나이기 때문에 기본 worksheet가 지정됩니다. title은 기본 worksheet의 명칭을 지정하기 위한 것으로 아래 코드에서는 'Sheet1'로 명칭을 지정하였습니다. 나머지 과정은 위의 코드와 같습니다. 

 

import openpyxl

#예시1 엑셀파일 생성하기
wb = openpyxl.Workbook()  #! Workbook 반드시 대문자로 작성해야만 함
#현재 Active된 시트를 지정
ws = wb.active
#엑셀 파일의 첫 번째 워크 시트이름을 정의, 기본은 'Sheet'
ws.title  = 'Sheet1'
new_filenanme = 'C:/Users/ryuje/Desktop/test.xlsx'
wb.save(new_filenanme)
wb.close()

 

2. 여러 개의 파일을 한번에 생성해 보자!

 

이번에는 위의 코드를 사용해 함수를 생성, 여러 개의 파일을 한번에 만들어 보겠습니다. 기존에 openpyxl을 통해 엑셀파일을 읽어와 쓰는 방식보다, 새로 여러가지 파일을 생성할 수 있다면, 다양한 방식으로 다양한 결과를 처리해 각각 파일로 저장할 수 있겠죠?

 

본 코드에서는 엑셀파일을 여러 개 생성하는 것만 실습을 해보겠습니다.

 

import openpyxl

#예시2 여러 개의 엑셀파일 생성하기
DirectoryString = 'C:/Users/ryuje/Desktop/'
FileString = 'test'

# Excel_Export 함수
def Excel_Export(DirectoryString, FileString, index) :
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title  = 'Sheet1'
    # 엑셀 파일의 첫 번째 워크 시트이름을 정의, 기본은 'Sheet'
    new_filenanme = DirectoryString + FileString + str(i) + '.xlsx'
    wb.save(new_filenanme)
    wb.close()
    print(new_filenanme)

# Excel_Export 실행
for i in range(0,5) :
    Excel_Export(DirectoryString, FileString, i)

먼저 변수를 2개 생성했습니다. DirectoryString은 저장이 되는 폴더의 위치를 가리킵니다.(여러분들이 원하는 위치로 변경해 보세요~) 그리고 FileString은 기본이 되는 파일명을 가리킵니다. 결과는 test0.xlsx, test2.xlsx~ test4.xlsx이렇게 for문을 이용하여 총 5개의 파일을 생성할 것입니다. 

 

함수를 통해 아래와 같이 5번을 호출합니다. 

for i in range(0,5) :
    Excel_Export(DirectoryString, FileString, i)

 

그럼 함수에 대해서 알아보겠습니다. 

def Excel_Export(DirectoryString, FileString, index) :
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title  = 'Sheet1'
    # 엑셀 파일의 첫 번째 워크 시트이름을 정의, 기본은 'Sheet'
    new_filenanme = DirectoryString + FileString + str(i) + '.xlsx'
    wb.save(new_filenanme)
    wb.close()
    print(new_filenanme)

함수는 1번 설명에서 나타난 바와 같이 동작은 동일합니다. 다만 DirectoryString, FileString 그리고 파일 번호를 가리키는 index가 추가되었습니다. 그리고 마지막에 print를 통해 해당 파일이 출력되었는지 확인합니다. 

 

코드를 실행하면 위와 같이 결과가 출력되고 아래와 같이 한번에 5개의 파일이 생성되는 것을 확인할 수 있습니다. 

 

 

3. 다른 결과 출력해 보기

 

이번에는 지난 포스트에서 작성한 코드를 활용해 다른 예시를 만들어 보겠습니다. 포스트 5화에서는 여러 개의 엑셀파일을 하나의 엑셀로 합치는 코드를 작성했습니다. 이번에는 원하는 엑셀파일을 각각 다른 조건으로 합쳐보려 합니다. 

 

https://dataleader.tistory.com/35

[파이썬(Python) 이야기 5화] 폴더 내 엑셀(Excel) 파일 하나로 합치기

 

조건0 : 합칠 파일의 갯수는 3개

조건1 : 합칠 파일을 모두 합친다

조건2 : 합칠 파일을 2개만 합친다

결과 : 조건1과 조건2를 다른 엑셀파일로 출력한다.

 

참고할 코드는 아래와 같습니다. 5화에서 append 함수를 concat으로 변경해 처리하였으니 이점 주의바랍니다. 

import pandas
import os
import openpyxl

#1.  폴더내 파일을 검색하기
dir = 'C:/Users/ryuje/Desktop/PyProject/blog6' #디렉토리 위치
files = os.listdir(dir) #폴더에 있는 파일 정보를 가져오기

dataframeFile = pandas.DataFrame(index=range(0, 0), columns=['파일명', '이름', '확장자', '위치정보']) # 파일의 정보를 넣는 데이터 프레임 생성
def file_search(dir, dataframeFile):
    files = os.listdir(dir)
    for file in files:
        fullname_file = os.path.join(dir, file)
        fullname_file = fullname_file.replace("\\", "/")
        if os.path.isdir(fullname_file):
            dataframeFile = file_search(fullname_file, dataframeFile)  # 재귀함수 호출
        else:
            name, ext = os.path.splitext(file)
            dic_file = pandas.DataFrame({'파일명': file, '이름': name, '확장자': ext, '위치정보': fullname_file}, index=[0])
            dataframeFile = pandas.concat([dataframeFile, dic_file], ignore_index=True)
    # 데이터프레임 리턴
    return dataframeFile

dataframeFile = file_search(dir, dataframeFile) # 폴더내 파일을 재귀함수 호출 검색

#2. 폴더내 엑셀파일만 데이터 프레임에 남기기
xldataframe = dataframeFile.where(dataframeFile['확장자']=='.xlsx') #.xlsx만 별도로 추출
xldataframe = xldataframe.dropna() #Na 결측치 제거
xldataframe = xldataframe.reset_index() #index 재설정
print(xldataframe) # xldataframe 출력

#3. 파일 생성하기

DirectoryString = dir+'/'
FileString = 'result'

# Excel_Export 함수
def Excel_Export(DirectoryString, FileString, index) :
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title  = 'Sheet1'
    # 엑셀 파일의 첫 번째 워크 시트이름을 정의, 기본은 'Sheet'
    new_filenanme = DirectoryString + FileString + str(i) + '.xlsx'
    wb.save(new_filenanme)
    wb.close()
    print(new_filenanme)

for i in range(0, 2):  # Excel_Export 실행해 2개의 파일을 생성
    Excel_Export(DirectoryString, FileString, i)

#4. 각각 다른 데이터 입력하기
#4-1. 모든 파일 합치기
index = 0 #result0 파일에 저장
for i in range(0, len(xldataframe.index)):
     if i==0:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = XlsxData_dataframe
     else:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = pandas.concat([total_dataframe, XlsxData_dataframe], ignore_index = True)

writer = pandas.ExcelWriter(('%s%s%s.xlsx'%(DirectoryString, FileString, str(index))), engine = 'xlsxwriter') #생성된 데이터 쓰기
total_dataframe.to_excel(writer, sheet_name = 'Sheet1')
writer.save()
print("result0 완료")

#4-2. 두개의 파일만 합치기
index = 1 #result1 파일에 저장
for i in range(0, len(xldataframe.index)-1):
     if i==0:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = XlsxData_dataframe
     else:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = pandas.concat([total_dataframe, XlsxData_dataframe], ignore_index = True)

writer = pandas.ExcelWriter(('%s%s%s.xlsx'%(DirectoryString, FileString, str(index))), engine = 'xlsxwriter') #생성된 데이터 쓰기
total_dataframe.to_excel(writer, sheet_name = 'Sheet1')
writer.save()
print("result1 완료")

 

먼저 파일을 생성하는 부분을 살펴보면 2절에서 설명한 내용가 크게 차이는 없습니다. 결과파일을 result로 명명했고, 2개의 엑셀파일을 생성하는 코드입니다. 

#3. 파일 생성하기

DirectoryString = dir+'/'
FileString = 'result'

# Excel_Export 함수
def Excel_Export(DirectoryString, FileString, index) :
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title  = 'Sheet1'
    # 엑셀 파일의 첫 번째 워크 시트이름을 정의, 기본은 'Sheet'
    new_filenanme = DirectoryString + FileString + str(i) + '.xlsx'
    wb.save(new_filenanme)
    wb.close()
    print(new_filenanme)

for i in range(0, 2):  # Excel_Export 실행해 2개의 파일을 생성
    Excel_Export(DirectoryString, FileString, i)

 

파일을 생성한 후에 폴더에 있는 모든 xlsx파일을 result0에 출력을 하였습니다.

#4. 각각 다른 데이터 입력하기
#4-1. 모든 파일 합치기
index = 0 #result0 파일에 저장
for i in range(0, len(xldataframe.index)):
     if i==0:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = XlsxData_dataframe
     else:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = pandas.concat([total_dataframe, XlsxData_dataframe], ignore_index = True)

writer = pandas.ExcelWriter(('%s%s%s.xlsx'%(DirectoryString, FileString, str(index))), engine = 'xlsxwriter') #생성된 데이터 쓰기
total_dataframe.to_excel(writer, sheet_name = 'Sheet1')
writer.save()
print("result0 완료")

 

파일을 생성한 후에 폴더에 있는 두개의 파일(test1, test2)만  xlsx파일을 result1에 출력을 하였습니다. 위 코드와 다른 점은 아래 부분입니다. 코드에 보는바와 같이 하나의 인덱스를 제거하였습니다. 같은 코드를 비효율적으로 작성했지만, 이는 이해를 돕기 위함이었으니, 양해 부탁드립니다. 

for i in range(0, len(xldataframe.index)-1)

 

#4-2. 두개의 파일만 합치기
index = 1 #result1 파일에 저장
for i in range(0, len(xldataframe.index)-1):
     if i==0:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = XlsxData_dataframe
     else:
         XlsxData_dataframe = pandas.read_excel(xldataframe.iloc[i].loc['위치정보'], sheet_name="Sheet1")
         total_dataframe = pandas.concat([total_dataframe, XlsxData_dataframe], ignore_index = True)

writer = pandas.ExcelWriter(('%s%s%s.xlsx'%(DirectoryString, FileString, str(index))), engine = 'xlsxwriter') #생성된 데이터 쓰기
total_dataframe.to_excel(writer, sheet_name = 'Sheet1')
writer.save()
print("result1 완료")

 

마지막으로 결과를 확인해 보도록하겠습니다.  좌측이 result0이고 우측이 result1입니다. 

 

 

4. 맺은말

 

이번 포스트에서는 openpyxl를 통해서 엑셀파일을 생성하는 방법에 대해서 알아보았습니다. 기존의 방식은 파일을 생성하는 방식이 아니라서, 사람이 개입해야 하는 부분이 있었습니다. 이 방법을 이용하면 프로그래밍을 통해 여러 결과를 엑셀에 자동을 출력할 수 있습니다. 나중에 데이터분석이나 전처리 과정에 사용하면 좋을 것 같습니다. 마지막까지 읽어주셔서 감사합니다. 

 

여러분의 구독과 조아요는 포스트를 작성하는데 많은 도움이 됩니다. 

 

코드는 아래 링크를 통해 참고하세요

https://github.com/jtryuKo/openpcyxl_merge

GitHub - jtryuKo/openpcyxl_merge