Openpyxl get cell number where a value into a range

[Moisés López]

Hi there,

I'm working on a excel file, I need to lookup a vale into a cell 'A1' and it if found give me cell numbers.

I have done a following code:

Loop with for and range, then check with if of row math vale "John".

libro_in = load_workbook("Employee.xlsx")

hoja_in = libro_in['Enero']

for fila in range (4,66):
    
        merchand = hoja_in.cell(row=fila,column=1).value
       
        if merchand == "John":

   

It's very basic code, I guest it should be any methos or funtions to do that easier, like lookup in excel. Any idea will be always wellcome.     

I check this module openpyxl.worksheet.cell_range it may help but not at all.

Thanks so much in advanced

[Charlie Clark]

On 24 Feb 2021, at 16:04, Moisés López wrote:

> libro_in = load_workbook("Employee.xlsx")
> hoja_in = libro_in['Enero']
>
> for fila in range (4,66):
>
> merchand = hoja_in.cell(row=fila,column=1).value
>
> if merchand == "John":

From the tutorial:

https://openpyxl.readthedocs.io/en/latest/tutorial.html#accessing-many-cells

According to your code you're only interested in the first column so
only the row number is important.

for row in ws.iter_rows(min_col=1, max_col=1, min_row=4):
cell = row[0]
if cell.value == "John":
print(cell.row)
break()

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Tvp Canarias]

Dear Charlie,

Sorry for delaying in aswering you about this topic, this help me a lot. Thanks so much in advanced.

from openpyxl import load_workbook

libros = "test.xlsx"
trabajadores = "SONIA MARÍA MONTESDEOCA QUINTANA"

libro_in = load_workbook(libros)
hoja_in = libro_in['Enero']

dimension_excel = hoja_in.dimensions
mi_fila = hoja_in.min_row
ma_fila = hoja_in.max_row
mi_columna = hoja_in.min_column
ma_columna = hoja_in.max_column


for row in hoja_in.iter_rows(min_row=1, max_col=1, max_row=ma_fila):
    for cell in row:
        if cell.value == trabajadores:
                print (row)
                print(cell.value)

This is console output:

*** Remote Interpreter Reinitialized ***
(<Cell 'Enero'.A7>,)
SONIA MARÍA MONTESDEOCA QUINTANA
(<Cell 'Enero'.A53>,)
SONIA MARÍA MONTESDEOCA QUINTANA

Next step is use these rows to look up another values from columna 4 until ma_columna. Now I have a loop with following code:

from openpyxl import load_workbook

libros = "Z:\\Informática\\Desarrollo\\alcampo\\Horas Personal Alcampo Telde PGC Salado 2.021.xlsx"
trabajadores = "SONIA MARÍA MONTESDEOCA QUINTANA"

libro_in = load_workbook(libros)
hoja_in = libro_in['Enero']

dimension_excel = hoja_in.dimensions
mi_fila = hoja_in.min_row
ma_fila = hoja_in.max_row
mi_columna = hoja_in.min_column
ma_columna = hoja_in.max_column


for row in hoja_in.iter_rows(min_row=1, max_col=1, max_row=ma_fila):
    for cell in row:
        if cell.value == trabajadores:
                print (row)
                print(cell.value)

                for objeto_columna in range (3,ma_columna-2):
                    horas_merchand = hoja_in.cell(row=cell,column=objeto_columna).value

                    print ("workes name: ", cell.value, " make these working hours: ", horas_merchand)

But as I guest python gives me an error, trying to access to access to read a cell value. Accoding to doc there is two ways to access value with row:

Traceback (most recent call last):
  File "<module1>", line 33, in <module>
  File "C:\Users\tpv09.TPVCAN\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\worksheet\worksheet.py", line 237, in cell
    if row < 1 or column < 1:
TypeError: '<' not supported between instances of 'Cell' and 'int'

In the example, we write two values to two cells.

sheet['A1'] = 1

Here, we assing a numerical value to the A1 cell.

sheet.cell(row=2, column=2).value = 2

I use second one but it does not work with loop (for), any idea how to solve this?

Thanks so much in advanced.

P.S. Im very beginner at dev in python ;-)

             

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/7Yflb9usknY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/D058928E-48F7-4C34-A8D9-2DC88A449AEB%40clark-consulting.eu.

[Tvp Canarias]

Hi again,

I tried this code but does not work

from openpyxl import load_workbook

libros = "Z:\\Informática\\Desarrollo\\alcampo\\Horas Personal Alcampo Telde PGC Salado 2.021.xlsx"
trabajadores = "SONIA MARÍA MONTESDEOCA QUINTANA"

libro_in = load_workbook(libros)
hoja_in = libro_in['Enero']

dimension_excel = hoja_in.dimensions
mi_fila = hoja_in.min_row
ma_fila = hoja_in.max_row
mi_columna = hoja_in.min_column
ma_columna = hoja_in.max_column

rango=[]

for row in hoja_in.iter_rows(min_row=1, max_col=1, max_row=ma_fila):
    for cell in row:
        if cell.value == trabajadores:
                print (row)
                print(cell.value)

                cells=cell.value
                #for objeto_columna in range (3,ma_columna-2):
                #    horas_merchand = hoja_in.cell(row=cell,column=objeto_columna).value

                rango.append(cell)

                #                    print ("Trabajador: ", cell.value, "ha hecho las siguientes horas: ", horas_merchand)
print (rango)
# for fila_normal, fila_refuerzo in rango:
#    print("{0:8} {1:8}".format(fila_normal.value, fila_refuerzo.value))

*** Remote Interpreter Reinitialized ***
(<Cell 'Enero'.A7>,)
SONIA MARÍA MONTESDEOCA QUINTANA
(<Cell 'Enero'.A53>,)
SONIA MARÍA MONTESDEOCA QUINTANA

[<Cell 'Enero'.A7>, <Cell 'Enero'.A53>]

Traceback (most recent call last):

  File "<module1>", line 40, in <module>
TypeError: cannot unpack non-iterable Cell object

>>>
*** Remote Interpreter Reinitialized ***

Any idea, how to solved ? thanks so much in advanced

[Charlie Clark]

On 4 Mar 2021, at 14:12, Tvp Canarias wrote:

> Traceback (most recent call last):
>   File "<module1>", line 40, in <module>
> TypeError: cannot unpack non-iterable Cell object
>>>>
> *** Remote Interpreter Reinitialized ***
>
> Any idea, how to solved ? thanks so much in advanced

Not really: the exception talks about line 40 and your code only has 31
lines. Somewhere you have a loop that is expecting some kind of sequence
but is being passed a single cell.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office

Waldlehne 23
Düsseldorf
D- 40489
Mobile: +49-178-782-6226

[Moisés López]

Hi there,

Maybe I copy wrong code, here is right code and output from python console

from openpyxl import load_workbook

libros = "Z:\\Informática\\Desarrollo\\alcampo\\Horas Personal Alcampo Telde PGC Salado 2.021.xlsx"
trabajadores = "SONIA MARÍA MONTESDEOCA QUINTANA"

libro_in = load_workbook(libros)
hoja_in = libro_in['Enero']

dimension_excel = hoja_in.dimensions
mi_fila = hoja_in.min_row
ma_fila = hoja_in.max_row
mi_columna = hoja_in.min_column
ma_columna = hoja_in.max_column

for row in hoja_in.iter_rows(min_row=1, max_col=1, max_row=ma_fila):
    for cell in row:

        # Busco al trabajador en el excel, siempre aparece en dos filas.
        if cell.value == trabajadores:
                cells=cell.internal_value
                print (cell, cells)

                for objeto_columna in range (3,ma_columna-2):
                    horas_merchand = hoja_in.cell(row=cell,column=objeto_columna).value

                    print (horas_merchand)

*** Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec  7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32. ***

>>>
*** Remote Interpreter Reinitialized ***

<Cell 'Enero'.A7> SONIA MARÍA MONTESDEOCA QUINTANA

Traceback (most recent call last):

  File "<module2>", line 38, in <module>

  File "C:\Users\tpv09.TPVCAN\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\worksheet\worksheet.py", line 237, in cell
    if row < 1 or column < 1:
TypeError: '<' not supported between instances of 'Cell' and 'int'
>>>

Issue is located at horas_merchand = hoja_in.cell(row=cell,column=objeto_columna).value, python wait for a int value at row, but I pass a cell so this is why Python can process.

Any idea or suggestion how to do a loop from columna 3 until ma_columna-2 from two row where cells in this example e.g. A7 and A53.

[Charlie Clark]

On 5 Mar 2021, at 12:44, Moisés López wrote:

> hoja_in.cell(row=cell,column=objeto_columna).value
> print (horas_merchand)
>
> Python console output:

> <Cell 'Enero'.A7> SONIA MARÍA MONTESDEOCA QUINTANA

> Traceback (most recent call last):

> File "<module2>", line 38, in <module>

> File
> "C:\Users\tpv09.TPVCAN\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\worksheet\worksheet.py",
> line 237, in cell
> if row < 1 or column < 1:
> TypeError: '<' not supported between instances of 'Cell' and 'int'

This is wrong:

hoja_in.cell(row=cell,…

Try hoja_in.cell(row=cell.row,…)

But I can't really understand the rest of the code.

Good luck!

[Moisés López]

Thanks so much Charli, This is was I need.

This is a little bit complex excel structure which I need to read each cell guest what insite and make some task which these data. But code is already worked with your solutions.

Attached screenshot with excel structure.

This is output after python script read excel file.

Sección:  PGC - Salado | Enero 2.021
Trabajador  M I R I A M   J A S M I N A   E L   G B I A H I   H E R N Á N D E Z
Días de bajas  21 22 23 25 26 27 28 29 30
Días de faltas
Días libres
Días vacaciones
Días refuerzos  1 2 3 4 5 6 10 11 12 14
Horas refuerzos  3 3 3 3 3 9 1 1 1 1
Observaciones  observaciones maria del mar
=============================================================

Best regards