读取含公式的Excel返回None，请求代码排查与解决方案

问题描述 #

我尝试读取包含公式驱动值的Excel文件（部分单元格值由公式生成），但使用两种方法均无法获取有效数据，返回结果为None。希望能读取到与Excel界面显示一致的数据。

尝试过的代码 #

方法一（openpyxl）

def clean_data_from_excel(self,file_path):  # 原代码缺少冒号，已修正
    # Load the workbook and select the sheet
    wb = openpyxl.load_workbook(file_path,data_only=True)
    sheet = wb['ABC']
    row_num = sheet.max_row  # Number of rows
    col_num = sheet.max_column  # Number of columns
    
    all_rows = []
    # Loop through rows and columns
    for row in range(2, row_num + 1):  # Skip header row
        row_values = {}
        for column in range(1, col_num + 1):
            # Get cell element
            cell = sheet.cell(row=row, column=column)
            header = sheet.cell(row=1, column=column).value  # Get header for keys
            row_values[header] = cell.value  # Store value exactly as it is in the cell
    
        if row_values:
            all_rows.append(row_values)

    return all_rows 

方法二（结合openpyxl与pandas）

data_section1 = []

for row in sheet['A2:B15']:
    row_data = [cell.value for cell in row]
    print("row_data -----> ", row_data)
    data_section1.append(row_data)

print("data_section1 -----> ", data_section1)

data_dict = {item[0]: [item[1]] for item in data_section1}
print("data_dict -----> ", data_dict)
# Create DataFrame
df = pd.DataFrame(data_dict)

print("data_df -----> ", df)

核心原因 #

openpyxl的data_only=True参数只能读取Excel文件中已保存的公式计算结果。如果目标文件从未通过Excel/WPS等软件打开并完成公式计算、保存操作，公式单元格的计算值不会被写入文件，此时cell.value就会返回None。

解决方案 #

方案1：手动预处理Excel文件 #

直接用Excel/WPS打开目标文件，等待所有公式计算完成（界面显示出计算结果）后，保存并关闭文件。之后再运行原有代码，data_only=True就能正常读取到计算后的值。

方案2：用xlwings自动触发公式计算（推荐） #

xlwings可以调用本地Excel引擎完成公式计算，无需手动操作，适合自动化场景：

import xlwings as xw

def read_excel_with_formulas(file_path):
    # 后台启动Excel，不显示界面
    app = xw.App(visible=False)
    wb = app.books.open(file_path)
    sheet = wb.sheets['ABC']
    
    # 读取已计算的全部数据，和Excel界面显示完全一致
    full_data = sheet.range('A1').expand().value
    
    wb.close()
    app.quit()
    
    # 转换为你需要的字典列表格式
    headers = full_data[0]
    result = []
    for row in full_data[1:]:
        result.append(dict(zip(headers, row)))
    
    return result

方案3：用pandas直接读取 #

pandas结合openpyxl引擎时，会自动读取公式计算后的值（前提是Excel文件曾被计算保存过，或pandas能调用引擎完成计算）：

import pandas as pd

def read_excel_pandas(file_path):
    # 读取Excel，自动处理公式计算值
    df = pd.read_excel(file_path, sheet_name='ABC')
    # 转换为字典列表格式返回
    return df.to_dict('records')

方案4：修正原有openpyxl代码（仅适用于已预处理的文件） #

先确保文件已被Excel计算保存，同时修复原代码的语法错误（比如函数定义缺少冒号），即可正常运行原有逻辑。

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