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