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)鍵詞的表頭行
    返回數(shù)據(jù)起始行號（即表頭行號+1）
    """
    # 通常數(shù)據(jù)不會超過前20行開始
    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ù)開始
                return cell.row + 1
    # 如果沒找到，默認從第2行開始（假設(shè)第一行是表頭）
    return 2

def get_column_mapping(ws, header_row_idx):
    """
    根據(jù)表頭行，建立列索引到標(biāo)準(zhǔn)列名的映射。
    例如：{1: '日期', 2: '銷售額', ...}
    """
    # 標(biāo)準(zhǔn)列名（總表使用的）
    standard_headers = ['日期', '渠道', '商品ID', '商品名稱', '銷售額', '訂單量']
    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 # 匹配到一個就跳出內(nèi)層循環(huán)
    return mapping

def read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values):
    """
    從指定行開始讀取數(shù)據(jù)，應(yīng)用列映射，并處理合并單元格值。
    返回一個列表，每個元素是一行數(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ù)全為空，則跳過（可能是末尾的空行）
        if any(row_data.values()):
            # 補充渠道信息（可以從文件名解析，這里簡化處理）
            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='周報匯總.xlsx'):
    """將整理好的數(shù)據(jù)列表寫入新的Excel文件"""
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "銷售周報匯總"
    
    # 定義標(biāo)準(zhǔn)表頭
    headers = ['日期', '渠道', '商品ID', '商品名稱', '銷售額', '訂單量']
    
    # 設(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'))
    
    # 寫入表頭
    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
    
    # 寫入數(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
            # 給銷售額和訂單量列設(shè)置數(shù)字格式
            if header in ['銷售額', '訂單量'] and isinstance(cell.value, (int, float)):
                if header == '銷售額':
                    cell.number_format = '#,##0.00' # 千位分隔符，兩位小數(shù)
                else:
                    cell.number_format = '#,##0'
        row_idx += 1
    
    # 自動調(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è)置一個最大寬度
        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對象加個屬性，方便后面取文件名
    
    # 找到表頭行和數(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} 中未找到匹配的表頭，跳過。")
        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)

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

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

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

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', '商品名稱', '銷售額', '訂單量']
    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()):
            # 簡單從文件名提取渠道名，可根據(jù)實際情況修改
            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='周報匯總.xlsx'):
    """寫入?yún)R總文件并添加格式"""
    wb = Workbook()
    ws = wb.active
    ws.title = "銷售周報匯總"
    
    headers = ['日期', '渠道', '商品ID', '商品名稱', '銷售額', '訂單量']
    # 樣式定義
    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'))
    
    # 寫表頭
    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
    
    # 寫數(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 ['銷售額', '訂單量'] and isinstance(cell.value, (int, float)):
                cell.number_format = '#,##0.00' if header == '銷售額' 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 = './周報匯總.xlsx'
    
    if not os.path.exists(reports_folder):
        print(f"錯誤：文件夾 '{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  # 臨時存儲路徑
            
            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"  ??  跳過，未識別到表頭。")
                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"  ? 處理文件時出錯：{e}")
    
    if all_data:
        write_summary(all_data, output_file)
        print(f"總計處理 {len(excel_files)} 個文件，合并 {len(all_data)} 行數(shù)據(jù)。")
    else:
        print("未讀取到任何有效數(shù)據(jù)。")

if __name__ == "__main__":
    main()