Let's say I have an excel spreadsheet like the example below. How can I use openpyxl check whether the last row contains the string "xyz" in column 1? For context, the end use case will be to send an email if the last row contains xyz

import smtplib
import sys
import openpyxl

wb = openpyxl.load_workbook("myworkbook.xlsx")
ws = wb.get_sheet_by_name("myworksheet")

count = 0
for row in ws:
    if not all([cell.value == None for cell in row]):
        count  = 1

max_row = count-1

if "xyz" in row[max_row].value:
    print("hi")

Edit: The max row in the previous version was not working because it always returned row 999 for some reason. With the new code, it returns the correct max row. Now the problem is with the row[max_row].value. I'm getting TypeError: argument of type 'NoneType' is not iterable, because "row" is NoneType. xyz is string and max_row is int, the problem is with row

Also removed the for loop and just ended it with print hi for simplicity, since I am not asking about the last part.

CodePudding user response：

When calling row[max_row] you are trying get max_row-th column of row, not last row of the worksheet. Your should point to specific column of last row of sheet, i.e. using cell function from openpyxl (https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.cell):

if 'xyz' in str(ws.cell(row=max_row, col=2).value):
    print('hi')

CodePudding user response：

Thanks both, this is what ended up working.

import openpyxl

wb = openpyxl.load_workbook("myworkbook.xlsx")
ws = wb.get_sheet_by_name("myworksheet")

count = 0
for row in ws:
    if not all([cell.value == None for cell in row]):
        count  = 1

max_row = count-1    

if "xyz" in ws.cell(max_row, 2).value:
    print("hi")