In openpyxl, how to move or copy a cell range with formatting, merged cells, formulas and hyperlinks

Solution 1:

The following methods work for me, you can also specify a different worksheet:

from copy import copy

def copy_cell(source_cell, coord, tgt):
    tgt[coord].value = source_cell.value
    if source_cell.has_style:
        tgt[coord]._style = copy(source_cell._style)
    return tgt[coord]

You can call it with the following:

copy_cell(worksheet['E6'], 'D11', worksheet)

Or if you instead need to move a cell, you can do this:

def move_cell(source_cell, coord, tgt):
    tgt[coord].value = source_cell.value
    if source_cell.has_style:
        tgt[coord]._style = copy(source_cell._style)

    del source_cell.parent._cells[(source_cell.row, source_cell.col_idx)]

    return tgt[coord]

Nevertheless, notice that the merge cells have to be done separatedly.

Solution 2:

I have built this method to copy cells without touching their contents:

import copy

def move_cell(source_cell, dest_row, dest_col, preserve_original=False):
    """
    :param source_cell: cell to be moved to new coordinates
    :param dest_row: 1-indexed destination row
    :param dest_col: 1-indexed destination column
    :param preserve_original: if True, does a copy instead of a move
    """
    if preserve_original:
        cell_to_move = copy.copy(source_cell)
    else:
        cell_to_move = source_cell

    worksheet = cell_to_move.parent
    source_address = (cell_to_move.row, cell_to_move.col_idx)
    dest_address = (dest_row, dest_col)

    cell_to_move.row = dest_row
    cell_to_move.col_idx = dest_col
    worksheet._cells[dest_address] = cell_to_move
    if not preserve_original:
        del worksheet._cells[source_address]

    return cell_to_move