如何使用pandas读取Excel带自定义格式的列并输出与Excel一致的数据

核心原因 #

pandas.read_excel默认读取的是Excel单元格的原始存储值，而非单元格应用自定义格式后展示的可视化文本，这是两者显示不一致的核心原因。

参考信息 #

• Excel数据：
• Excel单元格格式：
• 原pandas输出结果：
• 原使用的读取代码：df = pandas.read_excel(printsheet, sheet_name="Condition", skiprows=1, keep_default_na=False)

解决方案 #

方案1：通过openpyxl读取Excel格式化展示值（推荐，匹配度最高） #

该方案直接读取Excel单元格应用格式后的展示文本，完全对齐Excel可视化效果：

from openpyxl import load_workbook
import pandas as pd

# 加载工作簿，data_only设为False才能读取格式关联的展示值
wb = load_workbook(printsheet, data_only=False)
ws = wb["Condition"]

data = []
# min_row=2对应原逻辑的skiprows=1，跳过第一行
for row in ws.iter_rows(min_row=2, values_only=False):
    row_data = []
    for cell in row:
        # 读取单元格格式化后的展示内容，空值统一处理为空字符串对齐原keep_default_na=False逻辑
        display_val = cell.number_format.format(cell.value) if cell.value is not None else ""
        row_data.append(display_val)
    data.append(row_data)

# 转换为DataFrame，如需保留表头可额外读取第一行赋值：df.columns = [c.value for c in ws[1]]
df = pd.DataFrame(data)

方案2：读取后手动格式化列 #

如果不想更换读取逻辑，可根据Excel的自定义格式规则，对pandas读取后的列做对应格式化：

# 示例：若Excel列是日期格式YYYY/MM/DD，可做如下处理
# df['日期列名'] = df['日期列名'].dt.strftime("%Y/%m/%d")
# 示例：若Excel列是保留2位小数的数字格式，可做如下处理
# df['数字列名'] = df['数字列名'].apply(lambda x: f"{x:.2f}")

注意事项 #

• 若Excel中使用了条件格式控制展示效果，需额外读取条件格式规则后做对应匹配处理，openpyxl支持读取条件格式配置。
• 所有自定义格式的规则需和Excel中的设置完全一致，才能保证输出效果完全匹配。

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