Python3 openpyxl将包含特定值的行中的数据复制到现有工作簿中的新工作表 [英] Python3 openpyxl Copying data from row that contains certain value to new sheet in existing workbook

问题描述

我正在尝试将其中具有特定值的行复制到新的工作表中.通过查看下面列出的参考资料，我能够确定如何使用ws.append复制，但是在脚本中无法正常运行.我想做的是遍历wb，如果它具有一定的值，则创建另一个工作表并将该行复制到所述工作表.对于此方面的任何帮助，我们将不胜感激，因为我没有取得预期的结果.我唯一要做的就是添加一些elif条件以及其他条件，但是它并没有遍历工作表并复制预期的结果.它正在复制其中一个行，但不复制每个条件的预期行，并将其复制到与w(不是在条件中创建的新工作表)同名的新文件中.

I am attempting to copy a row with a certain value in it to a new sheet. In viewing the reference listed below I was able to identify how to copy with ws.append however that is not functioning properly in the script. What I would like to do is iterate over the wb and if it has a certain value, create another sheet and copy that row to said sheet. I would appreciate any assistance on this as I am not producing the expected results. The only thing I am doing are adding some elif conditions as well as a else but it is not iterating through the worksheet and copying over the intended results. It is copying one of the rows but not the intended row for each conditional and copying it to a new file with the same name as ws not the new sheet that is being created in the conditional.

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.utils import range_boundaries
from sys import argv
script, inpath, outpath = argv

min_col, min_row, max_col, max_row = range_boundaries("A:M")
wb = load_workbook(inpath)
print("File Loaded")
ws = wb.get_sheet_by_name("value")

check = 0 # == A

for row in ws.iter_rows():
    if row[check].value == 'value':
        try:
            ws1 = wb.create_sheet("value", 1)
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
            ws1.append((cell.value for cell in row[min_col-1:max_col]))
        except:
            print("words")
    elif row[check].value == 'value':
        try:
            ws2 = wb.create_sheet("value", 2)
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
            ws2.append((cell.value for cell in row[min_col-1:max_col]))
        except:
            print("moar words")

    elif row[check].value == 'value':
        try:
            ws3 = wb.create_sheet("value", 3)
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
            ws3.append((cell.value for cell in row[min_col-1:max_col]))
        except:
            print("words")
    else:
        try:
            ws4 = wb.create_sheet("value", 4)
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
            ws4.append((cell.value for cell in row[min_col-1:max_col]))
        except:
            print("words")


wb.save(outpath + '.xlsx')

如果我运行以下内容

for row in ws.iter_rows():
    if row[check].value == 'Info':

    try:
        #ws1.cell(row=1, column=1).value = ws1.cell(row=1, column=1).value
        ws1 = wb.create_sheet("Info", 1)
        ws1 = wb.active
    # Copy Row Values
    # We deal with Tuple Index 0 Based, so min_col must have to be -1
        ws1.append((cell.value for cell in row[min_col-1:max_col]))
    except:
        print("Words")



wb.save(outpath + '.xlsx')

它运行我需要的查询，在这种情况下，每行中都有单词Info，但它不会创建新的工作表，也不会将值附加到新的工作表中.它保留与原始图纸相同的图纸名称.添加elif或else语句无法正常运行.当所有这些一起运行时.它只会在原始excel文件中创建带有标题信息和行号的大量工作表.我已经将if语句中的值更改为info以外的其他值，它仍然输出包含Info的行.

It runs the query I need, in this case every row that has the word Info in it, but it does not create a new sheet and it doesn't append the values to the new sheet. It keeps the same sheet name as the original. Adding the elif or else statements dont function properly. When that is all run together. It just creates tons of sheets with the title Info and the row number in the original excel file. I have changed the value in the if statement to something other than info and it still outputs the rows that contain Info.

参考: Openpyxl:如何在检查单元格是否包含特定值后复制行

推荐答案

因此，通过与同事的讨论，我发现问题在于在for循环中创建工作表，从而为每个单元格创建了一个工作表具有指定的值.以下内容已经过测试，可以完美运行.

So through a discussion with a co-worker I was able to find out the that issue was creating the worksheets in the for loop created a worksheet for every cell that had the specified value. The following has been tested and works perfectly.

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.utils import range_boundaries
from sys import argv
script, inpath, outpath = argv

min_col, min_row, max_col, max_row = range_boundaries("A:M")
wb = load_workbook(inpath)
print("File Loaded")
ws = wb.get_sheet_by_name("Report")

interested_column = 6 # == G

ws1 = wb.create_sheet("Value", 1)
ws2 = wb.create_sheet("Value", 2)
ws3 = wb.create_sheet("Value", 3)
ws4 = wb.create_sheet("Value", 4)

for row in ws.iter_rows():
    if row[interested_column].value == 'Value':
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
        ws1.append((cell.value for cell in row[min_col-1:max_col]))
    elif row[interested_column].value == 'Value':
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
        ws2.append((cell.value for cell in row[min_col-1:max_col]))
    elif row[interested_column].value == 'Value':
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
        ws3.append((cell.value for cell in row[min_col-1:max_col]))
    else:
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
        ws4.append((cell.value for cell in row[min_col-1:max_col]))

wb.save(outpath + '.xlsx')

这篇关于Python3 openpyxl将包含特定值的行中的数据复制到现有工作簿中的新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！