”;
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
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.
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
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
The worksheet looks like the following in Excel software −
write_datetime() and strptime()
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.
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 |
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)
This datetime object can now be written into the worksheet with write_datetime() method.
Example
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
The worksheet appears as follows when opened with Excel.
”;