+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Openpyxl-change value of cells in column based on value that currently occupies cells (/thread-36780.html)
Openpyxl-change value of cells in column based on value that currently occupies cells - phillipaj1391 - Mar-29-2022
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.
RE: Openpyxl-change value of cells in column based on value that currently occupies cells - phillipaj1391 - Mar-29-2022
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]
RE: Openpyxl-change value of cells in column based on value that currently occupies cells - Larz60+ - Mar-29-2022
There's a good tutorial here: https://openpyxl.readthedocs.io/en/latest/tutorial.html
RE: Openpyxl-change value of cells in column based on value that currently occupies cells - Pedroski55 - Mar-30-2022
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??
RE: Openpyxl-change value of cells in column based on value that currently occupies cells - phillipaj1391 - Mar-30-2022
(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
RE: Openpyxl-change value of cells in column based on value that currently occupies cells - Pedroski55 - Mar-30-2022
Gotta keep the client happy!!
I believe, for large amounts of data, Pandas may be better than openpyxl.