[파이썬/업무자동화] 같은 양식 여러 엑셀 파일을 한번에 수정하기 - openpyxl 모듈

  회사에서 같은 양식의 엑셀파일(많으면 수백개까지)을 한번에  읽거나 수정해야하는 경우가 있는데 활용하면 좋을 것 같아서 써보는 포스팅. 예를들어 아래 같은 경우에 활용할 수 있을 것 같다.

  작성한 전체코드 및 샘플 양식은 포스팅 하단에 첨부하였음.

1. 견적서 등 동일한 양식의 다수 엑셀 파일에 한번에 입력하고 싶을 때

ex) 아래 양식과 같이 빈 양식 여러개 파일에 한번에 입력하고 싶을 때, 상황에 따라 새로운 양식 을 추가하여 한번에 적용하는 것도 가능하다.

ex) 빈 견적서 양식

2. 견적서 등 양식에 담당자, 연락처 등 값이 입력되어있는데 값이 바뀌어야 하는 경우

ex) 담당자가 퇴사해서 기존 양식을 모두 바꾸어야 할 때 등

ex) 견적서 양식에 이미 입력 된 값

 

 위와 같은 경우에 활용하도록 openpyxl, os 모듈을 사용하여 파이썬 코드를 구현해보았다.  해당 기능을 구현하기 위해서 필요한 openpyxl 도 같이 정리해보려고 한다. 아래와 같은 요구사항을 만족하는 프로그램을 제작하려고 함.

  -. 한 폴더에 있는 동일 양식 엑셀파일을 모두 처리할 것

  -. 입력(또는 수정)을 원하는 개수만큼 할 수있어야 함.

  -. 값을 입력하거나 바꾼 엑셀파일을 저장 함.

 

 

  일단 위와 같은 양식의 견적서를 임의로 같은 폴더에 3개 생성하였다. 

<견적서 양식>

 

1) 모듈 및 경로 설정

import os
import openpyxl

path = "C:/Users/Desktop/VS CODE/test3" #엑셀파일이 잇는 경로 설정
file_list = os.listdir(path) # path 폴더에 있는 파일들을 리스트로 받기

  먼저 os, openpyxl 모듈을 import하고 견적서 엑셀 파일이 있는 경로를 path 설정한다. 4번째 줄의 os.listdir( ) 함수는 os 모듈에 구현되어 있는 함수로 경로안에 있는 파일명을 리스트로 읽어온다.

< 위 코드 실행시 >

 

2) 몇개 바꾸는지에 따라 원하는 만큼 변수 생성(바꿀 위치, 바꿀 문자열)

num = int(input("몇개 바꿀지 정수로 입력 : "))
#동적변수 생성() : 바꿀 값이 여러개일경우 설정하기 위한 부분
for i in range(1, num+1):
    print(str(i)+"번째 설정:")
    globals()['cell_position'+str(i)] = str(input("바꿀 위치 : "))
    globals()['cell_value'+str(i)] = str(input("바꿀 문자열 : "))
    print("\n")

바꿀 위치는 엑셀의 셀 주소로 입력한다 ex) A1, A2, B3
바꿀 문자열은 입력할 문자열이다.(새로 입력할 문자열, 또는 바꿔야할 문자열)
저장할 변수는 입력 개수(num)에 따라 동적변수 할당을 통해 각각 저장할 위치와 값을 정하도록 하였다.
정수 num 값에 따라 동적변수 할당 개수가 정해진다.

위 코드를 보면 globals()를 사용한다. 간단히 요약하자면, 동적변수를 생성하기 위해 사용하는 문법이다.
먼저 globals( )를 간단히 정리해보겠다.

 

---

※ 파이썬 동적 변수 할당 방법 : globals() 사용

-. 입력하는 변수에 따라 할당되는 변수의 개수가 달라져야 할 경우 동적 변수 할당을 해야 한다.
-. globals(), local() 함수를 사용하여 동적변수를 할당할 수 있다. (위 코드에서 사용한 방법)
-. sys 모듈을 사용하여 할당할 수도 있다고 하는데 이 포스팅에서는 다루지 않고 따로 포스팅 예정.
-. 아래는 globals ()와 local()의 차이를 간단히 정리해봄.

Globals ()	Local ()
-. 전역변수 -. 프로그램 내 함수 밖에서 선언 됨 -. 프로그램 전체에 공유되지만 함수 내에서는 수정할 수 없다.	-. 지역변수 -. 프로그램 함수 내에서 선언 됨. -. 선언 된 함수내에서만 유효하다.

 

-. 이번 포스팅에서 사용하는 globals() 함수를 사용하여 동적변수를 할당하는 방법을 정리해보았다.

index = int(input("숫자를 입력하세요:"))

for i in range(1, index+1):
    globals()['test'+str(i)] = i * 100

<결과>

숫자를 입력하세요:>? 5
test1
100
test2
200
test3
300
test4
400
test5
500

-. 위 코드는 원하는 숫자를 입력받아서 global 함수를 통해 그 갯수만큼 변수를 생성한다.
-. 해당 변수에 i * 100 값을 저장한다.
-. for문에 range(1, index+1) 
   : range(a,b) -> a 이상 b미만의 숫자까지 루프를 돈다. 즉, 해당 for문은 1~index까지(index 숫자만큼) 동적변수를 할당한다.

---

3) 엑셀파일마다 for문을 돌아 정해진 셀 위치에 값을 입력하고 저장(또는 수정)

for file_name_raw in file_list:
    file_name = "C:/Users/Desktop/VS CODE/test3/" + file_name_raw #실행 할 파일 경로 설정
    wb = openpyxl.load_workbook(filename=file_name) # workbook 객체 생성
    ws = wb.active # 워크북에서 활성화 된 시트 객체 설정

    for i in range(1, num+1):
        print(file_name_raw +"의 "+str(i)+"번째 변경")
        cell_position = globals()['cell_position'+str(i)] #동적변수에 있는 값 저장(셀위치)
        cell_value = globals()['cell_value'+str(i)] # 동적변수에 있는 값 저장(셀값)
        ws[cell_position].value = cell_value # 정해진 셀위치에 셀값 입력
        print(cell_position+"의 값을("+cell_value+")로 입력합니다.")
    print("\n")
    wb.save(file_name_raw) #저장

-. 첫번째 for문 : 앞에서 file_list(견적서 리스트) for문을 돌아서 각각 엑셀파일의 활성화 된 Worksheet 객체를 생성한다.
-. 두번째 for문 : 위 동적변수를 할당한 개수만큼 for문을 돌아서 각 엑셀파일에 입력한 셀 위치에 따라 값을 입력(또는 수정)한다.

-. openpyxl을 사용한 부분에 있어서는 간단히 정리

---

※ openpyxl 간단 정리

file_name = "C:/Users/Desktop/VS CODE/test3/" + file_name_raw #실행 할 파일 경로 설정
wb = openpyxl.load_workbook(filename=file_name) # workbook 객체 생성

-. Workbook 객체 생성 : filename은 workbook 객체 생성에 필요한 실행 파일 경로이다. 위 코드에서 file_name은 엑셀파일의 전체 실행경로임을 알 수 있다. openpyxl을 통해 엑셀파일을 읽고 쓰려면 가장 먼저 workbook 객체를 생성해야 한다.

 

ws = wb.active # 워크북에서 활성화 된 시트 객체 설정

ws=wb['Sheet1'] #특정 시트명을 기준으로 객체 생성

-. Worksheet 객체 생성 : 위에서 생성한 workbook 객체에 대한 내부 Worksheet 객체를 생성한다. Worksheet란 엑셀의 내부 시트를 말하는 것이다. 엑셀파일에 따라 시트가 여러개 있을 수도 있고 1개만 있을 수도 있다. .active는 활성화되어있는 시트를 기준으로 객체를 생성한다.
  만약, 시트가 여러개 있어서 특정 시트명을 기준으로 객체를 생성하고 싶다면 두번째와 같이 작성하면 된다.

 

ws[cell_position].value = cell_value # 정해진 셀위치에 셀값 입력

ws['A1'].value = "YB" #직접 셀 주소로 입력하여 접근하기

ws.cell(row=i, column=j).value= 'YB' #행,열 숫자로 접근 (i, j는 integer)

-. Worksheet의 특정 Cell 접근 : 위에서 생성한 Worksheet 객체에서 특정 셀의 값을 수정하고 싶다면 위와 같이 작성하면 된다. cell_position, cell_value은 문자열 변수이다. 만약 cell_position = "A1", cell_value = "YB"이면 두번째 코드와 동일하다. 코드를 설명해보면 해당 시트의 A1 셀에 YB라는 문자를 입력한다는 이야기이다.
-. 행,열 숫자로 접근하는 것도 가능하다. (세번째 구문)
   : i, j는 정수이고 해당 워크시트의 i행 j열 셀 주소에 'YB'를 입력한다는 뜻이다.

 

wb.save(file_name_raw) #저장

-. 수정한 엑셀파일 저장 : 위에서 ws (Worksheet) 객체는 처음에 생성한 wb(Workbook) 객체에 대한 시트이다.
따라서, wb.save( ) 를 사용하면 위에 수정했던 내용이 저장된다. file_name_raw는 저장할 파일명이다. 만약 경로를 설정해주고싶다면 괄호 안에 전체 경로 및 파일명을 입력하면 된다. ( ex) C:/Users/파일명 )

---

프로그램을 실행해보면 

1) 몇개 바꿀지 정수 입력 : 3
     입력한 개수에 따라 위치 및 문자열 입력

< 동적변수 할당 및 변수값 입력 >

 

2) 엑셀에 값이 입력되는 과정이 출력 됨

<  엑셀 입력 과정 출력 >

3) 엑셀 파일 저장 : 기존에 입력되어있는 값은 덮어쓰기 된다.

< 저장 된 엑셀파일 결과 >

 

 

작성한 코드 및 양식은 아래 첨부합니다.

openpyxl_test.zip

0.02MB