如何在解析XLS数据时自动识别date、datetime、time等日期时间格式

方案1：读取单元格原生格式属性（准确率最高） #

Excel本身会为每个单元格存储格式配置，直接读取该属性可以100%区分三种类型：

• 处理xlsx格式（openpyxl库）：
  直接读取单元格的number_format属性，根据格式占位符判断即可：

  from openpyxl import load_workbook
  wb = load_workbook("你的文件.xlsx", data_only=True)
  cell = wb.active["A1"]
  fmt = cell.number_format.lower()
  has_date_part = "y" in fmt or "m" in fmt or "d" in fmt
  has_time_part = "h" in fmt or "s" in fmt or ("m" in fmt and ":" in fmt)
  
  if has_date_part and has_time_part:
      print("datetime类型")
  elif has_date_part:
      print("date类型")
  elif has_time_part:
      print("time类型")
  

• 处理xls格式（xlrd库）：
  通过单元格的xf_index获取对应的格式对象，读取格式字符串后用和上面相同的逻辑判断即可。

方案2：已提取为值后的兜底识别 #

如果已经无法获取单元格原生格式，可根据Excel日期时间的存储规则判断：

• 数值类型的判断逻辑：
  Excel的日期时间本质是浮点型数值，整数部分代表从1900-01-01开始的天数，小数部分代表当天的时间占比：
  • 数值小于1：无日期整数部分，为纯time类型，例：0.5对应12:00:00
  • 数值为正整数：无时间小数部分，为纯date类型，例：45231对应2023-12-05
  • 数值为大于1的带小数的浮点数：同时包含日期和时间，为datetime类型，例：45231.25对应2023-12-05 06:00:00
    注意：该逻辑仅适用于已确认是日期时间类的数值，避免和其他业务数值混淆
• 字符串类型的判断逻辑：
  用正则匹配格式特征即可：
  • 匹配到^[0-9]{1,2}:[0-9]{1,2}(:[0-9]{1,2})?$ 规则的为纯time类型
  • 匹配到^[0-9]{4}[-/年][0-9]{1,2}[-/月][0-9]{1,2}日?$ 规则的为纯date类型
  • 同时包含日期和时间格式特征的为datetime类型

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