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

Be First to Comment

发表评论

电子邮件地址不会被公开。 必填项已用*标注