17

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?

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
xorsyst
  • 7,897
  • 5
  • 39
  • 58

2 Answers2

24

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.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 1
    +1 for the example, but it should be added that just having an ISO format string such as '2014-01-23' will *not* convert as expected (format will be date, but content will be a useless text string); using the actual datetime() object avoids this issue. I did not test this with a comprehensive ISO string such as '2014-01-23T12:34:56', which may still work- someone else should drop a note here if they know this one. – Ryder May 06 '14 at 09:19
  • @RyderDain The title of the question is misleading and probably should be edited. The question, answer and example are about how to generate builtin date formats versus custom formats using `xlwt`. Generating the actual date isn't discussed. – jmcnamara May 06 '14 at 10:04
4

From http://www.youlikeprogramming.com/2011/04/examples-generating-excel-documents-using-pythons-xlwt/

Entering a Date into a Cell


import xlwt
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'D-MMM-YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls')
shantanoo
  • 3,617
  • 1
  • 24
  • 37
  • Thanks, but that's what I'm already doing, and it doesn't work correctly, as per my question. – xorsyst Jun 12 '13 at 16:08
  • Tried changing the cell format to 'General'. Getting expected floating point number. Changed to 'Date' format and it shows correct date. Is there any specific reason why it matters? – shantanoo Jun 12 '13 at 16:28
  • Yep, it works, but I'm afraid it does matter that it's Date in the first place. I can only be vague here and say "requirements". – xorsyst Jun 12 '13 at 16:37