import os import openai import gradio as gr from openpyxl import load_workbook from openpyxl.styles import Alignment from openpyxl.drawing.image import Image as ExcelImage from io import BytesIO import tempfile import re ###################### # (필수) OpenAI API 설정 ###################### openai.api_key = os.getenv("OPENAI_API_KEY").strip() ###################### # [api자료]에서 제공된 함수 (수정 금지) ###################### def call_api(content, system_message, max_tokens, temperature, top_p): response = openai.ChatCompletion.create( model="gpt-4o-mini", messages=[ {"role": "system", "content": system_message}, {"role": "user", "content": content}, ], max_tokens=max_tokens, temperature=temperature, top_p=top_p, ) return response.choices[0].message["content"] ###################### # 1) 애로사항/목표 분석 ###################### def analyze_texts(difficulties, goals): preview_difficulties = call_api( content=difficulties, system_message=( "사용자가 입력한 애로사항을 최소 4줄 이상으로 구체적으로 작성해줘. " "문장을 중간에 끊지 말고, 불필요한 나열(하나, 둘 등) 없이 '~습니다.'로 " "자연스럽게 끝내줘." ), max_tokens=400, temperature=0.7, top_p=0.9, ) preview_goals = call_api( content=goals, system_message=( "사용자가 입력한 컨설팅 목표를 최소 4줄 이상으로 구체적으로 작성해줘. " "문장을 중간에 끊지 말고, 불필요한 나열(하나, 둘 등) 없이 '~습니다.'로 " "자연스럽게 끝내줘." ), max_tokens=400, temperature=0.7, top_p=0.9, ) return preview_difficulties, preview_goals ###################### # 2) SWOT 분석(조금 더 길게) ###################### def analyze_swot(strengths, weaknesses, opportunities, threats): system_message = ( "당신은 swot 분석 전문가입니다. " "입력된 강점, 약점, 기회, 위협을 각각 6~7줄 정도로 조금 더 길고 자세히 요약해 주세요. " "출력은 반드시 아래 예시 형태처럼 총 4줄이며, 각각은 '강점:', '약점:', '기회:', '위협:'으로 시작해 주세요:\n\n" "강점: ~~~\n" "약점: ~~~\n" "기회: ~~~\n" "위협: ~~~\n\n" "항상 '강점:', '약점:', '기회:', '위협:'으로 시작하는 4줄로만 응답해 주세요." ) content = f""" 강점: {strengths} 약점: {weaknesses} 기회: {opportunities} 위협: {threats} """ return call_api( content=content, system_message=system_message, max_tokens=500, temperature=0.7, top_p=1.0 ) ###################### # 3) 재무 분석(4줄) ###################### def analyze_financials( avg_monthly_sales, rent, labor_cost, management_cost, utilities, current_interest, other_cost, cost_ratio ): system_message = ( "당신은 세무사(회계사)입니다. 아래 입력값을 바탕으로 계산한 감가상각비, 순이익율, " "월순수익, 손익분기점을 '제목: 수치' 형태로만 알려주세요. " "추가 설명 없이 항목명: 값 형태만." ) content = f""" 입력: - 월평균매출액: {avg_monthly_sales} - 임차료: {rent} - 인건비: {labor_cost} - 관리비: {management_cost} - 수도광열비: {utilities} - 지금이자: {current_interest} - 기타비용: {other_cost} - 원가율: {cost_ratio} 결과 항목: 1) 감가상각비 2) 순이익율 3) 월순수익 4) 손익분기점 """ return call_api( content=content, system_message=system_message, max_tokens=500, temperature=0.7, top_p=1.0 ) ###################### # 4) 상권 분석(1줄) ###################### def analyze_market(region, industry): system_message = ( "당신은 상권분석 전문가입니다. 지역과 업종에 대해 1km 이내 상권 요약. " "사진(시각자료) 언급 포함, 6줄 이내." ) content = f""" 지역: {region} 업종: {industry} """ return call_api( content=content, system_message=system_message, max_tokens=300, temperature=0.7, top_p=1.0 ) ###################### # 최종: 한 번에 EXE.xlsx 작성 ###################### def run_all_in_one( # (A) 상단 8개 consulting_company_rep, consulting_address, consulting_field, client_name, consulting_contact, consulting_date_time, consulting_round, consulting_region, # (B) 애로사항/목표 difficulties_input, goals_input, # (C) SWOT strengths_input, weaknesses_input, opportunities_input, threats_input, # (D) 재무분석(8개) avg_monthly_sales, rent, labor_cost, management_cost, utilities, current_interest, other_cost, cost_ratio, # (E) 상권분석 region_input, industry_input, # (F) 사진 photo1, photo2 ): """ 기존 로직 + 사용자 입력(8개)을 E87:F87:G87 ~ E94:F94:G94 등 병합된 셀에 넣으려 하면 MergedCell 에러 발생 => "왼쪽 상단(E87)"만 기록하도록 수정 => 나머지 로직(애로사항/목표, SWOT 4줄, 재무분석 4줄, 상권분석, 사진, 상단 8개 항목)은 건드리지 않음 """ ########## 1) GPT(애로사항/목표) preview_difficulties, preview_goals = analyze_texts(difficulties_input, goals_input) ########## 2) GPT(SWOT) swot_result = analyze_swot(strengths_input, weaknesses_input, opportunities_input, threats_input) swot_lines = swot_result.split("\n") strength_line = next((line for line in swot_lines if line.startswith("강점:")), "") weakness_line = next((line for line in swot_lines if line.startswith("약점:")), "") opportunity_line = next((line for line in swot_lines if line.startswith("기회:")), "") threat_line = next((line for line in swot_lines if line.startswith("위협:")), "") ########## 3) GPT(재무) fin_result = analyze_financials( avg_monthly_sales, rent, labor_cost, management_cost, utilities, current_interest, other_cost, cost_ratio ) fin_lines = fin_result.split("\n") ########## 4) GPT(상권) market_result = analyze_market(region_input, industry_input) ########## EXE.xlsx wb = load_workbook("EXE.xlsx") ws = wb.active # (A) 상단 8개 ws.merge_cells("C4:E4") ws["C4"] = consulting_company_rep ws["C4"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("C5:E5") ws["C5"] = consulting_address ws["C5"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("C6:E6") ws["C6"] = consulting_field ws["C6"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("C7:E7") ws["C7"] = client_name ws["C7"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("G4:H4") ws["G4"] = consulting_contact ws["G4"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("G5:H5") ws["G5"] = consulting_date_time ws["G5"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("G6:H6") ws["G6"] = consulting_round ws["G6"].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells("G7:H7") ws["G7"] = consulting_region ws["G7"].alignment = Alignment(horizontal="center", vertical="center") # (B) 애로사항/목표 ws["D11"] = preview_difficulties ws["D22"] = preview_goals ws["D11"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws["D22"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) # (F) 사진 if photo1 is not None: img1_bytes = BytesIO() photo1.save(img1_bytes, format="PNG") img1_bytes.seek(0) img_for_excel_1 = ExcelImage(img1_bytes) img_for_excel_1.width = int(37.79 * 7) # 가로 7cm img_for_excel_1.height = int(37.79 * 5) ws.add_image(img_for_excel_1, "B117") if photo2 is not None: img2_bytes = BytesIO() photo2.save(img2_bytes, format="PNG") img2_bytes.seek(0) img_for_excel_2 = ExcelImage(img2_bytes) img_for_excel_2.width = int(37.79 * 7) # 가로 7cm img_for_excel_2.height = int(37.79 * 5) ws.add_image(img_for_excel_2, "F117") # (C) SWOT 결과(4칸) ws.merge_cells("D45:H45") ws["D45"] = strength_line ws["D45"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.merge_cells("D54:H54") ws["D54"] = weakness_line ws["D54"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.merge_cells("D63:H63") ws["D63"] = opportunity_line ws["D63"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.merge_cells("D72:H72") ws["D72"] = threat_line ws["D72"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ######################################################################## # parse_financial_line: # - 라벨 제거 (콜론(:) 앞 부분은 무시) # - 숫자만 추출(문자·기호 등 전부 제거) # - 3자리마다 점(.) 삽입 ######################################################################## def parse_financial_line(line: str) -> str: # 예) "감가상각비: 1234567" -> "1.234.567" # 예) "순이익율: 10%" -> "10" (문자·기호 등 전부 제거하므로 %도 버림) # 1) 콜론(:) 뒤의 내용 추출 parts = line.split(":") if len(parts) < 2: return "" # 변환 불가 시 공백 value_str = parts[1].strip() # 예: "1234567", "10%", "1,234,567원" 등 # 2) 숫자만 추출 (문자·기호 제거) digits_str = re.sub(r"[^0-9]", "", value_str) # "1234567", "10" 등 if not digits_str: return "" # 숫자가 전혀 없으면 공백 # 3) 3자리마다 점(.) 삽입 reversed_digits = digits_str[::-1] grouped = ".".join(reversed_digits[i:i+3] for i in range(0, len(reversed_digits), 3)) final_num = grouped[::-1] # 정방향 return final_num # (D) 재무분석 결과(4줄 -> E87:E90) ws.merge_cells("E87:G87") line0 = fin_lines[0] if len(fin_lines) > 0 else "" ws["E87"] = parse_financial_line(line0) ws["E87"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.merge_cells("E88:G88") line1 = fin_lines[1] if len(fin_lines) > 1 else "" ws["E88"] = parse_financial_line(line1) ws["E88"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.merge_cells("E89:G89") line2 = fin_lines[2] if len(fin_lines) > 2 else "" ws["E89"] = parse_financial_line(line2) ws["E89"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.merge_cells("E90:G90") line3 = fin_lines[3] if len(fin_lines) > 3 else "" ws["E90"] = parse_financial_line(line3) ws["E90"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) # (E) 상권분석 결과(1줄 -> D101) ws.merge_cells("D101:H101") ws["D101"] = market_result ws["D101"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ################################################################ # (추가) 병합된 셀의 '왼쪽 상단'에만 value 대입 (MergedCell 에러 방지) # # 예: E87:F87:G87로 병합된 상태면, # - E87 = '대표 셀'(실제 value 가능) # - F87, G87 = MergedCell (read-only) # # 아래는 예시로 E87~E94까지 8줄 생성한다고 가정 # => '왼쪽 상단'만 기록 ################################################################ # 사용자 입력 8개 항목 (예: 월평균매출액, 임차료 등) input_labels = [ "월평균매출액", "임차료", "인건비", "관리비", "수도광열비", "지금이자", "기타비용", "원가율" ] input_values = [ avg_monthly_sales, rent, labor_cost, management_cost, utilities, current_interest, other_cost, cost_ratio ] base_row = 87 for i, (label, value) in enumerate(zip(input_labels, input_values)): row_idx = base_row + i # 87~94 cell_master = f"E{row_idx}" ws[cell_master] = f"{label}: {value}" ws[cell_master].alignment = Alignment( horizontal="center", vertical="center", wrap_text=True ) # 예: E95 => 감가상각비(예시) ws["E95"] = "감가상각비(예시)" ws["E95"].alignment = Alignment( horizontal="center", vertical="center", wrap_text=True ) # E96~E98 => 순이익율, 월순수익, 손익분기점 profit_line = next((l for l in fin_lines if "순이익율" in l), "") profit_val = profit_line.split(":")[1].strip() if profit_line else "" monthly_line = next((l for l in fin_lines if "월순수익" in l), "") monthly_val = monthly_line.split(":")[1].strip() if monthly_line else "" bep_line = next((l for l in fin_lines if "손익분기점" in l), "") bep_val = bep_line.split(":")[1].strip() if bep_line else "" ws["E96"] = f"순이익율: {profit_val}" ws["E97"] = f"월순수익: {monthly_val}" ws["E98"] = f"손익분기점: {bep_val}" for r_ in [96, 97, 98]: cell_e = f"E{r_}" ws[cell_e].alignment = Alignment( horizontal="center", vertical="center", wrap_text=True ) ################################################################ # 저장 output = BytesIO() wb.save(output) output.seek(0) with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp: tmp.write(output.getvalue()) tmp.flush() temp_file_path = tmp.name final_path = os.path.join(os.path.dirname(temp_file_path), "EXE.xlsx") os.rename(temp_file_path, final_path) return final_path ############################ # UI (하나의 버튼만 남김) ############################ def main(): with gr.Blocks() as demo: gr.Markdown("## 모든 입력 후 -> Excel 다운로드(하나의 버튼)") # (A) 상단 8개 with gr.Row(): consulting_company_rep = gr.Textbox(label="컨설팅업체명 및 대표") consulting_address = gr.Textbox(label="컨설팅 주소") consulting_field = gr.Textbox(label="컨설팅 분야") client_name = gr.Textbox(label="성명") with gr.Row(): consulting_contact = gr.Textbox(label="컨설팅 업체 연락처") consulting_date_time = gr.Textbox(label="컨설팅 수행일자 및 시간") consulting_round = gr.Textbox(label="진행차수") consulting_region = gr.Textbox(label="지역") gr.Markdown("---") # (B) 애로사항/목표 difficulties_input = gr.Textbox(label="애로사항 입력", lines=3) goals_input = gr.Textbox(label="컨설팅 목표 입력", lines=3) gr.Markdown("---") # (C) SWOT strengths_input = gr.Textbox(label="강점") weaknesses_input = gr.Textbox(label="약점") opportunities_input = gr.Textbox(label="기회") threats_input = gr.Textbox(label="위협") gr.Markdown("---") # (D) 재무(8개) with gr.Row(): avg_monthly_sales_input = gr.Textbox(label="월평균매출액") rent_input = gr.Textbox(label="임차료") labor_cost_input = gr.Textbox(label="인건비") management_cost_input = gr.Textbox(label="관리비") with gr.Row(): utilities_input = gr.Textbox(label="수도광열비") current_interest_input = gr.Textbox(label="지금이자") other_cost_input = gr.Textbox(label="기타비용") cost_ratio_input = gr.Textbox(label="원가율") gr.Markdown("---") # (E) 상권 region_input = gr.Textbox(label="지역") industry_input = gr.Textbox(label="업종") gr.Markdown("---") # (F) 사진 with gr.Row(): photo_input_1 = gr.Image(label="수진업체사진1", type="pil") photo_input_2 = gr.Image(label="수진업체사진2", type="pil") gr.Markdown("---") # 버튼 all_in_one_button = gr.Button("모두 입력 후, Excel 다운로드") all_in_one_file = gr.File(label="다운로드(클릭 후 저장)") # 실행 all_in_one_button.click( fn=run_all_in_one, inputs=[ # (A) consulting_company_rep, consulting_address, consulting_field, client_name, consulting_contact, consulting_date_time, consulting_round, consulting_region, # (B) difficulties_input, goals_input, # (C) strengths_input, weaknesses_input, opportunities_input, threats_input, # (D) avg_monthly_sales_input, rent_input, labor_cost_input, management_cost_input, utilities_input, current_interest_input, other_cost_input, cost_ratio_input, # (E) region_input, industry_input, # (F) photo_input_1, photo_input_2, ], outputs=[all_in_one_file] ) demo.launch() if __name__ == "__main__": main()