使用python在excel中复制和粘贴数据(保留源格式)

Question

我想用源格式(单元格模式、单元格边框、字体样式、字体颜色、字体大小、页眉和页脚(将数据从工作簿中的一个工作表复制到工作簿中的另一张工作表。这是我使用的代码，它完成了工作，但未能保持源格式。如果你们有什么想法可能对这个话题有所帮助，那就太好了。

源代码：

import openpyxl as xl;
from copy import copy
wb1 = xl.load_workbook('C:\Users\high.xlsx')
ws1 = wb1.active
filename = 'C:\Users\3.0.xlsx'
wb2 = xl.load_workbook(filename)
ws2 = wb2['Session']

mr = ws1.max_row
mc = ws1.max_column
mr2 = ws2.max_row
mc2 = ws2.max_column
ws2.delete_cols(1, mc2+1)
ws2.delete_rows(1, mr2+1)
for i in range (1, mr + 1):
for j in range (1, mc + 1):
c = ws1.cell(row = i, column = j)

ws2.cell(row = i, column = j).value = c.value
wb2.save(str(filename))

Answer 1

单元格对象上有许多属性可以复制，这些属性具有样式信息。尝试直接复制它们将失败，但使用复制模块可以工作。

样式信息也可以附加到行和列上——它们似乎附加到工作表上的标注对象上。

更新：哎呀，这个问题似乎已经得到了回答——不过我将把它留在这里，因为它包括行和列级别的样式。

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")