使用Python复制和粘贴excel中的数据(保持源格式)

 

import openpyxl as xl
from copy import copy
style_attrs = ["alignment", "border", "fill", "font", "number_format", "protection"]
def cells(worksheet):
    """Return a generator for the sequence of cells in the worksheet"""
    for row in worksheet:
        for cell in row:
            yield cell
def copy_attrs(src, dst, attrs=style_attrs):
    """Copy attributes from src to dst. Attributes are shallow-copied to avoid
    TypeError: unhashable type: 'StyleProxy'"""
    for name in attrs:
        setattr(dst, name, copy(getattr(src, name)))
def copy_column_attrs(worksheet_src, worksheet_dst, attrs=style_attrs + ["width"]):
    """Copy ColumnDimension properties from worksheet_src to worksheet_dst.
    Only properties listed in attrs will be copied."""
    for column, dimensions in worksheet_src.column_dimensions.items():
        copy_attrs(
            src=dimensions,
            dst=worksheet_dst.column_dimensions[column],
            attrs=attrs,
        )
def copy_row_attrs(worksheet_src, worksheet_dst, attrs=style_attrs + ["height"]):
    """Copy RowDimension properties from worksheet_src to worksheet_dst.
    Only properties listed in attrs will be copied."""
    for row, dimensions in worksheet_src.row_dimensions.items():
        copy_attrs(
            src=dimensions,
            dst=worksheet_dst.row_dimensions[row],
            attrs=style_attrs + ["height"],
        )
def copy_cells(worksheet_src, worksheet_dst, attrs=style_attrs):
    """Copy cells from worksheet_src to worksheet_dst. If cells are styled
    then also copy the attributes listed in attrs."""
    for cell in cells(worksheet_src):
        cell_dst = worksheet_dst.cell(row=cell.row, column=cell.column)
        if cell.has_style:
            copy_attrs(cell, cell_dst, attrs=attrs)
        cell_dst.value = cell.value
def delete_worksheet_cells(worksheet):
    worksheet.delete_cols(1, worksheet.max_column + 1)
    worksheet.delete_rows(1, worksheet.max_row + 1)
wb_src = xl.load_workbook("a.xlsx")
ws_src = wb_src.active
wb_dst = xl.load_workbook("b.xlsx")
ws_dst = wb_dst.active
delete_worksheet_cells(ws_dst)
copy_column_attrs(ws_src, ws_dst)
copy_row_attrs(ws_src, ws_dst)
copy_cells(ws_src, ws_dst)
wb_dst.save("b.xlsx")