파이썬 - 구분코드 엑셀 비교

비교하고자 하는 파일을 먼저 보고 설명을 하는 것이 이해를 돕는데 도움이 될 것 같습니다.

구분코드라고 이름을 지어준 이유는 각 엑셀 파일 내에 각각의 행을 구별해주는 key가 되는 값이 있기 때문입니다. 일반적으로 이렇게 키가 되는 값들은 제1열에 표시가 됩니다. 다음의 예에서 키가 되는 값은 바로 1열에 있는 은행 코드가 됩니다. 

 

 

이 은행코드들은 각 파일 내에서 유일한 값이며 중복되어 나타나지 않는다는 특징이 있어야 합니다. 만약에 반복적으로 나타난다면 이미 그 값은 유일한 값이 아니기 때문에 비교를 할 수 없습니다.

비교를 할 수 없는 상태의 파일이라면 비교를 할 수 있도록 파일을 변환해 줘야 합니다. 예를 들어볼까요?

 

스마트폰에 주소록이 있다고 해 봅시다. 동명이인은 있을 수 있어도 같은 전화번호는 있을 수 없겠죠. 그런데 혹시라도 같은 전화번호가 두 번 저장은 되어 있을 수 있습니다. 하나는 본명으로 저장이 되고 하나는 별명으로 저장이 되어 있을 수가 있겠죠. 예를 들자면요. 이럴 경우에는 두 개의 전화번호를 하나로 합쳐주는 과정이 필요합니다. 그래야 정확한 주소록이 될 수 있겠죠? 우리도 데이터를 비교하기 이전에 ① 각 데이터 파일에 겹치는 Key 값이 있는지를 사전에 확인하는 것이 필요하겠습니다. 

 

이와 같이 두 개의 파일이 준비가 된다면 Key 값을 기준으로 두 개의 파일을 비교합니다. ② 원본 파일을 처음부터 끝까지 검색하는 for문을 준비합니다. 원본 파일에서 하나의 Key 값을 대상으로 비교할 파일을 처음부터 Key 값을 찾을 때까지 for문을 돌립니다. ③ 찾으면 Key 값을 제외한 다른 값들을 비교해서 다른 부분을 체크합니다. ④비교할 파일에서  Key 값을 찾지 못하면 원본파일에 있는 내용이 삭제된 경우입니다. 

한가지 빠진 부분이 있습니다. ⑤ 비교할 파일에 추가가 된 Key가 있는 경우에는 모든 비교가 원본 파일 기준이기 때문에 찾을 방법이 없습니다. 비교할 파일에 추가가 된 Key가 있는 경우는 어떻게 찾을 수 있을까요? 비교대상 파일에서 하나를 찾을 때마다 표시를 해 두는 방법이 있습니다. 표시가 안된 Key를 가진 데이터가 바로 비교 파일에서 추가된 내용입니다. 

 

①~⑤까지의 내용을 순차적으로 코딩하면서 살펴보도록 하겠습니다. 

① 각 데이터 파일에 겹치는 Key 값이 있는지를 사전에 확인하기 

 

055_excelKeyComp.py

 import openpyxl

 

 wb_ori = openpyxl.load_workbook(r".\files\address_org.xlsx")

 ws_ori = wb_ori.active

 mc_ori = ws_ori.max_column

 mr_ori = ws_ori.max_row

 

 flag1 = False

 for i in range(2, mr_ori):

  for j in range(i+1, mr_ori + 1):

  if ws_ori.cell(i, 1).value == ws_ori.cell(j, 1).value:

  print(f"원본파일 : {i}, {j}겹치는 키가 있습니다.")

  Flag1 = True

  break

 

 if flag1 == False:

  print("원본 파일에는 겹치는 키가 없습니다.")

 

 flag2 = False

 wb_new = openpyxl.load_workbook(r".\files\address_new.xlsx")

 ws_new = wb_new.active

 mc_new = ws_new.max_column

 mr_new = ws_new.max_row

 

 for i in range(2, mr_new):

  for j in range(i+1, mr_new + 1):

  if ws_new.cell(i, 1).value == ws_new.cell(j, 1).value:

  print(f"비교할 파일: {i}, {j}겹치는 키가 있습니다.")

  Flag2 = True

  break

 

 if flag2 == False:

  print("비교할 파일에는 겹치는 키가 없습니다.")

    

코드가 짧지 않아 보입니다만 똑같은 루틴이 반복이 됩니다. 원본 파일과 비교할 파일에 대해서 각각 수행 됩니다. 이 중에서 가장 중요한 것은 for 문입니다. 

 

 flag1 = False

 for i in range(2, mr_ori):

  for j in range(i+1, mr_ori + 1):

  if ws_ori.cell(i, 1).value == ws_ori.cell(j, 1).value:

  print(f"원본파일 : {i}, {j}겹치는 키가 있습니다.")

  Flag1 = True

  break

 

 if flag1 == False:

  print("원본 파일에는 겹치는 키가 없습니다.")

 

첫번째 for 문은 2부터 시작을 해서 전체 개수까지 반복을 합니다. 첫 행이 제목이기 때문입니다. 그리고 맨 마지막 이전행 까지만 반복이 됩니다. 

 

그리고 안쪽에 있는 for문은 i + 1에서 전체 행 + 1까지 반복이 됩니다. 

 

만일 데이터가 100개라고 하면 제목을 빼는 2행부터 시작이므로 첫번째 for문은 2에서 99까지입니다. 

 

첫번째 for문에서 i가 2라고 하면 두번째 for문은 3에서 100까지 반복을 합니다.

첫번째 for문에서 i가 3이라고 하면 두번째 for문은 4에서 100까지 반복을 합니다.

첫번째 for문에서 i가 4라고 하면 두번째 for문은 5에서 100까지 반복을 합니다.

 

이와 같이 첫번째 for문에서 i가 정해짐에 따라서 안쪽의 for문에서 그 다음부터 끝까지 반복을 하면서 if문에서 같은 것이 있는지를 비교하게 됩니다. 한번이라도 같은 것이 나오지 않는다면 처음에 설정한 Flag1이 False이기 때문에 겹치는 키가 없다는 메시지를 출력합니다. 

 

Flag1이나 Flag2 중의 하나라도 True라면 비교를 진행하지 않고 에러 메시지를 보여주면서 프로그램을 종료해야만 합니다.

 

 

② 원본 파일을 처음부터 끝까지 검색

여기서는 같은 Key가 되는 행을 찾았는지, 같은 행을 찾지 못했는지를 검사합니다. 같은 행을 찾으면 원본파일과 비교할 파일의 각 열의 내용을 비교해야 합니다. 만일에 같은 행을 찾지 못했다면 그 행은 비교할 파일에서 삭제가 된 것입니다.

 

056_excelKeyComp2.py

 if flag1 or flag2:

  print("중복 키가 있습니다.\n각 파일을 확인해주시기 바랍니다.")

exit(0)

 

 found = False

 for i in range(2, mr_ori+1):

  for j in range(2, mr_new+1):

  if ws_ori.cell(i, 1).value == ws_new.cell(j, 1).value:

  print(f"원본 {i}행과 비교파일 {j}행이 같은 키” \ 

  “--> 각 열을 비교해야 함")

  found = True

  break

  if found == False:

  print(f"원본 {i}행은 비교파일에서 삭제되었음")

      found = False

시작 부분의 if문에서 flag1이나 flag2 중에서 하나라도 True이면 중복된 key가 있기 때문에 파일을 비교하기 직전에 파일을 확인해 달라는 메시지를 뿌리고 프로그램을 종료해야 합니다. 함수로 구현이 되어 있다면 return을 해 주면 됩니다. 하지만 위의 문장은 함수 내부가 아니기 때문에 프로그램 자체를 종료하기 위해서 exit(0) 함수를 사용합니다. 

 

for문 앞에서는 found 변수를 False로 선언해 줍니다. 이 변수가 그대로 남아 있으면 현재의 행이 사본에 없다는 의미입니다. 결국 삭제되었다는 의미입니다. 그럼 이 변수가 변경이 되는 if문을 살펴봐야 하겠습니다. 

 

첫번째 for문의 i는 원본 파일의 key를 가리키는 위치, 두번째 for문의 비교할 대상의 key를 가리킵니다. if문에서는 ws_ori, 즉 원본과 ws_new 비교할 파일의 key를 비교합니다. ws_ori는 i가 고정된 상태에서 ws_new가 2부터 끝까지 반복하면서 원본 ws_ori의 i번째 키와 비교할 파일 ws_new의 처음부터 끝까지 돌면서 key를 비교합니다. 

 

비교를 해서 같은 값을 찾으면 key 값을 제외한 각 열을 비교해야 합니다. 여기서는 우선 같은 key 값을 찾는데까지만 합니다. 같은 값을 찾았으면 원본의 어느 행과 비교 파일의 어느 행이 일치하는지를 확인할 수 있습니다. 찾으면 Found 변수를 True로 설정합니다. 

 

만일 끝까지 원본과 비교할 파일의 각 key 값을 비교했는데 찾지 못했다면 원본의 key  값에 해당하는 데이터가 비교 파일에서 삭제 되었음을 의미합니다.  여기까지를 실행해 보면 다음과 같이 각 열을 비교해야하는 데이터와 삭제된 key가 어떤 것인지를 출력해 줍니다.  

③ 각 열의 데이터 비교

앞의 그림에서 ‘각 열을 비교해야 함’에 해당하는 부분에 대한 코딩은 다음과 같습니다. 추가 변경된 사항만 발췌했습니다.

 

057_excelKeyComp3.py

 found = False

 diff_color = PatternFill(start_color='ffff99', 

  end_color='ffff99', fill_type='solid')

 for i in range(2, mr_ori+1): # 원본 키값 (행)

  for j in range(2, mr_new+1): # 비교파일 키값 (행)

  # 원본과 비교파일 키값이 같으면 (행)

  if ws_ori.cell(i, 1).value == ws_new.cell(j, 1).value: 

  for k in range(2, mc_ori + 1): # 같은 키값들의 열을 비교함

  # (열)이 다르다면

  if ws_ori.cell(i,k).value != ws_new.cell(j, k).value: 

  if ws_ori.cell(i,k).value # None --> ''

  == None: ws_ori.cell(i,k).value = '' 

  if ws_new.cell(j,k).value # None --> ''

  == None: ws_new.cell(j,k).value = '' 

  ws_ori.cell(i,k).value +=
f"--> {ws_new.cell(j, k).value}" # 바뀐값 표시

  ws_ori.cell(i,k).fill = diff_color

  found = True

  break

  if found == False:

  print(f"원본 {i}행은 비교파일에서 삭제되었음")

  found = False

 

wb_ori.save(r".\files\address_diff.xlsx")

 

코드가 많이 복잡해 보입니다. 이 코드는 다음의 코드가 있었던 부분에 보다 상세하게 내용이 추가된 것입니다. 

 

  print(f"원본 {i}행과 비교파일 {j}행이 같은 키” \ 

  “--> 각 열을 비교해야 함")

 

그럼 한줄씩 살펴보도록 하겠습니다. 먼저 for문이 추가되었습니다.

 

  for k in range(2, mc_ori + 1): # 같은 키값들의 열을 비교함

 

첫번째 열이 Key 값이기 때문에 두번째부터 마지막까지 원본 파일과 비교파일의 열을 비교합니다. 

 

  # (열)이 다르다면

  if ws_ori.cell(i,k).value != ws_new.cell(j, k).value: 

 

ws_ori 원본과 ws_new 비교 대상 파일의 열이 다르다면 내용이 변경이 된 것입니다.

 

  if ws_ori.cell(i,k).value # None --> ''

  == None: ws_ori.cell(i,k).value = '' 

  if ws_new.cell(j,k).value # None --> ''

  == None: ws_new.cell(j,k).value = '' 

  ws_ori.cell(i,k).value +=
f"--> {ws_new.cell(j, k).value}" # 바뀐값 표시

  ws_ori.cell(i,k).fill = diff_color

 

해당 셀에 있는 내용이 None 인 경우, 원래 값과 바뀐 값이 →로 연결될 때 에러가 발생합니다. 그래서 각 셀의 값이 None인 경우를 ‘ ‘와 같은 공백으로 변경해 줍니다. 

마지막 줄에는 셀의 색상을 바꿔줍니다. 결과 파일은 다음과 같습니다.

 

④ 삭제된 셀의 처리

원본 파일의 내용이 변경된 파일에서 삭제된 경우에 대한 처리 부분입니다

 

  if found == False:

  print(f"원본 {i}행은 비교파일에서 삭제되었음")

 

print() 함수를 대체할 내용입니다. 삭제된 것은 셀의 색상도 다른 색으로 바꾸고 취소선으로 내용을 지워주는 것이 보기 좋을 것 같아 그렇게 하려고 합니다.

 

058_excelKeyComp4.py

  if found == False:

  print(f"원본 {i}행은 비교파일에서 삭제되었음")

  for k in range(1, mc_ori + 1):

  ws_ori.cell(i, k).fill = del_color

  ws_ori.cell(i, k).font = cancel_font

  found = False

 

기존의 코드 대비해서 if 문 안에 for문이 추가되었습니다. 추가된 for문에 의해서 삭제된 파일은 셀의 색이 바뀌고 글자도 취소선이 그어지게 됩니다.

mc_ori는 각 열에 해당하므로 행은 그대로 두고, 해당 열로 이동해서 컬러를 바꿔주고 취소선을 넣어 주는 것으로 쉽게 마무리가 됩니다.  이렇게 취소선을 사용하기 위해서는 다음과 같이 Font를 import학도 Font에 취소선 strike에 True를 주었습니다. 

 

 from openpyxl.styles import PatternFill, Font

 

 del_color 

  = PatternFill(start_color='789ABC', end_color='789ABC', fill_type='solid')

 cancel_font = Font(strike=True)

⑤ 비교할 파일에 추가가 된 Key 찾기

원본 파일에서는 변경된 것과 삭제 된 것을 찾아서 표시했습니다. 이 내용들은 원래 원본 파일에 있던 내용이므로 원본 파일에서 바뀐 부분만 찾아서 셀의 색상을 바꾸고, 바뀐 값을 비교할 파일에서 읽어서 ‘ →  ’를 이용해 표시했습니다. 그리고 비교할 파일에서 삭제된 내용은 원본 파일에 정보가 그대로 남아 있기 때문에 역시나 셀 색상을 바꾸고 셀의 내용에 취소선만 추가해 주면 되었습니다. 

 

비교할 파일에 추가된 key는 원본 파일에서 추가된 위치를 찾고 해당 위치에 행을 삽입하고 삽입된 행에 비교할 파일의 내용을 하나씩 복사해 와야만 합니다. 우선은 비교할 파일에서 추가된 내용을 어떻게 찾아야 할 것인가에 대한 아이디어가 필요합니다.

 

059_excelKeyComp5.py

 

 added = [True for i in range(mr_new-1)]

 for i in range(2, mr_ori+1): # 원본 키값 (행)

  for j in range(2, mr_new+1): # 비교파일 키값 (행)

  # 원본과 비교파일 키값이 같으면 (행)

  if ws_ori.cell(i, 1).value == ws_new.cell(j, 1).value: 

  added[j-2] = False

  for k in range(2, mc_ori + 1): # 같은 키값들의 열을 비교함

  # (열)이 다르다면

  if ws_ori.cell(i,k).value != ws_new.cell(j, k).value: 

  if ws_ori.cell(i,k).value # None --> ''

  == None: ws_ori.cell(i,k).value = '' 

  if ws_new.cell(j,k).value # None --> ''

  == None: ws_new.cell(j,k).value = '' 

  ws_ori.cell(i,k).value +=
f"--> {ws_new.cell(j, k).value}" # 바뀐값 표시

  ws_ori.cell(i,k).fill = diff_color

  found = True

  break

  if found == False:

  print(f"원본 {i}행은 비교파일에서 삭제되었음")

  found = False

 

 print(f"len = {len(added)}")

 for i in range(len(added)):

  if added[i]:

  print(i+2, added[i])

 

wb_ori.save(r".\files\address_diff.xlsx")

 

수정된 코드에서 확인해야 할 부분은 added가 들어간 부분입니다. 이 코드에서는 비교할 파일에서 어느 행이 추가되었는지를 파악하는 기능만 가지고 있습니다. 원본에 비교할 파일의 추가된 내용을 복사해 넣는 과정은 다음에 추가하고자 합니다.

 

added가 들어간 부분만 발췌를 해 보면 다음과 같습니다.

 added = [True for i in range(mr_new-1)]

 

  added[j-2] = False

 

 print(f"len = {len(added)}")

 for i in range(len(added)):

  if added[i]:

  print(i+2, added[i])

 

첫번째 줄은 복잡해 보이지만 added라는 List를 생성해 주는 것입니다. 일반적인 리스트의 경우는 다음과 같이 선언을 합니다.

 

added = [1, 2, 3, 4, 5]

 

added를 이렇게 선언을 하면 added라는 리스트는 총 5개의 요소로 되어 있고 added[0] = 1, added[1] = 2, added[2] = 3, added[3] = 4, added[4] = 5와 같은 값들을 각각 갖게 되는 것입니다. 

그런데 우리 코드에서는 대 괄호 안에 값이 들어가 있는 것이 아니라 for문이 들어가 있습니다. 이 for문의 의미는 range()의 개수만큼 added라는 리스트에 값을 만들어 넣는데, 그 값이 for문 앞에 있는 True라는 값으로 만들어 넣으라는 의미가 되겠습니다.  예를 들어 mr_new의 값이 100이라고 한다면 mr_new - 1까지가 range에 들어가게 되니 0부터 99까지 100개의 리스트를 만들라는 의미입니다. 그리고 for 문 앞에 True가 있으니 모두 True 값으로 초기화가 되어 있겠죠.

 

added[0] = True, added[1] = True, added[2] = True …….., added[mr_new - 2] = True

 

위와 같이 초기화가 이루어집니다. range()에 mr_new - 1과 같이 해 주는 이유는 엑셀 파일의 첫번째 행은 열의 제목이 들어가기 때문에 제외했기 때문입니다.

 

  added[j-2] = False

 

원본파일의 Key 되는 값을 비교할 파일에서 찾습니다. 만약에 동일한 key 값을 찾았다는 얘기는 기존에 있던 내용이 그대로 들어 있거나 혹은 수정이 되었을 수도 있다는 얘기이므로 최소한 비교할 파일에 내용이 추가되지는 않았습니다. 그래서 위와 같이 표시를 해 주는 것입니다. 

왜 j-2를 해 주는걸까요? added 리스트의 개수는 제목을 뺀 데이터의 수 입니다. 전체 행 수에서 하나가 작은 값이죠. 

 

 for i in range(2, mr_ori+1): # 원본 키값 (행)

  for j in range(2, mr_new+1): # 비교파일 키값 (행)

 

위와 같이 각각의 원본 파일과 비교 대상 파일은 제목을 제외한 2부터 시작이 되고 있습니다. 그런데 리스트는 0부터 시작을 하거든요. 그래서 2를 빼서 위치를 조정해 준 것입니다.

 

 print(f"len = {len(added)}")

 

added의 길을 print() 함수에서 출력을 해 보고 있습니다. 여기서 출력되는 값은 비교할 대상 파일의 전체 데이터의 개수입니다. 행의 개수로 보면 제목이 들어가 있기 때문에 전체 행의 개수보다 하나 작은 값이 들어가 있을 것입니다. 

 

다음의 그림은 제가 실행을 해 본 결과인데 엑셀에는 235행의 데이터가 들어가 있습니다만, 실제로 데이터 개수는 제목을 빼야하기 때문에 총 234개가 되는 것입니다. 

 

다음의 for문은 added[i] 번째의 값이 True인 경우만 i+2라는 숫자와 함께 해당 리스트의 값을 찍어보고 있습니다. if에서 True인 값들만 선별해서 찍고 있기 때문에 i는 변하지만 added의 값은 항상 True가 됩니다.

 for i in range(len(added)):

  if added[i]:

  print(i+2, added[i])

 

처음에 added에 모두 True를 넣어줬고, 원본과 비교할 파일에서 서로 Key 값을 찾았을 때만 added에 False를 넣어줬습니다. 그렇기 때문에 비교할 파일에 해당하는 행의 Key 값이 원본 파일에 없다면 added의 값은 원래 넣어줬었던 True 값이 그대로 남아 있을 것이고, 그 행은 추가가 된 행이라고 보면 되는 것입니다. 그런데 i에 +2를 해 준 것은 다음과 같이 이전에 added의 j-2로 계산을 해 줬기 때문에 그 만큼을 보상해 주는 것입니다. 

 

  added[j-2] = False

 

이렇게 만들어준 코드를 실행해 보면 터미널에 나타나는 메시지는 다음과 같습니다. 여기에는 원본의 몇 번째 행이 비교파일에서 삭제가 되었는지를 먼저 나타내 줍니다. 그리고 len = 234 이후는 비교파일에서 추가가 된 행이 몇 번째 행인지를 표시해 줍니다. 

 

따라서 우리는 각각의 행의 내용을 그대로 복사해서 원본 파일에 넣어주고, 해당 셀들의 색상을 변경해서 새로운 파일로 저장을 해 주면 됩니다. 

 

 

추가 된 행을 복사해서 원본의 어느 위치에 넣는 것이 좋을까요? 추가된 행을 원본의 맨 마지막에 복사해 넣는 것은 가장 쉬운 방법 중의 하나입니다. 그런데 이런 방식으로 해 넣게 되면 비교할 파일이 만들어질 당시의 의도를 무시하는 경우가 되지 않나 생각이 듭니다. 그래서 가능하면 비교할 파일의 위치에 맞게 원본 파일에도 위치를 시키는 것이 좋겠다는 생각을 했습니다. 

 

 

어떻게 그 차이를 찾을 수 있을까 고민을 했습니다.

먼저 노란색이 표시되어 있는 엑셀은 그림 좌하단부, 우리가 만든 프로그램에 나타난 행으로 원본 파일 대비 추가된 내용이 들어 있습니다. 그리고 정면에 보이는 파일은 원본 파일입니다. 노란색으로 표시된 부분은 정면에 보이는 원본 파일에는 들어있지 않습니다. 

 

그래서 생각해낸 아이디어는 다음과 같습니다.

⑥ 비교할 파일에서 추가된 위치를 알고 있으므로 추가된 위치 다음의 Key 값을 읽어옵니다. 그런데 다음의 값 역시 새로 추가된 정보이면 원본 파일에서 찾을 수 없으므로 added에서 다음을 찾았을 때 True 값을 가지고 있으면 안됩니다. 즉, added에서 뒤로 그 값이 False인 것을 찾아서 그 위치에 해당하는 Key 값을 읽어와야 한다는 것입니다. 

⑦ 그렇게 읽어온 Key 값을 원본에서 찾아 그 위치를 찾아 빈 행을 삽입합니다. ⑧ 비교할 파일에서 추가된 내용을 원본의 삽입한 행에 하나씩 열을 복사해 오고 색을 변경합니다.  ⑨ 만일 비교할 파일에서 추가된 다음 위치에 값이 없으면 즉, 마지막 값이면 마지막에 행을 추가하고 ⑧과 같이 각각의 열을 복사해오고 색을 변경합니다.   

 

⑥ 비교할 파일에서 추가된 위치 다음의 Key 값을 읽기

060_excelKeyComp6.py

 for i in range(len(added)):

  if added[i]:

  added_key = ws_new.cell(i+2, 1).value

  next_key_not_added = None

  for j in range(i+1, len(added)):

  if added[j] == False:

  next_key_not_added = ws_new.cell(j+2, 1).value

  break

  print(i+2, added_key, next_key_not_added)

 

맨 마지막의 print() 함수가 있던 부분에 꽤 많은 수의 코드가 추가 되었습니다. 우선 added_key는 비교 파일에서 추가된 행 i + 2에 해당하는 Key 값을 가지고 있습니다. 다음은 added_key 다음에 추가가 되지 않은 Key  값을 찾아야 합니다. 쉽게 생각하면 i + 3에 해당하는 Key 값을 찾으면 되겠습니다.

 

하지만 비교 파일에 연속적으로 추가된 행이 있다고 하면, i + 2에서 추가된 key를 찾고 그 다음 두번째의 key를 찾은 다음, 두번째 key를 원본 파일에서 찾아서 그 앞에 행을 추가하려는 의도입니다. 그런데 i + 2의 다음에 있는 i + 3도 비교파일에서 추가된 행이라면  i + 3의 key 해당하는 값을 원본 파일에서 찾을 수 없어 오류가 발생합니다. 이와 같이 복잡한 for문이 들어간 것은 비교 파일에서 추가되지 않은 다음 값을 찾기 위함입니다. 

 

이것을 테스트 하기 위해서 원본 파일에서 100, 101번째의 파일을 삭제했습니다. 그러면 좌측에 출력된 것과 같이 99와 100번째 행이 True로 나타나게 됩니다. 제목이 있어서 1이 빠진 숫자죠. 

 

 

next_key_not_added는 처음에 None으로 초기화를 하고 for문에서 j는 현재의 다음 것, 즉 i + 1부터 끝까지 반복을 합니다. 반복하면서 처음 만난 added가 False인 것이 다음의 key 값이 되는  것이죠. 그런데 왜 처음에 None으로 초기화를 해 줬는가 하면, 만일에 추가된 값이 파일의 맨 마지막 값이라면 다음의 Key 값을 찾을 수 없을 것입니다. 그래서 이것을 체크하기 위해서  next_key_not_added를 None으로 넣어 준 것입니다. None이면 맨 마지막에 행을 추가하면 되겠죠.

 

⑦ 그렇게 읽어온 Key 값을 원본에서 찾아 그 위치를 찾아 빈 행을 삽입, ⑨ 원본에서 key를 못찾으면 맨 마지막에 행을 추가

 

061_excelKeyComp7.py

 for i in range(len(added)):

  if added[i]:

  added_key = ws_new.cell(i+2, 1).value

  next_key_not_added = None

  for j in range(i+1, len(added)):

  if added[j] == False:

  next_key_not_added = ws_new.cell(j+2, 1).value

  break

  print(i+2, added_key, next_key_not_added)

 

  mr_ori = ws_ori.max_row

  found = False

 

  for k in range(mr_ori):

  if ws_ori.cell(k + 1, 1).value == next_key_not_added 

  and next_key_not_added != None:

  print(">>>", k + 1, next_key_not_added)

  found = True

  ws_ori.insert_rows(k + 1)

  break

  if found == False:

  print(f">>> 맨 마지막에 추가 : {mr_ori+1}")

 

wb_ori.save(r".\files\address_diff3.xlsx")

 

맨 앞의 for문은 비교할 파일에서 추가된 행 만큼 반복을 하는 것이고, 그 안에 두 개의 추가적인 for문이 있습니다. 

그 중 첫번째는 위치를 파악했고, 두번째 for문에서는 원본 파일에서 추가된 파일이 삽입될 곳의 위치를 찾아서 빈 행을 추가해 주는 것입니다. 

 

  mr_ori = ws_ori.max_row

 

mr_ori는 원본 파일이 총 몇 행인지를 나타내는 변수로 max_row라는 변수가 자동으로 계산해서 그 값을 가지고 있습니다. 그런데 왜 매번 두 번째 for문을 실행하기 전에 이 값을 mr_ori에 넣는 것일까요? 

왜냐하면 다음의 for문을 실행하면 원본 파일에 한 행이 추가되기 때문에 매번 mr_ori를 확인하는 것입니다. 원본 파일에 100개의 데이터가 있었고 사본 파일에서 5개의 행이 추가되었다면 처음 두번째 for문을 시작할 때는 mr_ori가 100이지만 그 다음에는 한 행이 추가되어 101, 102와 같이 점점 커지기 때문입니다. 

 

  found = False

 

이 변수는 사용될 확률이 딱 한번입니다. 

언제인가 하면 비교할 파일에서 데이터가 추가된 곳이 파일의 맨 마지막일 경우 입니다. 엑셀에서 한 행을 선택하고 빈 행을 삽입하게 되면 선택된 행의 바로 앞에 한 행이 추가가 됩니다. 그렇기 때문에 추가된 데이터를 삽입하기 위해서 그 다음에 있는 key 값을 찾았습니다. 그런데 마지막에 추가된 행의 경우에는 다음행에서 key 값을 찾을 수 없습니다. 맨 마지막 행이기 때문이죠. key 값을 찾았을 때, 이 변수는 True로 변경이 됩니다. 만일 변경이 되지 않고 마지막까지 False로 남아 있다면 이 변수는 맨 마지막 행에 있는 정보가 되는 것입니다.

 

  for k in range(mr_ori):

  if ws_ori.cell(k + 1, 1).value == next_key_not_added \

  and next_key_not_added != None:

  print(">>>", k + 1, next_key_not_added)

  found = True

  ws_ori.insert_rows(k + 1)

  break

  if found == False:

  print(f">>> 맨 마지막에 추가 : {mr_ori+1}")

 

for 문에서는 삽입될 key의 다음 key가 있는 곳에 위치를 찾아 insert_row(k + 1)을 통해서 한 행을 추가합니다. 그리고 for문이 끝나고 난 후에 found가 바뀌지 않았는지 확인해서 바뀌지 않았다면 맨 뒤의 행에 자료를 추가하게 됩니다. 

 

그럼 for문의 내부를 살펴보겠습니다.

  if ws_ori.cell(k + 1, 1).value == next_key_not_added \

  and next_key_not_added != None:

 

앞에서 next_key_not_added는 추가된 key의 다음 key 중에서 처음 나오는 기존에 있던 자료의 key를 가지고 있습니다. 이 값이 None으로 되어 있다는 것은 맨 마지막 자료를 의미하는 것입니다. 그래서 맨 마지막 자료가 아니며, next_key_not_added인 key 값을 찾습니다. 이렇게 찾아진 key에서 추가를 하면 바로 앞의 행에 자료가 추가되는 것입니다. 

 

바로 다음 줄에는 ‘행이 추가될 곳을 찾았습니다’라는 의미에서 found 변수를 True로 변경해 줍니다.

 

  found = True

 

찾았으면 insert, 행을 추가해줘야겠죠. 그래서 원본 ws_ori에서 insert_rows를 이용해서 한 행을 삽입해 줍니다.

 

  ws_ori.insert_rows(k + 1)

 

다음은 값을 찾았기 때문에 추가로 찾을 필요가 없어 break 문을 통해서 for문을 빠져나갑니다. 

 

  if found == False:

  print(f">>> 맨 마지막에 추가 : {mr_ori+1}")

 

마지막에 있는 if문은 비교할 파일에서 맨 마지막에 추가된 데이터의 경우입니다. 즉, 추가된 key의 다음 key를 찾지 못한 경우입니다.

 

⑧ 비교할 파일에서 추가된 내용을 원본의 삽입한 행에 하나씩 열을 복사해 오고 색을 변경

순서가 조금 바뀌긴 했습니다만, 실제로 변경된 파일에서 각각의 값들을 원본으로 복사를 해 오는 과정을 진행해 보겠습니다.

 

062_excelKeyComp8.py

 for k in range(mr_ori):

  if ws_ori.cell(k + 1, 1).value == next_key_not_added \

  and next_key_not_added != None:

  # print(">>>", k + 1, next_key_not_added)

  found = True

  ws_ori.insert_rows(k + 1)

  for m in range(1, mc_new + 1):

  ws_ori.cell(k + 1, m).value = ws_new.cell(i + 2, m).value

  ws_ori.cell(k + 1, m).fill = added_color

  break

 

 if found == False:

  # print(f">>> 맨 마지막에 추가 : {mr_ori+1}")

  for m in range(1, mc_new + 1):

  ws_ori.cell(mr_ori + 1, m).value = ws_new.cell(i + 2, m).value

  ws_ori.cell(mr_ori + 1, m).fill = added_color

 

 wb_ori.save(r".\files\address_diff4.xlsx")

 

실제로 데이터를 복사해 오는 과정입니다. 셀을 선택하고 그 상태에서 행을 삽입하면 선택한 행의 위치가 삽입한 행의 위치가 됩니다. 예를 들어 7번째 행을 선택하고 행 삽입을 하면 현재 행은 8행이 되고 삽입된 행이 7행이 되는 것이죠.

 

  ws_ori.insert_rows(k + 1)

  for m in range(1, mc_new + 1):

  ws_ori.cell(k + 1, m).value = ws_new.cell(i + 2, m).value

  ws_ori.cell(k + 1, m).fill = added_color

 

그래서 삽입한 것과 같은 위치인 k + 1에 복사를 해 넣습니다. 열의 번호는 1부터 시작하기 때문에 for문에서 range가 1부터 mc_new + 1까지가 되구요. 왜냐하면 for문의 range는 기본적으로 0부터 시작하기 때문에 1씩 더해 준 것이죠. 그리고 복사해 올 값은 ws_new에서 i + 2 번째 행이 됩니다. 이유는 added 리스트에 있습니다. 우리의 엑셀은 1부터 행이 시작하는데 1행이 제목이므로 2부터 입니다. 그런데 added는 리스트이므로 처음이 1부터 입니다. 그래서 2를 더해줄 필요가 생긴 것입니다. added의 처음은 제목도 없이 0부터 시작이고 ws_new는 1부터 시작이라 1이 차이가 나는데 거기에 제목까지 있으므로 2가 차이가 나게 되는 것입니다.

 

  # print(f">>> 맨 마지막에 추가 : {mr_ori+1}")

  for m in range(1, mc_new + 1):

  ws_ori.cell(mr_ori + 1, m).value = ws_new.cell(i + 2, m).value

  ws_ori.cell(mr_ori + 1, m).fill = added_color

 

맨 마지막에 추가되는 행의 경우에는 k + 1 대신에 mr_ori + 1을 해 주고 있습니다. 이유는 전체 개수가 mr_ori이기 때문에 맨 마지막 행 다음에 넣는 것입니다. 여기는 insert를 해 주지 않는데 그 이유는 맨 마지막이기 때문에 맨 마지막 다음의 비어있는 셀들에 값을 넣기 때문입니다.

 

 

결과로 나온 파일을 보면 삭제된 내용은 파란색에 취소선이 들어가 있고, 추가된 행은 주황색, 그리고 변경이 된 셀들을 노란색에 원래 값과 변경된 값이 나타납니다.

 

구분코드로 된 엑셀 파일을 비교하는 루틴이 아마도 가장 길고 복잡한 코딩이 아니었나 싶습니다. 지금까지 해 온 순서대로 하나 하나 차근 차근 검사해 가면서 코딩을 해야 합니다. 중간 중간 print() 함수를 이용해서 값들을 확인하면서 코딩을 하고, 확인이 되면 필요 없는 코드들은 삭제를 하면 됩니다. 이런 방식으로 복잡한 코딩을 단순화 시켜서 하나씩 진척을 이뤄야 합니다. 

이 코드 중에서는 추가된 내용을 순서에 맞게 위치시키는 것이 가장 복잡한 코딩이 아니었나 싶습니다. 이렇게 복잡한 것을 단순화 시킬 수도 있습니다. 추가된 것은 파일 맨 뒤에 넣는 것이 하나의 방식이 되지 않을까요? 그래도 추가된 것과 변경, 삭제 된 것은 한 파일에 표시되니까요.

하지만 사람의 욕심은 끝이 없고 우리가 만든 프로그램의 결과물을 보는 사람들의 시선은 그렇지 않을때가 많습니다. 이왕 만드는거 추가된 위치에 넣어주면 더 편하지 않느냐는 것이죠.

 

여기까지 따라오셨다면 엑셀 관련한 프로그램은 마음만 먹으면 자유롭게 하실 수 있지 않을까 싶습니다. 우리가 하려고 하는 RPA, Robotic Process Automation은 단순 반복적인 작업을 빠르게 하기 위함입니다. 여기에 셀의 크기를 조정하고 폰트를 바꾸는 등의 작업은 코딩을 통하기 보다 익숙한 우리가 직접 작업하는 것이 더 빠르고 보기 좋고 수월하게 할 수 있습니다.