Python 如何使用openpyxl在Excel中使用'yyyy-mm-dd hh：mm：ss'形式的日期时间对象格式化单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24370385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to format cell with datetime object of the form 'yyyy-mm-dd hh:mm:ss' in Excel using openpyxl
提问by MothraDactyl
So, given:
所以，给定：
dttm = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
ws['A1'] = dttm
The result in excel is that the correct date-time is written to the cell (you can see it where you'd input formulas). BUT, the cell display format is only MM/DD/YYYY.
excel 中的结果是将正确的日期时间写入单元格（您可以在输入公式的地方看到它）。但是，单元格显示格式只有 MM/DD/YYYY。
I need the cell to display like "6/23/2014 13:56" instead of just "6/23/2014".
我需要单元格显示为“6/23/2014 13:56”，而不仅仅是“6/23/2014”。
How can I explicitly format the cell to accomplish this?
如何显式格式化单元格以完成此操作？
Thanks!
谢谢！
Edit
编辑
@alecxe This solution works and is exactly what I asked for. I would like to be able to save styles like the solution by @Woodham. Unfortunately it raises a typeError (see comment). Any suggestions?
@alecxe 这个解决方案有效，正是我所要求的。我希望能够像@Woodham 的解决方案一样保存样式。不幸的是它引发了一个类型错误（见评论）。有什么建议？
回答by alecxe
You can manually set the format_code:
您可以手动设置format_code：
value = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
cell = ws['A1']
cell.value = value
cell.style.number_format.format_code = 'MM/DD/YY HH:MM'
Alternatively, you can call _set_number_format()method on the cell:
或者，您可以_set_number_format()在单元格上调用方法：
cell._set_number_format('MM/DD/YY HH:MM')
You can also define the style and reuse it, by setting ws._stylesfor a cell:
您还可以通过设置ws._styles单元格来定义样式并重用它：
from openpyxl.styles import NumberFormat, Style
...
style = Style()
style.number_format = NumberFormat()
style.number_format.format_code = 'MM/DD/YY HH:MM'
ws['A1'] = value
ws._styles['A1'] = style
Tested - worked for me.
测试 - 为我工作。
回答by Woodham
I believe you will need to set a openpyxl.styles.Styleon the cell(s) that you want to format.
我相信您需要openpyxl.styles.Style在要格式化的单元格上设置 a 。
Looking at the documentation here, something like this should work:
dttm = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
s = Style(number_format=NumberFormat('dd-mm-yyyy h:mm:ss'))
ws['A1'] = dttm
ws['A1'].styles = s
Update:Style class is no longer used, for the solution refer to this answer.
更新：不再使用样式类，有关解决方案，请参阅此答案。
回答by Scott Roberts
I'm adding this as a new answer since I don't have enough reputation to add a comment to the above. The simplest way to format a cell is using .number_format = "format" as in:
我将此添加为新答案，因为我没有足够的声誉来为上述内容添加评论。格式化单元格的最简单方法是使用 .number_format = "format" ，如下所示：
value = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
cell = ws['A1']
cell.value = value
cell.number_format = 'YYYY MMM DD'
This is tested in openpyxl (2.2.2)
这是在 openpyxl (2.2.2) 中测试的
回答by Peconia
For openpyxl 2.3.4 the NumberFormat cannot be imported, but this code works to set the style:
对于 openpyxl 2.3.4，无法导入 NumberFormat，但此代码可用于设置样式：
from openpyxl.styles import Style
…
date_style = Style(number_format="DD/MM/YYYY HH:MM:MM")
ws['A1'].style = date_style
回答by quetzaluz
For openpyxl 2.4.5 you'll no longer have access to NumberFormatand Styleand will have to use NamedStyle. Here's some sample usage:
对于openpyxl 2.4.5你将不再有机会获得NumberFormat和Style，将不得不使用NamedStyle。以下是一些示例用法：
from openpyxl.styles import NamedStyle
date_style = NamedStyle(name='datetime', number_format='DD/MM/YYYY HH:MM:MM')
ws['A1'].style = date_style
Alternatively with the new NamedStyleclass, you can set the style by the string name once NamedStylehas been instantiated:
或者，使用新NamedStyle类，您可以NamedStyle在实例化后通过字符串名称设置样式：
from openpyxl.styles import NamedStyle
NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM')
ws['A1'].style = 'custom_datetime'
Documentation here: https://openpyxl.readthedocs.io/en/stable/styles.html
这里的文档：https: //openpyxl.readthedocs.io/en/stable/styles.html
回答by Will Grant
I found that this worked. Although number_format is used it seems to recognise the date format specified when put into the excel wb.
我发现这有效。尽管使用了 number_format，但它似乎可以识别放入 excel wb 时指定的日期格式。
import datetime
date = datetime.date(2020, 2, 24) # python datetime format is yyyy mm dd
ws.cell(row=[row_ref], column=[col_ref], value=date)
ws.cell(row=[row_ref], column=[col_ref]).number_format = 'dd/mm/yy'
