Python Forum
Openpyxl-change value of cells in column based on value that currently occupies cells
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Openpyxl-change value of cells in column based on value that currently occupies cells
#1
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.
Reply
#2
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]
Larz60+ write Mar-29-2022, 08:12 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Fixed for you this time. Please use BBCode tags on future posts.
Reply
#3
There's a good tutorial here: https://openpyxl.readthedocs.io/en/latest/tutorial.html
Reply
#4
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??
Reply
#5
(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
Reply
#6
Gotta keep the client happy!!

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  getting unexpected indent errors trying to move cells up jensengt 4 822 Jun-28-2023, 12:05 PM
Last Post: deanhystad
  create new column based on condition arvin 12 2,133 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 798 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  Converting cells in excel to JSON format desmondtay 4 1,686 May-23-2022, 10:31 AM
Last Post: Larz60+
Question Change elements of array based on position of input data Cola_Reb 6 2,063 May-13-2022, 12:57 PM
Last Post: Cola_Reb
  Find last filled column in openpyxl Irv1n 2 12,554 Jan-16-2022, 11:05 AM
Last Post: Pedroski55
  gspread - applying ValueRenderOption to a range of cells dwassner 0 1,672 Jan-12-2022, 03:05 PM
Last Post: dwassner
  Python “Formula” Package: How do I parse Excel formula with a range of cells? JaneTan 1 2,640 Jul-12-2021, 11:09 AM
Last Post: jefsummers
Photo Creating column in python based on the other colums value count Bartek635 2 2,892 Apr-15-2021, 03:47 PM
Last Post: Bartek635
  OpenPyxl: How to iterate through each Column (in 1 row) to find a value? chatguy 2 17,842 Apr-06-2021, 04:52 AM
Last Post: carlhyde

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020