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.

date format

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()

Output

工作表在 Excel 软件中看上去如下 −

The worksheet looks like the following in Excel software −

num format Parameter

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()

Output

在使用 Excel 打开时，工作表将显示如下内容：

The worksheet appears as follows when opened with Excel.

datetime and strptime