使用openpyxl将列表中元组写入Excel行遇格式转换错误求助

First, let's clarify why you're hitting that error:
The cell().value property only accepts single scalar values (like a single string, number, etc.)—it can't directly take a tuple like ('a','b'). Excel cells hold individual values, so trying to stuff an entire tuple into one cell will throw that conversion error.

Fix 1: Nested Loops to Map Elements to Cells #

Assuming you're using openpyxl (one of the most common libraries for Excel manipulation in Python), here's how to correctly write your array starting at row 2, column 3:

First, set up your workbook and worksheet:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
arr = [('a','b'),('a','b')]

Then implement the proper write logic:

# Define your starting position
start_row = 2
start_col = 3

# Iterate over each row in your array
for row_index, row_values in enumerate(arr):
    # Calculate the current row to write to (starts at start_row, increments each loop)
    current_row = start_row + row_index
    # Iterate over each value in the tuple to write to consecutive columns
    for col_offset, value in enumerate(row_values):
        current_col = start_col + col_offset
        ws.cell(row=current_row, column=current_col, value=value)

# Save the workbook
wb.save("output.xlsx")

How This Works: #

• enumerate(arr) gives us both the index of each tuple in your array and the tuple itself—we use the index to shift the row number from your starting row.
• For each tuple, enumerate(row_values) gives us the offset from your starting column, so the first value goes to column 3, the second to column 4, etc.
• Each value is written to its own individual cell, which aligns with how Excel stores data.

Alternative: Simplify with Batch Writing (OpenPyXL) #

If you prefer a more concise approach, you can target the starting cell and map your array to the correct range:

start_cell = ws.cell(row=start_row, column=start_col)

for row_idx, row_data in enumerate(arr):
    for col_idx, val in enumerate(row_data):
        ws.cell(row=start_cell.row + row_idx, column=start_cell.column + col_idx, value=val)

Note for Other Libraries: #

If you're using something like xlwt (for .xls files) or pandas, the core logic stays the same: you need to map each individual element in your 2D array to a unique cell, rather than trying to assign an entire row tuple to one cell. For pandas, you could convert your array to a DataFrame first, then use df.to_excel() with startrow and startcol parameters to position your data correctly.

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