[파이썬] 엑셀 파일형식 가져와서 적용하기(openpyxl 패키지 활용)

1. 문제

pandas를 활용해서 엑셀의 숫자 데이터를 읽어 데이터베이스에 업로드하는데 표시형식이 적용 안되는 문제가 발생했다.
함수를 적용하거나 셀 서식에서 소수점을 적용한 게 반영이 안 돼서 openpyxl로 패키지를 바꿔서 적용하였다.

2. 해결

1. openpyxl 라이브러리를 활용해서 엑셀파일-시트 읽기
2. 표시형식을 적용하여 리스트에 저장한다. (for문)

• 연번이 있는 행만 가져온다.
• 표시형식과 값 가져온다.
• 표시형식 적용해서 리스트에 넣는다.

workbook = load_workbook(filename=path, data_only=True) #data_only=True 셀 값 가져오기
sheet = workbook[xl.sheet_names[sheet_i]]

# 11행부터 데이터와 표시 형식 가져오기
# 행:row_index, 열:col_index, 데이터:cell_value, 표시형식:cell_format
# 11행부터 데이터와 표시 형식 가져오기
res_cnt = 0
y = 0
for row_index, row in enumerate(sheet.iter_rows(min_row=11, values_only=True), start=11):
   xls_raw_data = []
   if row[0] is not None:  # 데이터가 있는 경우에만 가져오기
       for col_index, cell_value in enumerate(row, start=1):
           if 4 <= col_index <= 13:
               res_cnt += 1
               cell = sheet.cell(row=row_index, column=col_index)
               cell_format = cell.number_format
               
               #표시형식 조건
               if '0.0_' in cell_format :
                   cell_value = format(cell_value, ".1f")

               if '0.00' in cell_format :
                   cell_value = format(cell_value, ".2f")

               xls_raw_data.append(cell_value)

               # 가져온 데이터와 표시 형식 출력
               print(f"행 {row_index}, 열 {col_index}: 데이터: {cell_value}, 표시 형식: {cell_format}")


       xls_data = "||".join(map(str,xls_raw_data)) 

데이터를 제대로 출력하는지 확인하기 위해 표시형식 조건을 if문으로 설정하였다. 현재 코드에서는 소수점 1자리~2자리까지만 캐치하는데 이후 수정본에서는 모든 표시형식을 다 반영하게끔 수정하였다.

cell = sheet.cell(row=row_index, column=col_index)
cell_format = cell.number_format

# print(cell_format)

#표시형식 전부 적용
if (cell_format!="General"):
    tmp1 = cell_format.split(')')
    tmp2 = tmp1[0].split('.')

    if (len(tmp2)>1):
        tmp3 = tmp2[1]
        cell_value = format(cell_value, "." + str(len(tmp3) -1) + "f")