[Python] Excel(openpyxl)를 다루는 방법

안녕하세요. 명월입니다.

이 글은 Python에서 Excel(openpyxl)를 다루는 방법에 대한 글입니다.

사무 업무를 하는 사람은 엑셀을 빼 놓을 수가 없네요. 그래서 그런지 Excel 라이브러리는 어떤 프로그램 언어에서도 항상 있는 것 같습니다.

Java와 C#에서는 Apache 재단에서 제공하는 POI라이브러리가 있는데 Python에서는 자체적으로 라이브러리가 있습니다.

링크 - https://openpyxl.readthedocs.io/en/stable/usage.html

먼저 Excel 파일을 생성해서 만드는 방법에 대해 설명하겠습니다.

# 날짜 형식 모듈
import datetime
# 엑셀을 사용하기 위한 모듈
from openpyxl import Workbook

# 엑셀 Workbook 생성
wb = Workbook();
# Workbook을 생성하면 기본적으로 시트가 하나 생성됩니다.
print(wb.sheetnames);

# 현재 활성화된 엑셀 시트 객체 가져오기
ws = wb.active;
# 시트 이름 바꾸기
ws.title = "Test1";
# 시트 이름으로 시트를 선택할 수도 있습니다.(시트명이 없는 것을 선택하면 에러가 발생합니다.)
ws = wb["Test1"];

# 시트를 생성하기
ws1 = wb.create_sheet(title="Test2");
# 총 시트를 출력하기
print(wb.sheetnames);

# 시트 내에 cell를 선택하는 방법입니다.
cell = ws.cell(row=1, column=1);
# 선택된 cell의 값 넣기
cell.value = "hello world";
# cell의 문자 포멧(general은 excel에서 일반을 뜻합니다.)
print(cell.number_format);

# 2행 1열의 셀을 선택하기.
cell = ws.cell(row=2, column=1);
# 선택된 cell에 날짜 타입의 값 넣기
cell.value = datetime.datetime(2020, 1, 1);
# 셀의 포멧을 변경하기
cell.number_format = 'yyyy-mm-dd';
# cell의 문자 포멧
print(cell.number_format);

# 여기는 Test2의 시트의 값을 넣습니다.
# 셀을 선택하는 방법은 R1C1 형식으로 선택 가능합니다.
cell = ws1["A1"];
# 선택된 cell에 값 넣기
cell.value = 5;

cell = ws1["A2"];
cell.value = 6;

# 함수식(formulae)은 엑셀 사용하는 방법 그대로 넣으면 됩니다.
ws1["A3"] = "=SUM(A1, A2)";

#위 작성한 엑셀 시트를 파일로 저장합니다.
wb.save('Example1.xlsx');

위 결과는 소스 상에서 print함수를 사용한 결과 출력입니다.

첫번째는 엑셀을 생성하고 시트 상태를 확인 했을때 리스트 형식으로 Sheet가 있는 것을 볼 수 있습니다. Workbook을 생성하면 Sheet라는 시트가 자동으로 생성되는 군요.

두번째는 Sheet의 시트 명을 Test1로 바꾸고 Test2라는 시트를 생성한 후의 값입니다. 시트가 Test1, Test2가 생긴 것을 확인할 수 있습니다.

그리고 첫번째 문자를 넣었을 때 데이터 타입입니다. General이 나왔습니다. 두번째는 날자를 넣고 'yyyy-mm-dd'의 형식으로 데이터를 설정한 후에 값을 확인한 것 입니다.

main.py 파일이 있는 곳을 보면 Example1.xlsx 엑셀 파일이 생긴 것을 확인할 수 있습니다.

Test1의 시트에 Hello world와 날짜가 포멧에 맞게 입력되어 있는 것을 확인할 수 있습니다.

Test2 시트에는 A1의 값과 A2의 값이 들어가 있고 SUM함수가 실행되어 있는 것을 확인할 수 있습니다.

이번에는 Excel데이터를 생성하는 것이 아니고 파일을 읽어서 스타일까지 입혀보겠습니다.

자료는 예전에 Java에서 사용하던 파일을 이용해 보겠습니다.

링크 - [Java] POI를 이용한 Excel 다루기

(참조로 Python의 openpyxl는 xls 형식의 엑셀은 참조가 되지 않습니다. 찾아보니 xls형식은 다른 라이브러리이네요. 그래서 xlsx로 변환한 후에 사용하겠습니다.)

예전에 C1에서 D13까지 데이터를 넣으면 그래프가 자동으로 바뀌는 식의 엑셀시트를 만들었네요.

# 엑셀을 사용하기 위한 모듈
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from random import random

# 랜덤 함수를 이용해 -20부터 20까지의 랜덤 값을 취득하는 함수
def get_random():
  while True:
    ret = int(random()*100);
    if ret < 40:
      return ret - 20;
# 엑셀을 읽어 온다.
wb = load_workbook(filename = "TestExcel.xlsx");
# 엑셀 시트를 active한다.
ws = wb[wb.sheetnames[0]];

# C2부터 D13까지의 데이터를 랜덤으로 입력한다.
for row in range(2,14):
  key = f'C{row}';
  # 랜덤 값을 입력한다.
  ws[key].value = get_random();
  # 해당 cell의 폰트 설정이다. (폰트 스타일, 크기, 두께, 이탤릭, 정렬, 아래선, 취소선, 색)
  ws[key].font = Font(name='Calibri',size=11,bold=False,italic=False,vertAlign=None,underline='none',strike=False,color='FF000000');
  # 해당 cell의 폰트 설정이다. (색 스타일, 시작색(그라데이션이 아니라면 바탕색), 종료색(그라데이션 용))
  ws[key].fill = PatternFill(fill_type="solid", start_color='FFFFFFFF', end_color='FF000000');
  # 해당 cell의 테두리 설정이다.
  ws[key].border = Border( left=Side(style="thick", color="000000"), 
                           right=Side(style='double', color="000000"),
                           top=Side(style="thick", color="000000"),
                           bottom=Side(style="thick", color="000000"),
                           diagonal=Side(style="thick", color="000000"),
                           diagonal_direction=0,
                           outline=Side(style="thick", color="000000"),
                           vertical=Side(style="thick", color="000000"),
                           horizontal=Side(style="thick", color="000000"));
  key = f'D{row}';
  # 랜덤 값을 입력한다.
  ws[key].value = get_random();
  # 정렬 설정이다. (가로 정렬, 세로 정렬, 회전, 줄바꿈, .., ..)
  ws[key].alignment=Alignment(horizontal='general',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0);
  # 셀의 속성이다(잠금, 가리기 등..).
  ws[key].protection = Protection(locked=True, hidden=False);

# 행의 높이를 설정한다.
for row in range(1, 14):
  ws.row_dimensions[row].height = 30;

# 열의 너비를 설정한다.
for col in range(65,70):
  ws.column_dimensions[chr(col)].width = 20; 

# 열의 너비를 자동 설정하는 기능인데, 작동을 안한다...
ws.column_dimensions['A'].auto_size = True;

#위 작성한 엑셀 시트를 파일로 저장합니다.
wb.save('Example2.xlsx');

위 예제는 먼저 TestExcel.xlsx파일을 읽어서 Sheet의 값을 변경하는 예제입니다.

C열과 D열은 random 함수로 -20에서 20까지의 수를 넣었습니다. 결과가 해괴한 기온 그래프가 그려지네요..

그리고 C열에는 font설정과 배경색 설정, 테두리 설정을 했습니다. 테두리에 들어가는 Side함수의 값은 color의 경우는 HEX데이터가 들어갔고 style의 경우는 아래의 종류 중에서 스타일을 선택할 수 있습니다.

‘mediumDashDot’, ‘mediumDashed’, ‘dotted’, ‘medium’, ‘thick’, ‘thin’, ‘double’, ‘dashed’, ‘slantDashDot’, ‘dashDot’, ‘dashDotDot’, ‘hair’, ‘mediumDashDotDot’

D열의 경우는 정렬과 속성을 선택하였습니다.

결과를 보면 C열에는 테두리가 보이고(바탕색은 흰색이라 티가 안나네요.) D열은 정렬이 아래로 되어있는 것을 확인 할 수 있습니다.

자세한 속성을 참고하시려면 아래 링크를 확인하세요.

링크 - https://openpyxl.readthedocs.io/en/stable/styles.html

링크 - https://www.geeksforgeeks.org/python-adjusting-rows-and-columns-of-an-excel-file-using-openpyxl-module/

여기까지 Python에서 Excel(openpyxl)를 다루는 방법에 대한 설명였습니다.

궁금한 점이나 잘못된 점이 있으면 댓글 부탁드립니다.