[python]엑셀 파일에서 데이터 추출해서 소스 생성하기

사내 자바 개발자들에게 공유한 가벼운 팁 옮겨 적어 봅니다.

엑셀 파일에서 추출한 정보로 템플릿 엔진을 이용하여 소스 파일을 만들어주는 python 프로그램을 만들어보았습니다(이하에서는 평어체로)

사용 라이브러리 : jinja2 (템플릿 엔진), openpyxl(엑셀 라이브러리)

1. JSON RPC i/f 스펙을 정의해놓은 엑셀 파일이 있다.
2. 엑셀 파일에는 시트별로 각 메시지에 대한 기본 정보와 JSON 형태의 메시지에 포함되는 속성들에 대한 정보가 기술되어 있다.

내가 해야할 작업은 다음과 같다.
2에 정의된 속성 정보들 중 '속성명', '데이터 타입(길이)'로 DB 테이블 생성 DDL 스크립트 작성, JPA 를 쓰기 때문에 메시지별 엔티티 파일 작성.

메시지 종류가 늘어나니 수작업으로 하던 이 작업을 프로그램으로 해결해보고자 한다.
 
1. 전체 소스는 아래와 같다.
구현 로직은 대부분 간단하므로 소스 내 코멘트로 설명해놨음.
속셩명 변환하는 함수만 따로 요약하여 마지막에 기술하겠음.

import jinja2
import openpyxl
import re


def to_pascal_case(snake_str):
    return snake_str.replace("_", " ").title().replace(" ", "")


def to_camel_case(snake_str):
    components = snake_str.split('_')
    return components[0] + ''.join(x.title() for x in components[1:])


def to_std_word(raw_name):
    std_dic = {'vessel': 'vsl', 'machine': 'mach', 'status': 'sts'}
    return raw_name if raw_name not in std_dic else std_dic[raw_name]


def to_std_name(raw_name):
    components = raw_name.split('_')
    return "_".join(list(map(to_std_word, components)))



# 시트에서 속성 정보들어 있는 시작과 끝 row 번호 구하기
def get_range(ws):
    field_col = ws['A']
    start_no = 0
    end_no = 0
    for cell in field_col:
        if cell.value == "BODY":
            start_no = cell.row + 1
        if cell.value == "Sample Request JSON":
            end_no = cell.row - 1
            break
    return end_no, start_no



# 시트의 속성 정보 들어있는 범위 내에서 필요 정보 구해서 data 란 리스트에 담기
# data = ['속성명(소문자 snake_case)', '축약어 처리 후 camelCase 처리한 속성명', '데이터 타입', '데이터 길이']

def get_data(ws, f_range):
    end_no, start_no = f_range(ws)
    data = []
    for i in range(end_no - start_no):
        c_snake = ws.cell(row=(i + start_no), column=1).value.replace("  > ", "").lower()
        c_camel = to_camel_case(to_std_name(c_snake))
        c_type = ws.cell(row=i + start_no, column=4).value
        c_len = re.sub(r'[^0-9]', '', c_type)
        data.append([c_snake, c_camel, c_type.split("(")[0], c_len])

    return data
 
# 템플릿 파일에 추출한 data 를 넣어서 랜더링
def get_output(data, sht_name):
    tbl_name = "evt_" + to_std_name(sht_name.lower())
    src_name = to_pascal_case(tbl_name)

    tpl_loader = jinja2.FileSystemLoader(searchpath="./")
    tpl_env = jinja2.Environment(loader=tpl_loader)
    tpl_file = "entity.jinja"
    template = tpl_env.get_template(tpl_file)
    output_text = template.render(data=data, tbl=tbl_name, src=src_name)
    return src_name, output_text



# 랜더링된 결과 텍스트를 파일명별로 저장.
def make_java(output_text, src_name):
    with open("./output/" + src_name + ".java", 'w') as f:
        f.write(output_text)

  
def main():
    wb = openpyxl.load_workbook(
        'C:\...\excel_file.xlsx')
    sht_names = wb.sheetnames            # 엑셀 파일의 모든 시트명 얻기
    exc_lists = ['MSG LIST', 'qcWorkList']   # 작업 제외할 시트
    for sht_name in sht_names:
        if sht_name not in exc_lists:
            ws = wb[sht_name]
            data = get_data(ws, get_range)
            src_name, output_text = get_output(data, sht_name)
            make_java(output_text, src_name)

   

     print('finished')


if __name__ == '__main__':
    main()

 

 

2. 템플릿 엔진에서 사용할 jinja 템플릿 파일 중 일부

...
import lombok.Data;
import java.time.LocalDateTime;

@Data
@Table("{{tbl}}")
@JsonNaming(PropertyNamingStrategies.SnakeCaseStrategy.class)
@JsonIgnoreProperties({ "id" })
public class {{src}} {
    @Id
    private Long id;

    {% for cols in data %}
    @JsonProperty("{{cols[0]}}")
    private {{cols[2]}} {{cols[1]}};
    {% endfor %}

...

 

3. 엑셀 파일의 'VESSEL_VISIT'라는 시트 중 메시지 속성에 대한 정보가 있는 부분은 대략 아래와 같은 형태이다.(엑셀 파일에서 일부 복사)

 

Col A                                                                              Col B       Col C                    Col D

Request CDetails
Field_ID	#	Nullable	Field_Type
HEAD	1
> MSG_ID	1	N	String
> MSG_SOURCE	1	N	String
> EVENT_TYPE	1	N	String
> EVENT_SUB_TYPE	1	Y	String
> NOTES	1	Y	String
BODY	1…n
> VESSEL_VISIT	1	N	String(36)
> VESSEL_CALL_SIGN	1	Y	String(30)
> BOW_BOLLARD_OFFSET_CM	1	Y	Integer(14)
> STERN_BOLLARD_OFFSET_CM	1	Y	Integer(14)

 

속성명 변환하는 로직 요약.

엑셀의 속성명 정의는 대문자 snake_case(under_score) 형식이다.
1. 속성명에 포함된 단어 중 표준 축약어가 있는 경우는 축약어로 변환해야 한다.( to_std_word )
2. java 소스에서 사용하려면 파일명과 class 명을 위해서는 PascalCase로( to_pascal_case ), 속성 선언을 위해서는 camelCase 로( to_camel_case ) 변환해야 한다.

1. to_std_word(), to_std_name()
pythonic 하게 코딩하려고 나름 고심하며 코딩한거다.
구현은 간단한데 '_' 기준으로 split 로 분리한 뒤 축약어로 정의한 단어는 축약어로 변환하고 다시 '_'로 다시 합쳤다.

이해 쉽게 'VESSEL_VISIT'이란 속성명이 변환되는걸 단계별로 표시하면 다음과 같다.
VESSEL_VISIT -> vessel_visit -> ['vessel', 'visit'] -> ['vsl', 'visit'] -> vsl_visit
위 단계 중 세번째부터가 to_std_name (네번째는 to_std_word)이 하는 일.

2. to_pascal_case, to_camel_case 함수는 간단하니 설명 스킵.

자바 엔티니 소스용 템플릿 파일은 최대한 단순하게 구성해놨기 떄문에 설명 스킵.

DB 테이블 생성 DDL 스크립트 생성 부분은 아직 구현하지 않았으나 필요한 데이터 추출은 거의 다 해놨기 때문에 템플릿 파일만 하나 말들고 몇 줄 정도만 코딩 추가하면 될 듯.

 

요약.

1. jinja2 (템플릿 엔진), openpyxl(엑셀 라이브러리) 활용도 높아 보입니다. 사용도 어렵지 않아 보이니 단순/반복 작업에 활용해보시길.

2. 소스 중 to_*** 로 네이밍된 함수들의 pythonic 한 코딩 스타일은 한 번 더 눈여겨 봐주시길. 

3. to_std_name 함수에서 for 문 사용하지 않고 map 함수 사용하여 처리하는 부분도 눈 도장 한 번.

4. 3의 map 함수, 그리고 get_data 함수에서 함수를 parameter 로 넘겨 사용한 부분도 눈 도장 한 번.