how to insert data in excel using python openpyxl from columns A to J

[Claver Barreto]

Hello, i have created function that would check if a cell in a row is None or not, if it is, it would insert the data from the functions parameter. The function would iterate from column A through J, however when i run the function it gives me this error:

Traceback (most recent call last): File "records.py", line 54, in <module> check_rows(3, "06so", "necklace", "somenecklace", 2, 10, 20, "Card", "Bank", "SomeBank") File "records.py", line 39, in check_rows j = st1["J" + str(i)] File "/home/akeno/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py", line 290, in __getitem__ min_col, min_row, max_col, max_row = range_boundaries(key) File "/home/akeno/.local/lib/python3.8/site-packages/openpyxl/utils/cell.py", line 135, in range_boundaries raise ValueError(msg) ValueError: J<Cell 'Records'.I2> is not a valid coordinate or range

Heres is the code:

import openpyxl

from openpyxl import Workbook

from openpyxl import load_workbook

import datetime

wb = load_workbook("excel.xlsx")

st1 = wb.active

st1 = wb["Records"]

st1["A1"] = "Date"

st1.column_dimensions["A"].width = 23

st1["B1"] = "Code"

st1["C1"] = "Family"

st1["D1"] = "Product"

st1.column_dimensions["D"].width = 30

st1["E1"] = "Quantity"

st1["F1"] = "Price"

st1["G1"] = "Total"

st1["H1"] = "Payment"

st1["I1"] = "Form"

st1["J1"] = "Deposit"

st1["K1"] = "Cod.Client"

def check_rows(counter, cod, fam, prod, quant, pric, total, pay, form, dep):

    a, b, c, d, e, f, g, h, i, j = '','','','','','','','','',''

    

    for i in range(2,counter):

        if (st1["A" + str(i)].value and st1["B" + str(i)].value and st1["C" + str(i)].value and st1["D" + str(i)].value and st1["E" + str(i)].value and st1["F" + str(i)].value and st1["G" + str(i)].value and st1["H" + str(i)].value and st1["I" + str(i)].value and st1["J" + str(i)].value) == None:

            a = st1["A" + str(i)]

            b = st1["B" + str(i)]

            c = st1["C" + str(i)]

            d = st1["D" + str(i)]

            e = st1["E" + str(i)]

            f = st1["F" + str(i)]

            g = st1["G" + str(i)]

            h = st1["H" + str(i)]

            i = st1["I" + str(i)]

            j = st1["J" + str(i)]

            a.value = datetime.datetime.today()

            b.value = cod

            c.value = fam

            d.value = prod

            e.value = quant

            f.value = pric

            g.value = total

            h.value = pay

            i.value = form

            j.value = dep

            

    wb.save(filename = "excel.xlsx")

    

check_rows(3, "06so", "necklace", "somenecklace", 2, 10, 20, "Card", "Bank", "Somebank")

the counter i to control the flow of the loop, like, how many rows i want to fill. can anyone help? thank you anyways.

if i change to iterate from column A through H, it works fine, its just starts to give me this error when i go through column I or J 

[Charlie Clark]

On 12 Nov 2020, at 23:36, Claver Barreto wrote:

Hello, i have created function that would check if a cell in a row is None or not, if it is, it would insert the data from the functions parameter. The function would iterate from column A through J, however when i run the function it gives me this error:

Traceback (most recent call last): File "records.py", line 54, in <module> check_rows(3, "06so", "necklace", "somenecklace", 2, 10, 20, "Card", "Bank", "SomeBank") File "records.py", line 39, in check_rows j = st1["J" + str(i)] File "/home/akeno/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py", line 290, in __getitem__ min_col, min_row, max_col, max_row = range_boundaries(key) File "/home/akeno/.local/lib/python3.8/site-packages/openpyxl/utils/cell.py", line 135, in range_boundaries raise ValueError(msg) ValueError: J<Cell 'Records'.I2> is not a valid coordinate or range

This is because you use the variable i to mean different things at different times: a cell value, a counter, a cell value.

Short variable names are great but also often cause problems like this, so it's better to use more expressive ones in anything other than comprehensions. I generally use idx or a derivative when referring to indices. But you could also use your categories such as cod_cell, fam_cell, etc.

Also, you're making your life unnecessarily hard for your self by using your own counter and Excel style addressing.

for row in ws.iter_rows(min_row=2, max_row=2+counter, max_col=10):
    cod_cell, fam_cell, prod_cell, quant_cell, pric_cell, total_cell, pay_cell, form_cell, dep_cell = row

And then do your assignments.

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

[Claver Barreto]

first of all thank you for your reply, so i've tested the code in my function and i wrote this:

def check_rows(counter, cod, fam, prod, quant, pric, total, pay, form, dep):

    date_cell, cod_cell, fam_cell, prod_cell, quant_cell, pric_cell, total_cell, pay_cell, form_cell, dep_cell = '','','','','','','','','',''

    for row in st1.iter_rows(min_row=2, max_row=2+counter, max_col=10):

        date_cel, cod_cell, fam_cell, prod_cell, quant_cell, pric_cell, total_cell, pay_cell, form_cell, dep_cell = row

                   

        date_cell = datetime.datetime.today()

        cod_cell = cod

        fam_cell = fam

        prod_cell = prod

        quant_cell = quant

        pric_cell = pric

        total_cell = total

        pay_cell = pay

        form_cell = form

        dep_cell = dep

            

        wb.save(filename = "excel.xlsx")

    

check_rows(3, "06so", "necklace", "somenecklace", 2, 10, 20, "Card", "Bank", "bank")

the code has no errors however when i check the excel file, it has no data, so i've tried to add to the assignments the "value" attribute i thought it could help, i had this:

        date_cell.value = datetime.datetime.today()

        cod_cell.value = cod

        fam_cell.value = fam

        prod_cell.value = prod

        quant_cell.value = quant

        pric_cell.value = pric

        total_cell.value = total

        pay_cell.value = pay

        form_cell.value = form

        dep_cell.value = dep

however when i try this approach it gives me this error:

Traceback (most recent call last):

  File "records.py", line 44, in <module>

    check_rows(3, "06so", "necklace", "somenecklace", 2, 10, 20, "Card", "Bank", "bank")

  File "records.py", line 31, in check_rows

    date_cell.value = datetime.datetime.today()

AttributeError: 'str' object has no attribute 'value'

i think i am not seeing something obvious here, could you please help?

thanks again for the reply.

Claver.



Reply all

Reply to author

Forward

[Charlie Clark]

On 13 Nov 2020, at 10:26, Claver Barreto wrote:

however when i try this approach it gives me this error:

Traceback (most recent call last):

File "records.py", line 44, in <module>

check_rows(3, "06so", "necklace", "somenecklace", 2, 10, 20, "Card",

"Bank", "bank")
File "records.py", line 31, in check_rows
date_cell.value = datetime.datetime.today()
AttributeError: 'str' object has no attribute 'value'

i think i am not seeing something obvious here, could you please help?
thanks again for the reply.

Looks like a typo:

date_cell, cod_cell, fam_cell, prod_cell, quant_cell, pric_cell,
total_cell, pay_cell, form_cell, dep_cell = '','','','','','','','','',''

You don't need this line and is biting you because

date_cel, cod_cell, fam_cell, prod_cell, quant_cell, pric_cell,


total_cell, pay_cell, form_cell, dep_cell = row

So you have defined date_cell as "" and date_cel as a cell. If you remove the first line you should get a name error.

Even easier of course to do without the names for the cells and such problems:

for value, cell in zip ([cod, fam, prod, quant, pric, total, pay, form, dep], row):
cell.value = value

[Claver Barreto]

your code worked amazingly well, super.

Now i just need to work on the Counter, i have a form in Tkinter in which the user would fill with data and those would be passed as parameters of this function.

so the Counter would increase whenever the user click the "Insert" Button, so that i would fill one row at a click and not multiple rows at the same time because of the "for loop", now the function just needs a bit of a change in the Counter but that i can do, well hopefully.

if i mess everything up i hope i can count on you again.

Sir, i truly dont know what to say, its been now 3 days i dont sleep because of this and other code related problem, the other codes i managed to solve today, this was the last one standing.

Whenever i get a problem mostly in coding, i just cannot sleep until i solved it, the only way for me to sleep is if my body just shutdown because of "low power".

sorry for writing these unrelated things. 

Once again thank, this means the world right now to me.

[Charlie Clark]

On 13 Nov 2020, at 11:16, Claver Barreto wrote:

your code worked amazingly well, super.
Now i just need to work on the Counter, i have a form in Tkinter in which
the user would fill with data and those would be passed as parameters of
this function.
so the Counter would increase whenever the user click the "Insert" Button,
so that i would fill one row at a click and not multiple rows at the same
time because of the "for loop", now the function just needs a bit of a
change in the Counter but that i can do, well hopefully.
if i mess everything up i hope i can count on you again.

Not when it comes to Tkinter…

Sir, i truly dont know what to say, its been now 3 days i dont sleep
because of this and other code related problem, the other codes i managed
to solve today, this was the last one standing.
Whenever i get a problem mostly in coding, i just cannot sleep until i
solved it, the only way for me to sleep is if my body just shutdown because
of "low power".
sorry for writing these unrelated things.

I think we've all had that – just spent more time than I should dealing with some quirks in Pandas.

But it is often better to step away and give your brain time to work things out. We've put a lot effort into openpyxl so that people don't need their own counters and Python has tricks like tuple unpacking and zip() to make everyone's life easier. So, when you do find yourself writing spaghetti code in Python, you're probably doing something wrong. Take a break sketch out what you need to do and simplify, simplify, simplify…

[Claver Barreto]

no problem i'll post only openpyxl related problems, and yes i better get some sleep now, thank you.