Python-Openpyxl-在一列多页中搜索字符串值。如何在for循环中检测Null、None、False

Question

搜索Excel文件字符串使用Openpyxl-（Python 3.10）

我正在搜索一个字符串，在列A中，在四个工作表中。如果在工作表中找到单元格值，则脚本可以工作。

我想知道如何确定细胞是否不存在？（这样我就可以使用一个替代字符串来搜索）。

脚本（我附上了结果的照片）

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell
import colorama
from colorama import init
init()
from colorama import Fore, Style, Back
import os
import shutil
import math
from decimal import Decimal
from openpyxl.styles import Alignment
from inspect import currentframe
import time

########################################
#########################################

src_path ="/Users/data/"
xlfileName = "test.xlsx"
xlfile = src_path + xlfileName
wb = Workbook()
wb = load_workbook(xlfile)

#########################################
#########################################

def get_linenumber():
    cf = currentframe()
    return cf.f_back.f_lineno

#########################################
#########################################

#########################################
#########################################

for xws in wb.sheetnames:
    worksheet = wb[xws]
    print(Style.RESET_ALL)
    print(Fore.BLUE, "Looping Through Worksheet Name -- ", worksheet, "Script line is -- ", get_linenumber())
    print(Style.RESET_ALL)
    for cell in worksheet['A']:
        search_string33 = "Current debt"  #
        search_string34 = "test"  #
        try:
            if search_string33 == cell.value:
                offset_colB_CD = cell.offset(row=0, column=1)
                print(Fore.RED, ( "Line 52 -- worksheet -- ", worksheet, "Searching for --", search_string33, "isinstance --", isinstance(search_string33, type(None))))
                if(offset_colB_CD.coordinate is None):
                    print("Line 54 -- null value detected in -- ", worksheet)
                elif(offset_colB_CD.coordinate is not None):
                    print(Fore.LIGHTRED_EX, "Line 56 -- worksheet -- ", worksheet," -- cell cordinate found --", offset_colB_CD.coordinate, " -- cell value found --", offset_colB_CD.value )
                    print(Style.RESET_ALL)
                else:
                    print("Line 59 if statement passed by on else")
                time.sleep(5)
        except (AttributeError, TypeError):
            continue

Answer 1

我能想到的一个快速解决方案是按照您想要搜索的顺序迭代字符串列表。

例子：

search_strings = ["Current debt", "test"]

for xws in wb.sheetnames:
    worksheet = wb[xws]
    print(Style.RESET_ALL)
    print(Fore.BLUE, "Looping Through Worksheet Name -- ", worksheet, "Script line is -- ", get_linenumber())
    print(Style.RESET_ALL)

    current_search_index = 0
    found_cell = False
    for current_string in search_strings:
        for cell in worksheet['A']:
            try:
                if current_string == cell.value:
                    offset_colB_CD = cell.offset(row=0, column=1)
                    print(Fore.RED, ( "Line 52 -- worksheet -- ", worksheet, "Searching for --", current_string, "isinstance --", isinstance(current_string, type(None))))
                    if(offset_colB_CD.coordinate is None):
                        print("Line 54 -- null value detected in -- ", worksheet)
                    elif(offset_colB_CD.coordinate is not None):
                        print(Fore.LIGHTRED_EX, "Line 56 -- worksheet -- ", worksheet," -- cell cordinate found --", offset_colB_CD.coordinate, " -- cell value found --", offset_colB_CD.value )
                        print(Style.RESET_ALL)
                        found_cell = True
                    else:
                        print("Line 59 if statement passed by on else")
                    time.sleep(5)
            except (AttributeError, TypeError):
                continue
        if found_cell == False:
            current_search_index += 1
        else:
            break