Writing xlwt dates with Excel 'date' format

Question

I'm using xlwt to make a .xls spreadsheet, and I need to create date cells.

I've got writing out numbers, and setting the number format string to make them look like dates, but critically they aren't actually getting written as dates - if you do format cell in Excel, it's a "custom" Category rather than a "date" one, and this matters.

Can I make xlwt actually write "date" cells?

Answer 1

The number will show up in the Excel "Date" category if you use a format string that corresponds to one of Excel's built-in format strings such as dd/mm/yyy. for example:

import xlwt
import datetime

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet1')

date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'

worksheet.write(0, 0, datetime.datetime.now(), date_format)

workbook.save('date_format.xls')

If you modify this example to use a format string of d/mm/yyy then the number will show up in the "Custom" category.

Note: the above code works for the version of Excel that I tried but it is possible that this is subject to regional variation. The best thing to do is format a number in Excel with the "Date" format that you want and then, in the same dialog, click on "Custom" to see what format string is associated with the "Date" format. Then use that in your program.

Also: There isn't any native date type in the Excel XLS format. All dates are stored as numbers plus a format. There is nothing to distinguish them from any other number with a format except for the format string. This is one of the things that makes parsing an Excel file a pain since you have to apply heuristics to determine if a cell contains a date.