如何将Openpyxl读取的cell.value转换为匹配cell.number_format的格式化字符串

Openpyxl默认返回的cell.value是单元格存储的原始值，不会自动按照cell.number_format渲染成Excel里的显示文本，我们可以通过格式规则映射、结合值类型判断做通用转换，覆盖绝大多数常规格式场景，输出和Excel端一致的显示结果。

可直接复用的实现代码如下：

import openpyxl
from datetime import datetime, date
import re
import sys

def excel_format_to_python_fmt(num_format):
    """
    将Excel number_format规则转换为Python可用的格式化串
    返回：(python格式化串, 是否为日期类型格式)
    """
    # Excel内置日期格式映射，可根据业务场景补充
    builtin_date_fmts = {
        14: "m/d/yyyy",
        15: "d-mmm-yy",
        16: "d-mmm",
        17: "mmm-yy",
        18: "h:mm AM/PM",
        19: "h:mm:ss AM/PM",
        20: "h:mm",
        21: "h:mm:ss",
        22: "m/d/yyyy h:mm",
    }
    is_date_fmt = False

    # 处理内置数字格式编码
    if isinstance(num_format, int):
        if num_format in builtin_date_fmts:
            num_format = builtin_date_fmts[num_format]
            is_date_fmt = True
    else:
        # 识别自定义日期格式：包含年月日时分秒、AM/PM占位符即判定为日期格式
        date_placeholder = re.compile(r'[ymdhsAM/PM]', re.IGNORECASE)
        if date_placeholder.search(num_format) and num_format != "0.00E+00":
            is_date_fmt = True

    if is_date_fmt:
        # 适配跨平台的无前导零格式符
        no_lead_fmt = "%-m" if not sys.platform.startswith("win") else "%#m"
        no_lead_day = "%-d" if not sys.platform.startswith("win") else "%#d"
        no_lead_hour = "%-H" if not sys.platform.startswith("win") else "%#H"
        # Excel日期占位符转Python strftime占位符，按长度从长到短替换避免误匹配
        fmt_map = {
            "yyyy": "%Y", "yy": "%y",
            "dddd": "%A", "ddd": "%a",
            "mmmm": "%B", "mmm": "%b",
            "dd": "%d", "d": no_lead_day,
            "mm": "%m", "m": no_lead_fmt,
            "hh": "%H", "h": no_lead_hour,
            "ss": "%S",
            "AM/PM": "%p", "am/pm": "%p"
        }
        py_fmt = num_format
        for old in sorted(fmt_map.keys(), key=lambda x: -len(x)):
            py_fmt = py_fmt.replace(old, fmt_map[old])
        return py_fmt, True
    else:
        # 清理数字格式里的Excel专用转义字符
        py_fmt = re.sub(r'_.', '', num_format)
        py_fmt = py_fmt.replace("\\", "")
        return py_fmt, False

def get_cell_display_text(cell):
    """获取和Excel端显示完全一致的单元格文本"""
    raw_val = cell.value
    if raw_val is None:
        return ""
    num_fmt = cell.number_format
    # 通用格式直接转字符串返回
    if num_fmt == "General":
        return str(raw_val)
    
    py_fmt, is_date = excel_format_to_python_fmt(num_fmt)
    if is_date and isinstance(raw_val, (datetime, date)):
        return raw_val.strftime(py_fmt)
    elif isinstance(raw_val, (int, float)):
        # 处理千分位、小数位数字格式
        if "#,##0" in py_fmt:
            dec_bit = 0
            if "." in py_fmt:
                dec_part = py_fmt.split(".")[-1]
                dec_bit = dec_part.count("0")
            return f"{raw_val:,.{dec_bit}f}"
        # 百分比、货币等格式可在此处补充对应转换逻辑
        return str(raw_val)
    else:
        return str(raw_val)

# 测试调用
if __name__ == "__main__":
    path = r'Test.xlsx'
    wb = openpyxl.load_workbook(path)
    sheet = wb.active
    target_cell = sheet.cell(row=2, column=3)
    print("原始值：", target_cell.value)
    print("单元格格式：", target_cell.number_format)
    print("Excel显示值：", get_cell_display_text(target_cell))

针对你提到的日期场景：当单元格number_format为[$-en-US]dddd, mmmm dd, yyyy、原始值为2022-01-31 00:00:00时，上述代码会返回Monday, January 31, 2022，和Excel显示效果完全一致。

使用时可根据业务中常用的自定义格式，补充格式映射规则即可：

• 带条件判断的分段格式、特殊货币/百分比格式，可在格式转换函数中增加分支解析
• 遇到匹配不准的格式，打印对应单元格的number_format实际值，补充到映射表即可快速适配
• 代码已经兼容Windows、macOS/Linux系统下日期无前导零的格式差异

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