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}")