在openpyxl中，如何使用格式设置，合并的单元格，公式和超链接移动或复制单元格范围 [英] In openpyxl, how to move or copy a cell range with formatting, merged cells, formulas and hyperlinks

问题描述

我正在尝试使用Python 2.7 + openpyxl在工作表中移动单元格区域.看来是一件容易且基本的任务，几乎是不可能的.这就是我希望它在Excel中显示的样子:

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:

为了简化任务，我们假设我只需要将范围从给定的单元格移到数据的末尾(到最后一行和最后一列).我的第一个想法很简单:

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)

但是，嘿，如何有效而充分地实现copy_cell? 通过反复试验，我设法得出了我需要复制的4件事:

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. 值
2. 样式
3. 超链接
4. 数字格式

它没有按我预期的那样工作-大多数单元格都被正确复制，但是超链接似乎不起作用，复制.style属性也不起作用(因此我尝试访问_style起作用)并且最糟糕问题出现了-合并的单元格不等.怎么对付他们?我的copy_cell()目前看起来像这样:

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?

推荐答案

以下方法对我有用，您还可以指定不同的工作表:

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.

这篇关于在openpyxl中，如何使用格式设置，合并的单元格，公式和超链接移动或复制单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！