search coordinates of the current cell

[Yves Larbodiere]

good morning,

I have a dictionary whose key is a string of 2 characters, and whose value is an amount.

Using an Excel sheet from a sample workbook, I'd like to find the cell containing a key from the dictionary and copy the corresponding value into the +1 column.

What's the simplest solution?

The code below doesn't return the coordinates of the current cell.

Thank you for your help.

import openpyxl
workbook = openpyxl.load_workbook('/Users/yves/documents_1/test_AWS_textractor/dataframes.xlsx')
workbook['2050']
valeur = "AT"

for row in workbook['2050'].rows:
    for cell in row:
        cval = cell.value
       
        if cval == valeur:
            cell.coordinate

[Charlie Clark]

On 24 Apr 2024, at 11:07, Yves Larbodiere wrote:

What's the simplest solution?

Well, apart from not needing to use ".rows", your code looks reasonable.

The code below doesn't return the coordinates of the current cell.

What do you mean by this? It's not a function, so it can't return anything. Do you want the cell coordinate to be displayed? Other than that, you can use the cell offset method to locate any cell relative to another.

for row in ws.iter_rows(min_row=1, min_col=1):
   for cell in row:
       if cell.value == key:
           cell.offset(column=1).value = key

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Yves Larbodiere]

Thanks a lot Charlie,

Do you want the cell coordinates to be displayed? Yes, to check my code.

here's what I've just done without any feedback.

import openpyxl
workbook = openpyxl.load_workbook('/Users/yves/documents_1/test_AWS_textractor/dataframes.xlsx')

ws = workbook['2050']
key = "AT"

for row in ws.iter_rows(min_row=1, min_col=1):
   for cell in row:
       if cell.value == key:
           cell.offset(column=1).value = key

           print(cell.coordinate)

[Charlie Clark]

On 24 Apr 2024, at 12:39, Yves Larbodiere wrote:

Thanks a lot Charlie,

Do you want the cell coordinates to be displayed? Yes, to check my code.

here's what I've just done without any feedback.

What you do mean "without any feedback"? You are not seeing anything printed? In this case the value is never matching and you need to look at the values in greater detail. As I don't have the file I can't really say but whitespace is often a real problem with data.

Let's assume the row for your test code is 4 and the column you expect do find is C

for row in ws.iter_rows(min_row=4, max_col=3, values_only=True):
    print(row)

(None, " ", "AT ",)

This wouldn't match because "AT " is not "AT". Printing strings as part of a list is good way to make sure whitespace is visible that might otherwise not be the case.

[Yves Larbodiere]

Hello Charlie,

the code below allows me to retrieve the correct cell (value and position). However, when I type the line “cell.offset(column=1).value = key” it returns an error because cell is a string.

Thanks again for your help.

import openpyxl
wb = openpyxl.load_workbook('/Users/yves/documents_1/test_AWS_textractor/liasse-fiscale-excel-outils.xlsx')
ws = wb['2050']
key = "AT"

for row in ws.iter_rows(min_row=4, max_col=10, values_only=True):
    for cell in row:
        if cell == key:
           print(cell)
           position_valeur = ws.active_cell
           print(position_valeur)          
           break

[Charlie Clark]

On 25 Apr 2024, at 17:01, Yves Larbodiere wrote:

> Hello Charlie,
>
> the code below allows me to retrieve the correct cell (value and position). However,
> when I type the line “cell.offset(column=1).value = key” it returns an
> error because cell is a string.

Your description does not match your code.

[Yves Larbodiere]

Charlie,

you're right. I went back over the code and reread the documentation to generate the following code, which works.

Thank you very much for your concern.

Kind regards

import openpyxl
wb = openpyxl.load_workbook('/Users/yves/documents_1/test_AWS_textractor/liasse-fiscale-excel-outils.xlsx')
ws = wb['2050']

key = "AU"

for row in ws.iter_rows(min_row=2, min_col=3):
    for cell in row:
        if cell.value == key:
            print(cell.coordinate,cell.value)
            cell.offset(column=1).value = key
           
            break
wb.save('test1.xlsx')