[python] 엑셀 자동화(openpyxl)

터미널에 아래명령어를 입력하여 openpyxl를 설치합니다.

pip install openpyxl

 

1. 엑셀 파일 및 시트 생성 및 저장

import openpyxl

# 새로운 파일생성
wb = openpyxl.Workbook()

# 현재 활성화된 시트 선택
ws = wb.active

# 시트명 변경
ws.title = "시트명"

# 새로운 시트 생성
ws = wb.create_sheet("시트명")

# 모든 시트이름 출력
print(wb.sheetnames)

# 시트 삭제
del wb['삭제할 시트명']

# 엑셀 저장
wb.save("파일 경로 및 파일명.xlsx")

 

 

2.엑셀파일 불러와서 수정하기

import openpyxl


#경로 지정
save_path = "파일 경로 및 파일명.xlsx"

# 엑셀파일 불러오기
wb = openpyxl.load_workbook(save_path)

#활성화된 시트 선택
ws = wb.active

#데이터 추가

#### 방법1 #####

ws['A1'] = "날짜" # A1 셀에 날짜를 입력
ws['B1'] = "수량"
ws['C1'] = "금액"
ws['D1'] = "합계"

#### 방법2 #####

ws.cell(row = 2,column=1,value='2023-09-05') #두번째 행, 첫번째 열의 값을 2023-09-05를 넣는다.
ws.cell(row = 2,column=2,value=30)
ws.cell(row = 2,column=3,value=500)
ws.cell(row = 2,column=4,value='=C2*D2') #수식을 넣어도 동작한다.

#### 방법3 #####

ws.append('2023-09-05',40,500,'=C3*D3') # 순서대로 넣는다. 실행할때마다 계속 생성한다.


# 데이터 수정
ws['B2'] = 50
ws['C2'] = 1000

# 데이터 삭제

del ws['A3'] # A3 셀을 삭제

# 엑셀 저장
wb.save(save_path)

 

3. 엑셀 데이터 가져오기

import openpyxl


#경로 지정
save_path = "파일 경로 및 파일명.xlsx"

# 엑셀파일 불러오기
wb = openpyxl.load_workbook(save_path,data_only=True) # data_only=True 수식이 아닌 데이터형식으로 나온다.

# 데이터 시트 선택
ws = wb['시트명']

 

1) 행과 열의 개수를 아는 경우

# 예) 행의 개수가 9개 열의개수가 6개일때
for x in range(1,9+1):
    for y in range(1,6+1):
        print(ws.cell(row=x,column=y).value,end=" ")
    print()

 

2) 행과 열의 개수를 모르는 경우

for x in range(1,ws.max_row+1):
    for y in range(1,ws.max_column+1):
        print(ws.cell(row=x,column=y).value,end=" ")
    print()

 

 

3) 모든 행 가져오기

for row in ws.iter_rows():
    print(row)

4) 특정 행부터 가져오기

# 예)2번째 행부터 가져오기
for row in ws.iter_rows(min_row=2):
    print(row)

5) 특정 행부터 특정행까지 가져오기

# 예) 2번째 행부터 5번째 행까지
for row in ws.iter_rows(min_row=2,max_row=5):
    print(row)

 

6) 특정행부터 특정행까지, 특정열부터 특정열까지 가져오기

# 예) 2-5행,3-4열
for row in ws.iter_rows(min_row=2,max_row=5,min_col=3,max_col=4):
    print(row)

 

7) 셀안의 정보 가져오기

# 예) 2-5행,3-4열
for row in ws.iter_rows(min_row=2,max_row=5,min_col=3,max_col=4):
    for cell in row:
        print(cell.value,end=" ")
    print()

 

8) 데이터 합치기

import openpyxl

# 새로운 엑셀파일 생성
total_wb = openpyxl.Workbook()

# 엑셀파일 활성화
total_ws = total_wb.active

# 시트이름 변경
total_ws.title = "data_sum"

# 헤더추가

total_ws.append(['첫번째 열','두번째 열','세번째 열'])

# 데이터 파일
file_list = ['첫번째 파일명','두번째 파일명','세번째 파일명']

for file in file_list:
    wb = openpyxl.load_workbook(f"파일경로/{file}.xlsx",data_only=True)
    ws = wb.active
    for row in ws.iter_rows(min_row=2): # 2번째 행부터 끝까지의 데이터를  row에 담는다
        data = []
        for cell in row: # row에서 cell 정보를 가져온다.
            data.append(cell.value) # 셀의 값을 데이터 리스트에 추가한다.
        total_ws.append(data)


####순번 업데이트###
## 방법1 ##
for row in total_ws.iter_rows(min_row =2,max_col=1):
    for cell in row:
        cell.value = row[0].row -1 # 첫번째 행의 index -1을  셀에 저장한다.

## 방법2 ##
i =0
for cell in total_ws['A']: # A열에 접근한다.
    if i !=0:
        cell.value = i
    i = i+1
    
#################

total_wb.save("엑셀 경로 및 파일명")

 

9)데이터 분리하기

import openpyxl

save_path = "수정할 엑셀파일.xlsx"
wb = openpyxl.load_workbook(save_path)

ws = wb.active

#제품명 리스트
name_list= []

for row in ws.iter_rows(min_row=2,min_col=2):
    name = row[0].value
    if name not in name_list: # 제품명이 name_list에 없으면
        name_list.append(name) # name_list에 추가하고
        wb.create_sheet(name) # 시트 추가

    data = []

    for cell in row:
        data.append(cell.value)

    wb[name].append(data)

wb.save(save_path)