파이썬으로 엑셀 다루기(完)

한동안 교내 CTF 준비 등 여러가지 이유로 파이썬 공부를 하지 못하였다가 9월 말에 들어와서 다시 파이썬을 시작하였다.

저번에 사용자에게 값을 받고 그 값을 찾아주는 것 까지 끝냈다.

이제 그 뒤로부터 시작하려 한다.

이전까지 했던 코드

import openpyxl
import operator

of = openpyxl.load_workbook("C:/Users/박상근/Desktop/port.xlsx")
sheet = of.active
wb = openpyxl.Workbook()

ro = int(input("찾고싶은 열을 입력하세요 :"))
find = int(input("찾고 싶은 값을 입력하세요 :")) 

for i in sheet.rows: # 세로 전체(행)까지
    for j in range(0,5):
        if(operator.eq(i[ro].value,find)):
            print(i[j].value)

이제 값을 받아온 것을 새로운 엑셀 파일을 열어서 그 엑셀에 집어 넣어보도록 하겠다.

1. 리스트 생성 및 리스트에 값 저장

엑셀에서 읽어온 값을 새로운 엑셀 파일을 쓰기 위해서 그 값을 저장하려고 하였다.

이 때 list()를 통하여 새로운 변수를 만들었다.

import openpyxl
import operator

of = openpyxl.load_workbook("C:/Users/박상근/Desktop/port.xlsx")
sheet = of.active
wb = openpyxl.Workbook()

ro = int(input("찾고싶은 열을 입력하세요 :"))
find = int(input("찾고 싶은 값을 입력하세요 :")) 

a = list()     # 새로운 리스트 생성

for i in sheet.rows: # 세로 전체(행)까지
    for j in range(0,5):
        if(operator.eq(i[ro].value,find)):
            
            a.append(i[j].value)    #a라는 리스트에 엑셀에서 읽어온 값 저장하기

a = list()를 통해 새로운 리스트를 선언을 하였고  a.append라는 함수를 사용하여 엑셀에서 읽어온 값을 a리스트에 저장하였다.

2. 1차원 배열을 2차원 배열로 변경

이 부분을 해결하는데 1주 이상 걸렸다.

책도 1차원 배열을 2차원 배열로 바꾸는 법을 안 알려주었다.

그래서 인터넷을 오지게 뒤졌다.

2차원 배열로 왜 변경하는지는 나중에 엑셀에 값을 넣을 때 알 수 있다.

import openpyxl
import operator

of = openpyxl.load_workbook("C:/Users/박상근/Desktop/port.xlsx")
sheet = of.active
wb = openpyxl.Workbook()

ro = int(input("찾고싶은 열을 입력하세요 :"))
find = int(input("찾고 싶은 값을 입력하세요 :")) 

a = list()     # 새로운 리스트 생성

for i in sheet.rows: # 세로 전체(행)까지
    for j in range(0,5):
        if(operator.eq(i[ro].value,find)):
            
            a.append(i[j].value)    #a라는 리스트에 엑셀에서 읽어온 값 저장하기
            
n=5    # 내부IP, 내부 IP 뒷자리 주소, 외부포트, 내부포트, 룰이름 총 5개에 보기가 있기 때문에
result = [a[i*n:(i+1)*n]for i in range((len(a) + n-1)//n)] # 2차원 배열에서 앞에부분의 갯수 파악 용

그러다 인터넷을 오지게 뒤져가지고 2차원 배열로 바꿔주는 코드를 알아냈다.

result가 있는 코드는 2차원 배열a[i][j]가 있다 가정할 때 i의 범위를 나타낸다.

코드를 해석하면 i의 범위가 (a 배열의 길이 + n-1)/n인데 소수점을 지워주는 것이다.

다음과 같은 방식을 수행하면 1차원 배열을 2차원 배열로 바꿀 수 있다.

 

 

3. excel 쓰기 파일 열기

import openpyxl
import operator

of = openpyxl.load_workbook("C:/Users/박상근/Desktop/port.xlsx")
sheet = of.active
wb = openpyxl.Workbook()

ro = int(input("찾고싶은 열을 입력하세요 :"))
find = int(input("찾고 싶은 값을 입력하세요 :")) 

a = list()     # 새로운 리스트 생성

for i in sheet.rows: # 세로 전체(행)까지
    for j in range(0,5):
        if(operator.eq(i[ro].value,find)):
            
            a.append(i[j].value)    #a라는 리스트에 엑셀에서 읽어온 값 저장하기
            
n=5    # 내부IP, 내부 IP 뒷자리 주소, 외부포트, 내부포트, 룰이름 총 5개에 보기가 있기 때문에
result = [a[i*n:(i+1)*n]for i in range((len(a) + n-1)//n)] # 2차원 배열에서 앞에부분의 갯수 파악 용

of.close()     # open했던 읽기 전용 엑셀 파일 닫기
ws = wf.active   # 워크시트 활성화

이제 값을 불러 왔으니까 값을 새로운 excel 파일에 내가 입력한 값을 써보도록 하겠다.

일단 excel 파일을 쓰기 위해서는 Openpyxl모듈에 Workbook() 함수를 사용해야 한다.

그 후 워크시트를 활성화 시키기위해 active를 해준다.

 

4. head셀 쓰기

wf = openpyxl.Workbook()
ws = wf.active
ws['A1']='내부 IP'  # 첫번째 헤드셀 "내부 IP" 입력
ws['C1']='외부포트'  # 두번째 헤드셀 "외부포트" 입력
ws['D1']='내부포트'  # 세번째 헤드셀 "내부포트" 입력
ws['E1']='룰이름'   # 네번째 헤드셀 "룰이름" 입력

과제 내용을 확인해보면 새로 오픈하는 파일에도 head셀은 항상 존재해야 한다고 나와있다.

그래서 excel에서 받아온 값을 적기 전에 head셀을 만들어봤다.

다음과 같이 작성함으로서 이 프로그램이 작성될 때마다 새로 만들어지는 헤드셀에는 다음과 같은 4개의 셀이 들어가게 된다.

 

4. 받아온 값을 새로운 엑셀파일에 집어넣기

for i in range(2,num+2):    # 첫번째는 헤드셀이므로 2번째 셀부터 사용
    for j in range(0,5):    # 헤드셀 갯수(5개)
        if j==1 : continue  # 2번째 셀(IP주소 뒷자리)인 경우 적지 않아야 하기 때문에 패스
        else :
            ws.cell(row=i, column= j+1).value = result[i-2][j] # i 행 j+1열에 받아온 값을 집어넣음

코드를 봐보면 첫번째에는 헤드셀(주제)가 들어가야 하기 때문에 첫번째 셀이 아닌 2번째 셀부터 사용하였다.

그리고 num은 행의 갯수를 의미하는데 num+2를 한 이유는 for문의 시작값이 2부터 이기 때문이다.

그 후 for문을 하나 더 돌리는데 이것은 열을 의미한다.

-> 첫번째는 for문은 행 두번째 for문은 열을 의미한다.

다음으로 if j==1 부분인데 과제를 보면 IP주소 뒷자리가 있는 칸은 비워놓으라고 했기 때문에 그 부분에 해당되는 칸에 for문이 도착하면 바로 건너뛰게 하기 위해서 continue 문을 사용하였다.

만약 j!=1이면 ws.cell.value를 이용하여 해당 행,열에 읽기 파일에서 가져온 값으로 집어 넣어준다.

 

5. 저장할 파일명 받아오기

name = input("저장할 파일명을 입력하세요 :")
xlsx = ".xlsx"
filename = name+xlsx

wf.save(" ~~~~/"+filename)

이제 모두 다 완성했으니 사용자에게 파일명을 받아와서 그 파일명으로 쓰기 파일을 저장을 해보도록 하겠다.

우선 input문을 이용하여 사용자에게 저장할 파일명을 받아오고  받아온 이름과 xlsx를 붙여 filename이라는 변수를 만들어 준다.

그 후 wf.save를 이용하여 파일을 저장해 주면 된다.

wf.save("경로"+filename)을 사용해 " "안에 경로를 적어주고 뒤에 filename을 붙여 경로 안에 filename으로 엑셀 파일을 저장하게 된다.

6. 전체코드

import openpyxl
import operator

of = openpyxl.load_workbook("파일경로"+읽어올 파일) # port.xlsx 파일 오픈
sheet = of.active  # sheet 활성화
wb = openpyxl.Workbook() # 쓰기용으로 파일 열기'

a= list()
print("1. 내부IP\n2. 외부 포트\n3.내부 포트\n4.룰 이름")

ro = int(input("찾고싶은 열을 입력하세요 :")) # 찾고싶은 해(가로)
find = int(input("찾고 싶은 값을 입력하세요 :")) # 찾고싶은 값
name = input("저장할 파일명을 입력하세요 :")
xlsx = ".xlsx"
filename = name+xlsx
for i in sheet.rows: # 세로 전체(행)까지
    for j in range(0,5):
        if(operator.eq(i[ro].value,find)):
            a.append(i[j].value)
n=5
result = [a[i*n:(i+1)*n]for i in range((len(a) + n-1)//n)]

num = int(len(a)/5)

of.close()
wf = openpyxl.Workbook()
ws = wf.active
ws['A1']='내부 IP'
ws['C1']='외부포트'
ws['D1']='내부포트'
ws['E1']='룰이름'

for i in range(2,num+2):
    for j in range(0,5):
        if j==1 : continue
        else :
            ws.cell(row=i, column= j+1).value = result[i-2][j]
wf.save("저장경로"+filename)

전체적인 코드이다.

전체적으로 코드 짜는데는 시간이 얼마 걸리지는 않았지만 읽기 파일에서 1차원으로 받아오는 파일을 2차원으로 바꿔주는 작업에서 너무 많은 시간이 소요되었다.

 

 

7.  프로그래밍 결과

다음과 같이 찾고 싶은 값과 파일명을 입력하면

 

다음과 같이 파일명(psk)라는 엑셀파일에 다음과 같이 값이 저장이 되게 된다.

읽기 파일과 비교해보면

원본 파일과 내가 새로 만든 파일 안의 내용이 똑같은 것을 확인할 수 있다.

결과물은 검토를 받고 있고 만약 통과가 되면 새로운 과제를 준다고 하였다.

만약 내 코드에 문제가 생길 경우 수정해서 다시 올리도록 하겠다.