한국투자 계열사 교육중 도움드렸던 몇 가지 주제중에 인상 깊었던 것들에 대해 정리하는 글을 써보려 한다.
첫 번째로는, 엑셀 데이터 양식에 맞게 알잘딱으로 최대한의 범위를 가로로 인쇄하기!
이를 테면 다음과 같다. 이런 양식의 엑셀파일을 인쇄하기 버튼을 눌렀을 때,
아래의 그림처럼 나오게 만들기! 인 셈이다.
처음에는 마냥 쉬울줄 알았는데, 생각보다 쉽지 않았다. ㅠㅠ 다음은 문제 해결을 위한 과정을 정리 했다.
>> 목표
1. 폰트가 비율 축소가 안되는 선에서(10pt) 가능한 최대한의 셀을 가로 인쇄해야함
2. 셀의 크기를 줄일 때 데이터가 $$$$.$$ 와 같이 표기되면 안됨
3. 숨김처리 되어있는 셀의 경우 그대로 숨김처리 된채로 인쇄 되어야함>> 문제가 되었던 부분
1. 파이썬으로는 엑셀파일의 숨김셀이 연속으로 여러번 존재하는 경우에는 판단이 불가능
2. 특정 셀의 너비 혹은 문자가 몇 개 들었는지는 확인 할 수 있지만, 숫자 영어 특수 문자등
크기가 조금씩 다른 문자들로 이루어진 셀의 픽셀을 최대로 줄이는 것이 파이썬으로 구현이 어려움처음에는 최대한 파이썬 코드만 활용하여 해결하려 했지만, 위의 이유로 오직 파이썬으로는 구현이 어려웠고 비슷한 사례에 대해 구글링 해본 결과 vba로는 어렵지 않게 해결이 가능하다고 판단되어 vba를 사용하기로 결정했다.
물론 나는 vba를 전혀 할줄 몰랐지만 구글링과 chatGPT가 있기 때문에, 밑져야 본전이라는 생각으로 진행했고, 결과적으로는 파이썬으로 어떻게든 하려고 고민한 시간보다 vba로 구현한 시간이 더 빨랐다.
>> 구현
사용 환경이 vscode - jupyter이고, 사용하는 사람이 비개발자임을 고려하여 최대한 자세한 주석과 총 3개의 실행 단계를 나누었고, 다소 개발자스럽지 않게 만드려고 노력했다..
> readme
기본적으로 위 첨부 파일 2개가 엑셀 파일과 같은 위치에 존재한다면, 문제 없이 동작합니다.
코드를 나눠 놓은 대로, 한 셀씩 실행 하시는 것을 권장 드립니다.> python code
# 1번 필요한 함수들 정의
import win32com.client
import pythoncom
from openpyxl import load_workbook
from openpyxl.worksheet.page import PageMargins
from openpyxl.utils import column_index_from_string, get_column_letter
import os
import win32com.client as win32
def close_all_excel_instances(): #활성화된 모든 excel파일 종료하기
# COM 오류를 처리하기 위해 pythoncom을 초기화
pythoncom.CoInitialize()
try:
# Excel 인스턴스를 가져옴
excel = win32com.client.Dispatch("Excel.Application")
# 모든 워크북을 닫고, Excel 인스턴스를 종료
excel.Workbooks.Close()
excel.Quit()
except Exception as e:
print("Error:", e)
finally:
# COM 라이브러리 해제
pythoncom.CoUninitialize()
def setting_excel_print_option(workbook, area, sheet_name=None, margin = 0.1): # 현재시트에 대해 인쇄옵션 설정하기
if sheet_name == None:
sheet = workbook.active
else:
sheet = workbook[sheet_name]
sheet.print_area = area
# 마진 설정 (인치 단위)
sheet.page_margins = PageMargins(left=margin, right=margin, top=margin, bottom=margin, header=margin, footer=margin)
# 페이지 설정 - 가로 방향으로 인쇄
sheet.page_setup.orientation = sheet.ORIENTATION_LANDSCAPE
return sheet
def calculate_total_width(file_name, sheet, start_col, end_col, hidden = True) -> int: # 열 너비 합계 구하기
if hidden == True:
hidden_columns = find_hidden_columns(file_name)
else:
hidden_columns = []
total_width = 0
area_list = list(range(start_col, end_col + 1))
target_colunms = [x for x in area_list if x not in hidden_columns]
for col in target_colunms:
column_letter = get_column_letter(col)
column_width = sheet.column_dimensions[column_letter].width
total_width += column_width
return total_width,target_colunms,len(hidden_columns)
# VBA 모듈 실행하기
def run_vba_macro(file_name,module_name,macro_name,module_path,current_directory):
file_path = os.path.join(current_directory, file_name)
print(file_path)
try:
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(file_path)
# VBA 모듈을 추가
excel.VBE.ActiveVBProject.VBComponents.Import(module_path)
# VBA 코드 실행
excel.Run(f"{module_name}.{macro_name}")
workbook.Save()
workbook.Close()
except Exception as e:
print(f"파일 '{file_name}'에서 오류 발생: {str(e)}")
finally:
excel.Quit()
# 파일 경로 설정
def find_hidden_columns(file_name):
# 현재 폴더 경로 설정
current_directory = os.getcwd()
txt_file_path = current_directory + "\\hidden_columns.txt"
module_path = os.path.join(current_directory, "Module1.bas") # VBA 모듈 파일 경로 설정
run_vba_macro(file_name,"Module1","FindHiddenColumnsAndSave",module_path,current_directory)
try:
# 파일 열기
with open(txt_file_path, "r") as file:
# 파일 내용 읽기
hidden_columns_content = file.read()
# 알파벳 추출 및 숫자로 변환하여 리스트에 저장
hidden_columns = [ord(letter) - ord('A') + 1 for letter in hidden_columns_content if letter.isalpha()]
# 리스트 출력
return hidden_columns
except FileNotFoundError:
print(f"파일을 찾을 수 없습니다: {txt_file_path}")
except Exception as e:
print(f"파일을 읽는 중 오류 발생: {str(e)}")
# 현재 폴더에 있는 엑셀 시트 셀의 너비 줄이기
def reduce_cell_width_all_files():
# 현재 폴더 경로 설정
current_directory = os.getcwd()
module_path = os.path.join(current_directory, "Module1.bas") # VBA 모듈 파일 경로 설정
# 현재 폴더 내의 모든 엑셀 파일에 대해 반복
for filename in os.listdir(current_directory):
if filename.endswith(".xlsx"):
file_path = os.path.join(current_directory, filename)
print(file_path)
try:
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(file_path)
# VBA 모듈과 매크로 이름을 설정
module_name = "Module1"
macro_name = "AdjustAndReduceColumnWidthAllSheets"
# VBA 모듈을 추가
excel.VBE.ActiveVBProject.VBComponents.Import(module_path)
# VBA 코드 실행
excel.Run(f"{module_name}.{macro_name}")
workbook.Save()
workbook.Close()
except Exception as e:
print(f"파일 '{filename}'에서 오류 발생: {str(e)}")
finally:
excel.Quit()# 2번셀
reduce_cell_width_all_files() # 엑셀파일 너비 줄이기 실행 vba로 실행되므로 시간이 다소 소요됩니다.
# 3번셀
# 인쇄 범위 지정 (예: A1부터 O10까지 == A1:O10)
file_name = '123.xlsx'
area = 'A1:O10'
# 시작 열과 끝 열을 숫자로 변환
start_col_str, end_col_str = area.split(':')
start_col = column_index_from_string(start_col_str[0])
end_col = column_index_from_string(end_col_str[0])
workbook = load_workbook(file_name)
sheet = setting_excel_print_option(workbook,area)
total_width, target_columns, len_hidden_columns = calculate_total_width(file_name,sheet,start_col,end_col)
print("숨겨진 열을 제외한 총 너비:",total_width)
print("줄여야 할 컬럼 목록:",target_columns)
print("숨겨진 컬럼의 개수",len_hidden_columns)
# 총 너비가 133을 넘지 않도록 너비 조정
target_total_width = 133
if total_width > target_total_width:
print("한면에 인쇄하려는 시트들의 크기가 너무 큽니다.")
else:
if target_total_width - total_width > 0.2:
# 늘려야 할 너비 계산
total_width_to_distribute = target_total_width - total_width - 0.4
# 숨김처리된 컬럼을 제외하고 몇개로 나누어야 하는지
num_of_added = end_col - start_col + 1 - len_hidden_columns
pixel_increment = total_width_to_distribute / num_of_added
print("각 셀에 더해줄 픽셀의 값 :", pixel_increment)
for col in target_columns:
# 각 셀에 pixel_increment 만큼 너비를 증가시킨다
column_letter = get_column_letter(col)
current_width = sheet.column_dimensions[column_letter].width
# 너비 증가
new_width = current_width + pixel_increment
sheet.column_dimensions[column_letter].width = new_width
# 변경 사항 저장
workbook.save('123123_modified123.xlsx')
else:
print("인쇄하려는 시트들의 크기를 조정할 필요가 없습니다.")
workbook.close()> VBA code
Attribute VB_Name = "Module1"
Sub AdjustAndReduceColumnWidthAllSheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim col As Range
' 현재 워크북 참조
Set wb = ThisWorkbook
' 모든 워크시트에 대해 코드 실행
For Each ws In wb.Worksheets
' 모든 열에 대해 너비를 조절
For Each col In ws.Columns
' 숨김 처리된 열인지 확인
If col.Hidden = False Then
col.Cells.EntireColumn.AutoFit
col.ColumnWidth = col.ColumnWidth - 0.7
End If
Next col
Next ws
End Sub
Sub FindHiddenColumnsAndSave()
Dim ws As Worksheet
Dim c As Integer
Dim hiddenColumns As String
Dim filePath As String
Dim fileName As String
Set ws = ActiveSheet
hiddenColumns = ""
fileName = "hidden_columns.txt"
' 지정된 폴더 경로
filePath = "C:\Users\USER\Desktop\sparta\지주교육\" & fileName
' 숨겨진 열 찾기
For c = 1 To ws.Columns.Count
If ws.Columns(c).Hidden Then
If hiddenColumns <> "" Then hiddenColumns = hiddenColumns & ", "
hiddenColumns = hiddenColumns & Chr(64 + c)
End If
Next c
' 결과를 파일에 쓰기
Open filePath For Output As #1
Print #1, hiddenColumns
Close #1
End Sub업무 시간 중간에 편성된 교육이기도 하고, 어른들의 사정으로, 아쉽게도 교육 기간에는 마무리가 안되었다.. ㅠㅠ 그래도 어떻게든 도움을 드리고 싶어서 다음날인가 다다음날까지 열심히 만들어서 파일과 사용법도 노션으로 예쁘게 정리해서 문자로 보내드렸는데 지금도 잘 쓰실지는 모르겠다.
'dev > 업무자동화' 카테고리의 다른 글
| python 업무 자동화 - 동적 크롤링(selenium) 1 (0) | 2024.03.12 |
|---|