large-order-fusion/mer1.py

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文件都存在！")