excel - json

[jmabrito]

I finished the program. as I said, I wrote, if cl == "F"
cl = "K" and the result was perfect. I had already made these changes, but carelessly it never worked. OK thanks.

[buran]

Did you try the snippet from my previous post?

[jmabrito]

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]

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")

Traceback (most recent call last):
File "C:\Users\José Brito\AppData\Local\Programs\Python\Python39\PrEXCEL\aexcteste2.py", line 58, in <module>
ws.cell(row=int(cell.row), column=column_index_from_string(cell.column)+OFFSET, value=cell.value)
NameError: name 'ws' is not defined

[buran]

ws is the worksheet where you write. Note that I did not provide the code to open/load the workbook and get the worksheet. It was missing also in your code. You need to add your code to load the workbook. It's obvious after all

[jmabrito]

import json
from collections import namedtuple
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.utils import column_index_from_string
#filename="aexa.xlsx"
workbook = load_workbook(filename="aexa.xlsx")
workbook.sheetnames
['Sheet 1']
sheet = workbook.active
print (sheet)
cols ={}
def print_rows():
    for value in sheet.iter_rows(min_row=2,
                               min_col=2,
                               max_col=6,                               
                               values_only=True):                               
        print(value)
       
#
#duas linhas em branco
#print_rows()
#print (num)
for row in sheet.iter_rows(min_row=2,
                               min_col=1,
                               max_col=6,                               
                               values_only=True):
    col_id = row[0]
    col ={
    "B": row[1],
    "C": row[2],
    "D": row[3],
    "E": row[4],
    "F": row[5],

    }
    cols[col_id] = col
cart = json.dumps(cols)
print (cart)
 
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(cart)
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") 

hhere is wrong ....
already working

[/quote]

[buran]

OMG, why do you dump to JSON, when you can just open the source workbook, iterate over cells and write respective values to target workbook?

[jmabrito]

OMG, why do you dump to JSON, when you can just open the source workbook, iterate over cells and write respective values to target workbook?

i change wb for workbook
For the manipulation test in excel, which will be included in a larger program, I read an excell, passed to json and then recorded IN THE SAME EXCEL (this is what I wanted). Before I did the composition of sheet [CE] = vl and the recording in the same file, which resulted.
Actually I don't know how to run a program, reading the excell, do what json does and write to the same location in + 5 columns ahead,:
thanks
running the program that suggested gave the following error:

este21.py", line 58, in <module>
ws.cell(row=int(cell.row), column=column_index_from_string(cell.column)+OFFSET, value=cell.value)
AttributeError: 'list' object has no attribute 'cell'

[buran]

I really don't understand what you are doing and there is a lot of unnecessary things going on in your code, but to fix the error

import json
from collections import namedtuple
from openpyxl import load_workbook

from openpyxl.utils import column_index_from_string
#filename="aexa.xlsx"
workbook = load_workbook(filename="aexa.xlsx")
sheet = workbook.active
print (sheet)
cols ={}
def print_rows():
    for value in sheet.iter_rows(min_row=2,
                               min_col=2,
                               max_col=6,                               
                               values_only=True):                               
        print(value)
        
#
#duas linhas em branco
#print_rows()
#print (num)
for row in sheet.iter_rows(min_row=2,
                               min_col=1,
                               max_col=6,                               
                               values_only=True):
    col_id = row[0]
    col ={
    "B": row[1],
    "C": row[2],
    "D": row[3],
    "E": row[4],
    "F": row[5],
 
    }
    cols[col_id] = col
cart = json.dumps(cols)
print (cart)
  
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(cart)
for cell in search_for_value(data, num):
    print(cell)
    sheet.cell(row=int(cell.row), column=column_index_from_string(cell.column)+OFFSET, value=cell.value)
    workbook.save(filename="aexa_append.xlsx") 

[jmabrito]

when it was set to = 82, it printed a single line on the screen. Now that it comes from a random this number of lines appears:
saiu o número:82
82
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='16', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='16', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='16', column='J', value=82)
Cell(row='7', column='J', value=82)
Cell(row='16', column='J', value=82)
Digite o nr.:

If I put in the end:
num = "" I only see one line.

PS how can I find in this excel one line that has 12 cells filled in it, and tell me the value of cell T(line), and the row.
With these last changes, the program is finished. thanks in advance José Brito (portuguese old man) obrigado!!!