7교시는 엑셀을 다루는 부분에 대해서 얘기하려고 한다. 보통 프로그램에서 나온 결과를 텍스트나 디비로 많이 저장하지만, 엑셀로 저장하게 되면, 여러가지 엑셀의 기능을 이용해서 정렬해 보기도 쉽기 때문에, 나름 장점이 있는 듯 한다.
[목차]
0. 왜 파이썬 공부에 구글을 이용하는게 좋은가?
1. 언어를 바라보는 방법. 파이썬을 어떻게 바라봐야 할까?
2. 파이썬 설치와 환경, 버전 선택 하기의 이유.
3. 만들고자 하는 기능을 모르는 조각으로 나눠 조사해 보기
4. 데이터 베이스에서 내용 가져와 출력하기
5. 암호화 모듈을 이용해 암복호화 해보기
6. 퍼즐 조각들을 합쳐보기
7. 엑셀 파일 사용해 보기 -> 부록 : fuction 을 이용해서, 코드 정리해 보기
8. 정규표현식을 왜 사용해야 할까? 언어속의 미니 언어 정규표현식 살펴보기
9. 입력과 결과를 GUI 화면과 연결해 보기
10. Whois API 이용해 보기
11. 웹페이지 호출해 내용 파싱 하기(BeautifulSoup 그리고 한계)
12. 자동화 - 웹 자동화(with Selenium)
13. 자동화 - 윈도우즈 GUI 자동화(with pywinauto)
14. 자동화 - 작업 자동화
15. 수학 라이브러리 살펴보기
16. 그래픽 라이브러리 살펴보기
17. 머신러닝에서의 파이썬의 역활
18. 웹 프로그래밍 - Legacy Web
19. 웹 프로그래밍 - Flask 살펴보기(feat. d3.js)
20. 웹 프로그래밍 - Django 살펴보기
21. 정리 - 이런저런 이야기
[엑셀 모듈 설치]
언제나처럼 구글에 검색을 시작한다. 'python 3 excel' 라고 검색하면, 제일 위의 결과에 파이썬에서 쓸수 있는 엑셀 모듈들에 대해 정리한 사이트가 나온다.
쭈루룩 5개정도의 모듈이 나오는데, 맨 위에 있는 OpenPyXl 을 써보기로 하자. 모듈을 설치하기 위해서 구글에서 'install openpyxl python3 pip' 로 검색하여 아래의 스택오버플로우 페이지를 참고한다.
http://stackoverflow.com/questions/38364404/how-to-install-openpyxl-with-pip
cmd 창에서 'pip install openpyxl' 를 실행 한다. 특별한 이슈 없이 잘 설치 된다~
c:\Python\code>pip install openpyxl
....
Installing collected packages: openpyxl
Running setup.py install for openpyxl ... done
Successfully installed openpyxl-2.4.4
[샘플 동작 검증]
그럼 일단 모듈이 잘 돌아간 다는 것을 테스트 하기 위해서 샘플 파일을 찾아본다. 구글에서 'python openpyxl sample' 로 찾으면 첫 페이지 중간 쯤에 아래와 같은 샘플 링크가 있다.
https://pypi.python.org/pypi/openpyxl
그냥 돌려도 잘 돌아가지만 내용 중 일부가 먼저 쓴 값을 덮는 식으로 만들어져 있어서 한글 주석도 달겸 셀이 겹치지 않도록 일부 수정해 본다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 |
from openpyxl import Workbook
import datetime
# 워크북 하나 만들기
wb = Workbook()
# 활성 워크시트 선택 하기(뭐 첫번째 것이 선택 된다)
ws = wb.active
# A1 행에 42라는 숫자를 넣는다.
ws['A1'] = 42
# 현재 글자가 쓰여있는 다음 row 에 1,2,3 이라고 넣는다.
ws.append([1, 2, 3])
# 요 부분을 바꿈 A2 로 하면 1,2,3 중 1이 겹쳐 쓰여져서, A3로 수정
ws['A3'] = datetime.datetime.now()
# 메모리에 있는 워크 북을 실제 엑셀 파일로 저장해 형상화 한다.
wb.save("sample.xlsx") |
cs |
워크 북(workbook), 워크 시크(work sheet)라는 조금 낯설은 용어가 나오지만, 우리가 평소 보는 엑셀을 기준으로 비교해 보면 아주 간단한다. 아래의 그림만 이해한다면 작업은 단순한 인형눈 끼우는 알바 모드가 된다. 엑셀의 표들이 있는 가장 바깥 쪽 부분이 workbook 이며, 엑셀 문서 하나 하나를 얘기한다고 봐도 무방할 듯 하다. 그 안에 들어있는 탭 문서들이 work sheet 이다.
c:\python\code 에 위의 소스 파일을 가져다 excel_sample.py 이름으로 저장하여 실행 한다. (저장하고 실행하는 부분을 모르겠으면 자세히 설명한 2교시에서 보고 오길 바란다.)
c:\Python\code>python excel_sample.py
코드를 실행 시키면 화면엔 아무것도 안 출력 되지만 폴더 안을 보면 아래와 같이 우리가 만든 코드가 실행 되어 sample.xlsx 파일이 만들어져 있다(참고로 테스트를 위해 여러번 실행 할 때, sample.xlsx 엑셀 파일을 열어 놓은 상태면 쓰지 못해서 에러가 난다). 파이썬 코드와 만들어진 값의 상관 관계를 결과 값 그림에 표시해 본다.
[문제 나누기 - 컬럼이름 얻어오기]
그럼 샘플은 실행해 검증해 봤고, 이제 우리가 원하는 기능을 만들어 보려고 한다. 4교시 때 우리가 만들어 보았던 supermarket 테이블을 조회한 내용을 엑셀에 저장해 보려 한다. 그런데 단순히 내용만 저장하면 거의 기존 수업 반복이여서 재미가 덜 하니까. 컬럼 이름을 얻어와서 엑셀의 맨 첫 번째 라인에는 컬럼 명을 적어 좀더 보기 좋게 하려 한다.
어 그럼 테이블의 컬럼 이름을 어떻게 얻어와야 하지? 하는 문제가 생긴다. 구글에서 'mssql table column name' 이라고 검색한다. 제일 첫번째 문서인 아래 문서의 링크를 열어보면, 여러개의 설명들이 있는데, 제일 심플해 보이는 아래 코드를 선택한다. 테이블의 스키마 구조를 저장하고 있는 테이블에서 컬럼이 이름을 가져오는 쿼리이다.
http://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'name_of_your_table'
그럼 저 쿼리안의 'name_of_your_table' 안에 4교시에 만든 'supermarket' 으로 바꾸어서, 잘 돌아가나 검증을 위해 쿼리 분석기에서 실행해 본다. (쿼리 실행 부분을 잘 모르겠으면, 4교시에 자세히 설명했으니 가서 보고 온다). 실행해 보면 우리가 원하는 컬럼 값이 잘 나옴을 볼수 있다.
[문제 나누기 - 루프를 이용해 엑셀에 출력하기]
또 하나의 해결해야할 부분은 엑셀에 뿌려주는 문제이다. 디비 구조하고 엑셀 구조는 상당히 비슷하기 때문에, 디비를 한줄 읽어서 컬럼 들을 가로로 쭉 써주고, 또 한줄 읽어서 가로로 쭉 써주는 걸 반복하면 되는데, 행이 증가 하는건, 아래 그림과 같이 뒤의 숫자만 1, 2, 3 으로 증가 시켜줌 되는데, 가로로 증가하는건 뒤의 숫자가 고정된 채 문자가 하나씩(A, B, C) 늘어나야 한다. 말로 표현해 보면,"DB의 첫째 행을 가져와 A1~E1 에 저장하고, 다음 행을 가져와 A2~E5 에 저장해야 한다."
for 문을 2개 겹쳐 써야 한다고 대충 감은 든다. 안쪽 for 문은 A1~E1 식으로 숫자는 고정한 채 알파벳만 늘어나게 해주어 한줄을 표시 하는 기능을 하고, 바깥 쪽 for 문은 뒤의 숫자를 하나 증가해 줄을 바꾸어 주어, 다음번 안쪽 for 문이 A2~E2 식으로 다음 줄에 표시하게 해줌 된다.
그런데 숫자 증가하는건 i = i + 1 하면 될거 같은데, 문자를 어떻게 증가 시킬 지 모르겠다(아스키 코드 어쩌고 하는 식으로 내부적으로 문자가 숫자로 됬다는건 기억이 나긴 한다). 그럼 다른 사람이 문자 증가 문제를 해결한 코드를 찾기 위해 'python increase alphabet' 으로 구글을 조회한다. 위에서 두번째 결과인 아래 페이지를 보면, char = chr(ord(char) + 1) 라는 코드를 알려준다. ord 가 뭘 하는 건지 'python ord' 로 찾아 해석해 보면 ord 는 문자를 아스키 코드로 바꿔주는 함수이기 때문에, 해당 코드는 '문자를 아스키 코드로 변경하여 1을 더하고, 다시 문자로 바꿔준다' 의 기능을 한다.
http://stackoverflow.com/questions/11827226/can-we-increase-a-lowercase-character-by-one
그럼 해당 코드를 이용하여 첫 번째 컬럼 명을 출력하는 부분은 아래와 같이 만들어 질 수 있다. (한 행만 찍음 되기 때문에 바깥 쪽 루프는 없어도 된다. 안에 보면 '1' 로 하드코딩 되있다)
|
1
2
3
4
5
6
7
8
9
10
11
12 |
# 컬럼 1번째 표시
column_char = 'a'
# supermarket 의 컬럼들 가져옴
cursor.execute('SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'supermarket\';')
# 한행 씩 가져오면서
row = cursor.fetchone()
while row:
# 컬럼 문자를 하나씩 증가하면서 해당 행을 넣음.
ws[column_char + '1'] = row[0]
column_char = chr(ord(column_char) + 1)
row = cursor.fetchone() |
cs |
위의 코드가 돌아가면 A1 = row[0](첫번째 컬럼이름), B1 = row[0](두번째 컬럼이름)... 이런식으로 엑셀의 맨 첫줄에 다섯 개의 컬럼 이름이 들어간다.
두번 째는 내용을 출력 하는 코드 부분이다. 비슷하지만, 조회된 내용을 한 줄씩 가져오며 내용이 없을 때까지 루프를 돌리는 while 문이 바깥쪽 for 루프 역활을 한다. 아까 구상 했듯이 안쪽 for 문이 한 행(5개 컬럼)을 출력하는 역활을 한다. 처음 1행은 컬럼 이름이 들어가 있을 것이므로, column_num 을 2로 해서 2번째 행부터 내용이 출력되게 한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 |
# 2번째 행 표시
column_num = 2
# supermarket 테이블의 내용을 가져온다.
cursor.execute('SELECT Itemno, Category, FoodName, Company, Price FROM supermarket(nolock);')
# 한 행씩 가져오면서
row = cursor.fetchone()
while row:
# 예전 수동 타자기 처럼, 새로운 줄이 오게 되면, 첫째 셀 a 로 돌아가는 초기 값
column_char = 'a'
# 1~5 까지 x 가 변하면서 컬럼 문자, row를 하나씩 늘여 결과를 하나씩 담음.
# ws['a1'] = row[0], ws['b1'] = row[1], ws['c1'] = row[2]...
for x in range(1, 6):
ws[column_char + str(column_num)] = row[x-1]
column_char = chr(ord(column_char) + 1)
# 다음 행을 표시하기 위해 뒤의 숫자 증가
column_num = column_num + 1
row = cursor.fetchone() |
cs |
[해결된 조각 합치기]
그럼 위의 코드들을 모두 적절히 합치면 아래와 같은 전체 소스가 된다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 |
#-*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
#from openpyxl.compat import range
import pymssql
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws.title = "output"
conn = pymssql.connect(server='localhost', user='pyuser', password='test1234', database='mytest')
cursor = conn.cursor()
column_char = 'a'
# supermarket 의 컬럼들 가져옴
cursor.execute('SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'supermarket\';')
# 한행 씩 가져오면서
row = cursor.fetchone()
while row:
# 컬럼 문자를 하나씩 증가하면서 해당 행을 넣음.
ws[column_char + '1'] = row[0]
column_char = chr(ord(column_char) + 1)
row = cursor.fetchone()
# 2번째 행 표시
column_num = 2
# supermarket 테이블의 내용을 가져온다.
cursor.execute('SELECT Itemno, Category, FoodName, Company, Price FROM supermarket(nolock);')
# 한 행씩 가져오면서
row = cursor.fetchone()
while row:
# 예전 수동 타자기 처럼, 새로운 줄이 오게 되면, 첫째 셀 a 로 돌아가는 초기 값
column_char = 'a'
# 1~5 까지 x 가 변하면서 컬럼 문자, row를 하나씩 늘여 결과를 하나씩 담음.
# ws['a1'] = row[0], ws['b1'] = row[1], ws['c1'] = row[2]...
for x in range(1, 6):
ws[column_char + str(column_num)] = row[x-1]
column_char = chr(ord(column_char) + 1)
# 다음 행을 표시하기 위해 뒤의 숫자 증가
column_num = column_num + 1
row = cursor.fetchone()
# 파일을 실제 저장
wb.save("test.xlsx") |
cs |
excel_save.py 로 저장하고 실행 하면 아래와 같이 test.xlsx 파일이 만들어 진다.
c:\Python\code>python excel_save.py
역시 실행 결과는 안보이지만, test.xlsx 엑셀 파일을 열어보면 아래와 같이 컬럼명과 내용들이 들어가 있다.
[역지사지 - 엑셀 읽어오기]
마지막으로 저장의 반대인 읽어오기도 위의 식으로 비슷하게 루프를 돌리면서 가져오고 싶은 값을 지정 하면 될 것이다. 쓰기가 충분히 해당 루프를 돌리는 방식을 구현하는 부분을 해결했다고 생각하기 때문에 여기서는 간단하게 읽어와서 A1(Itemno), B2(과자) 값을 출력하는 예제만 봐보자.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
#-*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.compat import range
import pymssql
# 엑셀을 읽어와
wbread = load_workbook(filename = 'test.xlsx')
# 이름이 output 인 sheet 를 가져온다.
mysheet = wbread['output']
# a1, a2 값을 출력한다
print (mysheet['a1'].value + ' ' + mysheet['b2'].value) |
cs |
excel_read,py로 저장하고 실행을 하면, 아래와 같이 결과가 나온다.
c:\Python\code>python excel_read.py
Itemno 과자
[마치면서...]
처음 찾았던 엑셀 모듈 모아놓은 사이트에서 소개했던 openpyxl 외의 다른 모듈의 샘플을 보니 셀을 지정할때, A1, A2 식으로 안하고, (1,1) (1,2) 식으로 좌표식으로 표시하는 모듈도 있다. 인기 많고 유지보수 되는 모듈 중, 각자 쓰기 편하다고 생각되는 모듈을 사용하면 될 듯하다.
그리고 이제와 고백하자면 위의 코드엔 알고도 놔둔 버그가 있다. 아스키 코드를 증가시키는 방법은 A, Z 까지 밖에 안되는데, 엑셀의 가로행은 Z 까지 가면 AA 같이 두자리로 늘어나고 ZZ 담에는 세자리로 늘어난다. 그럼 사실 저 부분도 그 부분을 고려해야 하는데, 테이블 컬럼이 26개(a-z) 이상은 잘 안 쓰일거 같아서 그냥 두었다. 뭐 개인적으로 파이썬은 유틸리티 방식으로 사용하는 편이라서, 26개 이상 필요가 생기면 고치려 했다고 변명 하고 싶다 --; (가로 문자를 증가시키는 로직을 만들던지 위의 좌표 지정 방식의 모듈을 사용해 숫자로만 루프를 돌리는 것도 하나의 해결 방법일 듯 싶다.)
그럼 다음 시간인 '7교시 부록'에서는 살짝 문법 시간으로 가서, 지금 만든 스크립트 샘플을 함수를 이용해 (자신은 별로 없지만) 정리하는 부분을 살펴 보면서 구조화된 코드에 대해 잠시 생각하는 시간을 가져 보려고 한다.
[보충 #1]
최근 어떤 책을 보다가 발견한건데, openpyxl 에서도 위에 같이 ws['a1'].value 같이 문자 말고,
ws.cell(row=1, column=2).value 로 하는것도 가능하다--; 뭐 위의 코드도 억지로 돌아가긴 하지만, 괜히 어려운 길을 간거 같아서 조금 미안하다^^. 관심 있으신 분은 위의 코드를 아래와 같이 row, column 지정 방식의 코드로 조금만 수정하면 코드가 더 간략해 질것 같다. 숫자로 지정하는 샘플 코드만 아래에 추가 한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 |
#-*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
# grab the active worksheet
ws = wb.active
# 예제로 들었던 방식
ws['A1'] = 12
# 더 편한 방식
ws.cell(row=2, column=2).value = 777
# Save the file
wb.save("sample.xlsx") |
cs |
추가 -> 댓글에서 SSL 이 알려주셔서..
2.5버전 부터는 아래와 같이 값을 바로 넣어도 되네요.
ws.cell(row=2, column=2).value = 777
[보충 #2]
문의가 와서 보니 openpyxl 최신버전에서는 아래 import 문이 에러가 나네요. 지금 보니 사실상 코드안에서 사용하지 않는 부분이라서 아예 빼버림 에러가 발생안합니다
from openpyxl.compat import range
|
2017.2.28 by 자유로운설탕 |
cs |
'프로그래밍' 카테고리의 다른 글
| 구글로 공부하는 파이썬 - 8교시 (정규표현식 소개) (0) | 2017.03.04 |
|---|---|
| 구글로 공부하는 파이썬 - 7교시 부록 (함수로 정리하기) (3) | 2017.03.01 |
| 구글로 공부하는 파이썬 - 6교시 (해결된 조각 합치기) (0) | 2017.02.25 |
| 구글로 공부하는 파이썬 - 5교시 (암호화 모듈 사용) (8) | 2017.02.23 |
| 구글로 공부하는 파이썬 - 4교시 (데이터 베이스 연결) (5) | 2017.02.18 |