Python Xlsxwriter 简明教程
Python XlsxWriter - Date & Time
在 Excel 中,日期存储为实数,以便可以在计算中使用它们。默认情况下,1900 年 1 月 1 日(称为纪元)被视为 1,因此 2022 年 1 月 28 日对应于 44589。类似地,时间表示为数字的小数部分,作为一天的百分比。因此,2022 年 1 月 28 日 11:00 对应于 44589.45833。
In Excel, dates are stored as real numbers so that they can be used in calculations. By default, January 1, 1900 (called as epoch) is treated 1, and hence January 28, 2022 corresponds to 44589. Similarly, the time is represented as the fractional part of the number, as the percentage of day. Hence, January 28, 2022 11.00 corresponds to 44589.45833.

The set_num_format() Method
由于 Excel 中的日期或时间就像其他任何数字一样,因此要将数字显示为日期,必须对其应用 Excel 数字格式。使用适当格式的 Format 对象的 set_num_format() 方法。
Since date or time in Excel is just like any other number, to display the number as a date you must apply an Excel number format to it. Use set_num_format() method of the Format object using appropriate formatting.
以下代码段显示“dd/mm/yy”格式的数字。
The following code snippet displays a number in "dd/mm/yy" format.
num = 44589
format1 = wb.add_format()
format1.set_num_format('dd/mm/yy')
ws.write('B2', num, format1)
The num_format Parameter
或者,可以将 add_format() 方法的 num_format 参数设置为所需的格式。
Alternatively, the num_format parameter of add_format() method can be set to the desired format.
format1 = wb.add_format({'num_format':'dd/mm/yy'})
ws.write('B2', num, format1)
Example
以下代码显示数字在各种日期格式中的情况。
The following code shows the number in various date formats.
import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
num=44589
ws.write('A1', num)
format2 = wb.add_format({'num_format': 'dd/mm/yy'})
ws.write('A2', num, format2)
format3 = wb.add_format({'num_format': 'mm/dd/yy'})
ws.write('A3', num, format3)
format4 = wb.add_format({'num_format': 'd-m-yyyy'})
ws.write('A4', num, format4)
format5 = wb.add_format({'num_format': 'dd/mm/yy hh:mm'})
ws.write('A5', num, format5)
format6 = wb.add_format({'num_format': 'd mmm yyyy'})
ws.write('A6', num, format6)
format7 = wb.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
ws.write('A7', num, format7)
wb.close()
write_datetime() and strptime()
XlsxWriter 的工作表对象还有 write_datetime() 方法,该方法在使用 Python 标准库的 datetime 模块获取日期和时间对象时很有用。
The XlsxWriter’s Worksheet object also has write_datetime() method that is useful when handling date and time objects obtained with datetime module of Python’s standard library.
strptime() 方法从根据给定格式解析的字符串中返回 datetime 对象。格式化字符串使用的部分代码如下所示 −
The strptime() method returns datetime object from a string parsed according to the given format. Some of the codes used to format the string are given below −
%a |
Abbreviated weekday name |
Sun, Mon |
%A |
Full weekday name |
Sunday, Monday |
%d |
Day of the month as a zero-padded decimal |
01, 02 |
%-d |
day of the month as decimal number |
1, 2.. |
%b |
Abbreviated month name |
Jan, Feb |
%m |
Month as a zero padded decimal number |
01, 02 |
%-m |
Month as a decimal number |
1, 2 |
%B |
Full month name |
January, February |
%y |
Year without century as a zero padded decimal number |
99, 00 |
%-y |
Year without century as a decimal number |
0, 99 |
%Y |
Year with century as a decimal number |
2022, 1999 |
%H |
Hour (24 hour clock) as a zero padded decimal number |
01, 23 |
%-H |
Hour (24 hour clock) as a decimal number |
1, 23 |
%I |
Hour (12 hour clock) as a zero padded decimal number |
01, 12 |
%-I |
Hour (12 hour clock) as a decimal number |
1, 12 |
%p |
locale’s AM or PM |
AM, PM |
%M |
Minute as a zero padded decimal number |
01, 59 |
%-M |
Minute as a decimal number |
1, 59 |
%S |
Second as a zero padded decimal number |
01, 59 |
%-S |
Second as a decimal number |
1, 59 |
%c |
locale’s appropriate date and time representation |
Mon Sep 30 07:06:05 2022 |
strptime() 方法的使用方式如下:
The strptime() method is used as follows −
>>> from datetime import datetime
>>> dt="Thu February 3 2022 11:35:5"
>>> code="%a %B %d %Y %H:%M:%S"
>>> datetime.strptime(dt, code)
datetime.datetime(2022, 2, 3, 11, 35, 5)
现在可以使用 write_datetime() 方法将此 datetime 对象写入工作表。
This datetime object can now be written into the worksheet with write_datetime() method.
Example
在以下示例中, datetime 对象将采用不同的格式写入。
In the following example, the datetime object is written with different formats.
import xlsxwriter
from datetime import datetime
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
dt="Thu February 3 2022 11:35:5"
code="%a %B %d %Y %H:%M:%S"
obj=datetime.strptime(dt, code)
date_formats = (
'dd/mm/yy',
'mm/dd/yy',
'dd m yy',
'd mm yy',
'd mmm yy',
'd mmmm yy',
'd mmmm yyy',
'd mmmm yyyy',
'dd/mm/yy hh:mm',
'dd/mm/yy hh:mm:ss',
'dd/mm/yy hh:mm:ss.000',
'hh:mm',
'hh:mm:ss',
'hh:mm:ss.000',
)
worksheet.write('A1', 'Formatted date')
worksheet.write('B1', 'Format')
row = 1
for fmt in date_formats:
date_format = wb.add_format({'num_format': fmt, 'align': 'left'})
worksheet.write_datetime(row, 0, obj, date_format)
worksheet.write_string(row, 1, fmt)
row += 1
wb.close()