import pandas as pd
import os
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter

def beautify_excel(input_excel, output_excel):
    """
    给Excel报表添加专业样式美化（含列宽自适应）
    :param input_excel: 清洗后Excel文件路径
    :param output_excel: 美化后的Excel文件路径
    """
    # 校验输入文件
    if not os.path.exists(input_excel):
        print(f"错误：未找到输入文件 {input_excel}")
        return

    try:
        df = pd.read_excel(input_excel, engine='openpyxl')
    except Exception as e:
        print(f"读取文件失败：{str(e)}")
        return

# 写入Excel并设置样式
with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='清洗合并数据', index=False)
    workbook = writer.book
    worksheet = writer.sheets['清洗合并数据']

    # 定义样式模板
    header_font = Font(name='微软雅黑', size=12, bold=True, color="000000") # 表头字体
    header_fill = PatternFill(start_color="2F5496", end_color='2F5496', fill_type='solid') # 表头背景色（深蓝色）
    header_align = Alignment(horizontal='center', vertical='center', wrap_text=True) # 表头对齐方式
    # 细边框样式（全局统一）
    thin_border = Border(
        left=Side(style='thin', color='000000'),
        right=Side(style='thin', color='000000'),
        top=Side(style='thin', color='000000'),
        bottom=Side(style='thin', color='000000')
    )
    key_col_fill = PatternFill(start_color='E7F0FF', end_color='E7F0FF', fill_type='solid') # 关键列背景色（浅蓝色）
    num_align = Alignment(horizontal='center', vertical='center') # 数值列对齐
    text_align = Alignment(horizontal='left', vertical='center') # 文本列对齐

# 设置表头样式
for col in worksheet.iter_cols(min_row=1, max_row=1):
    for cell in col:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border

# 定义列类型
max_row = worksheet.max_row
max_col = worksheet.max_column
num_cols = ['加购件数', '支付金额', '支付买家数','支付件数']

# 设置数据行样式
for row in worksheet.iter_rows(min_row=2, max_row=max_row, min_col=1, max_col=max_col):
    for cell in row:
        cell.border = thin_border
        col_name = df.columns[cell.column - 1]
        # 数值列居中，文本列左对齐
        if col_name in num_cols:
                    cell.alignment = num_align
                else:
                    cell.alignment = text_align

key_cols = ['日期', 'skuId']
# 关键列标色
for col in worksheet.iter_cols(min_row=2, max_row=max_row, min_col=1, max_col=max_col):
    col_name = df.columns[col[0].column - 1]
    if col_name in key_cols:
        for cell in col:
            cell.fill = key_col_fill

if '日期' in df.columns:
    date_col_idx = df.columns.get_loc('日期') + 1
    for cell in worksheet.iter_cols(min_col=date_col_idx, max_col=date_col_idx, min_row=2, max_row=max_row):
        for c in cell:
            c.number_format = 'yyyy-mm-dd'

if 'skuId' in df.columns:
    sku_col_idx = df.columns.get_loc('skuId') + 1
    # 设置该列的单元格格式为文本
    for row in range(2, max_row + 1):
        cell = worksheet.cell(row=row, column=sku_col_idx)
        cell.number_format = '0'
        cell.alignment = Alignment(horizontal='left', vertical='center')
        cell.border = thin_border

# 列宽自适应（考虑中英文字符宽度和日期格式）
date_column_letter = None
if df is not None and '日期' in df.columns:
    date_col_idx = df.columns.get_loc('日期') + 1
    date_column_letter = worksheet.cell(row=1, column=date_col_idx).column_letter

for col in worksheet.columns:
    max_length = 0
    column_letter = col[0].column_letter
    
    for cell in col:
        if cell.value is None:
            cell_length = 0
        else:
            # 特殊处理日期列：按格式化后的显示长度计算
            if column_letter == date_column_letter and isinstance(cell.value, pd.Timestamp):
                cell_content = cell.value.strftime('%Y-%m-%d')
                cell_length = len(cell_content)  # 固定为10
            else:
                # 普通单元格：区分中英文计算长度（中文占2个字符，英文占1个）
                cell_content = str(cell.value)
                cell_length = 0
                for char in cell_content:
                    if '\u4e00' <= char <= '\u9fff':
                        cell_length += 2
                    else:
                        cell_length += 1
        
        if cell_length > max_length:
            max_length = cell_length
    
    # 调整列宽（预留3个字符的边距）
    adjusted_width = max_length + 3
    worksheet.column_dimensions[column_letter].width = adjusted_width
    print(f"列 {column_letter} 最终调整宽度：{adjusted_width}（最大内容长度：{max_length}）")

if __name__ == "__main__":
    # 请修改为实际的文件路径
    input_file = "C:/Users/xx/Downloads/清洗后的合并数据.xlsx"
    output_file = "C:/Users/xx/Downloads/带样式_清洗后的合并数据.xlsx"
    beautify_excel(input_file, output_file)