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

user_5743194 0
Viewed 11349

I am trying to move a cell range inside a worksheet using Python 2.7 + openpyxl. What seemed to be an easy and basic task turned out to be close to impossible. Here is how I'd like it to look in Excel:

Excel copying pasting

To make the task easier let's assume I only need to move range from given cell to the end of data (to last row and column). My first idea was easy:

for i, column in enumerate(columns):
    if i >= starting_col:
        for j, cell in enumerate(column):
            if j >= starting_row:
                copy_cell(ws, j+1, i+1, j+1, i+movement)

But hey, what how to efficiently and fully realize copy_cell? By trial and error I managed to come to 4 things I need to copy:

  1. Value
  2. Style
  3. Hyperlinks
  4. Number format

It didn't work quite as I expected - most of cells are copied properly but hyperlinks don't seem to work, copying .style attribute didn't work either (hence my try to access _style which worked) and my worst problem appeared - merged cell ranged. How to deal with them? My copy_cell() looks like this at the moment:

def copy_cell(ws, from_row, from_col, to_row, to_col):
    # first value
    ws.cell(row=to_row, column=to_col).value = ws.cell(row=from_row, column=from_col).value
    # second formatting
    from_style = ws.cell(row=from_row, column=from_col)._style
    ws.cell(row=to_row, column=to_col)._style = from_style
    ws.cell(row=to_row, column=to_col).hyperlink = ws.cell(row=from_row, column=from_col).hyperlink
    ws.cell(row=to_row, column=to_col).number_format = ws.cell(row=from_row, column=from_col).number_format

Isn't there a better, generic way to copy whole cell range? Or at least whole cell with all of its attributes? If not, maybe there is an efficient way to move or copy merged cell ranges?

openpyxl excel python
3 Answers

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.

user_10603490

This works for me in version 3.0.9. Although, this only covers copying/moving values and styling.

from copy import copy
from openpyxl.cell.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import get_column_letter

def move_cell(cell: Cell, rows: int, cols: int, preserve_original=False) -> None:
    """Move ``cell`` by ``rows`` and ``cols``. If ``preserve_original`` is True, do copy instead
    of a move.

    .. note:: Anything already present in the new destination gets overwritten.
    """
    new_column = get_column_letter(cell.column + cols)
    new_cell = cell.parent[f"{new_column}{cell.row + rows}"]
    new_cell.value = cell.value
    if cell.has_style:
        new_cell.font = copy(cell.font)
        new_cell.border = copy(cell.border)
        new_cell.fill = copy(cell.fill)
        new_cell.number_format = copy(cell.number_format)
        new_cell.protection = copy(cell.protection)
        new_cell.alignment = copy(cell.alignment)
    if not preserve_original:
        cell.value = ""
        cell.style = "Normal"

def move_range(sheet: Worksheet, range_: str, rowscount: int, colscount: int,
               preserve_original=False) -> None:
    """Move range of cells defined by ``range_`` within the ``sheet`` by ``rowscount`` and
    ``colscount``.

    If ``preserve_original`` is True, this function does copy instead of a move (any overlapping
    gets overwritten).
    """
    for row in sheet[range_]:
        for cell in row:
            move_cell(cell, rowscount, colscount, preserve_original)
user_44657080
Related