YA-LIN/feedback_mapping

1import os2import gradio as gr3import pandas as pd4import datetime5import shutil6from dateutil.relativedelta import relativedelta7from openpyxl import load_workbook8from openpyxl.styles import Font9from openpyxl.utils.dataframe import dataframe_to_rows10 11def process_excel(feedback_file, tlw_file, hola_ec_file, hola_store_file):12    try:13        # 讀 feedback14        feedback_df = pd.read_excel(feedback_file)15 16        # 三個 channel file17        channel_files = {18            "TLW 官網": tlw_file,19            "HOLA 官網": hola_ec_file,20            "HOLA 門市": hola_store_file21        }22 23        # 設定輸出檔名（-1 個月）24        now = datetime.datetime.now()25        last_month = now - relativedelta(months=1)26        filename = f"回饋資料整合_{last_month.year}_{last_month.month:02d}.xlsm"27        output_path = filename28 29        # 套用 VBA 模板（⚠️ 請確保 template.xlsm 放在同一個資料夾）30        template_path = os.path.join(os.path.dirname(__file__), "template.xlsm")31        shutil.copyfile(template_path, output_path)32 33        wb = load_workbook(output_path, keep_vba=True)34        if "工作表1" in wb.sheetnames:35            wb.remove(wb["工作表1"])36 37        font = Font(name="Microsoft JhengHei")38        sheet_written = False39 40        # 根據來源對應 channel 檔案41        for channel_name, file_obj in channel_files.items():42            try:43                feedback_sub = feedback_df[feedback_df["來源"].str.contains(channel_name, na=False)]44                if feedback_sub.empty:45                    continue46 47                # 讀 channel file，只留 A:D，確保 E 欄之後是空的48                channel_df = pd.read_excel(file_obj, usecols="A:D")49                channel_df.columns = ['mem_id', '發票號碼', 'SKU', '商品名稱']50 51                # left join52                merged_df = pd.merge(53                    feedback_sub,54                    channel_df[['發票號碼', 'SKU', '商品名稱']],55                    on='發票號碼',56                    how='left'57                )58 59                merged_df['SKU'] = merged_df['SKU'].fillna('-')60                merged_df['商品名稱'] = merged_df['商品名稱'].fillna('-')61 62                if not merged_df.empty:63                    merged_df = merged_df[['category', 'subcategory', 'text', '發票號碼', 'SKU', '商品名稱', '來源']]64                    merged_df = merged_df.sort_values(by=['category', 'subcategory', '發票號碼'])65 66                    ws = wb.create_sheet(channel_name)67                    for r in dataframe_to_rows(merged_df, index=False, header=True):68                        ws.append(r)69                    for row in ws.iter_rows():70                        for cell in row:71                            cell.font = font72 73                    sheet_written = True74 75            except Exception as e:76                print(f"處理 {channel_name} 檔案時發生錯誤：{e}")77 78        # 收集來源不屬於三個既定來源的 feedback79        unmatched_all = feedback_df[80            ~feedback_df["來源"].str.contains("|".join(channel_files.keys()), na=False)81        ]82        if not unmatched_all.empty:83            ws = wb.create_sheet("無資料對應")84            for r in dataframe_to_rows(unmatched_all, index=False, header=True):85                ws.append(r)86            for row in ws.iter_rows():87                for cell in row:88                    cell.font = font89        elif not sheet_written:  # 沒有任何 match，也沒有 unmatched90            ws = wb.create_sheet("無資料對應")91            ws.append(['category', 'subcategory', 'text', '發票號碼', 'SKU', '商品名稱', '來源'])92            for cell in ws[1]:93                cell.font = font94 95        wb.save(output_path)96        return output_path97 98    except Exception as e:99        return f"產生 Excel 時發生錯誤：{e}"100 101 102# Gradio 介面103demo = gr.Interface(104    fn=process_excel,105    inputs=[106        gr.File(label="回饋資料檔案（feedback）", file_types=[".xlsx"]),107        gr.File(label="TLW 官網商品檔案", file_types=[".xlsx"]),108        gr.File(label="HOLA 官網商品檔案", file_types=[".xlsx"]),109        gr.File(label="HOLA 門市商品檔案", file_types=[".xlsx"]),110    ],111    outputs=gr.File(label="下載整合後的 Excel（含 VBA）"),112    title="📊 回饋整合工具",113    description="上傳回饋與商品檔案，系統將自動對應並產出含巨集的 Excel（開啟即整理）"114)115 116if __name__ == "__main__":117    demo.launch()  # Hugging Face 上不用 share=True118