Python使用openpyxl按行读取Excel生成指定格式嵌套列表问题

Hey there! Let's break down what's happening and how to fix your code to get the exact nested list format you want.

The Problem with Your Current Code #

Right now, your code is iterating over columns first, then rows. That means you're collecting all values from the first column into one sublist, all values from the second column into another, and adding those two sublists to R. That's why you get:

R = [[1, 6, 0, 3, 4, 5], [2, 2, 9, 2, 1, 6]]

But what you need is to pair values row-by-row—each sublist should hold the two values from a single row in your Excel sheet.

Solution 1: Rewrite the Loop to Build Rows Directly #

The most straightforward fix is to flip your loop order: iterate over rows first, then grab the two column values for each row and pack them into a sublist. Assuming your target columns are min_column+3 and min_column+4 (since your original loop covers two columns), here's the adjusted code:

R = []
# Define your two target columns (matches your original column range)
target_cols = [min_column + 3, min_column + 4]

# Iterate over each row starting from row 2
for row in range(2, max_row + 1):
    # Get values from both columns for the current row
    row_values = []
    for col in target_cols:
        cell_value = sheet.cell(row, col).value
        # Only add if the cell isn't empty (adjust this condition if needed)
        if cell_value is not None:
            row_values.append(cell_value)
    # Only add the row to R if both values exist (avoid incomplete sublists)
    if len(row_values) == 2:
        R.append(row_values)

print(f'R = {R}')

This will directly build your desired nested list:

R = [[1,2], [6,2], [0,9], [3,2], [4,1], [5,6]]

Solution 2: Transpose Your Existing List (Quick Fix) #

If you already have the column-based list from your original code, you can use Python's zip() function to transpose it (swap rows and columns), then convert the resulting tuples to lists:

# Assuming your original output is stored in R
R = [[1, 6, 0, 3, 4, 5], [2, 2, 9, 2, 1, 6]]
# Transpose and convert tuples to lists
R = list(map(list, zip(*R)))
print(f'R = {R}')

This will instantly give you the row-based nested list you want.

Which Solution to Choose? #

• Solution 1 is better for large datasets: it processes rows one at a time, so you don't need to store entire columns in memory before building your final list. It's also more readable if someone else (or future you) needs to understand how the list is being built.
• Solution 2 is a quick one-liner if you already have the column-based list and just need to reshape it.

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