使用openpyxl循环读取Excel的函数为何失效？

你的问题和read_excel_cell函数无关，核心问题出在列表的引用赋值上：

当你执行以下代码时：

OneD_array = []
for i in range(15):
    OneD_array.append(None)

A_radii = OneD_array
M_radii = OneD_array
X_radii = OneD_array
C_11 = OneD_array
C_12 = OneD_array
C_44 = OneD_array

这些变量并没有创建独立的列表，而是全部指向同一个列表对象。循环中先给C_44[i]赋值，随后给C_11[i]赋值，本质是在修改同一个列表的第i个元素，最终C_11和C_44打印的自然是同一个列表的内容。

修正代码 #

把创建数组的部分改为为每个变量生成独立的列表即可：

import openpyxl
import matplotlib.pyplot as plt


def read_excel_cell(file_name, sheets_name, row_num, col_num):
    wb = openpyxl.load_workbook(file_name)
    sheet = wb[sheets_name]
    cell_value = sheet.cell(row=row_num, column=col_num).value
    wb.close()
    return cell_value


file_path = r'C:\Users\User\Desktop\23Spring\Research\Modulus Sheet for Python.xlsx'
sheet_name = 'AMX3'


# 为每个变量创建独立的空列表（每个列表都是单独的对象）
A_radii = [None for _ in range(15)]
M_radii = [None for _ in range(15)]
X_radii = [None for _ in range(15)]
C_11 = [None for _ in range(15)]
C_12 = [None for _ in range(15)]
C_44 = [None for _ in range(15)]


# 赋值逻辑
for i in range(15):
    print(i)
    C_44[i] = read_excel_cell(file_path, sheet_name, 9, i + 2)
    C_11[i] = read_excel_cell(file_path, sheet_name, 7, i + 2)

print(C_11)
print(C_44)

额外优化建议 #

每次循环都调用read_excel_cell会重复打开/关闭Excel文件，效率很低。可以改为只打开一次工作簿，批量读取数据：

import openpyxl
import matplotlib.pyplot as plt


file_path = r'C:\Users\User\Desktop\23Spring\Research\Modulus Sheet for Python.xlsx'
sheet_name = 'AMX3'

# 只打开一次工作簿
wb = openpyxl.load_workbook(file_path)
sheet = wb[sheet_name]

# 创建独立列表
C_11 = [None for _ in range(15)]
C_44 = [None for _ in range(15)]

for i in range(15):
    print(i)
    C_44[i] = sheet.cell(row=9, column=i + 2).value
    C_11[i] = sheet.cell(row=7, column=i + 2).value

wb.close()
print(C_11)
print(C_44)

内容的提问来源于stack exchange，提问作者蕭力諶