import openpyxl from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle from openpyxl.drawing.image import Image from openpyxl.utils import get_column_letter from openpyxl.worksheet.merge import MergedCellRange import os import copy def copy_cell_style_complete(src_cell, dest_cell): """ 完整复制单元格所有样式属性 """ try: # 1. 复制字体 if src_cell.font: dest_cell.font = Font( name=src_cell.font.name, size=src_cell.font.size, bold=src_cell.font.bold, italic=src_cell.font.italic, underline=src_cell.font.underline, strike=src_cell.font.strike, color=copy.copy(src_cell.font.color) ) # 2. 复制对齐方式 if src_cell.alignment: dest_cell.alignment = Alignment( horizontal=src_cell.alignment.horizontal, vertical=src_cell.alignment.vertical, text_rotation=src_cell.alignment.text_rotation, wrap_text=src_cell.alignment.wrap_text, shrink_to_fit=src_cell.alignment.shrink_to_fit, indent=src_cell.alignment.indent ) # 3. 复制边框 if src_cell.border: dest_cell.border = Border( left=copy_border_side(src_cell.border.left), right=copy_border_side(src_cell.border.right), top=copy_border_side(src_cell.border.top), bottom=copy_border_side(src_cell.border.bottom) ) # 4. 复制填充颜色 if src_cell.fill: if src_cell.fill.fill_type == 'patternFill': dest_cell.fill = PatternFill( fill_type=src_cell.fill.fill_type, start_color=src_cell.fill.start_color, end_color=src_cell.fill.end_color, patternType=src_cell.fill.patternType ) elif src_cell.fill.fill_type == 'gradientFill': # 处理渐变填充（如果需要） pass # 5. 复制数字格式 dest_cell.number_format = src_cell.number_format # 6. 复制保护设置 dest_cell.protection = copy.copy(src_cell.protection) except Exception as e: print(f"复制单元格样式时出错: {e}") def copy_border_side(side): """复制边框边""" if side and side.style: return Side( style=side.style, color=copy.copy(side.color) ) return None def copy_merged_cells(src_ws, dest_ws, row_offset=0, col_offset=0): """ 复制合并单元格设置 """ try: for merged_range in src_ws.merged_cells.ranges: # 调整合并单元格的位置 min_row = merged_range.min_row + row_offset max_row = merged_range.max_row + row_offset min_col = merged_range.min_col + col_offset max_col = merged_range.max_col + col_offset # 在目标工作表中创建合并单元格 dest_ws.merge_cells( start_row=min_row, start_column=min_col, end_row=max_row, end_column=max_col ) print(f"复制合并单元格: {get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}") except Exception as e: print(f"复制合并单元格时出错: {e}") def copy_row_heights_complete(src_ws, dest_ws, start_row=1): """ 完整复制行高设置 """ try: for row_idx, row_dim in src_ws.row_dimensions.items(): if row_dim.height is not None: dest_row = start_row + row_idx - 1 dest_ws.row_dimensions[dest_row].height = row_dim.height # 复制其他行属性 dest_ws.row_dimensions[dest_row].hidden = row_dim.hidden except Exception as e: print(f"复制行高时出错: {e}") def copy_column_widths_complete(src_ws, dest_ws, start_col=1): """ 完整复制列宽设置 """ try: for col_idx, col_dim in src_ws.column_dimensions.items(): if col_dim.width is not None: dest_col_letter = get_column_letter(start_col + (ord(col_idx) - ord('A'))) dest_ws.column_dimensions[dest_col_letter].width = col_dim.width # 复制其他列属性 dest_ws.column_dimensions[dest_col_letter].hidden = col_dim.hidden except Exception as e: print(f"复制列宽时出错: {e}") def copy_images_improved(src_ws, dest_ws, row_offset=0): """ 改进的图片复制函数，处理各种图片嵌入方式 """ try: # 方法1: 直接复制图片对象 for img in src_ws._images: try: # 创建新的图片对象 if hasattr(img, 'ref') and img.ref: # 如果有图片引用，使用引用创建新图片 new_img = Image(img.ref) elif hasattr(img, 'path') and img.path and os.path.exists(img.path): # 如果有图片路径，使用路径创建新图片 new_img = Image(img.path) else: # 尝试从图片数据创建 img_data = img._data() img_stream = io.BytesIO(img_data) new_img = Image(img_stream) # 设置图片位置（考虑行偏移） if hasattr(img, 'anchor'): new_img.anchor = img.anchor # 调整行位置 if row_offset > 0: # 这里需要根据具体锚点类型调整 if hasattr(new_img.anchor, '_from') and hasattr(new_img.anchor._from, 'row'): new_img.anchor._from.row += row_offset if hasattr(new_img.anchor, 'to') and hasattr(new_img.anchor.to, 'row'): new_img.anchor.to.row += row_offset dest_ws.add_image(new_img) print(f"成功复制图片到位置: {new_img.anchor}") except Exception as img_error: print(f"复制单个图片时出错: {img_error}") continue except Exception as e: print(f"图片复制过程中出错: {e}") def copy_worksheet_complete(src_ws, dest_ws, start_row=1, start_col=1): """ 完整复制工作表的所有内容和样式 """ row_offset = start_row - 1 col_offset = start_col - 1 # 1. 先复制合并单元格 copy_merged_cells(src_ws, dest_ws, row_offset, col_offset) # 2. 复制行高和列宽 copy_row_heights_complete(src_ws, dest_ws, start_row) copy_column_widths_complete(src_ws, dest_ws, start_col) # 3. 复制单元格内容和样式 for row_idx, row in enumerate(src_ws.iter_rows(), 1): for col_idx, cell in enumerate(row, 1): dest_row = row_idx + row_offset dest_col = col_idx + col_offset dest_cell = dest_ws.cell( row=dest_row, column=dest_col, value=cell.value ) copy_cell_style_complete(cell, dest_cell) # 4. 复制图片 copy_images_improved(src_ws, dest_ws, row_offset) def merge_fashion_tables_complete(file1, file2, output_file): """ 完整保留样式的服装搭配表格合并 """ try: # 加载工作簿 wb1 = openpyxl.load_workbook(file1) wb2 = openpyxl.load_workbook(file2) # 创建新工作簿 merged_wb = openpyxl.Workbook() merged_ws = merged_wb.active merged_ws.title = "合并服装搭配表" # 获取工作表 ws1 = wb1.active ws2 = wb2.active print("开始合并第一个表格...") # 复制第一个表格（从第1行开始） copy_worksheet_complete(ws1, merged_ws, start_row=1, start_col=1) # 计算第一个表格的行数 first_table_rows = ws1.max_row print("开始合并第二个表格...") # 复制第二个表格（从第一个表格末尾+2行开始，留出间隔） copy_worksheet_complete(ws2, merged_ws, start_row=first_table_rows + 2, start_col=1) # 保存合并结果 merged_wb.save(output_file) print(f"表格合并完成！输出文件: {output_file}") # 验证结果 verify_merge_complete(output_file, ws1, ws2) return True except Exception as e: print(f"合并过程中出现错误: {e}") import traceback traceback.print_exc() return False def verify_merge_complete(output_file, ws1, ws2): """ 验证合并结果是否完整 """ try: wb = openpyxl.load_workbook(output_file) ws = wb.active print("\n=== 合并结果验证 ===") print(f"总行数: {ws.max_row}") print(f"总列数: {ws.max_column}") print(f"合并单元格数量: {len(ws.merged_cells.ranges)}") print(f"图片数量: {len(ws._images)}") # 检查样式 sample_cell = ws.cell(row=1, column=1) if sample_cell.fill.start_color: print(f"示例单元格填充颜色: {sample_cell.fill.start_color}") if sample_cell.alignment: print(f"示例单元格对齐方式: {sample_cell.alignment.horizontal}") wb.close() except Exception as e: print(f"验证过程中出错: {e}") # 使用示例 if __name__ == "__main__": file1 = "./output/xxxxxxxxxxx.xlsx" file2 = "./output/10CL6E1U086Y.xlsx" output = "merged_data.xlsx" if os.path.exists(file1) and os.path.exists(file2): print("开始合并服装搭配表格（完整样式保留）...") success = merge_fashion_tables_complete(file1, file2, output) if success: print("合并成功！") else: print("合并失败！") else: print("请确保两个Excel文件都存在！")