# python修改excel文档相关日期信息
Date: 2020-07-29


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

#### 环境：

- windows10
- python3.7 + openpyxl

#### 1\. 使用`openpyxl`修改excel属性

```python
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`方法修改文件属性的`访问时间`,`修改时间`

```python
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`方法修改文件属性的`创建日期`, `访问时间`,`修改时间`

```python
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](https://openpyxl.readthedocs.io/en/stable/index.html#) [python 修改文件的创建时间、修改时间、访问时间](https://www.cnblogs.com/suwanbin/p/12037497.html) [pythondoc-os.utime](https://docs.python.org/2/library/os.html#os.utime) [In Python, how do you convert a datetime object to seconds?](https://stackoverflow.com/questions/7852855/in-python-how-do-you-convert-a-datetime-object-to-seconds/7852891#7852891) [python ImportError: No module named win32file](https://stackoverflow.com/questions/55551188/python-importerror-no-module-named-win32file)