How to update cells if row name is X And if column header is Y using openpyxl

Question

I have an excel report and I want to update cells if row name is X And if column header is Y.

I have 53 columns with date, and 102 rows with names, so it's impossible to use 53 lines of code for each column, and 102 lines of code for each row, so I need code that checks if the row's value is for example SFR BOX HBD ECO THD and column header is 2022,10,31 then update the cell in this position.

from openpyxl import load_workbook

Wb = load_workbook('file.xlsx') 
Ws = wb['VD CONQUETE DC'] 
for rownum in range(2, Ws.max_rows):
    statusCol = Ws.cell(row=rownum, column=3).value 
    if statusCol == 'SFR BOX HBD ECO THD':
        Ws.cell(row=rownum, column='2022,10,31', value=vente1date1)

file photo the code

Just add another loop under the `if` to find the correct column, just the same as you did for the rows — Tomerikoo, Nov 30 '22 at 11:09

Timeless · Answer 1 · 2022-11-30T11:56:38.180

0

The screenshot of your Excel file shows the sheet "CUMUL" while your code describes "VD CONQUETE DC", but anyway, you can find below a proposition to update a value that match a given conditon. Feel free to readapt the code to fit your actual dataset.

from openpyxl import load_workbook
from datetime import datetime

# --- Loading the spreadsheet
wb = load_workbook("file.xlsx")
ws = wb["CUMUL"]

# --- Defining the filters/coordinates
date_name= "31/10/2022"
sales_name = "SFR BOX HBD ECO THD"

# --- Updating the matched value
vente1date1 = "new value"
for num_row in range(2, ws.max_row+1):
    sales_row = ws.cell(row=num_row, column=2) #Assuming that the sales are located in column B (thus, column=2)
    if sales_row.value == sales_name:
        for num_col in range(1, ws.max_column+1):
            date_col = ws.cell(row=1, column=num_col) #Assuming that the dates are located in the first row (thus, row=1)
            if isinstance(date_col.value, datetime):
                date_col_str = date_col.value.strftime("%d/%m/%Y") #Converting parsed date to string
                if date_col_str == date_name:
                    val_row = ws.cell(row=num_row, column=num_col)
                    val_row.value= vente1date1 #Updating the cell value
                    
wb.save("file.xlsx")

If the dates are stored as text in Excel, use this bloc for the update:

for num_row in range(2, ws.max_row+1):
    sales_row = ws.cell(row=num_row, column=2) #Assuming that the sales are in column B (thus, column=2)
    if sales_row.value == sales_name:
        for num_col in range(1, ws.max_column+1):
            date_col = ws.cell(row=1, column=num_col) #Assuming that the dates are located in the first row (thus, row=1)
            if date_col.value == date_name:
                val_row = ws.cell(row=num_row, column=num_col)
                val_row.value= vente1date1 #Updating the cell value

NB: Make sure to keep always a backup/copy of your original Excel file before running any kind of python/openpyxl's script.

edited Nov 30 '22 at 11:56

answered Nov 30 '22 at 11:33

Timeless

22,580
4
12
30

I try it but nothing happend after saving my file, plz can u verify plz – Nisrine Benyagoub Nov 30 '22 at 12:57
It's probably due to the shape of the sheet in your machine. Can you share a screenshot that shows the _columns letters_ and the _row numbers_ in the sheet `CUMUL` ? For that, you need to enable the `Headings` in the view : https://i.stack.imgur.com/RHaOo.png – Timeless Nov 30 '22 at 13:24
I added the screens – Nisrine Benyagoub Nov 30 '22 at 13:51
I can see that you put the right number of columns/rows. However, you didn't use `date1` as a string but as a datetime instead. That's why the value of `vente24date1` hasn't been assigned to the cell in the Excel file. `date1` should be equal to a string `date1= "01/10/2022"` – Timeless Nov 30 '22 at 14:10
In line 11, replace `if date_col_str == date1:` by `if date_col_str == date1.strftime("%d/%m/%Y"):` – Timeless Nov 30 '22 at 14:16
I Replace it in line 11 and still not working, it should put 2 in the cell, – Nisrine Benyagoub Nov 30 '22 at 15:58
Can you `print(ws["L1"].value)` ? If it's `datetime.datetime(2022, 10, 01, 0, 0)`, then use `if date_col_str == date1:` and if it's `'01/10/2022` then use `if date_col_str == date1.strftime("%d/%m/%Y"):`. – Timeless Nov 30 '22 at 17:12
When i print(ws["L1"].value) output =K1+1 – Nisrine Benyagoub Nov 30 '22 at 17:24
And when i print (date_col.value) output None – Nisrine Benyagoub Nov 30 '22 at 17:33
In this case, use `wb = load_workbook("file.xlsx", data_only=True)`. This should do it. – Timeless Nov 30 '22 at 17:46

How to update cells if row name is X And if column header is Y using openpyxl

1 Answers1