Openpyxl - wrong indexes for coordinates

Task is to collect an input number, i.e. 1. Use this number for a multiplication table n x n (1x1, 2x2 and so forth). And then repeat with x more numbers. I chose 5 iterations, so 6 numbers in total that we need to do the math for. The math is supposed to happen in Excel. Please take a look at the pic to see what I'm describing below:

A1 is supposed to stay empty, so I'm starting in B1 and A2 with the input number
5 additional iterations are added to the right and down

Need to do the math, in the framed area:

"maxcoord" finds the proper range
the loop goes through the rows top to bottom and the columns left to right
printing off i, it finds the proper coordinates, where the math result needs to end up

Everything works until here.

the math should be easy establishing mLeft (the values in col 1) and mTop (the values across row 1) to multiply with each other. I'm using counters to remain fixed on col and row. But something is not working out.

Why do I continue to get the last column and row in the range in my variables, when I pretty much hardcoded the index into the formula?

Thanks for your help

Here's my code:

import openpyxl, sys

# number = int(sys.argv[1])
number = int(input("Enter a number: "))

# Creating the Excel file
wb = openpyxl.Workbook() 

sheet = wb.active
col = sheet['B1'] 
col.value = number
r = sheet['A2']
r.value = number

for x in range(5):
    col.column += 1
    r.row += 1
    number += 1
    col.value = number
    r.value = number
    print("Col Coordinate %s, value %s" % (col.coordinate, col.value))
    print("Row Coordinate %s, value %s" % (r.coordinate, r.value))

maxcoord = sheet.cell(column = col.column, row = r.row)
print("maxcoord is ", maxcoord.coordinate)

cellrange = 'B2:' + str(maxcoord.coordinate)
print("cellrange", cellrange)

# initializing variables
mLeft_row = 2
mTop_col = 0            

for rows in sheet[cellrange]:
    # reseting to 1 for the next row
    mTop_col = 1 
    
    for i in rows:
        print("Correct Row: i.coordinate is %s, i.column is %s, i.row is %s" % (i.coordinate, i.column, i.row))
        #this is where the value or formula will go in the end
        
        # Left Multiplier fixed on column 1 and row to until we move to the next row in the loop
        mLeft = sheet.cell(row = mLeft_row, column = 1)    
        # with mLeft_row hardcoded - why is it picking up the end row of the range?
        print("Wrong Row: mLeft coordinate %s, mLeft col %s, mLeft row %s" %(mLeft.coordinate, mLeft.column, mLeft.row))
        # mLeft_row holds the proper value:
        print("Correct Row: mLeft_row", mLeft_row)
        
        # starting in column 2 = B
        mTop_col +=1
        # Top Multiplier fixed on row 1 and columns move with every iteration +1, but reset to 1 when moving to a new row in the loop
        mTop = sheet.cell(row = 1, column = mTop_col)               
        # with mTop_col having a particular value - why is it picking up the last column in the range?
        print("Wrong Col: mTop coordinate %s mTop col %s and mTop row %s" % (mTop.coordinate, mTop.column, mTop.row))
        # mTop_col holds the proper value:
        print("Correct Col: mTop_col", mTop_col)
        #val = "=" + str(mLeft.coordinate) + "*" + str(mTop.coordinate)
        #i.value = val
        #i.value = mLeft * mTop  
    mLeft_row += 1

Solution

It looks to me like you're somewhat over-engineering things. Rather than taking advantage of the iteration through the rows, you seem to be trying to keep track of the position of both the row and the column when setting the value of the cells.

The ideal way to handle this would be to fetch the position of the cell and:

Fetch the value of the first cell of the row in which the cell is found, let's call this val_row.
Fetch the value of the fist cell of the column in which the cell is found, let's call this val_col.
Set the value of the cell as the formula.

While there's probably some over-engineering on my end, this is how I've updated your code to eliminate the need for hardcoding cell references, make the logic a bit more dynamic and implement the logic I've explained.

import openpyxl

number = int(input("Enter a number: "))

# Creating the Excel file
wb = openpyxl.Workbook() 
sheet = wb.active

# Set the start row and column. Values should not be zero-based.
init_col = 2
init_row = 2

# Variables to track the row and column number.
col_number = 0
row_number = 0

# Set the desired number of rows and columns to populate.
number_of_iterations = 5

# Add 1 to the stop position because we need to populate the number
# of rows and columns as defined by `number_of_iterations` plus
# the first row and column
# Write the values to the first row and column.
for x in range(0, number_of_iterations + 1):
    target_col_index = init_col + x
    col_number = target_col_index
    col_cell = sheet.cell(column=target_col_index, value=number, row=1)

    target_row_index = init_row + x
    row_number = target_row_index
    row_cell = sheet.cell(row=target_row_index, value=number, column=1)

    number += 1

    print("Col Coordinate %s, value %s" % (col_cell.coordinate, col_cell.value))
    print("Row Coordinate %s, value %s" % (row_cell.coordinate, row_cell.value))

maxcoord = sheet.cell(column=col_number, row=row_number)
print("maxcoord is ", maxcoord.coordinate)

# Use the init_col and init_row values to get the starting cell.
start_cell = sheet.cell(column=init_col, row=init_row)
cellrange = str(start_cell.coordinate) + ':' + str(maxcoord.coordinate)
print("cellrange", cellrange)  

for row in sheet[cellrange]:    
    for i in row:
        print(
            "Correct Row: i.coordinate is %s, i.column is %s, i.row is %s" % (
                i.coordinate, i.column, i.row
            )
        )

        first_cell_in_row = sheet.cell(column=1, row=i.row)
        first_cell_in_col = sheet.cell(column=i.column, row=1)
        
        val = "=" + str(first_cell_in_row.coordinate) + "*" + str(first_cell_in_col.coordinate)
        i.value = val

wb.save("calc.xlsx")