使用OpenPyxl复制粘贴列范围 [英] Copy paste column range using OpenPyxl

问题描述

我想将W7:W46列复制并粘贴到另一个工作表中.我到目前为止的代码，

Hi so I am trying to copy and paste W7:W46 column into another worksheet. The code I have so far,

col_j = New_Burden['W']
for idx, cell in enumerate(col_j,1):
    ws1.cell(row = idx, column = 10).value = cell.value

可以复制整个列，但不幸的是，它也可以传输各种标题.我尝试过的一种解决方案是:

is able to copy over the entire column, but unfortunately transfers the various headers as well. One solution I have tried is:

for row in New_Burden['W7:W46']:
    for cell in row:
        ws1.cell(row = 2, column = 10).value = cell.value

但这只会复制W7的第一个值

But that only copies the first value of W7

推荐答案

将范围(['W7:W46'])从一个工作表复制到另一个工作表:
如果范围不重叠，则也可能在同一工作表中.

Copy a Range(['W7:W46']) from one Worksheet to another Worksheet:
If the Ranges are not overlapping, it's also possible in the same Worksheet.

from openpyxl import Workbook
# Create a new Workbook
wb = Workbook()
ws = wb.worksheets[0]

from openpyxl.utils import range_boundaries
# Define start Range(['J2']) in the new Worksheet
min_col, min_row, max_col, max_row = range_boundaries('J2')

# Iterate Range you want to copy
for row, row_cells in enumerate(New_Burden['W7:W46'], min_row):
    for column, cell in enumerate(row_cells, min_col):
        # Copy Value from Copy.Cell to given Worksheet.Cell
        ws.cell(row=row, column=column).value = cell.value

---

如果您要对多个不同的列执行上述操作， 在function中使用以上内容:

---

If you want to do the above with multiple different Columns, use the above in a function:

def copy_range(source_range, target_start):
    # Define start Range(target_start) in the new Worksheet
    min_col, min_row, max_col, max_row = range_boundaries(target_start)

    # Iterate Range you want to copy
    for row, row_cells in enumerate(New_Burden[source_range], min_row):
        for column, cell in enumerate(row_cells, min_col):
            # Copy Value from Copy.Cell to given Worksheet.Cell
            ws.cell(row=row, column=column).value = cell.value

for source_range, target_start in [('W7:W46','J2'), ('Y7:Y46','A2')]:
    copy_range(source_range, target_start)

使用Python测试:3.4.2-openpyxl:2.4.1-LibreOffice:4.3.3.2

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2

这篇关于使用OpenPyxl复制粘贴列范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！