[엑셀 자동화] 엑셀의 기본구조와 데이터 접근

파이썬에서 엑셀 관련 라이브러리로 엑셀 파일을 직접 조작하는 openpyxl을 주로 사용

python 패키지 인스톨러인 pip을 이용하여 설치

pip install -U openpyxl==3.0.4

 

기본적인 openpyxl 사용법

명칭과 생성법

• Workbook: 하나의 엑셀 문서를 의미, book이라고도 함
• Worksheet: book내에 한 장의 2차원 표를 의미, sheet라고도 함
  Workbook내에 여러 장의 sheet를 가질 수 있는데 엑셀 실행 시 가장 먼저보이는 시트를 Active Work Sheet라고 함.
  이 시트는 행과 열로 구성되며 행은 숫자 열은 알파벳으로 표현 됨.
• Cell: 행과 열이 교차하는 칸을 Cell이라고 하며 실질적인 데이터가 입력되는 곳. A1과 같이 [행][열] 형태로 위치 표현.

엑셀 창

 

위 엑셀 구조를 기반으로 작성한 기본적은 code는 다음과 같다.

실행하게 되면 위 엑셀 창같이 엑셀파일이 생성된다.

import openpyxl as excel

book = excel.Workbook()

sheet = book.active

sheet["A1"] = "이순신"

book.save("./class_list.xlsx")

 

엑셀 값 가져오기

반대로 작성된 엑셀파일로 부터 값을 가저오는 코드의 경우 하기와 같이 작성한다.

import openpyxl as excel

# 파일명을 통해 workbook load
book = excel.load_workbook("./class_list.xlsx")

# load한 workbook에서 첫 번째 시트 가져오기
sheet = book.worksheets[0]

# A1 위치의 셀 값 가져오기
cell = sheet["A1"]

# 가져온 셀 값의 데이터 출력
print(cell.value)

이순신

 

 

엑셀 데이터 접근 및 대입

엑셀 파일과 시트 그리고 셀에 다양한 방법으로 접근할 수 있음

import openpyxl as excel

''' workbook 생성 or 기존 엑셀 파일 불러오기 '''
book = excel.Workbook() # 새로운 워크북 생성
book = excel.load_workbook("class_list.xlsx") # 파일명을 통해 기존 엑셀 파일 load

''' sheet에 접근하는 세 가지 방법'''
sheet = book.active # 1. 활성화된 시트 가져오기
sheet = book.worksheets[0] # 2. 인덱스 활용
sheet = book["Sheet"] # 3. 시트명 활용

''' cell에 값을 기입하는 세 가지 방법 '''
sheet['A1'] = "이순신" # 1. cell 주소를 지정해 값 대입
sheet.cell(row=2, column=1, value="강감찬") # 2. 행, 열 번호 및 대입할 값을 지정하여 기입
cell = sheet.cell(row=3, column=1) # 3. 셀 생성 후 value 필드에 값을 대입하는 방식
cell.value = "광개토"

book.save("class_list.xlsx")

• workbook의 경우 Workbook()을 통해 클래스를 새로 생성하거나 <path/filename>을 기재함으로써 기존에 존자하는 엑셀 파일을 load할 수 있음.
• sheet의 경우 active를 통해 활성화된 sheet을 가져오거나, 인덱스 또는 시트이름을 활용하여 시트를 가져올 수 있음.
• cell의 경우 인덱스를 통해 접근하여 값을 대입하거나, 시트의 셀에 행/열/데이터 값을 전달하여 대입 할 수 있음(행, 열 값만전다달하여 셀 생성후 대입하는 방식도 가능) 

위와 같은 접근법을 활용하여 반복문을 활용하여 연속적인 대입을 통해 빠르게 값을 기입할 수 있음.

 

ex) 1~10등까지 순위 기재

import openpyxl as excel

book = excel.Workbook()

sheet = book.worksheets[0]

for i in range(10):
    sheet.cell(i+1, 1, str(i+1)+'위')

book.save("rank.xlsx")

rank.xlsx

 

ex) 구구단 표

import openpyxl as excel

book = excel.Workbook()

sheet = book.worksheets[0]

for y in range(1, 10):
    for x in range(1, 10):
        cell = sheet.cell(x,y)
        cell.value = x*y

book.save("gugudan.xlsx")

구구단

 

아주 간단한 예시를 작성했지만 하기와 같이데이터의 범위가 수 백, 수 천개일 경우 직접 작성하는 것보다 기하급수적으로 시간이 단축될 것이다.

 

ex) 100 x 100의 좌표 값

cell.coordinate 필드 값은 셀 위치의 주소 값을 나타냄.

import openpyxl as excel

book = excel.Workbook()

sheet = book.worksheets[0]

for y in range(1, 101):
    for x in range(1, 101):
        cell = sheet.cell(x,y)
        cell.value = cell.coordinate # A1 형식의 셀의 주소 값

book.save("position.xlsx")

 

엑셀 데이터 읽기

엑셀 데이터를 읽어오는 방법은 두 가지 방법(셀 주소, 행/열 값)이 있음.

import openpyxl as excel

book = excel.load_workbook("position.xlsx")
sheet = book.worksheets[0]

# 1. 셀 주소를 사용하여 값 읽기
print(sheet['A3'].value) #A3

# 2. 행과 열 값을 사용하여 값 읽기
print(sheet.cell(2,2).value) #B2

 

위의 방법을 통해 셀 값을 읽어올 수 있는데 이를 활용하여 다수의 값을 읽어오는 방법은

1. for문 활용

''' 
1. for문 활용 
'''
for row in range(3, 6):
    line = []
    for col in range(3, 6):
        line.append(sheet.cell(row, col).value)
    print(str(line))

 

2. 셀 주소 전달 

시트에 sheet[<Top,Left> : <Bottom, Right>]형태로 셀 주소를 전달하여 2차원 튜플로 반환

반환된 튜플을 한 행 씩 반복적으로 읽어들임 -> 읽어들인 한 행을 다시 한 열씩(즉, 셀) 읽어들임

'''
2. (Top,Left) 셀 주소와 (Bottom, Right) 셀 주소를 전달하는 방법
sheet[<Top, Left>셀 주소 : <Bottom, Right>셀 주소]를 전달하면 2차원 튜플로 반환
'''
for row in sheet["C3":"E5"]:
    line = []
    for cell in row:
        line.append(cell.value)
    print(line)

 

3. it_rows() 메소드 활용 

iter_rows()에 최소 행/열 값, 최대 행/열 값을 전달하여 해당 범위 내의 엑셀 데이터를 읽음.

최대 행/열 값을 생략하면 최소 행/열 값을 기준으로 시트의 모든 값을 가져옴.

'''
3. iter_rows 활용
iter_rows()에 최소 행/열 값, 최대 행/열 값을 전달하여 해당 범위 내의 엑셀 데이터를 읽음
최대 행/열 값을 생략하면 최소 행/열 값을 기준으로 시트의 모든 값을 가져옴
''' 
it = sheet.iter_rows(min_row=3, max_row=5, min_col=3, max_col=5)

for row in it:
    line = []
    for cell in row:
        line.append(cell.value)
    print(line)