Python 从一个Excel文件复制数据粘贴到另一个Excel文件中 向右粘贴

**

Python 从一个Excel文件复制数据粘贴到另一个Excel文件中

**
粘贴到模板文件右侧新增第三列第一行为起始位置

# Python: 根据模板文件，生成新的Excel表
import re
from copy import copy
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# 追加内容来自于该文件
tpl_sheet_name = 'Sheet1'
wb_tpl = load_workbook(filename=r'需要复制数据.xlsx')
ws_tpl = wb_tpl[tpl_sheet_name]

# 要保留的文件
wb = load_workbook(filename=r'粘贴到此文件.xlsx')
# active会选择你打开时选中的sheet表，如果有多个sheet表不确定时，建议使用wb['sheet_name']
ws = wb["Sheet2"]

max_row = ws.max_row
max_col = ws.max_column
old_max_col = max_col

cur_row = 0
cur_col = 2
column_l = ""
first_column = "A"
for row in ws_tpl:
    cur_row += 1
    cur_col = 2
    for cell in row:
        cur_col += 1
        # 复制值
        # ws[cell.coordinate].value = cell.value
        try:
            pos = f'{get_column_letter(max_col + cur_col)}{cur_row}'
            print("pos", pos)
        except Exception as e:
            cur_col -= 1
            continue
        ws[pos].value = cell.value
        ws[pos].data_type = copy(cell.data_type)

        if cell.has_style:
            # 复制字体，填充，对齐，边框
            ws[pos].font = copy(cell.font)
            ws[pos].fill = copy(cell.fill)
            ws[pos].number_format = copy(cell.number_format)
            ws[pos].alignment = copy(cell.alignment)
            ws[pos].border = copy(cell.border)

            if cell.comment:
                # 复制备注
                ws[pos].comment = copy(cell.comment)


def Col2Int(s: str) -> int:
    assert (isinstance(s, str))
    for i in s:
        if not 64 < ord(i) < 91:
            raise ValueError('Excel Column ValueError')
    return sum([(ord(n) - 64) * 26 ** i for i, n in enumerate(list(s)[::-1])])


# 复制合并单元格
for x in ws_tpl.merged_cells.ranges:
    # print(x.coord)
    match = re.search(r'([a-zA-Z]+)(\d+):([a-zA-Z]+)(\d+)', x.coord)
    col_1, row_1, col_2, row_2 = match.groups()
    new_coord = f'{get_column_letter(Col2Int(col_1) + old_max_col + 2)}{int(row_1)}:{get_column_letter(Col2Int(col_2) + old_max_col + 2)}{int(row_2)}'
    # print("new_coord", new_coord)
    ws.merge_cells(range_string=new_coord)

# 保存
wb.save(r'粘贴到此文件.xlsx.xlsx')