英文:
Openpyxl - wrong indexes for coordinates
问题
任务是收集一个输入数字，即1。使用这个数字创建一个n x n的乘法表（1x1，2x2等等）。然后用x个更多的数字重复这个过程。我选择了5次迭代，总共需要进行6个数字的数学运算。数学运算应该在Excel中进行。请查看图片以了解我下面描述的内容：
A1应该保持空白，所以我从B1和A2开始输入数字
右边和下边添加了5次额外的迭代
需要在框定的区域进行数学运算：
- “maxcoord”找到适当的范围
- 循环从上到下遍历行，从左到右遍历列
- 打印i，找到数学结果需要放置的适当坐标
到这里一切都正常。
数学应该很容易建立mLeft（第1列中的值）和mTop（第1行中的值）相乘。我使用计数器来保持在列和行上固定。但有些东西没有按计划进行。
为什么我在公式中基本上将索引硬编码时，继续获得范围中的最后一列和行的变量？
感谢您的帮助
以下是我的代码：
import openpyxl, sys
# number = int(sys.argv[1])
number = int(input("输入一个数字："))
# 创建Excel文件
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)
# 初始化变量
mLeft_row = 2
mTop_col = 0
for rows in sheet[cellrange]:
# 为下一行重置为1
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))
# 这是值或公式最终将放置的位置
# 左乘数固定在第1列和行上，直到我们进入循环中的下一行
mLeft = sheet.cell(row=mLeft_row, column=1)
# 使用mLeft_row硬编码 - 为什么它会选择范围的最后一行？
print("Wrong Row: mLeft coordinate %s, mLeft col %s, mLeft row %s" % (mLeft.coordinate, mLeft.column, mLeft.row))
# mLeft_row保存了正确的值：
print("Correct Row: mLeft_row", mLeft_row)
# 从第2列（B）开始
mTop_col += 1
# 顶部乘数固定在第1行，每次迭代中的列加1，但在移动到循环中的新行时重置为1
mTop = sheet.cell(row=1, column=mTop_col)
# 具有特定值的mTop_col - 为什么它会选择范围的最后一列？
print("Wrong Col: mTop coordinate %s mTop col %s and mTop row %s" % (mTop.coordinate, mTop.column, mTop.row))
# mTop_col保存了正确的值：
print("Correct Col: mTop_col", mTop_col)
# val = "=" + str(mLeft.coordinate) + "*" + str(mTop.coordinate)
# i.value = val
# i.value = mLeft * mTop
mLeft_row += 1
<details>
<summary>英文:</summary>
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[![Image to help visualize][1]][1]
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
[1]: https://i.stack.imgur.com/jDW47.png
</details>
# 答案1
**得分**: 0
这段代码看起来有点过度工程化。与其利用迭代遍历行，似乎你试图追踪单元格的行和列的位置来设置单元格的值。
处理这个问题的理想方式是获取单元格的位置并执行以下操作：
- 获取单元格所在行的第一个单元格的值，我们称之为 `val_row`。
- 获取单元格所在列的第一个单元格的值，我们称之为 `val_col`。
- 将单元格的值设置为公式。
虽然我的部分可能有些过度工程化，但这是我更新你的代码以消除对硬编码单元格引用的需要，使逻辑更加动态并实现我所解释的逻辑的方式。
```python
import openpyxl
number = int(input("输入一个数字："))
# 创建Excel文件
wb = openpyxl.Workbook()
sheet = wb.active
# 设置起始列和行。值不应该以零为基础。
init_col = 2
init_row = 2
# 用于跟踪行号和列号的变量。
col_number = 0
row_number = 0
# 设置要填充的行和列的数量。
number_of_iterations = 5
# 将停止位置加1，因为我们需要填充由`number_of_iterations`定义的行和列的数量，以及第一行和列
# 将值写入第一行和列。
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("列坐标 %s, 值 %s" % (col_cell.coordinate, col_cell.value))
print("行坐标 %s, 值 %s" % (row_cell.coordinate, row_cell.value))
maxcoord = sheet.cell(column=col_number, row=row_number)
print("maxcoord 是", maxcoord.coordinate)
# 使用init_col和init_row值获取起始单元格。
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(
"正确的行: i.coordinate 是 %s, i.column 是 %s, i.row 是 %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")
英文:
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")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论