[파이썬/업무자동화] 엑셀 전체 셀 내용 중 특정 문자만 변경/제거하기

다른 사람의 불편함을 해결해주는 업무자동화 1편 : 데이터를 종류별 자동 분류하기

다른 사람의 불편함을 해결해주는 업무자동화 2편 : 파일명 규칙 따라 폴더 자동 분류하기

 

---

 

  다른 사람의 불편함을 해결해주는 업무자동화 포스팅 3편. 먼저 이번 사연을 보자. 

 

 

이번 사연

 

  이 사연은 '나도 코딩' 티스토리에서 어떤 글의 댓글에 질문이 있어 작성해보는 포스팅이다. 심지어 4일전에 올라온 사연이다. 질문자의 사연을 간단하게 설명해보면 엑셀 파일의 전체 셀 내용 중 찾아 바꾸기를 통해 특정 문자를 제거하고 싶다는 것이다. 예를 들면 아래와 같은 경우를 말하는 것 같다.

 

예시를 설명하기 위한 수입지출표

 

 

  위 표는 흔히 볼 수 있는 수입지출표(가계부)이다. 만약 가계부의 데이터중 셀 데이터의 일부인 콤마(",")만 제거하려고 해본다고 하자. 사실 엑셀에는 아래와 같이 Ctrl+F(찾아바꾸기) 기능 또는 셀 서식을 통해 쉽게 바꿀 수 있다. 하지만, 업무자동화 프로그램과 연동하려고 하면 해당 기능이 필요할 수도 있을 것 같다.

 

  이번 편은 코드가 간단하므로 함수 1개로 정의하여 설명해보려고 한다. 먼저 전체 코드 및 결과를 보고 중요한 부분만 짚어서 설명하도록 하겠다.

 

 

1. 전체 코드 및 결과 확인

 

 

<전체 코드>

import openpyxl as op

#path는 대상이 되는 엑셀파일의 절대 경로
def corretion(path : str):
    #openpyxl 워크북 정의
    wb = op.load_workbook(path)
    #워크북의 활성화된 시트를 객체로 정의
    ws = wb.active

    #해당 시트의 마지막 열, 마지막 행 
    column_max = ws.max_column
    row_max = ws.max_row

    #열마다 행을 for loop문 진행
    for col_num in range(1, column_max+1):
        for row_num in range(1, row_max+1):
            
            #tempstr : cell값이 문자열이 아닌 경우를 감안하여 str로 바꿔줌
            tempstr = str(ws.cell(row = row_num, column = col_num).value)
            #문자열 함수 replace 사용(,를 빈칸으로)
            data = tempstr.replace(",","")
            #빈 셀의 경우 None이라는 문자열 타입이므로 제외하고 데이터 입력
            if data != "None":
                ws.cell(row = row_num, column = col_num).value = data 
    
    #저장
    wb.save("correction_result.xlsx")

if __name__ == "__main__":
    path = r"엑셀 파일 절대 경로"

    #함수 실행
    corretion(path)

 

 

<결과>

셀 전체 콤마( , ) 제거 됨

 

 

  결과에서 보면 알겠지만 모든 숫자가 ,가 제거되면서 int -> str(문자열) 형태로 변경되어 입력되었다. 이유는 파이썬 코드에서 문자열 str로 변경했기 때문이다. 만약 숫자 형태의 문자를 다시 엑셀에서 숫자 형식으로 바꾸려면 해당 셀들을 int 형태로 바꿔줘야 한다.(해당 코드에는 반영되지 않음)

 

 

 

 

2. 코드 설명

 

 

1) 모듈  import

 

 

<코드>

import openpyxl as op

 

  이전 엑셀 업무자동화 포스팅에서 내용과 마찬가지로 openpyxl을 사용했다. 엑셀 시트에 접근해서 셀값을 입력하고 읽어오는데는 openypxl 모듈이 정말 유용하다

 

 

 

2) 마지막 행, 마지막 열값 저장

 

 

<코드>

  #해당 시트의 마지막 열, 마지막 행 
    column_max = ws.max_column
    row_max = ws.max_row
    print("마지막 열 : ", column_max)
    print("마지막 행 : ", row_max)

 

 

  엑셀파일 특정 시트에 입력 된 데이터들이 어디까지인지를 알려면 행과 열의 최대값을 알 수 있어야 한다. openpyxl의 함수 중 max_column과 max_row가 그 기능을 제공한다. 위 예시 엑셀파일의 결과를 출력해보면 최대행값이 21이고 최대열값이 3인 것을 알 수 있다.(열의 경우 A,B,C 순서대로 1,2,3이다.)

 

 

<결과>

마지막 열 :  3
마지막 행 :  21

 

 

3) 최대행값, 열값을 활용한 이중 for loop문

 

 

<코드>

    #열마다 행을 for loop문 진행
    for col_num in range(1, column_max+1):
        for row_num in range(1, row_max+1):
            
         .
         .
         .

 

  

  이중 for문은 열 기준으로 row를 반복하는 loop문이다. 아래 이미지를 보면 쉽게 이해할 수 있다. 1)에서 구했던 row_max, column_max 값을 활용하여 반복문을 진행한다. column+1, row_max+1에서 +1을 한 이유는 range의 특성에 따라 마지막 값을 포함시키지 않기 때문이다. 예를 들어, range(1, 10)이면 실제 반복은 1~9까지만 한다.

 

열 기준으로 for문 진행

 

 

4) for 문 내부의 문자열 변환 및 엑셀에 입력하는 코드

 

 

<코드>

           #tempstr : cell값이 문자열이 아닌 경우를 감안하여 str로 바꿔줌
            tempstr = str(ws.cell(row = row_num, column = col_num).value)
            #문자열 함수 replace 사용(,를 빈칸으로)
            data = tempstr.replace(",","")
            #빈 셀의 경우 None이라는 문자열 타입이므로 제외하고 데이터 입력
            if data != "None":
                ws.cell(row = row_num, column = col_num).value = data

 

 

  이 코드에서 중요한 부분은 tempstr = str( ~ )과 replace 함수를 사용하는 부분이다. 일부 문자를 다른 문자로 대체하기 위해선 replace 함수를 사용해야 하는데  replace 함수는 문자열에만 적용된다. 따라서, 숫자 data(int)를 문자열로 바꾸기 위해 str( )을 적용한다. 

 

  그 다음, tempstr.replace를 통해 콤마(" , ")를 빈칸으로 바꿔준다. 위 엑셀 양식에서 빈칸의 경우 "None"이 문자열 형태로 변환되기 때문에 if문을 진행하지 않으면 None이 입력된다. 따라서 if data != "None" 조건문을 넣어줌으로써 "None"이 입력되는 것을 방지한다.

 

 

 

3. 코드 .py 파일

 

코드 원본 파일은 아래 첨부파일을 참고!

 

code.zip

0.00MB