datetime issue with xlrd & xlwt python libs
Question: I'm trying to write some dates from one excel spreadsheet to another.
Currently, I'm getting a representation in excel that isn't quite what I want
such as this: "40299.2501157407"

I can get the date to print out fine to the console, however it doesn't seem
to work right writing to the excel spreadsheet -- the data must be a date type
in excel, I can't have a text version of it.

Here's the line that reads the date in:

    
    
    date_ccr = xldate_as_tuple(sheet_ccr.cell(row_ccr_index, 9).value, book_ccr.datemode)
    

Here's the line that writes the date out:

    
    
    row.set_cell_date(11, datetime(*date_ccr))
    

There isn't anything being done to date_ccr in between those two lines other
than a few comparisons.

Any ideas?


Answer: You can write the floating point number directly to the spreadsheet and set
the number format of the cell. Set the format using the `num_format_str` of an
`XFStyle` object when you write the value.

<https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/doc/xlwt.html#xlwt.Worksheet.write-
method>

The following example writes the date 01-05-2010. (Also includes time of
06:00:10, but this is hidden by the format chosen in this example.)

    
    
    import xlwt
    
    # d can also be a datetime object
    d = 40299.2501157407
    
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('new')
    
    style = xlwt.XFStyle()
    style.num_format_str = 'DD-MM-YYYY'
    
    sheet.write(5, 5, d, style)
    wb.save('test_new.xls')
    

There are examples of number formats (num_formats.py) in the examples folder
of the xlwt source code. On my Windows machine: C:\Python26\Lib\site-
packages\xlwt\examples

You can read about how Excel stores dates (third section on this page):
<https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html>

