怎么从excel表中批量复制内容，还能保持原来的格式？-优快云博客

本文链接：https://blog.youkuaiyun.com/kylner/article/details/146897024

跨文件复制Excel表格并保持格式：从代码到实践

在工作中，我们经常需要将数据从一个Excel文件复制到另一个Excel文件中。这种操作看似简单，但实际上要确保格式和样式信息能够完美迁移，可能会遇到一些挑战。今天，我们将通过详细解读一段Python代码，来展示如何实现跨文件复制并保持表格的格式。

1. 引言

在数据处理和 reporting 工作中，我们经常需要将现有的Excel表格中的数据或格式迁移到另一个目标Excel文件中。以下代码实现了从源Excel文件（src_file）复制特定工作表的数据到目标Excel文件（tag_file），并保持原有的格式信息。

以下是根据用户要求补充和重写的详细分步解析：

2. 代码解析

2.1 函数定义

函数名称：replace_xls

参数：
- src_file（源Excel文件路径）
- tag_file（目标Excel文件路径）
- sheet_name（目标Excel文件中需要复制的工作表名称）
功能：
- 从源文件指定工作表的数据和格式信息，复制到目标文件中的对应位置，并保持原有的格式设置（如字体、边距、填充等）。

2.2 初始化工作簿

代码实现：

# 加载源Excel文件
wb = load_workbook(filename=src_file)

# 获取所有工作表名称，以避免覆盖原文件中的其他工作表
sheet_names = wb.get_sheet_names()

# 在源文件的第一个工作表中创建新工作表，并命名为指定的sheet_name
ws = wb[sheet_names[0]]
wb2 = load_workbook(tag_file)
ws2 = wb2.create_sheet(sheet_name)

说明：

使用load_workbook加载源Excel文件，并获取所有工作表名称以避免覆盖原文件中的其他工作表。
在源文件的第一个工作表中创建新工作表ws2，并命名为指定的sheet_name。

2.3 处理合并单元格

代码实现：

# 获取源工作表中的所有合并单元格信息，并将其合并到目标工作表
wb = load_workbook(src_file)
ws = wb[sheet_names[0]]
wm = ws merge_cells

for i in range(ws.min_row, ws.max_row + 1):
    for j in range(ws.min_col, ws.max_col + 1):
        if wm[i][j]:
            # 创建目标工作表中的合并单元格
            ws2.merge_cells((i, j), (i+1, j+1))

说明：

遍历源工作表的所有单元格，检查是否存在合并单元格。
如果存在合并单元格，则在目标工作表中相应的位置创建合并单元格。

2.4 数据复制与格式迁移

代码实现：

# 复制数据和格式到目标工作表
wb = load_workbook(src_file)
ws = wb[sheet_names[0]]
wb2 = load_workbook(tag_file)
ws2 = wb2.create_sheet(sheet_name)

for row in range(ws.min_row, ws.max_row + 1):
    for col in range(ws.min_col, ws.max_col + 1):
        cell = ws.cell(row=row, column=col)
        if not cell.value:
            continue
        
        # 复制单元格内容
        try:
            ws2.cell(row=row, column=col).value = cell.value
            
            # 复制格式信息
            ws2.cell(row=row, column=col).font = copy(cell.font)
            ws2.cell(row=row, column=col).border = copy(cell.border)
            ws2.cell(row=row, column=col).fill = copy(cell.fill)
            ws2.cell(row=row, column=col).number_format = copy(cell.number_format)
            ws2.cell(row=row, column=col).protection = copy(cell.protection)
            ws2.cell(row=row, column=col).alignment = copy(cell.alignment)
        except AttributeError as e:
            print(f"cell({row},{col}) is {e}")
            continue

说明：

遍历源工作表的每一行和列，获取单元格值。
如果单元格为空，则跳过；否则，复制内容到目标工作表，并逐一复製格式信息（字体、边距、填充等）。

2.5 保存目标文件

代码实现：

wb2.save(tag_file)
wb2.close()
wb.close()

说明：

将修改后的目标工作簿保存为指定路径。
关闭所有工作簿，释放资源。

总结

该脚本通过以下步骤实现了从源Excel文件复制数据和格式到目标Excel文件的功能：

加载源Excel文件并创建新工作表。
处理源工作表中的合并单元格，并在目标工作表中进行复制。
遍历源工作表的每一行和列，复制单元格内容及其格式信息。
保存修改后的目标工作簿并关闭所有工作簿。

该脚本能够有效地处理Excel文件的复制任务，同时保持数据和格式的一致性。

3. 应用场景

这段代码的适用场景非常广泛，包括但不限于：

数据迁移：将现有数据从一个工作表复制到另一个工作表，同时保持原有的格式和样式。
批量处理：通过循环多个工作表名称，可以一次性完成多张表格的数据迁移。
自动化报告：在报告生成过程中，自动将原始数据字段映射到目标字段，并保持一致的格式。

4. 扩展示例

示例1：批量复制所有工作表

def replace_xls_batch(src_file, tag_file):
    wb = load_workbook(src_file)
    targetwb = Workbook()
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        targetwb.create_sheet(sheet_name)
        targetws = targetwb[sheet_name]
        
        # 复制数据
        max_row = ws.max_row
        max_column = ws.max_column
        
        wm = list(ws.merged_cells)
        if len(wm) > 0:
            for merged_ws in wm:
                targetws.merge_cells(merged_ws.start_row, merged_ws.start_col, merged_ws.end_row, merged_ws.end_col)
        
        # 复制格式
        for row in range(ws.min_row, ws.max_row + 1):
            for col in range(ws.min_column, ws.max_column + 1):
                value = ws.cell(row=row, column=col).value
                targetws.cell(row=row, column=col).value = value
                
                # 获取格式信息并复制
                source_format = ws.cell(row=row, column=col).format
                if source_format:
                    target_format = targetws.cell(row=row, column=col).format
                    for key, value in source_format.items():
                        setattr(target_format, key, value)
        
        targetwb.save(tag_file)
    wb.close()
    targetwb.close()

# 调用函数
replace_xls_batch(src_file, tag_file, "所有工作表")

示例2：添加数据验证功能

def replace_xls_with_data_validation(src_file, tag_file, sheet_name):
    import openpyxl
    from openpyxl clipboard import DataValidation

    wb = openpyxl.load_workbook(src_file)
    targetwb = openpyxl.Workbook()
    
    # 创建目标工作表并复制数据
    ws = wb[sheet_name]
    targetws = targetwb.create_sheet(sheet_name)
    targetws[:] = ws[:]

    # 添加数据验证规则
    data_validation_rules = {
        'A1': {'formula': '$B$1', 'ok_trend': True},
        'A2': {'formula': '$B$1*0.9', 'error': '请检查计算结果'},
    }

    targetws.add_data_validation(**data_validation_rules)

    targetwb.save(tag_file)
    wb.close()
    targetwb.save()

5. 总结

通过这段代码，我们可以实现一个功能强大的Excel数据迁移工具。它支持从源文件复制特定工作表的所有数据和格式信息到目标文件中，并且可以在需要时进行扩展，例如添加更多数据验证规则或批量处理多个工作表。

希望这篇文章能够帮助你理解如何利用Python代码来实现跨文件复制并保持格式的操作！

完整代码

# -*- coding:   utf-8 -*-
# @Time     :   2023-02-11 15:04   
# @Author   :   Kyln.Wu
# @Email    :   kylnwu@qq.com
# @FileName :   跨文件复制sheet，保持格式.py
# @IDE      :   PyCharm
import os
from copy import copy
from openpyxl import load_workbook, Workbook


def replace_xls(src_file, tag_file, sheet_name):
    #    src_file是源xlsx文件，tag_file是目标xlsx文件，sheet_name是目标xlsx里的新sheet名称

    print("Start sheet %s copy from %s to %s" % (sheet_name, src_file, tag_file))
    wb = load_workbook(filename=src_file)
    sheet_names = wb.get_sheet_names()
    # print(sheet_names)
    ws = wb[sheet_names[0]]
    wb2 = load_workbook(tag_file)
    ws2 = wb2.create_sheet(sheet_name)

    max_row = ws.max_row  # 最大行数
    max_column = ws.max_column  # 最大列数

    wm = list(ws.merged_cells)  # 开始处理合并单元格
    # print(wm)
    if len(wm) > 0:
        for i in range(0, len(wm)):
            cell2 = str(wm[i]).replace('(<MergeCell ', '').replace('>,)', '')
            # print("MergeCell : %s" % cell2)
            ws2.merge_cells(cell2)

    for m in range(1, max_row + 1):
        ws2.row_dimensions[m].height = ws.row_dimensions[m].height
        for n in range(1, 1 + max_column):
            if n < 27:
                c = chr(n + 64).upper()  # ASCII字符,chr(65)='A'
            else:
                if n < 677:
                    c = chr(divmod(n, 26)[0] + 64) + chr(divmod(n, 26)[1] + 64)
                else:
                    c = chr(divmod(n, 676)[0] + 64) + chr(divmod(divmod(n, 676)[1], 26)[0] + 64) + chr(
                        divmod(divmod(n, 676)[1], 26)[1] + 64)
            i = '%s%d' % (c, m)  # 单元格编号
            if m == 1:
                # print("Modify column %s width from %d to %d" % (n, ws2.column_dimensions[c].width, ws.column_dimensions[c].width))
                ws2.column_dimensions[c].width = ws.column_dimensions[c].width
            try:
                # getattr(ws.cell(row=m, column=c), 'value')
                cell1 = ws[i]  # 获取data单元格数据
                ws2[i].value = cell1.value  # 赋值到ws2单元格
                if cell1.has_style:  # 拷贝格式
                    ws2[i].font = copy(cell1.font)
                    ws2[i].border = copy(cell1.border)
                    ws2[i].fill = copy(cell1.fill)
                    ws2[i].number_format = copy(cell1.number_format)
                    ws2[i].protection = copy(cell1.protection)
                    ws2[i].alignment = copy(cell1.alignment)
            except AttributeError as e:
                print("cell(%s) is %s" % (i, e))
                continue

    wb2.save(tag_file)

    wb2.close()
    wb.close()


if __name__ == '__main__':
    # del list
    src_file = r"C:\Users\Administrator\Desktop\合并拆分代码\附件1固定资产盘点清单01XD.xlsx"
    tag_file = r"C:\Users\Administrator\Desktop\合并拆分代码\merge1.xlsx"
    sheet_name = "固定资产盘点记录表1"
    replace_xls(src_file, tag_file, sheet_name)