无法删除具有特定单元格值的行 python openpyxl [英] Unable to remove rows with specific cell value python openpyxl

问题描述

我遇到了一个奇怪的问题，逻辑和代码告诉我它应该可以工作，但它没有.

I'm having a weird issue where the logic and code tells me it should work but it does not.

我的代码在下面

import shutil, sys
from distutils.version import StrictVersion
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('testing.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
x = ws.max_row
y = ws.max_column

for r in range(1,x+1):
        for j in range(1, y+1):
                d=ws.cell(row=r,column=j)
        if str(d.value).lower() == "false":
                ws.delete_rows(r)

wb.save("test_1.xlsx")

excel 由 5 列组成，A B C D E第一行有标题，所以可以忽略A2 有时间，B2 有名字 C2 有用户名，D2 路径，E2 包含 TRUE 或 FALSE 的值

The excel is made out of 5 columns, A B C D E first Row have titles so can be ignored A2 has time, B2 has name C2 has username,D2 path, E2 contains value of either TRUE or FALSE

我的脚本的重点是查看所有单元格，如果找到 FALSE 的值，它将删除该行.例如第 10 行

The point of my script is to look at all cells and if the value of FALSE is found it will remove that row. So for example row 10

01/01/1999 约翰·史密斯 JohnS/path/FALSE这应该被删除，因为它包含 FALSE，或者更具体地说 E10 包含 FALSE.TRUE FALSE 值仅出现在 E 列中，因此为了速度起见，我们可以指定我们只对 E 列感兴趣，但对任何行感兴趣.我已经在其他版本中做到了.

01/01/1999 John Smith JohnS /path/ FALSE This should be removed as it contains FALSE or more specifically E10 has FALSE. The TRUE FALSE values only appear in column E so for the sake of speed we could specify that we are only interested in column E but any row. I have done that in other version.

解决问题问题是，如上所述，我的测试 excel 共有 25 行和 A B C D E 列，但脚本仅删除了 5 行值为 FALSE.此外，脚本似乎随机删除了包含 FALSE 的行，因此在我的测试 excel 中，总共有 10 行带有 FALSE 单元格.顺序的用户名将是t1、t2、t3、t4、t5、t6、t7、t8、t9、t10但刚刚删除的脚本t1、t3、t5、t6、t7、t9现在看它似乎我在逻辑和检查奇数方面有问题

To the problem The problem is that my testing excel has total of 25 rows and columns A B C D E as stated above but the script only removes 5 rows that had value FALSE. Also it seems the script removes the rows that contain FALSE at random so in my testing excel there are total of 10 rows with FALSE cell. the usernames in order will be t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 but the script just now removed t1, t3, t5, t6, t7, t9 which looking at it now seems I have an issue with logic and its checking odd numbers

编辑似乎如果我重复循环足够多的次数，它将删除所有包含 FALSE 的行

EDIT it seems that if I repeat the loop enough times it will remove all rows that contain FALSE

当前正在运行的代码

import shutil, sys
from distutils.version import StrictVersion
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('testing.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
x = ws.max_row
y = ws.max_column

for r in range(1,x+1):
        for j in range(1, y+1):
                d=ws.cell(row=r,column=j)
        if str(d.value).lower() == "false":
                ws.delete_rows(r)


for r in range(1,x+1):
        for j in range(1, y+1):
                d=ws.cell(row=r,column=j)
        if str(d.value).lower() == "false":
                ws.delete_rows(r)

for r in range(1,x+1):
        for j in range(1, y+1):
                d=ws.cell(row=r,column=j)
        if str(d.value).lower() == "false":
                ws.delete_rows(r)

for r in range(1,x+1):
        for j in range(1, y+1):
                d=ws.cell(row=r,column=j)
        if str(d.value).lower() == "false":
                ws.delete_rows(r)

wb.save("test_1.xlsx")

它不漂亮，所以任何提示将不胜感激

its not pretty so any tips will be appreciated

推荐答案

我觉得是缩进的问题，试试这个:

I think it's the problem of the indentation, try this:

import shutil, sys
from distutils.version import StrictVersion
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('testing.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
x = ws.max_row
y = ws.max_column

for r in range(1,x+1):
    for j in range(1, y+1):
        d=ws.cell(row=x+1-r,column=j)
        if str(d.value).lower() == "false":
            ws.delete_rows(x+1-r)
            break

wb.save("test_1.xlsx")

并且我将行号从 r 更改为 x+1-r，这意味着从最后到第一行迭代行(这样当一行是删除，其余行不受影响)，并且需要打破内循环(因为循环行被删除，你不能再循环这一行)

and I change the row number from r to x+1-r, which means iterating the rows from the last to the first(so that when one row is deleted, the rest rows won't be affected), and It's necessary to break the inner loop(cause the looping row is deleted, you can't loop this row any more)

这篇关于无法删除具有特定单元格值的行 python openpyxl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！