[파이썬/업무자동화] 엑셀 시트별 분리 저장 - 3

※ 이 글을 쓰는 사람은 SW 비전공자입니다.

※ 개인 공부를 위해 정리하는 글이며, 작성한 코드들은 효율성, 깔끔함(?) 등과는 거리가 멀 수 있습니다.

 

1편: 2021.03.03 - [코딩/Python] - [Python/파이썬] 엑셀 시트별 분리 저장 - 1 (필요 사항 정리)

2편 : 2021.03.04 - [코딩/Python] - [Python/파이썬] 엑셀 시트별 분리 저장 - 2

3편 : 2021.03.05 - [코딩/Python] - [Python/파이썬] 엑셀 시트별 분리 저장 - 3

4편 : 2021.03.05 - [코딩/Python] - [Python/파이썬] 엑셀 시트별 분리 저장 - 코드 및 테스트 양식

 

---

 

  2편에서 엑셀 시트를 분리하기 위해 아래와 같이 도식화를 진행하였다. 파일을 각 상품 코드에 따라 파일을 분류하고 필요한 시트를 제외하고 삭제하는 방법으로 코딩하려고 한다. 

  파이썬 openypyxl 모듈에는 시트를 삭제하는 기능은 있으나 각각 분리하여 저장하는 기능은 없어 아래 방법을 고안함.                      (혹시나 제가 잘못 알고있는 것이라면 지적 부탁드립니다.) 

< 코드 도식화 >

 

코드는 2부분으로 나뉜다. 

-. 엑셀파일을 상품 수에 따라 파일을 생성하여 각 상품코드에 따라 저장하는 부분                                                 

-. 생성 된 엑셀파일에서 해당 상품코드를 제외하고 나머지 시트를 모드 삭제하는 부분

 

1. 엑셀파일을 상품 수에 따라 파일을 생성하여 각 상품코드에 따라 저장하는 방법

1편에서 '상품분류'라는 test파일을 4개 생성했었다. 

< 상품분류 >

이 중에 A파일을 틀어보면 상품1 시트, 상품2 시트에 다음과 코드 정보(production code)를 확인할 수 있다. 1번에서 다루고자 하는 코드는 각 상품시트수만큼 반복문을 진행해서 각 코드에 맞는 파일을 생성하는 것이다.

알기 쉽게 설명하기 위해 아래와 같이 도식화했다.

< 1번 코드 도식화>

 

다음 코드 부분은 os 모듈을 활용해 엑셀 파일이 있는 폴더 경로를 알려주는 부분이다. no 변수의 경우 파일에서 중복되는 상품코드가 있을 경우를 대비해 별도의 파일 생성 번호를 부여하였다.

path = "C:/Users/Desktop/VS CODE/test/"
pl_list = os.listdir(path)  # path 폴더에 있는 파일들을 리스트로 받기
no=1 #중복되는 상품코드가 있을 경우를 대비해서 파일 생성 번호 부여

 

지정 된 경로에 따라 반복문을 도는 Loop문은 아래 코드와 같다.

for i in pl_list: #위에 지정한 경로에 있는 파일 리스트를 활용해 엑셀 파일 반복문
    print (i + "파일 처리중입니다..." )
    pl_name = path + i #파일 실행 전체 경로 
    wb = op.load_workbook(filename=pl_name) #위 pl_name 경로 엑셀파일 workbook 객체 생성
    sh_list = wb.sheetnames # 해당 workbook(wb)의 시트이름들을 list화
    print(sh_list) #시트명 출력

    try:
        for p in sh_list: #해당 시트를 루프문을 돌아 각 시트명을 품목코드로 변경
            p_temp = p.upper() #시트의 모든 문자를 대문자화하여 저장하는 임시 변수
            if p_temp.find("TOTAL") == -1 and p_temp.find("HISTORY") == -1 and p_temp.find("표지") == -1:
                print(p+"시트 처리 중입니다")
                ws = wb[p] # 워크시트를 설정하는 코드(p는 시트명임)
                r = ws_edit(ws) # 'A1' 셀값 받아오기, 공백일경우 자체 양식 수정하는 함수 호출
                pn = PartNumberCode(r) # 'A1' 셀값을 string으로 추가 후, 품목코드(PartNumber)를 리턴받는 함수 호출
                ws.title = pn #해당 시트명 수정
                print(wb.sheetnames)
                wb.save("C:/Users/Desktop/VS CODE/test2/"+ str(no) + "_" + pn +".xlsx") #저장

                no=no+1 # 파일 저장 index 번호 증가

    #시트 루프를 돌며 처리시 오류가걸리면 해당 파일은 예외로 이동시키고 continue를 통해 다음 루프 실행
    except Exception as e:
        print (p + "파일은 예외처리 대상으로 예외폴더로 이동합니다.", e)
        src = "C:/Users/Desktop/VS CODE/test/"
        dir = "C:/Users/Desktop/VS CODE/예외/"
        shutil.move(src + i, dir + i) #파일 주어진 경로로 이동
        continue

    #시트가 정상적으로 모두 처리되면 해당파일은 완료파일로 구분된다.
    src = "C:/Users/Desktop/VS CODE/test/"
    dir = "C:/User/Desktop/VS CODE/정상/"
    print(i + "파일은 정상적으로 처리되어 완료폴더로 이동합니다")
    shutil.move(src + i, dir + i)  # 파일 주어진 경로로 이동

 

언뜻 보면 복잡해보이지만 어렵지 않다. 일단 코드의 중요한 부분을 요약하자면 다음과 같다.

-. 이중 for문 사용 : 여러 엑셀파일에 대해 for문을 진행 -> 각 엑셀파일의 내부 여러 Sheet에 대해 for문 진행

-. 상품코드일 경우에만 for문 진행하도록 if문 삽입 : Total ,표지, history 등의 Sheet는 무시

-. 함수를 통해 상품코드 파싱 :  ws_edit( ) 과 PartNumberCode( ) 를 통해 각 상품 시트에서 상품코드 문자열을 읽어옴

-.'상품' Sheet명을 상품코드로 변경 : 위 파싱한 문자열 결과 pn 활용

-. Sheet명 변경 후 각 엑셀파일 저장(no 변수 사용사여 번호 부여)

-. 예외처리 : 정상적으로 코드가 실행 된 경우 완료 폴더로 이동, 오류가 발생한 파일인 경우 예외 폴더로 이동

 

코드를 구분하여 디버깅해보면 쉽게 이해가 가능하다.

1) 1개 엑셀파일에서 sheet list를 불러오는 부분

<디버깅 중>

2) 각 Sheet에서 상품코드를 읽어오고 시트명을 수정

<디버깅 중>

 

프로그램을 실행해보면 아래와 같이 처리과정이 출력되며 결과 폴더에 각 시트 파일이 생성된다.

<처리과정 출력>

< 코드 실행 결과 : 총 6개 시트에 대한 파일 생성 >

 

2. 생성 된 엑셀파일에서 해당 상품코드를 제외하고 나머지 시트를 모드 삭제하는 부분

   1번에서 생성 된 결과파일 6개에 대해 각 상품코드 Sheet를 제외하고 파일을 새로 생성하는 코드를 구현한다. 해당 코드를 도식화하면 아래와 같다. 1번 코드에서 생성한 임시 엑셀 파일(temp_file)의 상품코드 시트를 빼고 삭제하여 결과를 새로 저장하는 부분이다.

< 2번 코드 도식화 >

 

temp_path = "C:/Users/Desktop/VS CODE/test2"
temp_file = os.listdir(temp_path) #위 경로에 있는 엑셀파일명을 리스트로 받아옴
for i in temp_file: #각 엑셀파일마다 반복
    start = i.find("_") #각 엑셀파일명의 대시바 "_"를 기준점으로 위치값을 받아옴(integer)

    code = i[start+1:start+14] #위 start를 기준점으로 엑셀 파일명에서 상품코드를 읽어옴
    print(code)
    pl_name = temp_path + i #엑셀 파일 실행 경로
    wb = op.load_workbook(filename=pl_name) # workbook 객체 생성
    sh_list = wb.sheetnames # 해당 엑셀파일의 시트명을 리스트로 받아옴

    for r in sh_list: #각 엑셀파일의 시트 리스트를 반복
       if r != code: #상품코드와 다르면 삭제
            wb.remove(wb[r])


    print("시트삭제 결과 :", wb.sheetnames)
    wb.save("C:/Users/Yongbeom Jeong/Desktop/VS CODE/결과/"+ "결과_" + pn +".xlsx") #저장

 

1번과 마찬가지로 2번 코드 내용도 요약해보았다. 자세한 내용은 코드 주석 참고

-. temp_path : 1번에서 결과로 저장 된 파일 경로 저장 변수

-. for문 : 각 엑셀파일을 변수로 하여 loop 문을 진행한다.

-. 불필요 Sheet 삭제 코드 : 파일명의  대시바 "_" 부분을 기준점으로 하여 파일명에서 상품코드를 읽어오고 if문을 통해 상품코드와 다른 모든 Sheet를 삭제하는 부분

-. 시트를 삭제한 파일 새로 저장 : 결과_'상품코드'.xlsx

 

프로그램을 실행해보면 Sheet를 삭제하고 남은 Sheet의 결과를 확인할 수 있다. 결과적으로, 상품코드와 같은 Sheet만 남는다.

< 시트 삭제 코드 실행시>

 

프로그램 코드 및 예외 파일은 따로 글을 작성하여 첨부할 예정