large-order-fusion/modules/save_data.py

import os
from tqdm import tqdm 
from typing import Dict
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.styles.numbers import FORMAT_PERCENTAGE_00
from utils.tools import read_json_file
from utils.logger_config import setup_logger

logger = setup_logger(__name__)

def number_to_letter(n):
    if n < 1:
        return None
    result = []
    while n > 0:
        n -= 1  # 调整索引从0开始（A=0, B=1,...）
        remainder = n % 26
        result.append(chr(65 + remainder))  # 65是'A'的ASCII码
        n = n // 26
    return ''.join(reversed(result))  # 反转拼接结果

def export_to_excel(data: Dict, filename: str = None) -> None:
    """
    将商品数据导出到Excel文件，并在单元格中插入实际图片
    
    参数:
        data: 包含商品信息的字典
        filename: 输出的Excel文件名
    """
    # 创建工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "商品数据"
    
    # 定义字体颜色 - 使用16进制RGB颜色码
    GREEN_FONT = Font(color="FF0000")  # 绿色
    BLUE_FONT = Font(color="0000FF")   # 蓝色
    RED_FONT = Font(color="00FF00")
    
    # 设置列宽
    _setup_column_dimensions(ws)

    # 提取各商品信息
    primary_good = data["primary_goods_info"]["goods_info"]
    combine_two = data["combine_two_info"]
    combine_three = data["combine_three_info"]
    outfit_orders = data["outfit_orders"]

    header_count = [len(order) for order in outfit_orders]
    
    # 设置表头
    _write_headers(ws, header_count)

    ws.freeze_panes = 'A2'

    
    
    # 主商品编码
    primary_code = primary_good['sku'] + primary_good['color_code']
    
    # 处理两种组合数据
    _process_combine_two(ws, primary_good, primary_code, combine_two, GREEN_FONT)
    _process_combine_three(ws, primary_good, primary_code, combine_three, len(combine_two), BLUE_FONT)
    _process_outfit_orders(ws, outfit_orders, [GREEN_FONT, RED_FONT])
    
    # 格式化工作表
    _format_worksheet(ws, len(combine_two) + len(combine_three))
    
    # 保存Excel文件
    if not filename:
        filename = primary_code + '.xlsx'
    os.makedirs('./output', exist_ok=True)  # 确保输出目录存在
    file_path = os.path.join('./output', filename)
    wb.save(file_path)
    logger.info(f"数据已成功写入 {file_path} 文件")

    return file_path


def _setup_column_dimensions(ws):
    """设置工作表列宽"""
    # 图片列
    for col in ['B', 'C', 'D']:
        ws.column_dimensions[col].width = 13.6
    
    # 数据列
    for col in ['A', *[get_column_letter(i) for i in range(4, 58)]]:
        ws.column_dimensions[col].width = 14 if col != 'A' else 40


def _write_headers(ws, header_count):
    """写入表头"""

    # 大单组合表头
    first_sub_header = ["大单组合一", "大单组合二", "大单组合三", "大单组合四", "大单组合五"]
    second_sub_header = ["图片", "款号", "价格"] * sum(header_count) + ["价格"]

    # 连带款表头
    headers = [
        "组合", "主推款图片", "连带款1图片", "连带款2图片", "连带排名", 
        "连带频次", "主商品频次", "连带率", "连带件数", "连带金额", ""
    ]
    
    # 设置连带款表头
    for col_idx, header in enumerate(headers, start=1):
        cell = ws.cell(row=1, column=col_idx)
        cell.value = header
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')

    # 设置连带款父表头
    start_col = 12
    current_col = start_col  # 当前写入位置

    # 定义红绿交替的颜色填充
    red_fill = PatternFill(start_color="FF9999", end_color="FF9999", fill_type="solid")
    green_fill = PatternFill(start_color="99FF99", end_color="99FF99", fill_type="solid")

    for idx, count in enumerate(header_count):
        # 计算合并范围
        merge_start = current_col
        merge_end = current_col + count * 3 - 1
        
        print(f"写入位置：{current_col}")
        print(f"合并范围：{merge_start}-{merge_end}")
        
        # 写入单元格内容
        cell = ws.cell(row=1, column=current_col)
        cell.value = f"大单组合：{idx+1}"  # 使用idx+1作为组合编号
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
        
        # 根据索引奇偶性选择颜色
        if idx % 2 == 0:  # 偶数索引使用红色
            cell.fill = red_fill
        else:  # 奇数索引使用绿色
            cell.fill = green_fill
        
        # 合并单元格
        merge_range = f"{number_to_letter(merge_start)}1:{number_to_letter(merge_end)}1"
        ws.merge_cells(merge_range)
        
        # 更新下一个组合的起始列
        current_col = merge_end + 1
        print('--------------------------------')


    # 设置连带款子表头
    for col_idx, header in enumerate(second_sub_header, start=12):
        cell = ws.cell(row=2, column=col_idx)
        cell.value = header
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')


def _process_combine_two(ws, primary_good, primary_code, combine_two, font_color):
    """处理两种商品的组合数据"""
    for row_idx, item in enumerate(combine_two, start=2):
        goods_info = item['goods_info']
        sub_code = goods_info['sku'] + goods_info['color_code']
        
        # 写入数据并设置字体颜色
        for col, value in [
            (1, f"{primary_code},{sub_code}"),
            (4, "22组合"),
            (5, row_idx-1),
            (6, item['freq']),
            (7, item['main_freq']),
            (8, item['combine_rate']),
            (9, item['count']),
            (10, item['sales'])
        ]:
            cell = ws.cell(row=row_idx, column=col, value=value)
            cell.font = font_color
        
        # 插入图片
        _insert_image(ws, primary_good['image_path'], f'B{row_idx}')
        _insert_image(ws, goods_info['image_path'], f'C{row_idx}')


def _process_combine_three(ws, primary_good, primary_code, combine_three, combine_two_count, font_color):
    """处理三种商品的组合数据"""
    for row_idx, item in enumerate(combine_three, start=2 + combine_two_count):
        goods_info = item['goods_info']
        sub1_code = goods_info[0]['sku'] + goods_info[0]['color_code']
        sub2_code = goods_info[1]['sku'] + goods_info[1]['color_code']
        
        # 写入数据并设置字体颜色
        for col, value in [
            (1, f"{primary_code},{sub1_code},{sub2_code}"),
            (5, row_idx - (1 + combine_two_count)),
            (6, item['freq']),
            (7, item['main_freq']),
            (8, item['combine_rate']),
            (9, item['count']),
            (10, item['sales'])
        ]:
            cell = ws.cell(row=row_idx, column=col, value=value)
            cell.font = font_color
        
        # 插入图片
        _insert_image(ws, primary_good['image_path'], f'B{row_idx}')
        _insert_image(ws, goods_info[0]['image_path'], f'C{row_idx}')
        _insert_image(ws, goods_info[1]['image_path'], f'D{row_idx}')


def _process_outfit_orders(ws, outfit_orders, font_colors):
    start_col = 12
    start_row = 3
    total_price = 0
    color_change = False
    for _, combine_order in enumerate(outfit_orders, start=2):
        current_font = font_colors[int(color_change)]
        for col_idx, order in enumerate(combine_order, start=12):
            for col, value in [(col_idx-11, order["skc"]), (col_idx-11, float(order["price"]))]:
                cell = ws.cell(row=start_row, column=start_col + col, value=value)
                cell.font = current_font
                start_col += 1
                total_price += float(order["price"])

            if not order["image_path"]:
                ws.cell(row=start_row, column=start_col + col - 3, value="None")
            else:
                col_name = number_to_letter(start_col + col - 3)
                _insert_image(ws, order["image_path"], f'{col_name}{start_row}')
        start_col += len(combine_order)
        color_change = not color_change

    ws.cell(row=start_row, column=start_col+col-2, value=total_price)


def _insert_image(ws, image_path, cell_location):
    """在指定单元格插入图片"""
    try:
        if os.path.exists(image_path):
            img = Image(image_path)
            img.width = img.height = 100  # 设置图片大小
            ws.add_image(img, cell_location)
    except Exception as e:
        logger.info(f"未获取到商品图片")


def _format_worksheet(ws, data_rows_count):
    """格式化工作表"""
    # 设置连带率列格式
    for cell in ws['H']:
        cell.number_format = FORMAT_PERCENTAGE_00
    
    # 设置行高
    for row in range(2, data_rows_count + 2):
        ws.row_dimensions[row].height = 80
    
    # 设置所有单元格居中对齐
    alignment = Alignment(horizontal='center', vertical='center')
    for row in ws.iter_rows():
        for cell in row:
            cell.alignment = alignment


# 使用示例
if __name__ == "__main__":

    files_path = "./test/output/"
    files = os.listdir(files_path)

    for file in tqdm(files):

        if file.split('.')[-1] == "json":

            try:
                file_path = os.path.join(files_path, file)
                file_content = read_json_file(file_path)

                file_name = file.replace("json", "xlsx")
                export_to_excel(file_content, file_name)

                print(f"完成文件处理：{file_path}")

            except Exception as e:
                print(f"文件处理失败：{file_path}")