- Python XlsxWriter Tutorial
- Python XlsxWriter - Home
- Python XlsxWriter - Overview
- Python XlsxWriter - Environment Setup
- Python XlsxWriter - Hello World
- Python XlsxWriter - Important classes
- Python XlsxWriter - Cell Notation & Ranges
- Python XlsxWriter - Defined Names
- Python XlsxWriter - Formula & Function
- Python XlsxWriter - Date and Time
- Python XlsxWriter - Tables
- Python XlsxWriter - Applying Filter
- Python XlsxWriter - Fonts & Colors
- Python XlsxWriter - Number Formats
- Python XlsxWriter - Border
- Python XlsxWriter - Hyperlinks
- Python XlsxWriter - Conditional Formatting
- Python XlsxWriter - Adding Charts
- Python XlsxWriter - Chart Formatting
- Python XlsxWriter - Chart Legends
- Python XlsxWriter - Bar Chart
- Python XlsxWriter - Line Chart
- Python XlsxWriter - Pie Chart
- Python XlsxWriter - Sparklines
- Python XlsxWriter - Data Validation
- Python XlsxWriter - Outlines & Grouping
- Python XlsxWriter - Freeze & Split Panes
- Python XlsxWriter - Hide/Protect Worksheet
- Python XlsxWriter - Textbox
- Python XlsxWriter - Insert Image
- Python XlsxWriter - Page Setup
- Python XlsxWriter - Header & Footer
- Python XlsxWriter - Cell Comments
- Python XlsxWriter - Working with Pandas
- Python XlsxWriter - VBA Macro
- Python XlsxWriter Useful Resources
- Python XlsxWriter - Quick Guide
- Python XlsxWriter - Useful Resources
- Python XlsxWriter - Discussion
Python XlsxWriter - Date & Time
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.