excel - json

[jmabrito]

{"3025": {"pos1": null, "pos2": 11, "pos3": null, "pos4": 34, "pos5": 41},
"3026": {"pos1": 6, "pos2": null, "pos3": 27, "pos4": 36, "pos5": null},
"3027": {"pos1": 10, "pos2": null, "pos3": 29, "pos4": null, "pos5": 50}, "…………………..…..}}
Through import json, I got this long string. You should see that number 29 is in row 3027, column pos3. How can I search for 29 and the program transmit me: it is in row 3027 column pos3 (D)
And if in this string there are more numbers 29, will it be possible to mention the rows/columns? Thank you

[buran]

import json
from collections import namedtuple

Cell = namedtuple('Cell', 'row column value')

def search_for_value(data, lookup_value):
    for row, values in data.items():
        for column, cell_value in values.items():
            if cell_value == lookup_value:
                yield Cell(row, column, cell_value)

spam = """{"3025": {"pos1": null, "pos2": 11, "pos3": null, "pos4": 29, "pos5": 41},
"3026": {"pos1": 6, "pos2": null, "pos3": 27, "pos4": 36, "pos5": null},
"3027": {"pos1": 10, "pos2": null, "pos3": 29, "pos4": null, "pos5": 50}}
"""

data = json.loads(spam)
for cell in search_for_value(data, 29):
    print(cell)

Output:
Cell(row='3025', column='pos4', value=29)
Cell(row='3027', column='pos3', value=29)

[jmabrito]

Matter completely resolved. However, in the line: for cell in search_for_value (date, 50): (was 29 in the previous example), I can't put a variable (num). Now the lines start at 2. I want to record in an append.xlsx in the cl+5 column the content vl Thank you

for cell in search_for_value(data, 50):
    print(cell)    
    li=cell[0]    
    print (li,  "row")
    print (cell[1])
    cl=cell[1]
    print (cl, "column")   
    print (cell[2])
    vl=cell[2]
    print (vl, "value")
    CE=f'{cl}{li}'
    print (CE, "2 juntos")
    sheet["CE"] = "vl"    
    workbook.save(filename="aexa_append.xlsx")

: 50}}
Cell(row='4', column='F', value=50)
4 row
F
F column
50
50 value
F4 2 juntos
Traceback (most recent call last):
File "C:\Users\José Brito\AppData\Local\Programs\Python\Python39\PrEXCEL\aexcteste2.py", line 62, in <module>
sheet["CE"] = "vl"
File "C:\Users\José Brito\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl-3.0.6-py3.9.egg\openpyxl\worksheet\worksheet.py", line 313, in __setitem__
self[key].value = value
AttributeError: 'tuple' object has no attribute 'value'
>>>

[buran]

sheet["CE"] will return tuple of columns CE. Also "vl" is string, bot the name vl

 sheet[CE] = vl

should do

or better

num = 50
for cell in search_for_value(data, num):
    CE=f'{cl}{li}'
    print (CE, "2 juntos")
    sheet[f'{cell.column}{cell.row}'] = cell.value
    workbook.save(filename="aexa_append.xlsx")

threre are also other ways to write to cell in openpyxl.

[jmabrito]

I think you are writing exactly the same thing that is in the excel source.
What I want is: found the row, column and value, go write in the same line, but in column K with the same value.
in this case it is in line 4 7 and 10 all in column F, the value must be copied to the append file for column K, in the same lines, and the same value. Thanks

[jmabrito]

Needing better, the values ​​are in column A to F, when they are found, they are now entered in line G to K, respectively.
in first quote i said cl+5 it means that the columns jump 5 positions. Thanks.

[buran]

I think you are writing exactly the same thing that is in the excel source.

I just fix your own code, line # 13 of your snippet.

Needing better, the values ​​are in column A to F, when they are found, they are now entered in line G to K, respectively.

A to F are 6 columns, G to K are 5 columns

[jmabrito]

I think you are writing exactly the same thing that is in the excel source.

I just fix your own code, line # 13 of your snippet.

Needing better, the values ​​are in column A to F, when they are found, they are now entered in line G to K, respectively.

A to F are 6 columns, G to K are 5 columns

I didn't see the entry on line # 13. In fact it should be in line X, column A is the order of the lines .. so it must be B to F write in column G to K. sorry

[buran]

line 13 of your code is

 sheet["CE"] = "vl"    

import json
from collections import namedtuple

from openpyxl import Workbook
from openpyxl.utils import column_index_from_string

Cell = namedtuple('Cell', 'row column value')

def search_for_value(data, lookup_value):
    for row, values in data.items():
        for column, cell_value in values.items():
            if cell_value == lookup_value:
                yield Cell(row, column, cell_value)


num = 50
OFFSET = 5
spam = "your JSON string here"
data = json.loads(spam)
for cell in search_for_value(data, num):
    print(cell)
    ws.cell(row=int(cell.row), column=column_index_from_string(cell.column)+OFFSET, value=cell.value)
    wb.save(filename="aexa_append.xlsx")

[jmabrito]

I just fix your own code, line # 13 of your snippet.

A to F are 6 columns, G to K are 5 columns

I didn't see the entry on line # 13. In fact it should be in line X, column A is the order of the lines .. so it must be B to F write in column G to K. sorry

I wrote sheet [CE] = vl, but before I put li = li +20. it worked, and wrote in the same column but 20 lines down. I will try to do if cl == "F" ..... "F" = "K". This attempt did not work. I will insist. Thanks