python完全拷贝源EXCEL的内容和格式到目标EXCEL的尾部

import openpyxl as op
import copy
import re
from collections import OrderedDict
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.utils.cell import coordinate_from_string

def fullcopy_sheet_s2t(s_wb, t_wb, s_ws=None, t_ws=None):
    """完全拷贝源EXCEL的内容和格式到目标EXCEL的尾部

    Args:
        s_wb (_type_): 源工作簿
        t_wb (_type_): 目标工作簿
        s_ws (_type_, optional): 源工作表. Defaults to None.
        t_ws (_type_, optional): 目标工作表. Defaults to None.
    """

    def get_effective_data_range(wb, sheet_name=None):
        """获取工作簿(第一个或指定)工作表的数据范围

        Args:
            wb (_type_): 工作表
            sheet_name (_type_, optional): 工作表名. Defaults to None.

        Returns:
            _type_: 开始CELL坐标,结束CELL坐标,有效数据行数
        """
        # 获取指定工作表
        if sheet_name is None:
            ws = wb[wb.sheetnames[0]]
        else:
            ws = wb[sheet_name]

        cells = [cell for row in ws.rows for cell in row]
        # print("🚀 ~ file: copyxlsx240517.py:199 ~ _list:", cells)
        if cells:
            start = cells[0]
            # print("🚀 ~ file: copyxlsx240517.py:201 ~ cells[0]:", cells[0])
            end = cells[-1]
            # print("🚀 ~ file: copyxlsx240517.py:203 ~ cells[-1]:", cells[-1])
            start_coordinate = start.coordinate
            end_coordinate = end.coordinate
            # 计算有效数据行数（不包括空行）
            data_row_count = end.row - start.row + 1
        else:
            # 空表格
            start_coordinate = None
            end_coordinate = None
            data_row_count = 0

        # 返回结果
        return (start_coordinate, end_coordinate, data_row_count)

    def get_newarea(start_coordinate, end_coordinate, current_row, newrows):
        """根据原始坐标, 开始行, 新增行数，生成新增数据的目标区域

        Args:
            start_coordinate (_type_): 开始坐标
            end_coordinate (_type_): 结束坐标
            current_row (_type_): 开始行
            newrows (_type_): 新增行数

        Returns:
            _type_: _description_
        """
        start_col, start_row = coordinate_from_string(start_coordinate)
        end_col, end_row = coordinate_from_string(end_coordinate)
        new_start_row = start_row + current_row
        new_end_row = end_row + current_row
        new_start_col = start_col
        new_end_col = end_col
        # print(new_start_col + str(new_start_row), new_end_col + str(new_end_row))
        return new_start_col + str(new_start_row), new_end_col + str(new_end_row)

    if s_ws is None:
        s_ws = s_wb.sheetnames[0]
    if t_ws is None:
        t_ws = s_ws

    ws_from = s_wb[s_ws]
    if t_ws not in t_wb.sheetnames:
        ws_to = t_wb.create_sheet(t_ws)
    else:
        ws_to = t_wb[t_ws]

    # 删除默认创建的空sheet
    if s_ws != "Sheet" and "Sheet" in t_wb.sheetnames:
        del t_wb["Sheet"]

    _, _, lastrow = get_effective_data_range(t_wb)

    start_coordinate, end_coordinate, data_row_count = get_effective_data_range(s_wb)

    s_range = f"{start_coordinate}:{end_coordinate}"

    targe_start_coordinate, targe_end_coordinate = get_newarea(
        start_coordinate, end_coordinate, lastrow, data_row_count
    )

    t_range = f"{targe_start_coordinate}:{targe_end_coordinate}"

    ws_to.sheet_properties.tabColor = ws_from.sheet_properties.tabColor
    if s_range is not None:
        source_area = ws_from[s_range]
    else:
        source_area = ws_from

    merge_cell_dict = OrderedDict()
    merged_ranges = ws_from.merged_cells.ranges
    for source_row in source_area:
        for source_cell in source_row:
            sc_str = str(source_cell)
            point_time = sc_str.count(".")
            sc_str = sc_str.replace(".", "", point_time - 1)
            start = sc_str.find(".")
            sc_str = sc_str[start + 1 : -1]
            for merged_range in merged_ranges:
                if source_cell.coordinate in merged_range:
                    _cell_value = ws_from.cell(
                        row=merged_range.min_row, column=merged_range.min_col
                    )
                    merge_cell_dict[sc_str] = (
                        merged_range.min_row,
                        merged_range.min_col,
                        _cell_value,
                    )
                    continue
    range_li = []
    for val in set(merge_cell_dict.values()):
        tmp = []
        for x, y in merge_cell_dict.items():
            if y == val:
                tmp.append(x)
        if len(tmp):
            range_li.append(min(tmp) + ":" + max(tmp))

    for i in range_li:
        # print(i)
        if s_range is not None:
            base_point_letter = s_range.split(":")[0]
            base_point = ws_from[base_point_letter]
            base_row = base_point.row
            base_col = base_point.column
        else:
            base_point_letter = i.split(":")[0]
            base_point = ws_from[base_point_letter]
            base_row = base_point.row
            base_col = base_point.column
        s = i.split(":")[0]
        e = i.split(":")[1]
        # 模板区间第一个点相对顶点距离
        base_delta_row = ws_from[s].row - base_row
        base_delta_col = ws_from[s].column - base_col
        # 模板区间两个端点距离
        delta_row = ws_from[e].row - ws_from[s].row
        delta_col = ws_from[e].column - ws_from[s].column
        # print(base_delta_row, base_delta_col, delta_row, delta_col)
        if t_range is not None:
            tar_s = t_range.split(":")[0]
            tar_s_letter = re.findall(r"([A-Za-z]+)", tar_s)[0]
            tar_base_col_idx = column_index_from_string(tar_s_letter)
            tar_base_row_idx = int(re.findall(r"(\d+)", tar_s)[0])
        else:
            tar_s = s
            tar_s_letter = re.findall(r"([A-Za-z]+)", tar_s)[0]
            tar_base_col_idx = column_index_from_string(tar_s_letter)
            tar_base_row_idx = int(re.findall(r"(\d+)", tar_s)[0])
        # print(tar_base_row_idx, tar_base_col_idx)
        tar_range_s_col = get_column_letter(tar_base_col_idx + base_delta_col)
        tar_range_s_idx = tar_base_row_idx + base_delta_row
        tar_range_e_col = get_column_letter(
            tar_base_col_idx + base_delta_col + delta_col
        )
        tar_range_e_idx = tar_base_row_idx + base_delta_row + delta_row
        tar_merge = (
            tar_range_s_col
            + str(tar_range_s_idx)
            + ":"
            + tar_range_e_col
            + str(tar_range_e_idx)
        )
        # print('tar merge:', tar_merge)
        ws_to.merge_cells(tar_merge)

    if s_range is not None and t_range is not None:
        source_point_letter = s_range.split(":")[0]
        source_point = ws_from[source_point_letter]
        source_row = source_point.row
        source_col = source_point.column

        tar_point_letter = t_range.split(":")[0]
        tar_point = ws_from[tar_point_letter]
        tar_row = tar_point.row
        tar_col = tar_point.column

        delta_row = tar_row - source_row
        delta_col = tar_col - source_col
        print("ROW:", tar_row, source_row)
        print("COL:", tar_col, source_col)
    else:
        delta_row = 0
        delta_col = 0
    # print("DELTA ROW COL:", delta_row, delta_col)

    for source_row in source_area:
        update_row_h = False
        for source_cell in source_row:
            source_x = source_cell.row
            new_x = source_x + delta_row
            source_y = source_cell.column
            new_y = source_y + delta_col

            if not update_row_h:
                ws_to.row_dimensions[new_x].height = ws_from.row_dimensions[
                    source_x
                ].height
                update_row_h = True

            ws_to.column_dimensions[
                get_column_letter(new_y)
            ].width = ws_from.column_dimensions[get_column_letter(source_y)].width

            ws_to.cell(row=new_x, column=new_y, value=source_cell.value)

            # 设置单元格格式
            target_cell = ws_to.cell(new_x, new_y)
            target_cell.fill = copy.copy(source_cell.fill)
            if source_cell.has_style:
                target_cell._style = copy.copy(source_cell._style)
                target_cell.font = copy.copy(source_cell.font)
                target_cell.border = copy.copy(source_cell.border)
                target_cell.fill = copy.copy(source_cell.fill)
                target_cell.number_format = copy.copy(source_cell.number_format)
                target_cell.protection = copy.copy(source_cell.protection)
                target_cell.alignment = copy.copy(source_cell.alignment)

    s_wb.close()
    t_wb.close()

targe_wb = op.Workbook()  # 新建一个EXCEL
source_wb1 = op.load_workbook("head.xlsx")
source_wb2 = op.load_workbook("data.xlsx")
source_wb3 = op.load_workbook("tail.xlsx")

fullcopy_sheet_s2t(source_wb1, targe_wb)
fullcopy_sheet_s2t(source_wb2, targe_wb)
fullcopy_sheet_s2t(source_wb3, targe_wb)

targe_wb.save(filename="all_V2.xlsx")