[OpenPyXL] 2. Worksheet에 값 삽입(할당)하기 (feat. append, value)

---

Openpyxl은 파이썬(Python)에서 엑셀을 다루는 유용한 모듈이다. 이번 포스팅에서는 Worksheet에 값을 넣는 방법을 알아보고자 한다.

 

- 목차 -

1) 행 삽입

2) 열 삽입

3) 셀 값 할당하기

 

Worksheet를 생성하는 방법은 아래 포스팅에서 다루었으니 참고하면 된다.

 

[Openpyxl] 1. Workbook, Worksheet 생성 및 저장하기

[Openpyxl] 1. Workbook, Worksheet 생성 및 저장하기

---

   Worksheet에 값 삽입하기 (feat. append, value)

1) 행 삽입

a. 첫 행부터 지정된 개수만큼 아래 방향으로 삽입하기

Worksheet 객체에는 append라는 메서드가 있는데 이를 이용하면 행을 첫 행부터 아래 방향으로 차례대로 삽입한다.

 

아래 코드는 20개의 행을 삽입하는 것이며 각 행은 0부터 9까지 왼쪽 첫 번째 열에서부터 차례대로 채워진다.

 

from openpyxl import Workbook

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 행에 삽입.
for row in range(40):
    ws.append(range(10))
    
wb.save(filename='test_02_01.xlsx')

 

만약 행 삽입할 때 특정 칼럼(열)에만 삽입하고 싶다면 아래와 같이 딕셔너리를 append에 넘겨준다. 이때 키는 열 주소를 뜻하는 문자를 넣거나 인덱스를 넣어야 한다. 아래 코드는 'A' 칼럼(첫 번째 칼럼)과 'C' 칼럼(세 번째 칼럼)에만 값을 할당하는 방식으로 행을 삽입한다.

 

import numpy as np

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 행에 삽입.
for row in range(20):
    random_int1 = np.random.randint(5, 20, 1)[0]
    random_int2 = np.random.randint(20, 30, 1)[0]
    ws.append({'A':random_int1, 'C':random_int2}) #@ ws.append({1:random_int1, 3:random_int2}과 동일 
    
wb.save(filename='test_02_01_01.xlsx')

 

위 코드를 실행하면 그림과 같이 A, C(첫 번째, 세 번째) 칼럼에만 값이 추가된 것을 알 수 있다.

b. 특정 행 위치에 행 삽입하기

만약 4번째 행에만 삽입하고 싶다면 아래와 같이 원하는 행이 아니면 None을 리스트로 만들어 append에 담아주고 원하는 행 위치에서는 원하는 값을 append에 넣어주면 된다.

 

from openpyxl import Workbook

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 행에 삽입.
row_idx = 3 ## 네 번째 행 삽입
for row in range(20):
    if row != row_idx:
        ws.append([None])
    else:
        ws.append(range(10))
    
wb.save(filename='test_02_01_02.xlsx')

 

하지만 이 방법은 다른 행에 기존에 있던 값을 바꿀 수 있기 때문에 바람직하지 않은 방법이다. 이때에는 Cell 객체를 이용하면 된다. 사용법은 다음과 같다.

---

cell( 행 인덱스, 칼럼 인덱스, 넣어줄 값)

---

여기서 주의할 점은 위의 행과 열 인덱스는 0부터 시작하는 것이 아닌 1부터 시작한다는 것이다. 

 

from openpyxl import Workbook

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 행에 삽입.
row_idx = 4 ## 네 번째 행 삽입
col_value_list = range(10)
for i, cv in enumerate(col_value_list):
    ws.cell(row_idx, i+1, value=cv) ## 열 인덱스는 1부터 시작하므로 1을 더해줘야한다.
    
wb.save(filename='test_02_01_03.xlsx')

 

---

2) 열 삽입

a. 첫 번째 열부터 지정된 개수만큼 오른쪽 방향으로 삽입하기

안타깝게도 append는 행 삽입을 위한 용도로 만들어졌으며 열 삽입을 위한 메서드는 찾을 수 없었다(고수님들 알려주시면 감사하겠다). 하지만 Cell 객체를 이용하면 열 삽입을 할 수 있다.

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 열에 삽입.
array = range(20)
for i in range(10):
    for j in array:
        ws.cell(j+1, i+1, array[j])
wb.save(filename='test_02_02.xlsx')

 

b. 특정 열 위치에 열 삽입

이번엔 3, 5번째 열에 값을 삽입해보자.

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 열에 삽입.
row_indice = [3, 5]
array = range(20)
for row_idx in row_indice:
    for j in array:
        ws.cell(row_idx, i+1, array[j])
wb.save(filename='test_02_02_01.xlsx')

 

---

3) 셀 값 할당하기

a. 개별 셀 값 할당하기

개별 셀 값을 할당하기 위해선 먼저 특정 주소의 셀을 접근한 뒤 값을 할당해야 한다. 이때 주소를 지정하는 방법은 행과 열 위치를 숫자로 지정하는 방법이 있고 엑셀 스타일의 문자 주소('A1', 'AA1' 등)로 접근하는 방법이 있다. 아래 코드를 통해 그 사용법을 확인할 수 있다. 특정 셀의 값을 넣는 방법은 아래 코드 6번째 줄 주석과 같이 value 필드를 통해 할당할 수도 있다는 것도 참고하자.

 

wb = Workbook()
ws = wb.active ## 첫 번째 시트

## 리스트를 열에 삽입.
ws.cell(1, 3, 3)## ws['C1'] = 3
ws.cell(2, 1, 30) ## ws.cell(2, 1).value = 30
ws['D4'] = 'D4' ## ws.cell(4, 4) = 'D4'
wb.save(filename='test_02_03.xlsx')

 

b. 특정 범위의 셀 값 할당하기

Worksheet 객체는 슬라이싱을 통하여 셀 범위를 지정할 수 있다. 아래 코드는 셀 주소 'A5' 부터 'E10'까지를 지정한 후 값을 할당한다.

 

import numpy as np

np.random.seed(100)
matrix = np.random.rand(6, 5)
print(matrix)

wb = Workbook()
ws = wb.active ## 첫 번째 시트

for i, row in enumerate(ws['A5':'E10']):
    for j, col in enumerate(row):
        col.value = matrix[i, j]
        
wb.save(filename='test_02_04.xlsx')

 

이때 셀 주소가 아닌 행과 열의 위치를 이용하여 범위를 지정하고 싶을 때가 있다. 이때에는 openpyxl.utils에서 제공하는 get_column_letter을 이용한다. 이 함수는 특정 열 위치 숫자를 넣어주면 이에 해당하는 열의 셀 주소를 리턴한다. 예를 들어 get_column_letter(1)은 'A'를 리턴하고 get_column_letter(3)은 'C'를 리턴한다는 것이다.

 

이와 같은 원리를 이용하면 앞에서 본 것을 행과 열 위치로 구현할 수 있다. 이때 행과 열 위치를 셀 주소로 바꾸는 함수 number_to_coordinate를 정의했다.

 

import numpy as np

from openpyxl.utils import get_column_letter

def number_to_coordinate(rc):
    row_idx, col_idx = rc[0], rc[1]
    col_string = get_column_letter(col_idx)
    return f'{col_string}{row_idx}'

np.random.seed(100)
matrix = np.random.rand(6, 5)
print(matrix)

wb = Workbook()
ws = wb.active ## 첫 번째 시트

start_rc = number_to_coordinate((5, 1)) ## A5
end_rc = number_to_coordinate((10, 5)) ## E10
for i, row in enumerate(ws[start_rc:end_rc]):
    for j, col in enumerate(row):
        col.value = matrix[i, j]
        
wb.save(filename='test_02_05.xlsx')

 

---