from flask import Flask, request, send_file
import pandas as pd
import re
from collections import Counter
from werkzeug.utils import secure_filename
import os

app = Flask(__name__)
UPLOAD_FOLDER = 'uploads'
OUTPUT_FOLDER = 'outputs'

os.makedirs(UPLOAD_FOLDER, exist_ok=True)
os.makedirs(OUTPUT_FOLDER, exist_ok=True)

@app.route('/')
def index():
    return '''
    <h1>엑셀 키워드 추출기</h1>
    <form action="/process" method="post" enctype="multipart/form-data">
        <input type="file" name="file" accept=".xlsx"/>
        <button type="submit">업로드 및 처리</button>
    </form>
    '''

@app.route('/process', methods=['POST'])
def process():
    file = request.files['file']
    filename = secure_filename(file.filename)
    filepath = os.path.join(UPLOAD_FOLDER, filename)
    file.save(filepath)

    # 엑셀 데이터 읽기
    df = pd.read_excel(filepath, engine='openpyxl')

    # D4:D열의 데이터 가져오기
    data = df.iloc[3:, 3].dropna().astype(str)

    # 키워드 처리
    keywords = []
    for text in data:
        text = re.sub(r'[^\w\s]', '', text)  # 특수문자 제거
        keywords.extend(text.split())

    # 키워드 빈도 계산
    keyword_counts = Counter(keywords)
    sorted_keywords = sorted(keyword_counts.items(), key=lambda x: x[1], reverse=True)

    # 결과 데이터프레임 작성
    result_df = pd.DataFrame(sorted_keywords, columns=['키워드', '빈도'])

    # 결과 엑셀로 저장
    output_path = os.path.join(OUTPUT_FOLDER, 'result.xlsx')
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        result_df.to_excel(writer, index=False, startrow=4, startcol=0, header=False)
        sheet = writer.sheets['Sheet1']
        sheet.cell(row=4, column=1).value = "A5셀명"
        sheet.cell(row=4, column=2).value = "B5셀명"

    return send_file(output_path, as_attachment=True, download_name='result.xlsx')

if __name__ == '__main__':
    app.run(debug=True)