python修改excel文档相关日期信息

工作需要修改excel文件创建内容时间(excel属性非文件属性)和上次修改时间, 用python写了一个脚本处理了一下。

环境：

• windows10
• python3.7 + openpyxl

1. 使用openpyxl修改excel属性

import openpyxl
from datetime import datetime

fh = openpyxl.load_workbook("results.xlsx")

obj = fh.properties   #To get old properties
print( obj )   # print old properties

fh.properties.created = datetime(2000,1,1, 8,30,11) 
fh.properties.modified = datetime(2000,2,1, 8,32,19) 

##similarly you can set other fields ##

new_obj = fh.properties   #Now get new properties
print ( new_obj )  # print new properties

fh.save("results2.xlsx")

2.使用os.utime方法修改文件属性的访问时间,修改时间

import os, sys,time
from datetime import datetime

# Showing stat information of file
stinfo = os.stat('results.xlsx')
print (stinfo)

# Using os.stat to recieve atime and mtime of file
print ("access time of results.xlsx: %s" %stinfo.st_atime)
print ("modified time of results.xlsx: %s" %stinfo.st_mtime)

# Modifying atime and mtime
t = datetime(2008, 1, 1, 12,12, 12)
atime = time.mktime(t.timetuple())
t = datetime(2009, 1, 1, 12,12, 12)
mtime = time.mktime(t.timetuple())

os.utime("results.xlsx",(atime, mtime))
print ("done!!")

3.使用pywin32–SetFileTime方法修改文件属性的创建日期, 访问时间,修改时间

from win32file import CreateFile, SetFileTime, GetFileTime, CloseHandle
from win32file import GENERIC_READ, GENERIC_WRITE, OPEN_EXISTING
from pywintypes import Time 
import time
from datetime import datetime

def modifyFileTime(filePath, createTime, modifyTime, accessTime):
    try:
        fh = CreateFile(filePath, GENERIC_READ | GENERIC_WRITE, 
        0, None, OPEN_EXISTING, 0, 0)

        createTimes = Time(createTime)
        accessTimes = Time(modifyTime)
        modifyTimes = Time(accessTime)

        print (createTimes, accessTimes, modifyTimes)

        SetFileTime(fh, createTimes, accessTimes, modifyTimes)
        CloseHandle(fh)
        return 0
    except:
        return 1

if __name__ == '__main__':
    t = datetime (2019, 12,13,21,51,2)
    cTime = time.mktime(t.timetuple())
    t = datetime (2019, 2,2,0,1,3)
    mTime = time.mktime(t.timetuple())
    t = datetime (2019, 2,2,0,1,4)
    aTime = time.mktime(t.timetuple())

    fName = r"results.xlsx"

    r = modifyFileTime(fName, cTime, mTime, aTime)
    if r == 0:
        print('修改完成')
    elif r == 1:
        print('修改失败')

参考及引用

Openpyxl Doc
python 修改文件的创建时间、修改时间、访问时间
pythondoc-os.utime
In Python, how do you convert a datetime object to seconds?
python ImportError: No module named win32file