While循环中重置Excel最后一行及pyautogui类型错误排查

需求场景：从Excel文件的A2单元格取值粘贴到其他应用指定位置，再将B2单元格值粘贴至该应用另一位置，完成操作后截图并在C2填入“Yes”，重复该流程至数据集最后一行。

当前问题：当A2单元格值为0时，代码抛出类型错误，错误堆栈如下：

Traceback (most recent call last):   
  File "c:\Users\reber\Gaming\Rise of Castles\Member_Details\tuple.py", line 37, in <module>
    pyauto.typewrite(x_coord) 
  File "c:\Users\reber\Gaming\Rise of Castles\Member_Details\GetMemberDetails_venv\Lib\site-packages\pyautogui\__init__.py", line 594, in wrapper
    returnVal = wrappedFunction(*args, **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^  
  File "c:\Users\reber\Gaming\Rise of Castles\Member_Details\GetMemberDetails_venv\Lib\site-packages\pyautogui\__init__.py", line 1682, in typewrite
    for c in message:
             ^^^^^^^ TypeError: 'float' object is not iterable

原代码：

import pyautogui as pyauto
import xlwings as xlw
import time
from pynput import mouse

wbmd = xlw.Book('Member_Details.xlsx')
md_coords = wbmd.sheets["Coords"]
mdlr = md_coords.cells (md_coords.cells.last_cell.row,3).end('up').row
dlr = md_coords.cells(md_coords.cells.last_cell.row,1).end('up').row + 1

mdilr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row + 1

# print(f"mdlr is {mdlr}")
# print(f"dlr is {dlr}")
# print(f"mdilr is {mdilr}")

while mdilr < dlr:

    mdilr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row + 1
    
    #Identify X Coord from Workbook.
    x_coord = md_coords.range(f"A{mdilr}").value

    #Identify Y Coord from Workbook.
    y_coord = md_coords.range(f"B{mdilr}").value

    #Click on Location Finder.
    pyauto.click(x=1097, y=827)
    time.sleep(.5)

    #Identify X Coord from Workbook.
    #x_coord = md_coords.range(f"A{mdilr}").value

    #Click on X Coord Box.
    pyauto.click(x=1038, y=784)
    time.sleep(.3)
    pyauto.typewrite(x_coord)

    #Identify Y Coord from Workbook.
    #y_coord = md_coords.range(f"B{mdilr}").value

    #Double-Click on Y Coord Box.
    pyauto.click(x=1189, y=786, clicks=2, interval=.3)
    time.sleep(.3)
    pyauto.typewrite(y_coord)

    #Double-Click on Find Button.
    pyauto.click(x=1263, y=786, clicks=2, interval=.3)

    # #Click Center of Target Tile.
    pyauto.click(x=1100, y=534, clicks=2, interval=.4)

    #Take and Save Screenshot
    im = pyauto.screenshot(region=(941,210, 324, 400))
    im.save(x_coord + '_' + y_coord + '.png')

    #Update workbook to show screenshot saved.
    md_coords.range(f"C{mdilr}").value = "Yes"

    #Resets the last row + 1
    mdilr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row + 1

问题原因 #

PyAutoGUI的typewrite()方法要求输入字符串类型的参数，它会遍历参数的每个字符来模拟键盘输入。但从Excel读取的数值（包括0）会被xlwings解析为float类型，float无法被迭代，因此抛出TypeError: 'float' object is not iterable。此外，后续截图文件名拼接时，float与字符串直接相加也会触发类型错误。

解决方案 #

读取单元格值后，将其转换为字符串类型，确保typewrite()和文件名拼接都能正常执行。修改后的代码如下：

import pyautogui as pyauto
import xlwings as xlw
import time
from pynput import mouse

wbmd = xlw.Book('Member_Details.xlsx')
md_coords = wbmd.sheets["Coords"]
mdlr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row
dlr = md_coords.cells(md_coords.cells.last_cell.row,1).end('up').row + 1

mdilr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row + 1

while mdilr < dlr:

    mdilr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row + 1
    
    # 读取并转换为字符串
    x_coord = str(md_coords.range(f"A{mdilr}").value)
    y_coord = str(md_coords.range(f"B{mdilr}").value)

    #Click on Location Finder.
    pyauto.click(x=1097, y=827)
    time.sleep(.5)

    #Click on X Coord Box.
    pyauto.click(x=1038, y=784)
    time.sleep(.3)
    pyauto.typewrite(x_coord)

    #Double-Click on Y Coord Box.
    pyauto.click(x=1189, y=786, clicks=2, interval=.3)
    time.sleep(.3)
    pyauto.typewrite(y_coord)

    #Double-Click on Find Button.
    pyauto.click(x=1263, y=786, clicks=2, interval=.3)

    #Click Center of Target Tile.
    pyauto.click(x=1100, y=534, clicks=2, interval=.4)

    #Take and Save Screenshot
    im = pyauto.screenshot(region=(941,210, 324, 400))
    im.save(f"{x_coord}_{y_coord}.png")

    #Update workbook to show screenshot saved.
    md_coords.range(f"C{mdilr}").value = "Yes"

    #Resets the last row + 1
    mdilr = md_coords.cells(md_coords.cells.last_cell.row,3).end('up').row + 1

关键修改点 #

• 读取单元格值时，用str()强制转换为字符串，适配typewrite()的参数要求
• 截图文件名使用f-string拼接，避免类型错误

内容的提问来源于stack exchange，提问作者Rodger