如何用Python实现基于日期比较的Excel数据矩阵输出

需求说明 #

现有Excel表格中，DEP_DATE_TIME（F列）、ARR_DATE_TIME（J列）及L列的日期时间格式均为dd-mm-yyyy hh:mm，需实现对应Excel公式=IF($F$13<L1,1,0)-IF($J$13>=L1,0,1)的逻辑：

• 若F13单元格时间 < 当前行L列时间，取1；否则取0
• 若J13单元格时间 >= 当前行L列时间，取0；否则取1
• 将两个结果相减，最终得到0或1并写入对应单元格

实现步骤 #

1. 安装依赖包 #

需要用到pandas处理表格数据，openpyxl读写Excel文件：

pip install pandas openpyxl

2. Python代码实现 #

方式一：用Pandas批量处理（适合大数据量）

以下代码以列名识别为例（若L列无列名，可改用位置索引，见注释）：

import pandas as pd

# 读取Excel文件，解析日期时间列
# 若L列无列名，可改用usecols='F,J,L', names=['DEP', 'ARR', 'L']
df = pd.read_excel(
    'your_file.xlsx',  # 替换为你的Excel文件路径
    sheet_name='Sheet1',  # 替换为目标工作表名
    parse_dates=['DEP_DATE_TIME', 'ARR_DATE_TIME', 'L列名'],  # 替换为L列实际列名
    dayfirst=True  # 关键：确保dd-mm-yyyy格式的日期被正确解析
)

# 获取F13和J13的值（Excel第13行对应pandas索引12，0-based）
f13 = df['DEP_DATE_TIME'].iloc[12]
j13 = df['ARR_DATE_TIME'].iloc[12]

# 执行逻辑计算，生成结果列
df['计算结果'] = (f13 < df['L列名']).astype(int) - (j13 >= df['L列名']).astype(int)

# 将结果写入原Excel文件（覆盖原工作表，保留数据结构）
with pd.ExcelWriter('your_file.xlsx', engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)

方式二：用OpenPyXL直接操作单元格（保留原有格式）

如果需要保留Excel的单元格样式，可直接操作单元格：

from openpyxl import load_workbook
from datetime import datetime

wb = load_workbook('your_file.xlsx')
ws = wb['Sheet1']

# 读取F13和J13的日期时间
f13 = datetime.strptime(ws['F13'].value, '%d-%m-%Y %H:%M')
j13 = datetime.strptime(ws['J13'].value, '%d-%m-%Y %H:%M')

# 遍历L列数据（假设从第1行到第100行，可根据实际调整）
for row in range(1, 101):
    l_cell = ws[f'L{row}']
    if l_cell.value is None:
        continue
    l_time = datetime.strptime(l_cell.value, '%d-%m-%Y %H:%M')
    # 计算逻辑
    val1 = 1 if f13 < l_time else 0
    val2 = 0 if j13 >= l_time else 1
    result = val1 - val2
    # 写入结果到M列（可替换为目标列）
    ws[f'M{row}'] = result

wb.save('your_file.xlsx')

关键说明 #

• dayfirst=True必须添加，否则pandas会错误将dd-mm-yyyy识别为mm-dd-yyyy，导致日期比较逻辑出错
• astype(int)将布尔值（True/False）转换为1/0，完全匹配Excel中IF函数的返回结果

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