Python Forum

I am completely new to openpyxl so, as you can imagine, I am having pretty hard times when I try to make use of it.

I have an Excel report that contains only one sheet (called Sheet1). I'd like to search all cells in one column (column H) for a specific string ("OK" and "QNS") and change that "OK" to "In Inventory" and "QNS" to "Exhausted".

I think my main confusion is exactly what to employ here. Is it a while loop combined with if/elif? Or do I need to iterate? Please help me understand how to even begin doing this. Thanks.

Omg I think I got it to work. Does this look valid/okay/makes sense?

wb = load_workbook('C:/Users/Me/AppData/Local/Programs/Python/Python310/March.xlsx')
ws = wb.active

conditionUpdate = {"OK": "In Inventory","Discrepancy: Subject": "In Inventory"}

for rowNum in range(2, ws.max_row):
    condition = ws.cell(row=rowNum, column=8).value
    if condition in conditionUpdate:
        ws.cell(row=rowNum, column=8).value = conditionUpdate[condition]

There's a good tutorial here: https://openpyxl.readthedocs.io/en/latest/tutorial.html

Something like this maybe??

wb = load_workbook('C:/Users/Me/AppData/Local/Programs/Python/Python310/March.xlsx')
ws = wb.active

'''
I'd like to search all cells in one column (column H) for a specific string ("OK" and "QNS")
and change that "OK" to "In Inventory" and "QNS" to "Exhausted".
'''

for rowNum in range(2, ws.max_row):
    state = ws.cell(row=rowNum, column=8).value
    if state == 'OK':
        ws.cell(row=rowNum, column=8, value='In Inventory')
    elif state == 'QNS':
        ws.cell(row=rowNum, column=8, value='(I am) Exhausted')

Why bother putting OK or QNS in the first place??

(Mar-30-2022, 05:09 AM)Pedroski55 Wrote: [ -> ]Something like this maybe??

wb = load_workbook('C:/Users/Me/AppData/Local/Programs/Python/Python310/March.xlsx')
ws = wb.active

'''
I'd like to search all cells in one column (column H) for a specific string ("OK" and "QNS")
and change that "OK" to "In Inventory" and "QNS" to "Exhausted".
'''

for rowNum in range(2, ws.max_row):
    state = ws.cell(row=rowNum, column=8).value
    if state == 'OK':
        ws.cell(row=rowNum, column=8, value='In Inventory')
    elif state == 'QNS':
        ws.cell(row=rowNum, column=8, value='(I am) Exhausted')

Why bother putting OK or QNS in the first place??

I like this as well. I am happy that my previous solution worked too. But, we are exporting a large amount of data from our laboratory database and it has a limited number of valid selections. But we have to send an inventory to one of our clients every month, and they are very picky about how the Excel file is formatted....gotta love it

Gotta keep the client happy!!

I believe, for large amounts of data, Pandas may be better than openpyxl.