파이썬 엑셀 업무 자동화 - xlwings

 

xlwings는 윈도우 기반 PC에서만 동작한다.

 

xw로 간편히 쓰기에 import를 아래와 같이 해보

import xlwings as xw

myexcel이라는 빈 엑세파일을 만들고

변수로 담아보자

보통 wb에 엑셀을 담는다.

새로운 엑셀파일을 생성하고 싶다면,

wb = xw.Book()

파일이 있다면,

wb = xw.Book('myexcel.xlsx')

요렇게 파일이 열리게 된다.

 

시트별로 작업을 하기 때문에

xw.Book으로 엑셀 파일을 지정하듯이

work sheet의 약자인 ws = wb.sheets['sheet1'] 로 표현해보자

 

sheet name을 확인하고 싶으면 아래와 같이 사용하면되고

확장자는 리스트이다.

wb.sheet_names

sheet를 추가하기 위해서는 wb에서 sheets.add를 사용하면 된다.

wb.sheets.add('A')
wb.sheets.add('B')

 

이제 배운 내용을 가지고

4가지를 순차적으로 해보자.

 

1. 엑셀 파일을 새로 생성하세요
2. productA, productB의 이름을 가진 시트를 추가하세요
3. 엑셀파일에 존재하는 모든 시트의 이름을 출력하세요
4. 'project1.xlsx'로 생성한 엑셀 파일을 저장하세요

 

# 새로운 엑셀 파일 생성하기
wb = xw.Book()
# sheet 추가하기
wb.sheets.add("productA")
wb.sheets.add("productB")
# 모든 이름 출력하기
for name in wb.sheet_names:
    print(name)
# 엑셀파일 저장
wb.save('project1.xlsx')
wb.close

 

셀을 다루는 법을 알아보자.

 

value를 통해 값을 변경 가능하다.

# 해당 셀의 값 가져오기
ws.range('A1').value

# 해당 셀에 값 채워넣기
ws.range('A1').value = '값'

# 해당 셀에 엑셀 고유의 함수 넣기
ws.range('A3').value = '=SUM(A1:A2)'

# 엑셀의 첫 행 찾기 
first_row = ws.range('A1').end('up').row

# 엑셀의 최대 행 찾기 
last_row = ws.range('A1').end('down').row

# 엑셀의 첫 열 찾기 
first_col = ws.range('A1').end('left').column

# 엑셀의 최대 열 찾기
last_col = ws.range('A1').end('right').column

# A1에서 시작하는 테이블 불러오기
ws.range('A1').expand('table').value

# 1행과 3행 사이에 빈 행 추가
ws.range('1:3').insert()

# A열과 C열 사이에 빈 행 추가
ws.range('A:C').insert()

# A1셀과 C1셀 병합
ws.range('A1:C1').merge()

여러 범위로 변경이 가능한데

 

# A1을 기준으로 열 추가
sheet['A1'].value = [[1],[2],[3],[4],[5]]

# A1을 기준으로 행 추가
sheet['A1'].value = [1, 2, 3, 4, 5]

sheet['A1'].value = [['Foo 1', 'Foo 2', 'Foo 3'], [10, 20, 30]]

만약에 특정 범위만 작업하고 싶은경우

range를 통해서 해당 범위만 선택하여 작업도 가능하다.

 

 

 

폰트 크기 및 색상도 쉽게 변경이 가능하다.

# 선택 범위 볼드체 변경
ws.range('A1').font.bold = True

# 선택 범위 이탤릭체 변경
ws.range('A1').font.italic = True

# 선택 범위 폰트사이즈 20 변경
ws.range('A1').font.size = 20

# 선택 범위 폰트 컬러 RGB (0, 0, 255) 변경
ws.range('A1').font.color = (0, 0, 255)

# 선택 범위 셀 컬러 RGB (0, 0, 255) 변경
ws.range('A1').color = (0, 0, 255)

 

 

셀 복사 및 붙혀넣기 

# 해당 범위 수식 포함 복사
ws.range('A1:C10').copy()

# 해당 범위에 수식 포함 붙혀넣기
ws.range('E1').paste()

# 해당 범위에 값만 붙혀넣기
ws.range('E1').paste(paste='values')

 

 

 

 

 

 

 

아래 예제를 한번 수행해보

 

1. 파일명 '판매현황.xlsx'인 엑셀 파일을 불러오세요
2. Sheet1(기본시트)의 A1, B1 셀에 '제품명', '판매량' 열을 추가하세요 
3. 2에서 추가된 열 아래에 제품명, 가격 데이터 ['아메리카노', 500], ['카페라떼', 700], ['카푸치노', 400] 를 입력하세요
4. B5 셀에 모든 제품 가격의 합을 입력하고 그것을 주피터 노트북에 출력하세요

myexcel.xlsx

0.01MB

project1.xlsx

0.01MB

판매현황.xlsx

0.01MB

 

 

그러면 배운 내용을 가지고 이렇게 쉽게 코드를 작성할 수 있다.

#1
wb=xw.Book('판매현황.xlsx')
#2
ws=wb.sheets['Sheet1']
ws.range('A1').value='제품명'
ws.range('B1').value='판매량'
#3
ws.range('A2').value=[['아메리카노', 500], ['카페라떼', 700], ['카푸치노', 400] ]
#4
ws.range('B5').value="=SUM(B2:B4)"
print(ws.range('B5').value)

 

외부 데이터 라이브러리 이용하여 엑셀 다루기

numpy 및 pandas를 이용하여 한번 엑셀에 응용을 해보자

 

 

 

 

 

 

 

 

 

 

다음의 example엑셀 파일을 통해 

실무에서 적용할 만한 파이썬을 이용한 엑셀 실습을 해보자

example.xlsx

0.01MB

 

 

배운대로 xlwings를 import하고

엑셀을 xw.Book을 통해서 불러와서 열어서

편집을 하기 위한 준비를 해보

import xlwings as xw
wb = xw.Book('example.xlsx')
sh1 = wb.sheets['사원명부']
sh2 = wb.sheets['재직증명서']
print(sh1.name)
print(sh2.name)

team =sh1['f2:f26'].value

 

 

사원 명부 시트에 있는 특정 사람 C29와 B29에 해당하는 특징이 맞는 사람을 찾아

명수를 구하는 코드를 짜보자

# 특정 사람 찾아 인원을 구하기
count = 0
for i in data:
    if i[2] == sh1['c29'].value and i[5]==sh1['B29'].value:
        count = count+1
        print(i[1])
print("기획실 소속의 여성은", count, '명')
sh1['d29'].value=count

 

이제 해당 특정 사람중에서 가장 나이가 많은 사람을 구하는 코드를 짜보자

# 가장 나이가 많은 사람
people=[]
for row in data:
    if row[2] == sh1['c29'].value and row[5]==sh1['b29'].value:
        people.append(row)

limit = 999999999999999999.0
oldest =[]
for row in people:
    if row[3] < limit:
        limit=row[3]
        oldest=row
print(oldest[1])     
sh1['e29'].value=oldest[1]

 

모든 부서의 사람들의 총 근무 년 수를 확인하고, 해당하는 셀에 작성하는 코드를 작성해보자

now = datetime.today().year
count =2
for row in data:
    row[8]=now-row[7].year
    sh1.range(f'I{count}').value = row[8]
    count += 1

 

이번에는 사원 명부 sheet에서 선정된 사람의 정보를 이용하여 재직증명서 시트를 작성하는 코드를 작성해보자

 

이렇게 파이썬을 이용해서 엑셀을 다루는 법을 확인했는데

 xlwings는 엑셀을 open한 상태로 수정하기 때문에

상대적으로 느릴 수 밖에 없고

엑셀을 열지 않고 파일을 만드는 라이브러리도 있다

openpyxl 라는 라이브러리인데

이는 xlwings보다 100배 이상 빠르고

기본 문법도 매우 유사하다.

다만 보안관련해서 보안에 민감한 회사보다 개인적으로 사용하기에 유용하니

참고하자

from openpyxl import Workbook
from openpyxl import load_workbook
import numpy as np

# 엑셀파일 쓰기
write_wb = Workbook()

# 이름이 있는 시트를 생성
write_ws = write_wb.create_sheet('생성시트')

# Sheet1에다 입력
write_ws = write_wb.active
write_ws['A1'] = '숫자'

#행 단위로 추가
write_ws.append([1,2,3])

#셀 단위로 추가
write_ws.cell(5, 5, '5행5열')
write_wb.save("숫자.xlsx")