使用Pandas pd.read_excel读取含#开头公式错误单元格需保留文本而非转NA

问题场景 #

使用pd.read_excel读取Excel文件时，文件内包含#NA、#DIV/0!、#Value这类公式错误（或文本形式的错误值），默认会被转换为NaN，需求是将这类内容保留为原始文本。曾尝试单独使用openpyxl，但因需处理多种Excel格式，更倾向于使用pd.read_excel相关的方案。

解决方案 #

通过openpyxl引擎读取单元格的显示文本（display_value），替代默认的单元格值（value），以此保留错误文本。具体实现需手动遍历工作表单元格并提取display_value，再转换为DataFrame。

代码示例 #

import pandas as pd
from openpyxl import load_workbook

# 加载工作簿，data_only=False确保保留公式相关的显示信息
wb = load_workbook('Book1.xlsx', data_only=False)
# 指定目标工作表
ws = wb['Sheet1']

# 提取表头行
headers = [cell.value for cell in ws[1]]
# 提取数据行，每行取单元格的显示文本
data_rows = []
for row in ws.iter_rows(min_row=2):  # min_row=2跳过表头
    data_rows.append([cell.display_value for cell in row])

# 转换为Pandas DataFrame
df = pd.DataFrame(data_rows, columns=headers)

说明 #

• 公式错误的单元格（如#DIV/0!）的value属性为None，因此默认读取会转为NaN；而display_value属性会返回单元格实际显示的文本内容。
• 此方法兼容多种Excel格式（xlsx、xlsm等），既利用了openpyxl的精确读取能力，又能通过Pandas进行后续数据处理。

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