时间戳

首页 > 解决方案 > 如何使用openpyxl比较excel表单列中的日期时间对象？

python - 如何使用openpyxl比较excel表单列中的日期时间对象？

问题描述

我正在尝试创建一个 python 脚本来遍历 excel 电子表格中特定列的所有行。此列包含日期，我需要比较这些日期中的每一个，以便在 Excel 表中查找并返回最旧的日期。之后，我需要修改该行中的数据。

我试图将日期作为 datetime 对象附加到一个 numpy 数组中，这是可行的，但我无法遍历数组并比较日期。我还尝试将 excel 表中的日期重新格式化为 python 中的 datetime 对象，然后进行比较，但出现以下错误：

AttributeError: type object 'datetime.datetime' has no attribute 'datetime'

我尝试了其他一些不成功的方法。这些是我最接近实现我想要的东西的地方。我很迷茫，请帮助！

import openpyxl
import numpy as np
import datetime

def main():

    wb = openpyxl.load_workbook("C:\\Users\\User\\Desktop\\Python Telecom Project.xlsx")
    sheet = wb.active


def menuSelection():

    while True:
        menuChoice = input("Please select one of the following options:\n1. Add User\n2.Delete User\n3.Modify User\n")

        if menuChoice not in ('1', '2', '3'):
            print("The input entered is invalid, please try again")
            continue
        else:
            break

    return menuChoice

def findOldestDate():

    wb = openpyxl.load_workbook("C:\\Users\\User\\Desktop\\Python Telecom Project.xlsx")
    sheet = wb.active
##    startMult = np.empty((0,1000), dtype='datetime64[D]')
##    value = datetime.date.strftime("%Y-%m-%d")

    for rowNum in range(2, sheet.max_row+1):
        status = sheet.cell(row=rowNum, column=5).value
        d8 = sheet.cell(row=rowNum, column=6).value
        d8_2 = sheet.cell(row=rowNum+1, column=6).value
        d8.value = datetime.date.strftime(d8, "%Y-%m-%d")
        d8_2.value = datetime.date.strftime(d8_2, "%Y-%m-%d")
        d8.number_format = 'YYYY MM DD'
        d8_2.number_format = 'YYYY MM DD'

        if d8 < d8_2:
            oldestDate = d8
        elif d8 > d8_2:
            oldestDate = d8_2
        else:
            continue

    return oldestDate

##            array.append(startMult, date)
##
##    while counter < len(array)-1:
##
##        if array[counter] < array[counter + 1]:
##
##            oldestDate = array[counter]
##            counter += 1
##                
##        elif array[counter] > array[counter + 1]:
##
##            oldestDate = array[counter + 1]
##            counter += 1
##
##        else:
##            oldestDate = array[counter]
##            continue
##
##    return oldestDate


def addUser():

    wb = openpyxl.load_workbook("C:\\Users\\User\\Desktop\\Python Telecom Project.xlsx")
    sheet = wb.active

    dateTimeObj = datetime.date.today()

    print("Please enter the following information:\n")
    inputName = input("Name: ")
    inputNTID = input("NTID: ")
    inputRATSID = input("RATSID: ")
    inputStatus = input("Status: ")
    inputTaskNum = input("Task #: ")

    for rowVal in range(2, sheet.max_row+1):

        oldestDate = findOldDate()

        phoneNum = sheet.cell(row=rowVal, column=1).value
        name = sheet.cell(row=rowVal, column=2).value
        ntID = sheet.cell(row=rowVal, column=3).value
        ratsID = sheet.cell(row=rowVal, column=4).value
        status = sheet.cell(row=rowVal, column=5).value
        date = sheet.cell(row=rowVal, column=6).value

        if date == oldestDate:

            name = inputName
            ntID = inputNTID
            ratsID = inputRATSID
            status = inputStatus
            date = dateTimeObj

            print("\nChanges have been implemented successfully!")





##def deleteUser():
##    
##
##
##def modifyUser():




addUser()

这是当前的错误消息：

AttributeError: type object 'datetime.datetime' has no attribute 'datetime'

在此之前，我得到：

can't compare 'str' to 'datetime'

我想要的是从此函数返回的列中最旧的日期。

标签： pythondatedatetimeopenpyxlpython-datetime

解决方案

可以使用如下的单行来查找最旧的日期：

from datetime import datetime as dt
from re import match

def oldest(sheet, column):
   """
   Returns the tuple (index, timestamp) of the oldest date in the given sheet at the given column.
   """
   return min([(i, dt.strptime(sheet.cell(row=i, column=column).value, '%Y %m %d').timestamp()) for i in range(2, sheet.max_row+1) if isinstance(sheet.cell(row=i, column=column).value, str) and  match(r'\d{4}\s\d{2}\s\d{2}', sheet.cell(row=i, column=column).value)], key=lambda x:x[1])

更长、更慢但更易读的版本如下：

def oldest(sheet, column):
   """
   Returns the tuple (index, timestamp) of the oldest date in the given sheet at the given column.
   """
   format = '%Y %m %d'
   values = list()
   for i in range(2, sheet.max_row+1):
      if isinstance(sheet.cell(row=i, column=column).value, str) and  match(r'\d{4}\s\d{2}\s\d{2}', sheet.cell(row=i, column=column).value):
         values.append((i, dt.strptime(sheet.cell(row=i, column=column).value, format).timestamp()))
   return min(values, key=lambda x: x[1])

如果需要，您可以将检索到的时间戳转换回您所拥有的日期格式，如 python REPL 的此示例会话中所示：

>>> row, timestamp = oldest(sheet, 1)
>>> date = dt.utcfromtimestamp(timestamp[1]).strftime('%Y %m %d')
>>> date
'2019 10 31'
>>> row
30

推荐阅读