Openpyxl-将行范围从工作表传输到另一个

问题描述

我想从一张纸上取一部分数据并将其复制到另一张纸上.

I want to take a certain part of data from a sheet and copy it to another sheet.

到目前为止，我有一本字典，键为开始行，值为结束行.

So far, I have a dictionary with key as start row and value as end row.

使用此功能，我想执行以下操作:

Using this, I would like to do the following:

-从sheet0获取第一个范围，并将其附加到sheet1

-Get the first range from sheet0 and append it to sheet1

-从sheet0获取第二个范围，并将其附加到sheet2

-Get the second range from sheet0 and append it to sheet2

-从sheet0获取第三个范围，并将其附加到sheet3

-Get the third range from sheet0 and append it to sheet3

我尝试了以下操作:

#First range starts at 1 and ends at 34, second range from 34-52 and third from 52-75
myDict = {1: 34, 34: 52, 52: 75}

#store all the sheets, ignoring main sheet
sheet = wb.worksheets[1:]


for item in myDict:
    for col in ws.iter_cols(min_row=item, max_row=myDict[item], min_col=1 , max_col=ws.max_column):
        for cell in col:
            for z in sheet:
                z.append(col)

另一种方法是使用函数和列表:

Another approach was to use a function and lists:

startRow=[1,34,52]
endRow=[34,52,75]

def addRange(first, second):
    for col in ws.iter_cols(min_row=first, max_row=second, min_col=1 , max_col=ws.max_column):
        for cell in col:
            for z in sheet:
                z.append(col)

#Call function
for start, end in zip(startRow, endRow):
    addRange(start, end)

但是在两种情况下，我都收到以下错误"ValueError:无法从其他工作表复制单元格"

But on both occasions, I get the following error "ValueError: Cells cannot be copied from other worksheets"

有人知道我在这里想念的东西吗?

Does anyone have a clue on what am I missing in here?

提前谢谢！

推荐答案

from openpyxl import load_workbook
from itertools import product

filename = 'wetransfer-a483c9/testFile.xlsx'
wb = load_workbook(filename)

sheets = wb.sheetnames[1:]

其中 sheets 为 ['Table 1'，'Table 2'，'Table 3']

# access the main worksheet
ws = wb['Main']

首先，获取每个表的边界(起点/终点)

First, get the boundaries (start-/endpoint) for each Table

span = []
for row in ws:
    for cell in row:
        if (cell.value
                and (cell.column == 2)  # restrict search to column2, which is where the Table entries are
                # this also avoids the int error, since integers are not iterable
                and ("Table" in cell.value)):
            span.append(cell.row)

# add sheet's length -> allows us to effectively capture the data boundaries
span.append(ws.max_row + 1)

结果 span : [1、29、42、58]

第二，获取边界的配对. +1 确保在捕获表并将其转换为 string 格式时包含结尾由于openpyxl以字符串形式引用边界，并具有1的索引符号，而不是加1，因此必须将其取掉.

Second, get the pairing of boundaries. +1 ensures the end is included when capturing the tables and convert them to string formatSince openpyxl refers to the boundaries in string form and has a 1 index notation, instead of adding 1, you have to take one off.

boundaries = [":".join(map(str,(start, end-1)))  for start, end in zip(span,span[1:])]

结果边界: ['1:28'，'29:41'，'42:57']

第三，创建主工作表，边界和其他工作表的笛卡尔坐标.请注意，边界和图纸是压缩的-本质上它们是一对.因此，我们将每个表与一个边界配对:

Third, create a cartesian of the main sheet, the boundaries and the other sheets. Note that boundaries and sheets are zipped - essentially they are a pair. As such, we paired each table with a boundary:

#table 1 is bound to 1:28,
#table 2 is bound to 29:41, ...

接下来，我们将主表与该对组合，因此主表与(表1，1:28)配对.相同的主表与(表2，29:41)...

Next, we combine the main sheet with the pair, so main sheet is paired with (table 1, 1:28). The same main sheet is paired with (table 2, 29:41) ...

第四，获取范围内的数据.由于我们已经成功地将主表与每对表格和边界配对，因此我们可以安全地获取该特定区域的数据并将其移至特定表格.

Fourth, get the data within the ranges. Since we have successfully paired the main sheet with every pair of table and boundary, we can safely get the data for that particular region and shift it to the particular table.

所以主表中的表1 引用 1:28 ，因为它绑定到此特定表.用 table 1 完成操作后，它返回循环并从"Table 2"开始，仅选择"29:41".因为这是本节的限制，依此类推.

So table 1 in the main sheet refers to 1:28, since it is bound to this particular table. When it's done with table 1, it returns to the loop and starts at "Table 2", selecting only "29:41" since this is the limit in this section, and so on.

for main,(ref, table) in product([ws],zip(boundaries, sheets)):

    sheet_content = main[ref]
    # append row to the specified table

    for row in sheet_content:
        #here we iterate through the main sheet
        #get one row of data
        #append it to the table
        #move to the next row, append to the table beneath the previous one
        #and repeat the process till the boundary has been exhausted
        wb[table].append([cell.value for cell in row])

最后，保存文件.

wb.save(filename)

这篇关于Openpyxl-将行范围从工作表传输到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！