Copy and paste data in excel using python (Keep source formatting)

Question

I want to copy the data from one sheet in a workbook to another sheet in a workbook with source formatting (Cell pattern, cell border, font style, font colour, font size, header and footer). This is the code i used, it does the work but it failed to keep the source formatting. It would be great if you guys have anything in mind that might be helpful in this topic.

SOURCE CODE:

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

There's a number of attributes on the cell object which you can copy over which have the style information. Trying to copy them directly will fail, but using the copy module will work.

Style information can also be attached to rows and columns - they seem to be attached to the dimension objects on the worksheet.

Update: Oops, looks like this has already been answered - I'll leave this here though since it includes the row and column level styles.

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