import pandas as pd
import os

all_data = []
folder_path = './sales_reports'

for file in os.listdir(folder_path):
    if file.endswith('.xlsx'):
        df = pd.read_excel(os.path.join(folder_path, file))
        all_data.append(df)

result = pd.concat(all_data, ignore_index=True)
result.to_excel('weekly_summary.xlsx', index=False)

from openpyxl import load_workbook

def find_data_start_row(ws, header_keyword="日期"):
    """
    在工作表中查找包含指定關(guān)鍵詞的表頭行
    返回?cái)?shù)據(jù)起始行號(hào)（即表頭行號(hào)+1）
    """
    # 通常數(shù)據(jù)不會(huì)超過(guò)前20行開(kāi)始
    for row in ws.iter_rows(min_row=1, max_row=20, max_col=10):
        for cell in row:
            if cell.value and header_keyword in str(cell.value):
                # 找到關(guān)鍵詞，返回下一行作為數(shù)據(jù)開(kāi)始
                return cell.row + 1
    # 如果沒(méi)找到，默認(rèn)從第2行開(kāi)始（假設(shè)第一行是表頭）
    return 2

def get_column_mapping(ws, header_row_idx):
    """
    根據(jù)表頭行，建立列索引到標(biāo)準(zhǔn)列名的映射。
    例如：{1: '日期', 2: '銷(xiāo)售額', ...}
    """
    # 標(biāo)準(zhǔn)列名（總表使用的）
    standard_headers = ['日期', '渠道', '商品ID', '商品名稱(chēng)', '銷(xiāo)售額', '訂單量']
    mapping = {}
    
    header_row = ws[header_row_idx]
    
    for cell in header_row:
        if cell.value:
            header_text = str(cell.value).strip()
            # 模糊匹配：如果標(biāo)準(zhǔn)列名是當(dāng)前表頭的一部分，或反之，則匹配
            for std_header in standard_headers:
                if std_header in header_text or header_text in std_header:
                    mapping[cell.column] = std_header # cell.column是列索引，如1,2,3
                    break # 匹配到一個(gè)就跳出內(nèi)層循環(huán)
    return mapping

def read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values):
    """
    從指定行開(kāi)始讀取數(shù)據(jù)，應(yīng)用列映射，并處理合并單元格值。
    返回一個(gè)列表，每個(gè)元素是一行數(shù)據(jù)的字典。
    """
    data = []
    # 獲取最大行和最大列（有數(shù)據(jù)的區(qū)域）
    max_row = ws.max_row
    max_column = ws.max_column
    
    for row in range(data_start_row, max_row + 1):
        row_data = {v: None for v in col_mapping.values()} # 用標(biāo)準(zhǔn)列名初始化字典
        for col_idx, std_header in col_mapping.items():
            cell = ws.cell(row=row, column=col_idx)
            cell_value = cell.value
            
            # 關(guān)鍵！處理合并單元格：如果當(dāng)前單元格在合并范圍內(nèi)，則取合并區(qū)域的值
            for merged_range in merged_cell_values:
                if cell.coordinate in merged_range:
                    cell_value = merged_cell_values[merged_range]
                    break
            
            row_data[std_header] = cell_value
        
        # 如果一行數(shù)據(jù)全為空，則跳過(guò)（可能是末尾的空行）
        if any(row_data.values()):
            # 補(bǔ)充渠道信息（可以從文件名解析，這里簡(jiǎn)化處理）
            row_data['渠道'] = os.path.basename(ws.parent.filepath).split('_')[0]
            data.append(row_data)
    return data

from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

def write_summary(data_list, output_path='周報(bào)匯總.xlsx'):
    """將整理好的數(shù)據(jù)列表寫(xiě)入新的Excel文件"""
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "銷(xiāo)售周報(bào)匯總"
    
    # 定義標(biāo)準(zhǔn)表頭
    headers = ['日期', '渠道', '商品ID', '商品名稱(chēng)', '銷(xiāo)售額', '訂單量']
    
    # 設(shè)置表頭樣式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    alignment = Alignment(horizontal="center", vertical="center")
    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                         top=Side(style='thin'), bottom=Side(style='thin'))
    
    # 寫(xiě)入表頭
    for col_idx, header in enumerate(headers, start=1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = alignment
        cell.border = thin_border
    
    # 寫(xiě)入數(shù)據(jù)
    row_idx = 2
    for row_data in data_list:
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=row_idx, column=col_idx, value=row_data.get(header))
            cell.border = thin_border
            # 給銷(xiāo)售額和訂單量列設(shè)置數(shù)字格式
            if header in ['銷(xiāo)售額', '訂單量'] and isinstance(cell.value, (int, float)):
                if header == '銷(xiāo)售額':
                    cell.number_format = '#,##0.00' # 千位分隔符，兩位小數(shù)
                else:
                    cell.number_format = '#,##0'
        row_idx += 1
    
    # 自動(dòng)調(diào)整列寬（近似）
    for col in ws.columns:
        max_length = 0
        column_letter = get_column_letter(col[0].column)
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = min(max_length + 2, 50) # 設(shè)置一個(gè)最大寬度
        ws.column_dimensions[column_letter].width = adjusted_width
    
    wb.save(output_path)
    print(f"匯總完成！文件已保存至：{output_path}")

# 在主函數(shù)中，讀取所有文件數(shù)據(jù)后
all_data = []
for file_path in excel_files:
    wb = load_workbook(file_path, data_only=True) # data_only=True只讀值，不讀公式
    ws = wb.active
    ws.filepath = file_path # 給ws對(duì)象加個(gè)屬性，方便后面取文件名
    
    # 找到表頭行和數(shù)據(jù)起始行
    header_row_idx = find_data_start_row(ws) - 1
    data_start_row = header_row_idx + 1
    
    # 獲取列映射
    col_mapping = get_column_mapping(ws, header_row_idx)
    if not col_mapping:
        print(f"警告：在文件 {file_path} 中未找到匹配的表頭，跳過(guò)。")
        continue
    
    # 預(yù)處理合并單元格的值
    merged_cell_values = {}
    for merged_range in ws.merged_cells.ranges:
        # 獲取合并區(qū)域左上角單元格的值
        top_left_cell = ws[merged_range.min_row][merged_range.min_col - 1] # 注意索引轉(zhuǎn)換
        merged_cell_values[merged_range] = top_left_cell.value
    
    # 讀取數(shù)據(jù)
    sheet_data = read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values)
    all_data.extend(sheet_data)

# 寫(xiě)入?yún)R總文件
write_summary(all_data)

# 在write_summary函數(shù)內(nèi)部，數(shù)據(jù)寫(xiě)入后，可以添加匯總行
# ... 數(shù)據(jù)寫(xiě)入循環(huán)之后 ...
summary_row = row_idx + 1
ws.cell(row=summary_row, column=4, value="渠道總計(jì)").font = Font(bold=True)
# 使用公式計(jì)算每個(gè)渠道的銷(xiāo)售額總和（假設(shè)渠道名列是B，銷(xiāo)售額列是E）
# 這里簡(jiǎn)化，實(shí)際可以按渠道分組計(jì)算
ws.cell(row=summary_row, column=5, value=f"=SUMIF(B2:B{row_idx}, B{summary_row}, E2:E{row_idx})")

"""
Excel銷(xiāo)售周報(bào)自動(dòng)化匯總腳本
作者：實(shí)戰(zhàn)踩坑記錄
功能：自動(dòng)讀取多個(gè)渠道銷(xiāo)售Excel，統(tǒng)一格式后匯總至一個(gè)文件。
"""

import os
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

def find_data_start_row(ws, header_keyword="日期"):
    """定位數(shù)據(jù)起始行"""
    for row in ws.iter_rows(min_row=1, max_row=20, max_col=10):
        for cell in row:
            if cell.value and header_keyword in str(cell.value):
                return cell.row + 1
    return 2

def get_column_mapping(ws, header_row_idx):
    """建立列索引到標(biāo)準(zhǔn)列名的映射"""
    standard_headers = ['日期', '渠道', '商品ID', '商品名稱(chēng)', '銷(xiāo)售額', '訂單量']
    mapping = {}
    header_row = ws[header_row_idx]
    for cell in header_row:
        if cell.value:
            header_text = str(cell.value).strip()
            for std_header in standard_headers:
                if std_header in header_text or header_text in std_header:
                    mapping[cell.column] = std_header
                    break
    return mapping

def read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values):
    """讀取單文件數(shù)據(jù)，處理合并單元格"""
    data = []
    max_row = ws.max_row
    for row in range(data_start_row, max_row + 1):
        row_data = {v: None for v in col_mapping.values()}
        for col_idx, std_header in col_mapping.items():
            cell = ws.cell(row=row, column=col_idx)
            cell_value = cell.value
            # 處理合并單元格
            for merged_range, merged_value in merged_cell_values.items():
                if cell.coordinate in merged_range:
                    cell_value = merged_value
                    break
            row_data[std_header] = cell_value
        if any(row_data.values()):
            # 簡(jiǎn)單從文件名提取渠道名，可根據(jù)實(shí)際情況修改
            filename = os.path.basename(ws.parent.filepath)
            channel = filename.split('_')[0] if '_' in filename else filename.replace('.xlsx', '')
            row_data['渠道'] = channel
            data.append(row_data)
    return data

def write_summary(data_list, output_path='周報(bào)匯總.xlsx'):
    """寫(xiě)入?yún)R總文件并添加格式"""
    wb = Workbook()
    ws = wb.active
    ws.title = "銷(xiāo)售周報(bào)匯總"
    
    headers = ['日期', '渠道', '商品ID', '商品名稱(chēng)', '銷(xiāo)售額', '訂單量']
    # 樣式定義
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    align_center = Alignment(horizontal="center", vertical="center")
    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                         top=Side(style='thin'), bottom=Side(style='thin'))
    
    # 寫(xiě)表頭
    for col_idx, header in enumerate(headers, start=1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = align_center
        cell.border = thin_border
    
    # 寫(xiě)數(shù)據(jù)
    row_idx = 2
    for row_data in data_list:
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=row_idx, column=col_idx, value=row_data.get(header))
            cell.border = thin_border
            if header in ['銷(xiāo)售額', '訂單量'] and isinstance(cell.value, (int, float)):
                cell.number_format = '#,##0.00' if header == '銷(xiāo)售額' else '#,##0'
        row_idx += 1
    
    # 調(diào)整列寬
    for col in ws.columns:
        max_len = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            try:
                cell_len = len(str(cell.value))
                if cell_len > max_len:
                    max_len = cell_len
            except:
                pass
        adjusted_width = min(max_len + 2, 50)
        ws.column_dimensions[col_letter].width = adjusted_width
    
    wb.save(output_path)
    print(f"? 匯總完成！文件已保存至：{output_path}")

def main():
    # 配置路徑
    reports_folder = './sales_reports'  # 存放所有渠道Excel文件的文件夾
    output_file = './周報(bào)匯總.xlsx'
    
    if not os.path.exists(reports_folder):
        print(f"錯(cuò)誤：文件夾 '{reports_folder}' 不存在。")
        return
    
    all_data = []
    excel_files = [os.path.join(reports_folder, f) for f in os.listdir(reports_folder) 
                   if f.endswith(('.xlsx', '.xlsm'))]
    
    if not excel_files:
        print("未找到Excel文件。")
        return
    
    for file_path in excel_files:
        print(f"正在處理：{os.path.basename(file_path)}")
        try:
            wb = load_workbook(file_path, data_only=True)
            ws = wb.active
            ws.parent.filepath = file_path  # 臨時(shí)存儲(chǔ)路徑
            
            header_row_idx = find_data_start_row(ws) - 1
            data_start_row = header_row_idx + 1
            
            col_mapping = get_column_mapping(ws, header_row_idx)
            if not col_mapping:
                print(f"  ??  跳過(guò)，未識(shí)別到表頭。")
                continue
            
            # 處理合并單元格
            merged_values = {}
            for merged_range in ws.merged_cells.ranges:
                top_left_cell = ws[merged_range.min_row][merged_range.min_col - 1]
                merged_values[merged_range] = top_left_cell.value
            
            sheet_data = read_sheet_data(ws, data_start_row, col_mapping, merged_values)
            all_data.extend(sheet_data)
            print(f"  ? 讀取到 {len(sheet_data)} 行數(shù)據(jù)。")
            
        except Exception as e:
            print(f"  ? 處理文件時(shí)出錯(cuò)：{e}")
    
    if all_data:
        write_summary(all_data, output_file)
        print(f"總計(jì)處理 {len(excel_files)} 個(gè)文件，合并 {len(all_data)} 行數(shù)據(jù)。")
    else:
        print("未讀取到任何有效數(shù)據(jù)。")

if __name__ == "__main__":
    main()