통합 파일 작성하기

통합 파일 작성하기

기존 형식의 엑셀 파일(results.xlsx)에 여러 개의 엑셀 파일 내 데이터를

하나의 엑셀 파일에 자동으로 통합하고 수식과 서식을 자동으로 정리하는 프로세스를 구축했다.

*첨부한 이미지들은 이해를 돕기 위해 임의로 작성했다.

주요 단계

1. 기존 엑셀의 데이터를 삭제 (수식은 유지하기위해 첫번째 행 제거x)

2. 최신 엑셀 파일 자동 탐색 및 불러오기

3. 파일 확장자 및 시트명 처리

4. 테이블 생성 및 수식·서식 자동 적용

5. 전체 자동 실행 및 예외 처리

기본

from openpyxl import load_workbook

# 엑셀 파일 열기 
wb = load_workbook("test.xlsx") # data_only=True : 계산된 값(수식x)  
wb.sheetnames # 전체 시트 리스트
ws = wb['abc'] # 'abc' 시트
ws['E2'].value # E2 셀 값 읽기 

1. 기존 데이터 제거

엑셀 내의 기존 데이터를 삭제하되 수식이 존재하는 첫 번째 행은 유지했다. (ex. =sum(...))

시트마다 헤더 위치가 달라(1행 또는 2행) 조건문으로 삭제 기준을 다르게 적용했다.

# 경고 메시지 
import warnings
warnings.filterwarnings('ignore')

from openpyxl import load_workbook
path = 'excel/'
wb = load_workbook(path + "raw_data.xlsx")    

for i in range(10):
    ws = wb.worksheets[i]
    max_row = ws.max_row
    if i <= 7:
        if max_row >= 4:
            ws.delete_rows(4, amount=max_row - 3)
    else:
        if max_row >= 3:
            ws.delete_rows(3, amount=max_row - 2)
wb.save("results.xlsx")

*숨겨진 시트도 포함되므로 시트 개수(range(10))는 전체 시트 수 기준으로 작성해야한다.

log 설정

import logging
logger = logging.getLogger(name = "00. 기존 데이터 제거.py")
logger.setLevel(logging.INFO)
formatter = logging.Formatter('[ %(name)s ] ( %(levelname)s ) %(message)s')
stream_handler = logging.StreamHandler()
stream_handler.setFormatter(formatter)
logger.addHandler(stream_handler)

파일별 로그명(name)을 다르게 설정해 동일한 로깅 포맷으로 관리했다.

2. 파일 불러오기

각 시트에 들어갈 데이터를 최근에 저장된 파일에서 불러왔다.

파일 하나씩 실행하면서 아래 함수를 실행하므로 가장 최근 파일로만 진행했다.

• openpyxl → 엑셀 내부 편집 용도

• pandas → 엑셀 데이터를 분석/가공 용도

  • openpyxl은 엑셀 기준 → 1부터 시작
  • pandas는 파이썬 기준 → 0부터 시작

xls → xlsx로 변환

.xls 파일은 win32com을 통해 Excel을 직접 실행시켜 .xlsx로 변환했다.

def convert_xls_to_xlsx(file_path):
    if not file_path.lower().endswith(".xls"):
        raise ValueError("Only .xls files can be converted.") # 에러를 일부러 발생시키는 키워드

    abs_path = os.path.abspath(file_path)
    new_path = abs_path + "x"  # .xlsx

    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.DisplayAlerts = False

    try:
        wb = excel.Workbooks.Open(abs_path)
        wb.SaveAs(new_path, FileFormat=51)  # .xlsx
        wb.Close()
        print(f"변환 완료: {new_path}")
    except Exception as e:
        print(f"변환 실패: {e}")
        new_path = None
    finally:
        excel.Quit()

    return new_path

win32com : 엑셀을 직접 실행시키고 저장

최신 파일 불러오기

def load_latest_excel(path, skiprows=0):
    excel_files = glob.glob(os.path.join(path, "*.xls*")) + glob.glob(os.path.join(path, "*.csv"))
    if not excel_files:
        logger.error("다운로드 폴더 - Excel 또는 CSV 파일 X")
        return None, None

    latest_file = max(excel_files, key=os.path.getmtime)
    print(f"가장 최근 파일: {os.path.basename(latest_file)}")

    if latest_file.lower().endswith(".csv"):
        df = pd.read_csv(latest_file, sep='\t', encoding='utf-16', skiprows=skiprows)
    elif latest_file.lower().endswith(".xls"):
        try :
            df = pd.read_excel(latest_file, skiprows=skiprows, engine='xlrd')
        except :
            converted = convert_xls_to_xlsx(latest_file)
            if converted:
                    df = pd.read_excel(converted, skiprows=skiprows, engine='openpyxl')
            else:
                raise Exception("변환 실패로 .xls 파일 열 수 없음!")
    elif latest_file.lower().endswith(".xlsx"):
        df = pd.read_excel(latest_file, skiprows=skiprows, engine="openpyxl")
    else:
        raise ValueError("지원하지 않는 파일 형식")
`
    return df, latest_file

• glob.glob() 으로 모든 엑셀 파일을 불러오고

• os.path.getmtime() 으로 가장 최신 파일을 선택

• 확장자에 따라 다르게 처리(.csv, .xls, .xlsx 파일 구분 처리)

3. 파일 통합 및 저장

확장자

def process_excel(sheet_name, start_row=2, skiprows=0, header_row = 2):
    path = r"C:\Users\Downloads"
    df, latest_file = load_latest_excel(path, skiprows=skiprows)
    if df is None:
        logger.info(f"{sheet_name} 파일이 없음!")
        return

   # 파일명 시트 이름으로 변경
    ext = os.path.splitext(latest_file)[1].lower() # 확장자
    new_filename = f"{sheet_name}{ext}"
    new_filepath = os.path.join(path, new_filename)

    if latest_file != new_filepath:
        os.rename(latest_file, new_filepath)
        logger.info(f"파일 이름 변경: {os.path.basename(latest_file)} → {new_filename}")

    exist_file = "results.xlsx"
    wb = load_workbook(exist_file)
   
    if sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
    else:
        ws = wb.create_sheet(sheet_name)

엑셀 파일의 확장자가 .xlsx뿐 아니라 .xls와 .csv도 존재한다.

splitext : 파일명을 입력으로 받아 파일명과 확장자를 분리해서 반환한다.

최신 파일명을 시트명으로 통일해 저장했다.(이름 변경)

헤더 및 데이터 붙여넣기

# 데이터 중에 info 와 같이 필요없는 데이터가 있는 경우가 있어 제거
df = df[~df.iloc[:, 0].astype(str).str.contains("info")] # 여러 행 첫번째 열

# header 붙여넣기
for j, col in enumerate(df.columns, start=1):
    ws.cell(row=header_row, column=j, value=col)

# 데이터 붙여넣기 (2행부터 수식 열 전까지)
for i, row in df.iterrows(): # 0 or 1부터 시작 
    for j, value in enumerate(row, start=1): # 1부터 시작
        ws.cell(row=i + start_row, column=j, value=value)

각 시트마다 헤더 위치(header_row)가 다르기 때문에 인자로 받았다.

기본값은 대부분의 시트에 맞춰 header_row=2로 설정했다.

i는 “info”가 있는 엑셀 파일은 “info” 행을 제거해서 1부터 시작하고

“info”가 없는 엑셀 파일은 0부터 시작해서 i가 0 or 1이다.

테이블 생성

# 테이블 범위
table_start_row = header_row
table_end_row = start_row + len(df) - (1 if header_row == 1 else 0)
end_col_letter = get_column_letter(df.shape[1] + 3)
table_range = f"A{table_start_row}:{end_col_letter}{table_end_row}"

# 기존 테이블 삭제
for tbl_name in list(ws.tables.keys()):
    del ws.tables[tbl_name]

# 테이블 생성
table = Table(displayName=sheet_name, ref=table_range)
table.tableStyleInfo = TableStyleInfo(
    name="TableStyleMedium2",
    showFirstColumn=False,
    showLastColumn=False,
    showRowStripes=True,
    showColumnStripes=False
)
ws.add_table(table)

• get_column_letter : 열의 인덱스 정보를 입력받고 해당 열에 대한 문자를 반환

  *행은 숫자, 열은 문자

수식 및 서식 자동 적용

끝에서 3개의 열(수식 포함)에 기존 행의 서식을 복사 적용

def apply_formatting(ws, table_start_row, table_end_row, columns_to_format):
    for col in range(1, ws.max_column+1) :
        first_row_cell = ws.cell(row=table_start_row+1, column=col)

        if col in columns_to_format: # 끝에서 3번째까지 열
            for row in range(table_start_row + 1, table_end_row + 1):
                cell = ws.cell(row=row, column=col)
                cell.value = first_row_cell.value
                cell.number_format = first_row_cell.number_format
        else :
            for row in range(table_start_row+1, table_end_row+1) :
                cell = ws.cell(row=row, column=col)
                cell.number_format = first_row_cell.number_format

적용

# 서식 자동 적용 (끝에서 3개 열만 수식 적용)
columns_to_format = [df.shape[1]+1, df.shape[1]+2, df.shape[1]+3]  # 끝에서 3개 열
apply_formatting(ws, table_start_row, table_end_row, columns_to_format)

wb.save(exist_file)
print(f"{sheet_name} 완료")

호출

x = '자동화/01. data/'

subprocess.run(["python", x + "00. 기존 데이터 제거.py"])
time.sleep(2)

data = subprocess.run(["python", x + "01. data_1.py"], capture_output=True, text=True)
output = data.stdout
if "데이터 확인" in output :  
    logger.info("데이터 존재 x")
else :
    process_excel("data_1")
time.sleep(2)

• STDOUT (Standard Output, 1, 출력을 위한 스트림)
  표준 출력은 프로그램이 출력 데이터를 기록하는 스트림으로 줄여서 stdout 으로 표현

  • capture_output = True : 표준 출력(stdout)과 표준 에러(stderr)가 캡처
  • text=True : 문자열

01. data_1.py에서 조건문에 print("데이터 확인")를 추가하여

원하는 조건이 실행 되었을 때 print문이 출력되고

해당 파일이 끝이나면 해당 값을 output에 저장하여

값 유무에 따라 process_excel("data_1")를 실행 여부를 결정

문제 해결 과정 정리

날짜 형식 문제

데이터 붙여넣기 시 날짜가 “YYYY-MM-DD HH:MM:SS” 형태로 변환되던 문제 해결

if (j==1) & ("data_1" in sheet_name):
    value = value.strftime("%Y-%m-%d")

이후 셀 서식 자동 적용 기능이 완성되면서 제거했다.

수식 자동 반영 문제

끝 3열에 존재하는 수식이 신규 행에 자동 복사되지 않는 문제 해결

첫 번째 데이터 행의 수식을 기준으로 아래 행에 복사

이후 일반 셀에도 서식 전체 적용(본문 내용 참고 - apply_formatting())

REFERENCE

• [새로 배운 코드 조각] 파일 이름에서 확장자 분리하기 : os.path.splitext(file_name)
• 04-02. 엑셀 데이터 읽기
• 표준 스트림과 stdin, stdout, stderr
• subprocess — 서브 프로세스 관리
• 파이썬(Python) logging 모듈을 이용한 로그(Log) 남기기
• openpyxl로 엑셀 다루기 016. get_column_letter 함수로 열에 대한 문자 얻기 (column letter)