Fixing Python openpyxl That Loses Cell Styles When Copying Rows Between Workbooks

You copy a row from one workbook to another with openpyxl, open the result in Excel, and find plain unstyled cells staring back at you. The data arrived fine but every font, background fill, and border vanished. This is one of the most common openpyxl pain points, and it happens because of a specific detail in how the library handles style objects.

This article explains exactly why styles are dropped and gives you working code patterns you can paste straight into your project.

What You'll Learn

• How openpyxl stores and references cell style attributes internally
• Why assigning a cell object from one workbook to another silently drops styles
• How to deep-copy font, fill, border, alignment, and number format correctly
• How to transfer named styles so they survive in the destination workbook
• The edge cases around merged cells and row heights that trip people up

Prerequisites

You need Python 3.8 or later and openpyxl 3.x installed (pip install openpyxl). The examples assume you are comfortable loading and saving workbooks. If you have run into the related problem of number formatting not sticking, the article on fixing openpyxl number formatting covers that specific case in detail.

Why Cell Styles Disappear When You Copy Rows

openpyxl stores style information — font, fill, border, alignment, number format — as objects attached to each cell. When you read a cell from a worksheet, those style objects are bound to the internal style registry of that workbook. They are not plain data; they carry a reference back to the parent workbook's shared styles.

When you write dest_cell.font = src_cell.font, you are assigning the same object reference across two different workbooks. Excel's file format does not allow that; each workbook has its own style table. openpyxl detects the mismatch at save time and silently drops or resets the style to the default.

The fix is to create a brand-new style object in the destination workbook's context, populated with the same attribute values from the source. Python's copy.copy() handles this for openpyxl's immutable style objects.

How openpyxl Represents Cell Styles

Each cell exposes six style-related attributes:

• font — typeface, size, bold, italic, color, underline
• fill — background pattern or gradient (PatternFill or GradientFill)
• border — edge lines on each of the four sides
• alignment — horizontal/vertical alignment, wrap text, indent
• number_format — a plain string like "#,##0.00" or "DD/MM/YYYY"
• protection — locked/hidden flags used with sheet protection

number_format is just a string, so it copies safely on its own. The others are openpyxl style objects and need copy.copy().

The Naive Copy That Loses Everything

Here is the pattern that looks right but silently strips styles:

from openpyxl import load_workbook

src_wb = load_workbook("source.xlsx")
dest_wb = load_workbook("destination.xlsx")

src_ws = src_wb.active
dest_ws = dest_wb.active

# Copy row 2 from source to row 2 in destination
for src_cell in src_ws[2]:
    dest_cell = dest_ws.cell(row=src_cell.row, column=src_cell.column)
    dest_cell.value = src_cell.value
    dest_cell.font = src_cell.font       # WRONG — same object, wrong workbook
    dest_cell.fill = src_cell.fill       # WRONG
    dest_cell.border = src_cell.border   # WRONG

dest_wb.save("destination.xlsx")

The values land correctly. The styles do not. You will not see an exception; openpyxl just resets unresolvable style references to defaults when building the output file.

Copying Styles Correctly: The copy() Approach

Python's copy.copy() creates a shallow copy of an openpyxl style object. Because openpyxl style objects are immutable value types (they implement __copy__), a shallow copy is all you need — the copied object is independent and can be registered in a different workbook without conflicts.

from openpyxl import load_workbook
from copy import copy

def copy_cell_style(src_cell, dest_cell):
    """Copy all style attributes from src_cell to dest_cell."""
    if src_cell.has_style:
        dest_cell.font      = copy(src_cell.font)
        dest_cell.fill      = copy(src_cell.fill)
        dest_cell.border    = copy(src_cell.border)
        dest_cell.alignment = copy(src_cell.alignment)
        dest_cell.protection = copy(src_cell.protection)
    dest_cell.number_format = src_cell.number_format  # plain string, no copy needed


def copy_row(src_ws, dest_ws, src_row_idx, dest_row_idx):
    """Copy values and styles from one row to another (different workbooks OK)."""
    src_row = src_ws[src_row_idx]
    for src_cell in src_row:
        dest_cell = dest_ws.cell(row=dest_row_idx, column=src_cell.column)
        dest_cell.value = src_cell.value
        copy_cell_style(src_cell, dest_cell)


src_wb  = load_workbook("source.xlsx")
dest_wb = load_workbook("destination.xlsx")

copy_row(src_wb.active, dest_wb.active, src_row_idx=2, dest_row_idx=2)

dest_wb.save("destination.xlsx")

The has_style guard skips empty cells that have never been styled, which avoids creating unnecessary style entries in the output file and keeps the file size down.

Copying an Entire Range of Rows

If you need to transfer many rows, wrap the single-row function in a loop and offset the destination index as needed:

def copy_rows(src_ws, dest_ws, src_start=1, src_end=None, dest_start=1):
    """
    Copy rows src_start..src_end from src_ws into dest_ws
    starting at dest_start. Preserves values and styles.
    """
    if src_end is None:
        src_end = src_ws.max_row

    offset = dest_start - src_start
    for row_idx in range(src_start, src_end + 1):
        copy_row(src_ws, dest_ws, row_idx, row_idx + offset)


src_wb  = load_workbook("template.xlsx")
dest_wb = load_workbook("report.xlsx")

copy_rows(src_wb.active, dest_wb.active, src_start=1, src_end=50, dest_start=1)
dest_wb.save("report.xlsx")

Handling Merged Cells and Row Dimensions

Style copying alone does not preserve merged cell ranges or row heights. You need to handle those separately.

Row Heights

Row height is stored on the worksheet's row_dimensions dictionary, not on individual cells:

from openpyxl.worksheet.dimensions import RowDimension

def copy_row_dimensions(src_ws, dest_ws, src_row_idx, dest_row_idx):
    src_rd = src_ws.row_dimensions.get(src_row_idx)
    if src_rd:
        dest_ws.row_dimensions[dest_row_idx].height = src_rd.height
        dest_ws.row_dimensions[dest_row_idx].hidden = src_rd.hidden

Merged Cells

Merge ranges are tracked on the worksheet object. When you copy rows you need to translate any merges that fall within the copied range to the destination:

def copy_merged_cells(src_ws, dest_ws, src_start, src_end, dest_start):
    offset = dest_start - src_start
    for merge in src_ws.merged_cells.ranges:
        # Only copy merges that are fully within the copied row range
        if merge.min_row >= src_start and merge.max_row <= src_end:
            new_merge = merge.coord  # e.g. "B2:D2"
            # Shift row numbers
            from openpyxl.utils import get_column_letter, column_index_from_string
            import re
            def shift_row(match):
                col = match.group(1)
                row = int(match.group(2)) + offset
                return f"{col}{row}"
            shifted = re.sub(r'([A-Z]+)(\d+)', shift_row, new_merge)
            dest_ws.merge_cells(shifted)

This regex-based shift is intentionally simple. For production use you may want to parse ranges with openpyxl.utils.cell.range_boundaries instead.

Copying Named Styles Across Workbooks

Named styles (created with NamedStyle) are registered at the workbook level. If cells in the source use a named style and that name does not exist in the destination workbook, openpyxl will raise a KeyError or silently fall back to default formatting.

The cleanest solution is to register any missing named styles in the destination before copying cells:

from openpyxl.styles import NamedStyle
from copy import copy

def transfer_named_styles(src_wb, dest_wb):
    """Copy named styles from src_wb to dest_wb if they don't already exist."""
    existing = {s.name for s in dest_wb.named_styles}
    for style in src_wb._named_styles:
        if style.name not in existing:
            new_style = NamedStyle(name=style.name)
            new_style.font      = copy(style.font)
            new_style.fill      = copy(style.fill)
            new_style.border    = copy(style.border)
            new_style.alignment = copy(style.alignment)
            new_style.number_format = style.number_format
            dest_wb.add_named_style(new_style)


transfer_named_styles(src_wb, dest_wb)
# Now copy rows — named styles will resolve correctly
copy_rows(src_wb.active, dest_wb.active)

Call transfer_named_styles before any cell copying. Once the named styles are registered in the destination workbook, cells that reference them by name will render correctly in Excel.

Common Pitfalls and Gotchas

Column Widths Are Not Copied by Default

Column widths live in ws.column_dimensions, similar to row heights. If your layout depends on specific column widths, iterate over src_ws.column_dimensions and mirror the width and hidden values to dest_ws.column_dimensions.

Formulas vs. Cached Values

When the source cell contains a formula, src_cell.value is the formula string (e.g., =SUM(A1:A10)). Copying that string to the destination works, but the cell reference may point to the wrong sheet. If you want the computed result instead, open the source workbook with data_only=True. The article on fixing openpyxl reading formulas instead of values covers this in depth.

Styles Are Not Always Stored on the Cell

openpyxl is lazy — a cell that was never explicitly styled has no style object at all. The has_style property returns False for those cells. Calling copy(src_cell.font) on an unstyled cell returns the default font, which is harmless but writes unnecessary style entries. The guard in copy_cell_style above prevents this.

Using load_workbook with keep_vba=True

If your source workbook contains macros and you open it with keep_vba=True, the workbook is saved as .xlsm. Make sure your destination filename also uses the .xlsm extension; saving macro-enabled content as .xlsx silently strips the VBA and can corrupt style tables.

Thread Safety

openpyxl workbook objects are not thread-safe. If you are processing multiple files concurrently — for example in a ThreadPoolExecutor — create one workbook instance per thread. Do not share a single workbook across threads even if each thread works on a different sheet. This is a common source of corrupted output files that looks unrelated to styles but ultimately traces back to shared mutable state.

The Wrong Sheet Problem

When loading a workbook, wb.active returns whichever sheet was last active when the file was saved in Excel. If you are building automation that processes sheets by name, always access sheets by name explicitly: wb["Sheet1"]. The article on fixing openpyxl writing data to the wrong sheet tab details why wb.active can surprise you.

If you are building more complex Excel tooling and run into data integrity issues on the database side of the pipeline, the same discipline of understanding how objects are bound and referenced applies — see the write-up on fixing sqlite3 stale data after a commit for a parallel example in a different library.

Next Steps

You now have a reliable foundation for copying rows between openpyxl workbooks without losing formatting. Here are concrete actions to take from here:

1. Drop the helper functions (copy_cell_style, copy_row, copy_rows, transfer_named_styles) into a shared excel_utils.py module in your project so you never re-solve this problem.
2. Add a round-trip test: create a workbook with known styles, copy a row to a new workbook, reload it with openpyxl, and assert that font name, fill color, and border style match. This catches regressions if you upgrade openpyxl.
3. Handle column widths by iterating src_ws.column_dimensions and mirroring each width to the destination worksheet.
4. Audit for named styles in your source files — run print([s.name for s in wb.named_styles]) before copying to know what needs transferring.
5. Profile file size after copying large sheets; if the output file is unexpectedly large, you may be writing duplicate style entries — the has_style guard and avoiding re-copying default styles will shrink it back down.