컴퓨터/Python
python 엑셀투파이썬 pandas 기존데이터 지우고 덮어쓰기 xls xlsx xlsm
풍경소리^^
2024. 10. 16. 17:54
https://www.youtube.com/playlist?list=PLrpXwtuxGqcJ03uulbSY3CEUK_n6ySb2t
엑셀유저 파이썬(판다스) 입문
www.youtube.com
참조 엑셀투파이썬
실행시간 측정
xls 경과시간:0.06530 sec
xlsx 경과시간:1.18069 sec
xlsm 경과시간:3.54817 sec
import pandas as pd
import os
import xlrd
import openpyxl
from openpyxl.utils.cell import column_index_from_string # 엑셀 열 이름을 숫자로 변환
from openpyxl.utils import get_column_letter # 숫자를 엑셀 열 이름으로 변환
# import xlwt # xlwt는 .xls 파일을 수정하기 위해 사용합니다.
from xlutils.copy import copy # xlutils를 사용하여 기존 .xls 파일 복사
import xlwings as xw # xlwings 사용
import time
start = time.time()
# FILE = "01vlookup.xls" # .xls 파일 경로
# FILE = "01vlookup.xlsx" # .xlsx 파일 경로
FILE = "01vlookup.xlsm" # .xlsm 파일 경로
# df1 설정 ----------
SOURCE_SH = "Sheet1"
COL_START = "A"
COL_END = "B"
# 파일 확장자 확인
file_ext = os.path.splitext(FILE)[1] # 확장자만 추출 (.xls, .xlsx, .xlsm)
COUNT_SKIPROW = 0 # 초기화
if file_ext == '.xls':
# .xls 파일 처리 (xlrd 엔진 사용)
wb = xlrd.open_workbook(FILE)
ws = wb.sheet_by_name(SOURCE_SH)
# "이름"이 나올 때까지 카운트
for row in range(ws.nrows): # 총 행 수 만큼 반복
if ws.cell_value(row, 0) == "이름": # 첫 번째 열에서 "이름" 확인
break
COUNT_SKIPROW += 1 # "이름"이 나오기 전까지 카운트
# DataFrame 생성
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START}:{COL_END}",
engine='xlrd')
elif file_ext == '.xlsx':
# .xlsx 파일 처리 (openpyxl 엔진 사용)
FIND_COL = "A"
FIND_TITLE = "이름"
COUNT_SKIPROW = 0 # 초기화
wb = openpyxl.load_workbook(FILE) # 워크북 객체 생성
ws = wb[SOURCE_SH] # 시트 객체 생성
for row in ws.iter_rows(min_col=column_index_from_string(FIND_COL), max_col=column_index_from_string(FIND_COL)): # A열만 확인
if row[0].value == FIND_TITLE: # "이름"이 있는지 확인
break
COUNT_SKIPROW += 1 # "이름"이 나오기 전까지 카운트
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START}:{COL_END}",
engine='openpyxl')
elif file_ext == '.xlsm':
# .xlsm 파일 처리 (xlwings 엔진 사용)
app = xw.App(visible=False) # 숨김 모드에서 Excel을 엽니다.
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# 첫 번째 행에서 "이름"을 찾는 과정
header_row = 1
while ws.range(f'A{header_row}').value != "이름": # A열에서 "이름" 찾기
header_row += 1
# A열의 마지막 데이터가 있는 행 찾기
last_row = ws.range('A4').end('down').row
# 데이터 가져오기
df1 = pd.DataFrame(ws.range(f"{COL_START}{header_row}:{COL_END}{last_row}").value)
df1.columns = df1.iloc[0] # 첫 번째 행을 헤더로 설정
df1 = df1[1:] # 첫 번째 행(헤더)을 DataFrame에서 제거
# Excel 파일 닫기
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다. .xls, .xlsx, .xlsm 파일을 사용하십시오.")
# skiprow 구하기 - 데이터 없는 행 갯수 - 무시해야 할 행 갯수
FIND_COL = "F"
FIND_TITLE = "제품"
COUNT_SKIPROW = 0 # 초기화
if file_ext == '.xls':
for row in range(ws.nrows):
if ws.cell_value(row, 5) == FIND_TITLE: # F열에서 "제품" 확인 (열 인덱스는 0부터 시작)
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsx':
for row in ws.iter_rows(min_col=column_index_from_string(FIND_COL), max_col=column_index_from_string(FIND_COL)): # F열만 확인
if row[0].value == FIND_TITLE: # "제품"이 있는지 확인
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsm':
app = xw.App(visible=False) # 숨김 모드에서 Excel을 엽니다.
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
while ws.range(f'F{COUNT_SKIPROW + 1}').value != FIND_TITLE:
COUNT_SKIPROW += 1
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# df2 데이터 생성 ################################
COL_START = "F"
COL_END = "G"
COL_TITLE_NAME = ['제품', '가격']
if file_ext == '.xls':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_NAME,
index_col=None,
usecols=f"{COL_START}:{COL_END}",
skiprows=COUNT_SKIPROW,
engine='xlrd')
elif file_ext == '.xlsx' or file_ext == '.xlsm':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_NAME,
index_col=None,
usecols=f"{COL_START}:{COL_END}",
skiprows=COUNT_SKIPROW,
engine='openpyxl')
# df3 데이터 병합 ################################
COL_STANDARD_NAME = "제품"
JOIN_HOW = "left"
df3 = df1.merge(df2, on=COL_STANDARD_NAME, how=JOIN_HOW)
# 가격을 정수형으로 변환
df3['가격'] = df3['가격'].astype(int)
# 특정 범위 지우기 ########################################
COL_START = "J"
COL_END = "L"
START_ROW = 4 # 제목열 타이핑 되어있는 상태 - 데이터 시작행 0,1,2,3,4,5
# START_CELL = f'A{START_ROW}' # START_ROW를 기반으로 시작 셀 설정
if file_ext == '.xls':
# xlrd로 파일 열기
wb = xlrd.open_workbook(FILE, formatting_info=True) # 포맷 정보를 가져오기
ws = wb.sheet_by_name(SOURCE_SH)
# 수정할 Workbook 생성
new_wb = copy(wb) # 기존 워크북 복사
new_ws = new_wb.get_sheet(0) # 첫 번째 시트 가져오기
# 데이터 지우기
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1):
for row in range(START_ROW, ws.nrows): # START_ROW부터 끝까지 반복
new_ws.write(row, column_letter - 1, '') # 값을 빈 문자열로 설정하여 지우기
# 수정된 파일 저장
new_wb.save(FILE)
elif file_ext == '.xlsx' or file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# START_ROW부터 마지막 데이터 행까지 찾음
START_CELL = f'A{START_ROW}' # START_ROW를 기반으로 시작 셀 설정
last_row = ws.range(START_CELL).end('down').row # START_ROW부터 마지막 데이터가 있는 행 확인
# 밑에 데이터가 없는 경우, last_row가 START_ROW와 같을 수 있음
if last_row == START_ROW:
print("삭제할 데이터가 없습니다.")
else:
# START_ROW + 1 부터 마지막 행까지 데이터를 지움 (제목 행은 제외)
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1):
column_cells = ws.range(f'{get_column_letter(column_letter)}{START_ROW + 1}:' # 제목행 다음부터
f'{get_column_letter(column_letter)}{last_row}')
column_cells.value = None # 제목행 이후 데이터만 지우기
wb.save()
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# 최종 파일 저장 특정 범위에 쓰기
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True) # 포맷 정보를 가져오기
new_wb = copy(wb) # 기존 워크북 복사
new_ws = new_wb.get_sheet(0) # 첫 번째 시트 가져오기
# df3의 데이터를 새로운 위치에 쓰기
for row_idx, row in df3.iterrows():
for col_idx, value in enumerate(row):
new_ws.write(START_ROW + row_idx, column_index_from_string(COL_START) - 1 + col_idx, value)
new_wb.save(FILE)
# 시간측정
end = time.time()
print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsx':
with pd.ExcelWriter(FILE, mode='a', engine='openpyxl', if_sheet_exists='overlay') as w:
# df3.to_excel(w, sheet_name='Sheet1', index=False, header=False, startcol=column_letter_to_number(start_column), startrow=start_row) # 제목열 제외하고 데이터 시작행 0,1,2,3,4,5
# df3.to_excel(w, sheet_name='Sheet1', index=False, header=False, startcol=ord(start_column) - ord('A'), startrow=start_row) # 제목열 제외하고 데이터 시작행 0,1,2,3,4,5
df3.to_excel(w, sheet_name=SOURCE_SH, index=False, header=False, startcol=column_index_from_string(COL_START) - column_index_from_string("A"), startrow=START_ROW) # 제목열 제외하고 데이터 시작행 0,1,2,3,4,5
# 시간측정
end = time.time()
print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# df3의 데이터를 새로운 위치에 쓰기
for row_idx, row in df3.iterrows():
ws.range(f"{COL_START}{START_ROW + 1 + row_idx}").value = row.values.tolist()
wb.save()
wb.close()
app.quit()
# 시간측정
end = time.time()
print("xlsm 경과시간:"f"{end - start:.5f} sec")01vlookup.xls
0.01MB
01vlookup.xlsm
0.01MB
01vlookup.xlsx
0.01MB
pandas_exceltopython004.py
0.01MB
ChatGPT 속도개선코드
import pandas as pd
import os
import xlrd
import openpyxl
from openpyxl.utils.cell import column_index_from_string
from openpyxl.utils import get_column_letter
from xlutils.copy import copy
import xlwings as xw
import time
start = time.time()
# FILE = "01vlookup.xls" # .xls 파일 경로
# FILE = "01vlookup.xlsx" # .xlsx 파일 경로
FILE = "01vlookup.xlsm" # .xlsm 파일 경로
# df1 설정
SOURCE_SH = "Sheet1"
COL_START = "A"
COL_END = "B"
file_ext = os.path.splitext(FILE)[1]
COUNT_SKIPROW = 0 # 초기화
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE)
ws = wb.sheet_by_name(SOURCE_SH)
for row in range(ws.nrows):
if ws.cell_value(row, 0) == "이름":
break
COUNT_SKIPROW += 1
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START}:{COL_END}",
engine='xlrd')
elif file_ext == '.xlsx':
FIND_COL = "A"
FIND_TITLE = "이름"
COUNT_SKIPROW = 0
wb = openpyxl.load_workbook(FILE)
ws = wb[SOURCE_SH]
for row in ws.iter_rows(min_col=column_index_from_string(FIND_COL), max_col=column_index_from_string(FIND_COL)):
if row[0].value == FIND_TITLE:
break
COUNT_SKIPROW += 1
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START}:{COL_END}",
engine='openpyxl')
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
header_row = 1
while ws.range(f'A{header_row}').value != "이름":
header_row += 1
last_row = ws.range('A4').end('down').row
df1 = pd.DataFrame(ws.range(f"{COL_START}{header_row}:{COL_END}{last_row}").value)
df1.columns = df1.iloc[0]
df1 = df1[1:]
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
FIND_COL = "F"
FIND_TITLE = "제품"
COUNT_SKIPROW = 0
if file_ext == '.xls':
for row in range(ws.nrows):
if ws.cell_value(row, 5) == FIND_TITLE:
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsx':
for row in ws.iter_rows(min_col=column_index_from_string(FIND_COL), max_col=column_index_from_string(FIND_COL)):
if row[0].value == FIND_TITLE:
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
while ws.range(f'F{COUNT_SKIPROW + 1}').value != FIND_TITLE:
COUNT_SKIPROW += 1
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
COL_START = "F"
COL_END = "G"
COL_TITLE_NAME = ['제품', '가격']
if file_ext == '.xls':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_NAME,
index_col=None,
usecols=f"{COL_START}:{COL_END}",
skiprows=COUNT_SKIPROW,
engine='xlrd')
elif file_ext == '.xlsx' or file_ext == '.xlsm':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_NAME,
index_col=None,
usecols=f"{COL_START}:{COL_END}",
skiprows=COUNT_SKIPROW,
engine='openpyxl')
# df3 데이터 병합
COL_STANDARD_NAME = "제품"
JOIN_HOW = "left"
df3 = df1.merge(df2, on=COL_STANDARD_NAME, how=JOIN_HOW)
df3['가격'] = df3['가격'].astype(int)
# 특정 범위 지우기
COL_START = "J"
COL_END = "L"
START_ROW = 4
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
ws = wb.sheet_by_name(SOURCE_SH)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1):
for row in range(START_ROW, ws.nrows):
new_ws.write(row, column_letter - 1, '')
new_wb.save(FILE)
elif file_ext == '.xlsx' or file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
START_CELL = f'A{START_ROW}'
last_row = ws.range(START_CELL).end('down').row
if last_row == START_ROW:
print("삭제할 데이터가 없습니다.")
else:
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1):
column_cells = ws.range(f'{get_column_letter(column_letter)}{START_ROW + 1}:'
f'{get_column_letter(column_letter)}{last_row}')
column_cells.value = None
wb.save()
wb.close()
app.quit()
# 최종 파일 저장
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for row_idx, row in df3.iterrows():
for col_idx, value in enumerate(row):
new_ws.write(START_ROW + row_idx, column_index_from_string(COL_START) - 1 + col_idx, value)
new_wb.save(FILE)
end = time.time()
print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsx':
with pd.ExcelWriter(FILE, mode='a', engine='openpyxl', if_sheet_exists='overlay') as w:
df3.to_excel(w, sheet_name=SOURCE_SH, index=False, header=False, startcol=column_index_from_string(COL_START) - column_index_from_string("A"), startrow=START_ROW)
end = time.time()
print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
for row_idx, row in df3.iterrows():
ws.range(f"{COL_START}{START_ROW + 1 + row_idx}").value = row.values.tolist()
wb.save()
wb.close()
app.quit()
end = time.time()
print("xlsm 경과시간:"f"{end - start:.5f} sec")
xls 경과시간:0.04670 sec
xlsx 경과시간:0.99222 sec
xlsm 경과시간:3.63020 sec
pandas_exceltopython005.py
0.01MB
속도개선006
import pandas as pd
import os
import xlrd
import openpyxl
from openpyxl.utils.cell import column_index_from_string
from openpyxl.utils import get_column_letter
from xlutils.copy import copy
import xlwings as xw
import time
start = time.time()
# FILE = "01vlookup.xls" # .xls 파일 경로
# FILE = "01vlookup.xlsx" # .xlsx 파일 경로
FILE = "01vlookup.xlsm" # .xlsm 파일 경로
# df1 설정
SOURCE_SH = "Sheet1"
COL_START = "A"
COL_END = "B"
file_ext = os.path.splitext(FILE)[1]
COUNT_SKIPROW = 0 # 초기화
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE)
ws = wb.sheet_by_name(SOURCE_SH)
for row in range(ws.nrows):
if ws.cell_value(row, 0) == "이름":
break
COUNT_SKIPROW += 1
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START}:{COL_END}",
engine='xlrd')
elif file_ext == '.xlsx':
FIND_COL = "A"
FIND_TITLE = "이름"
COUNT_SKIPROW = 0
wb = openpyxl.load_workbook(FILE)
ws = wb[SOURCE_SH]
for row in ws.iter_rows(min_col=column_index_from_string(FIND_COL), max_col=column_index_from_string(FIND_COL)):
if row[0].value == FIND_TITLE:
break
COUNT_SKIPROW += 1
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START}:{COL_END}",
engine='openpyxl')
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
header_row = 1
while ws.range(f'A{header_row}').value != "이름":
header_row += 1
last_row = ws.range('A4').end('down').row
df1 = pd.DataFrame(ws.range(f"{COL_START}{header_row}:{COL_END}{last_row}").value)
df1.columns = df1.iloc[0]
df1 = df1[1:]
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
FIND_COL = "F"
FIND_TITLE = "제품"
COUNT_SKIPROW = 0
if file_ext == '.xls':
for row in range(ws.nrows):
if ws.cell_value(row, 5) == FIND_TITLE:
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsx':
for row in ws.iter_rows(min_col=column_index_from_string(FIND_COL), max_col=column_index_from_string(FIND_COL)):
if row[0].value == FIND_TITLE:
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
while ws.range(f'F{COUNT_SKIPROW + 1}').value != FIND_TITLE:
COUNT_SKIPROW += 1
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
COL_START = "F"
COL_END = "G"
COL_TITLE_NAME = ['제품', '가격']
if file_ext == '.xls':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_NAME,
index_col=None,
usecols=f"{COL_START}:{COL_END}",
skiprows=COUNT_SKIPROW,
engine='xlrd')
elif file_ext == '.xlsx' or file_ext == '.xlsm':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_NAME,
index_col=None,
usecols=f"{COL_START}:{COL_END}",
skiprows=COUNT_SKIPROW,
engine='openpyxl')
# df3 데이터 병합
COL_STANDARD_NAME = "제품"
JOIN_HOW = "left"
df3 = df1.merge(df2, on=COL_STANDARD_NAME, how=JOIN_HOW)
df3['가격'] = df3['가격'].astype(int)
# 특정 범위 지우기
COL_START = "J"
COL_END = "L"
START_ROW = 4 # 제목열 타이핑 되어있는 상태 - 데이터 시작행 0,1,2,3,4,5
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
ws = wb.sheet_by_name(SOURCE_SH)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1):
for row in range(START_ROW, ws.nrows):
new_ws.write(row, column_letter - 1, '')
new_wb.save(FILE)
elif file_ext == '.xlsx':
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1): # 10,11,12
column_cells = ws[get_column_letter(column_letter)][START_ROW:] # 10열 전체, 11열 전체, 12열 전체
# 열 범위의 모든 셀을 확인하고 데이터가 있는 경우 삭제
for cell in column_cells:
if cell.value is not None:
cell.value = None
wb.save(FILE)
wb.close()
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
START_CELL = f'A{START_ROW}'
last_row = ws.range(START_CELL).end('down').row
if last_row == START_ROW:
print("삭제할 데이터가 없습니다.")
else:
for column_letter in range(column_index_from_string(COL_START), column_index_from_string(COL_END) + 1):
column_cells = ws.range(f'{get_column_letter(column_letter)}{START_ROW + 1}:'
f'{get_column_letter(column_letter)}{last_row}')
column_cells.value = None
wb.save()
wb.close()
app.quit()
# 최종 파일 저장
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for row_idx, row in df3.iterrows():
for col_idx, value in enumerate(row):
new_ws.write(START_ROW + row_idx, column_index_from_string(COL_START) - 1 + col_idx, value)
new_wb.save(FILE)
end = time.time()
print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsx':
with pd.ExcelWriter(FILE, mode='a', engine='openpyxl', if_sheet_exists='overlay') as w:
df3.to_excel(w, sheet_name=SOURCE_SH, index=False, header=False, startcol=column_index_from_string(COL_START) - column_index_from_string("A"), startrow=START_ROW)
end = time.time()
print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
for row_idx, row in df3.iterrows():
ws.range(f"{COL_START}{START_ROW + 1 + row_idx}").value = row.values.tolist()
wb.save()
wb.close()
app.quit()
end = time.time()
print("xlsm 경과시간:"f"{end - start:.5f} sec")
xls 경과시간:0.03690 sec
xlsx 경과시간:0.09275 sec
xlsm 경과시간:3.45276 sec
pandas_exceltopython006.py
0.01MB
코드정리
import pandas as pd
import os
import xlrd
import openpyxl
from openpyxl.utils.cell import column_index_from_string
from openpyxl.utils import get_column_letter
from xlutils.copy import copy
import xlwings as xw
import time
start = time.time() # 실행경과시간 측정
# FILE = "01vlookup.xls" # .xls 파일 경로
# FILE = "01vlookup.xlsx" # .xlsx 파일 경로
FILE = "01vlookup.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH = "Sheet1"
COL_START_GROUP1 = "A"
COL_END_GROUP1 = "B"
FIND_TITLE_GROUP1 = "이름"
# df2 설정 -----------------------------------------------------
COL_START_GROUP2 = "F"
COL_END_GROUP2 = "G"
FIND_TITLE_GROUP2 = "제품"
COL_TITLE_LIST_GROUP2 = ['제품', '가격']
# df3 설정 join -----------------------------------------------------
COL_STANDARD_NAME_GROUP3 = "제품"
JOIN_HOW_GROUP3 = "left"
# target 설정 -----------------------------------------------------
COL_START_TARGET = "J"
COL_END_TARGET = "L"
FIND_TITLE_TARGET = "이름"
# START_ROW_TARGET = 4 # 제목열 타이핑 되어있는 상태 - 데이터 시작행 0,1,2,3,4
file_ext = os.path.splitext(FILE)[1]
# df1 데이터프레임 생성 -----------------------------------------------------
# COUNT_SKIPROW = 0 # 초기화
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE)
ws = wb.sheet_by_name(SOURCE_SH)
# FIND_TITLE_GROUP1이 있는 행을 찾기
for COUNT_SKIPROW, row in enumerate(range(ws.nrows)):
if ws.cell_value(row, 0) == FIND_TITLE_GROUP1:
break
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START_GROUP1}:{COL_END_GROUP1}",
engine='xlrd')
elif file_ext == '.xlsx':
# COUNT_SKIPROW = 0
wb = openpyxl.load_workbook(FILE)
ws = wb[SOURCE_SH]
# FIND_TITLE_GROUP1이 있는 행을 찾기
for COUNT_SKIPROW, row in enumerate(ws.iter_rows(min_col=column_index_from_string(COL_START_GROUP1),
max_col=column_index_from_string(COL_START_GROUP1))):
if row[0].value == FIND_TITLE_GROUP1:
break
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW,
usecols=f"{COL_START_GROUP1}:{COL_END_GROUP1}",
engine='openpyxl')
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
header_row = 1
# FIND_TITLE_GROUP1이 있는 행을 찾기
while ws.range(f'{COL_START_GROUP1}{header_row}').value != FIND_TITLE_GROUP1:
header_row += 1
last_row = ws.range(f'{COL_START_GROUP1}{header_row + 1}').end('down').row
df1 = pd.DataFrame(ws.range(f"{COL_START_GROUP1}{header_row}:{COL_END_GROUP1}{last_row}").value)
df1.columns = df1.iloc[0]
df1 = df1[1:]
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# df2 ==================================================================
COUNT_SKIPROW = 0
if file_ext == '.xls':
for row in range(ws.nrows):
if ws.cell_value(row, 5) == FIND_TITLE_GROUP2:
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsx':
for row in ws.iter_rows(min_col=column_index_from_string(COL_START_GROUP2), max_col=column_index_from_string(COL_START_GROUP2)):
if row[0].value == FIND_TITLE_GROUP2:
break
COUNT_SKIPROW += 1
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
while ws.range(f'F{COUNT_SKIPROW + 1}').value != FIND_TITLE_GROUP2:
COUNT_SKIPROW += 1
wb.close()
app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
if file_ext == '.xls':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_LIST_GROUP2,
index_col=None,
usecols=f"{COL_START_GROUP2}:{COL_END_GROUP2}",
skiprows=COUNT_SKIPROW,
engine='xlrd')
elif file_ext == '.xlsx' or file_ext == '.xlsm':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_LIST_GROUP2,
index_col=None,
usecols=f"{COL_START_GROUP2}:{COL_END_GROUP2}",
skiprows=COUNT_SKIPROW,
engine='openpyxl')
# df3 데이터 병합 =====================================================================
df3 = df1.merge(df2, on=COL_STANDARD_NAME_GROUP3, how=JOIN_HOW_GROUP3)
df3['가격'] = df3['가격'].astype(int)
# 특정 범위 지우기 ========================================================================
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
ws = wb.sheet_by_name(SOURCE_SH)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
for row in range(COUNT_SKIPROW + 1, ws.nrows):
new_ws.write(row, column_letter - 1, '')
new_wb.save(FILE)
elif file_ext == '.xlsx':
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1): # 10,11,12
# column_cells = ws[get_column_letter(column_letter)][START_ROW_TARGET:] # 10열 전체, 11열 전체, 12열 전체 COUNT_SKIPROW
column_cells = ws[get_column_letter(column_letter)][COUNT_SKIPROW + 1:] # 10열 전체, 11열 전체, 12열 전체 COUNT_SKIPROW
# 열 범위의 모든 셀을 확인하고 데이터가 있는 경우 삭제
for cell in column_cells:
if cell.value is not None:
cell.value = None
wb.save(FILE)
wb.close()
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# START_CELL = f'{COL_START_GROUP1}{START_ROW_TARGET}' # header_row
START_CELL = f'{COL_START_GROUP1}{header_row}' # header_row
last_row = ws.range(START_CELL).end('down').row
# if last_row == START_ROW_TARGET: # header_row
if last_row == header_row: # header_row
print("삭제할 데이터가 없습니다.")
else:
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
# column_cells = ws.range(f'{get_column_letter(column_letter)}{START_ROW_TARGET + 1}:' # header_row
column_cells = ws.range(f'{get_column_letter(column_letter)}{header_row + 1}:' # header_row
f'{get_column_letter(column_letter)}{last_row}')
column_cells.value = None
wb.save()
wb.close()
app.quit()
# 최종 파일 저장
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for row_idx, row in df3.iterrows():
for col_idx, value in enumerate(row):
new_ws.write(COUNT_SKIPROW + 1 + row_idx, column_index_from_string(COL_START_TARGET) - 1 + col_idx, value)
new_wb.save(FILE)
end = time.time()
print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsx':
with pd.ExcelWriter(FILE, mode='a', engine='openpyxl', if_sheet_exists='overlay') as w:
df3.to_excel(w, sheet_name=SOURCE_SH, index=False, header=False,
startcol=column_index_from_string(COL_START_TARGET) - column_index_from_string("A"),
startrow=COUNT_SKIPROW+1)
end = time.time()
print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
for row_idx, row in df3.iterrows():
# ws.range(f"{COL_START_TARGET}{START_ROW_TARGET + 1 + row_idx}").value = row.values.tolist() # header_row
ws.range(f"{COL_START_TARGET}{header_row + 1 + row_idx}").value = row.values.tolist() # header_row
wb.save()
wb.close()
app.quit()
end = time.time()
print("xlsm 경과시간:"f"{end - start:.5f} sec")
xls 경과시간:0.01892 sec
xlsx 경과시간:0.06084 sec
xlsm 경과시간:3.02485 sec
pandas_exceltopython009.py
0.01MB
수정
import pandas as pd
import os
import xlrd
import openpyxl
from openpyxl.utils.cell import column_index_from_string
from openpyxl.utils import get_column_letter
from xlutils.copy import copy
import xlwings as xw
import time
start = time.time() # 실행경과시간 측정
# FILE = "01vlookup.xls" # .xls 파일 경로
# FILE = "01vlookup.xlsx" # .xlsx 파일 경로
FILE = "01vlookup.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH = "Sheet1"
COL_START_DF1 = "A"
COL_END_DF1 = "B"
FIND_TITLE_DF1 = "이름"
# df2 설정 -----------------------------------------------------
COL_START_DF2 = "F"
COL_END_DF2 = "G"
FIND_TITLE_DF2 = "제품"
COL_TITLE_LIST_DF2 = ['제품', '가격']
# df3 설정 join ------------------------------------------------
COL_STANDARD_NAME_DF3 = "제품"
JOIN_HOW_DF3 = "left"
# target 설정 --------------------------------------------------
COL_START_TARGET = "J"
COL_END_TARGET = "L"
FIND_TITLE_TARGET = "이름"
# START_ROW_TARGET = 4 # 제목열 타이핑 되어있는 상태 - 데이터 시작행 0,1,2,3,4
file_ext = os.path.splitext(FILE)[1]
# df1 ==================================================================
COUNT_SKIPROW_DF1 = 0
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE)
ws = wb.sheet_by_name(SOURCE_SH)
# FIND_TITLE_DF1이 있는 행을 찾기 # COUNT_SKIPROW 계산
# for COUNT_SKIPROW_DF1, row in enumerate(range(ws.nrows)): # COUNT_SKIPROW 계산
# if ws.cell_value(row, 0) == FIND_TITLE_DF1: # COUNT_SKIPROW 계산
# break # COUNT_SKIPROW 계산
for row in range(ws.nrows):
if ws.cell_value(row, column_index_from_string(COL_START_DF1)-1) == FIND_TITLE_DF1: # 여기수정했음
break
COUNT_SKIPROW_DF1 += 1
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW_DF1,
usecols=f"{COL_START_DF1}:{COL_END_DF1}",
engine='xlrd')
elif file_ext == '.xlsx':
wb = openpyxl.load_workbook(FILE)
ws = wb[SOURCE_SH]
# FIND_TITLE_DF1이 있는 행을 찾기 # COUNT_SKIPROW 계산
for COUNT_SKIPROW_DF1, row in enumerate(ws.iter_rows(min_col=column_index_from_string(COL_START_DF1), # COUNT_SKIPROW 계산
max_col=column_index_from_string(COL_START_DF1))): # COUNT_SKIPROW 계산
if row[0].value == FIND_TITLE_DF1: # COUNT_SKIPROW 계산
break # COUNT_SKIPROW 계산
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW_DF1,
usecols=f"{COL_START_DF1}:{COL_END_DF1}",
engine='openpyxl')
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# # FIND_TITLE_DF1이 있는 행을 찾기 # COUNT_SKIPROW 계산
# header_row = 1 # COUNT_SKIPROW 계산
while ws.range(f'{COL_START_DF1}{COUNT_SKIPROW_DF1 + 1}').value != FIND_TITLE_DF1: # COUNT_SKIPROW 계산
COUNT_SKIPROW_DF1 += 1 # COUNT_SKIPROW 계산
last_row = ws.range(f'{COL_START_DF1}{COUNT_SKIPROW_DF1 + 1}').end('down').row
df1 = pd.DataFrame(ws.range(f"{COL_START_DF1}{COUNT_SKIPROW_DF1 + 1}:{COL_END_DF1}{last_row}").value)
df1.columns = df1.iloc[0] # df1 첫번째 행 제목행으로 정의
df1 = df1[1:] # df1 첫번째 행 제목행 다음부터 df1의 데이터로 넣음
# wb.close()
# app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# df2 ==================================================================
# skip row 계산----------------------------------------------------START
COUNT_SKIPROW_DF2 = 0
if file_ext == '.xls':
for row in range(ws.nrows):
if ws.cell_value(row, column_index_from_string(COL_START_DF2)-1) == FIND_TITLE_DF2: # 여기수정했음
break
COUNT_SKIPROW_DF2 += 1
elif file_ext == '.xlsx':
for row in ws.iter_rows(min_col=column_index_from_string(COL_START_DF2), max_col=column_index_from_string(COL_START_DF2)): # ? 확인요망
if row[0].value == FIND_TITLE_DF2:
break
COUNT_SKIPROW_DF2 += 1
elif file_ext == '.xlsm':
# app = xw.App(visible=False)
# wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
while ws.range(f'{COL_START_DF2}{COUNT_SKIPROW_DF2 + 1}').value != FIND_TITLE_DF2: # skip row 계산
COUNT_SKIPROW_DF2 += 1 ############################################## skip row 계산
# wb.close()
# app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# skip row 계산------------------------------------------------------END
if file_ext == '.xls':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_LIST_DF2,
index_col=None,
usecols=f"{COL_START_DF2}:{COL_END_DF2}",
skiprows=COUNT_SKIPROW_DF2,
engine='xlrd')
elif file_ext == '.xlsx' or file_ext == '.xlsm':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_LIST_DF2,
index_col=None,
usecols=f"{COL_START_DF2}:{COL_END_DF2}",
skiprows=COUNT_SKIPROW_DF2,
engine='openpyxl')
# df3 데이터 병합 =====================================================================
COUNT_SKIPROW_DF3 = 0
df3 = df1.merge(df2, on=COL_STANDARD_NAME_DF3, how=JOIN_HOW_DF3)
df3['가격'] = df3['가격'].astype(int)
# 특정 범위 지우기 ========================================================================
COUNT_SKIPROW_TARGET = 0
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
ws = wb.sheet_by_name(SOURCE_SH)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
# # FIND_TITLE_DF1이 있는 행을 찾기 # COUNT_SKIPROW 계산
# for COUNT_SKIPROW_TARGET, row in enumerate(range(ws.nrows)): # COUNT_SKIPROW 계산
# if ws.cell_value(row, 0) == FIND_TITLE_TARGET: # COUNT_SKIPROW 계산
# break # COUNT_SKIPROW 계산
for row in range(ws.nrows): # COUNT_SKIPROW 계산
if ws.cell_value(row, column_index_from_string(COL_START_TARGET)-1) == FIND_TITLE_TARGET: # 여기수정했음 # COUNT_SKIPROW 계산
break # COUNT_SKIPROW 계산
COUNT_SKIPROW_TARGET += 1 # COUNT_SKIPROW 계산
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
for row in range(COUNT_SKIPROW_TARGET + 1, ws.nrows):
new_ws.write(row, column_letter - 1, '')
new_wb.save(FILE)
elif file_ext == '.xlsx':
for row in ws.iter_rows(min_col=column_index_from_string(COL_START_TARGET), max_col=column_index_from_string(COL_END_TARGET)): # ? 확인요망
if row[0].value == FIND_TITLE_TARGET:
break
COUNT_SKIPROW_TARGET += 1
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1): # 10,11,12
# column_cells = ws[get_column_letter(column_letter)][START_ROW_TARGET:] # 10열 전체, 11열 전체, 12열 전체 COUNT_SKIPROW
column_cells = ws[get_column_letter(column_letter)][COUNT_SKIPROW_TARGET + 1:] # 10열 전체, 11열 전체, 12열 전체 COUNT_SKIPROW
# 열 범위의 모든 셀을 확인하고 데이터가 있는 경우 삭제
for cell in column_cells:
if cell.value is not None:
cell.value = None
wb.save(FILE)
wb.close()
elif file_ext == '.xlsm':
# app = xw.App(visible=False)
# wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# # START_CELL = f'{COL_START_DF1}{START_ROW_TARGET}' # header_row
# START_CELL = f'{COL_START_DF1}{header_row}' # header_row
# last_row = ws.range(START_CELL).end('down').row
# # if last_row == START_ROW_TARGET: # header_row
# if last_row == header_row: # header_row
# print("삭제할 데이터가 없습니다.")
# else:
# for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
# # column_cells = ws.range(f'{get_column_letter(column_letter)}{START_ROW_TARGET + 1}:' # header_row
# column_cells = ws.range(f'{get_column_letter(column_letter)}{header_row + 1}:' # header_row
# f'{get_column_letter(column_letter)}{last_row}')
# column_cells.value = None
while ws.range(f'{COL_START_TARGET}{COUNT_SKIPROW_TARGET + 1}').value != FIND_TITLE_TARGET:
COUNT_SKIPROW_TARGET += 1
last_row = ws.range(f'{COL_START_TARGET}{COUNT_SKIPROW_TARGET + 1}').end('down').row
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
column_cells = ws.range(f'{get_column_letter(column_letter)}{COUNT_SKIPROW_TARGET + 1 + 1}:{get_column_letter(column_letter)}{last_row}')
column_cells.value = None
# wb.save()
# wb.close()
# app.quit()
# 최종 파일 저장
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for row_idx, row in df3.iterrows():
for col_idx, value in enumerate(row):
new_ws.write(COUNT_SKIPROW_TARGET + 1 + row_idx, column_index_from_string(COL_START_TARGET) - 1 + col_idx, value)
new_wb.save(FILE)
end = time.time()
print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsx':
with pd.ExcelWriter(FILE, mode='a', engine='openpyxl', if_sheet_exists='overlay') as w:
df3.to_excel(w, sheet_name=SOURCE_SH, index=False, header=False,
startcol=column_index_from_string(COL_START_TARGET) - column_index_from_string("A"),
startrow=COUNT_SKIPROW_TARGET+1)
end = time.time()
print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsm':
# app = xw.App(visible=False)
# wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
for row_idx, row in df3.iterrows():
# 데이터를 엑셀에 입력
ws.range(f"{COL_START_TARGET}{COUNT_SKIPROW_TARGET + 1 + 1 + row_idx}").value = row.values.tolist()
wb.save()
wb.close()
app.quit()
end = time.time()
print("xlsm 경과시간:"f"{end - start:.5f} sec")xls 경과시간:0.03391 sec
xlsx 경과시간:0.10372 sec
xlsm 경과시간:1.33144 sec
pandas_exceltopython010.py
0.01MB
skip row 계산 - 함수계산
import pandas as pd
import os
import xlrd
import openpyxl
from openpyxl.utils.cell import column_index_from_string
from openpyxl.utils import get_column_letter
from xlutils.copy import copy
import xlwings as xw
import time
start = time.time() # 실행경과시간 측정
FILE = "01vlookup.xls" # .xls 파일 경로
# FILE = "01vlookup.xlsx" # .xlsx 파일 경로
# FILE = "01vlookup.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH = "Sheet1"
COL_START_DF1 = "A"
COL_END_DF1 = "B"
FIND_TITLE_DF1 = "이름"
# df2 설정 -----------------------------------------------------
COL_START_DF2 = "F"
COL_END_DF2 = "G"
FIND_TITLE_DF2 = "제품"
COL_TITLE_LIST_DF2 = ['제품', '가격']
# df3 설정 join ------------------------------------------------
COL_STANDARD_NAME_DF3 = "제품"
JOIN_HOW_DF3 = "left"
# target 설정 --------------------------------------------------
COL_START_TARGET = "J"
COL_END_TARGET = "L"
FIND_TITLE_TARGET = "이름"
# START_ROW_TARGET = 4 # 제목열 타이핑 되어있는 상태 - 데이터 시작행 0,1,2,3,4
file_ext = os.path.splitext(FILE)[1]
def FN_xls_skip_row_count(file_path, sheet_name, col_start, find_title, count_skiprow_name):
# 워크북 열기
# wb = open_workbook(file_path)
# ws = wb.sheet_by_name(sheet_name)
# COUNT_SKIPROW = 0
# 행을 반복하여 제목 찾기
for row in range(ws.nrows):
if ws.cell_value(row, column_index_from_string(col_start) - 1) == find_title: # 제목 비교
break
count_skiprow_name += 1
return count_skiprow_name
def FN_xlsx_skip_row_count(file_path, sheet_name, col_start, find_title, count_skiprow_name):
# 워크북 열기
# wb = open_workbook(file_path)
# ws = wb.sheet_by_name(sheet_name)
# COUNT_SKIPROW = 0
# 행을 반복하여 제목 찾기
for count_skiprow_name, row in enumerate(ws.iter_rows(min_col=column_index_from_string(col_start), # COUNT_SKIPROW 계산
max_col=column_index_from_string(col_start))): # COUNT_SKIPROW 계산
if row[0].value == find_title: # COUNT_SKIPROW 계산
break # COUNT_SKIPROW 계산
return count_skiprow_name
def FN_xlsm_skip_row_count(file_path, sheet_name, col_start, find_title, count_skiprow_name):
while ws.range(f'{col_start}{count_skiprow_name + 1}').value != find_title: # COUNT_SKIPROW 계산
count_skiprow_name += 1 # COUNT_SKIPROW 계산
return count_skiprow_name
# df1 ==================================================================
COUNT_SKIPROW_DF1 = 0
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE)
ws = wb.sheet_by_name(SOURCE_SH)
COUNT_SKIPROW_DF1 = 0
COUNT_SKIPROW_DF1 = FN_xls_skip_row_count(FILE, SOURCE_SH, COL_START_DF1, FIND_TITLE_DF1,COUNT_SKIPROW_DF1)
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW_DF1,
usecols=f"{COL_START_DF1}:{COL_END_DF1}",
engine='xlrd')
elif file_ext == '.xlsx':
wb = openpyxl.load_workbook(FILE)
ws = wb[SOURCE_SH]
# FIND_TITLE_DF1이 있는 행을 찾기 # COUNT_SKIPROW 계산
# for COUNT_SKIPROW_DF1, row in enumerate(ws.iter_rows(min_col=column_index_from_string(COL_START_DF1), # COUNT_SKIPROW 계산
# max_col=column_index_from_string(COL_START_DF1))): # COUNT_SKIPROW 계산
# if row[0].value == FIND_TITLE_DF1: # COUNT_SKIPROW 계산
# break # COUNT_SKIPROW 계산
COUNT_SKIPROW_DF1 = 0
COUNT_SKIPROW_DF1 = FN_xlsx_skip_row_count(FILE, SOURCE_SH, COL_START_DF1, FIND_TITLE_DF1,COUNT_SKIPROW_DF1)
df1 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=COUNT_SKIPROW_DF1,
usecols=f"{COL_START_DF1}:{COL_END_DF1}",
engine='openpyxl')
elif file_ext == '.xlsm':
app = xw.App(visible=False)
wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# while ws.range(f'{COL_START_DF1}{COUNT_SKIPROW_DF1 + 1}').value != FIND_TITLE_DF1: # COUNT_SKIPROW 계산
# COUNT_SKIPROW_DF1 += 1 # COUNT_SKIPROW 계산
COUNT_SKIPROW_DF1 = 0
COUNT_SKIPROW_DF1 = FN_xlsm_skip_row_count(FILE, SOURCE_SH, COL_START_DF1, FIND_TITLE_DF1, COUNT_SKIPROW_DF1)
last_row = ws.range(f'{COL_START_DF1}{COUNT_SKIPROW_DF1 + 1}').end('down').row
df1 = pd.DataFrame(ws.range(f"{COL_START_DF1}{COUNT_SKIPROW_DF1 + 1}:{COL_END_DF1}{last_row}").value)
df1.columns = df1.iloc[0] # df1 첫번째 행 제목행으로 정의
df1 = df1[1:] # df1 첫번째 행 제목행 다음부터 df1의 데이터로 넣음
# wb.close()
# app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# df2 ==================================================================
# skip row 계산----------------------------------------------------START
COUNT_SKIPROW_DF2 = 0
if file_ext == '.xls':
# for row in range(ws.nrows):
# if ws.cell_value(row, column_index_from_string(COL_START_DF2)-1) == FIND_TITLE_DF2: # 여기수정했음
# break
# COUNT_SKIPROW_DF2 += 1
COUNT_SKIPROW_DF2 = 0
COUNT_SKIPROW_DF2 = FN_xls_skip_row_count(FILE, SOURCE_SH, COL_START_DF2, FIND_TITLE_DF2,COUNT_SKIPROW_DF2)
elif file_ext == '.xlsx':
# for row in ws.iter_rows(min_col=column_index_from_string(COL_START_DF2), max_col=column_index_from_string(COL_START_DF2)): # ? 확인요망
# if row[0].value == FIND_TITLE_DF2:
# break
# COUNT_SKIPROW_DF2 += 1
COUNT_SKIPROW_DF2 = 0
COUNT_SKIPROW_DF2 = FN_xlsx_skip_row_count(FILE, SOURCE_SH, COL_START_DF2, FIND_TITLE_DF2,COUNT_SKIPROW_DF2)
elif file_ext == '.xlsm':
# app = xw.App(visible=False)
# wb = app.books.open(FILE)
# ws = wb.sheets[SOURCE_SH]
# while ws.range(f'{COL_START_DF2}{COUNT_SKIPROW_DF2 + 1}').value != FIND_TITLE_DF2: # skip row 계산
# COUNT_SKIPROW_DF2 += 1 ############################################## skip row 계산
COUNT_SKIPROW_DF2 = 0
COUNT_SKIPROW_DF2 = FN_xlsm_skip_row_count(FILE, SOURCE_SH, COL_START_DF2, FIND_TITLE_DF2, COUNT_SKIPROW_DF2)
# wb.close()
# app.quit()
else:
raise ValueError("지원하지 않는 파일 형식입니다.")
# skip row 계산------------------------------------------------------END
if file_ext == '.xls':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_LIST_DF2,
index_col=None,
usecols=f"{COL_START_DF2}:{COL_END_DF2}",
skiprows=COUNT_SKIPROW_DF2,
engine='xlrd')
elif file_ext == '.xlsx' or file_ext == '.xlsm':
df2 = pd.read_excel(FILE, sheet_name=SOURCE_SH,
header=0,
names=COL_TITLE_LIST_DF2,
index_col=None,
usecols=f"{COL_START_DF2}:{COL_END_DF2}",
skiprows=COUNT_SKIPROW_DF2,
engine='openpyxl')
# df3 데이터 병합 =====================================================================
COUNT_SKIPROW_DF3 = 0
df3 = df1.merge(df2, on=COL_STANDARD_NAME_DF3, how=JOIN_HOW_DF3)
df3['가격'] = df3['가격'].astype(int)
# 특정 범위 지우기 ========================================================================
COUNT_SKIPROW_TARGET = 0
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
ws = wb.sheet_by_name(SOURCE_SH)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
# # FIND_TITLE_DF1이 있는 행을 찾기 # COUNT_SKIPROW 계산
# for COUNT_SKIPROW_TARGET, row in enumerate(range(ws.nrows)): # COUNT_SKIPROW 계산
# if ws.cell_value(row, 0) == FIND_TITLE_TARGET: # COUNT_SKIPROW 계산
# break # COUNT_SKIPROW 계산
# for row in range(ws.nrows): # COUNT_SKIPROW 계산
# if ws.cell_value(row, column_index_from_string(COL_START_TARGET)-1) == FIND_TITLE_TARGET: # 여기수정했음 # COUNT_SKIPROW 계산
# break # COUNT_SKIPROW 계산
# COUNT_SKIPROW_TARGET += 1 # COUNT_SKIPROW 계산
COUNT_SKIPROW_TARGET = 0
COUNT_SKIPROW_TARGET = FN_xls_skip_row_count(FILE, SOURCE_SH, COL_START_TARGET, FIND_TITLE_TARGET,COUNT_SKIPROW_TARGET)
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
for row in range(COUNT_SKIPROW_TARGET + 1, ws.nrows):
new_ws.write(row, column_letter - 1, '')
new_wb.save(FILE)
elif file_ext == '.xlsx':
COUNT_SKIPROW_TARGET = 0
COUNT_SKIPROW_TARGET = FN_xlsx_skip_row_count(FILE, SOURCE_SH, COL_START_TARGET, FIND_TITLE_TARGET,COUNT_SKIPROW_TARGET)
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1): # 10,11,12
# column_cells = ws[get_column_letter(column_letter)][START_ROW_TARGET:] # 10열 전체, 11열 전체, 12열 전체 COUNT_SKIPROW
column_cells = ws[get_column_letter(column_letter)][COUNT_SKIPROW_TARGET + 1:] # 10열 전체, 11열 전체, 12열 전체 COUNT_SKIPROW
# 열 범위의 모든 셀을 확인하고 데이터가 있는 경우 삭제
for cell in column_cells:
if cell.value is not None:
cell.value = None
wb.save(FILE)
wb.close()
elif file_ext == '.xlsm':
# app = xw.App(visible=False)
# wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
# while ws.range(f'{COL_START_TARGET}{COUNT_SKIPROW_TARGET + 1}').value != FIND_TITLE_TARGET:
# COUNT_SKIPROW_TARGET += 1
COUNT_SKIPROW_TARGET = 0
COUNT_SKIPROW_TARGET = FN_xlsm_skip_row_count(FILE, SOURCE_SH, COL_START_TARGET, FIND_TITLE_TARGET, COUNT_SKIPROW_TARGET)
last_row = ws.range(f'{COL_START_TARGET}{COUNT_SKIPROW_TARGET + 1}').end('down').row
for column_letter in range(column_index_from_string(COL_START_TARGET), column_index_from_string(COL_END_TARGET) + 1):
column_cells = ws.range(f'{get_column_letter(column_letter)}{COUNT_SKIPROW_TARGET + 1 + 1}:{get_column_letter(column_letter)}{last_row}')
column_cells.value = None
# wb.save()
# wb.close()
# app.quit()
# 최종 파일 저장
if file_ext == '.xls':
wb = xlrd.open_workbook(FILE, formatting_info=True)
new_wb = copy(wb)
new_ws = new_wb.get_sheet(0)
for row_idx, row in df3.iterrows():
for col_idx, value in enumerate(row):
new_ws.write(COUNT_SKIPROW_TARGET + 1 + row_idx, column_index_from_string(COL_START_TARGET) - 1 + col_idx, value)
new_wb.save(FILE)
end = time.time()
print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsx':
with pd.ExcelWriter(FILE, mode='a', engine='openpyxl', if_sheet_exists='overlay') as w:
df3.to_excel(w, sheet_name=SOURCE_SH, index=False, header=False,
startcol=column_index_from_string(COL_START_TARGET) - column_index_from_string("A"),
startrow=COUNT_SKIPROW_TARGET + 1)
end = time.time()
print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == '.xlsm':
# app = xw.App(visible=False)
# wb = app.books.open(FILE)
ws = wb.sheets[SOURCE_SH]
for row_idx, row in df3.iterrows():
# 데이터를 엑셀에 입력
ws.range(f"{COL_START_TARGET}{COUNT_SKIPROW_TARGET + 1 + 1 + row_idx}").value = row.values.tolist()
wb.save()
wb.close()
app.quit()
end = time.time()
print("xlsm 경과시간:"f"{end - start:.5f} sec")
xls 경과시간:0.03391 sec
xlsx 경과시간:0.09874 sec
xlsm 경과시간:1.12300 sec
pandas_exceltopython011.py
0.01MB
chatgpt버전
import os
import pandas as pd
import xlrd # .xls 파일을 읽기 위해 사용
import xlwt # .xls 파일을 쓰기 위해 사용
from xlutils.copy import copy # xlrd로 읽은 파일을 xlwt로 수정 가능하게 하기 위해 사용
import win32api
import time
start = time.time() # 실행경과시간 측정
# xls*****************************************************************************************************
# 특정 시트에서 제목 찾고 skip row 계산 (xlrd 사용)
def FN_skip_row_count_xls(wb, sheet_name, col_start, find_title):
ws = wb.sheet_by_name(sheet_name)
count_skiprow_name = 0
for idx, row in enumerate(ws.col_values(column_index_from_string(col_start) - 1)):
if row == find_title:
count_skiprow_name = idx
break
return count_skiprow_name
# 엑셀 열 번호 변환 함수
def column_index_from_string_xls(col_str):
return ord(col_str.upper()) - ord('A') + 1
# xls 파일 처리 함수 (데이터프레임으로 변환)
def process_dataframe_xls(file, sheet_name, col_start, col_end, find_title, col_title_list=None):
# .xls 파일 처리 (xlrd 사용)
wb = xlrd.open_workbook(file)
ws = wb.sheet_by_name(sheet_name)
skip_rows = FN_skip_row_count_xls(wb, sheet_name, col_start, find_title)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(file, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='xlrd')
return df
# Target 시트 업데이트 (xlwt 사용)
def update_target_sheet_xls(file, df3, target_sheet, col_start, col_end, find_title):
# xlrd로 파일 열기
wb = xlrd.open_workbook(file, formatting_info=True) # 포맷 정보를 가져오기
ws = wb.sheet_by_name(target_sheet)
# 수정할 Workbook 생성
new_wb = copy(wb) # 기존 워크북 복사
new_ws = new_wb.get_sheet(0) # 첫 번째 시트 가져오기
# skip_rows 찾기
skip_rows = FN_skip_row_count_xls(wb, target_sheet, col_start, find_title)
# 데이터 지우기
for column_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
for row in range(skip_rows + 1, ws.nrows): # START_ROW부터 끝까지 반복 0,1,2,3,4
new_ws.write(row, column_letter - 1, '') # 값을 빈 문자열로 설정하여 지우기
# 수정된 파일 저장
new_wb.save(file)
# 새로운 데이터 추가
for row_idx, row in enumerate(df3.values):
for col_idx, value in enumerate(row):
if pd.isna(value):
value = '' # NaN값 처리
new_ws.write(skip_rows + 1 + row_idx, col_idx + column_index_from_string(col_start) - 1, value) # str(value)
# 수정된 파일 저장
new_wb.save(file)
# 메인 실행 부분
# def main():
# FILE = "01vlookup_chatgpt.xls" # .xls 파일 경로 (원본 파일)
# # df1 처리
# # df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
# df1 = process_dataframe(FILE, "SheetA", "A", "B", "이름")
# # df2 처리
# # df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
# df2 = process_dataframe(FILE, "SheetB", "A", "B", "제품", ['제품', '가격'])
# # df1과 df2 결합
# df3 = df1.merge(df2, on="제품", how="left")
# # Target 시트 업데이트
# update_target_sheet(FILE, df3, "Sheet1", "J", "이름")
# # 시간측정
# end = time.time()
# print("xls 경과시간:"f"{end - start:.5f} sec")
# # 완료 메시지
# win32api.MessageBox(0, "작업을 완료했습니다.", "작업 완료", 0)
# if __name__ == "__main__":
# main()
# xlsx*************************************************************************************************
# import os
# import pandas as pd
import openpyxl
# from openpyxl.utils.cell import column_index_from_string, get_column_letter
# import win32api
# import time
# start = time.time() # 실행경과시간 측정
# 파일 확장자에 따라 워크북 불러오기
# def load_workbook_by_ext(file_path, file_ext):
# if file_ext ==".xlsx":
# return openpyxl.load_workbook(file_path)
# 특정 시트에서 제목 찾고 skip row 계산
def FN_skip_row_count_xlsx(file_path, sheet_name, col_start, find_title):
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
for count_skiprow_name, row in enumerate(ws.iter_rows(min_col=column_index_from_string(col_start),
max_col=column_index_from_string(col_start))):
if row[0].value == find_title:
break
return count_skiprow_name
# 데이터 행 수 계산
def calculate_data_row_count(ws, col_start, skip_rows):
max_row = skip_rows
for cell in ws[col_start][skip_rows + 1:]:
if cell.value is not None:
max_row += 1
else:
break
return max_row - skip_rows
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsx(file_path, sheet_name, col_start, col_end, find_title, col_title_list=None):
# file_ext = os.path.splitext(file_path)[1]
skip_rows = FN_skip_row_count_xlsx(file_path, sheet_name, col_start, find_title)
# wb = load_workbook_by_ext(file_path, file_ext)
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
# data_row_count_xlsx = calculate_data_row_count(ws, col_start, skip_rows)
df = pd.read_excel(file_path, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
return df
# Target 시트 업데이트
def update_target_sheet_xlsx(file_path, df3, target_sheet, col_start, col_end, find_title):
wb = openpyxl.load_workbook(file_path)
ws = wb[target_sheet]
# Target 시트에서 제목 찾고 skip row 계산
skip_rows = FN_skip_row_count_xlsx(file_path, target_sheet, col_start, find_title)
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
column_cells = ws[get_column_letter(col_letter)][skip_rows + 1:]
for cell in column_cells:
if cell.value is not None:
cell.value = None
# 새로운 데이터 추가
wb.save(file_path)
wb.close()
with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
df3.to_excel(writer, sheet_name=target_sheet, index=False, header=False,
startcol=column_index_from_string(col_start) - 1,
startrow=skip_rows + 1)
# 메인 실행 부분
# def main():
# FILE = "01vlookup_chatgpt.xlsx" # 파일 경로
# file_ext = os.path.splitext(FILE)[1]
# # df1 처리
# # df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
# df1 = process_dataframe(FILE, "SheetA", "A", "B", "이름")
# # df2 처리
# # df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
# df2 = process_dataframe(FILE, "SheetB", "A", "B", "제품", ['제품', '가격'])
# # df1과 df2 결합
# df3 = df1.merge(df2, on="제품", how="left")
# # Target 시트 업데이트
# update_target_sheet(FILE, df3, "Sheet1", "J", "L", "이름")
# # 시간측정
# end = time.time()
# print("xlsx 경과시간:"f"{end - start:.5f} sec")
# # 완료 메시지
# win32api.MessageBox(0, "작업을 완료했습니다.", "작업 완료", 0)
# if __name__ == "__main__":
# main()
# xlsm******************************************************************************************************
# import os
# import pandas as pd
import xlwings as xw # xlwings를 사용하여 .xlsm 파일 처리
from openpyxl.utils.cell import column_index_from_string, get_column_letter
# import win32api
# import time
# start = time.time() # 실행경과시간 측정
# 특정 시트에서 제목 찾고 skip row 계산
def FN_skip_row_count_xlsm(ws, col_start, find_title):
max_row = ws.used_range.last_cell.row # 최대 행 수 가져오기
for count_skiprow_name, row in enumerate(ws.range(f"{col_start}1:{col_start}{max_row}").value):
if row == find_title:
break
return count_skiprow_name
# 데이터 행 수 계산
def calculate_data_row_count(ws, col_start, skip_rows):
max_row = ws.used_range.last_cell.row # 최대 행 수 가져오기
# 데이터가 있는 셀만 확인
cells = ws.range(f"{col_start}{skip_rows + 2}:{col_start}{max_row}").value
actual_data_count = sum(1 for cell in cells if cell is not None)
return actual_data_count
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsm(wb, sheet_name, col_start, col_end, find_title, col_title_list=None):
ws = wb.sheets[sheet_name]
# skip_rows 계산
skip_rows = FN_skip_row_count_xlsm(ws, col_start, find_title)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(wb.fullname, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
return df
# Target 시트 업데이트
def update_target_sheet_xlsm(wb, df3, target_sheet, col_start, col_end, find_title):
ws = wb.sheets[target_sheet]
# Target 시트에서 제목 찾고 skip row 계산
skip_rows = FN_skip_row_count_xlsm(ws, col_start, find_title)
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
ws.range(f"{get_column_letter(col_letter)}{skip_rows + 2}:{get_column_letter(col_letter)}{ws.used_range.last_cell.row}").clear_contents()
# 새로운 데이터 추가
start_col = column_index_from_string(col_start) - 1
ws.range((skip_rows + 2, start_col + 1)).value = df3.values
# 메인 실행 부분
def main():
file_ext = os.path.splitext(FILE)[1][1:]
if file_ext == "xls":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xls(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, FIND_TITLE_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xls(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, FIND_TITLE_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xls(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET,FIND_TITLE_TARGET)
# 시간측정
# end = time.time()
# print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsx":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsx(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, FIND_TITLE_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsx(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, FIND_TITLE_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsx(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, FIND_TITLE_TARGET)
# 시간측정
# end = time.time()
# print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsm":
# 숨김 모드에서 Excel을 엽니다.
app = xw.App(visible=False)
try:
# Workbook 열기
wb = app.books.open(FILE)
# df1 처리
# df1 = process_dataframe(wb, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsm(wb, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, FIND_TITLE_DF1)
# df2 처리
# df2 = process_dataframe(wb, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsm(wb, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, FIND_TITLE_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsm(wb, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, FIND_TITLE_TARGET)
# 작업 완료 후 저장
wb.save()
finally:
# Excel 애플리케이션을 종료
wb.close()
app.quit()
# 시간측정
end = time.time()
print(f"{file_ext} 경과시간: {end - start:.5f} sec")
# 완료 메시지
win32api.MessageBox(0, "작업을 완료했습니다.", "작업 완료", 0)
if __name__ == "__main__":
FILE = "01vlookup_chatgpt.xls" # .xls 파일 경로
# FILE = "01vlookup_chatgpt.xlsx" # .xlsx 파일 경로
# FILE = "01vlookup_chatgpt.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH_1 = "Sheet1" # Sheet1
COL_START_DF1 = "A" # 데이터 시작열 # Sheet1
COL_END_DF1 = "B" # 데이터 종료열 # Sheet1
FIND_TITLE_DF1 = "이름" # 열제목으로 skiprow 계산 - "이름"# Sheet1
# SOURCE_SH_1 = "SheetA" # SheetA
# COL_START_DF1 = "A" # 데이터 시작열 # SheetA
# COL_END_DF1 = "B" # 데이터 종료열 # SheetA
# FIND_TITLE_DF1 = "이름" # 열제목으로 skiprow 계산 - "이름"# SheetA
# df2 설정 -----------------------------------------------------
SOURCE_SH_2 = "Sheet1" # Sheet1
COL_START_DF2 = "F" # Sheet1
COL_END_DF2 = "G" # Sheet1
FIND_TITLE_DF2 = "제품" # Sheet1
COL_TITLE_LIST_DF2 = ['제품', '가격'] # Sheet1
# SOURCE_SH_2 = "SheetB" # SheetB
# COL_START_DF2 = "A" # SheetB
# COL_END_DF2 = "B" # SheetB
# FIND_TITLE_DF2 = "제품" # SheetB
# COL_TITLE_LIST_DF2 = ['제품', '가격'] # SheetB
# df3 설정 join ------------------------------------------------
COL_STANDARD_NAME_DF3 = "제품"
JOIN_HOW_DF3 = "left"
# target 설정 --------------------------------------------------
SOURCE_SH_TARGET = "Sheet1"
COL_START_TARGET = "J"
COL_END_TARGET = "L"
FIND_TITLE_TARGET = "이름"
##############################################################################
main()
xls 경과시간: 0.54385 sec
xlsx 경과시간: 0.64145 sec
xlsm 경과시간: 1.29071 sec
01vlookup_chatgpt.py
0.02MB
01vlookup_chatgpt.xls
0.03MB
01vlookup_chatgpt.xlsm
0.01MB
01vlookup_chatgpt.xlsx
0.01MB
skip row 지정 방식
import os
import pandas as pd
import xlrd # .xls 파일을 읽기 위해 사용
import xlwt # .xls 파일을 쓰기 위해 사용
from xlutils.copy import copy # xlrd로 읽은 파일을 xlwt로 수정 가능하게 하기 위해 사용
import openpyxl
from openpyxl.utils.cell import column_index_from_string, get_column_letter
import win32api
import time
start = time.time() # 실행경과시간 측정
# xls*****************************************************************************************************
# 엑셀 열 번호 변환 함수
# def column_index_from_string_xls(col_str):
# return ord(col_str.upper()) - ord('A') + 1
# xls 파일 처리 함수 (데이터프레임으로 변환)
def process_dataframe_xls(file, sheet_name, col_start, col_end, skip_rows, col_title_list=None):
# .xls 파일 처리 (xlrd 사용)
# wb = xlrd.open_workbook(file)
# ws = wb.sheet_by_name(sheet_name)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(file, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='xlrd')
return df
# Target 시트 업데이트 (xlwt 사용)
def update_target_sheet_xls(file, df3, target_sheet, col_start, col_end, skip_rows):
# xlrd로 파일 열기
wb = xlrd.open_workbook(file, formatting_info=True) # 포맷 정보를 가져오기
ws = wb.sheet_by_name(target_sheet)
# 수정할 Workbook 생성
new_wb = copy(wb) # 기존 워크북 복사
new_ws = new_wb.get_sheet(0) # 첫 번째 시트 가져오기
# 데이터 지우기
for column_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
for row in range(skip_rows + 1, ws.nrows): # START_ROW부터 끝까지 반복 0,1,2,3,4
new_ws.write(row, column_letter - 1, None) # 값을 빈 문자열로 설정하여 지우기
# 수정된 파일 저장
new_wb.save(file)
# 새로운 데이터 추가
for row_idx, row in enumerate(df3.values):
for col_idx, value in enumerate(row):
if pd.isna(value):
value = '' # NaN값 처리
new_ws.write(skip_rows + 1 + row_idx, col_idx + column_index_from_string(col_start) - 1, value) # str(value)
# 수정된 파일 저장
new_wb.save(file)
# xlsx*************************************************************************************************
import openpyxl
# 데이터 행 수 계산
# def calculate_data_row_count(ws, col_start, skip_rows):
# max_row = skip_rows
# for cell in ws[col_start][skip_rows + 1:]:
# if cell.value is not None:
# max_row += 1
# else:
# break
# return max_row - skip_rows
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsx(file_path, sheet_name, col_start, col_end, skip_rows, col_title_list=None):
# wb = load_workbook_by_ext(file_path, file_ext)
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
# data_row_count_xlsx = calculate_data_row_count(ws, col_start, skip_rows)
df = pd.read_excel(file_path, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
return df
# Target 시트 업데이트
def update_target_sheet_xlsx(file_path, df3, target_sheet, col_start, col_end, skip_rows):
wb = openpyxl.load_workbook(file_path)
ws = wb[target_sheet]
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
column_cells = ws[get_column_letter(col_letter)][skip_rows + 1:]
for cell in column_cells:
if cell.value is not None:
cell.value = None
# 새로운 데이터 추가
wb.save(file_path)
wb.close()
with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
df3.to_excel(writer, sheet_name=target_sheet, index=False, header=False,
startcol=column_index_from_string(col_start) - 1,
startrow=skip_rows + 1)
# xlsm******************************************************************************************************
import xlwings as xw # xlwings를 사용하여 .xlsm 파일 처리
from openpyxl.utils.cell import column_index_from_string, get_column_letter
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsm(wb, sheet_name, col_start, col_end, skip_rows, col_title_list=None):
ws = wb.sheets[sheet_name]
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(wb.fullname, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
return df
# Target 시트 업데이트
def update_target_sheet_xlsm(wb, df3, target_sheet, col_start, col_end, skip_rows):
ws = wb.sheets[target_sheet]
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
ws.range(f"{get_column_letter(col_letter)}{skip_rows + 2}:{get_column_letter(col_letter)}{ws.used_range.last_cell.row}").clear_contents()
# 새로운 데이터 추가
start_col = column_index_from_string(col_start) - 1
ws.range((skip_rows + 2, start_col + 1)).value = df3.values
# 메인 실행 부분
def main():
file_ext = os.path.splitext(FILE)[1][1:]
if file_ext == "xls":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xls(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, SKIP_ROWS_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xls(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, SKIP_ROWS_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
# update_target_sheet_xls(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, SKIP_ROWS_TARGET)
update_target_sheet_xls(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, SKIP_ROWS_TARGET)
# 시간측정
# end = time.time()
# print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsx":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsx(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, SKIP_ROWS_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsx(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, SKIP_ROWS_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsx(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, SKIP_ROWS_TARGET)
elif file_ext == "xlsm":
# 숨김 모드에서 Excel을 엽니다.
app = xw.App(visible=False)
try:
# Workbook 열기
wb = app.books.open(FILE)
# df1 처리
# df1 = process_dataframe(wb, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsm(wb, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, SKIP_ROWS_DF1)
# df2 처리
# df2 = process_dataframe(wb, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsm(wb, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, SKIP_ROWS_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsm(wb, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, SKIP_ROWS_TARGET)
# 작업 완료 후 저장
wb.save()
finally:
# Excel 애플리케이션을 종료
wb.close()
app.quit()
# 시간측정
end = time.time()
print(f"{file_ext} 경과시간: {end - start:.5f} sec")
# 완료 메시지
win32api.MessageBox(0, "작업을 완료했습니다.", "작업 완료", 0)
if __name__ == "__main__":
FILE = "01vlookup_chatgpt.xls" # .xls 파일 경로
# FILE = "01vlookup_chatgpt.xlsx" # .xlsx 파일 경로
# FILE = "01vlookup_chatgpt.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH_1 = "Sheet1" # Sheet1
COL_START_DF1 = "A" # 데이터 시작열 # Sheet1
COL_END_DF1 = "B" # 데이터 종료열 # Sheet1
SKIP_ROWS_DF1 = 3 # 열제목으로 skiprow # Sheet1
# SOURCE_SH_1 = "SheetA" # SheetA
# COL_START_DF1 = "A" # 데이터 시작열 # SheetA
# COL_END_DF1 = "B" # 데이터 종료열 # SheetA
# SKIP_ROWS_DF1 = 0 # 열제목으로 skiprow 계산 - "이름" # SheetA
# df2 설정 -----------------------------------------------------
SOURCE_SH_2 = "Sheet1" # Sheet1
COL_START_DF2 = "F" # Sheet1
COL_END_DF2 = "G" # Sheet1
SKIP_ROWS_DF2 = 3 # Sheet1
COL_TITLE_LIST_DF2 = ['제품', '가격'] # Sheet1
# SOURCE_SH_2 = "SheetB" # SheetB
# COL_START_DF2 = "A" # SheetB
# COL_END_DF2 = "B" # SheetB
# SKIP_ROWS_DF2 = 0 # SheetB
# COL_TITLE_LIST_DF2 = ['제품', '가격'] # SheetB
# df3 설정 join ------------------------------------------------
COL_STANDARD_NAME_DF3 = "제품"
JOIN_HOW_DF3 = "left"
# target 설정 --------------------------------------------------
SOURCE_SH_TARGET = "Sheet1"
COL_START_TARGET = "J"
COL_END_TARGET = "L"
SKIP_ROWS_TARGET = 3
##############################################################################
main()
xls 경과시간: 0.19776 sec
xlsx 경과시간: 0.26318 sec
xlsm 경과시간: 0.96137 sec
01vlookup_chatgpt_skiprow.py
0.01MB
02multivlookup_chatgpt.py
import os
import pandas as pd
import xlrd # .xls 파일을 읽기 위해 사용
# import xlwt # .xls 파일을 쓰기 위해 사용
from xlutils.copy import copy # xlrd로 읽은 파일을 xlwt로 수정 가능하게 하기 위해 사용
import win32api
import time
start = time.time() # 실행경과시간 측정
# xls*****************************************************************************************************
# 특정 시트에서 제목 찾고 skip row 계산 (xlrd 사용)
def FN_skip_row_count_xls(wb, sheet_name, col_start, find_title):
ws = wb.sheet_by_name(sheet_name)
count_skiprow_name = 0
for idx, row in enumerate(ws.col_values(column_index_from_string(col_start) - 1)):
if row == find_title:
count_skiprow_name = idx
break
return count_skiprow_name
# 엑셀 열 번호 변환 함수
def column_index_from_string_xls(col_str):
return ord(col_str.upper()) - ord('A') + 1
# xls 파일 처리 함수 (데이터프레임으로 변환)
def process_dataframe_xls(file, sheet_name, col_start, col_end, find_title, col_title_list=None):
# .xls 파일 처리 (xlrd 사용)
wb = xlrd.open_workbook(file)
ws = wb.sheet_by_name(sheet_name)
skip_rows = FN_skip_row_count_xls(wb, sheet_name, col_start, find_title)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(file, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='xlrd')
# 첫 번째 공백 행 이후의 모든 행 제거
first_blank_index = df[df.isnull().all(axis=1)].index.min()
if pd.notna(first_blank_index): # 공백 행이 있는 경우
df = df.iloc[:first_blank_index]
return df
# Target 시트 업데이트 (xlwt 사용)
def update_target_sheet_xls(file, df3, target_sheet, col_start, col_end, find_title):
# xlrd로 파일 열기
wb = xlrd.open_workbook(file, formatting_info=True) # 포맷 정보를 가져오기
ws = wb.sheet_by_name(target_sheet)
# 수정할 Workbook 생성
new_wb = copy(wb) # 기존 워크북 복사
new_ws = new_wb.get_sheet(0) # 첫 번째 시트 가져오기
# skip_rows 찾기
skip_rows = FN_skip_row_count_xls(wb, target_sheet, col_start, find_title)
# 데이터 지우기
for column_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
for row in range(skip_rows + 1, ws.nrows): # START_ROW부터 끝까지 반복 0,1,2,3,4
new_ws.write(row, column_letter - 1, None) # 값을 빈 문자열로 설정하여 지우기
# 수정된 파일 저장
new_wb.save(file)
# 새로운 데이터 추가
for row_idx, row in enumerate(df3.values):
for col_idx, value in enumerate(row):
if pd.isna(value):
value = '' # NaN값 처리
new_ws.write(skip_rows + 1 + row_idx, col_idx + column_index_from_string(col_start) - 1, value) # str(value)
# 수정된 파일 저장
new_wb.save(file)
# xlsx*************************************************************************************************
import openpyxl
# 특정 시트에서 제목 찾고 skip row 계산
def FN_skip_row_count_xlsx(file_path, sheet_name, col_start, find_title):
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
for count_skiprow_name, row in enumerate(ws.iter_rows(min_col=column_index_from_string(col_start),
max_col=column_index_from_string(col_start))):
if row[0].value == find_title:
break
return count_skiprow_name
# 데이터 행 수 계산
# def calculate_data_row_count(ws, col_start, skip_rows):
# max_row = skip_rows
# for cell in ws[col_start][skip_rows + 1:]:
# if cell.value is not None:
# max_row += 1
# else:
# break
# return max_row - skip_rows
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsx(file_path, sheet_name, col_start, col_end, find_title, col_title_list=None):
# file_ext = os.path.splitext(file_path)[1]
skip_rows = FN_skip_row_count_xlsx(file_path, sheet_name, col_start, find_title)
# wb = load_workbook_by_ext(file_path, file_ext)
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
# data_row_count_xlsx = calculate_data_row_count(ws, col_start, skip_rows)
df = pd.read_excel(file_path, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
# 첫 번째 공백 행 이후의 모든 행 제거
first_blank_index = df[df.isnull().all(axis=1)].index.min()
if pd.notna(first_blank_index): # 공백 행이 있는 경우
df = df.iloc[:first_blank_index]
return df
# Target 시트 업데이트
def update_target_sheet_xlsx(file_path, df3, target_sheet, col_start, col_end, find_title):
wb = openpyxl.load_workbook(file_path)
ws = wb[target_sheet]
# Target 시트에서 제목 찾고 skip row 계산
skip_rows = FN_skip_row_count_xlsx(file_path, target_sheet, col_start, find_title)
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
column_cells = ws[get_column_letter(col_letter)][skip_rows + 1:]
for cell in column_cells:
if cell.value is not None:
cell.value = None
# 새로운 데이터 추가
wb.save(file_path)
wb.close()
with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
df3.to_excel(writer, sheet_name=target_sheet, index=False, header=False,
startcol=column_index_from_string(col_start) - 1,
startrow=skip_rows + 1)
# xlsm******************************************************************************************************
import xlwings as xw # xlwings를 사용하여 .xlsm 파일 처리
from openpyxl.utils.cell import column_index_from_string, get_column_letter
# 특정 시트에서 제목 찾고 skip row 계산
def FN_skip_row_count_xlsm(ws, col_start, find_title):
max_row = ws.used_range.last_cell.row # 최대 행 수 가져오기
for count_skiprow_name, row in enumerate(ws.range(f"{col_start}1:{col_start}{max_row}").value):
if row == find_title:
break
return count_skiprow_name
# 데이터 행 수 계산
# def calculate_data_row_count(ws, col_start, skip_rows):
# max_row = ws.used_range.last_cell.row # 최대 행 수 가져오기
# # 데이터가 있는 셀만 확인
# cells = ws.range(f"{col_start}{skip_rows + 2}:{col_start}{max_row}").value
# actual_data_count = sum(1 for cell in cells if cell is not None)
# return actual_data_count
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsm(wb, sheet_name, col_start, col_end, find_title, col_title_list=None):
ws = wb.sheets[sheet_name]
# skip_rows 계산
skip_rows = FN_skip_row_count_xlsm(ws, col_start, find_title)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(wb.fullname, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
# 첫 번째 공백 행 이후의 모든 행 제거
first_blank_index = df[df.isnull().all(axis=1)].index.min()
if pd.notna(first_blank_index): # 공백 행이 있는 경우
df = df.iloc[:first_blank_index]
return df
# Target 시트 업데이트
def update_target_sheet_xlsm(wb, df3, target_sheet, col_start, col_end, find_title):
ws = wb.sheets[target_sheet]
# Target 시트에서 제목 찾고 skip row 계산
skip_rows = FN_skip_row_count_xlsm(ws, col_start, find_title)
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
ws.range(f"{get_column_letter(col_letter)}{skip_rows + 2}:{get_column_letter(col_letter)}{ws.used_range.last_cell.row}").clear_contents()
# 새로운 데이터 추가
start_col = column_index_from_string(col_start) - 1
ws.range((skip_rows + 2, start_col + 1)).value = df3.values
# 메인 실행 부분
def main():
file_ext = os.path.splitext(FILE)[1][1:]
if file_ext == "xls":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xls(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, FIND_TITLE_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xls(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, FIND_TITLE_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xls(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET,FIND_TITLE_TARGET)
# 시간측정
# end = time.time()
# print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsx":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsx(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, FIND_TITLE_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsx(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, FIND_TITLE_DF2, COL_TITLE_LIST_DF2)
# # df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsx(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, FIND_TITLE_TARGET)
# 시간측정
# end = time.time()
# print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsm":
# 숨김 모드에서 Excel을 엽니다.
app = xw.App(visible=False)
try:
# Workbook 열기
wb = app.books.open(FILE)
# df1 처리
# df1 = process_dataframe(wb, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsm(wb, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, FIND_TITLE_DF1)
# df2 처리
# df2 = process_dataframe(wb, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsm(wb, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, FIND_TITLE_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsm(wb, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, FIND_TITLE_TARGET)
# 작업 완료 후 저장
wb.save()
finally:
# Excel 애플리케이션을 종료
wb.close()
app.quit()
# 시간측정
end = time.time()
print(f"{file_ext} 경과시간: {end - start:.5f} sec")
# 완료 메시지
win32api.MessageBox(0, "작업을 완료했습니다.", "작업 완료", 0)
if __name__ == "__main__":
FILE = "02multivlookup_chatgpt.xls" # .xls 파일 경로
# FILE = "02multivlookup_chatgpt.xlsx" # .xlsx 파일 경로
# FILE = "02multivlookup_chatgpt.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH_1 = "Sheet1" # Sheet1
COL_START_DF1 = "B" # 데이터 시작열 # Sheet1
COL_END_DF1 = "D" # 데이터 종료열 # Sheet1
FIND_TITLE_DF1 = "이름" # 열제목으로 skiprow 계산 - "이름"# Sheet1
# SOURCE_SH_1 = "SheetA" # SheetA
# COL_START_DF1 = "A" # 데이터 시작열 # SheetA
# COL_END_DF1 = "C" # 데이터 종료열 # SheetA
# FIND_TITLE_DF1 = "이름" # 열제목으로 skiprow 계산 - "이름"# SheetA
# df2 설정 -----------------------------------------------------
SOURCE_SH_2 = "Sheet1" # Sheet1
COL_START_DF2 = "G" # Sheet1
COL_END_DF2 = "I" # Sheet1
FIND_TITLE_DF2 = "업체" # Sheet1
COL_TITLE_LIST_DF2 = ['업체', '제품', '가격'] # Sheet1
# SOURCE_SH_2 = "SheetB" # SheetB
# COL_START_DF2 = "A" # SheetB
# COL_END_DF2 = "C" # SheetB
# FIND_TITLE_DF2 = "업체" # SheetB
# COL_TITLE_LIST_DF2 = ['업체', '제품', '가격] # SheetB
# df3 설정 join ------------------------------------------------
COL_STANDARD_NAME_DF3 = ["업체", "제품"]
JOIN_HOW_DF3 = "left"
# target 설정 --------------------------------------------------
SOURCE_SH_TARGET = "Sheet1"
COL_START_TARGET = "L"
COL_END_TARGET = "O"
FIND_TITLE_TARGET = "이름"
##############################################################################
main()
xls 경과시간: 0.62910 sec
xlsx 경과시간: 0.83277 sec
xlsm 경과시간: 1.31572 sec
02multivlookup_chatgpt_skiprow.py
import os
import pandas as pd
import xlrd # .xls 파일을 읽기 위해 사용
# import xlwt # .xls 파일을 쓰기 위해 사용
from xlutils.copy import copy # xlrd로 읽은 파일을 xlwt로 수정 가능하게 하기 위해 사용
import win32api
import time
start = time.time() # 실행경과시간 측정
# xls*****************************************************************************************************
# 특정 시트에서 제목 찾고 skip row 계산 (xlrd 사용)
# def FN_skip_row_count_xls(wb, sheet_name, col_start, find_title):
# ws = wb.sheet_by_name(sheet_name)
# count_skiprow_name = 0
# for idx, row in enumerate(ws.col_values(column_index_from_string(col_start) - 1)):
# if row == find_title:
# count_skiprow_name = idx
# break
# return count_skiprow_name
# 엑셀 열 번호 변환 함수
def column_index_from_string_xls(col_str):
return ord(col_str.upper()) - ord('A') + 1
# xls 파일 처리 함수 (데이터프레임으로 변환)
def process_dataframe_xls(file, sheet_name, col_start, col_end, skip_rows, col_title_list=None):
# .xls 파일 처리 (xlrd 사용)
wb = xlrd.open_workbook(file)
ws = wb.sheet_by_name(sheet_name)
# skip_rows = FN_skip_row_count_xls(wb, sheet_name, col_start, find_title)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(file, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='xlrd')
# 첫 번째 공백 행 이후의 모든 행 제거
first_blank_index = df[df.isnull().all(axis=1)].index.min()
if pd.notna(first_blank_index): # 공백 행이 있는 경우
df = df.iloc[:first_blank_index]
return df
# Target 시트 업데이트 (xlwt 사용)
def update_target_sheet_xls(file, df3, target_sheet, col_start, col_end, skip_rows):
# xlrd로 파일 열기
wb = xlrd.open_workbook(file, formatting_info=True) # 포맷 정보를 가져오기
ws = wb.sheet_by_name(target_sheet)
# 수정할 Workbook 생성
new_wb = copy(wb) # 기존 워크북 복사
new_ws = new_wb.get_sheet(0) # 첫 번째 시트 가져오기
# skip_rows 찾기
# skip_rows = FN_skip_row_count_xls(wb, target_sheet, col_start, find_title)
# 데이터 지우기
for column_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
for row in range(skip_rows + 1, ws.nrows): # START_ROW부터 끝까지 반복 0,1,2,3,4
new_ws.write(row, column_letter - 1, None) # 값을 빈 문자열로 설정하여 지우기
# 수정된 파일 저장
new_wb.save(file)
# 새로운 데이터 추가
for row_idx, row in enumerate(df3.values):
for col_idx, value in enumerate(row):
if pd.isna(value):
value = '' # NaN값 처리
new_ws.write(skip_rows + 1 + row_idx, col_idx + column_index_from_string(col_start) - 1, value) # str(value)
# 수정된 파일 저장
new_wb.save(file)
# xlsx*************************************************************************************************
import openpyxl
# 특정 시트에서 제목 찾고 skip row 계산
# def FN_skip_row_count_xlsx(file_path, sheet_name, col_start, find_title):
# wb = openpyxl.load_workbook(file_path)
# ws = wb[sheet_name]
# for count_skiprow_name, row in enumerate(ws.iter_rows(min_col=column_index_from_string(col_start),
# max_col=column_index_from_string(col_start))):
# if row[0].value == find_title:
# break
# return count_skiprow_name
# 데이터 행 수 계산
# def calculate_data_row_count(ws, col_start, skip_rows):
# max_row = skip_rows
# for cell in ws[col_start][skip_rows + 1:]:
# if cell.value is not None:
# max_row += 1
# else:
# break
# return max_row - skip_rows
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsx(file_path, sheet_name, col_start, col_end, skip_rows, col_title_list=None):
# file_ext = os.path.splitext(file_path)[1]
# skip_rows = FN_skip_row_count_xlsx(file_path, sheet_name, col_start, find_title)
# wb = load_workbook_by_ext(file_path, file_ext)
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
# data_row_count_xlsx = calculate_data_row_count(ws, col_start, skip_rows)
df = pd.read_excel(file_path, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
# 첫 번째 공백 행 이후의 모든 행 제거
first_blank_index = df[df.isnull().all(axis=1)].index.min()
if pd.notna(first_blank_index): # 공백 행이 있는 경우
df = df.iloc[:first_blank_index]
return df
# Target 시트 업데이트
def update_target_sheet_xlsx(file_path, df3, target_sheet, col_start, col_end, skip_rows):
wb = openpyxl.load_workbook(file_path)
ws = wb[target_sheet]
# Target 시트에서 제목 찾고 skip row 계산
# skip_rows = FN_skip_row_count_xlsx(file_path, target_sheet, col_start, find_title)
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
column_cells = ws[get_column_letter(col_letter)][skip_rows + 1:]
for cell in column_cells:
if cell.value is not None:
cell.value = None
# 새로운 데이터 추가
wb.save(file_path)
wb.close()
with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
df3.to_excel(writer, sheet_name=target_sheet, index=False, header=False,
startcol=column_index_from_string(col_start) - 1,
startrow=skip_rows + 1)
# xlsm******************************************************************************************************
import xlwings as xw # xlwings를 사용하여 .xlsm 파일 처리
from openpyxl.utils.cell import column_index_from_string, get_column_letter
# 특정 시트에서 제목 찾고 skip row 계산
# def FN_skip_row_count_xlsm(ws, col_start, find_title):
# max_row = ws.used_range.last_cell.row # 최대 행 수 가져오기
# for count_skiprow_name, row in enumerate(ws.range(f"{col_start}1:{col_start}{max_row}").value):
# if row == find_title:
# break
# return count_skiprow_name
# 데이터 행 수 계산
def calculate_data_row_count(ws, col_start, skip_rows):
max_row = ws.used_range.last_cell.row # 최대 행 수 가져오기
# 데이터가 있는 셀만 확인
cells = ws.range(f"{col_start}{skip_rows + 2}:{col_start}{max_row}").value
actual_data_count = sum(1 for cell in cells if cell is not None)
return actual_data_count
# df1과 df2 데이터를 처리하는 함수
def process_dataframe_xlsm(wb, sheet_name, col_start, col_end, skip_rows, col_title_list=None):
ws = wb.sheets[sheet_name]
# skip_rows 계산
# skip_rows = FN_skip_row_count_xlsm(ws, col_start, find_title)
# pandas를 사용하여 해당 시트의 데이터를 DataFrame으로 가져오기
df = pd.read_excel(wb.fullname, sheet_name=sheet_name,
header=skip_rows,
usecols=f"{col_start}:{col_end}",
names=col_title_list,
engine='openpyxl')
# 첫 번째 공백 행 이후의 모든 행 제거
first_blank_index = df[df.isnull().all(axis=1)].index.min()
if pd.notna(first_blank_index): # 공백 행이 있는 경우
df = df.iloc[:first_blank_index]
return df
# Target 시트 업데이트
def update_target_sheet_xlsm(wb, df3, target_sheet, col_start, col_end, skip_rows):
ws = wb.sheets[target_sheet]
# Target 시트에서 제목 찾고 skip row 계산
# skip_rows = FN_skip_row_count_xlsm(ws, col_start, find_title)
# 기존 데이터를 제거
for col_letter in range(column_index_from_string(col_start), column_index_from_string(col_end) + 1):
ws.range(f"{get_column_letter(col_letter)}{skip_rows + 2}:{get_column_letter(col_letter)}{ws.used_range.last_cell.row}").clear_contents()
# 새로운 데이터 추가
start_col = column_index_from_string(col_start) - 1
ws.range((skip_rows + 2, start_col + 1)).value = df3.values
# 메인 실행 부분
def main():
file_ext = os.path.splitext(FILE)[1][1:]
if file_ext == "xls":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xls(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, SKIP_ROWS_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xls(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, SKIP_ROWS_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xls(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET,SKIP_ROWS_TARGET)
# 시간측정
# end = time.time()
# print("xls 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsx":
# df1 처리
# df1 = process_dataframe(FILE, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsx(FILE, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, SKIP_ROWS_DF1)
# df2 처리
# df2 = process_dataframe(FILE, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsx(FILE, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, SKIP_ROWS_DF2, COL_TITLE_LIST_DF2)
# # df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsx(FILE, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, SKIP_ROWS_TARGET)
# 시간측정
# end = time.time()
# print("xlsx 경과시간:"f"{end - start:.5f} sec")
elif file_ext == "xlsm":
# 숨김 모드에서 Excel을 엽니다.
app = xw.App(visible=False)
try:
# Workbook 열기
wb = app.books.open(FILE)
# df1 처리
# df1 = process_dataframe(wb, "Sheet1", "A", "B", "이름")
df1 = process_dataframe_xlsm(wb, SOURCE_SH_1, COL_START_DF1, COL_END_DF1, SKIP_ROWS_DF1)
# df2 처리
# df2 = process_dataframe(wb, "Sheet1", "F", "G", "제품", ['제품', '가격'])
df2 = process_dataframe_xlsm(wb, SOURCE_SH_2, COL_START_DF2, COL_END_DF2, SKIP_ROWS_DF2, COL_TITLE_LIST_DF2)
# df1과 df2 결합
df3 = df1.merge(df2, on= COL_STANDARD_NAME_DF3, how= JOIN_HOW_DF3)
# Target 시트 업데이트
update_target_sheet_xlsm(wb, df3, SOURCE_SH_TARGET, COL_START_TARGET, COL_END_TARGET, SKIP_ROWS_TARGET)
# 작업 완료 후 저장
wb.save()
finally:
# Excel 애플리케이션을 종료
wb.close()
app.quit()
# 시간측정
end = time.time()
print(f"{file_ext} 경과시간: {end - start:.5f} sec")
# 완료 메시지
win32api.MessageBox(0, "작업을 완료했습니다.", "작업 완료", 0)
if __name__ == "__main__":
FILE = "02multivlookup_chatgpt.xls" # .xls 파일 경로
# FILE = "02multivlookup_chatgpt.xlsx" # .xlsx 파일 경로
# FILE = "02multivlookup_chatgpt.xlsm" # .xlsm 파일 경로
# df1 설정 -----------------------------------------------------
SOURCE_SH_1 = "Sheet1" # Sheet1
COL_START_DF1 = "B" # 데이터 시작열 # Sheet1
COL_END_DF1 = "D" # 데이터 종료열 # Sheet1
SKIP_ROWS_DF1 = 3 # 열제목으로 skiprow # Sheet1
# FIND_TITLE_DF1 = "이름" # 열제목으로 skiprow 계산 - "이름"# Sheet1
# SOURCE_SH_1 = "SheetA" # SheetA
# COL_START_DF1 = "A" # 데이터 시작열 # SheetA
# COL_END_DF1 = "B" # 데이터 종료열 # SheetA
# FIND_TITLE_DF1 = "이름" # 열제목으로 skiprow 계산 - "이름"# SheetA
# df2 설정 -----------------------------------------------------
SOURCE_SH_2 = "Sheet1" # Sheet1
COL_START_DF2 = "G" # Sheet1
COL_END_DF2 = "I" # Sheet1
SKIP_ROWS_DF2 = 3 # 열제목으로 skiprow # Sheet1
# FIND_TITLE_DF2 = "업체" # Sheet1
COL_TITLE_LIST_DF2 = ['업체', '제품', '가격'] # Sheet1
# SOURCE_SH_2 = "SheetB" # SheetB
# COL_START_DF2 = "A" # SheetB
# COL_END_DF2 = "B" # SheetB
# FIND_TITLE_DF2 = "제품" # SheetB
# COL_TITLE_LIST_DF2 = ['제품', '가격'] # SheetB
# df3 설정 join ------------------------------------------------
COL_STANDARD_NAME_DF3 = ["업체", "제품"]
JOIN_HOW_DF3 = "left"
# target 설정 --------------------------------------------------
SOURCE_SH_TARGET = "Sheet1"
COL_START_TARGET = "L"
COL_END_TARGET = "O"
SKIP_ROWS_TARGET = 3 # 열제목으로 skiprow # Sheet1
FIND_TITLE_TARGET = "이름"
##############################################################################
main()
xls 경과시간: 0.46974 sec
xlsx 경과시간: 0.74799 sec
xlsm 경과시간: 1.43787 sec