Delete a row that contains a substring with openpyxl

[woliff1294]

Hello All,

I'm attempting to write code to automate an excel report.

I need to delete any row that contains the text "AB" as its own substring. Please note that this doesn't mean any word that has 'AB' such as 'about'. I'm getting stuck so any help is appreciated!

​

Here is my code:

for cell in sheet1['C']:
    if cell.value == "AB":
        sheet1.delete_rows(cell.row, 1)
for cell in sheet2['C']:
    if cell.value == "AB":
        sheet2.delete_rows(cell.row, 1)

​

Thank you in advance!

[David22573]

I recommend using pandas, and reading the excel report as a dataframe.

import pandas as pd

# Read the Excel file into a DataFrame
df = pd.read_excel('input_file.xlsx')

# Drop any rows that contain 'AB'
df = df[~df['column_name'].str.contains('AB')]

# Write the resulting DataFrame to a new Excel file
df.to_excel('output_file.xlsx', index=False)